How to use SqlDataReader with a parametrized query in c#?

Question

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"));
    }
}

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?

Thanks

nammrick

Answer 1

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:

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
}

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

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
}

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.

More examples:

How to use string variable in sql statement

How to use sql parameters for a select query?