C#SqlCommands无法正常工作 [英] C# SqlCommands are not working
本文介绍了C#SqlCommands无法正常工作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个问题,代码无法正常工作,我正在尝试填充保存在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
, whileSqlConnection
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 andTry...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屋!
查看全文