ADO.NET SqlConnection Class

If a .NET application (Web, Desktop, Console etc.) has to retrieve data, then the application needs to
    1. Connect to the Database
    2. Prepare an SQL Command
    3. Execute the Command
    4. Retrieve the results and display in the application

This is the normal flow which is used by any .Net application, not only to retrieve data but also to insert, update or Delete etc. As you can see,the very first step is to connect to the database. The Ado.Net Sql Connection serves the same purpose, It is used to establish an open connection to the SQL Server database.

It is a sealed class , it means it can not be inherited. SqlConnection class uses SqlDataAdapter and SqlCommand classes together to increase performance when connecting to a Microsoft SQL Server database, meaning, if you want to execute some command on the Sql Server database then you can either use SqlCommand or SqlDataAdapter.

One key point, worth remembering is , connection is very precious if you will open hundreds of connection and doesn't close them then you will be out of resource and your application won't be functioning. So , if you open a connection then it is necessary to close that as well.

Now we know ,Connections are limited and are very valuable and Connections must be closed properly, for better performance and scalability. But, Connection does not close implicitly even if it goes out of scope. Therefore, you must explicitly close the connection by calling Close() method.

SqlConnection Signature

public sealed class SqlConnection : System.Data.Common.DbConnection, ICloneable, 
 

SqlConnection Constructors

Important SqlConnection Methods

In order to connect to the SQL Server Database , or to establish a connection you need to provide some information to SQL Connection object, these information's are
    • Data Source Name
    • Database Name and
    • Credential (user id and Password) or you can also set Integrated Security="true" in case of windows login.

The "data source" is the name or IP Address of the SQL Server that we want to connect to. If you are working with a local instance of sql server, you can just specify DOT(.). If the server is on a network, then use Name or IP address.

All these information are present in connection string property of database. The ConnectionString is a string made up of Key/Value pairs that has the information required to create a connection object.

SqlConnection Example

Now, let's create an example that establishes a connection to the SQL Server. We have created a Employee database and will use it to connect. Execute the following SQL script using SQL Server Management Studio. The script creates:-
  1. Test Database.
  2. Employee table under Test Database. and
&emsp 3. Populate it with some data

Create Database Test
Go

Use Test
Go

CREATE TABLE [dbo].[Employee] (
    [Id]     INT          IDENTITY (1, 1) NOT NULL,
    [Name]   VARCHAR (50) NULL,
    [Salary] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC)
);
Go

SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (1, N'sachin', 100000)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (2, N'arjun', 23000)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (3, N'vikash', 30000)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (4, N'pocha', 43000)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (5, N'Nivedita', 24000)
SET IDENTITY_INSERT [dbo].[Employee] OFF
Go

There are 2 ways to create an instance of SqlConnection class ,
  1. Using the Parameterized constructor of Sql Connection Class which takes connection string as parameter

SqlConnection connection = new SqlConnection("data source=.; database= Test; integrated security=true");

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "data source=.; database= Test; integrated security=True";

Now , it's time to take a full example of retrieving employee's data from Employee table and display it in web form's Gridview control.

protected void Page_Load(object sender, EventArgs e)
{
    //Create the connection object
    SqlConnection con = new SqlConnection(""Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"");;
    try
    {
        // Pass the connection to the command object, so the command object knows on which
        // connection to execute the command
SqlCommand cmd = new SqlCommand("select * from Employee", con);
        // Open the connection. Otherwise you get a runtime error. An open connection is
        // required to execute the command
         con.Open();
    SqlDataReader rdr=cmd.ExecuteReader();

                    GridView1.DataSource = rdr;
            GridView1.DataBind();
    }
    catch (Exception ex)
    {
        // Handle Exceptions, if any
    }
    finally
    {
        // The finally block is guaranteed to execute even if there is an exception.
        //  This ensures connections are always properly closed.
        con.Close();
    }
}

Understand the above code :-
  1. first we are creating a connection by SqlConnection object.
  2. The created connection object is then passed to the command object so that the command object knows on which SQL server connection to execute this command.
  3.Then we are Executing the command, and setting the command results, as the data source for the gridview control.
  4. Then we have Called the DataBind() method and at last
  5. we Closed the connection in the finally block.

Note: Connections should be opened as late as possible, and should be closed as early as possible.

We can also use the "using" statement to properly close the connection as shown below.

 public partial class Home: System.Web.UI.Page
    {
        string cs = "Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";
        protected void Page_Load(object sender, EventArgs e)
        {
            BindGrid();
        }

        private void BindGrid()
        {
            using(SqlConnection con = new SqlConnection(cs))
            {
             SqlCommand cmd = new SqlCommand("select * from Employee", con);
            con.Open();
            SqlDataReader rdr=cmd.ExecuteReader();
            GridView1.DataSource = rdr;
            GridView1.DataBind();
            }
           
        }
    }

We don't have to explicitly call Close() method, when using is used. The connection will be automatically closed for us.

There are 2 uses of an using statement in C# :-
    1. To import a namespace. Example: using System;
    2. To close connections properly as shown in the example above