best practice for inserting rows using a dataAdapter?

Hello,

I want to use a dataAdapter to insert rows into a table on a sql server DB.
I understand that the DataAdapter will automatically handle concurrency
issues. So first I have to get a table to insert a row into. I have been
doing this:

da.SelectCommand = New SqlCommand("Select * from tbl1", conn)
da.FillSchema(ds, SchemaType.Source, "tbl1")
....
my other method was this:

da.SelectCommand = New SqlCommand("Select * from tbl1", conn)
da.Fill(ds, "tbl1")
ds.Clear

I read one post of someone who said that the Fillschema method used more
resource than the Fill method for getting a table. But I am thinking, what
if I already have a ton of rows in the table? I only need the schema of the
table to insert a new row into. Any suggestions appreciated which method is
the best practice - or if neither method above is best practice - what would
be a best practice method?

Thanks,
Rich

You are best to explicitly create each of the commands:

Select
Insert
Update
Delete

and then use fill with the select command. With an explicitly created insert
and update, there are fewer surprises. :-)

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************
"Rich" <Rich@discussions.microsoft.com> wrote in message
news:32362AA6-8ADB-4D8C-A973-A402BE9E0B99@microsoft.com...
> Hello,
>
> I want to use a dataAdapter to insert rows into a table on a sql server
> DB.
> I understand that the DataAdapter will automatically handle concurrency
> issues. So first I have to get a table to insert a row into. I have been
> doing this:
>
> da.SelectCommand = New SqlCommand("Select * from tbl1", conn)
> da.FillSchema(ds, SchemaType.Source, "tbl1")
> ...
> my other method was this:
>
> da.SelectCommand = New SqlCommand("Select * from tbl1", conn)
> da.Fill(ds, "tbl1")
> ds.Clear
>
> I read one post of someone who said that the Fillschema method used more
> resource than the Fill method for getting a table. But I am thinking,
> what
> if I already have a ton of rows in the table? I only need the schema of
> the
> table to insert a new row into. Any suggestions appreciated which method
> is
> the best practice - or if neither method above is best practice - what
> would
> be a best practice method?
>
> Thanks,
> Rich
>

Thanks. Do you mean explicitly create the command as in

da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * from tbl1"
da.Fill(ds, "tbl1")

but suppose tbl1 contains 100,000 rows? And all I want to do is to insert a
row into tbl1 from the client app? I am thinking I want to get just the
table without bringing in any rows and then update the table:

da.InsertCommand.CommandText = "Insert Into tbl1 Select @a"

da.InsertCommand.Parameters.Add(New SqlParameter("@a, SqlDBtype.varchar, 30,
"colA")

da.InsertCommand.Parameters("@a").Value = "testing"
da.Update(ds, "tbl1")

I am setting this scenario and ask to make sure I understand you correctly,
that to get the table structure of "tbl1", in order to insert a row I get the
structure of the table with da.Fill rather than

da.FillSchema(ds, SchemaType.Source, "tbl1") ?




"Cowboy (Gregory A. Beamer)" wrote:

> You are best to explicitly create each of the commands:
>
> Select
> Insert
> Update
> Delete
>
> and then use fill with the select command. With an explicitly created insert
> and update, there are fewer surprises. :-)
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> *************************************************
> Think outside of the box!
> *************************************************
> "Rich" <Rich@discussions.microsoft.com> wrote in message
> news:32362AA6-8ADB-4D8C-A973-A402BE9E0B99@microsoft.com...
> > Hello,
> >
> > I want to use a dataAdapter to insert rows into a table on a sql server
> > DB.
> > I understand that the DataAdapter will automatically handle concurrency
> > issues. So first I have to get a table to insert a row into. I have been
> > doing this:
> >
> > da.SelectCommand = New SqlCommand("Select * from tbl1", conn)
> > da.FillSchema(ds, SchemaType.Source, "tbl1")
> > ...
> > my other method was this:
> >
> > da.SelectCommand = New SqlCommand("Select * from tbl1", conn)
> > da.Fill(ds, "tbl1")
> > ds.Clear
> >
> > I read one post of someone who said that the Fillschema method used more
> > resource than the Fill method for getting a table. But I am thinking,
> > what
> > if I already have a ton of rows in the table? I only need the schema of
> > the
> > table to insert a new row into. Any suggestions appreciated which method
> > is
> > the best practice - or if neither method above is best practice - what
> > would
> > be a best practice method?
> >
> > Thanks,
> > Rich
> >
>
>
>

I am going to try to get through this via inline answers:

"Rich" <Rich@discussions.microsoft.com> wrote in message
news:92A400AD-F28F-4A23-944A-8ECF9917C33B@microsoft.com...
> Thanks. Do you mean explicitly create the command as in
>
> da.SelectCommand = New SqlCommand
> da.SelectCommand.Connection = conn
> da.SelectCommand.CommandText = "Select * from tbl1"
> da.Fill(ds, "tbl1")

First, I would not suggest "SELECT *" in any application. There are very few
times when you are actively working with every single row in a table, which
answers your next question. You should only grab the data you need for a
particular function.

> but suppose tbl1 contains 100,000 rows?

Answered above. Do not grab all 100,000 rows unless you are really going to
do something with them.

> And all I want to do is to insert a
> row into tbl1 from the client app?

You can start with a DataSet that matches the table that is empty. Add the
row and then call Update(). If you have the proper INSERT command, it will
see the new row and add it. This is a VERY rough example that assumes a
connection object named conn.

