0

I am creating table(TestTable) in sql using the below code in c#, now i am trying to specify a primary index for this table on col2, can anyone suggest me how to achieve this through code.

Server srv = new Server("(local)");
Database db = srv.Databases["AdventureWorks2012"];

Table tb = new Table(db, "TestTable");
Column col1 = new Column(tb, "Name", DataType.NChar(50));
Column col2 = new Column(tb, "ID", DataType.Int);

tb.Columns.Add(col1); 
tb.Columns.Add(col2); 
tb.Create();
user2780650
  • 1
  • Possible duplicate http://stackoverflow.com/questions/3621721/marking-existing-column-as-primary-key-in-datatable – mockinterface Feb 02 '14 at 09:05
  • 2
    Nope, here he want´s to create a table in the database programaticly. – carleson Feb 02 '14 at 09:18
  • If you have a look in this post you could transform your data tables into tabels in your database. This solutio is generic and should work on any datatables that you want to transform. But.. I dont know why you create your tables this way since it is not the common way to do it. The Post: http://stackoverflow.com/questions/1348712/creating-a-sql-server-table-from-a-c-sharp-datatable – carleson Feb 02 '14 at 09:21

1 Answers1

0

To create something like that

   CREATE TABLE table_name
    (
    column_name1 data_type(size) constraint_name,
    column_name2 data_type(size) constraint_name,
    column_name3 data_type(size) constraint_name,
    ....
    );

We 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]);
        if (i=0)
        query.Append(" PRIMARY KEY   , ");
        else
        query.Append(" , ");
    }

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