如果表不存在，如何编写c＃代码在db中创建一个具有相同列的新表并插入数据

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 ?

开发人员,我是编程和c#编码的新手我编写了一个代码来将Xml数据插入到数据库中并且它工作正常但我在代码中的要求是“如果表不存在则创建一个在DataBase中具有相同列的新表并插入数据“那么如何编写代码?

   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,

3 个解决方案

#1

I think this works for SQLServer and MYSQL:

我认为这适用于SQLServer和MYSQL:

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

选择* 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.

这将返回与您的名称匹配的所有表 - 如果表存在则返回1行,否则不返回行。

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

我确信返回的字段可以减少,但由于它只返回一行,我从来没有担心过它。

#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.

以下是我昨天写的代码摘要,其中包含一些答案的更改。在开始时,程序使用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.

如果表不存在,则将使用createTableQuery字段创建,该字段表示用于创建新表的命令。如果需要增量值,请用您的列替换col1 col2 col 3 ...等(SerialNumber,TimeStamp,MeterData ...等)替换数据类型并使用IDENTITY(1,1)命令。

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

#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.

您不想尝试从字符串SQL执行创建表。我的意思是你可以创建存储过程,然后从代码中调用它。但是您可能希望在应用程序安装程序期间执行此操作,而不是在应用程序运行时执

#1