ADO.NET SqlConnection Class

If a .NET application (Web, Desktop, Console, etc.) has to retrieve data, 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 SqlConnection serves the same purpose. It is used to establish an open connection to the SQL Server database.

It is a sealed class, which means it cannot be inherited.

One key point worth remembering is that connection is very precious. If you open hundreds of connections and don't close them, then you will run out of resources and your application won't function. So, if you open a connection, it is necessary to close it as well.

Now, we know that connections are limited and are very valuable, and connections must be closed properly for better performance and scalability. But the 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

Important SqlConnection Methods

To connect to the SQL Server database, or to establish a connection, you need to provide some information to the SqlConnection object; this information includes:

• Data Source Name
• Database Name and
• Credentials (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 simply specify a period (.) as the server name. If the server is on a network, use its name or IP address.

All this information is present in the connection string property of the database. The ConnectionString is a string composed of key-value pairs that contain 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 an 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
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 the SqlConnection class:

1. Using the parameterized constructor of the SqlConnection class, which takes a connection string as a parameter:

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

2. Using the parameter-less constructor of the SqlConnection class. Here, we first create an instance of the 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= Test; integrated security=True";

Now, it's time to take a full example of retrieving employees’ data from the Employee table and display it in a webform'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 using the 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 the Close() method, when using is used. The connection will be automatically closed for us.

There are 2 uses of a using statement in C# :

1. To import a namespace. Example: using System;
2. To close connections properly as shown in the example above.