how to?: if table doesn't exist on database, create it using DataReader

I want to scan a database and then create a table using DataReader if it does not already exist. I found a block of code that is used to create a table, but it is in VB, which is not ideal as the rest of my code is C#. Regardless, I can't get it to work either way.

This is my code, thanks for taking a look:

var dif = new DataInterfaceFactory(DatabaseTypes.SqlServer, " DATABASE_NAME", "[SERVER_NAME]");

            using (DataReader dr = dif.GetDataReader())
            {
                exists = m_api.Database.ExecuteCommand(@"IF EXISTS(SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TABLE_NAME')");

                while (dr.Read())
                {
                    if (exists == 0)
                    {
                        TableBuilder calculationSchedule = new TableBuilder("TABLE_NAME", dif.DatabaseType);
                        calculationSchedule.AddField("TABLE_NAME_UID", DbFieldType.int_, 0, false, null, true, null);
                        calculationSchedule.AddField("SERVER_NAME", DbFieldType.nvarchar_);
                        calculationSchedule.AddField("DATABASE_NAME", DbFieldType.nvarchar_);
                        calculationSchedule.AddField("CHECK_DATE", DbFieldType.datetime_);
                        calculationSchedule.AddField("IS_RUNNING", DbFieldType.int_);

                        using (CommandExecutor cex = dif.GetCommandExecutor())
                        {
                            calculationSchedule.BuildTable(cex);
                        }
                    }
               }
           }

Solution

you can use SQL Servers Information Schema in order to identify whether the table(s) exists on the RDBMS or not.

select count(1) from INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'mySchema' and TABLE_NAME = 'myTable'

if the result is 0, it means the table does not exists, if the result is 1, the table exists under the schema.

now, you can use datareader to query your database and check whether your table exists or not.

then you can issue a create command to create your Table

this creates table if it does not exists

BEGIN
    if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'CALCULATION_SCHEDULE')
        create table CALCULATION_SCHEDULE (
            Name varchar(64) not null
            ...
        )
END;