Updating the Database | Beginning Visual C#supAND#174;/sup 2005

Now that you can read data from databases, how do you change it? This section provides a very simple example, again using just one table, and at the same time introduces a few new objects you will use later in the chapter.

All the actions you typically wish to perform on the database (updating, inserting, and deleting records) can be accomplished with the same pattern:

Fill a DataSet with the data from the database you wish to work with.
Modify the data held in the DataSet (update, insert, or delete records for example).
Once all the modifications are made, persist the DataSet changes back to the database.

You will see this theme recurring as you move through the examples — there is no need to worry about the exact SQL syntax for updating the database, say, and all the modifications to the data in the database can be performed at one time.

In the following Try It Out, you begin by looking at how to update data in a database before moving on to add and delete records.

Try It Out – Updating the Database

Imagine that one of your customers, Bottom-Dollar Markets, has changed its name to Acme, Inc. You need to change the company's name in your databases. Again, you use the SQL Server/MSDE version of the Northwind database.

Create a new console application called UpdatingData in the directory C:\BegVCSharp\ Chapter24.

Begin by adding the using directives for the ADO.NET classes you will be using:

#region Using Directives using System; using System.Data;            // Use ADO.NET namespace using System.Data.SqlClient;  // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion

Add the following code to the Main() method:

static void Main(string[] args)  { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); // Create DataAdapter object for update and other operations SqlDataAdapter thisAdapter = new SqlDataAdapter(  "SELECT CustomerID, CompanyName FROM Customers", thisConnection); // Create CommandBuilder object to build SQL commands SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); // Create DataSet to contain related data tables, rows, and columns DataSet thisDataSet = new DataSet(); // Fill DataSet using query defined previously for DataAdapter thisAdapter.Fill(thisDataSet, "Customers"); // Show data before change Console.WriteLine("name before change: {0}",  thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]); // Change data in Customers table, row 9, CompanyName column thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme, Inc."; // Call Update command to mark change in table thisAdapter.Update(thisDataSet, "Customers"); Console.WriteLine("name after change: {0}", thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

Running the program produces the output shown in Figure 24-7.

Figure 24-7

How It Works

The first part of the program is similar to the previous SQL Server example; you create a connection object using a connection string:

SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" +  "Database=northwind");

Then you create a SqlDataAdapter object with the next statement:

SqlDataAdapter thisAdapter = new SqlDataAdapter(      "SELECT CustomerID, CompanyName FROM Customers", thisConnection);

Next, you want to create the correct SQL statements to update the database — you don't have to do this yourself, the SqlCommandBuilder will take care of this:

SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

Note that you pass thisAdapter to the SqlCommandBuilder constructor as an argument. The correct SQL commands are generated and associated with the passed data adapter by the constructor when the SqlCommandBuilder object is created. A bit later in the chapter you look at different SQL statements; but for now the SQL has been taken care of for you.

Now you create your illustrious DataSet object and fill it with data:

DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Customers");

In this case, it is the Customers table you want, so you call the associated DataTable in the DataSet by the same name. Now that the DataSet has been filled, you can access the individual rows and columns.

Before you change the data, you output a before picture of the data you want to change:

Console.WriteLine("name before change: {0}",      thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);

What are you doing here? You are printing the value in the CompanyName column in the row with index number nine in the Customers table. This whole line outputs the following:

name before change: Bottom-Dollar Markets

You're cheating a little bit here; you just happen to know that you are interested in the row with index number nine (which is actually the tenth row since the indexer is zero-based — the first row is Rows[0]). In a real program, rather than an example, you would have probably put a qualifier in your SQL query to select just the rows you were interested in, rather than having to know to go to the row with an index number of nine. In the next example I discuss how to find only the rows you are interested in.

Another way to understand what is going on with all of this is to look at an equivalent example that breaks out each separate object in the expression:

 // Example using multiple objects DataTable customerTable = thisDataSet.Tables["Customers"]; DataRow rowTen = customerTable.Rows[9]; object companyName = rowTen["CompanyName"]; Console.WriteLine("name before change: {0}", companyName);

In this example, you declare customerTable as a DataTable and assign the Customers table from the Tables property of thisDataSet. You declare rowTen as a DataRow and to it you assign the tenth element of the Rows property of customerTable. Finally, you declare companyName as an object and use the indexer property of rowTen to assign the CompanyName field to it.

This example helps you follow the process as you follow the chain of related objects, but it is often simpler to use the one-line expression, which gives the same result:

Console.WriteLine("name before change: {0}",                   thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);

If the code using multiple objects is more understandable to you, by all means use this method. For a one-time reference like this one, it is potentially inefficient to create variables for each object and assign to them every time; however, if the objects are going to be reused the multiple-object method may be more efficient. The compiler's optimizer may compensate for any inefficiency in one way of coding over another; therefore it's often best to code in the most readable manner.

