ADO.NET Update two tables using one SQLCommandBuilder

Hank1234

Can I use one Data Adapter and one Command Builder to update amny tables?
Currently in my data adapter I query two tables and fill them into two tables
in a data set. When I make a change to a record in the second table and call
the update method of the data adapter the command builders update command
text is for the first table. Can the command builder handle two tables?

Code example:

Dim oCOnn As New SqlConnection("Data Source=.;" & _
"Initial
Catalog=Databasename;" & _
"Integrated Security=SSPI")
Dim oAdt As New SqlDataAdapter("Select * from location; select *
from vendor", oCOnn)
Dim oSet As New DataSet
Dim ocommbuild As New SqlCommandBuilder(oAdt)

oAdt.Fill(oSet)
oSet.Tables(1).Rows(1).Item("Notes") = "Hello"
Debug.WriteLine(ocommbuild.GetUpdateCommand.Comman dText)

oAdt.Update(oSet.Tables(1))

Jul 26 '05 #1

W.G. Ryan MVP

Just use two separate commandbuilders, one for each query. Or, you can
specify a different select query for it and then call update- but it's
easier to just use two of them. Also, you probably want to use a
DataRelation on those tables if they are related in any form, just to ensure
that nothing happens client side that won't fly when it gets back to the
server.

All in all though - I'm not a big fan of CommandBuilders - at least not in
this version of the framework - Bill Vaughn has a great article on why at
www.betav.com ->Articles -> MSDN - Weaning Developers from the
CommandBuilder.

As an aside... It's not a commonly known risk but believe it or not,
connection strings are susceptible to injection attacks as well and as it
stands, allowing user data to create the connection string could get you in
trouble potentially. As such, I'd be careful about that (ie don't do it
-) ). Store it in a config file or in isolated storage and ideally encrypt
it. Yes, you're using a trusted connection so that mitigates the risk of
having it stored in plain text - but it's a good practice and if you ever n
eed to change the authentication model, the code will be in place already.
Please note that in no way am I trying to lecture or anything - just figured
I'd mention it in case you were interested - when I learned about an
injection attack with a connectionstring- I was quite surprised b/c I didn't
realize such a thing was possible.

HTH,

Bill
"Hank1234" <Ha******@discussions.microsoft.com> wrote in message
news:45**********************************@microsof t.com...

Can I use one Data Adapter and one Command Builder to update amny tables?
Currently in my data adapter I query two tables and fill them into two
tables
in a data set. When I make a change to a record in the second table and
call
the update method of the data adapter the command builders update command
text is for the first table. Can the command builder handle two tables?

Code example:

Dim oCOnn As New SqlConnection("Data Source=.;" & _
"Initial
Catalog=Databasename;" & _
"Integrated Security=SSPI")
Dim oAdt As New SqlDataAdapter("Select * from location; select *
from vendor", oCOnn)
Dim oSet As New DataSet
Dim ocommbuild As New SqlCommandBuilder(oAdt)

oAdt.Fill(oSet)
oSet.Tables(1).Rows(1).Item("Notes") = "Hello"
Debug.WriteLine(ocommbuild.GetUpdateCommand.Comman dText)

oAdt.Update(oSet.Tables(1))

Jul 26 '05 #2

Hank1234

I don't understand how to use two different command builders when I have
selected from two different tables in the one data adapter. When I create a
command builder it only accepts the data adapter and since my data adapter
had two select statements in the command text how do I create a second
builder to use the second select statement. I hope this makes sense

"W.G. Ryan MVP" wrote:

Just use two separate commandbuilders, one for each query. Or, you can
specify a different select query for it and then call update- but it's
easier to just use two of them. Also, you probably want to use a
DataRelation on those tables if they are related in any form, just to ensure
that nothing happens client side that won't fly when it gets back to the
server.

All in all though - I'm not a big fan of CommandBuilders - at least not in
this version of the framework - Bill Vaughn has a great article on why at
www.betav.com ->Articles -> MSDN - Weaning Developers from the
CommandBuilder.

As an aside... It's not a commonly known risk but believe it or not,
connection strings are susceptible to injection attacks as well and as it
stands, allowing user data to create the connection string could get you in
trouble potentially. As such, I'd be careful about that (ie don't do it
-) ). Store it in a config file or in isolated storage and ideally encrypt
it. Yes, you're using a trusted connection so that mitigates the risk of
having it stored in plain text - but it's a good practice and if you ever n
eed to change the authentication model, the code will be in place already.
Please note that in no way am I trying to lecture or anything - just figured
I'd mention it in case you were interested - when I learned about an
injection attack with a connectionstring- I was quite surprised b/c I didn't
realize such a thing was possible.

HTH,

Bill
"Hank1234" <Ha******@discussions.microsoft.com> wrote in message
news:45**********************************@microsof t.com...

Can I use one Data Adapter and one Command Builder to update amny tables?
Currently in my data adapter I query two tables and fill them into two
tables
in a data set. When I make a change to a record in the second table and
call
the update method of the data adapter the command builders update command
text is for the first table. Can the command builder handle two tables?

