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:
我对ASP.NET世界很陌生,所以我遇到了在运行时配置连接字符串的最佳方法并让整个应用程序使用该连接字符串时遇到一些麻烦。以下是我计划构建的应用程序的更多信息:
- 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.
Application使用Forms身份验证,而不是Windows身份验证
将有一个登录页面,用户提供其SQL Server登录ID和密码
为简单起见,我希望所有SQLDataSource控件都指向web.config连接字符串。这将在设计时完成,而不是以编程方式设置它们。所以,他们会有这样的属性:ConnectionString =“<%$ ConnectionStrings:MyDB%>”
我想找到一种在运行时更改“MyDB”连接字符串的方法,以便它使用用户提供的登录ID和密码。但我不希望这个保存到web.config。它应仅对该用户的会话有效。
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.
人们通常这样做的“标准”方式是什么?我假设一种方法是使用连接字符串创建一个Session变量,然后在页面加载期间以编程方式更改每个SQLDataSource控件的ConnectionString属性。但我希望尽可能避免这种情况。
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.
由于许多人询问为什么我想为每个用户使用唯一的连接,并担心缺乏汇集,我想我会在这里评论,而不是评论每个单独的响应。
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.
此应用程序的性质要求每个用户都使用自己的帐户连接到数据库。后端安全性与其用户帐户相关联,因此我们无法使用“用户”和“管理员”等通用帐户。我们还需要知道每个用户的特定身份以进行审计控制。该应用程序通常只有10到20个用户,因此缺乏池化不是问题。我们可以再次讨论这种方法的优点,但不幸的是我在这里没有选项 - 项目要求每个用户在他们自己的帐户下连接到数据库。
I would love to require Windows authentication, but unfortunately some implementations of this application will require SQL authentication.
我很乐意要求Windows身份验证,但不幸的是,此应用程序的某些实现将需要SQL身份验证。
If I could just set the connection string when I declare the SQLDataSource controls like this, it would be a snap:
如果我在声明像这样的SQLDataSource控件时可以设置连接字符串,那么它将是一个快照:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString = "<%= Session("MyConnectionString") %>"
SelectCommand="SELECT * FROM [Customers]">
</asp: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?
但是我收到错误,因为它不喜欢那里的<%%>标签。如果在声明控件时我不能这样做,那么对于应用程序中的每个SQLDataSource控件,以编程方式执行此操作的最简单方法是什么?
Thanks very much for everyone's help!
非常感谢大家的帮助!
9 个解决方案
#1
5
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!
首先阅读关于表达式构建器的文章。我最喜欢的东西之一是引入我的网络应用程序!
Now for some code:
现在为一些代码:
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);
}
}
}
Then, in web.config register the Expression Builder as follows
然后,在web.config中注册表达式生成器,如下所示
...
<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)
(以上所有代码均来自此处,并且稍微修改过)
Finally change your SqlDataSource to the following (C#):
最后将您的SqlDataSource更改为以下(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;
}
}
Then your SqlDataSource would be
然后你的SqlDataSource将是
<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!
这样,如果您需要更改连接字符串的方式,您可以在一个地方完成!
#2
1
Load the Web.config connection string to a session variable in Session_Start. If user provided his/her own credentials, update them in the session variable. Otherwise, the defaults (web.config values) will be in effect.
将Web.config连接字符串加载到Session_Start中的会话变量。如果用户提供了他/她自己的凭据,请在会话变量中更新它们。否则,默认值(web.config值)将生效。
#3
1
I would say you have a few options, some work with what you have, others would require you to change things.
我会说你有几个选择,一些与你拥有的东西一起工作,其他人会要求你改变一些东西。
-
Store the default connection in the web.config, load it into session, use it until the user is logged in, then update the value in session later.
将默认连接存储在web.config中,将其加载到会话中,使用它直到用户登录,然后稍后更新会话中的值。
-
Implement windows authentication and identity impersonation and setup the windows accounts with access to the SQL Server box.
实现Windows身份验证和身份模拟,并设置可以访问SQL Server框的Windows帐户。
If you go with item number one, create a shared helper function that will get the connection, check session first, then load from web.config. That way all of your datasources can still be bound at design time.
如果您使用第一项,请创建一个共享帮助函数,该函数将获得连接,首先检查会话,然后从web.config加载。这样,您的所有数据源仍然可以在设计时绑定。
NOTE: this is NOT a typical situation, and there are performance implications of using multiple SQL Server accounts for connection, such as the lack of the ability to utilize connection pooling among other items.
注意:这不是典型的情况,并且使用多个SQL Server帐户进行连接会产生性能影响,例如缺乏在其他项目中使用连接池的能力。
#4
1
I don't recommend you do this, because you will have scalability problems. The web application won't be able to use connection pooling and will open as many connections as you have users accessing it. You will have many real connection opening/closing as well.
我不建议你这样做,因为你会遇到可扩展性问题。 Web应用程序将无法使用连接池,并且将打开与用户访问它时一样多的连接。您将有许多真正的连接打开/关闭。
If you decide to proceed:
如果您决定继续:
- If you can use windows authentication, use impersonation+integrated mode for the sql connection. Use windows groups to set up permissions on sql server.
- You can recursively access all items on the page, looking for sqldatasources, and apply the connection to each of them. You can attach to the oninit of the page from the global.asax, so you could apply it to all pages.
如果您可以使用Windows身份验证,请使用模拟+集成模式进行SQL连接。使用Windows组在sql server上设置权限。
您可以递归访问页面上的所有项目,查找sqldatasources,并将连接应用于每个项目。您可以从global.asax附加到页面的oninit,以便将其应用于所有页面。
Why do you want to do it this way? What are the constraints involved? (usually a trusted subsystem model is used on web applications i.e. authentication/authorization is done at the web app level/or at a separate business tier)
你为什么要这样做?涉及的约束是什么? (通常在Web应用程序上使用受信任的子系统模型,即在Web应用程序级别/或在单独的业务层进行身份验证/授权)
#5
0
Without saving it to web.config there is no way to do this.
如果不将其保存到web.config,则无法执行此操作。
#6
0
As Mitch says, you lose connection pooling if you have users login in with different credentials (if this causes different connection strings.)
正如Mitch所说,如果用户使用不同的凭据登录,则会丢失连接池(如果这会导致不同的连接字符串。)
If you're just worried about separating admin users from normal users, just have 2 connection strings, one for admins and one for normal users. Use asp.net role providers to provide appropriate permissions to users.
如果您只是担心将管理员用户与普通用户分开,则只需要2个连接字符串,一个用于管理员,一个用于普通用户。使用asp.net角色提供程序为用户提供适当的权限。
#7
0
You can use an aspect programming library like Postsharp, to change the connection string dynamically at runtime.
您可以使用Postsharp等方面编程库在运行时动态更改连接字符串。
#8
0
A method I am using (I forget where I learned abbout it) is to add a SettingsLoaded handler at app startup time, then set the new connection string in the handler. I haven't tried this in ASP.NET, just a local app, so your mileage may vary:
我正在使用的方法(我忘记了我在哪里学习它)是在app启动时添加一个SettingsLoaded处理程序,然后在处理程序中设置新的连接字符串。我没有在ASP.NET中尝试这个,只是一个本地应用程序,所以你的里程可能会有所不同:
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";
}
#9
0
I have a similar situation. I set the connection string to a default connection string from the web.config. Then in code behind, you can set the connection string to a different value. You must do this in Page_Load, before the data source selecting event fires, like so....
我有类似的情况。我将连接字符串设置为web.config中的默认连接字符串。然后在代码后面,您可以将连接字符串设置为不同的值。您必须在Page_Load中执行此操作,然后才能触发数据源选择事件....
SqlDataSource1.ConnectionString = ConnectionString;
If you want to make sure that the default is not used, you can make the default a non-working connection string and handle the exception, or just check if it is the default and throw an error if so. You can also just check if the user is logged in during the selecting event of your data source and cancel the event if not...
如果要确保未使用默认值,可以使默认值为非工作连接字符串并处理异常,或者只检查它是否为默认值,如果是,则抛出错误。您还可以检查用户是否在数据源的选择事件期间登录,如果不是,则取消该事件......
#1
5
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!
首先阅读关于表达式构建器的文章。我最喜欢的东西之一是引入我的网络应用程序!
Now for some code:
现在为一些代码:
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);
}
}
}
Then, in web.config register the Expression Builder as follows
然后,在web.config中注册表达式生成器,如下所示
...
<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)
(以上所有代码均来自此处,并且稍微修改过)
Finally change your SqlDataSource to the following (C#):
最后将您的SqlDataSource更改为以下(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;
}
}
Then your SqlDataSource would be
然后你的SqlDataSource将是
<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!
这样,如果您需要更改连接字符串的方式,您可以在一个地方完成!
#2
1
Load the Web.config connection string to a session variable in Session_Start. If user provided his/her own credentials, update them in the session variable. Otherwise, the defaults (web.config values) will be in effect.
将Web.config连接字符串加载到Session_Start中的会话变量。如果用户提供了他/她自己的凭据,请在会话变量中更新它们。否则,默认值(web.config值)将生效。
#3
1
I would say you have a few options, some work with what you have, others would require you to change things.
我会说你有几个选择,一些与你拥有的东西一起工作,其他人会要求你改变一些东西。
-
Store the default connection in the web.config, load it into session, use it until the user is logged in, then update the value in session later.
将默认连接存储在web.config中,将其加载到会话中,使用它直到用户登录,然后稍后更新会话中的值。
-
Implement windows authentication and identity impersonation and setup the windows accounts with access to the SQL Server box.
实现Windows身份验证和身份模拟,并设置可以访问SQL Server框的Windows帐户。
If you go with item number one, create a shared helper function that will get the connection, check session first, then load from web.config. That way all of your datasources can still be bound at design time.
如果您使用第一项,请创建一个共享帮助函数,该函数将获得连接,首先检查会话,然后从web.config加载。这样,您的所有数据源仍然可以在设计时绑定。
NOTE: this is NOT a typical situation, and there are performance implications of using multiple SQL Server accounts for connection, such as the lack of the ability to utilize connection pooling among other items.
注意:这不是典型的情况,并且使用多个SQL Server帐户进行连接会产生性能影响,例如缺乏在其他项目中使用连接池的能力。
#4
1
I don't recommend you do this, because you will have scalability problems. The web application won't be able to use connection pooling and will open as many connections as you have users accessing it. You will have many real connection opening/closing as well.
我不建议你这样做,因为你会遇到可扩展性问题。 Web应用程序将无法使用连接池,并且将打开与用户访问它时一样多的连接。您将有许多真正的连接打开/关闭。
If you decide to proceed:
如果您决定继续:
- If you can use windows authentication, use impersonation+integrated mode for the sql connection. Use windows groups to set up permissions on sql server.
- You can recursively access all items on the page, looking for sqldatasources, and apply the connection to each of them. You can attach to the oninit of the page from the global.asax, so you could apply it to all pages.
如果您可以使用Windows身份验证,请使用模拟+集成模式进行SQL连接。使用Windows组在sql server上设置权限。
您可以递归访问页面上的所有项目,查找sqldatasources,并将连接应用于每个项目。您可以从global.asax附加到页面的oninit,以便将其应用于所有页面。
Why do you want to do it this way? What are the constraints involved? (usually a trusted subsystem model is used on web applications i.e. authentication/authorization is done at the web app level/or at a separate business tier)
你为什么要这样做?涉及的约束是什么? (通常在Web应用程序上使用受信任的子系统模型,即在Web应用程序级别/或在单独的业务层进行身份验证/授权)
#5
0
Without saving it to web.config there is no way to do this.
如果不将其保存到web.config,则无法执行此操作。
#6
0
As Mitch says, you lose connection pooling if you have users login in with different credentials (if this causes different connection strings.)
正如Mitch所说,如果用户使用不同的凭据登录,则会丢失连接池(如果这会导致不同的连接字符串。)
If you're just worried about separating admin users from normal users, just have 2 connection strings, one for admins and one for normal users. Use asp.net role providers to provide appropriate permissions to users.
如果您只是担心将管理员用户与普通用户分开,则只需要2个连接字符串,一个用于管理员,一个用于普通用户。使用asp.net角色提供程序为用户提供适当的权限。
#7
0
You can use an aspect programming library like Postsharp, to change the connection string dynamically at runtime.
您可以使用Postsharp等方面编程库在运行时动态更改连接字符串。
#8
0
A method I am using (I forget where I learned abbout it) is to add a SettingsLoaded handler at app startup time, then set the new connection string in the handler. I haven't tried this in ASP.NET, just a local app, so your mileage may vary:
我正在使用的方法(我忘记了我在哪里学习它)是在app启动时添加一个SettingsLoaded处理程序,然后在处理程序中设置新的连接字符串。我没有在ASP.NET中尝试这个,只是一个本地应用程序,所以你的里程可能会有所不同:
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";
}
#9
0
I have a similar situation. I set the connection string to a default connection string from the web.config. Then in code behind, you can set the connection string to a different value. You must do this in Page_Load, before the data source selecting event fires, like so....
我有类似的情况。我将连接字符串设置为web.config中的默认连接字符串。然后在代码后面,您可以将连接字符串设置为不同的值。您必须在Page_Load中执行此操作,然后才能触发数据源选择事件....
SqlDataSource1.ConnectionString = ConnectionString;
If you want to make sure that the default is not used, you can make the default a non-working connection string and handle the exception, or just check if it is the default and throw an error if so. You can also just check if the user is logged in during the selecting event of your data source and cancel the event if not...
如果要确保未使用默认值,可以使默认值为非工作连接字符串并处理异常,或者只检查它是否为默认值,如果是,则抛出错误。您还可以检查用户是否在数据源的选择事件期间登录,如果不是,则取消该事件......