ADO.NET uses a multilayer architecture that has components such as the Connection, Reader, Command, Adapter and DataSet objects. ADO.NET introduced data providers that are a set of special classes to access a specific database, execute SQL commands and retrieve data. Data providers are extensible; developers can create their own providers for proprietary data source Some examples of data providers include SQL server providers, OLE DB and Oracle providers.

ADO.NET provides two types of classes of objects:

  • Connection-based: They are the data provider objects such as Connection, Command, DataAdapter and DataReader. They execute SQL statements and connect to a database.
  • Content-based: They are found in the System.Data namespace and includes DataSet, DataColumn, DataRow, and DataRelation. They are completely independent of the type of data source.

ADO.NET Namespaces

Namespaces Description
System.Data Contains the definition for columns, relations, tables, database, rows, views and constraints.
System.Data.SqlClient Contains the classes that are used to connect to a MS SQL server database such as SqlCommand, SqlConnection, and SqlDataAdapter.
System.Data.Odbc Contains classes required to connect to most ODBC drivers. These classes include OdbcCommand and OdbcConnection.
System.Data.OracleClient Contains classes such as OracleConnection and OracleCommand required to connect to an Oracle database.

Table 1.1 ADO.NET Namespace

Connection Class

You need to establish a connection class object to insert, update, delete or retrieve data from a database. The Connection class allows you to establish a connection to the data source, but to do so, it needs the necessary information to discover the data source, which is provided by a connection string.

Connection Strings

You need to supply a connection string in the Connection class object. The connection string is a series of name/value settings separated by semicolons (;). A connection string contains information such as the location of the database, its name and its authentication mechanism.

This connection is used to connect to the Master database on the current computer using integrated security, indicated by a currently logged-in Windows user to access the database.

C# Code

string conString = "Data Source=localhost;Initial Catalog=Master;Integrated Security=SSPI";

In case integrated security is not supported, then the connection must indicate a valid user name and password combination such as:

C# Code

string conString = "Data Source=localhost;Database=Master;user id=sa;password=sa";

If you use the OLE DB provider, then your connection string will have some additional settings that identify OLE DB drivers such as:

C# Code

string conString = "Data Source=localhost;Initial Catalog=Master;user id=sa;password=;Provider=MSDAORA";

You can mention the details of connection strings in the global application setting file and then you can retrieve your connection string by name from ConfigurationManager like so:

App.Config

<configuration>
	<connectionStrings>
		<add name="Master" connectionString ="Data Source=localhost;Initial Catalog=Master;Integrated Security=SSPI" />
	</connectionStrings> 
</configuration>

Once you declare all the details in the App.config file pertaining to the connection string, then you can use the definition in the code file as follows:

C# Code

string conSting = ConfigurationManager.ConnectionStrings["Master"].ConnectionString ;
            SqlConnection Conn = new SqlConnection(conSting);

Testing a Connection

Once you configure the right connection string to establish connectivity with a particular data source, you simply use the Open() and Close() methods as such:

C# Code

private void Form1_Load(object sender, EventArgs e)
        {
            string conSting =         ConfigurationManager.ConnectionStrings["Master"].ConnectionString ;
            SqlConnection Conn = new SqlConnection(conSting);

            try
            {
                Conn.Open();
                textBox1.Text = "Server Version=" + Conn.ServerVersion;
                textBox1.Text += "Connection Is=" + Conn.State.ToString();
            }
            catch (Exception err)
            {
                textBox1.Text = err.Message;
            }
            finally
            {
                Conn.Close();
                textBox1.Text += "Connection Is=" + Conn.State.ToString();
            }
        }

You can also use SqlConnectionStringBuilder class to configure a connection string rather than mention it on the App.Config file:

C# Code

SqlConnectionStringBuilder obj = new SqlConnectionStringBuilder();
obj.DataSource = "localhost";
obj.InitialCatalog = "Master";
obj.IntegratedSecurity = true;
SqlConnection Conn = new SqlConnection(obj.ConnectionString);

Important: Connections are a limited server resource so it is imperative to release the open connection as soon as possible.

Command and Data Reader Classes