Code example:

Dim oCOnn As New SqlConnection("Data Source=.;" & _
"Initial
Catalog=Databasename;" & _
"Integrated Security=SSPI")
Dim oAdt As New SqlDataAdapter("Select * from location; select *
from vendor", oCOnn)
Dim oSet As New DataSet
Dim ocommbuild As New SqlCommandBuilder(oAdt)

oAdt.Fill(oSet)
oSet.Tables(1).Rows(1).Item("Notes") = "Hello"
Debug.WriteLine(ocommbuild.GetUpdateCommand.Comman dText)

oAdt.Update(oSet.Tables(1))

Jul 26 '05 #3

Cor Ligthert [MVP]

Hank,

Bill wrote that in as in my opinion does not matter.
However when you want the result of the commandbuilder like this.
Dim mycmd as new SQLCommandBuilder(DA1)
mycmd = new SQLCommandBuilder(DA2)

Does give it give AFAIK not much advantages above this
Dim myCmd1 as new SQLCommandBuilder(DA1)
Dim myCmd2 as new SQLCommandBuilder(DA2)

I prefer the last.

Just my thought

Cor

Jul 26 '05 #4

Cor Ligthert [MVP]

Bill,

This one always triggers me.

Store it in a config file or in isolated storage and ideally encrypt it.

Do you want to tell that the security model from Windows is so rotten that
somebody who can reach that is not easily able to encrypt a string.

I am almost sure that that is not what you want to tell, not after that
bunch of patches we get forever

:-)

Cor

Jul 26 '05 #5

W.G. Ryan MVP

You can do both. In this case, what I recommended doing was using Windows
Authentication AND storing the connection string encrypted. You are storing
it somewhere and you are storing it either plaintext or not. So you can
store it in code for instance, as a constant or a regular string, or you can
store it elsewhere like a configuration file. Either way, you're storing it
somewhere. So all I'm recommending doing is storing it in a configuration
file or isolated storage, and storing it encrypted [you can use Windows
authentication or not - either way I'd recommend the same approach]. I see
0 value in storing a connectionstring in plaintext for any reason. If you
have it being used on an internal network that's 'secure', that mitigates
some of the risk, but why publicize the database name or server name? And
if you change your mind later and need to use Mixed mode authentication for
example, you'll already have the code in place
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:Oi**************@tk2msftngp13.phx.gbl...

Bill,

This one always triggers me.

Store it in a config file or in isolated storage and ideally encrypt it.

Do you want to tell that the security model from Windows is so rotten that
somebody who can reach that is not easily able to encrypt a string.

I am almost sure that that is not what you want to tell, not after that
bunch of patches we get forever

:-)

Cor

Jul 27 '05 #6

W.G. Ryan MVP

It makes sense ;-)

LEt me begin first by saying again that I"m not a big fan of
commandbuilders. If you look the code they generate, you'll understand why.
Anyway, to answer your question, you'd need to create two commandbuilders
and to do so, you'd need two dataadapters (each one containing one of the
select statements) or one dataadapter and change the commandtext. Neither
of these are elegant but CommandBUilders are NOT flexible in this regard (or
in respect to concurrency handling). As such, you're hands are tied if you
want to use them.

In Bill V's article, he mentions rolling your own code and I definitely
prefer this approach 99.9999% of the time over the commandbuilder.

HTH,

Bill
"Hank1234" <Ha******@discussions.microsoft.com> wrote in message
news:89**********************************@microsof t.com...

I don't understand how to use two different command builders when I have
selected from two different tables in the one data adapter. When I create
a
command builder it only accepts the data adapter and since my data adapter
had two select statements in the command text how do I create a second
builder to use the second select statement. I hope this makes sense

"W.G. Ryan MVP" wrote:

Just use two separate commandbuilders, one for each query. Or, you can
specify a different select query for it and then call update- but it's
easier to just use two of them. Also, you probably want to use a
DataRelation on those tables if they are related in any form, just to
ensure
that nothing happens client side that won't fly when it gets back to the
server.

All in all though - I'm not a big fan of CommandBuilders - at least not
in
this version of the framework - Bill Vaughn has a great article on why
at
www.betav.com ->Articles -> MSDN - Weaning Developers from the
CommandBuilder.

As an aside... It's not a commonly known risk but believe it or not,
connection strings are susceptible to injection attacks as well and as it
stands, allowing user data to create the connection string could get you
in
trouble potentially. As such, I'd be careful about that (ie don't do it
-) ). Store it in a config file or in isolated storage and ideally
encrypt
it. Yes, you're using a trusted connection so that mitigates the risk of
having it stored in plain text - but it's a good practice and if you ever
n
eed to change the authentication model, the code will be in place
already.
Please note that in no way am I trying to lecture or anything - just
figured
I'd mention it in case you were interested - when I learned about an
injection attack with a connectionstring- I was quite surprised b/c I
didn't
realize such a thing was possible.

