ADO.NET

Connection string

To connect to any kind of database from within the .NET Framework application we need something called the connection string. The connection string, as its name suggests, is a simple string that contains information required for the ADO.NET to connect to a database. It is nothing else than a formalized way to provide the server name, database name and other connection information to successfully connect and authenticate to the database server in a simple string.

The connection string can contain many different attributes. There are also many types of connection strings depending on the technology that you plan to use. To connect and work with our database from ADO.NET we will be using this connection string:

string connectionString =
   "Server=ookj3vjulf.database.windows.net;" +
   "Database=MyCSharpDotNet;" +
   "User Id=dbuser;" +
   "Password=Databa5e;";

As you can see, there is no magic to the connection string. In a form suitable for the ADO.NET technology it presents some basic connection information, including credentials to authenticate.

Using statement

Before we get into our first real example of getting the data from the database, we should first understand one new language construct. This construct, called “using” (not to be confused with the “using” used for namespaces), allows to easily declare and initialize a new object while making sure that when we are done with the object at the end all of its memory intensive resources will be released right away.

An SqlConnection object is one of those objects that can use the “using” statement. The object itself contains one memory intensive resource and that is the connection to the database. We need this resource to be in the memory only for the duration necessary to communicate with the database. Once we get all the data from the database that we need, the connection itself can be closed and all of its internal data can be released.

The following example shows how to use the ”using” statement for the new SqlConnection object. We can access the object from inside the “using” block. Once we get outside the using block, the connection will automatically be closed and its relevant internal memory will be released:

using (SqlConnection sqlConnection =
    new SqlConnection(connectionString))
{
    …
}

Getting table data

Now that we understand the concept of a connection string and know how to work the “using” statement, let us jump directly to our first example:

string connectionString =
    "Server=ookj3vjulf.database.windows.net;" +
    "Database=MyCSharpDotNet;" +
    "User Id=dbuser;" +
    "Password=Databa5e;";

string queryString =
    "SELECT * " +
        "FROM Orders";

using (SqlConnection sqlConnection =
    new SqlConnection(connectionString))
{
    SqlCommand sqlCommand =
        new SqlCommand(queryString, sqlConnection);

    sqlConnection.Open();

    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

    int rowIndex = 0;

    while (sqlDataReader.Read())
    {
        Console.WriteLine("Row {0}", rowIndex);

        for (int columnIndex = 0;
            columnIndex < sqlDataReader.FieldCount;
            columnIndex++)
        {
            Console.Write("Column {0}: ", columnIndex);
            Console.WriteLine(sqlDataReader[columnIndex]);
        }

        rowIndex++;

        Console.WriteLine();
    }

    sqlDataReader.Close();
}

Console.ReadLine();

This example will connect to the database using our connection string and it will write all the rows of our Orders stable out on the console.

The amount of data returned is of course driven by the SQL statement contained in our queryString variable.

Once we create a new SqlConnection object and prepare our command with help of a new SqlCommand object, we can go ahead and open the connection to the SQL server. This is done by calling the Open method of the SqlConnection object itself. Should there be any issues with our connection string or the database could not be reached for some reason, this is the line where an exception would be thrown. In a real-world application it might be good to enclose the call of this method into a try/catch block and provide a way to handle possible connection problems.

If everything succeeds, the connection gets opened. Now we need to execute the command. As the command itself is a SELECT statement, we expect data to be returned in a form of a table. To best handle this type of incoming data, we should use an SqlDataReader object. We can get the command to be executed and SqlDataReader returned to us in a single call using the ExecuteReader method.

Now, when we have the SqlDataReader object created and connected to the data that are coming from the database, we can use its Read method. Every call to the Read method will load another row from the database and return true. If there is no more rows available in the database, the Read method will return false. We can easily use a “while” cycle to iterate through all the rows.

When the data row is loaded inside the SqlDataReader object, we can see how many columns (or fields) the row actually has by accessing its FieldCount property. We will use this property to iterate through all of the columns using a “for” cycle. To get the data for a specific column we can use an indexer of the SqlDataReader object. We use this indexer in our example to write out every column to the console.

When you run the example, you should end up with a list of all rows from the Orders stable. This is what you get for the very last row:

