c# - 数据没有进入datagridview

Question

我正在使用 C# VS 2010 开发应用程序。

我有下面的代码来将带有某个名字的学生详细信息提取到数据 GridView 中。

var CommandText = @"SELECT sid as 'Student ID', name as 'Student Name', adDate as 
                  'Admission Date',
               paidFees as 'Fees Paid', balance as 'Fees Remaining'
                   FROM Student WHERE (status = '" + status + "') AND 
                   (name LIKE '%'+'"+txtSearchName.Text.Trim() + "'+'%')";
string select = CommandText;
            con.Open();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(select, con); 

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);
            con.Close();
            dgvSearch.ReadOnly = true;
            dgvSearch.DataSource = ds.Tables[0];

我的问题是我只是得到表格的标题，而不是像下面的屏幕截图那样的数据。

这里有什么问题吗？

Answer 1

我建议使用参数化查询。

var CommandText = @"SELECT sid as 'Student ID', name as 'Student Name', adDate as 
                  'Admission Date', paidFees as 'Fees Paid', balance as 'Fees Remaining'
                   FROM Student WHERE (status = @status) AND (name LIKE @stuname)";
string select = CommandText;
con.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(select, con); 
dataAdapter.SelectCommand.Parameters.AddWithValue("@status", status);
dataAdapter.SelectCommand.Parameters.AddWithValue("@stuname", "%" + txtSearchName.Text.Trim() + "%");
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
....

通过这种方式，LIKE 语句的引号和通配符的字符串连接问题得到了极大的简化，并且您可以避免细微的错误，例如 % 字符周围的错误引号。

当然这会删除所有可能的 SQL Injection attack