|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Creating the DataTable objects within a Dataset from the DB itselfI 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 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 -- Show quote____________________________________ 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. __________________________________ "Ed Minrights" <none@nospam.com> wrote in message news:9l4a4251ruhf3v02brmttgo9ep35dq047b@4ax.com... > > 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 > > 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 Show quote "Ed Minrights" wrote: > > 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 > > > Ed,
Do you mean that you are searching for the fillschema http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondbdataadapterclassfillschematopic.asp I hope this helps, Cor Show quote "Ed Minrights" <none@nospam.com> schreef in bericht news:9l4a4251ruhf3v02brmttgo9ep35dq047b@4ax.com... > > 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 > > On Wed, 19 Apr 2006 07:12:45 +0200, "Cor Ligthert [MVP]"
<notmyfirstn***@planet.nl> wrote: >Ed, Exactly what I needed, thanks very much. Quite a few of the books that> >Do you mean that you are searching for the fillschema > >http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondbdataadapterclassfillschematopic.asp > >I hope this helps, I have don't even mention the FillSchema method.  |
|||||||||||||||||||||||
Other interesting topics