SledgeHammer SledgeHammer - 1 month ago 4x
SQL Question

Table schema as DataTable?

I did a search and found some seemingly related answers, but they don't really do what I'm after.

Given a valid connection string and a table name, I want to get a

of the table. I.e. if the table has a column called "Name", I want the DataTable set up so I can do
dt["Name"] = "blah"

The trick is, I don't want to hard code any of that stuff, I want to do it dynamically.

People tell you to use
, but that gives you back a table with a bunch of stuff in it.

Everybody has random tricks like
TOP 0 * from the table
and get the schema from there, etc.

But is there a way to get the table with the primary keys, unique indexes, etc. Ie.. in the final format to do a bulk insert.


You can use SqlDataAdapter.FillSchema:

var connection = @"Your connection string";
var command = "SELECT * FROM Table1";
var dataAdapter = new System.Data.SqlClient.SqlDataAdapter(command, connection);
var dataTable = new DataTable();

dataAdapter.FillSchema(dataTable, SchemaType.Mapped);

This way you will have an empty DataTable with columns and keys defined and ready to use in code like dataTable["Name"] = "blah";.