以编程方式创建 SQL Server 表 [英] Create SQL Server table programmatically

问题描述

我需要在 C# 中以编程方式创建一个 SQL Server 2008 表，以便从列列表生成表的列(每个列名是表中一行的名称)

I need to programmatically create a SQL Server 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:

List<string> 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 将被替换为您从中获取数据的任何内容.根据您的描述，听起来您正在从查询中获取列值，因此您可能会使用 while 循环来迭代结果以构建查询，而不是使用 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 中创建表(或示例表)，然后右键单击表并选择 Script Table asCreate ToNew Query编辑器窗口.这将显示用于构建查询的脚本.

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 asCreate ToNew Query Editor Window. This will show you the script it would use to build the query.

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