Chapter Objectives

ADO.NET Architecture

The ADO.NET uses a multilayer architecture that mainly has a few concepts. For instance 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. The data providers are extensible; developers can create their own providers for a proprietary data source. There are some examples of data providers such as SQL Server, OLE DB and Oracle providers.
ADO.NET provides the following two types of class objects:

ADO.NET Namespaces

Namespaces Description
System.Data Contains the definition of columns, relations, tables, database, rows, views, and constraints.
System.Data.SqlClient Contains the classes that used to connect to a Microsoft SQL Server database such as SqlCommand, SqlConnection, SqlDataAdapter.
System.Data.Odbc Contains classes required to connect to most ODBC drivers. These classes include OdbcCommand, OdbcConnection.
System.Data.OracleClient Contains classes such as OracleConnection, OracleCommand required to connect to an Oracle database.
Table 1.1 ADO.NET Namespace

Connection Class

 You need to establish a connection class object for inserting, updating, deleting and retrieving data from a database. The Connection class allows you to establish a connection to the data source. The Connection class object needs the necessary information to discover the data source and this information 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 requires a few pieces of information, such as the location of the database, the database name, and the database authentication mechanism.
This connection is used to connect to the Master database on the current computer using integrated security (indicated currently logged-in Windows user to access the database).
C# Code
  1. string conString = "Data Source=localhost;Initial Catalog=Master;Integrated Security=SSPI";
If integrated security is not supported then the connection must indicate a valid user name and password combination.
C# Code
  1. 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 the OLE DB drivers.
C# Code
  1. string conString = "Data Source=localhost;Initial Catalog=Master;user id=sa;password=;Provider=MSDAORA";
You can provide the details of a connection string in the global application setting file and then you can retrieve your connection string by name from the ConfigurationManager.
App.Config
  1. <configuration>
  2. <connectionStrings>
  3. <add name="Master" connectionString ="Data Source=localhost;Initial Catalog=Master;Integrated Security=SSPI" />
  4. </connectionStrings>
  5. </configuration>
Once you declare all the details in the App.config file pertaining to the connection string you can use this definition in the code file also as in the following:
C# Code
  1. string conSting = ConfigurationManager.ConnectionStrings["Master"].ConnectionString ;
  2. SqlConnection Conn = new SqlConnection(conSting);

Testing a Connection

Once you configure the right connection string to establish connectivity with a specific data source you simply use the Open() and Close() methods.
C# Code
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. string conSting = ConfigurationManager.ConnectionStrings["Master"].ConnectionString ;
  4. SqlConnection Conn = new SqlConnection(conSting);
  6. try
  7. {
  8. Conn.Open();
  9. textBox1.Text = "Server Version=" + Conn.ServerVersion;
  10. textBox1.Text += "Connection Is=" + Conn.State.ToString();
  11. }
  12. catch (Exception err)
  13. {
  14. textBox1.Text = err.Message;
  15. }
  16. finally
  17. {
  18. Conn.Close();
  19. textBox1.Text += "Connection Is=" + Conn.State.ToString();
  20. }
  21. }
You can also use the SqlConnectionStringBuilder class to configure a connection string rather than providing it in the App.Config file.
C# Code
  1. SqlConnectionStringBuilder obj = new SqlConnectionStringBuilder();
  2. obj.DataSource = "localhost";
  3. obj.InitialCatalog = "Master";
  4. obj.IntegratedSecurity = true;
  5. 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

The Command class allows performing any data-definition tasks, such as creating and altering tables, databases, retrieving, update and delete records. The Command object is used to execute SQL queries that can be inline text or a Stored Procedure. It all depends on the type of command you are using. Before using the command, you need to configure the Command Type, Text, and Connection properties as in the following.
C# Code
  1. //Command Class definition
  2. SqlCommand sc = new SqlCommand();
  3. sc.Connection = Conn;
  4. sc.CommandType = CommandType.Text;
  5. sc.CommandText = query;
Alternatively, you can pass the connection argument directly to the Command class as in the following.
C# Code
  1. //Command Class definition
  2. SqlCommand sc = new SqlCommand(query,Conn);
In the following example, we are creating a window application form with a Text Box control. We are establishing a connection to the Customer table from the AdventureWorks database. Then, using the SqlDataReader class, we iterate through all the records of the table and display the FirstName and LastName in the TextBox control by executing the While() loop as in the following:
C# Code
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. //Connection String
  4. SqlConnectionStringBuilder obj = new SqlConnectionStringBuilder();
  5. obj.DataSource = "localhost";
  6. obj.InitialCatalog = "AdventureWorksLT2008";
  7. obj.IntegratedSecurity = true;
  9. // Add Connection string to SqlConnection
  10. SqlConnection Conn = new SqlConnection(obj.ConnectionString);
  12. // Query to retrieve records from AdventureWorks Database
  13. string query = "select FirstName,LastName from SalesLT.Customer";
  15. //Command Class definition
  16. SqlCommand sc = new SqlCommand();
  17. sc.Connection = Conn;
  18. sc.CommandType = CommandType.Text;
  19. sc.CommandText = query;
  21. SqlDataReader sdr = null;
  22. try
  23. {
  24. //Open connection
  25. Conn.Open();
  26. sdr = sc.ExecuteReader();
  28. //Get all records
  29. while(sdr.Read())
  30. {
  31. textBox1.AppendText(sdr.GetValue(0) + "\t" + sdr.GetValue(1));
  32. textBox1.AppendText("\n");
  33. }
  35. }
  36. catch (Exception err)
  37. {
  38. textBox1.Text = err.Message;
  39. }
  40. finally
  41. {
  42. //Release reader and connection object
  43. sdr.Close();
  44. Conn.Close();
  45. }
  46. }
