Hello everyone. I have a question here, that to me seems there must be a way to do what I want in the way I want :)
I have a thing that updates a Zen Cart DB and adds products to it.
Ok everything works the way it is now, but It close the connection to the DB after each item, is there a way to prevent this?
Little background and code
I originally tried to just null out my command but afte the first one I would get "There is already an open DataReader associated with this Connection which must be closed first."
so I searched around the internet and found something where they set the dataReader to close the connection, At that point it works, but as I said before, it disconnects and has to reconnect every item.
here is the code with before and after. any ideas would be great.
//** = points of interest
the SQL statement is 3 INSERT statements hence why i didn't have a DataReader Object originally
private static void AddZenCartItem(Product item)
{
Log.WriteLine("Im adding "+item.Description+" to the zencart.");
String sql;
//Now lets put some SQL in that String
sql = //BIG LONG SQL STATEMENT
//Reopen the connection just incase it closed
if (MyConn.State != ConnectionState.Open)
MyConnect();
MySqlCommand command = MyConn.CreateCommand();
//**Added from the internet fix
MySqlDataReader dataReader = null;
try
{
command.CommandText = sql;
//Fill in them infos
command.Parameters.Add(new MySqlParameter("?product_id", item.SKU));
command.Parameters.Add(new MySqlParameter("?products_model", item.ProductNo));
command.Parameters.Add(new MySqlParameter("?products_image", item.Attachments[0]));
command.Parameters.Add(new MySqlParameter("?price", item.Price));
command.Parameters.Add(new MySqlParameter("?master_categories_id", item.CategoryNo));
command.Parameters.Add(new MySqlParameter("?manufacturers_id", item.SupplierNo));
command.Parameters.Add(new MySqlParameter("?products_name", item.Description));
command.Parameters.Add(new MySqlParameter("?products_description", item.LongDesc));
//**Added from the internet fix
dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
//**Original code
command.ExecuteReader();
}
catch (Exception e)
{
Log.WriteLine("Error Adding item: " + item.Description + "(" + item.ProductNo + ")");
Log.WriteLine("Error: " + e.Message);
}
finally
{
//**Added from internet fix
if (dataReader != null)
dataReader.Close();
//**Original code
command.Dispose();
}
}