Posted on 12/22/2020 10:48:59 AM by Admin

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 them in the application

This is the normal flow that 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 so that cannot 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. Always remember, that connection is very precious if you will open 100 connection and doesn't close them then you will be out of resource and your application won't be functioning. So, if you open the connection then it is necessary to close it.

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 the Close() method.

SqlConnection Signature


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

SqlConnection Constructors

Constructors Description
SqlConnection() It is used to initializes a new instance of the SqlConnection class.
SqlConnection(String) It is used to initialize a new instance of the SqlConnection class and takes connection string as an argument.
SqlConnection(String, SqlCredential) It is used to initialize a new instance of the SqlConnection class that takes two parameters. First is connection string and second is sql credentials.

SqlConnection Methods

Method Description
BeginTransaction() It is used to start a database transaction.
ChangeDatabase(String) It is used to change the current database for an open SqlConnection..
ChangePassword(String, String) It changes the SQL Server password for the user indicated in the connection string.
Close() It is used to close the connection to the database.
CreateCommand() It enlists in the specified transaction as a distributed transaction.
GetSchema() It returns schema information for the data source of this SqlConnection.
Open() It is used to open a database connection.
It resets all values if statistics gathering is enabled.

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 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=SampleDB; integrated security=SSPI")


  2. Using the parameter-less constructor of SqConnection Class.
Here, we first create an instance of SqlConnection class using the parameterless constructor and then set the ConnectionString property of the connection object.


   SqlConnection connection = new SqlConnection();
   connection.ConnectionString = "data source=.; database=SampleDB; 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 "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