Row 829
Column 0: 11077
Column 1: RATTC
Column 2: 1
Column 3: 6. 5. 1998 0:00:00
Column 4: 3. 6. 1998 0:00:00
Column 5:
Column 6: 2
Column 7: 8,5300
Column 8: Rattlesnake Canyon Grocery
Column 9: 2817 Milton Dr.
Column 10: Albuquerque
Column 11: NM
Column 12: 87110
Column 13: USA

Working with column names

In our previous example we needed to know an index of a column to access its data. SqlDataReader object however provides two other methods that make the use of column names possible.

The first method, GetName, can be used to obtain a column name based on its index:

string columnName = sqlDataReader.GetName(columnIndex);

The second method, GetOrdinal, works the other way around. It will tell you the index of a column based on its name:

int customerIDIndex = sqlDataReader.GetOrdinal(customerIDColumnName);

The following example shows how these methods can be used:

string connectionString =
    "Server=ookj3vjulf.database.windows.net;" +
    "Database=MyCSharpDotNet;" +
    "User Id=dbuser;" +
    "Password=Databa5e;";

string queryString =
    "SELECT TOP 1 * " +
        "FROM Orders";

using (SqlConnection sqlConnection =
    new SqlConnection(connectionString))
{
    SqlCommand sqlCommand =
        new SqlCommand(queryString, sqlConnection);

    sqlConnection.Open();

    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

    if (sqlDataReader.Read())
    {
        // Showing the column names
        for (int columnIndex = 0;
            columnIndex < sqlDataReader.FieldCount;
            columnIndex++)
        {
            string columnName = sqlDataReader.GetName(columnIndex);

            Console.Write("Column index {0}, ", columnIndex);
            Console.Write("name {0}: ", columnName);
            Console.WriteLine(sqlDataReader[columnIndex]);
        }

        Console.WriteLine();

        // Getting a value based on a column name
        string customerIDColumnName = "CustomerID";
                    
        int customerIDIndex = 
            sqlDataReader.GetOrdinal(customerIDColumnName);

        Console.Write("{0}: ", customerIDColumnName);
        Console.WriteLine(sqlDataReader[customerIDIndex]);
    }
    else
    {
        Console.WriteLine("No rows returned!");
    }

    sqlDataReader.Close();
}

Console.ReadLine();

And it would produce an output similar to this:

Column index 0, name OrderID: 10248
Column index 1, name CustomerID: VINET
Column index 2, name EmployeeID: 5
Column index 3, name OrderDate: 4. 7. 1996 0:00:00
Column index 4, name RequiredDate: 1. 8. 1996 0:00:00
Column index 5, name ShippedDate: 16. 7. 1996 0:00:00
Column index 6, name ShipVia: 3
Column index 7, name Freight: 32,3800
Column index 8, name ShipName: Vins et alcools Chevalier
Column index 9, name ShipAddress: 59 rue de l'Abbaye
Column index 10, name ShipCity: Reims
Column index 11, name ShipRegion:
Column index 12, name ShipPostalCode: 51100
Column index 13, name ShipCountry: France

CustomerID: VINET

You can see we were able to get the column names as well as a separate value for column CustomerID based on the name of the column. Note that in the example above, in addition to substituting the “while” cycle with an “if” statement, we have also changed the SQL query a bit. It now returns only one row thanks to the introduction of the “TOP 1” statement.

Using specific .NET types

If you are following these examples closely, you have noticed that every column from the database that arrived through the SqlDataReader is of an object type. This was perfectly sufficient when our only goal was to write out the column content the output. We would appreciate getting the correct .NET Framework datatype though if we were planning on doing some extra work on these variables.

To get a specific datatype out of the SqlDataReader object, we can use its GetXyz methods. To get a correctly typed string, for example, we could use the GetString method. To get an integer, we would use the GetInt32 method. All of these methods take an index of the column as a parameter.

The following example shows the use of these methods:

string connectionString =
    "Server=ookj3vjulf.database.windows.net;" +
    "Database=MyCSharpDotNet;" +
    "User Id=dbuser;" +
    "Password=Databa5e;";