Command Class allows you to perform any Data-definition tasks such as creating and altering tables and database, or retrieving, updating and deleting records. The Command object is used to execute SQL queries which can be inline text or stored procedures. It is all dependent on the type of command you are using. Before using the command, you need to configure the Command Type, Text and Connection properties as follows:

C# Code

//Command Class definition
SqlCommand sc = new SqlCommand();
sc.Connection = Conn;
sc.CommandType = CommandType.Text;
sc.CommandText = query;

Alternatively, you can pass the connection argument directly to the Command class:

C# Code

//Command Class definition
SqlCommand sc = new SqlCommand(query,Conn);

In the following example, we are creating a window application form with a Text Box control. We will establish a connection to the Customer table from the AdventureWorks database. After that, using the SqlDataReader class, we go through all the records of the table and display the FirstName and LastName in the Text Box control by executing the While() loop as follows:

C# Code

private void Form1_Load(object sender, EventArgs e)
        {
            //Connection String
            SqlConnectionStringBuilder obj = new SqlConnectionStringBuilder();
            obj.DataSource = "localhost";
            obj.InitialCatalog = "AdventureWorksLT2008";
            obj.IntegratedSecurity = true;

            // Add Connection string to SqlConnection 
            SqlConnection Conn = new SqlConnection(obj.ConnectionString);

            // Query to retrieve records from AdventureWorks Database
            string query = "select FirstName,LastName from SalesLT.Customer";
            
            //Command Class definition
            SqlCommand sc = new SqlCommand();
            sc.Connection = Conn;
            sc.CommandType = CommandType.Text;
            sc.CommandText = query;
 
            SqlDataReader sdr = null;
            try
            {
               //Open connection
                Conn.Open();
                sdr = sc.ExecuteReader();

                //Get all records 
                while(sdr.Read())
                {
                    textBox1.AppendText(sdr.GetValue(0) + "t" + sdr.GetValue(1));
                    textBox1.AppendText("n");    
                }
                
            }
            catch (Exception err)
            {
                textBox1.Text = err.Message;
            }
            finally
            {
                //Release reader and connection object
                sdr.Close(); 
                Conn.Close();              
            }
        }

It is important to release the objects manually from the Reader class after the job is done, or you can set the CommandBehaviour Property to CloseConnection in the ExcuteReader() method to evade the burden of manually releasing the object:

C# Code

//Automatically releasing the Reader class Object
sdr = sc.ExecuteReader(CommandBehavior.CloseConnection);

DataReader Class

The DataReader Class object allows you to read the data returned by a SELECT command through a simple forward-only and read-only cursor. It requires a live connection with the data source and provides a very efficient way of looping and consuming all parts of the result set. The object of the DataReader cannot be directly instantiated. Instead, you must call the ExecuteReader method of the Command object and close the connection when you are done using the DataReader, otherwise the connection stays alive until it’s explicitly closed.

DataReader with ExecuteReader() Method

Once you have the DataReader, you can cycle through its records by calling the Read() method in a While loop. This moves the row cursor to the next record.

C# Code

//Open connection
Conn.Open();
sdr = sc.ExecuteReader(CommandBehavior.CloseConnection);

//Get all records 
 while(sdr.Read())
      {
        textBox1.AppendText(sdr.GetValue(0) + "t" + sdr.GetValue(1));
        textBox1.AppendText("n");    
      }

ExecuteScalar() Method

The ExecuteScalar () method returns the value stored in the first field of the first row of a result set generated by the command’s SELECT query. This method is usually used to count the total number of rows in the table:

C# Code

private void Form1_Load(object sender, EventArgs e)
        {
            //Connection String
            string conString = @"Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";

            // Add Connection string to SqlConnection 
            SqlConnection Conn = new SqlConnection(conString);

            // Query to retrieve records from AdventureWorks Database
            string query = "select COUNT(*) from SalesLT.Customer";

            //Command Class definition
            SqlCommand sc = new SqlCommand(query, Conn);

            //Open connection
            Conn.Open();
            int CountCustomer = (int)sc.ExecuteScalar();

            //Count all records 
            textBox1.AppendText("Total Customer=t" + CountCustomer.ToString());
        }

