C# and Ajax

WTF is Ajax?

Ajax is actually an acronym for Asyncronous Javascript and XML, but in recent times Ajax has become the defacto name for a method to transfer data seemlessly between a client and server (potentially multiple servers) without effecting the user experience.

Originally it was designed as a collection of technologies used in a manner which made client/server communication smoother from the users perspective. It was a solution to the constant 'post back' effect which plagued all systems prior to Ajax's intervention.

Based on the page life cycle and the stateless nature of HTTP, every interaction with a web server required that all session information either be carried with the client or persisted on the server (typically in the form of a session).

Page Life Cycle

In order to understand Ajax and Postbacks, you need to understand why it happens in the first place. With a fat client, the application is loaded into memory and is able to be connected to its resources until the time the user closes the application (or windows shits itself and closes it for you). If you wanted to use the application again you simply loaded it back into memory and the application regained its access to its resources once again.

A web page is different because of its disconnected nature. A web page simply is a content returned from a web server, rendered within a web browser. This is the typical page life cycle, please note however there are more steps to it, this is just the basic overview necessary to understand the cycle.

1. Client makes a request for a page in a browser (or other User Agent)
2. User Agent (typically the browser) will resolve the URL to an IP address using DNS
3. UA will attempt a connection to the resolved IP on port 80 (unless the URL is specified with another port in the format of http://somedomain.com:1234 <- 1234 is the port that the UA will attempt a connection on)
4. If the connection is successful the UA typically issues a series of HTTP commands. Most likely the HTTP GET command followed by the requested path is issued to the web server (ie. http://www.someplace.com/this/is/a/path.html, resoves www.someplace.com to an IP and then when the conncetion is made a GET /this/is/a/path.html is issued to the web server)
5. If the server is able to map the 'requested path' to a resource on the server, it will dump whatever the resource outputs without any change, directly back to the UA
6. If the server is unable to map the 'requested path' to a resource on the server, it will dump a default set of content and issue an HTTP Error code (typically an HTTP/404 error)
7. Once the server has finished the dump, the connection is closed and the UA is left to render the content in whatever manner it is able to (typically a CONTENT-TYPE is also passed back with the response so the UA knows how to render the content but that is not the servers responsibility, its the resources responsibility to specify it)

When a user submits data on a form that has just been served to them, the ACTION will determine what resource on the server to call and the page life cycle starts again.

The rendering process of the UA (typically) involves a screen clearing method designed to empty all the elements from the Document Object Model (DOM). The new data is then parsed (if it's HTML) into a DOM Tree and rendered accordingly.

Javascript and its role in the process

When a page is rendered to the browser, a Javascript Object Model is made available to execute anything contained within the <SCRIPT> tags in the content.

The script itself can be passed with the content to the UA or the content itself can 'load' external javascripts by use of the <SCRIPT SRC=''> tag. This will 'post-load' javascript files and then execute them when they have been loaded.

The Javascript Object Model is then responsible for creating certain objects (actually its the browser, but that's a bit beyond the scope of this tute, just pretend its JS's responsibility). One such object is the XMLHttpRequest (xhr) Object. Pre 3rd generation browsers (FF 1, IE 5.5, etc) did not have this object created automatically. Though it can be instantiated if necessary, the 'security' of the browser would sometimes prevent it, so you have to accept that pre 3rd gen browsers will not handle Ajax properly.

The xhr has the ability to open a second connection to anywhere (typically the same server due to the Cross-Domain Scripting restrictions most browsers have in place but it doesn't have to), send and receive data all without the user seeing a thing! Sweet as man!

With this opens a realm of new possible communication methods and as such, we can exploit them to make the user experience that much better. Please note however, the same amounts of time to process of page (ie. just say it takes 5 seconds to load the initial page because of server load) will also apply to Ajax requests. So please don't think that Ajax is going to perform BETTER than postbacks, Ajax takes advantage of the fact that LESS data needs to be transmitted 'over the wire' than a standard page does but the PROCESSING time will always be equivalent to the initial render.

Ajax Performance Comparisons

In order to understand why some people toute Ajax as a performance king, we need to understand where the performance benefits and costs are. Here's a break down on the performance details for both postback and Ajax requests.


Initial Page Load
    100% data
    100% processing

Ajax Request made
    Small amount of data sent to make the request
    100% processing
    Potentially small amount of data sent to fulfil the request

Postback request
    Small amount of data sent to make the requiest
    100% data
    100% processing

As you can see above, postbacks require that 100% of the data (ie. the HTML in the page, the DATA for the form fields, EVERYTHING) is sent to the client during the initial load. This also uses 100% of the processing time (that's not CPU time, that's the amount of time used by the server to server the request) to build the page.

Ajax requests typically use small amounts of data to make the request, utilise all the processing time and TYPICALLY send back a small set of data that is needed to fulfil the request.

The postback will typically send the same small amount of data to make the initial request but then take all the time and data required during the initial page load to fulfil the request.

So from the processing time perspective there is no difference between postbacks and Ajax request. From the data perspective there is potentially a MASSIVE difference, and I'll explain why in the next section.

Ajax's defacto response format

XML is a document format that allows for metadata driven documentation. With XML you are able to send through your data along with metadata to assist with its processing. Processing of data is very important as you typically don't want to just dump data to the client (I say typically as sometimes there is no need to process that data and you really do just want to dump it to the client!).

Most 4th gen browsers support the XML document object in its entirety and provides an excellent XPath engine to allow quick and easy object lookups, but for me personally, I would never send XML over the wire for the following reasons:

1. XML requires structure and this structure costs bytes!
2. XML does have excellent support for parsing client side but its still cumbersome

Although Ajax really does stand to include XML, there is another data format that serves the same purpose, but its smaller, sexier and oh, did I say sexier?

Ajax's step child, JSON
JSON really isn't a format in as much it is the default way that Javascript has rendered its own object notation for like, well forever actually!

JSON stands for JavaScript Object Notation and truly is a data format that is native to Javascript. Let's check out a comparison between XML and JSON for representing the exact same data structure.

XML
<?xml version="1.0" encoding="utf-8" ?>
<root>
    <child id="child1" isLeaf="false">
        <grandChild id="gc1" isLeaf="true" />
        <grandChild id="gc2" isLeaf="true" />
    </child>
</root>

Grand total: 191 bytes

JSON
[{
    id: 'child1'
    , isLeaf: false
    , children: [{
        id: 'gc1'
        , isLeaf: true
    }, {
        id: 'gc2'
        , isLeaf: true
    }]
}];

Grand total: 136 bytes

This is a simple example but already there is a 28% saving on data that is being transmitted, plus, the JSON data is ALREADY in a format ready to be used by Javascript!

JSON is fast becoming the defacto data format for Ajax, and with almost every Ajax library out there supporting it fully, the distribution and acceptance of JSON is world wide, therefore Ajax might soon be renamed to the less catchy Ajaj :P

Scenario
Ok so let's see this in action. This scenario is an ecommerce application that has an order form that calculates the total cost of something based upon an 'secret' markup value based on the product code. Here's the details.

Retailer A wants to sell two products to the public. Product 1 has a 10% markup on it and Product 2 has a 25% markup on it. The retailer does NOT want the public to be able to know how much the markup OR the price is and so needs a way to conceal the values. Since Javascript is sent to the client to render, including the markup in the Javascript would fail as the user would only need to View Source to see the individual markups and prices.

Retailer A wants the users to simply select the product and enter a quantity and the server will return the sub total amount for the user.

Details
We're going to provide two inputs, a Product Code and Quantity Input plus a 'Calculate Total' button. Once pressed we are going to send the product code and quantity to the server via Ajax, let it retrieve the product from a database, get the price, markup and quantity, do the math and return it to the client so render the 'Product Total' value on the screen for the client.

We have a few files to look at so firstly, I'll give you the lay of the land.

Site Map
App_Code
    PriceCalculationService.cs
App_Data
Default.aspx
jquery-1.4.2.min.js
main.js
PriceCalculationService.asmx
web.config

PriceCalculationService
This is a standard C# web service file. In an ASP.Net project, the App_Code folder contains all the C# code files as source code and prevents their download by the webserver. Therefore this web service is split into two files, the ASMX and the CS file. The ASMX file is the entry point (ie. the thing you actually reference with your Ajax request) and the CS file contains the actual business logic. We want the public to see the ASMX but don't want them to see the CS file so anything in the App_Code folder automatically fails when its is requested. In fact, we only need it during the build which happens outside of the webservers domain so it's all good.

JQuery
Currently using 1.4.2.min in this project but the $.ajax() method should be available in all future versions (don't hold me to that :P)

web.config
Standard file, just ignore it in this tute.

main.js
This is the primary client side business logic file. I tend to separate out the Javascript from the ASPX files for various good reasons :)

Starting the Process
Ok so we've requested the ASPX file and it's rendered a BEAUTIFULLY boring HTML form with two input fields and a button. Sweet! Let's check out Default.aspx

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>AJAX Tutorial</title>
    <script type="text/javascript" src="jquery-1.4.2.min.js"></script>
    <script type="text/javascript" src="json2.js"></script>
    <script type="text/javascript" src="main.js"></script>
</head>
<body>
    <h1>Product Order Form</h1>
    <form>
        <table border="0">
            <tr>
                <td>
                    Product Code (1 or 2):
                </td>
                <td>
                    <input type="text" id="pcode" />
                </td>
            </tr>
            <tr>
                <td>
                    Qty:
                </td>
                <td>
                    <input type="text" id="qty" />
                </td>
            </tr>
            <tr>
                <td>
                    Product Total:
                </td>
                <td>
                    <span id="productTotal">$0.00</span>
                </td>
            </tr>
            <tr>
                <td colspan="2" style="text-align: right;">
                    <input type="button" value="Calculate Total" onclick="calcTotal();" />
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

Here we have pcode and qty as the inputs and productTotal as the SPAN that will hold our output. Clicking on the button will fire the calcTotal() Javascript function.

Pretty stock standard HTML here. Let's check out the Javascript!

// main.js - Created by Bradley J. Gibby q:)
// Version 1.0 - 2010-08-06

function calcTotal() {
    var pcode = $('#pcode');
    var qty = $('#qty');

    $.ajax({
        type: 'POST',
        url: 'PriceCalculationService.asmx/CalculatePrice',
        data: '{"productCode":"' + pcode.val() + '","qty":"' + qty.val() + '"}',
        contentType: 'application/json; charset=utf-8',
        dataType: 'json',
        success: function(msg) {
            var results = eval('(' + msg.d + ')');
            $('#productTotal').html('$' + results.total);
        }
    });
}

Ok simple one function Javascript file...So what's it doing?

    var pcode = $('#pcode');
    var qty = $('#qty');

I personally HATE it when devs do this but for brevity I've put it here. Why do I hate it? Well look at it this way. This is what happens in memory when you do it this way.

1. jQuery traverses the entire DOM to do the lookup for the selector #pcode
2. A jQuery object is created, memory allocated
3. The jQuery object is populated with the hundreds of properties associated with the INPUT tag #pcode
4. The jQuery object is returned
5. A memory allocation for the newly created jQuery object occurs
6. The jQuery object is stored within the memory location (variable) called pcode

Versus doing it this way in version 1.1 (don't use 1.1, it's here JUST as an example for this point)

// main.js - Created by Bradley J. Gibby q:)
// Version 1.1 - 2010-08-06 - Revised for optimsation

function calcTotal() {
    $.ajax({
        type: 'POST',
        url: 'PriceCalculationService.asmx/CalculatePrice',
        data: '{"productCode":"' + $('#pcode').val() + '","qty":"' + $('#qty').val() + '"}',
        contentType: 'application/json; charset=utf-8',
        dataType: 'json',
        success: function(msg) {
            var results = eval('(' + msg.d + ')');
            $('#productTotal').html('$' + results.total);
        }
    });
}

See here...

        data: '{"productCode":"' + $('#pcode').val() + '","qty":"' + $('#qty').val() + '"}',

The point I'm trying to make is this. If you plan to reuse the object that you get back from your call, store it in a variable instead of making calls to retrieve it OVER and OVER again, BUT, if you NEVER plan to use it more than once, don't allocate the memory to store it, just use it directly like I have above.

Anyhow, I digress... So what's it doing? :P

    var pcode = $('#pcode');
    var qty = $('#qty');

Essentially we're retrieving two jQuery objects for later use.

    $.ajax({

Here is the jQuery Ajax method. This method takes a 'JSON' object, actually it takes a Javascript object written in JavaScript Object Notation (ie. JSON). Let's look at the JSON object.

    {
        type: 'POST',
        url: 'PriceCalculationService.asmx/CalculatePrice',
        data: '{"productCode":"' + pcode.val() + '","qty":"' + qty.val() + '"}',
        contentType: 'application/json; charset=utf-8',
        dataType: 'json',
        success: function(msg) {
            var results = eval('(' + msg.d + ')');
            $('#productTotal').html('$' + results.total);
        }
    }
   
Type is set to 'POST' (trust me, for ASP.Net/C#/Webservices it NEEDS to be set to POST - Another LOOOOOOOOOOOOOOONG story for that one). Url is set to our entry point, in this case it's 'PriceCalculationService.asmx/CalculatePrice'. Notice that the URL itself isn't just 'PriceCalculationService.asmx', that's because the web service is expecting to be told what METHOD to call (and trust me, you're in for another LONG story as to why it's been done this way by Microsoft!).

Ok here's the kicker. The quirk with using something other than XML as output is the data and type fields. Since type must be set to POST, even when you're doing a GET request, you also need to send something along with the data field or else you'll potentially get a 'Length Required' error. This is because the POST HTTP command requires a Content-Length to be sent along with it. When making a GET request it's not necessary. To get around this, if we are making a GET request, simply pass '{}' to the data parameter.

This will force jQuery to set a Content-Length value and get past the Length Required error.

DataType should be set to 'json' as that's what you're expecting back.

ContentType is one of the gotchas as well. 'application/json; charset=utf-8' is the required ContentType and helps the process along by telling the WebSerivce what to expect your data to be in.

So let's look at how you pass data through to the web service.

        data: '{"productCode":"' + pcode.val() + '","qty":"' + qty.val() + '"}',

As you can see here, the data itself is a STRINGified version of an actual JSON object. We'll go into more detail about this shortly.

        success: function(msg) {
            var results = eval('(' + msg.d + ')');
            $('#productTotal').html('$' + results.total);
        }

And lastly the success function. The parameter 'msg' is a special ASP.Net JSON object that get's sent back with one single property, the property 'd'. Don't ask why, I can't seem to find out the answer, but it looks like that's how ASP.Net actually handles the JSON version of its output internally. In order to access we EVAL the 'msg.d' property and the results variable now becomes a JSON object ready to be 'probed'.

In this example the webservice is sending back a JSON object with a single property called 'total'. We then set the productTotal SPAN's innerHTML to the '$' + results.total and thus update the client without a postback! Mission accomplished.

The Web Service
Here's the back end web service code that performs the business logic and outputs the JSON object to the client server.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class PriceCalculationService : System.Web.Services.WebService {
    public PriceCalculationService() {
        //InitializeComponent();
    }
   
    [System.Web.Script.Services.ScriptMethod]
    [WebMethod]
    public string CalculatePrice(int productCode, int qty) {
        double markup = 0;
        double price = 0;
       
        switch(productCode) {
            case 1:
                markup = 1.1;
                price = 15 * qty;
                break;
           
            case 2:
                markup = 1.25;
                price = 25 * qty;
                break;
               
            default:
                markup = 0;
                price = 0;
                break;
        }
       
        return "{'total':'" + (price * markup) + "'}";
    }
}

There are two things of note on this before going any further. Firstly the webservice itself MUST have the Script Service attribute (and Script Method attribute) applied to it so that the output can be done correctly.

The CalculatePrice method can be figured out for yourself. Typically you'd have this connect to whatever data source you needed to do the product lookup, then retrieve the appropriate prices and markups, do the math and output the results.

Let's look at the following line:

        return "{'total':'" + (price * markup) + "'}";

At the moment we are outputting a string, in the JSON format, quoted correctly but in essence it's just a string (which matches the public string CalculatePrice signature).

You CAN use a JSONObjectSerialiser if you wish but in this example we're only interested in simple.

The JSON object contains just one property called total with the appropriately calculated total.

Conclusion
Well taking it right back to the beginning, the basic flow works out as:

Page Load -> User does something requesting a partial page update -> Javascript prepares an Ajax request and sends it -> Server receives request, processes it and prepares a JSON response then sends it -> Javascript receives the response, converts it to a real JSON object and uses the object to change something on the client WITHOUT the need to update the entire bloody page!

Database work within C#

An application will almost certainly hold data within a data store of some kind. This data store typically is a database of some sort.

The database is designed to efficiently store on disk, data that is stored within the data store.

The database will provide a driver that is responsible for communications between applications and the data store.

The database type defines how that data is segmented. In our example the data store will be a database with a type of Relational (ie. RDBMS).

The RDBMS defines what objects exist within the database. In most cases, the primary objects stored within a database are tables. Other objects are store procedures, user defined functions, user defined views, system objects, etc.

The database will contain settings at a global level which usually define methods of access to its data. Primarily a data store needs to authenticate users and as such, a connection string must be passed to a data store with valid credentials. The data store is then responsible for authenticating the credentials and validating whether the user defined in the credentials has access to the underlying catalogue.

In an RDBMS (such as SQL Server or MySQL), the database engine defines a catalogue as a storage mechanism that holds data and metadata.

The data typically is the table row data that we need to access. The metadata typically is the name of the table, max number of rows, column schema, etc.

Within each catalogue, an 'engine' can be defined. A table engine is a mechanism that controls 'how' the data is actually persisted, and subsequently, how the data is actually retrieved. The engine is only responsible for encoding/decoding data directly off of the filesystem.

When accessing the data, a method to efficiently query, structure and modify that data is needed. As such, multiple languages have been established to perform such functions on the data retrieved by the table engine. In our case, SQL (more accurately, ANSI-SQL) provides a mechanism for translating human written commands into memory operations that produce an output that the developer requires.

SQL contains three portions, the Data Definition Language (DDL), the Data Control Language (DCL) and the Query Language itself. The DDL is responsible for managing tables and index structures. The DCL is responsible for managing authentication. The Query Language is responsible for retrieval of data or its manipulation.

DDL commands typically range in the CREATE TABLE, DROP TABLE arena. DCL commands typically range in the CREATE USER, GRANT ALL ON range. Query Language typically range in the SELECT, INSERT range.

All three command types can be executed within C# through the use of a SqlCommand object.

Ok, let's look at the .Net class libraries responsible for data access.

System.Data
System.Data.Sql
System.Data.SqlClient
These three libraries are the base libraries for using a database within a .Net application. System.Data provides top level classes resposible for accessing data across all database manufacturers (ie. Microsoft, MySQL, Oracle, etc.). System.Data.Sql provides Microsoft SQL Server specific objects that tie in to MSSQL (this is a very small class). System.Data.SqlClient have the Microsoft specific versions of the System.Data classes.

Since each manufacturer must implement most of the System.Data interface, the lessons learned here SHOULD be transferable to ALL manufacturers (ie. you use a SqlConnection object to make a connection to a MSSQL database, you would then use a MySqlConnection object to connect to a MySQL database).

The typical process for a database application is as follows:

1. Load configuraion from somewhere (typically app.config/web.config/xml config file)
2. Create a new SqlConnection object
3. Set the connectionString property (or pass it in with the constructor on step 2) of the SqlConnection object
4. Call the Open() method on the SqlConnection object. This will set the SqlConnection objects State property to a ConnectionState object representing whatever state the database reports based on the connectionString provided
5. Test the State for ConnectionState.Open. If the State is Open then proceed otherwise handle the error
6. Create a SqlCommand object
7. Set the CommandText of the SqlCommand object to the SQL you wish to execute (or you could pass it in to the constructor in step 6)
8. If you are running a SELECT (or anything that returns a result) then you will typically call the ExecuteReader() method. This will execute the command and return whatever results you are expecting into a SqlDataReader object ready for examination, otherwise if you are running a command that does not return a result (like INSERT, UPDATE or DELETE whereby the only results returned are the amount of rows affected), then you will typically call the ExecuteNonQuery() method
9. The ExecuteReader() method will return a SqlDataReader which typically is iterated via the Read() method. The Read() method will return true whilst there is data to read and false when you have read all the data. A SqlDataReader is typically a 'forward only' object in that, you can only read the data once then it is removed from memory. These are very fast and efficient objects that are commonly used to read data rather sequentially rather than randomly access the data. A DataSet object is appropriate if you wish to retain the data after its has been read
10. The SqlDataReader has an index property called this[string] (or this[int]). This indexer can be used to pull data from the reader if you know the column name otherwise you will retrieve the data via the GetString(int), GetInt32(int), etc. methods
11. You will then be finished with the reader and will then call Close() on the reader object (otherwise the resource will be empty but still remain in memory)
12. When you are finished executing your commands you will then need to close the Database object by calling the Close() method on the SqlConnection object. This will release the resource and allow another user to connect to the database (remember, the database server only has a limited number of connections it can have opened at any one time, be respectful and close your connection when you don't need them. You can alway re-open it if you must)

Wow...what a lot of crap going on and this was just the 'brief' version! Let's examine what's going on in more detail.
 

Load configuraion from somewhere (typically app.config/web.config/xml config file)

Depending on your application, it usually is smarter to allow your configuration settings to be accessed, outside of your application, otherwise you would have to recompile your application every time a database server is renamed, moved, credentials are changed, and so on.

Typically an app.config or web.config file is used to store the connectionString details. Whilst this is an awesome thing to do, you serverly compromise your database server as you are storing credentials IN PLAIN TEXT!

For a greater detailed review of encrypting your connection strings, see http://ondotnet.com/pub/a/dotnet/2005/02/15/encryptingconnstring.html (yes yes I totally get that this is FAR from a perfect solution, but obfuscating a password is almost always better than a clear text version, even if it's piss easy to crack!)

Create a new SqlConnection object

What is a SqlConnection object? Well it's a container for all the underlying methods that the database driver exposes. In fact, it wraps up all the crap that each database driver requires from a communications point of view that you don't need/have to worry about. So thankfully, the SqlConnection object is the primary method of communicating to and from a database server.

For every database server you need to connect to (and trust me, sometimes you need to connect to more than one to get all the data necessary to perform your action), you will need a seperate SqlConnection object.

This object holds the connectionString details required to authenticate you against that server. By calling the Open() method you are in fact calling a whole bunch of DCL commands necessary to authenticate you against Microsoft SQL Server. Sweet!

This object then needs to have its State property checked. This holds the state the database returns after the credentials have been checked. Typically the state will be Open or Closed. There are a few others like Broken, Connecting, Fetching, etc. but you typically will never use them.

If the State is Open then you have a green light to proceed. This simply means though, you have permission to connect to the server. This DOES NOT necessarily mean that you have permission to CRUD on the tables you need. This is a seperate level of permissions, one that needs to be granted on your users behalf for each bloody table.

Typically the DCL GRANT ALL is executed for a user which means that for every table, the user will have XYZ access and you don't need to do it for every single table, it's done in one hit, though after it's been done you can selectively remove access to certain tables or remove INSERT/UPDATE/DELETE access to tables leaving SELECT access only (effectively giving READ-ONLY access) but that is a DCL discussion for another time :)
 

Set the connectionString property (or pass it in with the constructor on step 2) of the SqlConnection object

Typically the connection string is passed in with the constructor of the SqlConnection object but you can choose to create the new object and then set the ConnectionString property. This is personal preference. :)

If you forget what the layout of a connection string should be, visit http://www.connectionstrings.com/
 

Call the Open() method on the SqlConnection object
I spoke about this just a few paragraphs ago. By calling the Open() method you are asking the SqlConnection object to issue the required Microsoft SQL Server Authentication DCL commands on your behalf. The server will return a response and from that response, the .Net framework will set the SqlConnection State property accordingly.

By checking the State property, you can know for certain whether you have a successful connection to the database or not.

Please note however, each connection has an overhead of time, memory and network resources associated to it. If the database is on another machine then you have network latency as well. Opening the connection means that the resources are locked to you until you call the Close() method on the SqlConnection object. Until then, no other person is able to use those resources on the server.

Since server resources are usually limited (approx. 150 simultaneous connections), keeping connections opened longer than necessary is considered bad practice and will typically get DBA's pissed off with you :)

Test the State for ConnectionState.Open. If the State is Open then proceed otherwise handle the error
Again, already covered this previously. The primary reason for checking the State property is to know before executing SQL commands, whether you have a server listening to you or not.

You COULD try to open the database connection and execute the SQL within a TRY/CATCH statement and listen for a whole bunch of Exceptions but this is poor practice and defeats the purpose of the State property altogether.

Debugging the reasons why the connection Open() call fails is a WHOLE other can of worms and requires a bit more knowledge on the SqlException Error Codes that Microsoft SQL Server return and therefore is beyond the scope of this tutorial.

Create a SqlCommand object
Ok, we've got our credentials, made our connection, connected successfully and now what?

Typically all DDL commands are handled through the SqlCommand object. The purpose of this object is to translate your SQL commands into the appropriate DDL commands necessary to execute your query (and handle the method of preparing that data for use later on).

The first thing you'll notice is that a SqlCommand object ALWAYS requires a SqlConnection object. Without it, the command has no where to execute against and therefore is a dud object. So typically a SqlCommand object constructor will take a SqlConnection object, though can can set a single command object to handle ALL your SQL command requests and set the Connection property.

'What do you mean by set a single command object?'

Well there is no real need to create multiple SqlCommand objects in order to execute multiple SQL commands. Each SqlCommand object uses resources and memory so reducing the number of SqlCommand objects will optimise your application. Though it is not necessary to use just one, it's typically a good idea. I'll leave that up to your personal judgement.

'How would you use just one command object to execute multiple commands tho?'

Good question. Let's take a look at some code (FINALLY!).

// Create SqlConnection object and pass connection string into constructor
SqlConnection con = new SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword");

// Attempt a connection
con.Open();

// Test whether the connection was successful or not
if (con.State == ConnectionState.Open) {
    // Create the single SqlCommand object that we will re-use
    SqlCommand com = new SqlCommand();
   
    // Set the connection property to the newly opened SqlConnection object
    com.Connection = con;
   
    // Set the command text of the SqlCommand object to the first SQL command to execute
    com.CommandText = "SELECT id, user, password FROM users;";
   
    // Execute the command and return the data into a SqlDataReader object
    SqlDataReader reader = com.ExecuteReader();
   
    // Do something more useful with the data than I have done in this example
    while (reader.Read()) {
        int id;
        string username, password;
       
        if (!reader.IsDBNull(0)) {
            id = reader.GetInt32(0);
        } else {
            id = -1;
        }
       
        if (!reader.IsDBNull(1)) {
            username = reader.GetString(1);
        } else {
            username = "Unknown";
        }
       
        if (!reader.IsDBNull(2)) {
            password = reader.GetString(2);
        } else {
            password = "";
        }
    }
   
    // Release the reader object but don't destroy it. This frees the object for future re-use
    reader.Close();
   
    // Set the command text of the SqlCommand object to the next SQL command to execute
    com.CommandText = "SELECT id, user, password FROM users WHERE id > 4;";
   
    // Populate the existing reader with the data from the next SQL command
    reader = com.ExecuteReader();

    // Again, do something more useful with the data than I have done in this example
    while (reader.Read()) {
        int id;
        string username, password;

        if (!reader.IsDBNull(0)) {
            id = reader.GetInt32(0);
        } else {
            id = -1;
        }

        if (!reader.IsDBNull(1)) {
            username = reader.GetString(1);
        } else {
            username = "Unknown";
        }

        if (!reader.IsDBNull(2)) {
            password = reader.GetString(2);
        } else {
            password = "";
        }
    }
   
    // Release the reader resources
    reader.Close();
   
    // Release the resources (being respectful :P)
    con.Close();
} else {
    // Handle when connection to database fails for whatever reason
}

All the other items have now been addressed within the example code so I don't need to explain them further.

Let's look at the code in a bit more detail and explain what's going on.

// Create SqlConnection object and pass connection string into constructor
SqlConnection con = new SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword");

// Attempt a connection
con.Open();

// Test whether the connection was successful or not
if (con.State == ConnectionState.Open) {

You should recognise what's going on here. Creation of a new SqlConnection object, population of the connectionString, attempting to connect and verifying whether that connection was successful.

Assume it is we then...

    // Create the single SqlCommand object that we will re-use
    SqlCommand com = new SqlCommand();
   
    // Set the connection property to the newly opened SqlConnection object
    com.Connection = con;

Here we are creating the SqlCommand object that we are going to re-use and associating the SqlConnection object with it. This is required because the SqlCommand object creates the DDL commands directly and does not know what connection to send them down without being told specifically which SqlConnection object it must use.

The conncetion object remains the same unless you specify a different SqlConnection object.

    // Set the command text of the SqlCommand object to the first SQL command to execute
    com.CommandText = "SELECT id, user, password FROM users;";

Here we are specifying the first command we wish to execute by setting the SQL to the CommandText property. When calling Stored Procedures, we have to adjust our approach slightly but we'll go into that later in the tutorial (sorry guys, didn't get around to it this time!)

    // Execute the command and return the data into a SqlDataReader object
    SqlDataReader reader = com.ExecuteReader();

At this point we still haven't done anything other than set up the SqlCommand object. This line actually forms the DDL's, and sends them through the associated SqlConnection object. The SqlConnection object then prepares the commands by breaking them down into network packets and physically sends the data across the network.

When received the server will execute the commands and based on whether you screwed the SQL up or not, will return an answer (whether it's 'you're shit out of luck' or, 'here's the data you requested' is another story).

You will be given a SqlDataReader object. Whether it's filled with any data or not is beside the point, you will be given the SqlDataReader object period.

    // Do something more useful with the data than I have done in this example
    while (reader.Read()) {

We then call the Read() method on the reader. This will tell us whether there are any more 'unread' rows of data queued in the reader for us to have or not. So we use a while() statement to handle the looping as we get the free bail out when there's nothing more to read and the continuance if there is stuff to read all in one command.

        int id;
        string username, password;
       
        if (!reader.IsDBNull(0)) {
            id = reader.GetInt32(0);
        } else {
            id = -1;
        }
       
        if (!reader.IsDBNull(1)) {
            username = reader.GetString(1);
        } else {
            username = "Unknown";
        }
       
        if (!reader.IsDBNull(2)) {
            password = reader.GetString(2);
        } else {
            password = "";
        }

Ok, pretty useless stuff here but it does explain how to get access to the data within the reader on a 'row-by-row' basis. Because we are accessing the data directly, an intimate knowledge of the data schema is required. Otherwise we would have to examine the data type and row count of each item and decide how to handle the data which requires a lot more code. So for this example, let's assume that we know we are operating on a database table called users and the schema looks like this:

Users
-----
id int not null auto_increment primary key,
user varchar(100) not null,
password varchar(100) not null

We are also going to assume that there are 10 user records of various data in the table.

Since C# is a strongly typed language we must be specific with our data mappings. We can use the Sql Types themselves instead of the int and string types but in this example we're going to stick with C# data types.

So we define our expected data types:

        int id;
        string username, password;

In this example, these are just placeholders for values that are returned from the database whereas IRL (in real life) you would typically create an object of type User and store the values in the properties of the object, but that's another tutorial.

Since some fields can be null, it's typically safest to verify that the column you are going to retrieve is not null before trying to access it because the database is happy to give you a null value, but C# is NOT happy to accept a null value when it's expected a string, int or other data type. So for type safety we need to check if the column in the current reader row is null or not.

        if (!reader.IsDBNull(0)) {
            id = reader.GetInt32(0);
        } else {
            id = -1;
        }

Here we ask the reader to tell us whether column zero IsDBNull or not. This returns a true or false result. If it's true, you know you have to handle the field when you are presented with a null value. Since the code is written, 'if reader.IsDBNull(column zero) IS NOT true then id = reader.GetInt32(column zero) otherwise id = -1'.

My logic simply expects to handle the TRUE state first (hence the ! at the beginning of the reader.IsDBNull(0)) then the FALSE state second.

This is repeated for each of the columns in the row except the data type that is being accessed is different. This allows us to handle the data type mapping from the database to C#. This is the trade off between having strongly typed objects accessing data that can have a varied number of types. From an optimisation POV, this process CAN be improved but for the sake of brevity, we'll demand that the dev know the structure of the table intimately (in all honesty, a dev that doesn't know the table structure is going to have more problems that this!).

    // Release the reader object but don't destroy it. This frees the object for future re-use
    reader.Close();

Ok so we've looped through the reader and we've processed every row that was received by the reader, now what? Well in order to re-use the reader we must Close() it first. This releases the resource and allows new data to be added to its internal data structures. If we don't close the reader first we'll receive an Exception which will kill your app :)

    // Set the command text of the SqlCommand object to the next SQL command to execute
    com.CommandText = "SELECT id, user, password FROM users WHERE id > 4;";
   
    // Populate the existing reader with the data from the next SQL command
    reader = com.ExecuteReader();

    // Again, do something more useful with the data than I have done in this example
    while (reader.Read()) {
        int id;
        string username, password;

        if (!reader.IsDBNull(0)) {
            id = reader.GetInt32(0);
        } else {
            id = -1;
        }

        if (!reader.IsDBNull(1)) {
            username = reader.GetString(1);
        } else {
            username = "Unknown";
        }

        if (!reader.IsDBNull(2)) {
            password = reader.GetString(2);
        } else {
            password = "";
        }
    }
   
    // Release the reader resources
    reader.Close();

This code basically does the same thing as the first SQL request except it issues a different command. By re-using the SQLCommand and SQLDataReader objects we can reduce the overhead consumed by the application whilst improving performance by not needing to do all the memory allocation and object initialisation that is required just to get these objects ready to use.

    // Release the resources (being respectful :P)
    con.Close();

Once all work has been done on the SqlConnection object we need to Close() it. This will release the network connection to the server and subsequently put the SqlConnection object (and all child objects such as the SqlCommand) in a state ready for the Garbage Collector to clean up after you.

Conclusion

At this point we've gone through the entire process of connecting, quering and doing something with the data from a data connection.

Examine the source code associated with this tutorial and step through the TutorialCode entry point. When you have finished, comment the TutorialCode entry point and uncomment the BetterCode entry point and step through this one. From a logical, pragmatic and maintainable POV the BetterCode entry point represents a better way of handling the repetitivness of code, and allows to easier maintainability in the future. Something you will appreciate when you go back to your own source code a week from now and think, WTF was I thinking when I wrote this?!?!