'TODO: Add row here
'This is best done explicitly, column by column
Dim da As newSqlAdapter()
Dim cmd As new SqlCommand("INSERT INTO Table (col1, col2) VALUES (@col1,
@col2)")
cmd.Parameters.Add(new SqlParameter("@col1", value1))
cmd.Parameters.Add(new SqlParameter("@col2", value2))

conn.Open()
da.Update(dataTableInQuestion)


>I am thinking I want to get just the
> table without bringing in any rows and then update the table:

Similar to example above. If ALL you are doing is adding a record, then
empty DataSet that matches the table (strongly typed datasets are easier to
use in these cases, as you get dotted notation and Intellisense, but
non-strongly typed will work) and call Update().

> da.InsertCommand.CommandText = "Insert Into tbl1 Select @a"

The SQL above will not work. Look at the example I have given.

> da.InsertCommand.Parameters.Add(New SqlParameter("@a, SqlDBtype.varchar,
> 30,
> "colA")

This part is fine.

> da.InsertCommand.Parameters("@a").Value = "testing"
> da.Update(ds, "tbl1")

You have the right idea here as well. The main thing you need to get correct
is the insert statement, which will follow this basic format:

INSERT INTO tableName (column1, column2)
VALUES (value1, value2)

Your basic change, other than using real names :-), is to make the values as
parameters @value1, @value2 and create a SQL Parameter for each parameter in
the statement.

> I am setting this scenario and ask to make sure I understand you
> correctly,
> that to get the table structure of "tbl1", in order to insert a row I get
> the
> structure of the table with da.Fill rather than
>
> da.FillSchema(ds, SchemaType.Source, "tbl1") ?

If you want to get an empty DataSet, without using a strongly typed DataSet,
you can do something like this:

Dim conn As new SqlConnection("connection string here")
Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
Dim da As new SqlDataAdapter(cmd)
Dim ds As new DataSet("DataSet name here")

Try
conn.Open()
da.Fill(ds)
Finally
conn.Dispose()
End Try

A strongly typed DataSet can then be created (to avoid calling the database
for definition alone), by adding the statement:

ds.WriteXml("path to write to here")

You then create a new DataSet in your project, open the XMLyou saved off,
and copy the tables from the one you saved to the new one in your project.
You can then completely avoid filling an empty DataSet for an INSERT.

NOTE: The above steps are migratory. If you want to avoid writing the fill
routine, you can simply create a new DataSet, add a data connection to
Visual Studio (works in Pro or higher in 2005 (maybe Standard, but not
Express), should be the same in Visual Studio 2003). You can then drag a
table from the database onto the DataSet and have it automagically create
the table definition.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

Wow! Thanks, that is really hot stuff. I am using VB2005, and I do sort of
recall dragging tables into the project. But I like your ds.WriteXML(...)
idea.

I also like

Dim conn As new SqlConnection("connection string here")
Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
Dim da As new SqlDataAdapter(cmd)
Dim ds As new DataSet("DataSet name here")

Try
conn.Open()
da.Fill(ds)
Finally
conn.Dispose()
End Try


May I ask for an example of what I would enter in

ds.WriteXML(...)

That was the only thing that I am not sure what to do.

Thanks again for your great advice!

Rich



"Cowboy (Gregory A. Beamer)" wrote:

> I am going to try to get through this via inline answers:
>
> "Rich" <Rich@discussions.microsoft.com> wrote in message
> news:92A400AD-F28F-4A23-944A-8ECF9917C33B@microsoft.com...
> > Thanks. Do you mean explicitly create the command as in
> >
> > da.SelectCommand = New SqlCommand
> > da.SelectCommand.Connection = conn
> > da.SelectCommand.CommandText = "Select * from tbl1"
> > da.Fill(ds, "tbl1")
>
> First, I would not suggest "SELECT *" in any application. There are very few
> times when you are actively working with every single row in a table, which
> answers your next question. You should only grab the data you need for a
> particular function.
>
> > but suppose tbl1 contains 100,000 rows?
>
> Answered above. Do not grab all 100,000 rows unless you are really going to
> do something with them.
>
> > And all I want to do is to insert a
> > row into tbl1 from the client app?
>
> You can start with a DataSet that matches the table that is empty. Add the
> row and then call Update(). If you have the proper INSERT command, it will
> see the new row and add it. This is a VERY rough example that assumes a
> connection object named conn.
>
> 'TODO: Add row here
> 'This is best done explicitly, column by column
> Dim da As newSqlAdapter()
> Dim cmd As new SqlCommand("INSERT INTO Table (col1, col2) VALUES (@col1,
> @col2)")
> cmd.Parameters.Add(new SqlParameter("@col1", value1))
> cmd.Parameters.Add(new SqlParameter("@col2", value2))
>
> conn.Open()
> da.Update(dataTableInQuestion)
>
>
> >I am thinking I want to get just the
> > table without bringing in any rows and then update the table:
>
> Similar to example above. If ALL you are doing is adding a record, then
> empty DataSet that matches the table (strongly typed datasets are easier to
> use in these cases, as you get dotted notation and Intellisense, but
> non-strongly typed will work) and call Update().
>
> > da.InsertCommand.CommandText = "Insert Into tbl1 Select @a"
>
> The SQL above will not work. Look at the example I have given.
>
> > da.InsertCommand.Parameters.Add(New SqlParameter("@a, SqlDBtype.varchar,
> > 30,
> > "colA")
>
> This part is fine.
>
> > da.InsertCommand.Parameters("@a").Value = "testing"
> > da.Update(ds, "tbl1")
>
> You have the right idea here as well. The main thing you need to get correct
> is the insert statement, which will follow this basic format:
>
> INSERT INTO tableName (column1, column2)
> VALUES (value1, value2)
>
> Your basic change, other than using real names :-), is to make the values as
> parameters @value1, @value2 and create a SQL Parameter for each parameter in
> the statement.
>
> > I am setting this scenario and ask to make sure I understand you
> > correctly,
> > that to get the table structure of "tbl1", in order to insert a row I get
> > the
> > structure of the table with da.Fill rather than
> >
> > da.FillSchema(ds, SchemaType.Source, "tbl1") ?
>
> If you want to get an empty DataSet, without using a strongly typed DataSet,
> you can do something like this:
>
> Dim conn As new SqlConnection("connection string here")
> Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
> Dim da As new SqlDataAdapter(cmd)
> Dim ds As new DataSet("DataSet name here")
>
> Try
> conn.Open()
> da.Fill(ds)
> Finally
> conn.Dispose()
> End Try
>
> A strongly typed DataSet can then be created (to avoid calling the database
> for definition alone), by adding the statement:
>
> ds.WriteXml("path to write to here")
>
> You then create a new DataSet in your project, open the XMLyou saved off,
> and copy the tables from the one you saved to the new one in your project.
> You can then completely avoid filling an empty DataSet for an INSERT.
>
> NOTE: The above steps are migratory. If you want to avoid writing the fill
> routine, you can simply create a new DataSet, add a data connection to
> Visual Studio (works in Pro or higher in 2005 (maybe Standard, but not
> Express), should be the same in Visual Studio 2003). You can then drag a
> table from the database onto the DataSet and have it automagically create
> the table definition.
>
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> *************************************************
> Think outside of the box!
> *************************************************
>
>
>

