V
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()
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()