illustrates the use of adding, modifying, and deleting a row in a DataTable object and synchronizing those changes with the

   
 
/*
Mastering Visual C# .NET
by Jason Price, Mike Gunderloy

Publisher: Sybex;
ISBN: 0782129110
*/

/*
  Example23_3.cs illustrates the use of adding, modifying, and deleting
  a row in a DataTable object and synchronizing those changes with the
  database
*/

using System;
using System.Data;
using System.Data.SqlClient;

public class Example23_3
{

  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("CustomerID = " + myDataRow["CustomerID"]);
      Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]);
      Console.WriteLine("ContactName = " + myDataRow["ContactName"]);
      Console.WriteLine("Address = " + myDataRow["Address"]);
    }

  }

  public static void AddRow(
    DataTable myDataTable
  )
  {

    Console.WriteLine("
Adding a new row with CustomerID of 'T1COM'");

    // 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["CustomerID"] = "T1COM";
    myNewDataRow["CompanyName"] = "T1 Company";
    myNewDataRow["ContactName"] = "Jason Price";
    myNewDataRow["Address"] = "1 Main Street";

    // 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("
Modifying the new row");

    // step 1: set the PrimaryKey property for the DataTable object
    DataColumn[] myPrimaryKey = new DataColumn[1];
    myPrimaryKey[0] = myDataTable.Columns["CustomerID"];
    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("T1COM");

    // step 3: change the column values
    myEditDataRow["CompanyName"] = "Widgets Inc.";
    myEditDataRow["ContactName"] = "John Smith";
    myEditDataRow["Address"] = "1 Any Street";

    // 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("
Removing the new row");

    // step 1: set the PrimaryKey property for the DataTable object
    DataColumn[] myPrimaryKey = new DataColumn[1];
    myPrimaryKey[0] = myDataTable.Columns["CustomerID"];
    myDataTable.PrimaryKey = myPrimaryKey;

    // step 2: use the Find() method to locate the DataRow
    DataRow myRemoveDataRow = myDataTable.Rows.Find("T1COM");

    // 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 =
      "server=localhost;database=Northwind;uid=sa;pwd=sa";

    // create a SqlConnection object to connect to the
    // database, passing the connection string to the constructor
    SqlConnection mySqlConnection =
      new SqlConnection(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 CustomerID, CompanyName, ContactName, Address " +
      "FROM Customers " +
      "WHERE CustomerID = 'ALFKI'";

    // create a SqlCommand object to hold the SELECT statement
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

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

    // create a SqlDataAdapter object
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();

    // set the SelectCommand property of the SqlAdapter object
    // to the SqlCommand object
    mySqlDataAdapter.SelectCommand = mySqlCommand;

    // 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
    mySqlConnection.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 Customers table");
    mySqlDataAdapter.Fill(myDataSet, "Customers");

    // get the DataTable object from the DataSet object
    DataTable myDataTable = myDataSet.Tables["Customers"];

    // 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
    mySqlDataAdapter.Fill(myDataSet, "Customers");

    // close the database connection using the Close() method
    // of the SqlConnection object
    mySqlConnection.Close();

  }

}