Glad you like it:

Any legal drive path (or UNC if you are feeling froggy):

ds.WriteXML("C:\NameOfDataSet.xsd")

I know using root is not the best option, but it is a development machine
and it gives me a common place to search for garbage. :-)


The idea of writing out the XML came from having to retool a lot of
applications that created DataSets on the fly. In ADO.NET 1.1, we found a
nice perf gain by simply creating strongly typed datasets, so I added that
line to each routine and then switched each routine to use the STD instead
of the generic DS. It was an easy fix.

For the most part, I try to work with a code gened O/R mapping layer these
days, esp. in .NET 2.0 apps, as the generics add strong typing to
collections. But, there is nothing wrong with using DataSets and there is an
added bonus: maintainability increases, as they are well documented. :-)

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************
"Rich" <Rich@discussions.microsoft.com> wrote in message
news:348E3A72-8870-4FC0-BACE-59AC800376D8@microsoft.com...
> Wow! Thanks, that is really hot stuff. I am using VB2005, and I do sort
> of
> recall dragging tables into the project. But I like your ds.WriteXML(...)
> idea.
>
> I also like
>
> Dim conn As new SqlConnection("connection string here")
> Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
> Dim da As new SqlDataAdapter(cmd)
> Dim ds As new DataSet("DataSet name here")
>
> Try
> conn.Open()
> da.Fill(ds)
> Finally
> conn.Dispose()
> End Try
>
>
> May I ask for an example of what I would enter in
>
> ds.WriteXML(...)
>
> That was the only thing that I am not sure what to do.
>
> Thanks again for your great advice!
>
> Rich
>
>
>
> "Cowboy (Gregory A. Beamer)" wrote:
>
>> I am going to try to get through this via inline answers:
>>
>> "Rich" <Rich@discussions.microsoft.com> wrote in message
>> news:92A400AD-F28F-4A23-944A-8ECF9917C33B@microsoft.com...
>> > Thanks. Do you mean explicitly create the command as in
>> >
>> > da.SelectCommand = New SqlCommand
>> > da.SelectCommand.Connection = conn
>> > da.SelectCommand.CommandText = "Select * from tbl1"
>> > da.Fill(ds, "tbl1")
>>
>> First, I would not suggest "SELECT *" in any application. There are very
>> few
>> times when you are actively working with every single row in a table,
>> which
>> answers your next question. You should only grab the data you need for a
>> particular function.
>>
>> > but suppose tbl1 contains 100,000 rows?
>>
>> Answered above. Do not grab all 100,000 rows unless you are really going
>> to
>> do something with them.
>>
>> > And all I want to do is to insert a
>> > row into tbl1 from the client app?
>>
>> You can start with a DataSet that matches the table that is empty. Add
>> the
>> row and then call Update(). If you have the proper INSERT command, it
>> will
>> see the new row and add it. This is a VERY rough example that assumes a
>> connection object named conn.
>>
>> 'TODO: Add row here
>> 'This is best done explicitly, column by column
>> Dim da As newSqlAdapter()
>> Dim cmd As new SqlCommand("INSERT INTO Table (col1, col2) VALUES (@col1,
>> @col2)")
>> cmd.Parameters.Add(new SqlParameter("@col1", value1))
>> cmd.Parameters.Add(new SqlParameter("@col2", value2))
>>
>> conn.Open()
>> da.Update(dataTableInQuestion)
>>
>>
>> >I am thinking I want to get just the
>> > table without bringing in any rows and then update the table:
>>
>> Similar to example above. If ALL you are doing is adding a record, then
>> empty DataSet that matches the table (strongly typed datasets are easier
>> to
>> use in these cases, as you get dotted notation and Intellisense, but
>> non-strongly typed will work) and call Update().
>>
>> > da.InsertCommand.CommandText = "Insert Into tbl1 Select @a"
>>
>> The SQL above will not work. Look at the example I have given.
>>
>> > da.InsertCommand.Parameters.Add(New SqlParameter("@a,
>> > SqlDBtype.varchar,
>> > 30,
>> > "colA")
>>
>> This part is fine.
>>
>> > da.InsertCommand.Parameters("@a").Value = "testing"
>> > da.Update(ds, "tbl1")
>>
>> You have the right idea here as well. The main thing you need to get
>> correct
>> is the insert statement, which will follow this basic format:
>>
>> INSERT INTO tableName (column1, column2)
>> VALUES (value1, value2)
>>
>> Your basic change, other than using real names :-), is to make the values
>> as
>> parameters @value1, @value2 and create a SQL Parameter for each parameter
>> in
>> the statement.
>>
>> > I am setting this scenario and ask to make sure I understand you
>> > correctly,
>> > that to get the table structure of "tbl1", in order to insert a row I
>> > get
>> > the
>> > structure of the table with da.Fill rather than
>> >
>> > da.FillSchema(ds, SchemaType.Source, "tbl1") ?
>>
>> If you want to get an empty DataSet, without using a strongly typed
>> DataSet,
>> you can do something like this:
>>
>> Dim conn As new SqlConnection("connection string here")
>> Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
>> Dim da As new SqlDataAdapter(cmd)
>> Dim ds As new DataSet("DataSet name here")
>>
>> Try
>> conn.Open()
>> da.Fill(ds)
>> Finally
>> conn.Dispose()
>> End Try
>>
>> A strongly typed DataSet can then be created (to avoid calling the
>> database
>> for definition alone), by adding the statement:
>>
>> ds.WriteXml("path to write to here")
>>
>> You then create a new DataSet in your project, open the XMLyou saved off,
>> and copy the tables from the one you saved to the new one in your
>> project.
>> You can then completely avoid filling an empty DataSet for an INSERT.
>>
>> NOTE: The above steps are migratory. If you want to avoid writing the
>> fill
>> routine, you can simply create a new DataSet, add a data connection to
>> Visual Studio (works in Pro or higher in 2005 (maybe Standard, but not
>> Express), should be the same in Visual Studio 2003). You can then drag a
>> table from the database onto the DataSet and have it automagically create
>> the table definition.
>>
>>
>> --
>> Gregory A. Beamer
>> MVP; MCP: +I, SE, SD, DBA
>>
>> *************************************************
>> Think outside of the box!
>> *************************************************
>>
>>
>>

