Using a SqlDataReader from a class.

Question

Hi i am currently teaching myself C# and .NET and i would appreciate some help on this issue from you kind guys.

I have created an aspx page that is going to update the quantity of some text boxes. In the code behind the method calls a class that connects to my SQL server database and returns the values to place in the text boxes in a SQLDataReader.

However the only way i can seem to get the code to work is to not close the connection of the reader or the connection to the database.

Ill post my code below for you guys to see and if you need anymore information please ask.

update.aspx

private void LoadQuantity(int ProdID)
{
SqlDataReader reader2 = Geordie_Jeans.Classes.AdminControl.GetQuant(ProdID);

            int count = reader2.FieldCount;
            while (reader2.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    if (reader2["PROD_SIZE"].ToString() == "L")
                    {
                        txtLQty.Text = reader2["PROD_QUANT"].ToString();
                    }
                    if (reader2["PROD_SIZE"].ToString() == "M")
                    {
                        txtMQty.Text = reader2["PROD_QUANT"].ToString();
                    }
                    if (reader2["PROD_SIZE"].ToString() == "S")
                    {
                        txtSQty.Text = reader2["PROD_QUANT"].ToString();
                    }
                }
            }

        }

admin.cs

public static SqlDataReader GetQuant(int prodID)
        {
            string connectionString =
            WebConfigurationManager.ConnectionStrings["Geordie_Jeans"].ConnectionString;

            SqlConnection con2 = new SqlConnection(connectionString);
            SqlCommand cmd2 = new SqlCommand("PR_GET_QUANTITY", con2);
            cmd2.CommandType = CommandType.StoredProcedure;

cmd2.Parameters.Add("@ID", SqlDbType.Int).Value = prodID;

SqlDataReader reader2 = null;

            try
            {
                con2.Open();
                reader2 = cmd2.ExecuteReader();
                return reader2;

            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                if (reader2 != null)
                {
                    reader2.Close();
                }

                // close the connection
                if (con2 != null)
                {
                    con2.Close();
                }
            }

}

Thanks Boldonglen

Jignesh Trivedi · Answer

hi datareader.Dispose() method ensures all resources associated with the current DataReader object instance are released. hopet thsi help.

Glen Robson · Answer

Thanks Jignesh what would reader2.Dispose(); do?

Jignesh Trivedi · Answer

hi Back to your original code. try.. reader2.Dispose(); after closing datareader. hope this help.

Narayan · Answer

Hi A clean way of doing this just create a class having three properties public class MyQty { public string LQty{get;set;} public string MQty{get;set;} public string S Qty{get;set;} } In your admin.cs where you are connecting to database try return the object of MyQty class and you are ready to use it any where. Admin.cs private MyQty LoadQuantity(int ProdID) { MyQty obj=null; using(SqlConnection con2=new SqlConnection( WebConfigurationManager.ConnectionStrings[Geordie_Jeans].ConnectionString )) { using( SqlCommand cmd2 = new SqlCommand(PR_GET_QUANTITY con2){ cmd2.Parameters.Add(@ID SqlDbType.Int).Value = prodID; con2.open(); using(SqlDataReadear= cmd2.ExecuteReader()) { obj=new MyQty(); // Here set the property of the object } } } return obj; } Thanks narayan

Glen Robson · Answer

Thanks for the reply. However i think this sounds a little overkill just to close the connection. There must be another way to close it? This is not the only place that i am having this problem. I have over 70 other situations like this so to go through and change all of them would take a very long time. Boldonglen

Jignesh Trivedi · Answer

Hi OK I forgot that this code is in aspx.cs file Can we move your code in admin.cs? Create One class like in admin.cs public class QuantData { public string LQty { get; set; } public string MQty { get; set; } public string SQty { get; set; } } update.aspx.cs private void LoadQuantity(int ProdID) { Admin admin = new Admin(); QuantData q = new QuantData(); q = admin.GetQuantData(ProdID); if (q != null) { txtLQty.Text = q.LQty; txtMQty.Text = q.MQty; txtSQty.Text = q.SQty; } } Admin.cs SqlConnection con2; static string connectionString = WebConfigurationManager.ConnectionStrings[Geordie_Jeans].ConnectionString; public SqlDataReader GetQuant(int prodID) { SqlCommand cmd2 = new SqlCommand(PR_GET_QUANTITY con2); cmd2.CommandType = CommandType.StoredProcedure; cmd2.Parameters.Add(@ID SqlDbType.Int).Value = prodID; SqlDataReader reader2 = null; try { reader2 = cmd2.ExecuteReader(); return reader2; } catch (Exception err) { throw err; } } public void OpenConnection() { if (con2 == null) { con2 = new SqlConnection(connectionString); } if (con2.State == ConnectionState.Closed) { con2.Open(); } } public void CloseConnection() { if (con2 != null && con2.State == ConnectionState.Open) { con2.Close(); } } public QuantData GetQuantData(int ProdID) { OpenConnection(); SqlDataReader reader2 = GetQuant(ProdID); QuantData q = new QuantData(); int count = reader2.FieldCount; while (reader2.Read()) { for (int i = 0; i { if (reader2[PROD_SIZE].ToString() == L) { q.LQty = reader2[PROD_QUANT].ToString(); } if (reader2[PROD_SIZE].ToString() == M) { q.MQty = reader2[PROD_QUANT].ToString(); } if (reader2[PROD_SIZE].ToString() == S) { q.SQty = reader2[PROD_QUANT].ToString(); } } } reader2.Close(); CloseConnection(); return q; }

Glen Robson · Answer

Ok i think i know what you mean. So you think i should close the reader in the .aspx.cs file and not in my class file (.cs)? Look something like this: private void LoadQuantity(int ProdID) { SqlDataReader reader2 = Geordie_Jeans.Classes.AdminControl.GetQuant(ProdID); int count = reader2.FieldCount; while (reader2.Read()) { for (int i = 0; i { if (reader2[PROD_SIZE].ToString() == L) { txtLQty.Text = reader2[PROD_QUANT].ToString(); } if (reader2[PROD_SIZE].ToString() == M) { txtMQty.Text = reader2[PROD_QUANT].ToString(); } if (reader2[PROD_SIZE].ToString() == S) { txtSQty.Text = reader2[PROD_QUANT].ToString(); } } } reader2.Close(); } This seems to work however i cannot access the connection (con) to close it within this block of code above. It seems the only way i will be able to close my connection is in the class file under my GetQuant() method? I can see that you have asked me to use a global vairable for the connection. However i dont feel that this is very secure. Is there any other way that i could close the connection? Thanks Boldonglen

Jignesh Trivedi · Answer

hi Do not close reader and connection in finally block of GetQuant method. Intead of close reader and connection after datareader.read() method. for same make connection as a globle variable. Let me know if you not solved ur problem. hope this help.