Adding column names from a data table

Question

So I have this chunk of code that uploads data tables to a data base table.

public bool TableCreator(DataTable dt, string tableName)
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        try
        {
            /*####  Method needs to include adding apropreate amount of colunm headers  ####*/
            string colunmHeader = " (TestColunmHeader varchar(50));";
            //Opens connection to the DataBase
            con.Open();
            //Creates a new Table in the Database
            using (SqlCommand command = new SqlCommand("CREATE TABLE " + tableName + colunmHeader, con))
                command.ExecuteNonQuery();
            UploadConnection(dt, tableName);
            return true;
        }
        catch (Exception)
        {
            return false;
        }
    }
}

This works great if I "Hard Code" the column names and amounts but I need the new table that's created in the database to have the same number of columns as the dataTable.

I have tried a few things but I'm just lacking the knowledge to do this.

Possible duplicate of Converting a DataTable to an CREATE TABLE + INSERT script for SQL in C# — Aftab Ahmed
– Aftab Ahmed, Commented Aug 17, 2016 at 11:41
That shows the bulk insert which i have, its the creating the column names in the sql data table based of the data table in the application that i need. Is this why it was downvoted? — Ryan white
– Ryan white, Commented Aug 17, 2016 at 11:55

MMan · Accepted Answer · 2016-08-17 12:56:05Z

0

If i understand correctly, all you want to do is to create a table in a sql database that contains an X amount of columns given by a DataTable.

So i guess the first thing you should know is how DataTables are defined in the context of how it stores columns. A quick look at the documention will reveal that you have a column property. In that page you can see an example how you can iterate thru every column in you DataTable.

The second would be to know how you can create a an SQL command to create a table with columns (of which you seem to understand the basics of it).

So it should be a matter of just building a colunmHeader string that contains all of the definitions of every column. You could even use that documentation example as a small guide to build said string, that way your columnHeader will look like "(TestColunmHeader varchar(50), TestColunmHeader1 varchar(50), TestColunmHeader2 varchar(50))" and so on.

answered Aug 17, 2016 at 12:56

MMan

7810 bronze badges

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

3 Comments

Ryan white Over a year ago

Could this be built to be sort of dynamic? it will change from data table to data table

MMan Over a year ago

It could definitely be built so that it will dynamically create different sql DataTables of different column sizes, given that for your function "TableCreator(DataTable dt, string tableName)" every dt and tableName you supply to your function are well defined and unique.

Ryan white Over a year ago

ah cool, well ill put this as the correct answer then. Thank you

Collectives™ on Stack Overflow

Adding column names from a data table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related