Sahil
Thu Jan 05 14:46:23 CST 2006
LOL :), or I can just sneak in to be served.
SM
"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()
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>