It is important to release the objects of the Reader class explicitly after the job is done or you can set the CommandBehaviour Property to CloseConnection in the ExcuteReader() method to avoid the burden of explicitly releasing the object as in the following:
C# Code
  1. //Automatically releasing the Reader class Object
  2. sdr = sc.ExecuteReader(CommandBehavior.CloseConnection);

DataReader Class

The DataReader class object allows you to read the data returned by a SELECT command by 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 Data Reader otherwise the connection remains alive until it is 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
  1. //Open connection
  2. Conn.Open();
  3. sdr = sc.ExecuteReader(CommandBehavior.CloseConnection);
  5. //Get all records
  6. while(sdr.Read())
  7. {
  8. textBox1.AppendText(sdr.GetValue(0) + "\t" + sdr.GetValue(1));
  9. textBox1.AppendText("\n");
  10. }

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 as in the following:
C# Code
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. //Connection String
  4. string conString = @"Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";
  6. // Add Connection string to SqlConnection
  7. SqlConnection Conn = new SqlConnection(conString);
  9. // Query to retrieve records from AdventureWorks Database
  10. string query = "select COUNT(*) from SalesLT.Customer";
  12. //Command Class definition
  13. SqlCommand sc = new SqlCommand(query, Conn);
  15. //Open connection
  16. Conn.Open();
  17. int CountCustomer = (int)sc.ExecuteScalar();
  19. //Count all records
  20. textBox1.AppendText("Total Customer=\t" + CountCustomer.ToString());
  21. }

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 specific record in the Customer table of the Adventure Works database.
C# Code
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. //Connection String
  4. string conString = @"Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";
  6. // Add Connection string to SqlConnection
  7. SqlConnection Conn = new SqlConnection(conString);
  9. // Query to retrieve records from AdventureWorks Database
  10. string query = @"update AdventureWorksLT2008.SalesLT.Customer
  11. set FirstName='ajay'
  12. where CustomerID=2";
  14. //Command Class definition
  15. SqlCommand sc = new SqlCommand(query, Conn);
  17. //Open connection
  18. Conn.Open();
  20. //Reflect changes into database
  21. int CountCustomer = sc.ExecuteNonQuery();
  23. //Result
  24. MessageBox.Show("Record Update Successfully");
  25. }

DataAdapter and DataTable class

 The DataAdapter bridges the gap between the disconnected DataTable objects and the physical data source. 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 a DataTable.
C# Code
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. //Connection String
  4. string conString = "Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";
  6. // Add Connection string to SqlConnection
  7. SqlConnection Conn = new SqlConnection(conString);
  9. // Query to retrieve records from AdventureWorks Database
  10. string query = "select FirstName,LastName from SalesLT.Customer";
  12. //Command Class definition
  13. SqlCommand sc = new SqlCommand(query, Conn);
  15. // Data Adapter definition
  16. SqlDataAdapter sda = new SqlDataAdapter(sc);
  18. // filling the result set in data table
  19. DataTable dt = new DataTable();
  20. sda.Fill(dt);
  22. //output in data grid
  23. dataGridView1.DataSource = dt.DefaultView;
  24. }
The following are commonly used properties offered by the SqlDataAdapter class:
Property Description
SelectCommand This command 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 on the SQL database.
DeleteCommand Executed to delete an existing record on the SQL database.
Table 1.2 Data Adapter Properties

SelectCommand Example

 C# Code
  1. ..
  2. // Query to retrieve records from AdventureWorks Database
  3. string query = "select FirstName,LastName from SalesLT.Customer";
  5. //Command Class definition
  6. SqlCommand sc = new SqlCommand(query, Conn);
  8. // Data Adapter definition
  9. SqlDataAdapter sda = new SqlDataAdapter();
  10. sda.SelectCommand = sc;
  12. // filling the result set in data table
  13. DataTable dt = new DataTable();
  14. sda.Fill(dt);
  15. ..

Update Command Example

C# Code
  1. ..
  2. string query = @"update AdventureWorksLT2008.SalesLT.Customer
  3. set FirstName='ajay'
  4. where CustomerID=2";
  6. //Command Class definition
  7. SqlCommand sc = new SqlCommand(query, Conn);
  9. // Data Adapter definition
  10. SqlDataAdapter sda = new SqlDataAdapter();
  11. sda.UpdateCommand = sc;
  12. ..

