How to create SQL server table with dynamic number of columns from C#?

Question

0.00/5 (No votes)

See more:

Hi, i am developing an application software for an automotive industry. i am using multiple number of datagridviews in my GUI to display some critical data to the user. In some data table the number columns are fixed so i have no problem in creating a dynamic sql server table with the column names same as in datatable and transferring the table data from C# to sql server table.

Whereas in some datatables the user wants to add/delete any number of columns as per their needs. So in such cases i can't hard code the dynamic sql server table creation with fixed number of columns. I am thinking of using IF condition like

if(dataGridView1.Columns.Count == 2)
{
var commandStr = "If not exists (select name from sysobjects where name = 'DGV2') CREATE TABLE DGV2(" + dataGridView2.Columns[0].Name + " char(50)," + dataGridView2.Columns[1].Name + " char(50))";
}
if(dataGridView1.Columns.Count == 3)
{
....//add 3 number of columns
}

but I feel it's a dragging way and i should restrict user from adding coulmns beyond a particular count. So i want to ask your suggestions on how to overcome this problem? please help me

What I have tried:

C#

string connectionString = @"Data Source=LENOVO-PC\SQLEXPRESS;Initial Catalog=TESTDB;Integrated Security=True";

            SqlConnection connection = new SqlConnection(connectionString);

            connection.Open();

            var commandStr = "If not exists (select name from sysobjects where name = 'DGV2') CREATE TABLE DGV2(" + dataGridView2.Columns[0].Name + " char(50)," + dataGridView2.Columns[1].Name + " char(50))";

            using (SqlCommand command = new SqlCommand(commandStr, connection))

            command.ExecuteNonQuery();

            connection.Close();

also i referred SQL pivot topic. But it's not what i require.
http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql

Posted 28-Feb-17 21:01pm

Member 11905879

Updated 3-May-17 23:36pm

Add a Solution

Comments

Garth J Lancaster 1-Mar-17 4:59am

I worked with some software and they always allowed for their tables to have a set number of 'user fields', so they'd have

user_int_1
user_int_2
...
user_text_1
user_text_2

etc up to 5 or 10 of each type of field required. I used to think it was as ugly as sin, but, compared to hand concatenating sql to create a 'dynamic' table its a godsend - periodically, if the user could make a case, the DB schemas were updated, and some of the user_ fields migrated into the 'standard schema'

If I were going to do it your way I'd likely build a procedure that takes a list/array of tuples/structs, representing the field name and datatype, and builds a SQL create statement iteratively from the list

Member 11905879 1-Mar-17 5:22am

Yeah agreed. Will be better to do with the structs

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pilot13 · Answer 1 · 2017-05-03T23:36:00

I had a same problem and that i did was to create a string with the names of the datatable and then adding them to my command builder.

C#

private string GetColumnNames(DataTable dt)
        {
            string name = String.Empty;
            foreach (DataColumn dc in dt.Columns)
            {
                 name += dc.ColumnName + " NVARCHAR(50)"+", "; 
            }
            return name;

        }
        private void CreateTableFromDataTable(DataTable dt,string tablename,string columns)
        {
            string createString = "CREATE TABLE "+tablename+" ("+ columns+")"; //YOUR SQL COMMAND TO CREATE A TABLE                      
            SqlCommand create = new SqlCommand(createString, sqlconnection);
            sqlconnection.Open();            
            create.ExecuteNonQuery();
            sqlconnection.Close();

        }