CODEWITHSUNDEEP
c#sql-serverado.net
codeulike
codeulike

Reputation: 23064

How to save an arbitrary DataTable to a new SQL Table

Suppose I have an ADO.NET DataTable that I was to 'persist' by saving it to a new table in a SQL Server database - is there a fast way of doing this?

I realise I could write code generating the DDL for the 'CREATE TABLE' statement by looping through the DataColumns collection and working out the right type mappings and so on ... but I'm wondering if there is an existing method to do this, or a framework someone has written?

(NB: I need to be able to handle arbitrary columns, nothing too fancy like blobs; just common column types like strings, numbers, guids and dates. The program won't know what the columns in the DataTable are until run-time so they can't be hard-coded.)

Upvotes: 2

Views: 4512

Answers (4)

RBarryYoung
RBarryYoung

Reputation: 56725

ADO.net cannot create tables in SQL Server directly, however, SMO can do this with the .Create method of the Table class. Unfortunately, there is no built-in way to use a DataTable to define an SMO Table object.

Fortunately, Nick Tompson wrote just such a DataTable-to-SMO.Table routine back in 2006. It is posted as one of the replies to this MSDN forums topic http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/4929a0a8-0137-45f6-86e8-d11e220048c3/ (edit: I can make hyperlinks now).

Note also, the reply post that shows how to add SQLBulkCopy to it.

Upvotes: 3

A-K
A-K

Reputation: 17080

I might be easier to store your DataTable as XML. just create a table with an XML column.

Upvotes: 0

Ahmed
Ahmed

Reputation: 7238

I think this post can help

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062600

If the table exists, you can use SqlBulkCopy (which will accept a DataTable) to get the data into the table in the fastest possible way (much faster than via an adapter). I don't think it will create the table though. You might have to write the DDL yourself, or find some existing code to loop over the DataTable.Columns to do it.

Upvotes: 1

Related Questions