C＃SqlCommands无法正常工作 [英] C# SqlCommands are not working

问题描述

我有一个问题，代码无法正常工作，我正在尝试填充保存在SQL Server本地主机中的数据库，但是代码却无济于事：（

I have a problem that the code isn't working, I'm trying to populate the database that saved in localhost on SQL Server but the code is doing nothing :(

private void button2_Click(object sender, EventArgs e)
{
    try
    {
        conn.Open();

        if (dataGridView1.SelectedRows.Count != 0 && listBox1.SelectedIndex != -1)
        {
            string id = "select studentID from student where studentName like '%" + listBox1.SelectedItem.ToString() + "%'";

            SqlCommand a = new SqlCommand(id, conn);

            a.ExecuteNonQuery();

            SqlDataReader reader = a.ExecuteReader();

            string s = reader.GetString(0);

            string q = "insert into Borrow values (" + dataGridView1.CurrentRow.Cells[0].Value.ToString()
                + ", " + s + " , '" + DateTime.Now + "' , Null)";

            SqlCommand cmd = new SqlCommand(q, conn);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Book is now Borrowed");

            string tmp = "update Book set quantity=quantity-1 where bookID " + dataGridView1.CurrentRow.Cells[0].Value.ToString();
            SqlCommand tm = new SqlCommand(tmp, conn);
        }
    }
    catch
    {
    }
    finally
    {
        conn.Close();
    }
}

推荐答案

有此代码有多个问题，不幸的是，甚至没有提到 existing （现已删除）的答案，更不用说帮助解决了。

There are multiple problems with this code, unfortunately none of the existing (now deleted) answers even mention, let alone help to fix.

• marc_s警告过SQL注入风险。


• 所有这些命令必须在事务内。


• 您使用的是 like 来获取学生ID，但是您是从列表框中获取姓名的-所以为什么不拥有ID已经作为ListBoxItem的值了吗？


• 在借书之前，必须首先确保至少有一个副本可以借阅


• 您正在吞噬例外
>

• 您正在为 SqlConnection 使用类级变量，而最好使用 SqlConnection 作为using语句内的局部变量。


• 您正在将代码与显示混合在一起-这将是不可能的，或者至少很难进行测试。可测试性是可维护性的一部分。


• 您的变量命名很糟糕。变量名应该有意义-这样，当有人阅读代码时，他们只要阅读变量名就可以了解变量的含义。


• try 块内唯一的内容是实际的数据库访问权限。异常和 Try ... catch 块是您无法在代码中测试或控制的。

• There's the SQL Injection risk marc_s warned about.
• All these commands must be inside a transaction.
• You are using like to get the student id, but you get the name from a list box - so why not have the id already as the value of the ListBoxItem?
• Before you can borrow a book, you must first make sure you have at least one copy left to borrow
• You are swallowing exceptions
• You are using a class-level variable for SqlConnection, while SqlConnection is best used as a local variable inside a using statement.
• You are mixing code with display - this will be impossible or at least very hard to test. Testability is a part of Maintainability.
• Your variable naming is terrible. A variable name should be meaningful - so that when someone reads the code they can get the meaning of the variable just by reading it's name.
• The only thing that should be inside the try block is the actual database access. Exceptions and Try...catch blocks are for things you can't test or control in your code.

所有这些，这是代码的某种程度的改进版本起点。请尝试进一步改进它，以解决我上面列出的所有（或至少大部分）问题：

All of that being said, here is a somewhat improved version of your code as a starting point. Please try to improve it further to fix all (or at least most of) the issues I've listed above:

private void button2_Click(object sender, EventArgs e)
{
    if (dataGridView1.SelectedRows.Count != 0 && listBox1.SelectedIndex != -1)
    {            
        using(var con = new SqlConnection(connectionString))
        {
            var trn = con.BeginTransaction();
            var sqlGetStudentId = "select studentID from student where studentName = @studentName";   
            using(var cmd = new SqlCommand(sqlGetStudentId, conn))
            {
                cmd.Parameters.Add("@studentName", SqlDbType.VarChar).Value = listBox1.SelectedItem.ToString();
                try
                {
                    con.Open();
                    string studentId = cmd.ExecuteScalar()?.ToString();

                    if(!string.IsNullOrEmpty(studentId))
                    {
                        // Note: You must first check if there are books left to borrow!
                        cmd.CommandText = "update Book set quantity=quantity-1 where bookID = @BookId";
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@BookId", SqlDbType.VarChar).Value = dataGridView1.CurrentRow.Cells[0].Value.ToString();
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "insert into Borrow (/* Alwasy specify columns list! */) values (@IAmGuessingBookId, @StudentId, GETDATE(), NULL)";
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@IAmGuessingBookId", SqlDbType.VarChar).Value = dataGridView1.CurrentRow.Cells[0].Value.ToString();
                        cmd.Parameters.Add("@StudentId", SqlDbType.VarChar).Value = studentId;
                        cmd.ExecuteNonQuery();

                        trn.Commit();
                    }
                }
                catch(Exception ex)
                {
                    // Do something with the exception!
                    // show an error description to the client,
                    // log for future analisys
                    trn.Rollback();
                }
            }
        }

    }
}

这篇关于C＃SqlCommands无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！