FAQ Item: How to create a new table for a SQL Server database using C#? RRS feed

  • Question

  • How to create a new table for a SQL Server database using C#?
    Sunday, June 20, 2010 1:46 PM


  • There are two ways to create a table for a database,

    1.       We can use ADO.NET and write and execute T-SQL statement to create a table.

            private void CreateTableBtn_Click(object sender, System.EventArgs e)


                // Open the connection

                if (conn.State == ConnectionState.Open)


                ConnectionString = "Integrated Security=SSPI;" +

                "Initial Catalog=mydb;" +

                "Data Source=localhost;";

                conn.ConnectionString = ConnectionString;


                sql = "CREATE TABLE myTable" +

                "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," +

                "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)";

                cmd = new SqlCommand(sql, conn);



    2.       We can reference the SMO library and create a table using SMO functions,

            private void CreateTableBtn_Click(object sender, System.EventArgs e)


                // Establish the database server

                string connectionString = "...";

                SqlConnection connection =

                     new SqlConnection(connectionString);

                Server server =

                     new Server(new ServerConnection(connection));

                // Create table in my personal database

                Database db = server.Databases["mydb"];

                // Create new table, called TestTable

                Table newTable = new Table(db, "TestTable");

                // Add "ID" Column, which will be PK

                Column idColumn = new Column(newTable, "ID");

                idColumn.DataType = DataType.Int;

                idColumn.Nullable = false;

                idColumn.Identity = true;

                idColumn.IdentitySeed = 1;

                idColumn.IdentityIncrement = 1;

                // Add "Title" Column

                Column titleColumn = new Column(newTable, "Title");

                titleColumn.DataType = DataType.VarChar(50);

                titleColumn.Nullable = false;

                // Add Columns to Table Object



                // Create a PK Index for the table

                Index index = new Index(newTable, "PK_TestTable");

                index.IndexKeyType = IndexKeyType.DriPrimaryKey;

                // The PK index will consist of 1 column, "ID"

                index.IndexedColumns.Add(new IndexedColumn(index, "ID"));

                // Add the new index to the table.


                // Physically create the table in the database



    Related Threads:



    • Marked as answer by MSDN FAQ Sunday, June 20, 2010 1:47 PM
    Sunday, June 20, 2010 1:47 PM