Use connection pool after sql server restart the query will fail

  • Thread starter Thread starter fish
  • Start date Start date
F

fish

Hi,

If I use the connection pool (It is default) in ADO sql server provider,
it will get trouble to run any sql after the sql server restarted.
Is ther a way to detect the pooling connection is valid ?
The SqlConnection state is useless.
It can not reflect the real situation when we use pooling connection.
If I switch to use non pooling connection, it is ok.
I still prefer to use pooling connection. Is any way to solve the problem ?

Thanks.

The sample code that I get the trouble is below.
try

{

string connectionstring ="Server=localhost;Initial Catalog=NorthWind;user id=sa;pwd=sapassword";

string strquery = "select * from categories";

string strcolumn = "categoryid";

//connectionstring += ";Pooling=false";

SqlConnection conn = new SqlConnection();

conn.ConnectionString = connectionstring;

conn.Open();


SqlCommand cmd = new SqlCommand(strquery,conn);

SqlDataReader reader = cmd.ExecuteReader();

int id ;

while( reader.Read() )

{

id= reader.GetInt32( reader.GetOrdinal(strcolumn));

//Debug.WriteLine(id.ToString());

}

reader.Close();

conn.Close();

// Net Stop mssqlserver

// Net Start mssqlserver

conn = new SqlConnection();

conn.ConnectionString = connectionstring;

conn.Open();

//Debug.WriteLine("ServerVersion: " + conn.ServerVersion

+ "\nState: " + conn.State.ToString());


cmd = new SqlCommand(strquery,conn);

reader = cmd.ExecuteReader(); // Exception will raise here

while( reader.Read() )

{

id= reader.GetInt32( reader.GetOrdinal(strcolumn));

//Debug.WriteLine(id.ToString());

}

reader.Close();

conn.Close();

}

catch( Exception ex)

{

string str = ex.Message; //"General network error. Check your network documentation.

//Debug.WriteLine(str);

}



Guo
 
Not until ADO 2.0 and the 2.0 Framework.

The problem is that all pooled connections are "broken" when the server goes down. Your application's exception handler needs to walk through all of these one-at-a-time until you get a good connection. The problem is unless you add more code to "poll" the server to see if it's really back up, you might be doing this to no avail.

ADO 2.0 has a "clear pool" method that will help and ways to enumerate the visible servers to make sure it's alive before you start trying to connect.

I'm giving workshops over the next couple of months (one this weekend in Orlando) that discuss and detail these issues.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi,

If I use the connection pool (It is default) in ADO sql server provider,
it will get trouble to run any sql after the sql server restarted.
Is ther a way to detect the pooling connection is valid ?
The SqlConnection state is useless.
It can not reflect the real situation when we use pooling connection.
If I switch to use non pooling connection, it is ok.
I still prefer to use pooling connection. Is any way to solve the problem ?

Thanks.

The sample code that I get the trouble is below.
try

{

string connectionstring ="Server=localhost;Initial Catalog=NorthWind;user id=sa;pwd=sapassword";

string strquery = "select * from categories";

string strcolumn = "categoryid";

//connectionstring += ";Pooling=false";

SqlConnection conn = new SqlConnection();

conn.ConnectionString = connectionstring;

conn.Open();


SqlCommand cmd = new SqlCommand(strquery,conn);

SqlDataReader reader = cmd.ExecuteReader();

int id ;

while( reader.Read() )

{

id= reader.GetInt32( reader.GetOrdinal(strcolumn));

//Debug.WriteLine(id.ToString());

}

reader.Close();

conn.Close();

// Net Stop mssqlserver

// Net Start mssqlserver

conn = new SqlConnection();

conn.ConnectionString = connectionstring;

conn.Open();

//Debug.WriteLine("ServerVersion: " + conn.ServerVersion

+ "\nState: " + conn.State.ToString());


cmd = new SqlCommand(strquery,conn);

reader = cmd.ExecuteReader(); // Exception will raise here

while( reader.Read() )

{

id= reader.GetInt32( reader.GetOrdinal(strcolumn));

//Debug.WriteLine(id.ToString());

}

reader.Close();

conn.Close();

}

catch( Exception ex)

{

string str = ex.Message; //"General network error. Check your network documentation.

//Debug.WriteLine(str);

}



Guo
 
Back
Top