Modifying a DataTable Object and Synchronizing the Changes with the Database

 Modifying a DataTable Object and Synchronizing the Changes with the Database 

  In this article, we’ll see the steps required to add, modify, and remove rows from a DataTable object and then synchronize those changes with the database.

 When we make changes to the rows in a DataTable, it frees we from writing SQL INSERT, UPDATE, and DELETE statements. Our changes are made to the local copy of the rows on our local computer. When we’re ready to synchronize our changes with the database, we simply call the Fill() method of our data adapter to permanently record our changes in the database.

  Code: Program.cs 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace DatatableSynchWithDB
{
    class Program
    {
        public static void DisplayDataTable(DataTable myDataTable)
        {
            // display the columns for each row in the DataTable,
            // using a DataRow object to access each row in the DataTable
            foreach (DataRow myDataRow in myDataTable.Rows)
            {
                Console.WriteLine("deptno = " + myDataRow["deptno"]);
                Console.WriteLine("Department Name = " + myDataRow["dname"]);
                Console.WriteLine("loc = " + myDataRow["loc"]);
            }
        }
        public static void AddRow(DataTable myDataTable)
        {
            Console.WriteLine("\nAdding a new row with deptno of '50'");
            // step 1: use the NewRow() method of the DataRow object to create
            // a new row in the DataTable
            DataRow myNewDataRow = myDataTable.NewRow();
            // step 2: set the values for the columns of the new row
            myNewDataRow["deptno"] = "50";
            myNewDataRow["dname"] = "Testser";
            myNewDataRow["loc"] = "Pune";
            // step 3: use the Add() method through the Rows property to add
            // the new DataRow to the DataTable
            myDataTable.Rows.Add(myNewDataRow);
            // step 4: use the AcceptChanges() method of the DataTable to commit
            // the changes
            myDataTable.AcceptChanges();
        }

        public static void ModifyRow(DataTable myDataTable)
        {
            Console.WriteLine("\nModifying the new row");
            // step 1: set the PrimaryKey property for the DataTable object
            DataColumn[] myPrimaryKey = new DataColumn[1];
            myPrimaryKey[0] = myDataTable.Columns["deptno"];
            myDataTable.PrimaryKey = myPrimaryKey;
            // step 2: use the Find() method to locate the DataRow
            // in the DataTable using the primary key value
            DataRow myEditDataRow = myDataTable.Rows.Find("10");
            // step 3: change the column values
            myEditDataRow["dname"] = "IT";
            myEditDataRow["loc"] = "Mumbai";
            // step 4: use the AcceptChanges() method of the DataTable to commit
            // the changes
            myDataTable.AcceptChanges();
            Console.WriteLine("myEditDataRow.RowState = " + myEditDataRow.RowState);
        }

        public static void RemoveRow(DataTable myDataTable)
        {
            Console.WriteLine("\nRemoving the new row");
            // step 1: set the PrimaryKey property for the DataTable object
            DataColumn[] myPrimaryKey = new DataColumn[1];
            myPrimaryKey[0] = myDataTable.Columns["deptno"];
            myDataTable.PrimaryKey = myPrimaryKey;
            // step 2: use the Find() method to locate the DataRow
            DataRow myRemoveDataRow = myDataTable.Rows.Find("50");
            // step 3: use the Delete() method to remove the DataRow
            myRemoveDataRow.Delete();
            // step 4: use the AcceptChanges() method of the DataTable to commit
            // the changes
            myDataTable.AcceptChanges();
        }

        public static void Main()
        {
            // formulate a string containing the details of the
            // database connection
            string connectionString = "data source=LearnAsp;user id=LearnAsp;
            password=LearnAsp; Pooling=true; Max pool size=200; Min pool size=0;";
            // create a SqlConnection object to connect to the
            // database, passing the connection string to the constructor
            OracleConnection myConn = new OracleConnection(connectionString);

            // formulate a SELECT statement to retrieve the
            // CustomerID, CompanyName, ContactName, and Address
            // columns for the first row from the Customers table
            string selectString = "SELECT deptno,dname, loc   FROM dept where deptno=10 ";

            // create a SqlCommand object to hold the SELECT statement
            OracleCommand myCommand = myConn.CreateCommand();

            // set the CommandText property of the SqlCommand object to
            // the SELECT string
            myCommand.CommandText = selectString;

            // create a SqlDataAdapter object
            OracleDataAdapter myDataAdapter = new OracleDataAdapter();

            // set the SelectCommand property of the SqlAdapter object
            // to the SqlCommand object
            myDataAdapter.SelectCommand = myCommand;

            // create a DataSet object to store the results of
            // the SELECT statement
            DataSet myDataSet = new DataSet();

            // open the database connection using the
            // Open() method of the SqlConnection object
            myConn.Open();

            // use the Fill() method of the SqlDataAdapter object to
            // retrieve the rows from the table, storing the rows locally
            // in a DataTable of the DataSet object
            Console.WriteLine("Retrieving a row from the Depart ment  table");
            myDataAdapter.Fill(myDataSet, "dept");

            // get the DataTable object from the DataSet object
            DataTable myDataTable = myDataSet.Tables["dept"];
            // display the rows in the DataTable object
            DisplayDataTable(myDataTable);

            // add a new row
            AddRow(myDataTable);
            DisplayDataTable(myDataTable);

            // modify a row
            ModifyRow(myDataTable);
            DisplayDataTable(myDataTable);

            // remove a row
            RemoveRow(myDataTable);
            DisplayDataTable(myDataTable);

            // use the Fill() method of the SqlDataAdapter object
            // to synchronize the changes with the database

            myDataAdapter.Fill(myDataSet, "dept");
           
            // close the database connection using the Close() method
            // of the SqlConnection object
            myConn.Close();
        }
    }
}

  Output: