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.

Answer 1

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.

Answer 2

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 = ',')