Alexey.mdr wrote:Do you open/close a connection on each database data exchange operation?
Could you provide a block of code where this error shows up please?
Thank you for your reply.
No, db connection isn't opened and closed on each query. All SQL communication is done within our Class files, so pretty much every time an essential class is opened, the connection to the db is opened, the select queries take place, and the db connection is closed. The data is manipulated while in the sesion/viewstate and the update statements are executed all at once once updateDB() is called within the class.
The error can take place at ANY time in the application, even if the instruction is a single update statement, and no classes are even used. Again, I notice this can be reproduced ANYWHERE I use mysql code to a remote mysql server -- the key here, however, is the time. As expressed in the first post, if the mysql statements are made in succession, the error usually isn't experienced. If I sit on a page for a few minutes, and then make another command, thats where I run into the error...... seriously sounds like some type of timeout or keepalive issue, but I've looked into both extensively.
A code example:
Code: Select all
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using CoreLab.MySql;
///
/// Summary description for Clients
///
public class Client
{
int clientid;
string firstname, lastname;
string company;
string address1, address2;
string city, state, zip;
string phone1, phone2, fax;
string email;
string username, password;
string status;
string additional; // additional contract terms applies to all client carts
ArrayList cartids;
bool isActive;
public int getClientID() { return clientid; }
public string getFirstName() { return firstname; }
public string getLastName() { return lastname; }
public string getFullName() { return firstname + " " + lastname; }
public string getCompany() { return company; }
public string getAddress1() { return address1;}
public string getAddress2() { return address2;}
public string getCity() { return city; }
public string getState() { return state;}
public string getZip() { return zip;}
public string getPhone1() { return phone1;}
public string getPhone2() { return phone2;}
public string getFAX() { return fax;}
public string getEmail() { return email;}
public string getUsername() { return username;}
public string getPassword() { return password;}
public string getStatus() { return status;}
public bool getIsActive() { return isActive;}
public string getAdditional() { return additional; }
public void setAdditional(string thisAdditional) { additional = thisAdditional; }
public Client()
{
isActive = false;
clientid = 0;
firstname = "";
lastname ="";
firstname = "";
company="";
email = "";
address1 = "";
address2 = "";
city = "";
state = "";
zip = "";
phone1 = "";
phone2 = "";
fax = "";
username = "";
password = "";
status ="A";
additional = "";
}
public void setValues(string thisFirstName, string thisLastName, string thisCompany, string thisAddress1, string thisAddress2, string thisCity, string thisState, string thisZip, string thisPhone1, string thisPhone2, string thisFAX, string thisEmail, string thisUsername, string thisPassword, string thisStatus, string thisAdditional)
{
firstname = thisFirstName;
lastname = thisLastName;
company = thisCompany;
email = thisEmail;
address1 = thisAddress1;
address2 = thisAddress2;
city = thisCity;
state = thisState;
zip = thisZip;
phone1 = thisPhone1;
phone2 = thisPhone2;
fax = thisFAX;
additional = thisAdditional;
// isActive = true;
}
public int updateDB()
{
MySqlConnection myConnection = connectDB();
myConnection.Open();
if (isActive == true)
{
// update records
string myInsertQuery = "UPDATE Clients SET FirstName = '" + firstname + "', LastName = '" + lastname + "', Email = '" + email + "', Address1 = '" + address1 + "', Address2 = '" + address2 + "', City = '" + city + "', State = '" + state + "', Zip = '" + zip + "', Phone1 = '" + phone1 + "', Phone2 = '" + phone2 + "', FAX = '" + fax + "', Username = '" + username + "', Password = '" + password + "', Status = '" + status + "', Additional = '" + additional + "' WHERE ClientID = " + clientid.ToString() + ";";
MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConnection);
myCommand.ExecuteNonQuery();
}
else if (isActive == false)
{
// insert new record
string myInsertQuery = "INSERT INTO Clients (ClientID, FirstName, LastName, Company, Address1, Address2, City, State, Zip, Phone1, Phone2, FAX, Email, Username, Password, Status, Additional) VALUES ('', '" + firstname + "','" + lastname + "','" + company + "','" + address1 + "','" + address2 + "','" + city + "','" + state + "','" + zip + "','" + phone1 + "','" + phone2 + "','" + fax + "','" + email + "','" + username + "','" + password + "','" + status + "', '" + additional + "');";
MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConnection);
myCommand.ExecuteNonQuery();
clientid = getLastClientID();
isActive = true;
}
myConnection.Close();
return clientid;
}
public int getLastClientID()
{
MySqlConnection myConnection = connectDB();
myConnection.Open();
string mySelectQuery = "SELECT ClientID FROM Clients ORDER BY ClientID ASC;";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
int count = 0;
int result = 0;
while (myReader.Read())
{
result = myReader.GetInt32(0);
count++;
}
myReader.Close();
// always call Close when done reading.
myConnection.Close();
return result;
}
public ArrayList populateCarts(int thisClientID)
{
MySqlConnection myConnection = connectDB();
myConnection.Open();
ArrayList myList = new ArrayList();
string mySelectQuery = "SELECT CartID FROM Carts WHERE ClientID = " + thisClientID.ToString() + ";";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
int count = 0;
int result = 0;
while (myReader.Read())
{
result = myReader.GetInt32(0);
myList.Add(result);
count++;
}
myReader.Close();
// always call Close when done reading.
myConnection.Close();
return myList;
}
public ArrayList populateCarts()
{
ArrayList myList = new ArrayList();
if (isActive)
{
MySqlConnection myConnection = connectDB();
myConnection.Open();
string mySelectQuery = "SELECT CartID FROM Carts WHERE ClientID = " + clientid.ToString() + ";";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
int count = 0;
int result = 0;
while (myReader.Read())
{
result = myReader.GetInt32(0);
myList.Add(result);
count++;
}
myReader.Close();
// always call Close when done reading.
myConnection.Close();
}
return myList;
}
public void openClient(int openClientID)
{
MySqlConnection myConnection = connectDB();
myConnection.Open();
string mySelectQuery = "SELECT FirstName, LastName, Company, Address1, Address2, City, State, Zip, Phone1, Phone2, FAX, Email, Username, Password, Status FROM Clients WHERE ClientID = " + openClientID.ToString() + ";";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
int count = 0;
while (myReader.Read())
{
firstname = myReader.GetString(0).ToString();
lastname = myReader.GetString(1).ToString();
company = myReader.GetString(2).ToString();
address1 = myReader.GetString(3).ToString();
address2 = myReader.GetString(4).ToString();
city = myReader.GetString(5).ToString();
state = myReader.GetString(6).ToString();
zip = myReader.GetString(7).ToString();
phone1 = myReader.GetString(8).ToString();
phone2 = myReader.GetString(9).ToString();
fax = myReader.GetString(10).ToString();
email = myReader.GetString(11).ToString();
username = myReader.GetString(12).ToString();
password = myReader.GetString(13).ToString();
status = myReader.GetString(14).ToString();
clientid = openClientID;
count++;
}
myReader.Close();
// always call Close when done reading.
myConnection.Close();
if (count > 0)
{
isActive = true;
// proceed
cartids = getCartIDs();
}
else
isActive = false; // nothing found
}
public ArrayList getClientNames()
{
MySqlConnection myConnection = connectDB();
myConnection.Open();
string mySelectQuery = "SELECT ClientID, Name FROM clients ORDER BY Name ASC;";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
ArrayList myList = new ArrayList();
ArrayList idList = new ArrayList();
ArrayList nameList = new ArrayList();
while (myReader.Read())
{
idList.Add((string)myReader.GetInt32(0).ToString());
nameList.Add((string)myReader.GetString(1).ToString());
}
myReader.Close();
// always call Close when done reading.
myConnection.Close();
myList.Add(idList);
myList.Add(nameList);
return myList;
}
public ArrayList getCartIDs()
{
ArrayList myList = new ArrayList();
if (isActive)
{
MySqlConnection myConnection = connectDB();
myConnection.Open();
string mySelectQuery = "SELECT CartID FROM Carts WHERE ClientID = " + clientid.ToString() + ";";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
myList.Add((string)myReader.GetInt32(0).ToString());
}
myReader.Close();
// always call Close when done reading.
myConnection.Close();
}
return myList;
}
protected MySqlConnection connectDB()
{
Misc m = new Misc();
return m.connectDB();
}
public ArrayList getRecentTransactions()
{
ArrayList myList = new ArrayList();
if (isActive)
{
Cart car = new Cart();
// todo method convertDateStringToString should be in class Misc in addition to Cart - performance
string searchDate = car.convertDateStringToString(DateTime.Now.AddMonths(-3).ToShortDateString());
MySqlConnection myConnection = connectDB();
myConnection.Open();
//todo - i removed the WHERE clauase specifying only the last 90 days should be included. errors occurred with it enabled. currently all results will be displayed
string mySelectQuery = "SELECT Transactions.TransactionID FROM Transactions, CartsSchedule WHERE Transactions.ClientID = " + clientid.ToString() + " AND Transactions.TransactionID = CartsSchedule.TransactionID ORDER BY CartsSchedule.RentDate ASC;";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
int result = 0;
while (myReader.Read())
{
result = myReader.GetInt32(0);
myList.Add(result);
}
myReader.Close();
// always call Close when done reading.
myConnection.Close();
}
return myList;
}
}
See any problems or bad practices with CoreLab? Thank you!