Using ADO.NET - Part II

In previous post we reviewed ADO.NET object and data reader to read through the data. As I mentioned, data reader is read only, forward only, connected mechanism. Today, we will review data adapter and dataset.

Dataset is a disconnected representation of the data. A dataset is a subset of the database and can has one or more data tables. You can also create relationship between two tables just as in a database. There are two types of datasets - Typed and Untyped. Typed datasets are created during design time and are schematic representation of the subset of your database. You can create typed datasets by creating table schema either manually or by dragging the table from the database on to the design surface. Untyped datasets are create in memory. Although you can create relationship between the tables and enforce data types but it is much easier with typed datasets.

Today, we will review how you can use data adapter and its "Fill" method to populate untyped dataset declared in memory. In future post we will see how you can insert new data or update existing data in a dataset and then save it to the database.

Example

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,TerritoryID,
               AccountNumber,CustomerType,ModifiedDate FROM Sales.Customer";
              
               // now use data sets instead of data read
               SqlDataAdapter da = new SqlDataAdapter();
               da.SelectCommand = myCommand;
               DataSet ds = new DataSet();
               da.Fill(ds);
               foreach (DataRow customerrow in ds.Tables[0].Rows)
               {
                   Console.WriteLine(customerrow[0].ToString() + " " + 
                                 customerrow[1] + " " + customerrow[2]);
               }
               
               //You can also name the table in the databaset. Also, if you were 
               //using a stored procedure which returned multiple result sets, 
               //each will go in its own table. For example, if your stored proc 
               //returned Customers and Orders as two separate result set, 
               //Table[0] will have the first result set and table[1] 
               //will have the second result set. Dataset is a subset of 
              //your SQL Database schema and in a typed database you can 
              //also define and enforce relationship integrity and data types.
              
               // Rename the table in dataset
              
               ds.Tables[0].TableName = "Customers";
               foreach (DataRow customerrow in ds.Tables["Customers"].Rows)
               {
                   Console.WriteLine(customerrow[0].ToString() + " " 
                   + customerrow[1] + " " + customerrow[2]);
               }
               Console.Read();

               
           }
           catch (Exception ex)
           {
               Console.WriteLine("Exception{0}: ", ex.Message);
           }
           finally
           {
               myConnection.Close();
           }
            

        }
    }
}

As you can see in this example, a dataset is a collection of datatables. Datatables have rowstate which you can use to determine whether a row has been changed or a new row has been inserted.

Dataset is disconnected and you can pass it to other objects or bind to other elements such as grid, dropdownlist etc.

In future post we will discuss using dataset to insert / update / delete rows and then save them to the database.

Thank you.