string queryString =
    "SELECT TOP 1 * " +
        "FROM Orders " +
        "WHERE OrderID = '11006'";

using (SqlConnection sqlConnection =
    new SqlConnection(connectionString))
{
    SqlCommand sqlCommand =
        new SqlCommand(queryString, sqlConnection);

    sqlConnection.Open();

    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

    if (sqlDataReader.Read())
    {
        int orderID = sqlDataReader.GetInt32(0);
        string customerID = sqlDataReader.GetString(1);
        int employeeID = sqlDataReader.GetInt32(2);
        DateTime orderDate = sqlDataReader.GetDateTime(3);
        DateTime requiredDate = sqlDataReader.GetDateTime(4);
        DateTime shippedDate = sqlDataReader.GetDateTime(5);
        int shipVia = sqlDataReader.GetInt32(6);
        decimal freight = sqlDataReader.GetDecimal(7);
        string shipName = sqlDataReader.GetString(8);
        string shipAddress = sqlDataReader.GetString(9);
        string shipCity = sqlDataReader.GetString(10);
        string shipRegion = sqlDataReader.GetString(11);
        string shipPostalCode = sqlDataReader.GetString(12);
        string shipCountry = sqlDataReader.GetString(13);

        Console.WriteLine("OrderID        : {0}", orderID);
        Console.WriteLine("CustomerID     : {0}", customerID);
        Console.WriteLine("EmployeeID     : {0}", employeeID);
        Console.WriteLine("OrderDate      : {0}", orderDate);
        Console.WriteLine("RequiredDate   : {0}", requiredDate);
        Console.WriteLine("ShippedDate    : {0}", shippedDate);
        Console.WriteLine("ShipVia        : {0}", shipVia);
        Console.WriteLine("Freight        : {0}", freight);
        Console.WriteLine("ShipName       : {0}", shipName);
        Console.WriteLine("ShipAddress    : {0}", shipAddress);
        Console.WriteLine("ShipCity       : {0}", shipCity);
        Console.WriteLine("ShipRegion     : {0}", shipRegion);
        Console.WriteLine("ShipPostalCode : {0}", shipPostalCode);
        Console.WriteLine("ShipCountry    : {0}", shipCountry);
    }
    else
    {
        Console.WriteLine("No rows returned!");
    }

    sqlDataReader.Close();
}

Console.ReadLine();

Dealing with Nullable types

A keen eye might have noticed that in the previous example we have not only used the specifically typed Get methods, we have also changed the query string a bit. It now includes a WHERE clause to get a specific order by its OrderID.

This has been done so that the example would work. In the order specified, there are no NULL values in any of the columns. The table however supports NULLs and if we were to use the code above for a row that actually had NULL in any of its columns, such as a row with an OrderID ‘11008’, we would get an exception. You can try this yourselves just by changing the WHERE clause.

The reason for the exception is that we cannot use the typed Get methods to get a content of a column containing NULL. There is a special method of the SqlDataReader object, called IsDbNull, that we need to use to check if a particular column contains NULL. We need to make sure to check this for any column that has NULL values enabled in the database.

The following example shows how to use this IsDbNull method to get a Nullable .NET Framework datatype:

string connectionString =
    "Server=ookj3vjulf.database.windows.net;" +
    "Database=MyCSharpDotNet;" +
    "User Id=dbuser;" +
    "Password=Databa5e;";

string queryString =
    "SELECT TOP 1 * " +
        "FROM Orders " +
        "WHERE OrderID = '11008'";