Back to the example — you've displayed the value of the column before you make a change, so now let's make a change to the column. To change the value of a DataColumn, simply assign to it, as in the next line of the example:

thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme, Inc.";

This line changes the value of the CompanyName column in the row with index number nine of Customers to "Acme, Inc.".

Note

However this change only changes the value of the column in the DataSet in memory, not in the database itself.

The DataSet, DataTable, DataRow, and DataColumn are in-memory representations of the data in the table. To update the database, you need to call the Update() method.

Update() is a method of the data adapter object. To call it, specify the DataSet you want the update to be based on and the name of the DataTable in the DataSet to update. It's important that the DataTable name ("Customers") match the one you used when calling the Fill() method previously:

thisAdapter.Update(thisDataSet, "Customers");

The Update() method automatically goes through the rows in the DataTable to check for changes that need to be made to the database. Each DataRow object in the Rows collection has a property, RowState, that tracks whether this row is deleted, added, modified, or is unchanged. Any changes made are reflected in the database.

Now you confirm the change by printing out the after state of the data:

Console.WriteLine("name after change: {0}",      thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);

That's all there is to it!

Before moving on, let's have a quick reminder of the new characters you met here:

SqlCommandBuilder: The SqlCommandBuilder object takes care of the correct SQL statements for updating the database — you don't have to craft these statements ourselves.
SqlDataAdapter.Update(): This method goes through the rows in a DataTable to check for changes that need to be made to the database. Each DataRow object in the Rows collection has a property, RowState,, tracking whether this row is deleted, added, modified, or is unchanged. Any changes made are reflected in the database.

These, of course, are the SQL Server provider versions — there are corresponding OLE DB provider versions that work in the same way.

Adding Rows to the Database

In the previous example you updated values in existing rows, and your next step is to add an entirely new row. Your procedure to add a new record to the database involves, exactly like the update example earlier, adding a new row to an existing DataSet (this is where most of the work is required), and then persisting this change back to the database.

The process for adding a new row to the database is straightforward:

Create a new DataRow.
Populate it with some data.
Add it to the Rows collection of the DataSet.
Persist this change back to the database by calling the Update() method of the data adapter.

Sounds like a perfectly sensible scheme. In the following Try It Out, you see exactly how it's done.

Try It Out – Adding Rows

Follow the steps below to create the AddingData example in Visual Studio 2005.

Create a new console application called AddingData in the directory C:\BegVCSharp\ Chapter24.

Begin by adding the usual using directives for the ADO.NET classes, you will be using:

#region Using Directives using System; using System.Data;            // Use ADO.NET namespace using System.Data.SqlClient;  // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion

Now add the following code to the Main() method:

static void Main(string[] args)  { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); // Create DataAdapter object for update and other operations SqlDataAdapter thisAdapter = new SqlDataAdapter(  "SELECT CustomerID, CompanyName FROM Customers", thisConnection); // Create CommandBuilder object to build SQL commands SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); // Create DataSet to contain related data tables, rows, and columns DataSet thisDataSet = new DataSet(); // Fill DataSet using query defined previously for DataAdapter thisAdapter.Fill(thisDataSet, "Customers"); Console.WriteLine("# rows before change: {0}", thisDataSet.Tables["Customers"].Rows.Count); DataRow thisRow = thisDataSet.Tables["Customers"].NewRow(); thisRow["CustomerID"] = "ZACZI"; thisRow["CompanyName"] = "Zachary Zithers Ltd."; thisDataSet.Tables["Customers"].Rows.Add(thisRow); Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Customers"].Rows.Count); // Call Update command to mark change in table thisAdapter.Update(thisDataSet, "Customers"); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

On executing, the output of this example is as shown in Figure 24-8.

Figure 24-8

How It Works

The lines of interest here are the lines between the thisAdapter.Fill() and thisAdapter.Update() method calls.

First, to see the before picture you introduce a new property of the Rows collection: Count. This gives you a count of how many rows are in this table:

Console.WriteLine("# rows before change: {0}",      thisDataSet.Tables["Customers"].Rows.Count);

Next, you create the new row object, using the NewRow() method of the DataTable object:

DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();

Note that this creates a new row object using the same columns as the Customers table, but does not actually add it to the DataSet; you need to assign some values to the columns before that can be done:

thisRow["CustomerID"] = "ZACZI"; thisRow["CompanyName"] = "Zachary Zithers Ltd.";

Now, you can actually add the row using the Add() method of the Rows collection:

thisDataSet.Tables["Customers"].Rows.Add(thisRow);

If you check the Count property again after calling Add(), you see that you have indeed added one row:

Console.WriteLine("# rows after change: {0}",                   thisDataSet.Tables["Customers"].Rows.Count);

