Automatically define the DataTable in C# from the schema of SQL server tables?

Question

I already have some tables in SQL Server and I am writing a C# program to populate the tables from some flat files. I plan to use SQLBulkCopy to load the tables.

It can be a lot of work to define all the columns for each DataTable for SQLBulkCopy. Is it a easy way to generate these DataTables in C# from the definition of the existed SQL Server tables?

I cannot use Bulk insert or bcp because the flat files are in different strange layout and they had to be parsed by some C# code before inserting.

Show us what you have so far. – RBarryYoung Jul 31 '12 at 21:53 — RBarryYoung, Jul 31 '12 at 21:53

score 17 · Accepted Answer · edited Sep 17 '12 at 17:21

17

If you want the brute force approach, you could use the following for every table:

    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection("Some SQLConnectionString")) {
        conn.Open();
        using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT TOP 0 * FROM SomeTable", conn))
        {
            adapter.Fill(dt);
        };
    };

The SELECT TOP 0 will return only the table structure with no records.

edited Sep 17 '12 at 17:21

Blowsie

40,239
15
88
108

answered Jul 31 '12 at 21:58

Holger Brandt

4,324
1
20
35

Usually one flat file need to be used for populating multiple tables. Is it OK to do `new SqlDataAdapter("SELECT TOP 0 * FROM T1; select top 0 * from T2; select top * from t3", conn)`? – ca9163d9 Jul 31 '12 at 22:23
@NickW, I suppose it would work using a `dataset` instead of a `datatable`. However, the names of the tables in the dataset will be defaulted automatically to `Table`,`Table1`,...,`TableN`. – Holger Brandt Jul 31 '12 at 22:45
I know this is an old post but I'm doing something similar and have found the problem with this is the default values from SQL server for the columns that have them don't get set on the datatable so when you go to use the datatable you will have an issue on columns that don't allow nulls because their default values don't get used. – Chris Ward Sep 26 '17 at 19:42

score 1 · Answer 2 · answered Jul 31 '12 at 21:55

1

If you are looking for a quick way to load flat files into SQL, you could use BULK INSERT. Just specify a delimiter and as long as the columns in your files are laid out in the same order as the database table, everything is automatically mapped out. You do need some extra permission to execute bulk inserts, but from my experience, it's the quickest, most efficient way to handle the process.

BULK INSERT Database.dbo.Table
FROM 'C:\inetpub\website\file.csv'
WITH (FIELDTERMINATOR = ',')

answered Jul 31 '12 at 21:55

CoderMarkus

1,118
1
10
24

1

+1: Could also use BCP which is the command-line equivalent, only even faster. – RBarryYoung Jul 31 '12 at 21:57
I cannot use `Bulk insert` or `bcp` because the flat files are in different strange layout and they had to be parsed by some C# code before inserting. – ca9163d9 Jul 31 '12 at 22:05

Automatically define the DataTable in C# from the schema of SQL server tables?

2 Answers2