ADO.NET Cookbook

10.8. Adding Tables to a Database

Problem

You need to add a table to an existing database.

Solution

Use the CREATE TABLE statement.

The sample code executes the DDL statement—using the ExecuteNonQuery( ) method of the Command object—to add a table to an existing SQL Server database.

The C# code is shown in Example 10-8.

Example 10-8. File: AddTableToDatabaseForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

//  . . . 

SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

String createSql = "CREATE TABLE MyTable " +
    "(MyTableId int IDENTITY(1,1) PRIMARY KEY CLUSTERED)";

SqlCommand cmd = new SqlCommand(createSql, conn);
// Create the table in the database.
try 
{
    conn.Open( );
    cmd.ExecuteNonQuery( );
    resultTextBox.Text = "Table created successfully";
}
catch (System.Exception ex)
{
    resultTextBox.Text = ex.ToString( );
}
finally
{
    if (conn.State == ConnectionState.Open)
        conn.Close( );
}

Discussion

There are two categories of SQL statements:

Database Definition Language (DDL)

Used to manage all objects in the database, generally with CREATE, ALTER, and DROP statements to create, modify, and delete objects, respectively. These statements generally require DBA permissions to execute.

Database Management Language (DML)

Used to manipulate—select, insert, update, and delete—data in the database objects. Database objects are defined using DDL.

The solution executes a DDL