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, Jan 24 '19 at 11:18
As you see In `DataReader` Name itself, it is for reading purpose not writing or modifying — Yashar Aliabbasi, Jan 24 '19 at 11:20
@Simonare, I am using SQL Server Management Studio. the database definitely exists in that server. — Joe Joe Joe, Jan 24 '19 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, Jan 24 '19 at 11:28
See this for checking if table already exists : https://stackoverflow.com/questions/42859381/check-if-table-exists-using-command — PaulF, Jan 24 '19 at 11:35
I already have checked this, could not get it to work following that advice — Joe Joe Joe, Jan 24 '19 at 14:19
why do you use if exists ? you dont need to :) I will update my answer for you wait — Derviş Kayımbaşıoğlu, Jan 24 '19 at 14:35

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-24T14:38:59.970

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 '19 at 14:38

answered Jan 24 '19 at 11:36

Derviş Kayımbaşıoğlu

28,492
4
50
72

Thanks I will try this now. Quick noob question: what is "mySchema"? should it be my database name? – Joe Joe Joe Jan 24 '19 at 11:57
If you directly issue select statement to SSMS, you will understand the difference between catalog and schema. – Derviş Kayımbaşıoğlu Jan 24 '19 at 12:04
1

executing the above query in SSMS returned nothing – Joe Joe Joe Jan 24 '19 at 12:47
`select * from INFORMATION_SCHEMA.TABLES` will return you – Derviş Kayımbaşıoğlu Jan 24 '19 at 12:48
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 Jan 24 '19 at 13:40
what you mean by into statement? – Derviş Kayımbaşıoğlu Jan 24 '19 at 13:44
never mind, I was doing something wrong. I have now managed to put the sql query into a variable. I have updated my code above. "exists" returns -1 for some reason :( – Joe Joe Joe Jan 24 '19 at 14:27

Joe Joe Joe · Answer 2 · 2019-01-25T15:32:52.033

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

score -1 · Answer 3 · answered Jan 24 '19 at 11:21

-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 '19 at 11:21

jdweng

33,250
2
15
20

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. – Joe Joe Joe Jan 24 '19 at 11:30

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

3 Answers3