Using ADO.NET - Part III

In previous post  we discussed using data adapter to fill dataset. Dataset is a disconnected memory representation of the database. You can bind this dataset to other objects and add/delete/update rows in the datatables in this dataset. Rowstate of the datarows can be used to determine whether the data has been modified. Rowstate can be unchanged, added, modified or deleted.

You can use "HasChanges" method of a dataset to determine whether a dataset has changes and then "GetChanges" method of a datatable to only get the changes and insert in the database. You can also use dataadapter's update method to insert/update/delete a record in the database (we will review that in future post).

This example will demonstrate how you can insert a new row in a datatable and then use the GetChanges method to filter only the new rows and then insert the new row in the database.

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
           {
               DemonstrateRowState();
               Console.Read();

                   // 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);

               // Rename the table in dataset
               ds.Tables[0].TableName = "Customers";
               
               //Dataset is a disconnected representation of the data in memory. 
               //You can update/delete/insert data into this dataset 
               //and then save to the database.
               
               //Inserting a new Customer
               DataRow newCustomer;
               newCustomer = ds.Tables["Customers"].NewRow();
               newCustomer["TerritoryID"] = 7;
               newCustomer["CustomerType"] = "I";
               newCustomer["ModifiedDate"] = DateTime.Now;
               ds.Tables["Customers"].Rows.Add(newCustomer);
               
               //now save this data in database. 
               
               //create a command object. 
             string strSQL = "sp_InsertCustomer";
             myCommand.CommandType = CommandType.StoredProcedure;
             myCommand.Parameters.Add(new SqlParameter("@TerritoryID", 
                                     SqlDbType.Int));
             myCommand.Parameters.Add(new SqlParameter("@CustomerTYpe", 
                                     SqlDbType.Char));
             myCommand.Parameters.Add(new SqlParameter("@ModifiedDate", 
                                     SqlDbType.DateTime));

               myCommand.CommandText = strSQL;
               da.InsertCommand = myCommand;
               
               //HasChanges tells you whethere is changed data
               if (ds.HasChanges()) 
               {
                   
    foreach (DataRow newCustomerRow in ds.Tables["Customers"].GetChanges().Rows)
         {
    myCommand.Parameters["@TerritoryID"].Value = newCustomerRow["TerritoryID"];
    myCommand.Parameters["@CustomerType"].Value = newCustomerRow["CustomerType"];
    myCommand.Parameters["@ModifiedDate"].Value = newCustomerRow["ModifiedDate"];

    myCommand.ExecuteNonQuery();
         }
      }

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

        }

When you are working with an n-tier application, you can create a dataset in business layer and send it to the presentation layer, which can work with this data. The presentation layer can send only the modified or newly added data rows back to the business layer which can then update the database. You can declare datatable in memory and define data type for each column in the datatable and fill it with the data from the database. This will ensure no data conversion is necessary. You can go one step further and define typed dataset which in addition to defining the appropriate datatype can also provide intellisense and data validation. We will review typed datasets in future post.

Thank you.