7 Replies - 603 Views - Last Post: 25 July 2019 - 10:17 AM Rate Topic: -----

#1 BenignDesign   User is offline

  • holy shitin shishkebobs
  • member icon




Reputation: 8025
  • View blog
  • Posts: 12,431
  • Joined: 28-September 07

SqlDataReader - Where to close it?

Posted 25 July 2019 - 07:33 AM

My code:
.
.
.
con.Open(); //Open database connection
var sql = new SqlCommand("SELECT x,y from [database].[table] WHERE [email protected]", con);
sql.Parameters.AddWithValue("@SessionId", SessionId);
SqlDataReader reader = null;
reader = sql.ExecuteReader(); // Open DataReader

while (reader.Read())
{
   int db_x = (int)reader[0];
   int db_y = (int)reader[1];

   int new_x = db_x + 1;  //Increment x by 1

   if (curr_y > db_y)
   {
       int new_y = curr_y;  //If current y is greater than database y, set new y to current y
       varPass(new_x, new_y);
   } else
   {
       int new_y = db_y;  //If current y is less than database y, set new y to database y
       varPass(new_x, new_y);
   }
}
reader.Close(); //Close DataReader

void varPass(int new_x, int new_y)
{
   var cmd = new SqlCommand("UPDATE [database].[table] SET [email protected]_x, [email protected]_y WHERE [email protected]", con); //Update record
   cmd.Parameters.AddWithValue("@SessionId", SessionId);
   cmd.Parameters.AddWithValue("@new_x", new_x);
   cmd.Parameters.AddWithValue("@new_y", new_y);
   cmd.ExecuteNonQuery();
}
con.Close(); //Close database connection
.
.
.



The issue I'm running into is when I hit the varPass function, I get the following error:

Quote

System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.'


I've tried moving the reader.Close(); command to several different places, to no avail.

Any ideas?

This post has been edited by BenignDesign: 25 July 2019 - 07:33 AM


Is This A Good Question/Topic? 0
  • +

Replies To: SqlDataReader - Where to close it?

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,899
  • Joined: 12-June 08

Re: SqlDataReader - Where to close it?

Posted 25 July 2019 - 07:36 AM

I typically close connections as soon as possible. Using the 'using' setup it makes it hard to keep a single connection open too long.

Example:

 
            try
            {
                using (SqlConnection con = new SqlConnection(""))
                {
                    con.Open();

                    sql = "";// '-- 2.0  SQL statement.
                    using (SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(sql, con))
                    {
                        adapt.SelectCommand.Parameters.Add("", SqlDbType.VarChar).Value = "";

                        ds = new DataSet();

                        adapt.Fill(ds);
                    }
                }
                //convert dataset to collection
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    myList = new List<string>();

                    foreach (DataRow tempRow in ds.Tables[0].Rows)
                    {
                        myList.Add((string)tempRow[""]);
                    }
                }

            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }


This post has been edited by modi123_1: 25 July 2019 - 07:37 AM
Reason for edit:: whoops.. make that C#

Was This Post Helpful? 0
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,201
  • Joined: 12-January 10

Re: SqlDataReader - Where to close it?

Posted 25 July 2019 - 07:39 AM

so what i am seeing is that you are running a command on a reader and then trying to run another command on the reader that is already open with the original cmd

might be easier to dump that into a dataset -- close the connection reopen the connection and do your manipulation that way

This post has been edited by DarenR: 25 July 2019 - 08:30 AM

Was This Post Helpful? 0
  • +
  • -

#4 BenignDesign   User is offline

  • holy shitin shishkebobs
  • member icon




Reputation: 8025
  • View blog
  • Posts: 12,431
  • Joined: 28-September 07

Re: SqlDataReader - Where to close it?

Posted 25 July 2019 - 07:56 AM

Dumb anything into anything is going to require some serious assistance because I need that whole concept dumbed for me.

How do I assign the values from the query to the dataset?
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,899
  • Joined: 12-June 08

Re: SqlDataReader - Where to close it?

Posted 25 July 2019 - 08:03 AM

I use the SQLAdapter to fill a dataset from a query, and a SQLCommand object for the UPDATE/INSERT/DELETEs.

Derp.. my snippets I use.
https://www.dreaminc...-command-setup/
https://www.dreaminc...-adapter-setup/
Was This Post Helpful? 1
  • +
  • -

#6 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,201
  • Joined: 12-January 10

Re: SqlDataReader - Where to close it?

Posted 25 July 2019 - 08:32 AM

after you make that dataset -- it will have the column names for you from your query and then you can do what ever you want with that data
Was This Post Helpful? 1
  • +
  • -

#7 h4nnib4l   User is offline

  • The Noid
  • member icon

Reputation: 1416
  • View blog
  • Posts: 2,017
  • Joined: 24-August 11

Re: SqlDataReader - Where to close it?

Posted 25 July 2019 - 09:15 AM

Here's a stab at some sample code doing what (I think) you're trying to do, using the previous recommendations.

Spoiler


Like modi123_1 said, the using directive will close and dispose of the SqlConnection instance for you, but you can reuse the same instance and just manually open/close the connection until you're done. This is assuming that you get multiple rows back from your SELECT. If you're only going to get one back, you can just use DataRow row = ds.Tables[0].Rows[0] instead of the foreach.

This post has been edited by h4nnib4l: 25 July 2019 - 09:18 AM

Was This Post Helpful? 2
  • +
  • -

#8 BenignDesign   User is offline

  • holy shitin shishkebobs
  • member icon




Reputation: 8025
  • View blog
  • Posts: 12,431
  • Joined: 28-September 07

Re: SqlDataReader - Where to close it?

Posted 25 July 2019 - 10:17 AM

// the ternary operator is more concise than an if/else
var newY = currentY > dbY
   ? currentY // if the statement in the first line is true
   : dbY; // if the statement in the first line is false



This little snippet right here is slick as shit! Thank you!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1