Efficient Best Syntax Using statement to Open a Database SQL Connection in Asp.Net C# VB.Net

To describe the best way to open sql server connection in asp.net here I am choosing to Bind a GridView because in most of the cases the major task is to bind GridView data. You can make more generous method to collect sql server data but here my intention is to show you how you can open sql server connection efficiently & effectively.

Objective/Outcome:
Efficient best Syntax Using statement to Open a Database SqlConnection in Asp.Net Csharp VB.Net

To do the Example First Create 2 Database Tables:

CREATE TABLE [dbo].[Brand](
	[ID] [bigint] NOT NULL,
	[Name] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Product](
	[ID] [bigint] NOT NULL,
	[Name] [varchar](max) NULL,
	[Description] [varchar](max) NULL,
	[BrandID] [bigint] NULL,
 CONSTRAINT [PK_Product_2] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now Insert Some Data:

INSERT INTO Brand Values(1,'Kohinoor')
INSERT INTO Brand Values(2,'Proctor & Gamble')
INSERT INTO Brand Values(3,'Telenor')
INSERT INTO Brand Values(4,'Unilever')

INSERT INTO PRODUCT VALUES(1,'Lux Beauty Soap','Product of Unilever',4)
INSERT INTO PRODUCT VALUES(2,'Tibbet Pomed','Winter Product',1)
INSERT INTO PRODUCT VALUES(3,'Data SIM','Subscriber Identity Module',3)
INSERT INTO PRODUCT VALUES(4,'Nippon','Color Television',1)

Now modify the Web.Config File to Connect to Database:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now add the GridView Control like Below:

<asp:GridView ID="GridView1" runat="server" DataKeyNames="ID" 
        AutoGenerateColumns="False" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
         <Columns>
             <asp:BoundField DataField="Name" HeaderText="Name"/>
             <asp:BoundField DataField="Description" HeaderText="Description" />
             <asp:BoundField DataField="Brand" HeaderText="Brand" />
        </Columns>
        </asp:GridView>

Now use Using Statement to Open Database Connection:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt;
            String SQL = "SELECT P.ID ID, P.Name Name, P.Description Description, B.ID BrandID,B.Name Brand" +
                    " FROM Product P, Brand B" +
                    " WHERE P.BrandID=B.ID";

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
            {
                conn.Open();
                using (SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(SQL, conn)))
                {
                    dt = new DataTable("tbl");
                    da.Fill(dt);
                }
            }

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim dt As DataTable
            Dim SQL As [String] = "SELECT P.ID ID, P.Name Name, P.Description Description, B.ID BrandID,B.Name Brand" + " FROM Product P, Brand B" + " WHERE P.BrandID=B.ID"

            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
                conn.Open()
                Using da As New SqlDataAdapter(New SqlCommand(SQL, conn))
                    dt = New DataTable("tbl")
                    da.Fill(dt)
                End Using
            End Using

            GridView1.DataSource = dt
            GridView1.DataBind()
        End If
    End Sub

The best practice is to wrap up all code under “using statement“. If you look at the code you will find that i have wrapped up all code under connection object as well as sql command. Keep in mind that when corresponding “using” statement reached at the end then asp.net automatically clear all variables immediately within the scope. You do not need to dispose those manually. Such as here i don’t close the connection, sqlcommand. But keep in mind to wrap up the connection object within “Using” statement. This is my message to you.

Download Code Example C#        Download Code Example VB.Net

Leave a Reply

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

AlphaOmega Captcha Classica  –  Enter Security Code