如何使用C＃中的动态列数创建SQL Server表？ [英] How to create SQL server table with dynamic number of columns from C#?

问题描述

您好，我正在为汽车行业开发应用软件。我在GUI中使用多个datagridviews来向用户显示一些关键数据。在某些数据表中，数字列是固定的，因此我在创建动态sql server表时没有问题，其中列名与datatable中的列名相同，并将表数据从C＃传输到sql server表。



在某些数据表中，用户希望根据需要添加/删除任意数量的列。所以在这种情况下，我不能硬编码具有固定列数的动态sql server表创建。我正在考虑使用IF条件，例如

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
}

但我觉得这是一种拖延方式，我应该限制用户添加超出特定计数的coulmns。所以我想问一下你如何克服这个问题的建议？请帮帮我



我的尝试：

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:

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();

我也提到了SQL pivot主题。但这不是我要求的。

http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql

推荐答案

我有同样的问题，我做的是创建一个包含数据表名称的字符串，然后将它们添加到我的命令构建器。

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.

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();

        }

这篇关于如何使用C＃中的动态列数创建SQL Server表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！