[dba-VB] Updating dataset from datagrid

Hi Charlotte and Eric

Exactly. It will even write pass-through queries for the asking.

/gustav

>>> cfoust at infostatsystems.com 30-05-2008 17:50 >>>
I haven't worked with ASP.Net for a while since our apps aren't
currently web-based (we have one but we built it several years ago).
However, our apps are built so the data tier works with either winforms
or webforms.  Our data entity classes are based on typed datasets, which
are based on tableadapters.  Once you build the data tier, it takes care
of the ugly details of when and how to write to the data source.  Most
of our code coesn't even care whether we're working with SQL Server or
Access as a back end because that's transparent to the UI project.

Charlotte Foust 

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com 
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Thursday, May 29, 2008 6:25 PM
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: [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("co
nnec
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?