using (SqlConnection sqlConnection =
    new SqlConnection(connectionString))
{
    SqlCommand sqlCommand =
        new SqlCommand(queryString, sqlConnection);

    sqlConnection.Open();

    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

    if (sqlDataReader.Read())
    {
        int orderID = sqlDataReader.GetInt32(0);
        string customerID = sqlDataReader.GetString(1);
        int employeeID = sqlDataReader.GetInt32(2);
        DateTime orderDate = sqlDataReader.GetDateTime(3);
        DateTime requiredDate = sqlDataReader.GetDateTime(4);

        DateTime? shippedDate =
            sqlDataReader.IsDBNull(5) ?
            (DateTime?)null :
            sqlDataReader.GetDateTime(5);

        int shipVia = sqlDataReader.GetInt32(6);
        decimal freight = sqlDataReader.GetDecimal(7);
        string shipName = sqlDataReader.GetString(8);
        string shipAddress = sqlDataReader.GetString(9);
        string shipCity = sqlDataReader.GetString(10);

        string shipRegion =
                sqlDataReader.IsDBNull(11) ?
                null :
                sqlDataReader.GetString(11);

        string shipPostalCode = sqlDataReader.GetString(12);
        string shipCountry = sqlDataReader.GetString(13);

        Console.WriteLine("OrderID        : {0}", orderID);
        Console.WriteLine("CustomerID     : {0}", customerID);
        Console.WriteLine("EmployeeID     : {0}", employeeID);
        Console.WriteLine("OrderDate      : {0}", orderDate);
        Console.WriteLine("RequiredDate   : {0}", requiredDate);

        Console.WriteLine(
            "ShippedDate    : {0}",
            shippedDate.HasValue ?
                shippedDate.Value.ToString() :
                "NULL");

        Console.WriteLine("ShipVia        : {0}", shipVia);
        Console.WriteLine("Freight        : {0}", freight);
        Console.WriteLine("ShipName       : {0}", shipName);
        Console.WriteLine("ShipAddress    : {0}", shipAddress);
        Console.WriteLine("ShipCity       : {0}", shipCity);

        Console.WriteLine(
            "ShipRegion     : {0}",
            shipRegion != null ?
                shipRegion :
                "NULL");

        Console.WriteLine("ShipPostalCode : {0}", shipPostalCode);
        Console.WriteLine("ShipCountry    : {0}", shipCountry);
    }
    else
    {
        Console.WriteLine("No rows returned!");
    }

    sqlDataReader.Close();
}

Console.ReadLine();

The example also slightly modified how the data is written out on the console so that for the columns containing NULL we will get a word “NULL”. This, of course, is done only for the demonstration purposes – once you have the data in a Nullable object, you can work with it however you like.

Also note please that the example above expects NULL values in columns ShippedDate and ShipRegion only. These are in fact the columns where there is a NULL value in the database for this particular order. If we were to make sure the example works on all orders, we would need to modify it to handle NULL values in all of the columns supporting it.

As you can see now, the code that needs to be used with ADO.NET can be rather lengthy when you need to account for specific datatypes, including the Nullable ones. There is a way, of course, to centralize the necessary code and make it a bit easier to use. One possible solution is creating an object wrapper around the SqlDataReader class and an example of its implementation is offered in the final example of this topic.

Adding parameters

Now that we can get and process all the data returned from the SQL query, we should also learn how to provide a way to filter the results.

If you are fluent with the SQL language, you might be tempted to just hack into the query string and filter the results by adding a where clause:

string queryString =
    "SELECT TOP 1 * " +
        "FROM Orders " +
        "WHERE CustomerID = '" + customerID + "'";

The customer ID variable in the example above would be a string variable that you simply put inside the query string. This will definitely work but if the content of customerID string comes directly from the user of your application, by using this approach you have opened yourself to something known as SQL injection attacks.

By cleverly manipulating the content of customerID variable, the user might easily be able to do just about anything with your database - including retrieving, updating or deleting any data they choose.

To prevent this from happening you should never use such a concatenation of strings when customizing the query string. You need to use parameters instead.

To introduce a parameter into the query string, you simply type it in prefixed by an at-sign (@). This is, for example, how you would make the example above right:

string queryString =
    "SELECT TOP 1 * " +
        "FROM Orders " +
        "WHERE CustomerID = @CustomerID";

Now when the parameter has been put inside the query string, we need to tell ADO.NET the value of the parameter. This is done through a Parameters property of the command object. The following snippet shows how to add the new CustomerID parameter.

SqlParameter sqlParameter = sqlCommand.CreateParameter();
sqlParameter.ParameterName = "CustomerID";
sqlParameter.Value = customerID;
sqlCommand.Parameters.Add(sqlParameter);

Always make sure that the “ParameterName” matches the name of the parameter placeholder, excluding the at-sign (@).

