how to write c# code if table not exists create a new table in db with same columns and insert the data

Question

Developers, I am new to programming and c# coding I written a code to insert the Xml data into database and it is working perfect but my requirement in code is "if table is not exists create a new table with same columns in the DataBase and insert the data " so how can I write the code ?

   public void SaveXmltoDB(List<MeterReading> MeterReadingList)
    {
        //OpenConnection();

       // CreateTableIfNotExists();
        foreach (var meterReading in MeterReadingList)
        {
            foreach(var interval in meterReading.IntervalDatalist)
            {
                foreach(var reading in interval.Readinglist)
                {
                    string command = string.Format("insert into INTERVALDATA1(SerialNumber,TimeStamp,MeterData) VALUES ({0},'{1}',{2})", meterReading.MeterName, reading.TimeStamp.ToString(), reading.RawReading);
                    using (SqlConnection conn = new SqlConnection("server=LAPTOP-N6V52QKD\\AKHIL5656;" +
                                   "Trusted_Connection=yes;" +
                                   "database=ReportServer$AKHIL5656; " +
                                   "connection timeout=30;" + "persist security info = True;" +
    "Integrated Security = SSPI;"))
                    {

                        SqlCommand myCommand = new SqlCommand(command,conn);
                        myCommand.CommandType = System.Data.CommandType.Text;

                        conn.Open();
                        try
                        {
                            myCommand.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {

                        }

                    }


                }
            }
        }
        CloseConnection();

    }

The above code is perfectly working to insert the data into my table ,In the above code how can I program If table not exists in the database create new table with same columns and insert the data?

can anyone help me on this?

Thanks,

Answer 1

I think this works for SQLServer and MYSQL:

Select * From Information_Schema.Tables Where Table_Name = 'TableName';

This returns all tables matching your name--1 row if the table exists, no rows if it doesn't.

I'm sure the fields returned can be reduced but since it's returning only one row I have never worried about it.

Answer 2

Here is summary of a code that I wrote yesterday with a few changes for the answer.
in the beginning the program checks if the table exist, using INFORMATION_SCHEMA.TABLES.

if the table is not exist it will be create with createTableQuery field that represents the command for creating the new table.
replace col1 col2 col 3... etc with your columns (SerialNumber,TimeStamp,MeterData... etc) replace the data types and use IDENTITY (1, 1) command if you need incremental value.

    private void saveTableToDataBase()
    {
        string tableName = dbTableName;    

        // check if table exist in sql server db
        if (IsTableExistInDb(tableName) == true) {
            // table exist do something...

        } else {
            // create table, replace with your column names and data types
            string createTableQuery = "CREATE TABLE " & "." & tableName & "(" & _
                "ID int  IDENTITY (1, 1) NOT NULL PRIMARY KEY, " & _
                "Col1 int, " & _
                "Col2 decimal(5,4), " & _
                "Col3 int, " & _
                "Col4 decimal(5,4), " & _
                "Col5 int " & _
                ")"
                // create table in database
                Insert(createTableQuery);
        }        
    }


    public static Boolean IsTableExistInDb(string tableName)
    {

        Object result = ExecuteScalarWithAnonimusType("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = " + "'" + tableName + "'", Con);

        if (result != null && byte.Parse(result.ToString()) == 1)
        {
            return true;
        }
        else
        {
            return false;
        }    
    }

    public static object ExecuteScalarWithAnonimusType(string query)
    {
        Cmd = new SqlCommand(query, Con);
        try
        {
            return Cmd.ExecuteScalar();
        }

        catch (Exception ex)
        {
            return null;
        }
        finally
        {

            if (Con.State != ConnectionState.Closed)
                Con.Close(); Con.Close();
        }
    }

    public static bool Insert(string command)
    {

        try {
            con = new SqlConnection(System_Vars.SqlClientConnString);
            con.Open();
            cmd = new SqlCommand(command, con);
            return cmd.ExecuteNonQuery();

        } catch (Exception ex) {

            return false;

        } finally {
            con.Close();
        }
    }

Answer 3

You don't want to try and do a create table from string SQL. I mean you could create stored procedure and then call it from code. But you probably want to do this during application setup program and not when the application runs.