473,241 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

ADO.NET Update two tables using one SQLCommandBuilder

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
10 9289
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
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
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
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
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
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: LP | last post by:
Hello, In the past I used SqlCommandBuilder and SqlDataAdapter .Update method to apply changes in a DataTable back to its table source in SQL Server. It worked fine when DataSet had only 1...
1
by: Wing | last post by:
Hi all, I have created 2 tables in sql database and join these 2 tables before assign the result to the dataset, and display the result in datagrid. Everything is fine up to this point. The...
4
by: Oscar Thornell | last post by:
Hi, I have a relativley large/complex typed dataset that contains 7-8 tables and some supporting relational tables (lookups) for many-to-many relations. A good exampel would be a dataset that...
10
by: Hank1234 | last post by:
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...
1
by: andrewcw | last post by:
I have just 1 table that I am updating, the SQL I use to generate the DataTable is complex using multiple tables, however when I view the fieldnames of the DataTable from the DataSet - everything...
1
by: Pietro Ova | last post by:
Hello, I wrote a little program to understand the ADO.NET's mechanism. It's a common program with a datagrid, textboxes and a toolstripbar with usually buttons. The problem is that meanwhile i add...
0
by: k3nTm | last post by:
Good day!!! can you help me with my code on whats wrong with it. I want to update my MSSQLSERVER database from a DataSet. and there's nothing happen with my Database. Here is my code: ...
0
OuTCasT
by: OuTCasT | last post by:
can some please tell me how to update a table with data from a dataset what ive done is fill the dataset with the adapter and used a for Next loop to step into each row and display it into text...
1
by: sachinkale123 | last post by:
Hi I am working in a Windows C# application. I m using Dataset. But whenever I updated the dataset it gets updated. I can see the changes in dataset but using SQLAdapter.Update(Dataset) command It is...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.