创建SQL表编程 [英] create sql table programmatically

问题描述

我需要以编程方式创建在C＃中的SQL 2008表，使得表中的列应列的名单中产生（每列名在表中的行的名称）

I need to programmatically create a SQL 2008 table in C# such that the columns of the table should be generated from a list of columns (each column name is the name of a row in the table)

我的问题是什么是通过列的列表中的命令字符串循环，并创建该表的记录：

My question is what is the command string to loop through the list of columns and creates the table's recorded:

列表columnsName = [COL1 ，COL2，COL3]

List columnsName = ["col1","col2","col3"]

我想创建一个表，在columnsName列。但是，由于在不恒定列表的大小，我需要遍历该列表生成表列中。

I want to create a table with the columns in the columnsName. But since the list size in not constant, i need to loop through the list to generate the table columns.

推荐答案

简单的答案是

CREATE TABLE table_name
(
    column_name1 data_type,
    column_name2 data_type,
    column_name3 data_type,
    ....
)

从的 w3Schools.com

在C＃中使用字符串生成器，来连接查询，然后执行查询

In C# use a string builder to concatenate the query and then execute the query.

StringBuilder query = new StringBuilder();
query.Append("CREATE TABLE ");
query.Append(tableName);
query.Append(" ( ");

for (int i = 0; i < columnNames.Length; i++)
{
    query.Append(columnNames[i]);
    query.Append(" ");
    query.Append(columnTypes[i]);
    query.Append(", ");
}

if (columnNames.Length > 1) { query.Length -= 2; }  //Remove trailing ", "
query.Append(")");
SqlCommand sqlQuery = new SqlCommand(query.ToString(), sqlConn);
SqlDataReader reader = sqlQuery.ExecuteReader();

请注意：tableName值，COLUMNNAMES和columnTypes将与什么都你是从获取数据所取代。从你的描述它听起来就像你从查询中获取列值，因此，而不是使用一个for循环和数组，你可能会被使用，同时通过循环的结果，重复，以构建查询。让我知道如果你使用这种方法，我会做一个，今晚需要一个例子。

Note: tableName, columnNames, and columnTypes would be replaced with what ever you are getting the data from. From your description it sounds like you are getting the column values from a query, so rather than using a for loop and arrays you will probably be using a while loop to iterate through the results to build the query. Let me know if you need an example using this method and I will make one tonight.

如果您有创建，你可以尝试创建表的语法麻烦表（或一个示例表）的MS SQL Server Management Studio中，然后右击该表并选择脚本表as\Create To\New查询编辑器窗口。这将显示你的脚本将用来建立查询。

If you are having trouble with the syntax for creating the table you can try creating the table (or a sample table) in MS SQL Server Management Studio, then right click the table and select Script Table as\Create To\New Query Editor Window. This will show you the script it would use to build the query.

这篇关于创建SQL表编程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！