Using SQL Server with .NET and System.Data.SqlClient

Opening a connection to the SQL Server

First things first.  Before you do anything, you must create and open a connection to the SQL server that is going to be executing your query.  To do this, you need to instantiate a SqlConnection object and set its ConnectionString property (you may do so in the constructor, if you like).  ADO connection strings are documented a bit arcanely in the MSDN library, mainly due to their flexibility.  95% of the time you can get away with a simple connection string like this:

server=MyServer; database=MyDatabase; Integrated Security=SSPI

...or if you are using SQL security (bad! bad! bad!) instead of Windows Integrated security:

server=MyServer; database=MyDatabase; userid=MyUserID; password=MyPassword

Once you have created the SqlConnection, the next step is to open the connection.  To do so, just call the Open() method on your SqlConnection object:

SqlConnection conn = new SqlConnection("server=MyServer; database=MyDatabase; Integrated Security=SSPI");
conn.Open();

After you are finished with the connection, close it!  SQL connections are often limited in number by a license agreement, so you want to get in, get/send your data, and get out as soon as possible.

using System; using System.Data; using System.Data.SqlClient; namespace Pegasi { class SqlSample { [STAThread] public static void Main(string[] args) { SqlConnection conn = null; try { conn = new SqlConnection("server=mySqlServer;database=myDatabase;Integrated Security=SSPI"); conn.Open(); } catch(SqlException ex) { Console.Error.WriteLine(ex.Message + ex.StackTrace); // do not throw ex! this is important for preserving InnerException and stack trace data throw; } finally { if (null != conn) conn.Close(); } } } }

Creating a Stored Procedure SqlCommand

Now that you have an open connection to the server, you may execute SQL commands over that connection.  The object used for this is the SqlCommand.  In order to send a command, you must first tell the connection what type of command you are going to execute: a Stored Procedure, dynamic SQL (Text), or direct OLEDB table access.  For the purposes of this exercise, we will be dealing with Stored Procedures ("sprocs") and dynamic SQL ("text") only, as direct table access is only used the the SqlDataAdapter class.

Instantiate your SqlCommand object (see Opening a connection to the SQL Server above) and set the proper CommandType:

SqlCommand cmd = new SqlCommand("SprocName", conn);
cmd.CommandType = CommandType.StoredProcedure;

Most sprocs need at least one input parameter, and some require output parameters to return scalar data as well.  Parameters are stored in the SqlCommand's Parameters property.  Here are some examples of adding parameters:

int intParam = 1; float realParam = 1.0f; string varcharParam = "my string"; DateTime dateParam = DateTime.Now; cmd.Parameters.Add("@name", varcharParam); cmd.Parameters.Add("@count", intParam); cmd.Parameters.Add("@strength", realParam); cmd.Parameters.Add("@date", dateParam); SqlParameter spStatus = cmd.Parameters.Add("@retval", SqlDbType.Int); spStatus.Direction = ParameterDirection.Output;

Notice the spStatus parameter?  After you execute the command, you can obtain the output parameter from the sproc by getting the value of spStatus through its Value property.

Creating a Dynamic SQL ("Text") SqlCommand

Using a SqlCommand that uses dynamic SQL instead of a stored procedure consists of setting the CommandType to CommandType.Text and setting the Text property of the SqlCommand to the text of the SQL to execute:

SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.Text = "SELECT * FROM MyTable";

Executing queries that don't return a result set

If you need to execute a query that does not return a result set (or you want to ignore any returned data), use SqlCommand.ExecuteNonQuery(). Since it does not allocate resources for a reader, it's a bit more efficient, especially if the query returns a large result (although in that case you may want to use a different query...)

As one might expect, queries that do not return a result set are simple to execute. There is no result set to iterate over, so you just execute the command and optionally obtain a count on the number of rows affected.

// if your command is inserting or updating rows, it's often useful to // get the number of rows affected. ExecuteNonQuery() returns an Int32 // for that purpose. You don't *have* to get it, of course, if you don't care. int numRowsAffected = cmd.ExecuteNonQuery();

Retrieving data with a SqlDataReader

Now that the command's parameters have been set, you can execute it!  For commands that return one or more result set, you need to use a SqlDataReader to get the data.  You get the reader by calling SqlCommand.ExecuteReader();

SqlDataReader reader = cmd.ExecuteReader();

SqlDataReader works like a forward-only, non-caching row reader.  In other words, you get access to one row at a time, and you can only move forward.  Since the whole point is to get the data quickly and close the connection, this is exactly what you need.  Fortunately, the .NET Framework has dynamically-sized arrays available for use out-of-the-box.  In the 1.0 and 1.1 Frameworks, you have the System.Collections.ArrayList.  The 2.0 Framework adds generics support (similar to C++ templates), but that's out of scope for this sample.

A simple way to get the data:

SqlDataReader reader = cmd.ExecuteReader(); ArrayList list = new ArrayList(); while (reader.Read()) { object[] row = new object[reader.FieldCount]; reader.GetValues(row); list.Add(row); } reader.Close();

This is certainly simple, but if you know the data types ahead of time, you can explictly retrieve the fields directly into your custom objects:

using System; using System.Data; using System.Data.SqlClient; namespace Pegasi { class SqlSample { [STAThread] public static void Main(string[] args) { SqlConnection conn = null; SqlDataReader reader = null; ArrayList list = new ArrayList(); try { conn = new SqlConnection("server=mySqlServer;database=myDatabase;Integrated Security=SSPI"); conn.Open(); reader = cmd.ExecuteReader(); int ixName = reader.GetOrdinal("Name"); int ixCount = reader.GetOrdinal("Count"); int ixStrength = reader.GetOrdinal("Strength"); int ixDate = reader.GetOrdinal("Date"); while (reader.Read()) { MyObject obj = new MyObject(); obj.Name = reader.GetString(ixName); obj.Count = reader.GetInt32(ixCount); obj.Strength = reader.GetFloat(ixStrength); obj.Date = reader.GetDateTime(ixDate); list.Add(obj); } } catch(SqlException ex) { Console.Error.WriteLine(ex.Message + ex.StackTrace); throw; // do not throw ex! this is important for preserving InnerException and stack trace data } finally { if (null != reader) reader.Close(); if (null != conn) conn.Close(); } } } }