SQLCommand，创建表

Question

单击按钮后，我希望在我的数据库中创建表。在Button_Click函数中，我有一个代码

SqlConnection conn = new SqlConnection(@"MyConnectionString");

conn.Open();

SqlCommand cmd = new SqlCommand("CREATE TABLE '" + tableName+ "' (IdPy INT IDENTITY(1,1), Question NVARCHAR (MAX) NOT NULL, IsChecked BIT NOT NULL,  CONSTRAINTPK_'" + tableName+ "' PRIMARY KEY(Id) )", conn);

cmd.ExecuteNonQuery();
conn.Close();

tableName是我的String变量(它的值是2018-04-18 asd -是的，我希望表具有这样的名称)。在按一下按钮后，我有一个错误：

System.Data.SqlClient.SqlException：“2018-0418 asd附近的不正确语法”。

我认为问题出在我的SqlCommand上。如果你能帮我解决那个问题，我将不胜感激。

Answer 1

看起来tableName变量是2018-04-18 asd。如果这确实是正确的表名，则需要在方括号中转义它(和约束)：

SqlCommand cmd = new SqlCommand("CREATE TABLE [" + tableName + "] (IdPy INT IDENTITY(1,1), Question NVARCHAR (MAX) NOT NULL, IsChecked BIT NOT NULL, CONSTRAINT [CONSTRAINTPK_" + tableName+ "] PRIMARY KEY(Id) )", conn);

Answer 2

您应该转义(在MS情况下的[...])表和约束名称：

  //DONE: wrap IDisposable into using
  using(SqlConnection conn = new SqlConnection(@"MyConnectionString")) {
    conn.Open();

    //DONE: Make sql readable. Can you see that you've skipped CONSTRAINT keyword?
    string sql = 
      $@"CREATE TABLE [{tableName}] (
           -- Fields
           IdPy      INT IDENTITY(1,1),
           Question  NVARCHAR (MAX)     NOT NULL, 
           IsChecked BIT                NOT NULL,

           -- Constraints
           --DONE: Constraint key word (optional in some RDBMS) added 
           CONSTRAINT [CONSTRAINTPK_{tableName}] PRIMARY KEY(Id) 
         )";

    //DONE: wrap IDisposable into using
    using (qlCommand cmd = new SqlCommand(sql, conn)) {
      cmd.ExecuteNonQuery();
    }
  } 

Answer 3

使用字符串变量和参数化字符串格式可能更容易识别SQLCommand中的问题。举个例子：

string query = "CREATE TABLE @tablename (IdPy INT IDENTITY(1,1), 
Question NVARCHAR (MAX) NOT NULL, IsChecked BIT NOT NULL,  
CONSTRAINTPK_@tablename PRIMARY KEY(Id) )";

string param = new {@tablename = txttable.txt(example)};

SqlCommand cmd = new SqlCommand(query, param, conn);

这可能有助于确保您必须检查的变量更加简洁。