Set Dynamic ConnectionString to SqlDataSource ASP.NET Control Inside GridView Template
Intro: The SqlDataSource control enables you to use a Web server control to access data that is located in a relational database. You can use the SqlDataSource control with data-bound controls such as the GridView, FormView, and DetailsView controls to display and manipulate data on an ASP.NET Web page, using little or no code.
Here in this article I am writing how to set Dynamically ConnectionString Attribute on SqlDataSource ASP.NET Control. Setting connection string for SqlDataSource control can de done programmatically in the code-behind ie (.aspx.cs) file.
On Page_Load:
SqlDataSource1.ConnectionString = yourconnectionstring;
If the SqlDataSource control is inside the FooterTemplate of a GridView control then it will not be accessible at Page_Load. Here’s the challenging task how to set ConnectionString on SqlDatasource control under Gridview Templates.
By following below steps you can achieve this:
Step 1:
Here sample code of gridview templates:
<asp:dropdownlist autopostback=”True” datasourceid=”sqldsWorkcategory” datatextfield=”name_wt” datavaluefield=”id_wt” id=”cmbfWorkCategory” onselectedindexchanged=”cmbfWorkCategory_SelectedIndexChanged” runat=”server” width=”118″>
</asp:dropdownlist>
<asp:sqldatasource connectionstring=”<%$ RepConnectionString: ” getconnection=”getconnection”>”
id=”sqldsWorkcategory” runat=”server”
selectcommand=”SELECT [id_wt], [name_wt] FROM [yourtablename]”>
</asp:sqldatasource>
</footertemplate>
Step 2:
Now edit your web.config file
As i have many connection so have make a diifernt XML file to store all connections key
<!– Now Add this pieace of code under Compilation as u can see. –>
<compilation debug=”true” targetFramework=”4.0″>
<expressionbuilders>
<add expressionprefix=”RepConnectionString” type=”RepConnectionStringExpressionBuilder”>
</add></expressionbuilders></configsource>
Step 3:
Add class file inside App_code.
Have named as CodeExpressionBuilder.cs.
Note: using System.CodeDom; is needed
if u find error then add refference or google for this namespace,
Code of CodeExpressionBuilder.cs
just copy as it is.
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Compilation; using System.Configuration; using System.IO; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data; using System.Collections.Generic; using System.Xml.Linq; using System.Xml; using System.CodeDom; //this is what we set in .aspx page (ie sqldatacource control) [ExpressionPrefix("RepConnectionString")] public class RepConnectionStringExpressionBuilder : ExpressionBuilder { public override CodeExpression GetCodeExpression(BoundPropertyEntry entry, object parsedData, ExpressionBuilderContext context) { CodeTypeReferenceExpression thisType = new CodeTypeReferenceExpression(base.GetType()); CodePrimitiveExpression expression = new CodePrimitiveExpression(entry.Expression.Trim().ToString()); string evaluationMethod = "GetConnectionString"; // return new CodeMethodInvokeExpression(thisType, evaluationMethod, new CodeExpression[] { expression }); } public static string GetConnectionString(string expression) { string getcon1='Some thing lke ur connectionstring... form xml' SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[getcon1].ConnectionString); string wConnString = conn.ConnectionString; return wConnString; } }
This will set your dynamic ConnectionString for SqlDataCource control on .aspx page.
Debug n get the flow of code.Enjoy…….
2 comments on “Sqldatasource control set dynamic Connectionstring in .aspx page only”
Works like a charm, thanks!
My pleasure…
If its helps anyone