Parameterized Commands (Stored Procedure)

 A Stored Procedure is a batch of one or more SQL statements stored in the database. They are similar to a function in that they are well-encapsulated blocks of logic that accepts data using an input parameter and returns data via a result set or output parameter. Here the SQL code is needed to create a procedure for extracting a single something from the customer table on behalf of a specific CustomerID.
SQL.script
  1. Create Proc GetCustomer
  2. @CustID varchar(10)
  3. AS
  4. select * from SalesLT.Customer where CustomerID=@CustID
  5. GO
Next you can create a SqlCommand to wrap the call to the Stored Procedure. This command takes one parameter as input and returns the records. A parameterized command basically uses placeholders in the SQL text. The placeholder indicates dynamically supplied values that are then sent using a parameters collection of the Command object.
C# Code
  1. private void btnData_Click(object sender, EventArgs e)
  2. {
  3. //Connection String
  4. string conString = "Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";
  6. // Add Connection string to SqlConnection
  7. SqlConnection Conn = new SqlConnection(conString);
  9. //Command Class definition
  10. SqlCommand sc = new SqlCommand("GetCustomer", Conn);
  11. sc.CommandType = CommandType.StoredProcedure;
  13. sc.Parameters.Add("@CustID",txtParameter.Text);
  15. // Data Adapter definition
  16. SqlDataAdapter sda = new SqlDataAdapter(sc);
  18. // filling the result set in data table
  19. DataTable dt = new DataTable();
  20. sda.Fill(dt);
  22. //output in data grid
  23. dataGridView1.DataSource = dt.DefaultView;
  24. }
This example uses a parameterized command that is supplied via a text box (Customer ID) and the result is processed using the Stored Procedure in the code file and the result is displayed in the Data Grid View control as in the following:
get data

DataSet class

 A DataSet is a Disconnected Architecture technology. It contains zero or more tables and relationships. When you work with a dataset, the data in the data source is not 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 a SQL Server table and use it to fill in a DataTable object in the DataSet as in the following.
C# Code
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. //Connection String
  4. string conString = "Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI";
  6. // Add Connection string to SqlConnection
  7. SqlConnection Conn = new SqlConnection(conString);
  9. string query = "select * from SalesLT.Customer";
  11. //Command Class definition
  12. SqlCommand sc = new SqlCommand(query, Conn);
  14. // Data Adapter definition
  15. SqlDataAdapter sda = new SqlDataAdapter();
  16. sda.SelectCommand = sc;
  18. //data Set definition
  19. DataSet ds = new DataSet();
  21. // filling the result set in data table
  22. sda.Fill(ds, "SalesLT.Customer");
  24. //output in data grid
  25. dataGridView1.DataSource = ds.Tables["SalesLT.Customer"];
  26. }
Here you need to create an empty DataSet and use the SqlDataAdapter Fill() method to execute the query and place the results in a new DataTable in the DataSet.
customer id

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 using a set of base common classes.
Important: You need to import the System.Data.Common namespace into the C# code file for utilizing provider agnostic code functionality.
The first step is to set up the App.Config file with the connection string, provider name and the query for this example as in the following.
App.Config
  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <configuration>
  3. <connectionStrings>
  4. <add name="Adventure" connectionString ="Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI" />
  5. </connectionStrings>
  6. <appSettings>
  7. <add key ="factory" value="System.Data.SqlClient" />
  8. <add key="CustQuery" value ="select * from SalesLT.Customer"/>
  9. </appSettings>
  10. </configuration>
Next here the factory is based code as in the following.
C# Code
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. //Get the Factory
  4. string factory = ConfigurationManager.AppSettings["factory"];
  5. DbProviderFactory pro = DbProviderFactories.GetFactory(factory);
  7. //Use this factory to create a connection
  8. DbConnection con = pro.CreateConnection();
  9. con.ConnectionString = ConfigurationManager.ConnectionStrings["Adventure"].ConnectionString;
  11. //Create the command
  12. DbCommand cmd = pro.CreateCommand();
  13. cmd.CommandText = ConfigurationManager.AppSettings["CustQuery"];
  14. cmd.Connection = con;
  16. //Open the connection
  17. con.Open();
  18. DbDataReader rdr = cmd.ExecuteReader();
  20. //Get all records
  21. while (rdr.Read())
  22. {
  23. textBox1.AppendText(rdr.GetValue(3) + "\t" + rdr.GetValue(5));
  24. textBox1.AppendText("\n");
  25. }
  27. }

Summary

ADO.NET is the native data access technology of the .NET platform. In this article, we examined the connected layer and learned the significant role of data providers that are essentially a concrete implementation of several namespaces, interfaces, and base classes. Finally, you got an understanding of employing common objects including commands, connection, reader and transaction of the connection layer to select, delete and update records.