4.6 Commands and Data Definition Language (DDL)

Most data-access code focuses on Data Manipulation Language (DML) commands. These instructions change, delete, or retrieve information about the values in tables. Typically, a database administrator creates the tables as part of a separate process, using a dedicated tool. This isn't always the case. Sometimes you want to create or modify table structure directly from your code. To do this, you need Data Definition Language (DDL) commands. SQL defines many basic commands various database vendors use to implement and sometimes extend. These include old standbys such as DROP TABLE and CREATE DATABASE.

ADO, the previous generation of data-access technology, had a sibling called ADOX that provided an object-oriented wrapper for the DDL commands. ADO.NET doesn't have any such niceties. However, you can still modify table structure or create new tables programmatically; you just have to do it the hard way, by constructing a Command and executing it with the ExecuteNonQuery( ) method. Example 4-9 shows a trivial example that uses the CREATE TABLE statement to create a single table with two columns. In this case, the table is created in the Northwind database because that's the initial database selected when the connection is opened.

Example 4-9. Inserting a new table programmatically

// DDL.cs - Inserts a new table

using System;
using System.Data.SqlClient;

public class UpdateRecord
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                      "Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "CREATE TABLE Users ("+
                      "UserName nvarchar(20), Password nvarchar(20) )";


        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);

        // Execute the command.
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();

        // Display the result of the operation.
        Console.WriteLine(rowsAffected.ToString() + " row(s) affected");
    }
}

In this case, rowsAffected will be -1 because the Command didn't execute an UPDATE, DELETE, or INSERT statement.