The .NET framework has a few different automated methods for managing connections to SQL Server and other databases. The framework has been around since 2003 and various versions have introduced new, improved data management tools while maintaining the old ones for backward compatibility.

It’s still possible, however to retrieve data from SQL Server and other database systems with just a few lines of code and it’s important to know how to do this to truly understand how the communication between SQL Server and a language like C# actually works.

There are three classes that you will need to be familiar with in order to do a basic retrieval of a SQL data set and they’re shown in the graphic below. Pulling data out of SQL Server really is very much like drawing water from a well and through your house’s pipes and remembering this comparison might help you to understand the process a bit better.

You have the following objects:

• Database – This can be in any version of SQL Server and holds the data on the server.
• SqlConnection – This .NET class provides a connection to a specific database on a specific SQL server. It uses a string to indicate these settings and can be opened and closed as needed.
• SqlDataAdapter – The Data Adapter provides the methods for actually moving data back and forth between SQL Server over a SqlConnection object. It can work with multiple SQL commands in order to SELECT, UPDATE, INSERT and DELETE data.
• DataTable – While the other two classes are specific to SQL Server, the DataTable is a generic table class that can hold data from a variety of sources. It’s used here to hold the data retrieved by the SqlDataAdapter.

Let’s look at some basic code:

 using System.Data;
 using System.Data.SqlClient;
 
 string connString = "Server=COMEAU-WIN7;Database=AdventureWorks2012;Trusted_Connection=True;";

 SqlConnection sqlConn = new SqlConnection(connString);
 SqlDataAdapter sqlDA = new SqlDataAdapter("spCurrentEmployees", sqlConn);
 DataTable dtEmployees = new DataTable();
 sqlDA.Fill(dtEmployees);
 dgEmployees.DataSource = dtEmployees;

This is a very basic retrieval of a set of data through a stored procedure. The two Using statements at the top indicate the namespaces the objects belong to.

1. The connection string is defined first. This is the specification that .NET needs in order to know where to connect.  The basic connection string shown has three parts – the server, database and security type.  This connection string uses a trusted connection which means that it’s using Windows Authentication.
2. The SqlConnection is then declared and instantiated using the connection string. This establishes a closed connection to the server.
3. The SqlDataAdapter is then declared and instantiated, in this case with the name of a stored procedure as a command and the connection that was defined in the last line.
4. A new, empty DataTable is declared and instantiated.
5. The Fill method from the SqlDataAdapter is then used to fill the DataTable with the data it retrieved from SQL Server.
6. The last line refers to a DataGrid object on a Windows form. The DataGrid’s DataSource property is set to the DataTable that we just filled.

ConnectionStrings.com provides the formats for connection strings used to connect to various database systems with options for different circumstances.

Of course, this is just for a stored procedure that returns a table of data.  If the stored procedure uses input parameters or returns output variables, you would need to declare a SqlCommand object with one or more SqlParameter objects.  You still need to carry out the tasks above first, though, so this is a good starting point for moving data around.