Hi,
I have a .NET web application which is distributed across 3 physical tiers.
The application and web tier communicate via .NET Remoting. All calls to SQL
server is made only from the application tier. Both application and web tier
are configured for network load balancing and the SQL Server is configured on
a active/passive cluster. All tiers run on Win2K3 and the .NET framework 1.1
is installed on app and web tier.
Of late, I have been getting a â??General network errorâ?? from SQL Server,
which is thrown as a System.Data.SqlClient.SQLException by the app tier,
which is then recorded in the event log. Please find the complete exception
stack trace at the bottom of this posting. Due to the â??connection brokenâ??
error, the users experiences severe data loss and query execution failures.
In order to give you an idea of how the I use the connections I have
included a few lines from my code below.
Class SqlDAO
{
private SqlConnection connection;
protected bool isDisposed;
public SqlDAO()
{
isDisposed = false;
GetDatabaseConnection();
}
public void Dispose()
{
CloseDatabaseConnection();
isDisposed = true;
GC.SuppressFinalize(this);
return;
}
public void Finalize()
{
if (!isDisposed)
{
CloseDatabaseConnection();
isDisposed = true;
}
return;
}
private void CloseDatabaseConnection()
{
connection.Close();
connection.Dispose();
}
private void GetDatabaseConnection()
{
try
{
if (connection == null && isDisposed == false)
{
connection = new SqlConnection(<SQL_CONNECTION_STRING>);
}
if (connection != null)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
}
}
catch(SqlException ex)
{
throw new DBConnectionFailedException
("Database Connection Failure.",ex);
}
}
private void PrepareCommand( SqlCommand command,
SqlTransaction transaction, string storedProcName,
SqlParameter[] commandParameters)
{
// check the status of the database connection
GetDatabaseConnection();
// associate the connection with the command
command.Connection = connection;
// set the command text (stored procedure name)
command.CommandText = storedProcName;
// if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
// set the command type
command.CommandType = CommandType.StoredProcedure;
// attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
private void AttachParameters(SqlCommand command, SqlParameter[]
commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
// check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value ==
null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
protected DataTable ExecuteDataset(string storedProcName,
params SqlParameter[] commandParameters)
{
// create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, (SqlTransaction)null,storedProcName,
commandParameters);
// create the DataAdapter & DataSet
SqlDataAdapter da = new SqlDataAdapter(cmd);
//Create a new data table object
DataTable dt = new DataTable();
// fill the DataTable
da.Fill(dt);
// detach the SqlParameters from the command object,
// so they can be used again.
cmd.Parameters.Clear();
return dt;
}
}
***************************************************************
class LookupDAO: SqlDAO
{
Dataset ds;
Public LookupDAO()
{
ds = new Dataset();
}
public Dataset SelectUniqueParticipantLastNames(short regionID,
short participantStatusID)
{
try
{
// Set up parameters (1 input and 1 output)
SqlParameter [] arParms = new SqlParameter[3];
// Region ID Input parameter
arParms[0] = new SqlParameter("@RegionID", LookupConstants.REGION_ID_DT);
arParms[0].Value = regionID;
// Participant Status ID Input parameter
arParms[1] = new SqlParameter("@StatusID", LookupConstants.STATUS_ID_DT);
arParms[1].Value = participantStatusID;
// ReturnStatus Output Parameter
arParms[2] = new SqlParameter("@ReturnStatus",
CommonDBConstants.RETURN_STATUS_DT );
arParms[2].Direction = ParameterDirection.Output;
DataTable dt = ExecuteDataset( "Sp_GetParticipantByRegionAndStatus",
arParms);
if ( int.Parse(arParms[2].Value.ToString()) != 0) return lookupDTO;
dt.TableName = TableConstants.PARTICIPANT_TABLE;
ds.Tables.Add(dt);
}
catch(System.Exception ex)
{
throw new QueryExecutionFailedException("Participant Search Failure",ex);
}
return ds;
}
}
************************************************************
************************************************************ Exception
Information
*********************************************
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 20
LineNumber: 0
Message: General network error. Check your network documentation.
Number: 11
Procedure: ConnectionWrite (send()).
Server:
State: 0
Source: .Net SqlClient Data Provider
TargetSite: System.Data.SqlClient.SqlDataReader
ExecuteReader(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior,
Boolean)
HelpLink: NULL
StackTrace Information
*********************************************
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Us.Idhw.Rms.Biz.Util.SqlDAO.ExecuteDataset(String storedProcName,
SqlParameter[] commandParameters)
at
Us.Idhw.Rms.Biz.Lookup.LookupDAO.SelectUniqueParticipantLastNames(Int16
regionID, Int16 participantStatusID).
************************************************************
************************************************************I have searched
various newsgroups for a resolution but nothing seem satisfactory. I am not
sure if this is purely a network, database server, user load or connection
pooling problem. Any suggestions and pointers would be greatly appreciated.
Thank you.
Magdelin