trouble in connecting to Postgre SQL from web server.

hellono1

Hi all. I'm now developing a website that connect to Postgre SQL database. I used NpgSQL.DLL to connect from C# to database. I also finished testing this project in my local pc, it worked well. But unfortunately it did not work well in server. Some errors in connecting to server happened. These errors happened when website had much people logged in and began using database query functions ( select, update, delete .. ). I think errors begin from codes in connect database functions. but i still can not fix it. this is my connecting database codes. ------ codes connects database ---------

-- Connect.cs file.
public static void connectDB()
{
try
{
conn.ConnectionString = connection;
conn.Open();
}
catch
{
//closeConnectDB();
}
}
public static void closeConnectDB()
{
//conn.Dispose();
conn.Close();
//conn.Dispose();
}
public static int ExecuteQuery(string SQL)
{
try
{
connectDB();
NpgsqlCommand cmd = new NpgsqlCommand(SQL, conn);
return cmd.ExecuteNonQuery();
}
catch
{
closeConnectDB();
return 0;
}

}

public static DataSet SelectQuery(string SQL)
{
    DataSet ds = new DataSet();
    try
    {
        connectDB();
        NpgsqlDataAdapter nDa = new NpgsqlDataAdapter(SQL, conn);
        
        nDa.Fill(ds);
        // closeConnectDB();
        return ds;
    }
    catch
    {
        closeConnectDB();
        return ds;
    }
    
}

----------------- use to execute these codes -------- ....

--- calling functions files.
try
{
string strTest = "Select * from abc where .. ";
DataSet ds1 = new DataSet();
ds1 = Connect.SelectQuery(strTest);
if (ds1.Tables[0].Rows.Count > 0)
{

                        }
                        else
                        {
                            int kq = Connect.ExecuteQuery(strInsert);
                            if (kq > 0)
                            {
                                int

Pete OHanlon

Looking at your code, I can't help but think you're being hammered because you have static methods in a multi user system, and the code isn't thread safe. In other words, you have static variables (take a look at your connection variable), and you are opening and closing them without any form of synchronisation in place. In other words, you could have two users both "open" a connection at the same time, and then one finishes and closes the connection before the other one completes. The easiest fix is to move the open and close logic into the other methods, rather than relying on member variables.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Luc Pattyn

stop your static abuse, and start using the using construct[^], it fits quite well in DB applications. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

hellono1

thanks. i did it and my problems solved.

hellono1

thank u so much.