Using ADO.NET - Part IV

In previous post we discussed how you can use dataset to insert new row and then use HasChanges() method and GetChanges() to only get newly added rows and insert them in the database instead of looping through all rows.

Today, we will see how you can use SQL Data Adapter to update the changes made to the data table and also Datarow's rowstate property to detect a deleted row and then delete it in the database.

In the first example, I have created a method that will change a datarow and then use dataadapter to update the changed row. I didn't have to loop through all records to determine which rows were updated.

Updating Modified Rows

private static void UpdateCustomer(DataSet ds, SqlConnection myConnection)
        {
            SqlCommand myCommand = myConnection.CreateCommand();
            
            //lets modify a record
            if (ds.Tables["Customers"].Rows.Count > 0)
            {
                ds.Tables["Customers"].Rows[0]["TerritoryID"] = "7";
                ds.Tables["Customers"].Rows[0]["CustomerType"] = "S";
                ds.Tables["Customers"].Rows[0]["ModifiedDate"] = DateTime.Now;

            }

            //now save this data in database. 

            //create a command object. 
            string strSQL = "sp_UpdateCustomer";
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(new SqlParameter("@CustomerID", 
                          SqlDbType.Int,4,"CustomerID"));
            myCommand.Parameters.Add(new SqlParameter("@TerritoryID", 
                          SqlDbType.Int,4,"TerritoryID"));
            myCommand.Parameters.Add(new SqlParameter("@CustomerType", 
                          SqlDbType.Char,1,"CustomerType"));
            myCommand.Parameters.Add(new SqlParameter("@ModifiedDate", 
                          SqlDbType.DateTime,8,"ModifiedDate"));

           
            myCommand.CommandText = strSQL;
            SqlDataAdapter da = new SqlDataAdapter();
            da.UpdateCommand = myCommand;

            da.Update(ds.Tables["Customers"].GetChanges());

        }

In the second example, we will delete a row in a data table then detect the deleted row to delete it from the database.

Deleting a Deleted Row

 private static void DeleteCustomer(DataSet ds, SqlConnection myConnection)
        {
            SqlCommand myCommand = myConnection.CreateCommand();
            string strSQL = "sp_DeleteCustomer";
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = strSQL;
            myCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));

            //lets modify a record
            if (ds.Tables["Customers"].Rows.Count > 0)
            {
                ds.Tables["Customers"].Rows[0].Delete();   //delete a record

            }
            foreach (DataRow CustomerRow in ds.Tables["Customers"].GetChanges().Rows)
            {
                //use row state to determine the deleted row
                switch (CustomerRow.RowState)  
                {
                    case DataRowState.Deleted:
                        //use original version to get the ID
                        myCommand.Parameters["@CustomerID"].Value= 
                        CustomerRow["CustomerID",DataRowVersion.Original];   
                        myCommand.ExecuteNonQuery();
                        break;
                 }
                          
            }
                       

        }

Calling from the Main method

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.
               
               //Update Example
               UpdateCustomer(ds, myConnection);
               //Delete Example
               DeleteCustomer(ds, myConnection);
           }
           catch (Exception ex)
           {
               Console.WriteLine("Exception{0}: ", ex.Message);
           }
           finally
           {
               myConnection.Close();
           }
            

        }

As you saw in last few posts, dataset allows you to store data in memory, move it around, insert a new row, update or delete an existing row and then upload changes to the database.

So far we concerned ourselves with untyped datasets. In the next post we will review typed datasets and the differences between the two.

Thank you.