如何在 c# 中将 SqlDataReader 与参数化查询一起使用? [英] How to use SqlDataReader with a parametrized query in c#?

问题描述

我正在查看参数化查询问题 我找不到使用带有参数化查询的 SqlDataReader 来填充下拉列表的示例.

I'm looking at parameterized query questions I could not find an example of using SqlDataReader with a parameterized query to populate a drop down list.

现在我可以在这里使用我的代码填充我的下拉菜单

Right now I can populate my drop down just fine using my code here

if (!this.IsPostBack)
{
    using (SqlConnection con = new SqlConnection(SQLConnectionString))
    {
        System.Data.SqlClient.SqlCommand go = new System.Data.SqlClient.SqlCommand();

        con.Open();
        go.Connection = con;
        go.CommandText = "SELECT InsuredID, FirstName, LastName FROM [Lab2].[dbo].[INSURED]";
        go.ExecuteNonQuery();

        SqlDataReader readIn = go.ExecuteReader();

        while (readIn.Read())
        {
            ddlHomeInsuredID.Items.Add(
                new ListItem(readIn["InsuredID"].ToString() + " : " + readIn["FirstName"].ToString()
                + " " + readIn["LastName"].ToString()));
        }

        con.Close();

        ddlHomeInsuredID.Items.Insert(0, new ListItem("--Select InsuredID--", "0"));
    }
}

但是，我想让这个 select 语句参数化.我该怎么做?我很乐意编写如下参数化插入语句:

However, I want to make this select statement parameterized. How can I do this? I am comfortable writing parameterized insert statements like the following:

using (SqlConnection connection = new SqlConnection(SQLConnectionString))
{
    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandType = System.Data.CommandType.Text;

    command.CommandText = @"INSERT INTO [Lab2].[dbo].[INSURED] ([FirstName], [LastName], [MI], [DateOfBirth], 
[CreditScore], [AddressID], [DriversLicenseNumber], [LastUpdatedBy], [LastUpdated]) VALUES
(@firstName, @lastName, @middleInitial, @dateOfBirth, @creditScore, @addressID,
@driversLicenseNumber, @lastUpdatedBy, @lastUpdated)";

    command.Parameters.Add("@firstName", SqlDbType.VarChar, 20).Value = Insured.insuredArr[j].getFirstName();
    command.Parameters.Add("@lastName", SqlDbType.VarChar, 30).Value = Insured.insuredArr[j].getLastName();
    command.Parameters.Add("@middleInitial", SqlDbType.Char, 1).Value = Insured.insuredArr[j].getMiddleInitial();
    command.Parameters.Add("@dateOfBirth", SqlDbType.VarChar, 30).Value = Insured.insuredArr[j].getDateOfBirth();
    command.Parameters.Add("@creditScore", SqlDbType.Int).Value = Insured.insuredArr[j].getCreditScore();
    command.Parameters.Add("@addressID", SqlDbType.Int).Value = Insured.insuredArr[j].getAddressID();
    command.Parameters.Add("@driversLicenseNumber", SqlDbType.VarChar, 30).Value = Insured.insuredArr[j].getDriversLicenseNumber();
    command.Parameters.Add("@lastUpdatedBy", SqlDbType.VarChar, 20).Value = Insured.insuredArr[j].getLastUpdatedBy();
    command.Parameters.Add("@lastUpdated", SqlDbType.Date).Value = Insured.insuredArr[j].getLastUpdated();

    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

MsgBox("Record(s) inserted into database", this.Page, this);

那么，我怎样才能像第二个例子那样进行我的第一个查询?

So, how can I make my first query like the second example?

谢谢

纳姆里克

推荐答案

首先，ExecuteNonQuery() 方法对 SELECT 查询无效，坚持使用 ExecuteReader() 因为要返回查询结果.这是ExecuteNonQuery方法的使用说明:

First of all, the usage of ExecuteNonQuery() method isn't valid for SELECT query, just stick with ExecuteReader() since you want to return query results. This is the usage description of ExecuteNonQuery method:

您可以使用 ExecuteNonQuery 来执行目录操作(对于例如，查询数据库的结构或创建数据库对象，如表)，或更改数据库中的数据，而无需通过执行UPDATE、INSERT 或 DELETE 语句来使用数据集.

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

修改后的查询流程应该是这样的:

The modified query flow should be like this:

using (SqlConnection con = new SqlConnection(SQLConnectionString))
{
    SqlCommand go = new SqlCommand();

    con.Open();
    go.Connection = con;
    go.CommandText = "SELECT InsuredID, FirstName, LastName FROM [Lab2].[dbo].[INSURED]";

    SqlDataReader readIn = go.ExecuteReader();
    while (readIn.Read())
    {
       // reading data from reader
    }

    con.Close();

    // other stuff
}

如果你想对 SELECT 语句使用参数化查询，你需要在 WHERE 子句中至少包含一列(和一个参数名称)(见下面的例子)):

If you want to use parameterized query for SELECT statement, you need at least one column (and one parameter name) to be included in WHERE clause (see example below):

SELECT InsuredID, FirstName, LastName FROM [Lab2].[dbo].[INSURED] WHERE InsuredID = @InsuredID

然后，您可以使用 SqlParameter 将参数值传递到上面的查询中:

Then, you can use SqlParameter to pass parameter value into the query above:

using (SqlConnection con = new SqlConnection(SQLConnectionString))
{
    System.Data.SqlClient.SqlCommand go = new System.Data.SqlClient.SqlCommand();

    con.Open();
    go.Connection = con;
    go.CommandText = "SELECT InsuredID, FirstName, LastName FROM [Lab2].[dbo].[INSURED] WHERE InsuredID = @InsuredID";
    go.Parameters.Add("@InsuredID", SqlDbType.Int).Value = 1; // example value for parameter passing

    SqlDataReader readIn = go.ExecuteReader();
    while (readIn.Read())
    {
       // reading data from reader
    }

    con.Close();

    // other stuff
}

注意:避免同时执行INSERT/UPDATE/DELETE操作，并通过SELECT语句填充数据并使用相同的活动连接，应先关闭前一个连接执行另一个查询.

NB: Avoid perform INSERT/UPDATE/DELETE operation at the same time with populating data by SELECT statement with same active connection, the previous connection should be closed first before executing another query.

更多示例:

如何在sql语句中使用字符串变量

如何使用 sql 参数进行选择查询?

这篇关于如何在 c# 中将 SqlDataReader 与参数化查询一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！