Why use data adaptor functionality ?

[HaggardPete]

Well Im fairly new to .NET but it seems to me that there is little/
no advantage to using the update, delete, insert methods of a data
adaptor at all. The commandbuilder seems not to work in many
real-world scenarios so you have to code the various commands
anyway. And if (for example) the update command is invalid the
update fails without a warning which makes debugging a nightmare.

So why bother with the adaptor?. Just issue the command directly. Its
easy to write a one line function call to process an SQL string -
and you can see whats happening.

RunSQL(OleDbConnection1, "UPDATE payments SET removed=1 WHERE pay_key
= " & Str(nRef))

Where RunSQL is along the lines of :

Public Function RunSQL(ByVal cn As OleDb.OleDbConnection, ByVal
sql As String) As Integer
'
'Run command text against given connection. Why doesnt .net
just do this !
'

Try
Dim sqlCmd As New OleDb.OleDbCommand()
RunSQL = 0

If cn.State = ConnectionState.Open Then
cn.Close()
End If
cn.Open()
With sqlCmd
.CommandType = CommandType.Text
.CommandText = sql
.Connection = cn
End With
RunSQL = sqlCmd.ExecuteNonQuery()

Catch e As Exception

MsgBox("Database command " & sql & " ended with
the error " & vbCrLf & e.Message & vbCrLf & vbCrLf
& e.StackTrace)

Finally
cn.Close()
End Try
End Function

I would be more than happy to be convinced otherwise but right now
I'm just struggling to see the point of data adaptors at all ...

 

[Jon Skeet [C# MVP]]

Well Im fairly new to .NET but it seems to me that there is little/
no advantage to using the update, delete, insert methods of a data
adaptor at all. The commandbuilder seems not to work in many
real-world scenarios so you have to code the various commands
anyway. And if (for example) the update command is invalid the
update fails without a warning which makes debugging a nightmare.

So why bother with the adaptor?. Just issue the command directly. Its
easy to write a one line function call to process an SQL string -
and you can see whats happening.

RunSQL(OleDbConnection1, "UPDATE payments SET removed=1 WHERE pay_key
= " & Str(nRef))

Okay, so you've immediately opened yourself up to a possible SQL
injection attack by not using parameters, to start with.

Using parameters, you then need to fill all the parameters in from your
DataRow - which DataAdapter does for you.

You then need to make sure that you *have* actually
deleted/updated/added a single entry.

I agree that command builders are pretty much a waste of time, but if
you set the commands yourself, DataAdapter works pretty well in my
experience.

 

[William Ryan eMVP]

For one thing you don't have much choice with concurrency although that's
something you can still easily implement using it your way. I think using
an adapter is much cleaner and calling fill each time for one thing. Like
John mentions you are using dynamic sql and this is what one rather
opinionated chap thinks of Dynamic Sql
http://www.knowdotnet.com/articles/dynamisql.html But there are a lot of
other features. Rowstate is an important issue here and using an adapter,
you can specify an update on just rows that have been added, deleted etc.
You have the ability to do stuff like select AcceptChangesDuringFill to
false http://www.knowdotnet.com/articles/datasetmerge.html which will let
you transfer data around to different sources. You can specify Column
mapping for your parameters and although you can still do it your way, it's
infinitely easier to read and write. You have two events RowUpdating and
RowUpdated which provided quite useful event handling ability. You can also
pass different datatables to it and it will update your data for you, and
you can specify rules for this via the MissingSchemaaction property for
instance.

Over the long haul you'll be a lot more productive and you'll write code
that's a lot easier to use and read. Do you really want to write a loop
each time for each datatable so you can loop through the tables and build
your statement? Compared to defining a command and just calling update?
It's definitely a timesaver.

HTH,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp

Well Im fairly new to .NET but it seems to me that there is little/
no advantage to using the update, delete, insert methods of a data
adaptor at all. The commandbuilder seems not to work in many
real-world scenarios so you have to code the various commands
anyway. And if (for example) the update command is invalid the
update fails without a warning which makes debugging a nightmare.

So why bother with the adaptor?. Just issue the command directly. Its
easy to write a one line function call to process an SQL string -
and you can see whats happening.

RunSQL(OleDbConnection1, "UPDATE payments SET removed=1 WHERE pay_key
= " & Str(nRef))

Where RunSQL is along the lines of :

Public Function RunSQL(ByVal cn As OleDb.OleDbConnection, ByVal
sql As String) As Integer
'
'Run command text against given connection. Why doesnt .net
just do this !
'

Try
Dim sqlCmd As New OleDb.OleDbCommand()
RunSQL = 0

If cn.State = ConnectionState.Open Then
cn.Close()
End If
cn.Open()
With sqlCmd
.CommandType = CommandType.Text
.CommandText = sql
.Connection = cn
End With
RunSQL = sqlCmd.ExecuteNonQuery()

Catch e As Exception

MsgBox("Database command " & sql & " ended with
the error " & vbCrLf & e.Message & vbCrLf & vbCrLf
& e.StackTrace)

Finally
cn.Close()
End Try
End Function

I would be more than happy to be convinced otherwise but right now
I'm just struggling to see the point of data adaptors at all ...

 

[HaggardPete]

Thanks for the input. I guess by injection attack you refer to
interception of the SQL string and insertion of other values - I
could be totally wrong. In any event, probably crucial in some
applications but on a secure corporate network - well I can think of
many more immediate risks.

Its when you come to sucess / failure of the update that my concerns
really started. It seems that if the update command is invalid the
data adaptor accepts the CommandString but does not report a failure
when the AcceptChanges method is called. Bizzare !

 

[Jon Skeet [C# MVP]]

Thanks for the input. I guess by injection attack you refer to
interception of the SQL string and insertion of other values - I
could be totally wrong. In any event, probably crucial in some
applications but on a secure corporate network - well I can think of
many more immediate risks.

Consider the case where nRef in your example was

'' OR pay_key IS NOT NULL

Oops... goodbye table.

Its when you come to sucess / failure of the update that my concerns
really started. It seems that if the update command is invalid the
data adaptor accepts the CommandString but does not report a failure
when the AcceptChanges method is called. Bizzare !

You shouldn't call AcceptChanges yourself at all - if you do, then
calling Update isn't going to do anything, as you won't have any
updates to send.

 

[William Ryan eMVP]

How did your IT guys secure your internal network against pissed off
employees? I almost thought that was a joke but realized it wasn't. I was
at a TechNet conference in Atlanta a few months ago and one of the people I
was talking with had a whole table wiped out (on their secure internal
network) by an intern who was playing around. He was a CS major and had
read about Injection attacks. He wanted to test it to see if it was really
possible and thought he was going against the test server. Seems he wasn't.
A mere three hours later, all was well.

I'd encourage you to take a step back at this point and look into the
adapter a bit mroe if you're going to use it. AcceptChanges will do nothing
of use in the context you described except ensure that nothing gets updated
in the db. The adapter loops through the rows in a table when you call
Update and checks the rowstate of each row. From there it maps the rowstate
to a command object (provided you have one) and ideally maps the column
values back to parameters. AcceptChanges changes to rowstate to each row to
unchanged and if any rows are marked deleted, it actually gets rid of them.
I show it in depth here
http://www.knowdotnet.com/articles/efficient_pt4.html Anyway if the dataset
doesn't ahve changes, calling update until the Y10K crisis won't do anything
in the way of updating the db. And calling acceptchanges will make sure
the dataset doesn't have any changes.
--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp

Thanks for the input. I guess by injection attack you refer to
interception of the SQL string and insertion of other values - I
could be totally wrong. In any event, probably crucial in some
applications but on a secure corporate network - well I can think of
many more immediate risks.

Its when you come to sucess / failure of the update that my concerns
really started. It seems that if the update command is invalid the
data adaptor accepts the CommandString but does not report a failure
when the AcceptChanges method is called. Bizzare !

 

[HaggardPete]

Hello William

I can see this isn’t a new topic. Your right, the first chap you
quote has some strong opinions !. I can see your point about
iterating through tables though I don't think much more code is
involved, given we have to build up the update commands etc anyway.
And I must have a look into MissingSchemaaction.

Still, you have inspired me to persevere. I will give the little
blighters one more shot.

An excellent reply. Thanks, Pete

 

[HaggardPete]

[quote="Consider the case where nRef in your example was

'' OR pay_key IS NOT NULL

Oops... goodbye table. [/quote]

A frightening prospect, However I’m just not convinced that using data
adapters will significantly protect against an attack of this
nature. After all if a malicious individual could intercept the
update command they could intercept the original query as well. In
either case the code you are questioning was intended to make a point
rather than provide a solution.

Another reply did make a good point about parameters protecting
against SQL delimiters etc in user data. The code needed to protect
against this has an overhead which might make the parameters which
come "free" with the data adapter seem attractive. I am going to
persist with data adapters for a while anyway

Many thanks for the input, Pete

 

[Jon Skeet [C# MVP]]

[quote="Consider the case where nRef in your example was

'' OR pay_key IS NOT NULL

Oops... goodbye table.

A frightening prospect, However I?m just not convinced that using data
adapters will significantly protect against an attack of this
nature. After all if a malicious individual could intercept the
update command they could intercept the original query as well. In
either case the code you are questioning was intended to make a point
rather than provide a solution.[/QUOTE]

No, the difference is that there's no need to get at your original real
command in this case - only the parameter that you give. There's
usually *much* more room for that to go wrong than for the code to be
broken into.

My point was to suggest that the solution is to use an UpdateCommand
and parameters, which is not open to the same kind of injection attack.