HTH,

Bill
"Hank1234" <Ha******@discussions.microsoft.com> wrote in message
news:45**********************************@microsof t.com...

> Can I use one Data Adapter and one Command Builder to update amny
> tables?
> Currently in my data adapter I query two tables and fill them into two
> tables
> in a data set. When I make a change to a record in the second table and
> call
> the update method of the data adapter the command builders update
> command
> text is for the first table. Can the command builder handle two tables?
>
> Code example:
>
> Dim oCOnn As New SqlConnection("Data Source=.;" & _
> "Initial
> Catalog=Databasename;" & _
> "Integrated
> Security=SSPI")
> Dim oAdt As New SqlDataAdapter("Select * from location; select *
> from vendor", oCOnn)
> Dim oSet As New DataSet
> Dim ocommbuild As New SqlCommandBuilder(oAdt)
>
> oAdt.Fill(oSet)
> oSet.Tables(1).Rows(1).Item("Notes") = "Hello"
> Debug.WriteLine(ocommbuild.GetUpdateCommand.Comman dText)
>
> oAdt.Update(oSet.Tables(1))
>
>

Jul 27 '05 #7

Cor Ligthert [MVP]

Bill,

Why encrypt something when it is unreachable, that was the sense of my
question.

It is in a metaphor something as hiring a safe in a quality bank for your
diamonds and put those than in your own small inferior safe inside that safe
with a code lock on that.

Cor

Jul 27 '05 #8

W.G. Ryan MVP

It's not unreachable. Many security breaches occur from inside the firewall
by employees that have access that outsiders don't. Sure, encrypting
information doesn't mean that a savvy adversary couldn't get it, but there
is virtually 0 cost assoicated with encrypting it so making it more
difficult to tamper with is practically free..

And more importantly, if you have to change the database or later move to
mixed mode authentication, you can avoid a recompile by moving it to a
configuration file. And if it's already encrypted, then you won't have to
change 1 line of code to accomodate this change. In addition, by obscuring
the name of the db, you could stop someone from easily changing it to an
additional or different server. I know personally of a major US bank where
an intern found a script of part of the db and loaded an Oracle database on
his machine. He went into a VB6 application and changed the settings to
point over to his machine's db and then ran the application like he normally
would - checking all the data that came back and forth. Since multiple
servers were used, he did this one at a time with a few different databases
and found out quite a bit information from this. Fortunately he was fired
after being discovered - but this is a prime example of something that
couldn't have happened (at least not as easliy) if the server info was
encrypted.


"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

Bill,

Why encrypt something when it is unreachable, that was the sense of my
question.

It is in a metaphor something as hiring a safe in a quality bank for your
diamonds and put those than in your own small inferior safe inside that
safe with a code lock on that.

Cor

Jul 27 '05 #9

Cor Ligthert [MVP]

Bill,

I know personally of a major US bank where an intern found a script

He did, as you describe it, not found it in a *safe* unreachable place,
maybe he was by instance the one who wrote it. For that there is no
protection there will be always some persons who knows it.

For me this as you propose sounds the same as those persons who set in their
programs
integer a = 2;
if(a!=2) MessageBox.Show("the value of a is wrong");

:-)

This does not mean that I am against encrypting of connectionstring. There
are situations that I agree with that advice completly. However if it is in
a securit unreachable place. Than it looks for me a little bit overdone.

However let stop the discussion, we don't agree in this, but that is
sometimes very well.

:-)

Cor

Jul 27 '05 #10

W.G. Ryan MVP

I understand what you're saying but I'm giving multiple reasons. The most
important one is that if you need to switch the authentication mode, then
you don't need to recompile the application.

As far as the guy I'm speaking of, he was an intern there, not a developer.
Certainly someone writng the app can never hide stuff from themselves but
that wasn't the case here. The point was that If it's encrypted, then it
has a level of protection against tampering - someone who doesn't know the
key can't go and point it to another database. It's a philosophical
difference but things IMHO shouldn't be in plaintext unless you're sure that
they are 100% safe for everyone in the world to see and anything related to
permissioning falls outside of this realm.

I agree though - we're spending too much time on this.
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:e6**************@TK2MSFTNGP12.phx.gbl...

Bill,

I know personally of a major US bank where an intern found a script

He did, as you describe it, not found it in a *safe* unreachable place,
maybe he was by instance the one who wrote it. For that there is no
protection there will be always some persons who knows it.

For me this as you propose sounds the same as those persons who set in
their programs
integer a = 2;
if(a!=2) MessageBox.Show("the value of a is wrong");

:-)

This does not mean that I am against encrypting of connectionstring. There
are situations that I agree with that advice completly. However if it is
in a securit unreachable place. Than it looks for me a little bit
overdone.

However let stop the discussion, we don't agree in this, but that is
sometimes very well.

:-)

Cor

Jul 27 '05 #11