[dba-VB] Updating dataset from datagrid

I'm curious as to how you guys approach this scenario:

NOTE: This is web-specific (ie ASP.NET) and SQL server specific.

With the DataGrid web control you can bind it to a DataSet as the
DataSource. The user can then perform CRUD operations using the Edit,
Update, Cancel methods. What approach do you usually take?

Option 1: 

Use code that sends only changed data on the DataGrid to the SQL database
using the SQLCommandBuilder object?  This approach basically means that you
have to build in code that updates the DataSet's corresponding DataTable.

Code example:

'get the target datarow that the user is on
Dim targetRow as DataRow = ds.Tables("Clients").Rows(e.Item.ItemIndex)

'open it up for editing
targetRow.BeginEdit()

'shadow the textbox on the datagrid
Dim tb as TextBox = CType(e.Item.Cells(2).FindControl("tb_FirstName"),
TextBox)

'assign what the user typed in the datagrid textbox control to the
appropriate DataTable column
targetRow("FirstName") = tb.Text
'---you may have more columns you want to update here in which case you will
need to include them

'end edit mode
targetRow.EndEdit()

'invoke the GetChanges method to create a second DataSet
Dim dsChanges as DataSet = ds.GetChanges(DataRowState.Modified)

'merge the changes
dsTarget.Merge(UpdateClients(dsChanges), true)

'initialize the connection object
Dim con as SqlConnection = New
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connec
tionstring"))

'initialize the data adapter object
Dim daClients as SqlDataAdapter = New SqlDataAdapter("SELECT ClientID,
LastName, FirstName FROM Clients", con)

'build the update command
Dim cbClient as SqlCommandBuilder = New SqlCommandBuilder(daClients)

'invoke the Update method to update the table in the dataset
daClients.Update(ds, "Clients")


Option 2: 

Pass the parameters (fields to be updated) to a class that calls a method
which in turns calls a stored procedure that executes on the SQL database
using the parameters passed from the method. This approach basically means
that the record is saved and hence a connection to SQL server is needed at
the moment the user saves the record.

Code example:

Dim Cmd As New SqlCommand("UpdateClients", Conn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@FirstName",
CType(e.Item.Cells(2).FindControl("tb_FirstName"), TextBox).Text)
Cmd.Parameters.Add("@LastName",
CType(e.Item.Cells(2).FindControl("tb_LastName"), TextBox).Text)
'---you may have more columns you want to update here in which case you will
need to include them

Conn.Open()
Cmd.ExecuteNonQuery()
Conn.Close()


I've always used Option 2 and for the first time today I was able to get
Option 1 to work. What I don't like about the approach in Option 1 is that
you have to hard-code the SELECT statement in order to get SQLCommandBuilder
to work its magic. In other words I can't specify a stored procedure to call
(if someone knows how it can be done I'd appreciate knowing). In all my CRUD
operations I just invoke a method of the Data Helper class I created
specifically for that purpose and then pass the parameters as a Hashtable
and finally execute the query to run.

What do you guys use?