s INTRODUCING DATASETS AND DATA ADAPTERS in VB.NET

Encoding ECC200 in VB.NET s INTRODUCING DATASETS AND DATA ADAPTERS

cmd.Parameters.Add( _ "@country", _ SqlDbType.NVarChar, _ 15, _ "country")

Finally, you set the data adapter s InsertCommand property with the command to insert into the Employees table so it will be the SQL the data adapter executes when you call its Update method. You then called Update on the data adapter to propagate the change to the database. Here you added only one row, but since the SQL is parameterized, the data adapter looked for all new rows in the employees data table and submitted inserts for all of them to the database.

' Update database da.InsertCommand = cmd da.Update(ds, "employees")

Figure 8-8 shows the new row, and if you if you check with Database Explorer or the SSMSE, you ll see the row has been propagated to the database. Roy Beatty is now in the Employees table.

Try It Out: Propagating New Dataset Rows to a Data Source Let s again modify Listing 8-4 to delete a row from the database. 1. Add a new Console Application project named PersistDeletes to your 08 solution. 2. Replace the code in Module1.vb with the code in Listing 8-7.

Sub Main() ' Set up connection string Dim connString As String = _ "server = .\sqlexpress;" _ & "integrated security = true;" _ & "database = northwind" ' Set up query Dim qry As String = _ "select " _ & " * " _ & "from " _ & " employees " _ & "where " _ & " country = 'UK' " ' Set up DML Dim del As String = _ "delete from employees " _ & "where " _ & " employeeid = @employeeid "

' Create connection Dim conn As SqlConnection = New SqlConnection(connString) Try ' Create data adapter Dim da As SqlDataAdapter = New SqlDataAdapter(qry, conn) ' Create and fill dataset Dim ds As DataSet = New DataSet() da.Fill(ds, "employees") ' Get the data table reference Dim dt As DataTable = ds.Tables("employees")

' Delete Employees ' ' Create command Dim cmd As SqlCommand = New SqlCommand(del, conn)

' ' Map parameters ' cmd.Parameters.Add( _ "@employeeid", _ SqlDbType.Int, _ 4, _ "employeeid") ' ' ' Select employee to delete Dim filt As String = _ "firstname = 'Roy' " _ & "and " _ & "lastname = 'Beatty' " ' ' Delete employee from data table For Each row As DataRow In dt.Select(filt) row.Delete() Next ' ' Update database da.DeleteCommand = cmd da.Update(ds, "employees") ' Display data table For Each row As DataRow In dt.Rows Console.WriteLine( _ "{0} {1} {2}", _ row("firstname").ToString().PadRight(15), _ row("lastname").ToString().PadLeft(25), _ row("city") _ ) Next Catch e As Exception ' Display error Console.WriteLine("Error: " & e.ToString) Finally ' Close connection conn.Close() End Try End Sub End Module

3. Make this the startup project, and run it with Ctrl+F5. You should see the output in Figure 8-9.

How It Works You added a DELETE statement (and changed the name of the original query string variable from sql to del in order to clearly distinguish it from this statement):

' Set up DML Dim del As String = _ "delete from employees " _ & "where " _ & " employeeid = @employeeid "

You inserted the delete code ahead of the display, after creating a command and mapping a parameter:

' Delete Employees ' ' Create command Dim cmd As SqlCommand = New SqlCommand(del, conn) ' ' Map parameters ' cmd.Parameters.Add( _ "@employeeid", _ SqlDbType.Int, _ 4, _ "employeeid")

You selected the row to delete and deleted it. Actually, you selected all rows for employees named Roy Beatty, since you don t know (or care about) their employee IDs. Although you expect only one row to be selected, you use a loop to delete all the rows. (If you ran the PropagateInserts program multiple times, you d have more than one row that matches this selection criteria.)