The output shows 92 rows, one more than the before change output. As with the previous example, the call to Update() is needed to actually add the new row to the database on disk:

thisAdapter.Update (thisDataSet, "Customers");

Remember, the DataSet is an in-memory, disconnected copy of the data; it is the DataAdapter which is actually connected to the database on disk and, therefore, its Update() method needs to be called to synchronize the in-memory data in the DataSet with the database on disk.

If you look at the table in the VS2005 Server Explorer after executing this program, you can see that you have indeed successfully added a row by scrolling to the bottom of the table display, as shown in Figure 24-9.

image from book
Figure 24-9

Notice that only the Customer ID and Company Name columns are filled, since that's all you used in your program. The remaining columns are blank (actually, they contain the value NULL in SQL terms). You might think filling in, say, Contact Name is simply a matter of adding the line to the code:

thisRow["ContactName"] = "Zylla Zithers";

However, this is not the only step you must perform. Recall that when you made the original query, you built the DataSet specifying just two columns CustomerID and CompanyName:

SqlDataAdapter thisAdapter = new SqlDataAdapter(      "SELECT CustomerID, CompanyName FROM Customers", thisConnection);

The reference to ContactName would cause an error, because there is no such column in the DataSet that you built. You could rectify this by adding the ContactName column to the original SQL query:

 SqlDataAdapter thisAdapter = new SqlDataAdapter( "SELECT CustomerID, ContactName, CompanyName FROM Customers", thisConnection);

Or you could select all the columns from Customers using this command:

 SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection);

The asterisk (*) in a SQL SELECT command is a shorthand for all the columns in the table; with this change, you can add values for any of the columns in the database. However, getting all the columns when you are only working with two or three is inefficient; this is something you should generally avoid.

Finding Rows

If you tried to run the previous example more than once, you would have seen a message like the one shown in Figure 24-10.

Figure 24-10

This indicates that the Add() failed because it would have created a duplicate row. The definition of the Customers table requires that the CustomerID field contain unique values, which is required when a column is designated the primary key. The value "ZACZI" was already present when you tried to run the code for the second time, because it was placed in the table the first time that you ran the sample.

Let's change the logic so that you search for the row first before you try to add it. the DataTable Rows collection provides a method called Find() that is very useful for this purpose. In the following Try It Out, you rewrite the logic surrounding your row addition to use Find() instead of counting rows.

Try It Out – Finding Rows

Follow these steps to create FindingData example in Visual Studio 2005:

Create a new console application called FindingData in the directory C:\BegVCSharp\ Chapter24.

Begin by adding the usual using directives for the ADO.NET classes you will be using:

#region Using Directives using System; using System.Data;            // Use ADO.NET namespace using System.Data.SqlClient;  // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion

Now add the following code to the Main() method:

static void Main(string[] args)  { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); // Create DataAdapter object for update and other operations SqlDataAdapter thisAdapter = new SqlDataAdapter(  "SELECT CustomerID, CompanyName FROM Customers", thisConnection); // Create CommandBuilder object to build SQL commands SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); // Create DataSet to contain related data tables, rows, and columns DataSet thisDataSet = new DataSet(); // Fill DataSet using query defined previously for DataAdapter thisAdapter.Fill(thisDataSet, "Customers"); Console.WriteLine("# rows before change: {0}", thisDataSet.Tables["Customers"].Rows.Count); // Set up keys object for defining primary key DataColumn[] keys = new DataColumn[1]; keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"]; thisDataSet.Tables["Customers"].PrimaryKey = keys; DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI"); if (findRow == null)  { Console.WriteLine("ZACZI not found, will add to Customers table"); DataRow thisRow = thisDataSet.Tables["Customers"].NewRow(); thisRow["CustomerID"] = "ZACZI"; thisRow["CompanyName"] = "Zachary Zithers Ltd."; thisDataSet.Tables["Customers"].Rows.Add(thisRow); if ((findRow =  thisDataSet.Tables["Customers"].Rows.Find("ZACZI")) != null) { Console.WriteLine("ZACZI successfully added to Customers table"); } }  else  { Console.WriteLine("ZACZI already present in database"); } thisAdapter.Update(thisDataSet, "Customers"); Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Customers"].Rows.Count); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

How It Works

The beginning of the program up to the Fill() method call is the same as previous examples. You use the Count property to output the number of rows currently existing, then proceed to use Find() to check that the row you want to add is already present.

Before you can use Find() you need to set up a primary key. The primary key is what you will use when searching; it is made of one or more of the columns of the table and contains a value or set of values that uniquely identifies this particular row in the table, so that when you search by the key you will find one and only one row. the Customers table in the Northwind database uses the CustomerID column as its primary key:

DataColumn[] keys = new DataColumn[1]; keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"]; thisDataSet.Tables["Customers"].PrimaryKey = keys;

