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

Question

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);
                        }
                    }
               }
           }

which rdbms are you using? apparently WDM_SOFTWARE_INFO table is not available in your database — Derviş Kayımbaşıoğlu
– Derviş Kayımbaşıoğlu, Commented Jan 24, 2019 at 11:18
As you see In DataReader Name itself, it is for reading purpose not writing or modifying — Yashar Aliabbasi
– Yashar Aliabbasi, Commented Jan 24, 2019 at 11:20
@Simonare, I am using SQL Server Management Studio. the database definitely exists in that server. — Joe Joe Joe
– Joe Joe Joe, Commented Jan 24, 2019 at 11:28
@Yashar, I know that, but it needs to read the database in order to know whether or not to create the table. — Joe Joe Joe
– Joe Joe Joe, Commented Jan 24, 2019 at 11:28
See this for checking if table already exists : stackoverflow.com/questions/42859381/… — PaulF
– PaulF, Commented Jan 24, 2019 at 11:35

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-24 14:38:59Z

2

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;

edited Jan 24, 2019 at 14:38

answered Jan 24, 2019 at 11:36

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Joe Joe Joe Over a year ago

Thanks I will try this now. Quick noob question: what is "mySchema"? should it be my database name?

Derviş Kayımbaşıoğlu Over a year ago

If you directly issue select statement to SSMS, you will understand the difference between catalog and schema.

Joe Joe Joe Over a year ago

executing the above query in SSMS returned nothing

Derviş Kayımbaşıoğlu Over a year ago

select * from INFORMATION_SCHEMA.TABLES will return you

Joe Joe Joe Over a year ago

Thanks, it works and I now understand what a schema is. However, if I put the result into a variable using the INTO statement, I cannot access it with the VB or C# code, it is only accessible within sql. Same thing if I write the IF statement in the sql query, it is not accessible with C# or VB.

|

Joe Joe Joe · Accepted Answer · 2019-01-25 15:32:52Z

Here is the solution to my problem:

var dif = new DataInterfaceFactory(DatabaseTypes.SqlServer, "WDM_SOFTWARE_INFO", "d2sql4.d2.wdm");

        using (DataReader dr = dif.GetDataReader())
        {
            dr.ExecuteReader("SELECT COUNT(1) AS TABLECOUNT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CALCULATION_SCHEDULE'");

            var exists = Convert.ToInt32(dr["TABLECOUNT"]);

            CommandExecutor CE = dif.GetCommandExecutor();
            try
            {
                if (exists == 0)
                {
                    string sql = "CREATE TABLE dbo.CALCULATION_SCHEDULE(CALCULATION_SCHEDULE_UID INT,SERVER_NAME char(25),DATATABLE_NAME char(20),LAST_CHECK_DATE DATE,IS_RUNNING INT)";
                    CE.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: could not create CALCULATION_SCHEDULE");
            }
         }

jdweng · Accepted Answer · 2019-01-24 11:21:11Z

-1

Try using a datatable :

            DataTable calculationSchedule = new DataTable();
            calculationSchedule.Columns.Add("CALCULATION_SCHEDULE_UID", typeof(int));
            calculationSchedule.Columns.Add("SERVER_NAME", typeof(string));
            calculationSchedule.Columns.Add("DATABASE_NAME", typeof(string));
            calculationSchedule.Columns.Add("LAST_CHECK_DATE", typeof(DateTime));
            calculationSchedule.Columns.Add("IS_RUNNING", typeof(int));

answered Jan 24, 2019 at 11:21

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

1 Comment

Joe Joe Joe Over a year ago

Thanks, just need to convert the following code to C# now: using (CommandExecutor cex = dif.GetCommandExecutor()) { calculationSchedule.BuildTable(cex); }. It will make more sense having it all in C# but it still won't solve my problem though.

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related