ExecuteNonQuery() Method

The ExecuteNonQuery() method executes commands that don’t return a result set, for instance, INSERT, UPDATE and DELETE. Here in this example we made a modification to a particular record in the Customer table of the AdventureWorks database:

C# Code

private void Form1_Load(object sender, EventArgs e)
        {
            //Connection String
            string conString = @"Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";

            // Add Connection string to SqlConnection 
            SqlConnection Conn = new SqlConnection(conString);

            // Query to retrieve records from AdventureWorks Database
            string query = @"update AdventureWorksLT2008.SalesLT.Customer 
                            set FirstName='ajay'
                            where CustomerID=2";

            //Command Class definition
            SqlCommand sc = new SqlCommand(query, Conn);

            //Open connection
            Conn.Open();

            //Reflect changes into database
            int CountCustomer = sc.ExecuteNonQuery();

            //Result
            MessageBox.Show("Record Update Successfully");  
        }

DataAdapter and DataTable class

The DataAdapter bridges the gap between the disconnected DataTable objects and the physical data source. The SqlDataAdapter is capable of executing a SELECT, DELETE and UPDATE statement on a data source as well as extract input from the result set into a DataTable object. The SqlDataAdapter class provides a method called Fill() to copy the result set into DataTable.

C# Code

private void Form1_Load(object sender, EventArgs e)
        {
            //Connection String
            string conString = "Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";
            
            // Add Connection string to SqlConnection 
            SqlConnection Conn = new SqlConnection(conString);

            // Query to retrieve records from AdventureWorks Database
            string query = "select FirstName,LastName from SalesLT.Customer";

            //Command Class definition
            SqlCommand sc = new SqlCommand(query, Conn);

            // Data Adapter definition
            SqlDataAdapter sda = new SqlDataAdapter(sc);

            // filling the result set in data table
            DataTable dt = new DataTable(); 
            sda.Fill(dt);

            //output in data grid
            dataGridView1.DataSource = dt.DefaultView;   
        }

These are the commonly used properties offered by SqlDataAdapter class:

Property Description
SelectCommand This command is executed to fill in a Data Table with the result set.
InsertCommand Executed to insert a new row to the SQL database.
UpdateCommand Executed to update an existing record in the SQL database.
DeleteCommand Executed to delete an existing record in the SQL database.

Table 1.2 Data Adapter Properties

SelectCommand Example

C# Code

..
// Query to retrieve records from AdventureWorks Database
string query = "select FirstName,LastName from SalesLT.Customer";

//Command Class definition
SqlCommand sc = new SqlCommand(query, Conn);

// Data Adapter definition
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = sc;

// filling the result set in data table
DataTable dt = new DataTable();
sda.Fill(dt);
..

Update Command Example

C# Code

..
string query = @"update AdventureWorksLT2008.SalesLT.Customer
set FirstName='ajay'
where CustomerID=2";

//Command Class definition
SqlCommand sc = new SqlCommand(query, Conn);

// Data Adapter definition
SqlDataAdapter sda = new SqlDataAdapter();
sda.UpdateCommand = sc;
..

Parameterized Commands (Stored Procedure)

A stored procedure is a batch of one or more SQL statements that are stored in the database. They are similar to functions in that they are well-encapsulated blocks of logic that accept data through input parameters and return data via result set or output parameter. Here is the SQL code needed to create a procedure for extracting a single from the customer table using a particular CustomerID:

Sql.script

Create Proc GetCustomer
@CustID varchar(10)
AS
select * from SalesLT.Customer where CustomerID=@CustID
GO  

Next, you can create an SqlCommand to wrap the call to the stored procedure. This command takes one parameter as input and returns the records. A parameterized command is basically something that uses a placeholder in the SQL text. The placeholder indicates dynamically supplied values which are then sent through parameters collected by the Command object.

C# Code

Want to learn more?? The InfoSec Institute Web Application Penetration Testing Boot Camp focuses on preparing you for the real world of Web App Pen Testing through extensive lab exercises, thought provoking lectures led by an expert instructor. We review of the entire body of knowledge as it pertains to web application pen testing through a high-energy seminar approach.

