Using ADO.NET - Part I

In next few posts we will discuss ADO.NET object and how you can use it to connect to SQL Server database (you can connect to other databases as well, but here we will limit our discussion to SQL Server) and then work with the data. This will be a nice segway into other ways of working with data such as Entity Framework, Linq etc.

In this post we will discuss the following topics

• Connecting to a database
• Using SQL Command Object
• Using Data Reader Object

ADO.NET Provides SQL Connection Object and SQL Command Object that work together to retrieve/select/update your data. 

Let's consider an example. Notice, you have to reference System.Data.SqlClient class to work with ADO.NET object.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ADONET2ConnectedCompact
{
    class Program
    {
        static void Main(string[] args)
        {
            // Step 1: Handshake with the database
            string connectionstring = System.Configuration.ConfigurationManager.
            ConnectionStrings["MyDBConnection"].ToString();
           SqlConnection myConnection = new SqlConnection(connectionstring);
           myConnection.Open();
           try
           {
               // Step 2: Specify a command (request)
               SqlCommand myCommand = myConnection.CreateCommand();

               myCommand.CommandType = CommandType.Text;
               myCommand.CommandText = "SELECT TOP 10 CustomerID,
               AccountNumber,CustomerType FROM Sales.Customer";
               SqlDataReader dr;

               dr = myCommand.ExecuteReader();


               // Step 3: Use the data

               while (dr.Read())
               {
                   Console.WriteLine(dr[0].ToString() + " " + dr[1] + 
                   " " + dr[2]);
               }
               Console.ReadLine();
           }
           catch (Exception ex)
           {
               Console.WriteLine("Exception{0}: ", ex.Message);
           }
           finally
           {
               myConnection.Close();
           }
            

        }
    }
}

In this example, we used inline SQL script as opposed to stored procedure. But, you can use stored procedure instead, just change the command type from CommandType.Text to CommandType.StoredProcedure. In this blog we will not be discussing SQL Server but if you would like to know more about SQL Server, checkout my SQL Blog.

We used connection string from the App.Config file to get the SQL Server, database and login information. Below is a snippet from App.Config that pertains to connection string.

<connectionStrings>
    <add name="MyDBConnection" connectionString="Data Source=HOME-PC\SQL2008;Integrated Security=true;Initial Catalog=AdventureWorks;"/>
  </connectionStrings>

Data Reader
There are couple of ways you can retrieve data from the SQL Server. Data Reader is one of them. The key characteristics of data reader are that it is connected, read only and forward only. You cannot use data reader while you are disconnected from the database or to update/insert data.

Notice the user of try / catch / finally. You want to make sure you always close your SQL connection.

There are other ways to read data namely data adapter. We will review data adapter in future post.

Thank you.