How to store multiple SQL data columns into different variables C#

Question

I am trying to store sql data that I have for a voucher id and voucher amount into a variable and display it into a label on a click of a button.

protected void Button1_Click(object sender, EventArgs e)
{
    string voucherId = String.Empty;
    string voucherAmount = String.Empty;

    string queryVoucherId = "select voucherid from ReturnForm where email = '" + Session["username"] + "';";
    string queryVoucherAmount = "select voucheramount from ReturnForm where email = '" + Session["username"] + "';";

    int index = 0;

    using (SqlConnection con = new SqlConnection(str))
    {
        SqlCommand cmd = new SqlCommand(queryVoucherId, con);

        con.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            voucherId = reader[index].ToString();
            index++;
        }
    }

    using (SqlConnection con = new SqlConnection(str))
    {
        SqlCommand cmd = new SqlCommand(queryVoucherAmount, con);
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            voucherAmount = reader[index].ToString();
            index++;
        }
    }

    if (txtVoucher.Text == voucherId)
    {
        Label3.Visible = true;
        Label3.Text = voucherAmount;
    }
}

When I click the button its giving me an error saying that the index is out of bounds.

First of all - you can do this in a single query by selecting both required columns in your SELECT statement. And more importantly: NEVER EVER write SQL like this! Do NOT concatenate together your SQL code with parameter values - use parametrized queries - ALWAYS - no exceptions. You're wide open to the #1 internet vulnerability - SQL injection ... — marc_s
– marc_s, Commented Sep 21, 2021 at 11:52

marc_s · Accepted Answer · 2021-09-21 12:22:45Z

Building on @JSGarcia's answer - but using parameters as one ALWAYS should - you'd get this code:

string email = Session['username'];
string query = $"SELECT voucherid, voucheramount FROM ReturnFrom WHERE Email = @email";

DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(query, conn))
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
    // set the parameter before opening connection
    // this also defines the type and length of parameter - just a guess here, might need to change this
    cmd.Parameters.Add("@email", SqlDbType.VarChar, 100).Value = email;

    conn.Open();
    sda.Fill(dt);
    conn.Close();
}

Personally, I'd rather use a data class like

public class VoucherData
{
    public int Id { get; set; }
    public Decimal Amount { get; set; }
}

and then get back a List<VoucherData> from your SQL query (using e.g. Dapper):

string query = $"SELECT Id, Amount FROM ReturnFrom WHERE Email = @email";

List<VoucherData> vouchers = conn.Query<VoucherData>(query).ToList();

I'd try to avoid the rather clunky and not very easy to use DataTable construct...

J. S. Garcia · Accepted Answer · 2021-09-21 11:23:21Z

0

I strongly recommend combining your sql queries into a single one, write it into a datatable and continue your logic from there. IMHO it is much cleaner code:

string email = Session['username'];
string query = $"SELECT voucherid, voucheramount FROM ReturnFrom where Email = '{email}'";

DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = conn.CreateCommand())
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
    cmd.CommandText = query;
    cmd.CommandType = CommandType.Text;
    conn.Open();
    sda.Fill(dt);
    conn.Close();
}

// Work with DataTable dt from here on
...

answered Sep 21, 2021 at 11:23

J. S. Garcia

3861 silver badge10 bronze badges

2 Comments

marc_s Over a year ago

But PLEASE - NEVER EVER write SQL like this! Do NOT concatenate together your SQL code with parameter values - use parametrized queries - ALWAYS - no exceptions. You're wide open to the #1 internet vulnerability - SQL injection ...

J. S. Garcia Over a year ago

@marc_s can you please edit my answer to demonstrate the parametrized query?

Albert D. Kallal · Accepted Answer · 2021-09-21 20:35:28Z

Well, one more big tip? You ONLY as a general rule need a dataadaptor if you going to update the data table.

And you ONLY need a new connection object if you say not using the sql command object.

The sqlcommand object has:

a connection object - no need to create a separate one
a reader - no need to create a separate one.

Note how I did NOT create a seperate connection object, but used the one built into the command object.

And since the parameter is the SAME in both cases? Then why not re-use that too!!

So, we get this:

    void TestFun2()
    {
        String str = "some conneciton???";

        DataTable rstVouch = new DataTable();
        using (SqlCommand cmdSQL =
              new SqlCommand("select voucherid from ReturnForm where email = @email",
              new SqlConnection(str)))
        {
            cmdSQL.Parameters.Add("@email", SqlDbType.NVarChar).Value = Session["username"];
            cmdSQL.Connection.Open();

            rstVouch.Load(cmdSQL.ExecuteReader());

            // now get vouch amount
            cmdSQL.CommandText = "select voucheramount from ReturnForm where email = @email";

            DataTable rstVouchAmount = new DataTable();
            rstVouchAmount.Load(cmdSQL.ExecuteReader());

            if (rstVouch.Rows[0]["vourcherid"].ToString() == txtVoucher.Text)
            {
                Label3.Visible = true;
                Label3.Text = rstVouchAmount.Rows[0]["voucheramount"].ToString();
            }
        }
    }

Collectives™ on Stack Overflow

How to store multiple SQL data columns into different variables C#

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related