Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Fri May 30 10:42:54 CDT 2008
Hi Max,
Yes, this is why I'm wondering what Gustav means :)
BTW, I do use multi-layered approach with a lot of custom classes and mainly
ObjectDataSource controls for ASP.NET web forms - works very well but it
needs one or another code generation tool: I did develop my own but maybe
you'd better purchase existing ones if you do not have time to develop your
own...
For the task like yours (assuming ObjectDataSource and custom classes are
used) I'd batch updates into transactions - that will need some more work
than built-in approach you're trying to use but when that custom approach is
used ((generated) custom classes + ObjectDataSource control + a few custom
coding) then you have control on every bit of your program and you're not
dependent on any new technologies MS will develop, and with some more
efforts you can have different backends SQL Servers or even MS Access for
different customers of the same application etc.
--
Shamil
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Friday, May 30, 2008 6:09 PM
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: Re: [dba-VB] Updating dataset from datagrid
It is the one that doesn't contain option 1 or option 2 <smile - am I right
Gustav?)
Max
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Friday, May 30, 2008 1:46 PM
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: Re: [dba-VB] Updating dataset from datagrid
Hi Gustav,
<<<
So go for option 3. (!)
>>>
What is option 3?
--
Shamil
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, May 30, 2008 11:21 AM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Updating dataset from datagrid
Hi Eric
> What I don't like about the approach in Option 1 is that you have to
hard-code the SELECT statement
I think all this SQLAdapter and SQLCommand mechanics are left-overs from old
.Net and ASP.
MS has done a tremendous work with the DataTable, DataTableAdapter, DAL
(Data Access Layer), and LINQ.
Shamil has posted several links for tutorials and I can highly recommend
looking these up.
Problem is, that if you google for code on various topics, the net is
flooded with old SqlThisAndThat quick-and-dirty code from ASP kiddies which
won't help you in the long run.
So go for option 3. (!)
/gustav
>>> "Eric Barro" <ebarro at verizon.net> 30-05-2008 03:25 >>>
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?
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com