如何解决此Declare变量Sql

Question

我正在制作一个问答应用程序，用户可以在其中编辑数据库中的问题。

我在下面的代码中试图做的是，当用户打开QuizEditForm，并在显示题库中当前数据的数据网格视图中进行更改时，用户可以对其进行编辑，例如添加问题、删除、更新。

当用户进行更改时，我希望将其与他的UserID一起插入数据库，这样，如果多个用户使用该应用程序，他们只能看到自己的问题。

然而，我一直在尝试修复SQL语句中的许多错误，例如声明标量变量、不正确的语法等等。有人知道如何处理这个问题吗？

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Quiz_Game_Official
{
public partial class QuizEditForm : Form
{
private string path = Path.Combine(System.IO.Directory.GetCurrentDirectory(), "Database1.mdf");
private string dbConnectionPath;
private SqlConnection connectionString;
private Form mParent;
public QuizEditForm(Form parent)
{
InitializeComponent();
int UserID = Program.UserID;


this.mParent = parent;
this.dbConnectionPath = String.Format(@"Data Source = (LocalDB)MSSQLLocalDB; AttachDbFilename = {0}; Integrated Security = True", path);
this.connectionString = new SqlConnection(dbConnectionPath);
string SQL = "UPDATE QuestionBank SET UserID = "+UserID +
"SELECT UserID,QuestionID , QuestionText,CorrectAnswer,WrongAnswer1,WrongAnswer2,WrongAnswer3 FROM QuestionBank";
//    string SQL = "SELECT UserID,QuestionID , QuestionText,CorrectAnswer,WrongAnswer1,WrongAnswer2,WrongAnswer3 FROM QuestionBank WHERE UserID =" +UserID ; // selecting questions
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(SQL, connectionString);
DataTable quizDataTable = new DataTable();     // prepare datatable to handle data for gridview
sqlDataAdapter.Fill(quizDataTable);   // sqlDataAdapter respresents question Table
quizTable.DataSource = quizDataTable;  // datatable is stil data , not UI representaion so we use this line to show it on the UI , this means  table's datasource is quizDataTable variable 
}
private void quizTable_CellValueChanged(object sender, DataGridViewCellEventArgs e)   // one of the events , triggered when the user edits cell value
{

if (quizTable.CurrentRow != null)
{
this.connectionString = new SqlConnection(dbConnectionPath);
using (connectionString)
{
DataGridViewRow quizRow = quizTable.CurrentRow;// this line gets current editing table row
int UserID = Program.UserID;
string SQL = "DECLARE @QuestionBank TABLE (UserID INT)" +
"IF @QuestionID = 0 INSERT INTO QuestionBank(UserID,QuestionText, CorrectAnswer, WrongAnswer1, WrongAnswer2, WrongAnswer3) " +
"OUTPUT INSERTED.UserID INTO @QuestionBank(UserID) VALUES ("+UserID +")"+
"output INSERTED.QuestionID VALUES(@UserID,@QuestionText, @CorrectAnswer, @WrongAnswer1, @WrongAnswer2, @WrongAnswer3)  ELSE " +
"UPDATE QuestionBank SET QuestionText = @QuestionText, CorrectAnswer = @CorrectAnswer, WrongAnswer1 = @WrongAnswer1, WrongAnswer2 = @WrongAnswer2, WrongAnswer3 = @WrongAnswer3 , UserID = @UserID " +
"output INSERTED.QuestionID WHERE QuestionID = @QuestionID";  //Question Add or Edit  // update / insert
using (SqlCommand command = new SqlCommand(SQL, connectionString)) 
{
if (quizRow.Cells[0].Value == System.DBNull.Value)  //the select row doesnt have question id meaning it is for new questions
{
command.Parameters.Add("@QuestionID", SqlDbType.Int).Value = 0;  //  we set question ID to 0 otherwise
} 
else
{
command.Parameters.Add("@QuestionID", SqlDbType.Int).Value = quizRow.Cells[0].Value;
}

command.Parameters.Add("@QuestionText", SqlDbType.VarChar, 200).Value = quizRow.Cells[1].Value != System.DBNull.Value ? quizRow.Cells[1].Value : "";
command.Parameters.Add("@CorrectAnswer", SqlDbType.VarChar, 200).Value = quizRow.Cells[2].Value != System.DBNull.Value ? quizRow.Cells[2].Value : "";
command.Parameters.Add("@WrongAnswer1", SqlDbType.VarChar, 200).Value = quizRow.Cells[3].Value != System.DBNull.Value ? quizRow.Cells[3].Value : "";
command.Parameters.Add("@WrongAnswer2", SqlDbType.VarChar, 200).Value = quizRow.Cells[4].Value != System.DBNull.Value ? quizRow.Cells[4].Value : "";
command.Parameters.Add("@WrongAnswer3", SqlDbType.VarChar, 200).Value = quizRow.Cells[5].Value != System.DBNull.Value ? quizRow.Cells[5].Value : "";
try
{
this.connectionString.Open();
int result = (int)command.ExecuteScalar();   //when sql query is ran , it ouputs last inserted or updated id ,  get the id and store it to results variable
int modified = (int)result;
UserID = Program.UserID;
if (modified == 0)
{
MessageBox.Show("Quiz Table Update Failed");
}
quizTable.CurrentRow.Cells[0].Value = modified;                            
this.connectionString.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
}
private void quizTable_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)  // event triggered when user deletes a question 
{
//e.Cancel = true;
if (quizTable.CurrentRow.Cells[0].Value != System.DBNull.Value)
{
if (MessageBox.Show("Are you sure to Delete this question?", "Warning", MessageBoxButtons.YesNo) == DialogResult.Yes)  // user is asked if he is suere about deleting the question
{
this.connectionString = new SqlConnection(dbConnectionPath);
using (connectionString)
{
DataGridViewRow quizRow = quizTable.CurrentRow;
string SQL = "DELETE from QuestionBank WHERE QuestionID = @QuestionID";  //Question Add or Edit
using (SqlCommand command = new SqlCommand(SQL, connectionString))
{
command.Parameters.Add("@QuestionID", SqlDbType.Int).Value = quizRow.Cells[0].Value;
try
{
this.connectionString.Open();
int affectedRow = command.ExecuteNonQuery();
if (affectedRow == 0)
{
e.Cancel = true;
MessageBox.Show("Question delete failed");
}
this.connectionString.Close();
}
catch (Exception ex)
{
e.Cancel = true;
MessageBox.Show(ex.Message);
}
}
}
}
}
}
private void QuizEditForm_FormClosing(object sender, FormClosingEventArgs e)
{
this.mParent.Show();
}
}
}

Answer 1

我不知道从哪里开始。首先，在执行任何操作之前，请在MSDN上阅读数据库是如何工作的，以及您要生成的每个语句的正确语法。

我会注意到在您的代码中出现的一些问题，似乎有很多。如果你不清楚其中任何一个，在你理解之前不要继续运行你的应用程序：

1. AttachDbFileName在大多数情况下都是错误的，除了测试。它会创建数据库的副本，并在您关闭连接时将其删除。请参阅坏习惯：使用AttachDBFileName
2. 您不应该缓存SqlConnection对象，您应该在每次需要时从连接字符串创建它，这只是一个字符串
3. 您应该使用using块处理连接、命令、适配器和/或读取器对象
4. 我不知道你为什么要用"UPDATE QuestionBank SET UserID = "+UserID开始你的主显示器，它会在该表的每一行设置相同的UserId
5. 在CellValueChanged中，通常最佳做法是通过e参数获取单元格值，而不是通过仅获取所选行的CurrentRow，因为它可能已通过编程进行了更改
6. 我不完全确定Upsert(插入/更新组合语句(是什么样子的，但这绝对不是正确的语法，即使是，也不是正确的方法。请参阅此处了解最佳实践
7. 如果要插入一行，但没有questionID，则需要在insert命令中特别检查。您可以将if...== System.DBNull.Value组合为一行：command.Parameters.Add("@QuestionID", SqlDbType.Int).Value = quizRow.Cells[0].Value;并检查是否为null
8. 在连接打开时，不要创建MessageBoxes或执行任何可能需要时间的操作