.NET Answers

Once you have your database created, you’ll want to get some tables set up into it.  To do this, you’ll need the CREATE TABLE statement that can be issued from the connection object once you’ve established the connection.

The first thing you’ll need to do before executing this command is to establish a connection to your SQL database.

Once you’ve done that, you’ll want to create a SqlCommand object passing in the CREATE TABLE command string and the connection object.

Finally, you’ll open the connection, issue the ExecuteNonQuery() method and close the connection.

The CSharp code for this looks something like:

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(createTable, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Pretty standard code that I hope you are already familiar with.

But what you probably want to know is what to put in the createTable string.  Here you go:

string createTable = @"CREATE TABLE tableName
(
ColumnName ColumnType(size)
    [IDENTITY(seed,increment)]
    [NULL|NOT NULL
    [DEFAULT defaultData]]
    [PrimaryKey]
[comma delimited column statements go here]
)";

I like to use the @ string statement so that I can create my CREATE TABLE statements in SQL Enterprise Manager and copy and paste the results into my code without having to worry about carriage return/linefeed pairs causing me to do extra editing once I paste the code into my CSharp editor.

A column statement is in the form of:

ColumnName ColumnType(size)
    [NULL|NOT NULL
    [DEFAULT defaultData]]
    [Primary Key]

Where ColumnType is one of the various data types available in T-SQL

The datatypes you are most likely to use are:

• Int
• varchar(size) or nvarchar(size)
• datetime
• decimal(precision,scale)
• money

If you want the field to hold NULL values, you can leave the NULL or NOT NULL qualifier out, but if you want to make sure the field always holds some sort of valid data, you’ll want to include the NOT NULL statement after your type and size information.

And if the field is the primary key for the table, include the Primary Key statement at the end of the column description.

If you want the field to auto-increment, you’ll want to include the identity information.  Assuming you want to start at 1 and increment by 1, your SQL will look like:

    IDENTITY(1,1)

To put this all together in a typical CREATE TABLE string, here is what a typical CREATE TABLE string would look like for a Users table:

string createTable = @"CREATE TABLE [dbo].[Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Username] [nvarchar](100) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Email] [nvarchar](256) NULL,
    [DisplayName] [nvarchar](128) NOT NULL DEFAULT (''),
)";

There is more to the CREATE TABLE syntax, but this should get most programmers as far as they need to go.