Create DataTable Schema from SQL

Question

I want to use a dataadapter with a datatable to insert thousands of record into a 30-column sql table.

SqlDataAdapter adapter = new SqlDataAdapter();
DataTable table = new DataTable();
adapter.InsertCommand = new SqlCommand("INSERT INTO ...");
adapter.UpdateBatchSize = 1000;
DataRow r = null;
foreach(var entry in list) 
{
    r = table.NewRow();
    r["lastchange"] = entry.TryGet("LastChangeTime"); 
    // <- throws System.ArgumentException: Column does not belong to table
    ...
}

Is there any way to not manually define the schema of the datatable, but to read it from the table the insertions should take place in?

This is possible duplicate of [SQL statement to get column type](https://stackoverflow.com/questions/13405572/sql-statement-to-get-column-type) — Marek Vitek, Jul 04 '17 at 08:26

score 1 · Accepted Answer · answered Jul 04 '17 at 08:25

1

Define SelectCommand and apply Fill method to get data first. If you need only table schema just make the query which returns no rows.

SqlDataAdapter adapter = new SqlDataAdapter();
DataTable table = new DataTable();
adapter.SelectCommand = new SqlCommand("SELECT * FROM myTable WHERE 1=2");
adapter.Fill(table);

answered Jul 04 '17 at 08:25

Serg

22,285
5
21
48

DO NOT EVER DO THAT. I have seen this in real world and it was terrible performance disaster. – Marek Vitek Jul 04 '17 at 09:02
@MarekVitek, under circiumstances everything can cause a disaster. Can you be more specific, which line is dangerous? – Serg Jul 04 '17 at 10:11
Dangerous is getting data structure by running query against DB. You might think that your query is simple, then will come someone who thinks why not put it behind view and suddenly you find yourselves running query against view containing multiple joins and few conditions so even simple query will end up doing lot of work. – Marek Vitek Jul 04 '17 at 11:19
There are better ways how to get database structure. As we are not talking specific DB engine, here are few examples [MS SQL](https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql) and [Oracle](http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12019.htm) and [PostrgreSQL](http://www.postgresqltutorial.com/postgresql-describe-table/) and [MySQL](https://dev.mysql.com/doc/refman/5.7/en/getting-information.html) I believe that your trick works just fine most of the time. But it will burn you one day. – Marek Vitek Jul 04 '17 at 11:37
1

@MarekVitek, Yes, running this against complex view may be costly, but sure it's not OP's case as he says "insert into .. sql table.", and `SqlDataAdapter` implies it's MS sql-server. – Serg Jul 04 '17 at 12:19
@MarekVitek, Can you specify a better solution please. – dellos Nov 03 '20 at 03:44
1

@dellos, I am not C# programmer. But you might want to look at [FillSchema](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbdataadapter.fillschema?view=netcore-3.1&viewFallbackFrom=dotnet-plat-ext-3.1) . I believe it is what you are looking for. – Marek Vitek Nov 03 '20 at 17:33
@MarekVitek, "The FillSchema method retrieves the schema from the data source using the SelectCommand." – Serg Nov 04 '20 at 08:41
@Serg It may be *more idomatic* to use `FillSchema()`. I agree your method works perfectly fine in most cases. If it doesn't, either you're doing something strange (like targeting a view), the RDBMS is doing something strange, or the DB contention is so high that you should define the table in application logic anyways. The doc says that `FillSchema()` uses `FOR BROWSE`. That has its own caveats, and suggests that it uses `sp_describe_first_result_set` or `sys.dm_exec_describe_first_result_set`. One benefit of `FillSchema()` is that the same query for fetching data and schema can be used. – Bacon Bits Nov 13 '20 at 14:17

score 0 · Answer 2 · answered Jul 04 '17 at 08:25

You can create/define dataset in your project and can use it in your subsequent operation wherever you need.

Have a look at below link showing how to add dataset to your project and add tables into it and also how to use data adapters with this dataset.

https://msdn.microsoft.com/en-us/library/04y282hb.aspx

https://msdn.microsoft.com/en-us/library/ms171919.aspx

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/populating-a-dataset-from-a-dataadapter

I hope this will help you. :)

Create DataTable Schema from SQL

2 Answers2