C# SQL create table IF it doesn't already exist

Question

Hey, I am trying to put a little logic into my C# app that will create a table called Import, IF it doesn't already exist.. here is my code, it doesn't seem to work tho.

con.Open();
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText =
                            @" 
                       IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'RX_CMMData' AND TABLE_NAME = 'Import'))
BEGIN
CREATE TABLE Import (
  RowId     integer PRIMARY KEY NOT NULL,
  PartNumber  varchar(200) NOT NULL,
  CMMNumber   varchar(200) NOT NULL,
  Date        varchar(200) NOT NULL,
  FeatType    varchar(200) NOT NULL,
  FeatName    varchar(200) NOT NULL,
  Value       varchar(200) NOT NULL,
  Actual      decimal,
  Nominal     decimal,
  Dev         decimal,
  TolMin      decimal,
  TolPlus     decimal,
  OutOfTol    decimal,
  FileName    varchar(200) NOT NULL
); END";
                        cmd.ExecuteNonQuery();
                    }
                    con.Close();

Answer 1

Some DBMSes allow you to do CREATE TABLE IF NOT EXISTS <tablename> inside the SQL. I'd check the syntax for your DBMS.

Answer 2

syntax of that under sql server 2005 is

  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tablename]') AND type in (N'U'))
  BEGIN
   create
  END

in your code, where is the = FALSE or NOT keyword

Answer 3

Your SQL is creating the table if it does exist and not if it doesn't.

Change the SQL to read "IF NOT EXISTS".

Answer 4

If you're using Oracle, you could try

select count(*) from user_tables
where table_name = ''

which should tell you if the table exists

Answer 5

You're probably creating the table in the default schema (typically dbo.)

Specify the full name, like:

CREATE TABLE RX_CMMData.Import

Answer 6

You can also look into using Microsoft SMO objects. I prefer to work SMO objects and let them do the work for me if possible instead of executing SQL text through code.

Using the SMO Database object you can do the following.

Database db = myServer.Databases["MyDB"];

if (! db.Tables.Contains("NewTable"))
{

    Table tbl = new Table(db, "NewTable");

    Column col1 = new Column(tbl, "Column1", DataType.Varchar(10));
    col1.Nullable = true;
    tbl.Columns.Add(col1);

    tbl.Create();

}

http://www.mssqltips.com/tip.asp?tip=1826