在运行时更改web.config连接字符串的最佳方法是什么? [英] What is the best method for changing a web.config connectionstring at runtime?

问题描述

我对ASP.NET领域还很陌生，因此想出在运行时配置连接字符串并让整个应用程序使用该连接字符串的最佳方法有点麻烦.这是有关我计划构建的应用程序的更多信息:

I'm pretty new to the ASP.NET world so I'm having a little trouble coming up with the best way to configure a connection string at runtime and have the entire application use that connection string. Here is a little more info on the application that I plan to build:

• 应用程序使用Forms身份验证，而不是Windows身份验证
• 会有一个登录"页面，用户可以在其中提供其SQL Server登录ID和密码.
• 为简单起见，我希望所有SQLDataSource控件都指向一个web.config连接字符串.这将在设计时完成，而不是以编程方式进行设置.因此，它们将具有以下属性: ConnectionString =<％$ ConnectionStrings:MyDB％>"
• 我想找到一种在运行时更改"MyDB"连接字符串的方法，以便它使用用户提供的登录ID和密码.但是我不希望将其保存到web.config.它仅应在该用户的会话中处于活动状态.

• Application uses Forms authentication, not Windows authentication
• There will be a Login page where the user supplies their SQL Server login ID and password
• For simplicity, I would like to have all the SQLDataSource controls point to a web.config connection string. This would be done at design time rather than setting them programatically. So, they would have a property like this: ConnectionString="<%$ ConnectionStrings:MyDB %>"
• I'd like to find a way to change the "MyDB" connection string at runtime so it uses the login id and password that the user provided. But I do NOT want this saved to web.config. It should only be active for that user's session.

人们通常这样做的标准"方式是什么?我假设一种方法是使用连接字符串创建一个Session变量，然后以编程方式在页面加载期间更改每个SQLDataSource控件的ConnectionString属性.但是我希望尽可能避免这种情况.

What is the "standard" way that people usually do this? I assume one method would be to create a Session variable with the connection string and then programatically change the ConnectionString property of every SQLDataSource control during page load. But I was hoping to avoid that if possible.

由于许多人问我为什么要为每个用户使用唯一的连接，并担心缺少池，所以我想在这里对此发表评论，而不是对每个响应进行评论.

Since a number of people asked about why I would want to use a unique connection for each user and were concerned about the lack of pooling, I figured I would comment on that here rather than comment on each individual response.

此应用程序的性质要求每个用户都使用自己的帐户连接到数据库.后端安全性与他们的用户帐户绑定，因此我们不能使用用户"和管理员"之类的通用帐户.我们还需要知道每个用户的特定身份以进行审核控制.该应用程序通常只有10至20个用户，因此没有池不足的问题.我们可以再次讨论这种方法的优点，但不幸的是我这里没有选择-该项目要求每个用户都使用自己的帐户连接到数据库.

The nature of this application requires that every user connect to the database under their own account. The back-end security is tied to their user account so we can't use generic accounts like "user" and "administrator". We also need to know the specific identity of each user for auditing control. The application usually only has 10 to 20 users, so the lack of pooling isn't a concern. We could debate the merits of this approach another time, but unfortunately I don't have an option here - the project requires that each user connect to the database under their own account.

我很想要求Windows身份验证，但是不幸的是，此应用程序的某些实现将需要SQL身份验证.

I would love to require Windows authentication, but unfortunately some implementations of this application will require SQL authentication.

如果在声明这样的SQLDataSource控件时仅设置连接字符串，那将是一件容易的事:

If I could just set the connection string when I declare the SQLDataSource controls like this, it would be a snap:

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

但是我收到一个错误，因为它不喜欢那里的<％％>标签.如果在声明控件时无法执行此操作，那么对应用程序中的每个SQLDataSource控件以编程方式执行此操作的最简单方法是什么?

But I get an error because it doesn't like <% %> tags there. If I can't do this when declaring the control, what is the easiest way to do this programatically for every SQLDataSource control in the application?

非常感谢大家的帮助！

推荐答案

如果您不想深入研究背后的代码，则可以采用另一种方法.

If you are not wanting to delve into code behind, there is another way you can do this.

请先阅读表达式构建器.我最喜欢带入我的网络应用程序的东西之一！

First read this article on expression builders. One of my favorite things to bring into to my web-apps!

现在输入一些代码:

首先在您的项目中创建一个包含以下内容的类:

First make a class in your project that contains the following:

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中，按如下所示注册表达式生成器

Then, in web.config register the Expression Builder as follows

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

(以上所有代码均摘自此处并稍加修改)

(all code above from taken from here and modified ever so slightly)

最后将您的SqlDataSource更改为以下(C#):

Finally change your SqlDataSource to the following (C#):

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

如果您想要(并且我建议)创建一个静态类，该类可以为您确定连接字符串，例如:

If you wanted (and I would recommend) creating a static class that handles figuring out the connection string for you say something like:

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

那么您的SqlDataSource应该是

Then your SqlDataSource would be

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

这样，如果您需要更改获取连接字符串的方式，就可以在一个地方完成！

That way if you ever need to change how get a connection string you can do it one place!

这篇关于在运行时更改web.config连接字符串的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！