The following is a complete example that shows how to get the customerID string from the user input and then safely use it to influence the resulting set of data through the use of parameters:

while (true)
{
    Console.WriteLine("Enter the CustomerID ('GODOS', 'SUPRD', ...) " +
        "(leave empty to exit):");

    string customerID = Console.ReadLine();

    if (String.IsNullOrWhiteSpace(customerID))
        break;

    Console.WriteLine();

    string connectionString =
        "Server=ookj3vjulf.database.windows.net;" +
        "Database=MyCSharpDotNet;" +
        "User Id=dbuser;" +
        "Password=Databa5e;";

    string queryString =
        "SELECT TOP 1 * " +
            "FROM Orders " +
            "WHERE CustomerID = @CustomerID";

    using (SqlConnection sqlConnection =
        new SqlConnection(connectionString))
    {
        SqlCommand sqlCommand =
            new SqlCommand(queryString, sqlConnection);

        SqlParameter sqlParameter = sqlCommand.CreateParameter();
        sqlParameter.ParameterName = "CustomerID";
        sqlParameter.Value = customerID;
        sqlCommand.Parameters.Add(sqlParameter);

        sqlConnection.Open();

        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

        if (sqlDataReader.Read())
        {
            Console.WriteLine("OrderID    : {0}",
                sqlDataReader.GetInt32(0));

            Console.WriteLine("CustomerID : {0}",
                sqlDataReader.GetString(1));
        }
        else
        {
            Console.WriteLine("No rows returned!");
        }

        sqlDataReader.Close();
    }

    Console.WriteLine();
    Console.WriteLine();
}

Inserting, updating and deleting data

We now have a pretty solid knowledge about querying the database and getting the data. There are however other operations than SELECT, we should learn. You should be able to use INSERT, UPDATE and DELETE statements as well.

The way how these statements are used is very similar to previous examples. The only notable difference is that we do not expect a table data to be returned from these statements. The only thing we might expect is the number of rows affected by this operation. This is why we do not use the ExecuteReader and SqlDataReader anymore.

For these types of commands we will use an ExecuteNonQuery method. The method itself just simply executes the SQL command and returns an integer representing the number of rows affected. The following example shows how a new row could be inserted into a WritableRows table:

string connectionString =
    "Server=ookj3vjulf.database.windows.net;" +
    "Database=MyCSharpDotNet;" +
    "User Id=dbuser;" +
    "Password=Databa5e;";

string queryString =
    "INSERT INTO WritableRows " +
        "VALUES (NEWID(), GETDATE(), 'Test row', 'Nobody')";

using (SqlConnection sqlConnection =
    new SqlConnection(connectionString))
{
    SqlCommand sqlCommand =
        new SqlCommand(queryString, sqlConnection);

    sqlConnection.Open();

    int rowsAffected = sqlCommand.ExecuteNonQuery();

    Console.WriteLine("Finished, {0} rows affected.", rowsAffected);
}

Console.ReadLine();

To use update or delete, simply change the query string text. To delete the row that we have just created, use this query string for example:

string queryString =
    "DELETE FROM WritableRows " +
        "WHERE Nickname = 'Nobody'";

Please note that you can and should use SqlParameters for these types of SQL statements as well. You can do this exactly the same way as we have seen in an earlier example.

Executing stored procedures

We now know how to execute all of the basic SQL commands – the SELECT, INSERT, UPDATE and DELETE. The last example will show how you can execute a stored procedure.

A stored procedure is an SQL script stored on the server itself. The script can contain SELECTs, INSERTs, UPDATEs, DELETEs, as well as other SQL commands supported by the SQL server. There are basically two reasons why you might want to use stored procedures.

The first reason is when a single operation that you would need to perform from a client application actually requires more than one SQL command to be sent to the server. Consider a situation, for example, where you want to make a bank transfer. The bank transfer essentially means that a specific amount of money is first taken out of one account and then added to another account. This, if done by standard SQL commands, would require two of them. First a new row would be inserted to indicate debiting the source account and then another row would be inserted to indicate crediting the target account. This could be done in a faster way by calling one already prepared procedure stored on the SQL server. The procedure could do all of the required operations just by getting three simple parameters – the account to be debited, the account to be credited and the amount itself. Using this approach, as opposed to firing up multiple INSERTs directly from the client, you get a much better performance. Especially if the example is much more complex and more than two rows need to be changed like this. Also, by having your SQL code on the SQL server inside a stored procedure, the SQL server can pre-compile it and make other optimizations so that when you need to call this procedure, the SQL server can get you even better performance.

