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("\nAdding 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("\nModifying 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("\nRemoving 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(); } }