c# - SQL检查表是否存在于C#中，如果不存在则创建

Question

我想我几乎看过与这个问题相关的每一页，最有可能的答案是 Check if a SQL table exists但并没有真正理解它。这是我得到的:

    private void select_btn_Click(object sender, EventArgs e)
    {
        string theDate = dateTimePicker1.Value.ToString("dd-MM-yyyy");
        SqlConnection SC = new SqlConnection("Data Source=ruudpc;Initial Catalog=leden;Integrated Security=True");
        SqlCommand DateCheck = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + theDate + "'");
    }

现在我想要 DateCheck.ExecuteScalar() 的返回值；可以告诉我它是否存在，可能非常简单。

编辑

不管 sql 注入(inject)部分，对于某些人来说这个问题是有帮助的，动态创建表通常是不好的做法，我建议您重新考虑您的 ERD。只是说。

Answer 1

使用 IF EXISTS T-SQL

private void select_btn_Click(object sender, EventArgs e)
{
    string theDate = dateTimePicker1.Value.ToString("dd-MM-yyyy");

    // Enclose the connection inside a using statement to close and dispose
    // when you don't need anymore the connection (to free local and server resources)
    using(SqlConnection SC = new SqlConnection("Data Source=ruudpc;Initial Catalog=leden;Integrated Security=True"))
    {
        // Sql command with parameter 
        string cmdText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
                           WHERE TABLE_NAME=@name) SELECT 1 ELSE SELECT 0";
        SC.Open();
        SqlCommand DateCheck = new SqlCommand(cmdText, SC);

        // Add the parameter value to the command parameters collection
        DateCheck.Parameters.Add("@name", SqlDbType.NVarChar).Value = theDate

        // IF EXISTS returns the SELECT 1 if the table exists or SELECT 0 if not
        int x = Convert.ToInt32(DateCheck.ExecuteScalar());
        if (x == 1)
            MessageBox.Show("Table exists for date " + theDate);
        else
            MessageBox.Show("Table doesn't exist for date " + theDate);
    }
}