以编程方式创建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将替换为您从中获取数据的内容.从您的描述中听起来好像您正在从查询中获取列值，因此，与其使用for循环和数组，您可能会使用while循环来遍历结果以构建查询.让我知道您是否需要使用此方法的示例，今晚我将做一个.

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中创建表(或示例表)，然后右键单击该表并选择脚本表为" \创建到" \新查询"编辑器窗口.这将向您显示用于构建查询的脚本.

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 Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！