The second common reason why stored procedures are used is to provide additional security. If you are a database administrator and you give developers the right to directly execute SQL commands, such as INSERTs, UPDATEs and DELETEs, you are making yourself vulnerable to mistakes these developers might make in their applications. Such mistakes can vary from inserting duplicate records, updating records inconsistently with other updates as well as accidentally deleting the content of an entire table - perhaps just because the developer forgot to include the WHERE clause to their DELETE statement. If you code all the operations that the client application requires into separate stored procedures, you do not need to give your developer rights to fire individual SQL commands and can only give them the right to execute stored procedures. This way you, as the database administrator, have a full control over what your client applications can and cannot do with your data.

Our sample database happens to include one stored procedure just to demonstrate how you can call them using ADO.NET. The stored procedure is called SalesByCategory and as its name already suggests, it returns a total revenue by every product for a given product category. The procedure takes two parameters – the name of the category and a year for which the total revenue should be calculated. It then returns the list of products, together with their sales.

Calling a stored procedure from ADO.NET is as easy as configuring the SqlCommand object to execute a stored procedure and provide it with its name. Getting a result from the stored procedure call is similar to that of an SELECT statement as the data returned is also in a form of a table.

The following example shows how to call the stored procedure in ADO.NET and write out all the data returned to a console:

string connectionString =
    "Server=ookj3vjulf.database.windows.net;" +
    "Database=MyCSharpDotNet;" +
    "User Id=dbuser;" +
    "Password=Databa5e;";

string storedProcedure = "SalesByCategory";

using (SqlConnection sqlConnection =
    new SqlConnection(connectionString))
{
    SqlCommand sqlCommand =
        new SqlCommand(storedProcedure, sqlConnection);

    sqlCommand.CommandType = CommandType.StoredProcedure;

    SqlParameter categoryNameSqlParameter =
        sqlCommand.CreateParameter();
    categoryNameSqlParameter.ParameterName = "CategoryName";
    categoryNameSqlParameter.Value = "Beverages";
    sqlCommand.Parameters.Add(categoryNameSqlParameter);

    SqlParameter orderYearSqlParameter =
        sqlCommand.CreateParameter();
    orderYearSqlParameter.ParameterName = "OrdYear";
    orderYearSqlParameter.Value = "1996";
    sqlCommand.Parameters.Add(orderYearSqlParameter);

    sqlConnection.Open();

    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

    int rowIndex = 0;

    while (sqlDataReader.Read())
    {
        Console.WriteLine("Row {0}", rowIndex);

        for (int columnIndex = 0;
            columnIndex < sqlDataReader.FieldCount;
            columnIndex++)
        {
            Console.Write("Column {0}: ", columnIndex);
            Console.WriteLine(sqlDataReader[columnIndex]);
        }

        rowIndex++;

        Console.WriteLine();
    }

    sqlDataReader.Close();
}

Console.ReadLine();

Notice that the only “text” we needed to give the SqlCommand was the name of the stored procedure. Once the SqlCommand is configured to work with stored procedures by having its CommandType property set to StoredProcedure, there is nothing more we need to do.

Our stored procedure takes two parameters. You can see that passing these parameters to the stored procedure is identical to working with parameters in previous examples. The only difference is that now you cannot choose the parameter names freely – you have to respect the names that were used in the stored procedure definition on the SQL server.

Sample application

There is an ADO.NET sample application available to show you most of the ADO.NET functionality discussed above. It even contains a couple more new features – especially the new EnhancedDataReader class that provides a way for easier handling of column names and Nullable datatypes.

Continue to: Entity Framework

Go up to: Databases


Should you have any questions or found a mistake that needs correcting, feel free to send an email to: info [at] mycsharp [dot] net


Advertisements :