change SqlDataSource connection string in asp.net dynamically

Question

I have a page in asp.net (web forms) that up until now only had one database for data.

So all the controls that require something from the database use a datasource and the same connection string.

<asp:SqlDataSource id="SqlDataSource1" runat="server"
                    ConnectionString="<%$ ConnectionStrings:DefaultConnectionString %>" 
                    ProviderName="System.Data.SqlClient" 
                                        SelectCommand="SELECT ... ">
</asp:SqlDataSource>

Up until now I only needed one connection string since I had only one database but this has changed since I want some users to connect to database A and others to database B

What would be the correct way to go about it? Preferably without many changes...

Ta01 · Accepted Answer · 2010-06-01 12:22:06Z

1

You can change it programatically after you determine which one you want to use, i.e.

SqlDataSource1.ConnectionString = "whateveryouwant";

It doesn't have to be set inline in the control.

answered Jun 1, 2010 at 12:22

Ta01

31.7k13 gold badges74 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Thanos Papathanasiou Over a year ago

when in the page life cycle would be the best time to do this? I had this way as a backup since I have to go searching for all the sqlDataSources and changing them by hand.

Ta01 Over a year ago

Where will you be determining user info? Most likely after that, could be at the application level in Global.asax at Session Start? or at the page level at Page_Load, not sure of your design so just throwing out suggestions :)

Thanos Papathanasiou Over a year ago

I have the default asp.net membership (in a 3rd DB) so when the user logs in depending on some parameters I can find out what database he should belong. It's my first site so I don't know much about Global.asax or anything like that... could you give some links maybe?

Ta01 Over a year ago

Hmm if you have a membership DB, could you check the role if you have assigned them? like User.IsInRole? Also here is an older article but covers global.asax events articles.techrepublic.com.com/5100-10878_11-5771721.html

Thanos Papathanasiou Over a year ago

so... I should have the datasources get the connection string from Global.asax where I can change it once the user authenticates himself and then it would be correct?

|

No Name · Accepted Answer · 2013-05-10 10:26:12Z

1

On your page you have SqlDataSource1, and also in your web.config you should have:

<connectionStrings>
    <add name="MyConectionString" connectionString="Data Source=127.0.0.1;Initial Catalog=DBNAME;User ID=userName;Password=PASS" />
</connectionStrings>

To add the connection string to your SqlDatasource1, in code behind (cs file) use the following code in onliad methot:

SqlDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings["MyConectionString"].ConnectionString;

edited May 10, 2013 at 10:26

answered Apr 16, 2013 at 14:06

No Name

7496 silver badges14 bronze badges

2 Comments

Chris Forrence Over a year ago

Hello! Could you please add more context or an explanation for your answer?

No Name Over a year ago

On your page you have SqlDataSource1, and also in your web.config you should have:

<connectionStrings>     <add name="MyConectionString" connectionString="Data Source=127.0.0.1;Initial Catalog=DBNAME;User ID=userName;Password=PASS" />   </connectionStrings>

and to add connection string to your SqlDatasource1 you use code from above.

Collectives™ on Stack Overflow

change SqlDataSource connection string in asp.net dynamically

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related