Power By 专否社区

Create SQL Server table programmatically

Go To StackoverFlow.com

4

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"]

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.

2012-04-05 18:12
by wingman55
0_o I don't know of any RDBMS where rows have names. In every RDBMS that I'm aware of, columns have names.. - NoName 2012-04-05 18:13
Welcome to StackOverflow. When you're asking about something that varies widely (like support for specific SQL syntax), you should provide a more specific tag along with the generic one. In this case, since DBMSs vary widely in their SQL syntax, it's important to know what database you're using (MySQL, SQL Server, etc.). Some don't even allow creation of a database using SQL (DDML) statements. Please edit your question to add the tag for the database engine you're using, as it's pretty difficult to provide one answer that covers all of them. Thanks. : - Ken White 2012-04-05 18:18
You can generate an SQL table from columns in DataTable. Is this any good - MoonKnight 2012-04-05 18:18
I updated the question to be more precisely - wingman55 2012-04-05 21:03
@wingman55 Please let us know if you need more help or mark an answer as correct - Trisped 2012-04-06 17:04

11

The simple answer is

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

from w3Schools.com

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

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.

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.

2012-04-05 21:32
by Trisped

1

Are you looking to implement something like an Entity–attribute–value model?

http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

2012-04-05 18:18
by ficuscr
Ads