/* Mastering Visual C# .NET by Jason Price, Mike Gunderloy Publisher: Sybex; ISBN: 0782129110 */ /* Example23_2.cs illustrates how to perform INSERT, UPDATE, and DELETE statements using ADO.NET */ using System; using System.Data; using System.Data.SqlClient; public class Example23_2 { public static void DisplayDataTable( SqlDataAdapter mySqlDataAdapter, DataSet myDataSet, DataTable myDataTable ) { // use the Clear() method of the DataSet object // to remove all the rows in the DataSet myDataSet.Clear(); // use the Fill() method of the SqlDataAdapter object // to synchronize any changes made to the database // with the DataSet object mySqlDataAdapter.Fill(myDataSet, "Customers"); // 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( SqlConnection mySqlConnection, SqlDataAdapter mySqlDataAdapter, DataSet myDataSet ) { Console.WriteLine(" Adding a new row with CustomerID of 'T1COM'"); // step 1: formulate a string containing the SQL statement string insertString = "INSERT INTO Customers (" + " CustomerID, CompanyName, ContactName, Address" + ") VALUES (" + " @CustomerID, @CompanyName, @ContactName, @Address" + ")"; // step 2: create a SqlCommand object to hold the SQL statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // step 3: set the CommandText property of the SqlCommand object to // the SQL string mySqlCommand.CommandText = insertString; // step 4: use the Add() method through the Parameters property // of the SqlCommand object to add the parameters to the SqlCommand // object mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30); mySqlCommand.Parameters.Add("@Address", SqlDbType.NVarChar, 60); // step 5: set the parameters to specified values using the // Value property mySqlCommand.Parameters["@CustomerID"].Value = "T1COM"; mySqlCommand.Parameters["@CompanyName"].Value = "T1 Company"; mySqlCommand.Parameters["@ContactName"].Value = "Jason Price"; mySqlCommand.Parameters["@Address"].Value = "1 Main Street"; // step 6: use the ExecuteNonQuery() method to run the // SQL statement mySqlCommand.ExecuteNonQuery(); } public static void ModifyRow( SqlConnection mySqlConnection, SqlDataAdapter mySqlDataAdapter, DataSet myDataSet ) { Console.WriteLine(" Modifying the new row"); // step 1: formulate the SQL statement string updateString = "UPDATE Customers " + "SET " + " CompanyName = @CompanyName, " + " ContactName = @ContactName, " + " Address = @Address " + "WHERE CustomerID = @CustomerID"; // step 2: create a SqlCommand object to hold the SQL statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // step 3: set the CommandText property of the SqlCommand object to // the SQL string mySqlCommand.CommandText = updateString; // step 4: use the Add() method through the Parameters property // to add the parameters mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30); mySqlCommand.Parameters.Add("@Address", SqlDbType.NVarChar, 60); // step 5: set the parameters to values using the Value property mySqlCommand.Parameters["@CustomerID"].Value = "T1COM"; mySqlCommand.Parameters["@CompanyName"].Value = "Widgets Inc."; mySqlCommand.Parameters["@ContactName"].Value = "John Smith"; mySqlCommand.Parameters["@Address"].Value = "1 Any Street"; // step 6: use the ExecuteNonQuery() method to run the // SQL statement mySqlCommand.ExecuteNonQuery(); } public static void RemoveRow( SqlConnection mySqlConnection, SqlDataAdapter mySqlDataAdapter, DataSet myDataSet ) { Console.WriteLine(" Removing the new row"); // step 1: formulate the SQL statement string deleteString = "DELETE FROM Customers " + "WHERE CustomerID = @CustomerID"; // step 2: create a SqlCommand object to hold the SQL statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // step 3: set the CommandText property of the SqlCommand object to // the SQL string mySqlCommand.CommandText = deleteString; // step 4: use the Add() method through the Parameters property // to add the parameter mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); // step 5: set the parameters to values using the Value property mySqlCommand.Parameters["@CustomerID"].Value = "T1COM"; // step 6: use the ExecuteNonQuery() method to run the // SQL statement mySqlCommand.ExecuteNonQuery(); } 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 rows from the Customers table string selectString = "SELECT CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "WHERE CustomerID IN ('ALFKI', 'T1COM')"; // 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 string dataTableName = "Customers"; Console.WriteLine("Retrieving a row from the Customers table"); mySqlDataAdapter.Fill(myDataSet, dataTableName); // get the DataTable object from the DataSet object DataTable myDataTable = myDataSet.Tables[dataTableName]; // display the rows in the DataTable object DisplayDataTable(mySqlDataAdapter, myDataSet, myDataTable); // add a new row AddRow(mySqlConnection, mySqlDataAdapter, myDataSet); DisplayDataTable(mySqlDataAdapter, myDataSet, myDataTable); // modify the new row ModifyRow(mySqlConnection, mySqlDataAdapter, myDataSet); DisplayDataTable(mySqlDataAdapter, myDataSet, myDataTable); // remove the new row RemoveRow(mySqlConnection, mySqlDataAdapter, myDataSet); DisplayDataTable(mySqlDataAdapter, myDataSet, myDataTable); // close the database connection using the Close() method // of the SqlConnection object mySqlConnection.Close(); } }