ebarro at verizon.net
Thu May 29 20:25:25 CDT 2008
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?
More information about the dba-VB mailing list