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 — Marek Vitek
– Marek Vitek, Commented Jul 4, 2017 at 8:26

Serg · Accepted Answer · 2017-07-04 08:25:04Z

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 4, 2017 at 8:25

Serg

22.9k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Marek Vitek Over a year ago

DO NOT EVER DO THAT. I have seen this in real world and it was terrible performance disaster.

Serg Over a year ago

@MarekVitek, under circiumstances everything can cause a disaster. Can you be more specific, which line is dangerous?

Marek Vitek Over a year ago

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.

Serg Over a year ago

@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.

Marek Vitek Over a year ago

@dellos, I am not C# programmer. But you might want to look at FillSchema . I believe it is what you are looking for.

|

Hitesh · Accepted Answer · 2017-07-04 08:25:32Z

-1

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. :)

answered Jul 4, 2017 at 8:25

Hitesh

4,0102 gold badges18 silver badges21 bronze badges

Collectives™ on Stack Overflow

Create DataTable Schema from SQL

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related