locked
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

Answers

  • 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)

                    conn.Close();

                ConnectionString = "Integrated Security=SSPI;" +

                "Initial Catalog=mydb;" +

                "Data Source=localhost;";

                conn.ConnectionString = ConnectionString;

                conn.Open();

                sql = "CREATE TABLE myTable" +

                "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," +

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

                cmd = new SqlCommand(sql, conn);

                cmd.ExecuteNonQuery();

            }

    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

                newTable.Columns.Add(idColumn);

                newTable.Columns.Add(titleColumn);

                // 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.

                newTable.Indexes.Add(index);

                // Physically create the table in the database

                newTable.Create();

            }

    Related Threads:

    http://www.c-sharpcorner.com/UploadFile/mahesh/CreatingDBProgrammaticallyMCB11282005064852AM/CreatingDBProgrammaticallyMCB.aspx

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/4929a0a8-0137-45f6-86e8-d11e220048c3

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/ed6e07c6-1876-43cd-9bd4-f69dc22a7d58
    • Marked as answer by MSDN FAQ Sunday, June 20, 2010 1:47 PM
    Sunday, June 20, 2010 1:47 PM