c#, how to Reuse the SqlCommand and OleDbCommand

Question

this is the url i refer to: http://support.microsoft.com/kb/317559

i study their method and had applied them, everything is fine until i include 2_2, error msg: No current query in data reader.

I already close my drSelect1_1 under 2_1 if statement. I need to close it or else i cant create a new one. so problem is, how to open the drSelect1_1 again so that i can use for my 2_2? i tried to create drSelect1_1a instead of use back drSelect1_1, got error with: Invalid attempt to access a field before calling Read() . any advice? code as below:

cmdSelect.CommandText = "SELECT * FROM member WHERE username = @username";

connSelect.Open();

MySqlDataReader drSelect1_1 = cmdSelect.ExecuteReader();

if (drSelect1_1.Read())
{
     //1_1
     this.lbl1_1.Text = drSelect1_1["username"].ToString();

     //2_1
     if (drSelect1_1["direct1"].ToString() != "")
     {
        //this.ib2_1.Visible = true;
        this.lbl2_1.Text = drSelect1_1["direct1"].ToString();
        drSelect1_1.Close();

        cmdSelect.CommandText = "SELECT * FROM member WHERE username = '" + this.lbl2_1.Text + "'";
        MySqlDataReader drSelect2_1 = cmdSelect.ExecuteReader();

        if (drSelect2_1.Read())
        {                  
             drSelect2_1.Close();
        }
    }

    //2_2            
    if (drSelect1_1["direct2"].ToString() != "")
    {

    }
}
connSelect.Close();

What's your reasoning behind wanting to reuse the command? Why not just create another one? — Richard Szalay, Jul 14 '12 at 03:19
What happens if an exception is thrown, where do you Dispose your resources? — ta.speot.is, Jul 14 '12 at 03:20
@RichardSzalay, my previous code is create a new command and connection. but the page take times to load. because i need to repeat the same step for 32 times. that means the website need to open 32 new connection and close them. — melvintcs, Jul 14 '12 at 03:26
Why do you want to re-open drSelect1_1, can't you complete all the work i.e. what you're trying to do at 2_2 before you close it in the first place? — HasaniH, Jul 14 '12 at 03:28
Creating multiple commands that use the same connection object will not open multiple connections — Richard Szalay, Jul 14 '12 at 03:29
@SpaceghostAli, i know what u mean, but how? it's a "IF" statement. i need to done the first "IF" before the second "IF" — melvintcs, Jul 14 '12 at 03:32
If you can't do the work for 2_2 before closing the reader, you'll have to create another instance. What's the actual problem you're trying to solve though? I noticed the 2 command texts are almost identical, maybe there is a better way to approach your solution. — HasaniH, Jul 14 '12 at 03:43

Ruel · Accepted Answer · 2012-07-14T04:35:59.793

EDIT #2

Alright, well if that's the case. Assign the values to variables at the beginning of the block, before closing the reader. It will save you from so much trouble. :)

if (drSelect1_1.Read())
{
     string username = drSelect1_1["username"].ToString();
     string direct1 = drSelect1_1["direct1"].ToString();
     string direct2 = drSelect1_1["direct2"].ToString();

     drSelect1_1.Close();

     //1_1
     this.lbl1_1.Text = username;

     //2_1
     if (direct1 != "")
     {
        //this.ib2_1.Visible = true;
        this.lbl2_1.Text = direct1;

        cmdSelect.CommandText = "SELECT * FROM member WHERE username = '" + this.lbl2_1.Text + "'";
        MySqlDataReader drSelect2_1 = cmdSelect.ExecuteReader();

        if (drSelect2_1.Read())
        {                  
             drSelect2_1.Close();
        }
    }

    //2_2            
    if (direct2 != "")
    {

    }
}

You have closed your data reader drSelect1_1.Close(); and tried accessing it again at

if (drSelect1_1["direct2"].ToString() != "")

Remove this line

drSelect1_1.Close();

Or just move before it before the } in your Read() block.

EDIT:

Just create another instance of MySqlCommand, do it like this:

MySqlCommand cmdSelect2 = new MySqlCommand("SELECT * FROM member WHERE username = '" + this.lbl2_1.Text + "'", connSelect);
MySqlDataReader drSelect2_1 = cmdSelect2.ExecuteReader();

So you can safely remove the above mentioned line.

@Ruen same, i still need to close the previous dataReader first :( — melvintcs, Jul 14 '12 at 04:17

c#, how to Reuse the SqlCommand and OleDbCommand

1 Answers1