Create SQL Server table programmatically

Question

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.

Answer 1

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.

Answer 2

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