I store the connection strings for all three of my environments (development, test, production) in the “connectionstrings” section of web.config on most of my ASP.NET Web applications. When deploying the app to one of these environments, I either have to change a key called “Environment” in the appSettings section of web.config, or I can just put it in machine.config on the server. Connection strings:
<connectionStrings>
<add name="AppDB_ConnectionString_DEVELOPMENT"
connectionString="Data Source=DEV_DBSERVERNAME;
Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString_TEST"
connectionString="Data Source=TEST_DBSERVERNAME;
Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString_PRODUCTION"
connectionString="Data Source=PROD_DBSERVERNAME;
Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
</connectionStrings>
Environment key:
<appSettings>
<add key="Environment" value="PRODUCTION"/>
</appSettings>
Setting the connection string attribute on a SqlDataSource control can be done in a number of ways. It can be programmatically done in the code-behind file, and this is the way it is most often done dynamically. This is not always convenient, as you may have to spend lots of time determining the event during which the string must be set.
SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("whatever")
For instance, if the SqlDataSource control is embedded inside the EditTemplate of a FormView control, it will not be accessible during Page.Load, and if a parameter of the SqlDataSource control is databound, you may have to navigate a Byzantine maze of ASP.NET lifecycle events to find out that it needs to be set in the Load event of some seemingly unrelated control.
To solve this problem, many people give up and use only one active connection string at a time in web.config, and comment the others out:
<connectionStrings>
<add name="AppDB_ConnectionString" connectionString="
Data Source=DEV_DBSERVERNAME;
Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<!-- <add name="AppDB_ConnectionString" connectionString="
Data Source=TEST_DBSERVERNAME;
Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString" connectionString="
Data Source=PROD_DBSERVERNAME;
Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/> -->
</connectionStrings>
Then, “dynamically” setting the string in the SqlDataSource markup like this:
<asp:SqlDataSource ID="SqlChildren" runat="server"
ConnectionString="<%$ ConnectionStrings:AppDB_ConnectionString %>"
SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblAppID" DefaultValue="0"
Name="SourceID" PropertyName="Text" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
This works fine, but will require a change in the connection string in web.config to move from one environment to another. At first, I assumed (and we all know what that does!) that one could dynamically set the attribute using the standard inline < %= %> notation like this:
<asp:SqlDataSource ID="SqlChildren" runat="server"
ConnectionString="<%=
System.Configuration.ConfigurationManager.AppSettings.Get("Environment") &
...[connection string] ... %>"
SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblAppID" DefaultValue="0" Name="SourceID"
PropertyName="Text" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Anyone who has tried this already knows what happened next. This caused a run-time error when the SqlDataSource tried to bind to the expression. After much wailing and gnashing of teeth, I found that it IS possible to dynamically set the connection string in a manner similar to the standard way! Instead of the < %= %> expression, a custom expression using the < %$ %> notation will work.
First, to build the custom expression in a class:
Imports System
Imports System.CodeDom
Imports System.Web.UI
Imports System.ComponentModel
Imports System.Web.Compilation
' Apply ExpressionEditorAttributes to allow the
' expression to appear in the designer.
<ExpressionPrefix("ConnStringExpression")> _
<ExpressionEditor("ConnStringExpressionEditor")> _
Public Class ConnStringExpressionBuilder
Inherits ExpressionBuilder
' Create a method that will return the result
' set for the expression argument.
Public Shared Function GetEvalData(ByVal expression As String, _
ByVal target As Type, ByVal entry As String) As Object
Return System.Configuration.ConfigurationManager.AppSettings("Environment") &
"_" & _
System.Configuration.ConfigurationManager.ConnectionStrings("whatever")
End Function
Public Overrides Function GetCodeExpression(ByVal entry _
As BoundPropertyEntry, ByVal parsedData As Object, ByVal context _
As ExpressionBuilderContext) As CodeExpression
Dim type1 As Type = entry.DeclaringType
Dim descriptor1 As PropertyDescriptor = _
TypeDescriptor.GetProperties(type1)(entry.PropertyInfo.Name)
Dim expressionArray1(2) As CodeExpression
expressionArray1(0) = New CodePrimitiveExpression(entry.Expression.Trim())
expressionArray1(1) = New CodeTypeOfExpression(type1)
expressionArray1(2) = New CodePrimitiveExpression(entry.Name)
Return New CodeCastExpression(descriptor1.PropertyType, _
New CodeMethodInvokeExpression(New CodeTypeReferenceExpression _
(MyBase.GetType()), "GetEvalData", expressionArray1))
End Function
End Class
Next, in your web.config file, add this to the compilation section:
<expressionBuilders>
<add expressionPrefix="ConnStringExpression"
type="ConnStringExpressionBuilder"/>
</expressionBuilders>
Lastly, change the ConnectionString attribute in the markup for the SqlDataSource control to:
<asp:SqlDataSource ID="SqlChildren" runat="server"
ConnectionString="<%$ ConnStringExpression:AppDB %>"
SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblAppID" DefaultValue="0"
Name="SourceID" PropertyName="Text" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Voila!
You can now have multiple connection strings declared in the web.config, and set them dynamically in your markup rather than in the code-behind.
Note: What goes after the “ConnStringExpression:” is incidental in this case; I just put “AppDB” for readability purposes.
This unique article, Window Shades “Dynamically Set the ConnectionString Attribute on a SqlDataSource
ASP.NET Control | Deep in the Code” ended up being excellent.
I am creating out a reproduce to demonstrate to my close friends.
Thanks-Cassandra
Great & Excellent Article .!
It helped me so so so much .!!
Thanks
in Visual Studio 2010+ you can use config transforms, just started using them myself. http://msdn.microsoft.com/en-us/library/dd465318(v=vs.100).aspx
David,
Excellent Article and work!
Anthony,
I have tried what worked for you but get the following ERROR:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Any ideas?
Marius
Hi David,
I figured it out. I was trying to change the hard-coded connection string in my devexpress xtrareports. I was able to change the connection dynamically on Page Load of my xtrareport. as shown below.
Me.myTableAdapted.Connection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings(Session(“myConnStr”)).ToString)
Thank you again for the excellent article!
Thanks,
Anthony
<DataSource DefaultConnectionIndex="0" FunctionsComponentName="QueriesTableAdapter" Modifier="AutoLayout, AnsiClass, Class, Public" SchemaSerializationMode="IncludeSchema" xmlns="urn:schemas-microsoft-com:xml-msdatasource">
<Connections>
<Connection AppSettingsObjectName="Web.config" AppSettingsPropertyName="MyData " ConnectionStringObject="" IsAppSettingsProperty="true" Modifier="Assembly" Name="MyData undefinedWeb.config)" ParameterPrefix="@" PropertyReference="AppConfig.System.Configuration.ConfigurationManager.0.ConnectionStrings.AppData.ConnectionString" Provider="System.Data.SqlClient" />
</Connections>
<Tables>
I’m not sure I understand what you’re asking, but if you’re talking about including it in the markup (.aspx file), you can replace the hard-coded connection string in an ASP:SqlDataSource tag as in http://satindersinght.blogspot.com/2012/01/how-to-set-dynamic-connectionstring-of.html.
My Sample Dataset is shown below
…
Great Article! Can you please help me how to dynamically connect to xsd using the above article. My xsd connection is using table adapter as shown below (static ConnectionString is MyData). How do I replace MyData to ConnStringExpression:AppDB