Updating - let me count the ways

[Vayse]

There are several ways of updating a database through ADO.Net 2.
In my case, I'm interested in using queries to update my Access data.
I'm not sure what all the pros and cons are for each method.
Below I've listed what I know, I'd welcome some discussion. And some
correction on the 'terms' if required
For my examples, I have a table called Weeks. This table has a boolean
field, PastWeek. I wish to set this to true for all weeks prior to a
supplied week number.

Which one do you use? Whats the fastest to run?
Thanks
Vayse

__________________________________________________________________________
1) TableAdapter
Where one opens the xsd file, right clicks on the table adapter, and clicks
on Add Query.
For my example, I create a query called SetPastWeeks, as follows
UPDATE Weeks SET PastWeek = True WHERE (WeekID<= ?)

To use this in my code, I would do
Me.WeeksTableAdapter.SetPastWeeks(lNewWeekID)

Pros:
Easy to setup
Easy to test - you can run the query in the xsd window. (what is the correct
term for xsd window?)
Its easy to use

Cons:
I guess its slower, but I don't know for sure.
__________________________________________________________________________
2) Commands and Dymanic SQL

Using connection As New OleDbConnection(stConnection)
connection.Open()
Dim stSQL as string = "UPDATE Weeks SET PastWeek = True WHERE
WeekID<= " & lNewWeekID
Dim command As New OleDbCommand(stSQL, Connection)
Dim lRows = command.ExecuteNonQuery()
End Using

Pros/ Cons: I'm not sure!

__________________________________________________________________________
3) Commands and Access Query
That is, create the query in Access, then run it via your code. Now that I
think about it, I'm not sure how to pass a parameter to such a query!
Anyway, running it should be the same at the Dymanic SQL.

I don't know what advantages this has over method 2, but it can make your
code trickier to debug.
__________________________________________________________________________
4) DataAdapter

To keep the code short, I won't add the parameters to the command.

Dim dbConn As New OleDbConnection(stConnection)
dbConn.Open()

Dim stSQL As String = "SELECT * FROM Weeks"
Dim daWeeks As New OleDbDataAdapter(stSQL, dbConn)

stSQL = "UPDATE Weeks SET PastWeek = True WHERE WeekID <= 9"
Dim command As New OleDbCommand(stSQL, dbConn)
daWeeks.UpdateCommand = command
daWeeks.UpdateCommand.ExecuteNonQuery()

 

[Kevin Yu [MSFT]]

Hi Vayse,

Thanks for sharing your knowledge with all the people here. In this case, I
will choose the second to use a TableAdapter. Because here, you're using a
single UPDATE statement to achieve the job, using ExecuteNonQuery is the
most efficient way.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Sahil Malik [MVP C#]]

Vayse,

I agree with Kevin, your method #2 - ExecuteNonQuery is the best (also read
point #c below)

Here is a gist of the reasoning -
a) Access sucks eggs at extracting metadata/datatypes out of the db using
OleDbDataAdapter in a Fill Operation. All dataadapters have this behavior
coded into them - On a Fill operation the underlying db will be queried and
data types will be attempted to be put in the DataTable. So performance
blows - but Access != performance, Access == Ease Of Use & deployment. But
since to Update, you would have done a Fill at some point - the overhead is
just not justified in this scenario.
b) You know exactly the data you need to update, without having read the
data first. You don't need disconnected data for job - so why bother
complicating things with TableAdapter/DataAdapter/Dataset?
c) I want to add one thing - you are concatenating strings for your dynamic
SQL - BAD BAD BAD. Always use parameterized queries instead.



- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Vayse]

Thanks Sahil and Kevin for the replies.

b) If I already had a TableAdapter for other purposes, (say the form was
bound to it) - would I better off running the query that way?

c) Why is concatenating strings bad? I must admit, when I am doing complex
SQL, I use parameters. But I never realised one method was bad.

Thanks
Diarmuid

 

[Sahil Malik [MVP C#]]

Diarmuid,

b) -- If you wanna be lazy sure , we're just suggesting a technically
better solution. There is some argument that you will have code reuse when
using the TableAdapter, but frankly it's hard to comment on the value you
get out of that. Is that code reuse so important that you are willing to
accept a technically inferior solution? You have to weigh that yourself

c) Concatenating strings is -

a) Error Prone.
b) Hacker Prone (injection attack).
c) Inefficient.
d) Complicated and unreadable.
e) Inefficient usually.
f) ..more

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Vayse]

Ok, I'm convinced. Thanks.

And Diarmuid isn't my real name. Definitely not. No way would I break
company policy and use my real name on a public forum. No, not me.

Vayse

 

[Sahil Malik [MVP C#]]

LOL and I thought Diarmuid was a dutch name from Amsterdam or sump'n

SM

 

[Vayse]

A friend of a friend tells me its a traditional Irish name.
And in a completely unrelated matter, if you ever run your Essential ADO
..NET 2.0 course in Ireland, I'll be there!
Though it looks like you'll need to bring ID if you want to be served in the
pub.

 

[Sahil Malik [MVP C#]]

LOL , or I can just sneak in to be served.

SM