How to Run Time Dynamically Build Connection String to DataBase in Asp.Net C# VB.Net

Asp.net gives developers a class to build runtime dynamically connectionstring named SqlConnectionStringBuilder. Here in this article I will show you by an example. Please have a look at the below code example which will help you to understand how one can build dynamically Connectionstring with less error possibilities.

To do the Example First Create a SQL Server Database Table:

CREATE TABLE [dbo].[tblStock](
    [BrandName] [varchar](max) NULL,
    [CategoryName] [varchar](max) NULL,
    [ProductName] [varchar](max) NULL,
    [LogicalQuantity] [bigint] NULL,
    [PhysicalQuantity] [bigint] NULL,
    [QuarentineQuantity] [bigint] NULL
) ON [PRIMARY]

Now Insert Some Data:

INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Handwash',55781,55780,1)
INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Soap',38786,38780,6)
INSERT INTO tblStock VALUES('Kohinoor','ToothPaste','Tibbet Clear',34503,34500,3)
INSERT INTO tblStock VALUES('Proctor & Gamble','Bubble','',43809,43800,9)
INSERT INTO tblStock VALUES('Telenor','Mobile','Data SIM',43876,43870,6)
INSERT INTO tblStock VALUES('Telenor','Mobile','GP Modem',23907,23900,7)
INSERT INTO tblStock VALUES('Uniliver','Soap','Lux',19806,19800,6)
INSERT INTO tblStock VALUES('Uniliver','TeleProducts','Brittle',20703,20700,3)
INSERT INTO tblStock VALUES('Uniliver','ToothPaste','Close Up',16755,16750,5)

Now add a GridView in Default.aspx Page:

    <asp:GridView ID="GridView1" runat="server" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
    </asp:GridView>

Now Create Runtime ConnectionString under Page_Load Event:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //Build connectionstring
            SqlConnectionStringBuilder ConnBuilder = new SqlConnectionStringBuilder();

            // For Windows Authentication
            ConnBuilder.DataSource = @".\SQLExpress"; // Name of the Sql Server instance
            ConnBuilder.InitialCatalog = "TestDB"; // Database Name
            ConnBuilder.ConnectTimeout = 0;
            ConnBuilder.IntegratedSecurity = true;

            /*
            // For SQL Server Authentication
            ConnBuilder.DataSource = @".\SQLExpress"; // Name of the Sql Server instance
            ConnBuilder.InitialCatalog = "TestDB"; // Database Name
            ConnBuilder.UserID = "sa";
            ConnBuilder.Password = "Your Password";
            ConnBuilder.ConnectTimeout = 0;
            ConnBuilder.IntegratedSecurity = false;
            */

            //How to use
            DataTable dt;
            String SQL = "SELECT * From tblStock";
            using (SqlConnection Conn = new SqlConnection(ConnBuilder.ConnectionString))
            {
                using (SqlCommand comm = new SqlCommand(SQL, Conn))
                {
                    Conn.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(comm))
                    {
                        dt = new DataTable("tbl");
                        da.Fill(dt);
                    }
                }
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }

Note: Don’t forget to add “System.Data” and “System.Data.SqlClient” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            'Build connectionstring
            Dim ConnBuilder As New SqlConnectionStringBuilder()

            ' For Windows Authentication
            ConnBuilder.DataSource = ".\SQLExpress"
            ' Name of the Sql Server instance
            ConnBuilder.InitialCatalog = "TestDB"
            ' Database Name
            ConnBuilder.ConnectTimeout = 0
            ConnBuilder.IntegratedSecurity = True

            '
            '            // For SQL Server Authentication
            '            ConnBuilder.DataSource = @".\SQLExpress"; // Name of the Sql Server instance
            '            ConnBuilder.InitialCatalog = "TestDB"; // Database Name
            '            ConnBuilder.UserID = "sa";
            '            ConnBuilder.Password = "Your Password";
            '            ConnBuilder.ConnectTimeout = 0;
            '            ConnBuilder.IntegratedSecurity = false;
            '            


            'How to use
            Dim dt As DataTable
            Dim SQL As [String] = "SELECT * From tblStock"
            Using Conn As New SqlConnection(ConnBuilder.ConnectionString)
                Using comm As New SqlCommand(SQL, Conn)
                    Conn.Open()
                    Using da As New SqlDataAdapter(comm)
                        dt = New DataTable("tbl")
                        da.Fill(dt)
                    End Using
                End Using
            End Using
            GridView1.DataSource = dt
            GridView1.DataBind()
        End If

    End Sub

Note: Don’t forget to add “System.Data” and “System.Data.SqlClient” namespaces.

Output:
Runtime Connection string asp.net csharp vb.net

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*