using "Insert Into..." with dataAdapter Insert Command?

[Ed]

Hi,

I want to load data to a table in Sql Server from a
dataset table in my vb.net app using a dataAdapter. I
know how to do this as follows (my question is to see if I
can reduce the amount of code below):
....
Dim DA As SqlDataAdapter = New SqlDataAdapter
Dim Parm As New SqlParameter
....
DA.InsertCommand = New SqlCommand("Insert Into tbl1(fld0,
fld1, fld2) Values(@fld0, @fld1, @fld2)", conn)
Parm = DA.InsertCommand.Parameters.Add(New SqlParameter
("@fld0", NVarChar, 50, "fld0"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter
("@fld1", SqlDbType.NVarChar, 50, "fld1"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter
("@fld2", SqlDbType.NVarChar, 50, "fld2"))
....
DA.Update(dataset1, "tbl1")
....

The parameters is where I feel I am writing too much
code. Is there a way to say something like:

DA.InsertCommand = New SqlCommand("Insert Into tbl1 Select
* from " & dataTable.Name")

Could I create a class object that is a dataTable with a
property called "Name" or something like that? Or am I
limited to creating a bunch of parameters for the
dataAdapter Insert command? Or, is there a way to use the
Sql Command object without the dataAdapter to insert All
data to my sql server table from a dataset table (without
looping through a dataTable object)?

TIA,
Ed

[Ken Tucker [MVP]]

Hi,

Take a look at the command builder.
http://msdn.microsoft.com/library/de...classtopic.asp

Ken
----------

"Ed" <an*******@discussions.microsoft.com> wrote in message
news:1c*****************************@phx.gbl:

Hi,

I want to load data to a table in Sql Server from a
dataset table in my vb.net app using a dataAdapter. I
know how to do this as follows (my question is to see if I
can reduce the amount of code below):
...
Dim DA As SqlDataAdapter = New SqlDataAdapter
Dim Parm As New SqlParameter
...
DA.InsertCommand = New SqlCommand("Insert Into tbl1(fld0,
fld1, fld2) Values(@fld0, @fld1, @fld2)", conn)
Parm = DA.InsertCommand.Parameters.Add(New SqlParameter
("@fld0", NVarChar, 50, "fld0"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter
("@fld1", SqlDbType.NVarChar, 50, "fld1"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter
("@fld2", SqlDbType.NVarChar, 50, "fld2"))
...
DA.Update(dataset1, "tbl1")
...

The parameters is where I feel I am writing too much
code. Is there a way to say something like:

DA.InsertCommand = New SqlCommand("Insert Into tbl1 Select
* from " & dataTable.Name")

Could I create a class object that is a dataTable with a
property called "Name" or something like that? Or am I
limited to creating a bunch of parameters for the
dataAdapter Insert command? Or, is there a way to use the
Sql Command object without the dataAdapter to insert All
data to my sql server table from a dataset table (without
looping through a dataTable object)?

TIA,
Ed

--
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.230 / Virus Database: 263.3.0 - Release Date: 6/12/2004

[Ed]

Many thanks for your help! I could never figure out how
to use the commandbuilder - you have to use the
dataAdapter as an argument. Now I get it! Works great!

Many thanks,
Ed

-----Original Message-----
Hi,

Take a look at the command builder.
http://msdn.microsoft.com/library/default.asp? url=/library/en-
us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclas
stopic.asp
Ken
----------

"Ed" <an*******@discussions.microsoft.com> wrote in messagenews:1c*****************************@phx.gbl:

Hi,

I want to load data to a table in Sql Server from a
dataset table in my vb.net app using a dataAdapter. I
know how to do this as follows (my question is to see if I can reduce the amount of code below):
...
Dim DA As SqlDataAdapter = New SqlDataAdapter
Dim Parm As New SqlParameter
...
DA.InsertCommand = New SqlCommand("Insert Into tbl1 (fld0, fld1, fld2) Values(@fld0, @fld1, @fld2)", conn)
Parm = DA.InsertCommand.Parameters.Add(New SqlParameter
("@fld0", NVarChar, 50, "fld0"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter ("@fld1", SqlDbType.NVarChar, 50, "fld1"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter ("@fld2", SqlDbType.NVarChar, 50, "fld2"))
...
DA.Update(dataset1, "tbl1")
...

The parameters is where I feel I am writing too much
code. Is there a way to say something like:

DA.InsertCommand = New SqlCommand("Insert Into tbl1 Select * from " & dataTable.Name")

Could I create a class object that is a dataTable with a
property called "Name" or something like that? Or am I
limited to creating a bunch of parameters for the
dataAdapter Insert command? Or, is there a way to use the Sql Command object without the dataAdapter to insert All
data to my sql server table from a dataset table (without looping through a dataTable object)?

TIA,
Ed

--
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.230 / Virus Database: 263.3.0 - Release

Date: 6/12/2004

.

[Herfried K. Wagner [MVP]]

* "Ed" <an*******@discussions.microsoft.com> scripsit:

I want to load data to a table in Sql Server from a
dataset table in my vb.net app using a dataAdapter. I
know how to do this as follows (my question is to see if I
can reduce the amount of code below):
...
Dim DA As SqlDataAdapter = New SqlDataAdapter
Dim Parm As New SqlParameter

Just FYI: There is a separate group for .NET + database (ADO.NET)
related questions avaliable:

<URL:news://news.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>