The Web Application Penetration Testing course from InfoSec Institute is a totally hands-on learning experience. From the first day to the last day, you will learn the ins and outs of Web App Pen Testing by attending thought provoking lectures led by an expert instructor. Every lecture is directly followed up by a comprehensive lab exercise (we also set up and provide lab workstations so you don't waste valuable class time installing tools and apps). Benefits to you are:

  • Get CWAPT Certified
  • Learn the Secrets of Web App Pen Testing in a totally hands-on classroom environment
  • Learn how to exploit and defend real-world web apps: not just silly sample code
  • Complete the 83 Step "Web App Pen Test Methodology", and bring a copy back to work with you
  • Learn how perform OWASP Top 10 Assessments: for PCI DSS compliance
private void btnData_Click(object sender, EventArgs e)
{
    //Connection String
    string conString = "Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";

    // Add Connection string to SqlConnection
    SqlConnection Conn = new SqlConnection(conString);

    //Command Class definition
    SqlCommand sc = new SqlCommand("GetCustomer", Conn);
    sc.CommandType = CommandType.StoredProcedure;

    sc.Parameters.Add("@CustID",txtParameter.Text);

    // Data Adapter definition
    SqlDataAdapter sda = new SqlDataAdapter(sc);

    // filling the result set in data table
    DataTable dt = new DataTable();
    sda.Fill(dt);

    //output in data grid
    dataGridView1.DataSource = dt.DefaultView;
}

This example uses a parameterized command that is supplied via text box (Customer ID) and the result is processed by using the stored procedure in the code file and then displayed in the Data Grid View control:

DataSet class

DataSet is a Disconnected Architecture technology. It contains zero or more tables and relationships. When you work with DataSet, the data in the data source isn’t touched at all. Instead all the changes are made locally to the dataset in memory. In the following example, you will see how to retrieve data from an SQL server table and use it to fill a DataTable object in the DataSet:

C# Code

private void Form1_Load(object sender, EventArgs e)
        {
            //Connection String
            string conString = "Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";

            // Add Connection string to SqlConnection 
            SqlConnection Conn = new SqlConnection(conString);

            string query = "select * from SalesLT.Customer";

            //Command Class definition
            SqlCommand sc = new SqlCommand(query, Conn);

            // Data Adapter definition
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = sc;
            
            //data Set definition
            DataSet ds = new DataSet();
            
            // filling the result set in data table
            sda.Fill(ds, "SalesLT.Customer");

            //output in data grid
            dataGridView1.DataSource = ds.Tables["SalesLT.Customer"];
        }

Here you need to create an empty DataSet, use SqlDataAdapter Fill() method to execute the query, and place the results in a new DataTable in the DataSet:

Provider Agnostic code

You can use a single factory object to create every other type of provider specific object that you need. You can then interact with these provider specific objects in a completely generic way through a set of base common classes.

Important: You have to import the System.Data.Common namespace in the C# code file to utilize provider agnostic code functionality.

The first step is to set up the App.Config file with the connection string and provider name, and the query for this example is as follows:

App.Config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<add name="Adventure" connectionString ="Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI" />
	</connectionStrings>
	<appSettings>
		<add key ="factory" value="System.Data.SqlClient" />
		<add key="CustQuery" value ="select * from SalesLT.Customer"/>
	</appSettings>
</configuration>

Next, here’s the factory based code:

C# Code

private void Form1_Load(object sender, EventArgs e)
        {
            //Get the Factory
            string factory = ConfigurationManager.AppSettings["factory"];
            DbProviderFactory pro = DbProviderFactories.GetFactory(factory);

            //Use this factory to create a connection
            DbConnection con = pro.CreateConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["Adventure"].ConnectionString;

            //Create the command
            DbCommand cmd = pro.CreateCommand();
            cmd.CommandText = ConfigurationManager.AppSettings["CustQuery"];
            cmd.Connection = con;

            //Open the connection
            con.Open();
            DbDataReader rdr = cmd.ExecuteReader();

            //Get all records 
            while (rdr.Read())
            {
                textBox1.AppendText(rdr.GetValue(3) + "t" + rdr.GetValue(5));
                textBox1.AppendText("n");
            }

        }