How to Select Against a MS-SQL Server Database in C#

November 6, 2010

One of the most-common tasks I find myself doing in C#, is retrieving data from a database. Here, I will quickly cover how to accomplish selecting against a Microsoft SQL Server database, specifically.

First, you should define your connection string within your project properties. Right click on your project in the Solution Explorer (from within Visual Studio) and select "Properties". Now, click on the "Settings" tab on the left. Click on the first text-well, and enter a name for your connection string. I'll call ours something simple like "SQLConn". Set it to a data type of "String" and set give it a scope of "Application". As for the value, your connection string should look something like this:

Data Source=MYDBSERVER,1234;User ID=kingRoland;Password=12345;Initial Catalog=atmoShieldDB

Note, that you can also specify an IP address for your data source. Additionally, you may need fully-qualify the name of your server (as in MYDBSERVER.planetdruidia.com), depending on the restrictions of your network. Also, if your database server makes use of the default MS SQL Server port (1433), then you do not need to specify it.

There is some speculation about whether or not to name your Initial Catalog in the connection string. Without it, you will need to specify your database along with your table name on all subsequent SQL statements. But really, that is a good practice to get into, anyway. If you are sure that you'll only need to access one particular database from your program, then feel free to specify it.

Once our connection string is set, then we're ready to start coding. This time around, we'll assume that we want to select every row in table "mytable" and send it to the STDOUT (standard output):

using System.Data.SqlClient;
using System.Data;
using System;

public static int Main(string[] args)
{
    string strSQL = "SELECT * FROM mytable ";
    string strField1 = "";
    string strField2 = "";

    SqlConnection objConn = new SqlConnection(Properties.Settings.Default.SQLConn);
    objConn.Open();
    SqlDataAdapter objDA = new SqlDataAdapter(strSQL,objConn);
    DataTable objDT = new DataTable();
    objDA.Fill(objDT);
    DataTableReader objDTReader = objDT.CreateDataReader();

    while (objDTReader.Read())
    {
        strField1 = objDTReader[0];
        strField2 = objDTReader[1];
        Console.WriteLine(strField1 + "-" + strField2);
    }
    objConn.Close();
}

First, we define the strings and the SQL Server connection objects that we're going to use. Get the connection string from the SQLConn app.config setting created above and be sure to open the connection. Next, we'll initialize a new SQLDataAdapter with the opened connection object and the SQL string. Next, define a generic DataTable, and "fill" it with the data from the data adapter. Finally, we initialize and create a DataTableReader from the DataTable, and interate through the reader (as long as it contains rows) outputting the field data to the STDOUT.

Now, let's say that we want to get a specific collection of rows back from our table. Here, we'll take a slightly different approach and use a SqlCommand object instead.

using System.Data.SqlClient;
using System.Data;
using System;

public static int Main(string[] args)
{
    string strSQL = "SELECT * FROM mytable WHERE username = @user ";
    string strField1 = "";
    string strField2 = "";

    SqlConnection objConn = new SqlConnection(Properties.Settings.Default.SQLConn);
    SqlDataReader objRdr = new SqlDataReader();
    objConn.Open();
    SqlCommand objCMD = new SqlCommand;
    objCMD.Connection = objConn;
    objCMD.CommandText = strSQL;
    objCMD.Parameters.Clear();
    objCMD.Parameters.AddWithValue("user","LoneStar");

    try
    {
        objRdr = objCMD.ExecuteReader();

        while (objRdr.Read())
        {
            strField1 = objRdr[0];
            strField2 = objRdr[1];
            Console.WriteLine(strField1 + "-" + strField2);
        }
    }
    catch (Exception e)
    {
        objRdr = null;
        Console.WriteLine(e.InnerException.ToString());
    }
    objConn.Close();
}

The main differences here, are that this query requires fewer intermediate objects to be created and it allows for the use of SQL parameters.

NOTES:

You should notice that I cleared my parameter list before adding the "user" parameter. While I don't really need to (the parameter list is empty by default), clearing the list prior to adding parameters is a good habit to get into.
Additionally, you should use SQL parameters for your WHERE clause anytime you're working with user input (which can be unpredictable).
You always want to have a "try/catch" around a SQLCommand call. In the event of a failure or a query that returns zero rows, the underlying objects won't be set to anything and all subsequent usage of them will cause a runtime error.

The SQLCommand object has several versions of the "Execute" function, but they are beyond the scope of this article. There you have it; returning a list of records from a MS-SQL Server Database with C#...easy-easy.

Aaron Ploetz