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.