Thank you again. I think I am starting to get the idea. I just experimented
with

ds.WriteXML("C:\NameOfDataSet.xsd")

So I can write an empty table to the disk and then read it back in with

ds.ReadXML("C:\NameOfDataSet.xsd")

At least I am starting to understand this, but I admit that I like

cmd.commandtext = "Select * from tbl1 Where 1 = 2)

a little bit more because it is a little more on the fly and I don't have to
store anything on the disk. What if the .xsd file gets mysteriously deleted?



"Cowboy (Gregory A. Beamer)" wrote:

> Glad you like it:
>
> Any legal drive path (or UNC if you are feeling froggy):
>
> ds.WriteXML("C:\NameOfDataSet.xsd")
>
> I know using root is not the best option, but it is a development machine
> and it gives me a common place to search for garbage. :-)
>
>
> The idea of writing out the XML came from having to retool a lot of
> applications that created DataSets on the fly. In ADO.NET 1.1, we found a
> nice perf gain by simply creating strongly typed datasets, so I added that
> line to each routine and then switched each routine to use the STD instead
> of the generic DS. It was an easy fix.
>
> For the most part, I try to work with a code gened O/R mapping layer these
> days, esp. in .NET 2.0 apps, as the generics add strong typing to
> collections. But, there is nothing wrong with using DataSets and there is an
> added bonus: maintainability increases, as they are well documented. :-)
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> *************************************************
> Think outside of the box!
> *************************************************
> "Rich" <Rich@discussions.microsoft.com> wrote in message
> news:348E3A72-8870-4FC0-BACE-59AC800376D8@microsoft.com...
> > Wow! Thanks, that is really hot stuff. I am using VB2005, and I do sort
> > of
> > recall dragging tables into the project. But I like your ds.WriteXML(...)
> > idea.
> >
> > I also like
> >
> > Dim conn As new SqlConnection("connection string here")
> > Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
> > Dim da As new SqlDataAdapter(cmd)
> > Dim ds As new DataSet("DataSet name here")
> >
> > Try
> > conn.Open()
> > da.Fill(ds)
> > Finally
> > conn.Dispose()
> > End Try
> >
> >
> > May I ask for an example of what I would enter in
> >
> > ds.WriteXML(...)
> >
> > That was the only thing that I am not sure what to do.
> >
> > Thanks again for your great advice!
> >
> > Rich
> >
> >
> >
> > "Cowboy (Gregory A. Beamer)" wrote:
> >
> >> I am going to try to get through this via inline answers:
> >>
> >> "Rich" <Rich@discussions.microsoft.com> wrote in message
> >> news:92A400AD-F28F-4A23-944A-8ECF9917C33B@microsoft.com...
> >> > Thanks. Do you mean explicitly create the command as in
> >> >
> >> > da.SelectCommand = New SqlCommand
> >> > da.SelectCommand.Connection = conn
> >> > da.SelectCommand.CommandText = "Select * from tbl1"
> >> > da.Fill(ds, "tbl1")
> >>
> >> First, I would not suggest "SELECT *" in any application. There are very
> >> few
> >> times when you are actively working with every single row in a table,
> >> which
> >> answers your next question. You should only grab the data you need for a
> >> particular function.
> >>
> >> > but suppose tbl1 contains 100,000 rows?
> >>
> >> Answered above. Do not grab all 100,000 rows unless you are really going
> >> to
> >> do something with them.
> >>
> >> > And all I want to do is to insert a
> >> > row into tbl1 from the client app?
> >>
> >> You can start with a DataSet that matches the table that is empty. Add
> >> the
> >> row and then call Update(). If you have the proper INSERT command, it
> >> will
> >> see the new row and add it. This is a VERY rough example that assumes a
> >> connection object named conn.
> >>
> >> 'TODO: Add row here
> >> 'This is best done explicitly, column by column
> >> Dim da As newSqlAdapter()
> >> Dim cmd As new SqlCommand("INSERT INTO Table (col1, col2) VALUES (@col1,
> >> @col2)")
> >> cmd.Parameters.Add(new SqlParameter("@col1", value1))
> >> cmd.Parameters.Add(new SqlParameter("@col2", value2))
> >>
> >> conn.Open()
> >> da.Update(dataTableInQuestion)
> >>
> >>
> >> >I am thinking I want to get just the
> >> > table without bringing in any rows and then update the table:
> >>
> >> Similar to example above. If ALL you are doing is adding a record, then
> >> empty DataSet that matches the table (strongly typed datasets are easier
> >> to
> >> use in these cases, as you get dotted notation and Intellisense, but
> >> non-strongly typed will work) and call Update().
> >>
> >> > da.InsertCommand.CommandText = "Insert Into tbl1 Select @a"
> >>
> >> The SQL above will not work. Look at the example I have given.
> >>
> >> > da.InsertCommand.Parameters.Add(New SqlParameter("@a,
> >> > SqlDBtype.varchar,
> >> > 30,
> >> > "colA")
> >>
> >> This part is fine.
> >>
> >> > da.InsertCommand.Parameters("@a").Value = "testing"
> >> > da.Update(ds, "tbl1")
> >>
> >> You have the right idea here as well. The main thing you need to get
> >> correct
> >> is the insert statement, which will follow this basic format:
> >>
> >> INSERT INTO tableName (column1, column2)
> >> VALUES (value1, value2)
> >>
> >> Your basic change, other than using real names :-), is to make the values
> >> as
> >> parameters @value1, @value2 and create a SQL Parameter for each parameter
> >> in
> >> the statement.
> >>
> >> > I am setting this scenario and ask to make sure I understand you
> >> > correctly,
> >> > that to get the table structure of "tbl1", in order to insert a row I
> >> > get
> >> > the
> >> > structure of the table with da.Fill rather than
> >> >
> >> > da.FillSchema(ds, SchemaType.Source, "tbl1") ?
> >>
> >> If you want to get an empty DataSet, without using a strongly typed
> >> DataSet,
> >> you can do something like this:
> >>
> >> Dim conn As new SqlConnection("connection string here")
> >> Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
> >> Dim da As new SqlDataAdapter(cmd)
> >> Dim ds As new DataSet("DataSet name here")
> >>
> >> Try
> >> conn.Open()
> >> da.Fill(ds)
> >> Finally
> >> conn.Dispose()
> >> End Try
> >>
> >> A strongly typed DataSet can then be created (to avoid calling the
> >> database
> >> for definition alone), by adding the statement:
> >>
> >> ds.WriteXml("path to write to here")
> >>
> >> You then create a new DataSet in your project, open the XMLyou saved off,
> >> and copy the tables from the one you saved to the new one in your
> >> project.
> >> You can then completely avoid filling an empty DataSet for an INSERT.
> >>
> >> NOTE: The above steps are migratory. If you want to avoid writing the
> >> fill
> >> routine, you can simply create a new DataSet, add a data connection to
> >> Visual Studio (works in Pro or higher in 2005 (maybe Standard, but not
> >> Express), should be the same in Visual Studio 2003). You can then drag a
> >> table from the database onto the DataSet and have it automagically create
> >> the table definition.
> >>
> >>
> >> --
> >> Gregory A. Beamer
> >> MVP; MCP: +I, SE, SD, DBA
> >>
> >> *************************************************
> >> Think outside of the box!
> >> *************************************************
> >>
> >>
> >>
>
>
>

