What is the best way to change the web.config connection string at runtime?

Question

I am new to ASP.NET, so I am having a bit of trouble with the best way to configure the connection string at runtime and use the entire application in this chain. Here is a bit more information about the application I plan to build:

• Application uses forms authentication rather than Windows authentication
• There will be a login page where the user submits their ID and password to log into SQL Server
• For simplicity, I would like all SQLDataSource controls to point to the web.config connection string. This would be done during development, and not for their programming. So they will have this property: ConnectionString = "<% $ ConnectionStrings: MyDB%>"
• I would like to find a way to change the connection string "MyDB" at runtime so that it uses the login ID and password provided by the user. But I do NOT want this to be saved in web.config. It must be active only for this user session.

What is the “standard” way people usually do this? I suggest that one of the methods would be to create a Session variable with a connection string, and then programmatically change the ConnectionString property of each SQLDataSource control at page load time. But I was hoping to avoid this, if possible.

---

Since many people asked why I would like to use a unique connection for each user and were worried about the lack of a pool, I decided that I would comment on this here and not comment on each individual answer.

, . , , "" "". . 10-20 , . , , , - , .

Windows, , , SQL.

, SQLDataSource, , :

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString = "<%= Session("MyConnectionString") %>"
    SelectCommand="SELECT * FROM [Customers]">
  </asp:SqlDataSource>

, <%% > . , SQLDataSource ?

!

Answer 1

, , .

. , -!

:

, :

using System;
using System.CodeDom;
using System.Web.UI;
using System.Web.Compilation;

namespace MyNamespace.Web.Compilation
{
    [ExpressionPrefix("code")]
    public class CodeExpressionBuilder : ExpressionBuilder
    {
        public override CodeExpression GetCodeExpression(BoundPropertyEntry entry,
           object parsedData, ExpressionBuilderContext context)
        {
            return new CodeSnippetExpression(entry.Expression);
        }
    }

}

web.config Builder

...
<compilation debug="false">
  <expressionBuilders>
    <add expressionPrefix="Code" type="MyNamespace.Web.Compilation.CodeExpressionBuilder"/>
  </expressionBuilders>
</compilation>
...

( )

, SqlDataSource (#):

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString='<%$ code: (string)Session["MyConnectionString"] ?? ConfigurationManageer.ConnectionStrings["myDefaultConn"].ConnectionString %>'
    SelectCommand="SELECT * FROM [Customers]">
</asp:SqlDataSource>

( ) , , - :

public static ConnectionManager
{
   public static string GetConnectionString()
   {
      return HttpContext.Current.Session["MyConnectionString"] as string ??
             ConfigurationManager.ConnectionStrings["DefaultConnectionStr"].ConnectionString;
   }
}

SqlDataSource

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString='<%$ code: ConnectionManager.GetConnectionString() %>'
    SelectCommand="SELECT * FROM [Customers]">
</asp:SqlDataSource>

, - , !

Answer 2

Web.config Session_Start. , . (Web.config) .

Answer 3

, , , , .

• web.config, , , , .

• Windows Windows SQL Server.

, , , , web.config. , .

. , SQL Server , , .

Answer 4

, . - , . .

:

• Windows, + sql. sql.
• , sqldatasources . oninit global.asax, .

? ? ( , -, / - -).

Answer 5

web.config, .

Answer 6

, , ( .)

- , , . asp.net, .

Answer 7

. web.config. . Page_Load, , , ...

SqlDataSource1.ConnectionString = ConnectionString;

, , , , , , . , , ...

Answer 8

, ​​ Postsharp, .

Answer 9

The method that I use (I forgot where I found out about this) is to add a handler SettingsLoadedduring application startup, and then set a new connection string in the handler. I have not tried this in ASP.NET, just in a local application, so your mileage may vary:

Settings.Default.SettingsLoaded += new System.Configuration.SettingsLoadedEventHandler(Default_SettingsLoaded);

void Default_SettingsLoaded(object sender, System.Configuration.SettingsLoadedEventArgs e)
{
    Settings.Default["ConnectionString"] = "my new connection string";
}