C# SqlConnection Example

SqlConnection handles database connections. It initiates a connection to your SQL database. The SqlConnection class is best used in a 'using' resource acquisition statement and must have its Open method called as well, in most places where you want to query the database with SqlCommand.

Example

We emphasize the usage of SqlConnection in a 'using' resource acquisition statement. The SqlConnection has a constructor that requires a string reference pointing to the connection string character data. This connection string is often autogenerated for you by the dialogs in Visual Studio, and sometimes is provided by your hosting company or department. You must include the SqlConnection code before you can use the SqlCommand object pattern to perform a database query.

This C# program uses SqlConnection. It requires System.Data.SqlClient.

Program that uses SqlConnection [C#]

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
	//
	// First access the connection string, which may be autogenerated in Visual Studio for you.
	//
	string connectionString = ConsoleApplication1.Properties.Settings.Default.ConnectionString;
	//
	// In a using statement, acquire the SqlConnection as a resource.
	//
	using (SqlConnection con = new SqlConnection(connectionString))
	{
	    //
	    // Open the SqlConnection.
	    //
	    con.Open();
	    //
	    // The following code shows how you can use an SqlCommand based on the SqlConnection.
	    //
	    using (SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM Dogs1", con))
	    using (SqlDataReader reader = command.ExecuteReader())
	    {
		while (reader.Read())
		{
		    Console.WriteLine("{0} {1} {2}",
			reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
		}
	    }
	}
    }
}

Output

57 Koko Shar Pei
130 Fido Bullmastiff

Get started. This program will not work for you unless you change the connection string reference to point to the correct one in your environment. To create a connection string to a database, you can go to the Visual Studio Data menu and select Add New Data Source. Also, the program assumes the name of a SQL table that will not be present in most databases. You can change the SqlCommand code to match your database.

Using statement. The C# language provides a using-statement construct. The parameterized statement to the using statement is called the resource acquisition statement. The purpose of the using statement is to provide a simpler way to specify when the unmanaged resource is needed by your program, and when it is no longer needed. Internally, the language can transform the using statement into a try/finally statement that calls the Dispose method.

Open method call. The using statement creates a read-only variable in the scope of the using statement of type SqlConnection. You need to actually call the Open method on the SqlConnection instance before using it in an actual SqlCommand. The SqlConnection is passed as the parameter to the SqlCommand, which is an easy way to link the two together and specify that the SqlCommand "uses" the SqlConnection.

Tutorial

For the example in this article to work correctly, it would need to find the specified SQL table and the specified connection string must be correct. This site contains a more thorough overview and tutorial of SqlConnection and its supporting constructs that can help with this.

Using

In the annotated C# language specification, advice is given that you should use the 'using' statement when creating any object that implements the interface IDisposable. The justification for this is that even if the interface does nothing, it is safest to always call it if it exists. Many examples of SqlConnection and SqlCommand do not use the using statement reliably, including the one on MSDN.

Summary

We looked at database code and stressed the proper usage of the SqlConnection class and the using statement's resource acquisition pattern. The SqlConnection is required for correctly using other SQL objects such as SqlCommand and SqlDataReader. For this reason, it is a common object to encounter in data-oriented applications in the C# language and .NET Framework in general.