In this article I will explain with an example, how to dynamically change (modify) Connection String of SqlDataSource control in Code Behind in ASP.Net using C# and VB.Net.
In order to dynamically change (modify) Connection String of SqlDataSource control in ASP.Net, the SqlDataSource has to be dynamically added in Code Behind.
Database
For this article I have made use Customers table of Microsoft Northwind Database. You can download the database using the link below.
HTML Markup
The HTML Markup contains the following GridView
.
<asp:GridView ID="GridView1" runat="server" CssClass="Grid" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Customer Name" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Country" HeaderText="Country" />
<asp:BoundField DataField="PostalCode" HeaderText="PostalCode" />
</Columns>
</asp:GridView>
Connection String
I have added the following connection string in the Web.Config file.
<connectionStrings>
<add name="conString" connectionString="Data Source=.\SQL2005;Initial Catalog=northwind;User id = user;password=xxx"/>
</connectionStrings>
Dynamically Change (Modify) Connection String of SqlDataSource in Code Behind
In the below code I am binding the ASP.Net GridView from code using SqlDataSource. The SqlDataSource is dynamically added to the page at runtime in the Page_Load event of the ASP.Net page.
The following parameters of the SqlDataSource need to be set.
1. ConnectionString – ConnectionString to the Northwind Database.
2. SelectCommand – The SQL Query you need to execute.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlDataSource SqlDataSource1 = new SqlDataSource();
SqlDataSource1.ID = "SqlDataSource1";
this.Page.Controls.Add(SqlDataSource1);
SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlDataSource1.SelectCommand = "SELECT top 10 ContactName, City, Country, PostalCode from Customers";
GridView1.DataSource = SqlDataSource1;
GridView1.DataBind();
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Dim SqlDataSource1 As New SqlDataSource()
SqlDataSource1.ID = "SqlDataSource1"
Me.Page.Controls.Add(SqlDataSource1)
SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString
SqlDataSource1.SelectCommand = "SELECT top 10 ContactName, City, Country, PostalCode from Customers"
GridView1.DataSource = SqlDataSource1
GridView1.DataBind()
End If
End Sub
Demo
Download
You can download the complete code in VB.Net and C# using the download link provided below.
