Creating the DataTable objects within a Dataset from the DB itself

[Ed Minrights]

I have a feeling I'm thinking about this the wrong way...

I need to insert lots of rows into lots of different tables and then
submit the whole lot into the database. I'm not concerned about what
rows will already be present in the database, these will all be
inserts only. The data itself will come from a non-DB source. I want
to present the whole thing to the user as a DataGrid before they agree
to submit it all.

With this situation, it seems a bit daft to have to create the all the
DataTable objects programatically (using new DataColumn etc..), when I
simply want the DataTable objects in the DataSet to match those
already in the DB itself. So I want to be able to say "create
DataTable in DataSet based on actual table 'name'".

I figure I could perhaps create the DataTable objects by submitting a
select statement for each table, but I wouldn't want any rows returned
because I'd only want to show the new data in the datagrid.

Any pointers in the right direction?

Thanks

 

[William \(Bill\) Vaughn]

Is this SQL Server you plan to target? I tend to avoid designs that
construct INSERT statements, but this might work. However, an approach I
would lean toward is using BCP/DTS SqlBulkCopy to move the rows from the
populated tables. It's easy to create a DataTable (or several) from an
existing database table--simply execute

SELECT * FROM myTable WHERE 1=0

for each table that you want to target. In TSQL you can concatenate several
of these SELECT statements together to construct several local DataTable
objects in a single round trip. Use the Fill to do so.
Ok, now that the tables are populated, you can use SqlBulkCopy to upload the
tables to the database by extruding a DataReader from the DataTable (a new
2.0 feature). I would not try to bulk copy to the actual tables though, I
would copy to a temporary table and use a server-side SP to INSERT the new
rows so that the RI, rules and triggers can do their validation thing.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Ed,

In the Select statement you could supply a Where clause that is guranteed to
return zero rows, such as:

Select * From myTable Where 1 = 0

This should return just the table structure, without any data rows.

Kerry Moorman

 

[Cor Ligthert [MVP]]

Ed,

Do you mean that you are searching for the fillschema

http://msdn.microsoft.com/library/d...tacommondbdataadapterclassfillschematopic.asp

I hope this helps,

Cor

 

[Ed Minrights]

Ed,

Do you mean that you are searching for the fillschema

http://msdn.microsoft.com/library/d...tacommondbdataadapterclassfillschematopic.asp

I hope this helps,

Exactly what I needed, thanks very much. Quite a few of the books that
I have don't even mention the FillSchema method.