|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Updating - let me count the waysIn 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() 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." 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 ---------------------------------------------------------------------------- Show quote "Vayse" <vayse@nospam.nospam> wrote in message news:uvmuFGJEGHA.312@TK2MSFTNGP09.phx.gbl... > 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() > > > > > > > > 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 Show quote "Sahil Malik [MVP C#]" <contactmethrumyblog@nospam.com> wrote in message news:eLEd89VEGHA.3100@tk2msftngp13.phx.gbl... > 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" <vayse@nospam.nospam> wrote in message > news:uvmuFGJEGHA.312@TK2MSFTNGP09.phx.gbl... >> 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() >> >> >> >> >> >> >> >> > > 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 ---------------------------------------------------------------------------- Show quote "Vayse" <vayse@nospam.nospam> wrote in message news:OUAbE0eEGHA.3100@tk2msftngp13.phx.gbl... > 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#]" <contactmethrumyblog@nospam.com> wrote in message > news:eLEd89VEGHA.3100@tk2msftngp13.phx.gbl... >> 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" <vayse@nospam.nospam> wrote in message >> news:uvmuFGJEGHA.312@TK2MSFTNGP09.phx.gbl... >>> 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() >>> >>> >>> >>> >>> >>> >>> >>> >> >> > > 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 Show quote "Sahil Malik [MVP C#]" <contactmethrumyblog@nospam.com> wrote in message news:uNhldpgEGHA.3100@tk2msftngp13.phx.gbl... > 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" <vayse@nospam.nospam> wrote in message > news:OUAbE0eEGHA.3100@tk2msftngp13.phx.gbl... >> 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#]" <contactmethrumyblog@nospam.com> wrote in message >> news:eLEd89VEGHA.3100@tk2msftngp13.phx.gbl... >>> 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" <vayse@nospam.nospam> wrote in message >>> news:uvmuFGJEGHA.312@TK2MSFTNGP09.phx.gbl... >>>> 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() >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>> >>> >> >> > > LOL and I thought Diarmuid was a dutch name from Amsterdam or sump'n :)
SM Show quote "Vayse" <vayse@nospam.nospam> wrote in message news:evp8qAhEGHA.3092@TK2MSFTNGP10.phx.gbl... > 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#]" <contactmethrumyblog@nospam.com> wrote in message > news:uNhldpgEGHA.3100@tk2msftngp13.phx.gbl... >> 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" <vayse@nospam.nospam> wrote in message >> news:OUAbE0eEGHA.3100@tk2msftngp13.phx.gbl... >>> 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#]" <contactmethrumyblog@nospam.com> wrote in message >>> news:eLEd89VEGHA.3100@tk2msftngp13.phx.gbl... >>>> 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" <vayse@nospam.nospam> wrote in message >>>> news:uvmuFGJEGHA.312@TK2MSFTNGP09.phx.gbl... >>>>> 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() >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > 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. Show quote "Sahil Malik [MVP C#]" <contactmethrumyblog@nospam.com> wrote in message news:enORHFiEGHA.1508@TK2MSFTNGP15.phx.gbl... > LOL and I thought Diarmuid was a dutch name from Amsterdam or sump'n :) > > SM > > > > "Vayse" <vayse@nospam.nospam> wrote in message > news:evp8qAhEGHA.3092@TK2MSFTNGP10.phx.gbl... >> 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#]" <contactmethrumyblog@nospam.com> wrote in message >> news:uNhldpgEGHA.3100@tk2msftngp13.phx.gbl... >>> 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" <vayse@nospam.nospam> wrote in message >>> news:OUAbE0eEGHA.3100@tk2msftngp13.phx.gbl... >>>> 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#]" <contactmethrumyblog@nospam.com> wrote in >>>> message news:eLEd89VEGHA.3100@tk2msftngp13.phx.gbl... >>>>> 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" <vayse@nospam.nospam> wrote in message >>>>> news:uvmuFGJEGHA.312@TK2MSFTNGP09.phx.gbl... >>>>>> 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() >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > LOL :), or I can just sneak in to be served.
SM Show quote "Vayse" <vayse@nospam.nospam> wrote in message news:%23AaaxLiEGHA.1088@TK2MSFTNGP10.phx.gbl... >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#]" <contactmethrumyblog@nospam.com> wrote in message > news:enORHFiEGHA.1508@TK2MSFTNGP15.phx.gbl... >> LOL and I thought Diarmuid was a dutch name from Amsterdam or sump'n :) >> >> SM >> >> >> >> "Vayse" <vayse@nospam.nospam> wrote in message >> news:evp8qAhEGHA.3092@TK2MSFTNGP10.phx.gbl... >>> 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#]" <contactmethrumyblog@nospam.com> wrote in message >>> news:uNhldpgEGHA.3100@tk2msftngp13.phx.gbl... >>>> 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" <vayse@nospam.nospam> wrote in message >>>> news:OUAbE0eEGHA.3100@tk2msftngp13.phx.gbl... >>>>> 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#]" <contactmethrumyblog@nospam.com> wrote in >>>>> message news:eLEd89VEGHA.3100@tk2msftngp13.phx.gbl... >>>>>> 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" <vayse@nospam.nospam> wrote in message >>>>>> news:uvmuFGJEGHA.312@TK2MSFTNGP09.phx.gbl... >>>>>>> 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() >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics