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 TrivediPosted Apr 19, 2012, 11:37 PM
datareader.Dispose() method ensures all resources associated with the current DataReader object instance are released.
hopet thsi help.
Glen RobsonPosted Apr 19, 2012, 8:27 AM
Jignesh TrivediPosted Apr 19, 2012, 8:21 AM
Back to your original code.
try..
reader2.Dispose(); after closing datareader.
hope this help.
NarayanPosted Apr 19, 2012, 7:00 AM
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 SQty{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 RobsonPosted Apr 19, 2012, 6:56 AM
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 TrivediPosted Apr 19, 2012, 6:47 AM
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 < count; 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 RobsonPosted Apr 19, 2012, 5:54 AM
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 < 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();
}
}
}
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 TrivediPosted Apr 19, 2012, 12:05 AM
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.