Using Strongly Typed DataSet Objects

Chapter 10

Submitting Updates to Your Database

When you believe in things that you don't understand, then you suffer. Superstition ain't the way.

Stevie Wonder

Although Stevie Wonder probably wasn't talking about submitting updates, the quote is still relevant to the topic. ADO.NET gives database programmers unprecedented power and control over submitting updates. However, based on the questions I've handled personally on internal and external newsgroups and at conferences during the .NET beta, I'd say that few developers really understand how to effectively wield this control and power.

So many of the ADO.NET code snippets I've seen rely on the CommandBuilder object to generate updating logic. Sometimes the code snippet comes with a warning that says you should generate your own updating logic instead, but those comments rarely explain why or how this is done.

How many times have you asked someone how they got their code to work only to have them shrug, smile, and say, "It just works"? That's the sort of superstition I want to dispel in this chapter and the following chapter.

	I've actually seen a technical support organization adopt "It just works" as their slogan. Sad but true.

The more you understand how you can use ADO.NET to submit updates, the more comfortable you'll become generating your own updating logic and/or submitting updates via stored procedures. This chapter will help you understand how to use a DataAdapter to submit the pending changes from your DataSet to your database. Along the way, you'll also learn how and when to use tools to save time without sacrificing performance or control.

If you've been reading the chapters of this book in sequence, you should already be comfortable creating untyped and strongly typed DataSet objects to store the data returned by DataAdapter objects. You should also be comfortable modifying the contents of a DataSet. This chapter will help you understand the basics of using DataAdapter objects to submit the changes stored in your DataSet to your database.

Let's look at an order from the sample Northwind database. Figure 10-1 shows the query issued in SQL Server Query Analyzer to retrieve information for the order. For the sake of argument, let's say the customer calls and wants to change the order. Tofu isn't selling, but bottles of hot sauce are flying off the shelves and people keep asking for chai tea.

Figure 10-1

Contents of an order in the Northwind database.

Thanks to the knowledge you gained in Chapter 5, you know how to fetch the results of a query into a DataSet. You can use that knowledge to build an application that allows the user to fetch the customer's order into a DataSet. Based on what you learned in Chapter 6, you can enable your application to modify the data in the DataSet per the customer's instructions. However, as I've noted, changing the contents of a DataSet doesn't change the corresponding rows in the database.

In Chapter 5, you also learned that the DataAdapter exposes an Update method that you can use to submit pending changes to your database. So, you can build an application that uses code such as this to submit the changes to the order:

Visual Basic .NET

'Retrieve the contents of the order into a DataTable. Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _          "FROM [Order Details] WHERE OrderID = 10503 " & _          "ORDER BY ProductID" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim tbl As New DataTable("Order Details") da.Fill(tbl) 'Modify the contents of the order. tbl.Rows(0).Delete() tbl.Rows(1)("Quantity") = CShort(tbl.Rows(1)("Quantity")) * 2 tbl.Rows.Add(New Object() {10503, 1, 24, 18}) 'Submit the pending changes. Try     da.Update(tbl)     Console.WriteLine("Successfully submitted new changes") Catch ex As Exception     Console.WriteLine("Call to DataAdapter.Update " & _                       "threw exception:" & vbCrLf & ex.Message) End Try

Visual C# .NET

//Retrieve the contents of the order into a DataTable. string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +          "FROM [Order Details] WHERE OrderID = 10503 " +          "ORDER BY ProductID"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataTable tbl = new DataTable("Order Details"); da.Fill(tbl); //Modify the contents of the order. tbl.Rows[0].Delete(); tbl.Rows[1]["Quantity"] = (short) (tbl.Rows[1]["Quantity"]) * 2; tbl.Rows.Add(new object[] {10503, 1, 24, 18}); //Submit the pending changes. try {     da.Update(tbl);     Console.WriteLine("Successfully submitted new changes"); } catch (Exception ex) {     Console.WriteLine("Call to DataAdapter.Update threw exception:\n"                       + ex.Message); }

This code will successfully compile, but it will not successfully submit the changes to the order to your database. Instead, you'll receive an exception that says, "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

Exceptions such as these confused many developers during the Microsoft .NET Framework beta. Previous data access technologies such as ADO include features that let you submit changes automatically. With ADO.NET, you can submit changes using the DataAdapter object, but the DataAdapter does not automatically include the logic required to submit updates.

So how do you add the necessary updating logic to your ADO.NET DataAdapter? You have three basic options: you can write your own code, ask ADO.NET to generate the updating logic for you, or rely on a code generation tool such as the Visual Studio .NET Data Adapter Configuration Wizard.

This chapter will cover all three of these options and explain the pros and cons of each.