Make the XSD part of your project using this:

http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!374.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink

I messed up by stating WriteXml instead of WriteXmlSchema.

Hope that helps!

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************
"Rich" <Rich@discussions.microsoft.com> wrote in message
news:3D5DBB63-F079-4634-B01E-83F26526E13F@microsoft.com...
> Thank you again. I think I am starting to get the idea. I just
> experimented
> with
>
> ds.WriteXML("C:\NameOfDataSet.xsd")
>
> So I can write an empty table to the disk and then read it back in with
>
> ds.ReadXML("C:\NameOfDataSet.xsd")
>
> At least I am starting to understand this, but I admit that I like
>
> cmd.commandtext = "Select * from tbl1 Where 1 = 2)
>
> a little bit more because it is a little more on the fly and I don't have
> to
> store anything on the disk. What if the .xsd file gets mysteriously
> deleted?
>
>
>
> "Cowboy (Gregory A. Beamer)" wrote:
>
>> Glad you like it:
>>
>> Any legal drive path (or UNC if you are feeling froggy):
>>
>> ds.WriteXML("C:\NameOfDataSet.xsd")
>>
>> I know using root is not the best option, but it is a development machine
>> and it gives me a common place to search for garbage. :-)
>>
>>
>> The idea of writing out the XML came from having to retool a lot of
>> applications that created DataSets on the fly. In ADO.NET 1.1, we found a
>> nice perf gain by simply creating strongly typed datasets, so I added
>> that
>> line to each routine and then switched each routine to use the STD
>> instead
>> of the generic DS. It was an easy fix.
>>
>> For the most part, I try to work with a code gened O/R mapping layer
>> these
>> days, esp. in .NET 2.0 apps, as the generics add strong typing to
>> collections. But, there is nothing wrong with using DataSets and there is
>> an
>> added bonus: maintainability increases, as they are well documented. :-)
>>
>> --
>> Gregory A. Beamer
>> MVP; MCP: +I, SE, SD, DBA
>>
>> *************************************************
>> Think outside of the box!
>> *************************************************
>> "Rich" <Rich@discussions.microsoft.com> wrote in message
>> news:348E3A72-8870-4FC0-BACE-59AC800376D8@microsoft.com...
>> > Wow! Thanks, that is really hot stuff. I am using VB2005, and I do
>> > sort
>> > of
>> > recall dragging tables into the project. But I like your
>> > ds.WriteXML(...)
>> > idea.
>> >
>> > I also like
>> >
>> > Dim conn As new SqlConnection("connection string here")
>> > Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
>> > Dim da As new SqlDataAdapter(cmd)
>> > Dim ds As new DataSet("DataSet name here")
>> >
>> > Try
>> > conn.Open()
>> > da.Fill(ds)
>> > Finally
>> > conn.Dispose()
>> > End Try
>> >
>> >
>> > May I ask for an example of what I would enter in
>> >
>> > ds.WriteXML(...)
>> >
>> > That was the only thing that I am not sure what to do.
>> >
>> > Thanks again for your great advice!
>> >
>> > Rich
>> >
>> >
>> >
>> > "Cowboy (Gregory A. Beamer)" wrote:
>> >
>> >> I am going to try to get through this via inline answers:
>> >>
>> >> "Rich" <Rich@discussions.microsoft.com> wrote in message
>> >> news:92A400AD-F28F-4A23-944A-8ECF9917C33B@microsoft.com...
>> >> > Thanks. Do you mean explicitly create the command as in
>> >> >
>> >> > da.SelectCommand = New SqlCommand
>> >> > da.SelectCommand.Connection = conn
>> >> > da.SelectCommand.CommandText = "Select * from tbl1"
>> >> > da.Fill(ds, "tbl1")
>> >>
>> >> First, I would not suggest "SELECT *" in any application. There are
>> >> very
>> >> few
>> >> times when you are actively working with every single row in a table,
>> >> which
>> >> answers your next question. You should only grab the data you need for
>> >> a
>> >> particular function.
>> >>
>> >> > but suppose tbl1 contains 100,000 rows?
>> >>
>> >> Answered above. Do not grab all 100,000 rows unless you are really
>> >> going
>> >> to
>> >> do something with them.
>> >>
>> >> > And all I want to do is to insert a
>> >> > row into tbl1 from the client app?
>> >>
>> >> You can start with a DataSet that matches the table that is empty. Add
>> >> the
>> >> row and then call Update(). If you have the proper INSERT command, it
>> >> will
>> >> see the new row and add it. This is a VERY rough example that assumes
>> >> a
>> >> connection object named conn.
>> >>
>> >> 'TODO: Add row here
>> >> 'This is best done explicitly, column by column
>> >> Dim da As newSqlAdapter()
>> >> Dim cmd As new SqlCommand("INSERT INTO Table (col1, col2) VALUES
>> >> (@col1,
>> >> @col2)")
>> >> cmd.Parameters.Add(new SqlParameter("@col1", value1))
>> >> cmd.Parameters.Add(new SqlParameter("@col2", value2))
>> >>
>> >> conn.Open()
>> >> da.Update(dataTableInQuestion)
>> >>
>> >>
>> >> >I am thinking I want to get just the
>> >> > table without bringing in any rows and then update the table:
>> >>
>> >> Similar to example above. If ALL you are doing is adding a record,
>> >> then
>> >> empty DataSet that matches the table (strongly typed datasets are
>> >> easier
>> >> to
>> >> use in these cases, as you get dotted notation and Intellisense, but
>> >> non-strongly typed will work) and call Update().
>> >>
>> >> > da.InsertCommand.CommandText = "Insert Into tbl1 Select @a"
>> >>
>> >> The SQL above will not work. Look at the example I have given.
>> >>
>> >> > da.InsertCommand.Parameters.Add(New SqlParameter("@a,
>> >> > SqlDBtype.varchar,
>> >> > 30,
>> >> > "colA")
>> >>
>> >> This part is fine.
>> >>
>> >> > da.InsertCommand.Parameters("@a").Value = "testing"
>> >> > da.Update(ds, "tbl1")
>> >>
>> >> You have the right idea here as well. The main thing you need to get
>> >> correct
>> >> is the insert statement, which will follow this basic format:
>> >>
>> >> INSERT INTO tableName (column1, column2)
>> >> VALUES (value1, value2)
>> >>
>> >> Your basic change, other than using real names :-), is to make the
>> >> values
>> >> as
>> >> parameters @value1, @value2 and create a SQL Parameter for each
>> >> parameter
>> >> in
>> >> the statement.
>> >>
>> >> > I am setting this scenario and ask to make sure I understand you
>> >> > correctly,
>> >> > that to get the table structure of "tbl1", in order to insert a row
>> >> > I
>> >> > get
>> >> > the
>> >> > structure of the table with da.Fill rather than
>> >> >
>> >> > da.FillSchema(ds, SchemaType.Source, "tbl1") ?
>> >>
>> >> If you want to get an empty DataSet, without using a strongly typed
>> >> DataSet,
>> >> you can do something like this:
>> >>
>> >> Dim conn As new SqlConnection("connection string here")
>> >> Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
>> >> Dim da As new SqlDataAdapter(cmd)
>> >> Dim ds As new DataSet("DataSet name here")
>> >>
>> >> Try
>> >> conn.Open()
>> >> da.Fill(ds)
>> >> Finally
>> >> conn.Dispose()
>> >> End Try
>> >>
>> >> A strongly typed DataSet can then be created (to avoid calling the
>> >> database
>> >> for definition alone), by adding the statement:
>> >>
>> >> ds.WriteXml("path to write to here")
>> >>
>> >> You then create a new DataSet in your project, open the XMLyou saved
>> >> off,
>> >> and copy the tables from the one you saved to the new one in your
>> >> project.
>> >> You can then completely avoid filling an empty DataSet for an INSERT.
>> >>
>> >> NOTE: The above steps are migratory. If you want to avoid writing the
>> >> fill
>> >> routine, you can simply create a new DataSet, add a data connection to
>> >> Visual Studio (works in Pro or higher in 2005 (maybe Standard, but not
>> >> Express), should be the same in Visual Studio 2003). You can then drag
>> >> a
>> >> table from the database onto the DataSet and have it automagically
>> >> create
>> >> the table definition.
>> >>
>> >>
>> >> --
>> >> Gregory A. Beamer
>> >> MVP; MCP: +I, SE, SD, DBA
>> >>
>> >> *************************************************
>> >> Think outside of the box!
>> >> *************************************************
>> >>
>> >>
>> >>
>>
>>
>>

Thanks. Yes. I did wonder about that, but I sort of figured it out. Thanks
again for all the help and enlightenment.

Rich

"Cowboy (Gregory A. Beamer)" wrote:

> Make the XSD part of your project using this:
>
> http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!374.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
>
> I messed up by stating WriteXml instead of WriteXmlSchema.
>
> Hope that helps!
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> *************************************************
> Think outside of the box!
> *************************************************
> "Rich" <Rich@discussions.microsoft.com> wrote in message
> news:3D5DBB63-F079-4634-B01E-83F26526E13F@microsoft.com...
> > Thank you again. I think I am starting to get the idea. I just
> > experimented
> > with
> >
> > ds.WriteXML("C:\NameOfDataSet.xsd")
> >
> > So I can write an empty table to the disk and then read it back in with
> >
> > ds.ReadXML("C:\NameOfDataSet.xsd")
> >
> > At least I am starting to understand this, but I admit that I like
> >
> > cmd.commandtext = "Select * from tbl1 Where 1 = 2)
> >
> > a little bit more because it is a little more on the fly and I don't have
> > to
> > store anything on the disk. What if the .xsd file gets mysteriously
> > deleted?
> >
> >
> >
> > "Cowboy (Gregory A. Beamer)" wrote:
> >
> >> Glad you like it:
> >>
> >> Any legal drive path (or UNC if you are feeling froggy):
> >>
> >> ds.WriteXML("C:\NameOfDataSet.xsd")
> >>
> >> I know using root is not the best option, but it is a development machine
> >> and it gives me a common place to search for garbage. :-)
> >>
> >>
> >> The idea of writing out the XML came from having to retool a lot of
> >> applications that created DataSets on the fly. In ADO.NET 1.1, we found a
> >> nice perf gain by simply creating strongly typed datasets, so I added
> >> that
> >> line to each routine and then switched each routine to use the STD
> >> instead
> >> of the generic DS. It was an easy fix.
> >>
> >> For the most part, I try to work with a code gened O/R mapping layer
> >> these
> >> days, esp. in .NET 2.0 apps, as the generics add strong typing to
> >> collections. But, there is nothing wrong with using DataSets and there is
> >> an
> >> added bonus: maintainability increases, as they are well documented. :-)
> >>
> >> --
> >> Gregory A. Beamer
> >> MVP; MCP: +I, SE, SD, DBA
> >>
> >> *************************************************
> >> Think outside of the box!
> >> *************************************************
> >> "Rich" <Rich@discussions.microsoft.com> wrote in message
> >> news:348E3A72-8870-4FC0-BACE-59AC800376D8@microsoft.com...
> >> > Wow! Thanks, that is really hot stuff. I am using VB2005, and I do
> >> > sort
> >> > of
> >> > recall dragging tables into the project. But I like your
> >> > ds.WriteXML(...)
> >> > idea.
> >> >
> >> > I also like
> >> >
> >> > Dim conn As new SqlConnection("connection string here")
> >> > Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
> >> > Dim da As new SqlDataAdapter(cmd)
> >> > Dim ds As new DataSet("DataSet name here")
> >> >
> >> > Try
> >> > conn.Open()
> >> > da.Fill(ds)
> >> > Finally
> >> > conn.Dispose()
> >> > End Try
> >> >
> >> >
> >> > May I ask for an example of what I would enter in
> >> >
> >> > ds.WriteXML(...)
> >> >
> >> > That was the only thing that I am not sure what to do.
> >> >
> >> > Thanks again for your great advice!
> >> >
> >> > Rich
> >> >
> >> >
> >> >
> >> > "Cowboy (Gregory A. Beamer)" wrote:
> >> >
> >> >> I am going to try to get through this via inline answers:
> >> >>
> >> >> "Rich" <Rich@discussions.microsoft.com> wrote in message
> >> >> news:92A400AD-F28F-4A23-944A-8ECF9917C33B@microsoft.com...
> >> >> > Thanks. Do you mean explicitly create the command as in
> >> >> >
> >> >> > da.SelectCommand = New SqlCommand
> >> >> > da.SelectCommand.Connection = conn
> >> >> > da.SelectCommand.CommandText = "Select * from tbl1"
> >> >> > da.Fill(ds, "tbl1")
> >> >>
> >> >> First, I would not suggest "SELECT *" in any application. There are
> >> >> very
> >> >> few
> >> >> times when you are actively working with every single row in a table,
> >> >> which
> >> >> answers your next question. You should only grab the data you need for
> >> >> a
> >> >> particular function.
> >> >>
> >> >> > but suppose tbl1 contains 100,000 rows?
> >> >>
> >> >> Answered above. Do not grab all 100,000 rows unless you are really
> >> >> going
> >> >> to
> >> >> do something with them.
> >> >>
> >> >> > And all I want to do is to insert a
> >> >> > row into tbl1 from the client app?
> >> >>
> >> >> You can start with a DataSet that matches the table that is empty. Add
> >> >> the
> >> >> row and then call Update(). If you have the proper INSERT command, it
> >> >> will
> >> >> see the new row and add it. This is a VERY rough example that assumes
> >> >> a
> >> >> connection object named conn.
> >> >>
> >> >> 'TODO: Add row here
> >> >> 'This is best done explicitly, column by column
> >> >> Dim da As newSqlAdapter()
> >> >> Dim cmd As new SqlCommand("INSERT INTO Table (col1, col2) VALUES
> >> >> (@col1,
> >> >> @col2)")
> >> >> cmd.Parameters.Add(new SqlParameter("@col1", value1))
> >> >> cmd.Parameters.Add(new SqlParameter("@col2", value2))
> >> >>
> >> >> conn.Open()
> >> >> da.Update(dataTableInQuestion)
> >> >>
> >> >>
> >> >> >I am thinking I want to get just the
> >> >> > table without bringing in any rows and then update the table:
> >> >>
> >> >> Similar to example above. If ALL you are doing is adding a record,
> >> >> then
> >> >> empty DataSet that matches the table (strongly typed datasets are
> >> >> easier
> >> >> to
> >> >> use in these cases, as you get dotted notation and Intellisense, but
> >> >> non-strongly typed will work) and call Update().
> >> >>
> >> >> > da.InsertCommand.CommandText = "Insert Into tbl1 Select @a"
> >> >>
> >> >> The SQL above will not work. Look at the example I have given.
> >> >>
> >> >> > da.InsertCommand.Parameters.Add(New SqlParameter("@a,
> >> >> > SqlDBtype.varchar,
> >> >> > 30,
> >> >> > "colA")
> >> >>
> >> >> This part is fine.
> >> >>
> >> >> > da.InsertCommand.Parameters("@a").Value = "testing"
> >> >> > da.Update(ds, "tbl1")
> >> >>
> >> >> You have the right idea here as well. The main thing you need to get
> >> >> correct
> >> >> is the insert statement, which will follow this basic format:
> >> >>
> >> >> INSERT INTO tableName (column1, column2)
> >> >> VALUES (value1, value2)
> >> >>
> >> >> Your basic change, other than using real names :-), is to make the
> >> >> values
> >> >> as
> >> >> parameters @value1, @value2 and create a SQL Parameter for each
> >> >> parameter
> >> >> in
> >> >> the statement.
> >> >>
> >> >> > I am setting this scenario and ask to make sure I understand you
> >> >> > correctly,
> >> >> > that to get the table structure of "tbl1", in order to insert a row
> >> >> > I
> >> >> > get
> >> >> > the
> >> >> > structure of the table with da.Fill rather than
> >> >> >
> >> >> > da.FillSchema(ds, SchemaType.Source, "tbl1") ?
> >> >>
> >> >> If you want to get an empty DataSet, without using a strongly typed
> >> >> DataSet,
> >> >> you can do something like this:
> >> >>
> >> >> Dim conn As new SqlConnection("connection string here")
> >> >> Dim cmd As new SqlCommand("SELECT * FROM Table WHERE 1=2", conn)
> >> >> Dim da As new SqlDataAdapter(cmd)
> >> >> Dim ds As new DataSet("DataSet name here")
> >> >>
> >> >> Try
> >> >> conn.Open()
> >> >> da.Fill(ds)
> >> >> Finally
> >> >> conn.Dispose()
> >> >> End Try
> >> >>
> >> >> A strongly typed DataSet can then be created (to avoid calling the
> >> >> database
> >> >> for definition alone), by adding the statement:
> >> >>
> >> >> ds.WriteXml("path to write to here")
> >> >>
> >> >> You then create a new DataSet in your project, open the XMLyou saved
> >> >> off,
> >> >> and copy the tables from the one you saved to the new one in your
> >> >> project.
> >> >> You can then completely avoid filling an empty DataSet for an INSERT.
> >> >>
> >> >> NOTE: The above steps are migratory. If you want to avoid writing the
> >> >> fill
> >> >> routine, you can simply create a new DataSet, add a data connection to
> >> >> Visual Studio (works in Pro or higher in 2005 (maybe Standard, but not
> >> >> Express), should be the same in Visual Studio 2003). You can then drag
> >> >> a
> >> >> table from the database onto the DataSet and have it automagically
> >> >> create
> >> >> the table definition.
> >> >>
> >> >>
> >> >> --
> >> >> Gregory A. Beamer
> >> >> MVP; MCP: +I, SE, SD, DBA
> >> >>
> >> >> *************************************************
> >> >> Think outside of the box!
> >> >> *************************************************
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>