C# MS SQL Connection

For a project I was working on I needed to connect to a MS SQL database.  I didn't need to do anything fancy, just connect and submit a bunch of queries.  Still though, this is the sort of thing I felt I should write down as I'm sure I'll need to do this again.

Fortunately Microsoft provided everything we need for this in .Net.  Mono is building an equivalent, but it doesn't currently have the entire feature set, pretty sure it has everything I needed though.  The project I was working on was specifically for a Windows environment so Mono didn't seem useful.

For this I used the SqlConnection class to handle my database operations.  It's ADO based, which is convenient since I've been using that in VBScript for nearly a decade now.  It's exclusively for Microsoft databases; so if you're using something else, like MySQL, you'll need to find a different library.

We'll start by added the System.Data.SqlClient to our code and create a SqlConnection variable.  The class will need a connection string.  This is a string that takes the form:

 user id=<LoginName>; password=<Password>; server=<Hostname>; database=<DatabaseName>; connection timeout=30  

Just replace the stuff in angle brackets with your actual connection information.  I tend to put this in a string variable for convenience.

Now that we have a connection string we give it to the constructor of the new SqlConnection variable.  The database connection won't be made though, this just gets it configured.  When the SqlConnection method Open() is called it will reach out to the database and become ready to submit your SQL queries.  All together this looks like:

1:   SqlConnection dbConn;   
2:   string strConnString;   
3:   strConnString = "user id=<LoginName>; password=<Password>; server=<Hostname>; database=<DatabaseName>; connection timeout=30";   
4:   dbConn = new SqlConnection(strConnString);   
5:   dbConn.Open();   

For the project I was doing all I needed to do was retrieve records from the database, which means using select queries.  To do that a SqlCommand object is needed to submit the query, and then a SqlDataReader object is needed to allow the data to be parsed.  Creating these objects looks like this:

1:  string strSQL;   
2:  SqlCommand odbCmd;   
3:  SqlDataReader odbRead;   
4:    
5:  strSQL = "SELECT * FROM DBTable";   
6:    
7:  odbCmd = new SqlCommand(strSQL, dbConn);   
8:  odbRead = odbCmd.ExecuteReader();   

At this point all the records returned from the query is accessible through the odbRead variable.  Now we need to determine all of the fields that were return and go through the records and pull out the data.  This takes a couple of loops to get through, something like this is what you need:

1:  string strName;  
2:  int iCtr;  
3:    
4:  if (odbRead.HasRows == true) { //Database records were returned  
5:      while (odbRead.Read() == true) { //Loop through all the records  
6:          for (iCtr = 0; iCtr < odbRead.FieldCount; iCtr++) {  
7:          //Loop through all the fields in the record  
8:          strName = odbRead.GetName(iCtr);  
9:    
10:          Console.WriteLine("Field:" + strName + " Has Value:" + odbRead[strName].ToString());  
11:          }  
12:      }  
13:  } else {  
14:      Console.WriteLine("No records were returned by the Select Query.");  
15:  }  

There's a lot going on there, lets try this line by line.

First we need to check if any rows matched our query.  Fortunately SqlDataReader object has just such a thing in the HasRows property.  It will be true if records were returned, false otherwise.

The SqlDataReader object only lets you read from one record at a time.  The Read() method loads the next record in the data set.  It will return true if there are more records, and false if its on the last one.  Line 5 sets up a while loop to go through each record that was returned by the SQL query.

In this case I'm assuming I don't know what fields were returned.  It's pretty common that queries will return specific fields or the table schema is known and there's no need to discover what values are in the record.  Whether you need it or not the FieldsCount property of SqlDataReader tells the number of fields returned, and I use it to control a for loop in line 6.

The GetName() method will give the name of a particular field, you can specify the field by a number.  You get data from the fields by using their name as an index so I'm saving that in a convenient variable in line 8.

Lastly the value of a particular field can be used as an index to the SqlDataReader class.  It returns the value in an Object class, which has a convenient ToString() method to use to put this value on the screen.  This is done on line 10.