First, you create a DataColumn array — since the key can consist of one or more columns, an array is the natural structure to use; you call your DataColumn array keys. Next, you assign the first element of the keys array, keys[0], to the CustomerID column in your Customers table. Finally, you assign keys to the PrimaryKey property of the Customers DataTable object.

Alternatively, it is possible to load primary key information directly from the database, which is not done by default. You can explicitly tell ADO.NET to load the primary key information by setting the DataAdapter MissingSchemaAction property before filling the DataSet, as follows:

 thisAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; thisAdapter.Fill(thisDataSet, "Customers");

This accomplishes the same primary key setup by initializing the PrimaryKey property of the DataTable implicitly.

In any case, now you're ready to find a row!

DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI");

Find() returns a DataRow, so you set up a DataRow object named findRow to get the result. Find() takes a parameter, which is the value to look up; this can be an array of objects for a multicolumn primary key, but in this case with only one primary key column, you need just one value which you pass as a string containing the value ZACZI — this is the CustomerID you want to look up.

If Find() locates a matching row, it returns the DataRow matching that row; if it does not find a match, it returns a null reference, which you can check for:

if (findRow == null)  {    Console.WriteLine("ZACZI not found, will add to Customers table");        DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();    thisRow["CustomerID"] = "ZACZI";    thisRow["CompanyName"] = "Zachary Zithers Ltd.";    thisDataSet.Tables["Customers"].Rows.Add(thisRow);    if ((findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI")) != null)    {       Console.WriteLine("ZACZI successfully added to Customers table");    } } else {    Console.WriteLine("ZACZI already present in database"); }

If findRow is null, you go ahead and add the row as in the previous example. Just to make sure that the Add() was successful, you do a Find() again immediately after the add operation to prove that it worked.

As mentioned at the start of this section, this version using Find() is repeatable; you can run it multiple times without errors. However, it never executes the Add() code once the "ZACZI" row is in the database. Let's look at how to make it repeat that part of the program also.

Deleting Rows

Once you can add rows to the DataSet and to the database, it is logical to follow with the opposite action, removing rows.

The DataRow object has a Delete() method that deletes the current row. The following Try It Out changes the sense of the if statement on findRow so that you test for findRow not equal to null (in other words, the row you were searching for was found). Then you remove the row by calling Delete() on findRow.

Try It Out – Deleting Rows

Follow these steps to create the DeletingData example in Visual Studio 2005:

Create a new console application called DeletingData in the directory C:\BegVCSharp\ Chapter24.

As usual, begin by adding the using directives for the ADO.NET classes you will be using:

#region Using Directives using System; using System.Data;            // Use ADO.NET namespace using System.Data.SqlClient;  // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion

Now add the following code to the Main() method:

static void Main(string[] args)  { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); // Create DataAdapter object for update and other operations SqlDataAdapter thisAdapter = new SqlDataAdapter(  "SELECT CustomerID, CompanyName FROM Customers", thisConnection); // Create CommandBuilder object to build SQL commands SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); // Create DataSet to contain related data tables, rows, and columns DataSet thisDataSet = new DataSet(); // Fill DataSet using query defined previously for DataAdapter thisAdapter.Fill(thisDataSet, "Customers"); Console.WriteLine("# rows before change: {0}", thisDataSet.Tables["Customers"].Rows.Count); // Set up keys object for defining primary key DataColumn[] keys = new DataColumn[1]; keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"]; thisDataSet.Tables["Customers"].PrimaryKey = keys; DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI"); if (findRow != null)  { Console.WriteLine("ZACZI already in Customers table"); Console.WriteLine("Removing ZACZI  . . ."); findRow.Delete(); thisAdapter.Update(thisDataSet, "Customers"); } Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Customers"].Rows.Count); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

How It Works

The code to create the DataSet and the data adapter objects is standard — you've seen it before several times in this chapter so you won't go through it again.

The difference between this code and the previous example is that if the row is found, it is deleted! Note that when Delete() is called it doesn't remove the row in the database until Update is called to commit the change.

Note

The Delete() method doesn't actually delete a row, it just marks it for deletion.

Each DataRow object in the Rows collection has a property, RowState, that tracks whether this row is deleted, added, modified, or is unchanged. the Delete() method sets the RowState of the row to Deleted, and then Update() deletes any rows it finds in the Rows collection marked as Deleted from the database.

A word of caution about calling the AcceptChanges() method of the DataSet after Delete() — doing so will remove the row from the DataSet, which means that there will be no effect on the row in the actual database, because Update() acts only on the rows it finds in the Rows collection, and a missing row is simply ignored.

This same issue applies to the Remove() method; call this only if you want to remove rows from the Rows collection of the DataSet, but not from the database itself.