Accessing SQL Server Data in C# with ADO.NET

[OmMuruga]

The following is my collection of notes regarding how to get your data from SQL Server to your C# program. I got tired of doing all the guesswork after reading several tutorials, so once I figured it out from everything I looked at it, I put together this simple tutorial for my classmates. This tutorial assumes you know something about SQL as well as C#.

ADO.NET is the next iteration of ADO/OLEDB and refers to a set of .NET namespaces that control data manipulation and database connectivity. Note that everything below applies to ASP.NET programs that use a C# codebehind file just as well as a regular C# file.

Setting up your SQL Server

I recommend using "Mixed Mode" as opposed to Windows Mode--this will allow data access from non-Windows programs and users. I call this "Password Mode" over "Authentication" mode. From my experience, you can only set your server to Mixed Mode upon installing/creating a new server.

Using ADO.NET in code files

Since I'm just using SQL, I need access to the following namespaces (sometimes just one, sometimes all)

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



System.Data has a lot of necessary enum properties such as ConnectionState.
System.Data.SqlClient has the necessary classes such as SqlConnection, SqlCommand, etc.
System.Data.SqlTypes has the class representations of the SQL datatypes, and is particularly useful for the common SQL types that are not CLS-compliant (such as SqlMoney). All SQL datatypes can be cast into common datatypes readily available to the C# programmer. Note that all SQL datatypes are originally returned as objects.

Creating connections

All methods that utilize ADO.NET must have the following:

    * an open connection to the database
    * a source from which data will be sent
    * a method of translating/storing the data
    * a source to which data will be sent

All connections require a connection string, either coded into the program (static) or requested and formed at runtime (dynamic). A basic connection string for Mixed Mode is:

private string connectionString = @"Initial Catalog=<database name>;" +
     @"Data Source=<server name>;" +
     @"User ID=<username>;" +
     @"Password=<password>";



Note that each field within the string is separated by a semi-colon but does not end with one.

A connection is created by one of the two following codes:

    SqlConnection connection = new SqlConnection(connectionString);



or

    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = connectionString;



Writing

    connection.Open();



is all you need to do open the connection. Depending on how you set up the server,

    connection.Open();



can throw any number of exceptions if the source was unavailable or a password was entered incorrectly. However, you need only catch one exception type: SqlException. The properties of the SqlException (State, Source, Message, etc.) will tell you what happened.

Using the connection

Once opened, we can send commands to the database to retrieve and manipulate data. Not surprisingly, there are many ways of performing these commands.

First we set up our command. The syntax for setting up a command is the same regardless of whether the command is one of the four basic (SELECT, INSERT, UPDATE, DELETE) or more involved (CREATE).

    //I want to look at all items that have been made in my game.
    SqlCommand command = new SqlCommand("SELECT * from Item where GameID = "+my_gameid,connection);

    //Same idea, except I have a Stored Procedure on my server
    SqlCommand command = new SqlCommand("getItemsInGame("+my_gameid+")",connection);



Note that I include the connection at the end of these strings. This is not necessary, as long as I define the Connection property before I execute the command. An optional third field for the constructor is a Transaction, but I haven't done anything with that yet.

Once the command has been set up, I'm ready to execute it. For INSERT,UPDATE, and DELETE, the code is simply:

    command.ExecuteNonQuery();



This method returns an integer for the number of rows affected, which I've never really found useful but you might.

For SELECT commands, we will want to get the data so we can view it. If we just called ExecuteNonQuery, it would return the number of rows selected--and the data is wasted (don't tie up the line doing this...you get your data faster if you use a "SELECT COUNT" command). Instead, we have to set up something so that we can view the data. For this, we can use an SqlDataReader:

    SqlDataReader reader = command.ExecuteReader();



Getting the data of each row returned can be done using a while loop:

    while (reader.Read()) // Read() returns true if their was a row in the table to read
        //Examine the data:  see below.



Using a reader, there are three ways to access the data in each column of the row.

My favorite is:

    item.Name = (string)reader["Name"];



Indexing on the column name returns an object, which must be cast into whatever data type. This is great if you know the order of the schema of the table (the names of the columns and the type of data stored in it). If you don't, you can use

    reader.GetSchemaTable();



to find out.

Also available, but less useful in my opinion, are:

    item.Name = (string)reader[0];



and

    item.Name = reader.GetString(0);



These are less useful to me because they assume you know the column number for your data, which, if you use joins or select from multiple tables, can be difficult to know. Also, you would have to change the indices if you ever changed the number or order of columns in your database.

Calling

reader.Get[datatype](index);



can be tricky as well, as some datatypes are not readily castable into the common datatypes. You may end up doing something like

    item.Value = reader.GetDouble(3).ToDecimal();



when something easier like

    item.Value = reader.GetDecimal(3);



or even

    item.Value = (decimal)reader[3];



exists for your use. It depends on how you want to store your data in your application vs. in your database. Maybe you prefer it to stay in a form that the database will understand:

    //public DateTime CreationDate is a property
    item.CreationDate = reader.GetDateTime(5);



or, you like something that's easier for your application to use:

    //public string CreationDate is a property
    item.CreationDate = reader.GetDateTime(5).ToShortDateString();



After you're done using the SqlDataReader, you want to close it--otherwise, future commands on the same connection cannot be executed:

    reader.Close();



Another way to manipulate your data is to use a DataSet. A DataSet is a class that stores data as if it were a database. Using a DataSet and a SqlDataAdaptor, you can create a SqlCommandBuilder that implements the four manipulation statements on the DataSet. However, I didn't find DataSets to be that useful unless I was using a DataGrid in my GUI (a Datagrid allows for direct viewing/manipulation of data within a table format).

Upon exiting your program, you want to close your connection for completeness:

    connection.Close();