Hi,

I just developed a ASP.NET website with SQL Server 2005 as database. I am having connection crash problem when multiple user click on same link to fetch data from the database. If users click on different links or there is few seconds of time gap between the data access, then it works fine. But the connections crash problem only occurs when 2 or more users click same link at same time.

I am using the following kind of Datalayers to access the data from database:

public static ContentInformation GetContentForUpdate(int ContentId)

{

ContentInformation result = new ContentInformation(); ;

SqlCommand command = new SqlCommand();

command.CommandType = CommandType.StoredProcedure;

command.Connection = Connection;

command.CommandText = "Content_GetContentForUpdate1";

SqlParameter parameter = new SqlParameter("@ContentId", SqlDbType.Int);

parameter.Direction = ParameterDirection.Input;

parameter.Value = ContentId;

command.Parameters.Add(parameter);

try

{

command.Connection.Open();

SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);

if (reader.Read())

{

// read the data

}

reader.Close();

}

catch (SqlException ex)

{

Trace.TraceError(ex.Message);

throw ex;

}

finally

{

command.Connection.Close();

}

return result;

}

I would be really grateful if someone can help me out with this problem. Waiting for the soonest reply. Thanks.

 Where does "Connection" come from? It should NOT be a static variable.

Connection is defined as below:

public static SqlConnection Connection
{
        get
        {
            if (System.Web.HttpContext.Current.Application["sqlConnection"] == null)
                System.Web.HttpContext.Current.Application["sqlConnection"] = new SqlConnection(ConfigurationManager.ConnectionStrings["PortalConnectionString"].ConnectionString);
           

        return (SqlConnection)System.Web.HttpContext.Current.Application["sqlConnection"];    
        }
}

Also Connection String is:

Data Source=CYPAPRM2SRV\SQLEXPRESS;Initial Catalog=Portal;Integrated Security=True

OK, that explains your problems. It a really bad idea to share a single Connection instance, since you will run in to all kinds of concurrency issues. One thread could be closing the connection while another is reading from it etc.

Create connection instances when you need them. The built in connection pooling will do the relevant resource handling for you.

I suggest you remove the Connection method immediately, so that you'll easily find all places where it has been used.



 

public static ContentInformation GetContentForUpdate(int ContentId)
{
	using (SqlConnection conn=new SqlConnection(ConfigurationManager.ConnectionStrings["PortalConnectionString"].ConnectionString))
	{
		//"using" will implicitly close the connection, so no "finally" is needed
		ContentInformation result = new ContentInformation();
		//Simple syntax
		SqlCommand command = new SqlCommand("Content_GetContentForUpdate1",conn);
		command.CommandType = CommandType.StoredProcedure;
		//Simple syntax
		command.Parameters.Add("@ContentId", SqlDbType.Int).Value=ContentId;
		try
		{
			conn.Open();
			SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
			if (reader.Read())
			{
			
				// read the data

			}

			reader.Close();
		}
		catch (SqlException ex)
		{
			Trace.TraceError(ex.Message);
			throw ex;
		}
	}
	return result;
}

  

Thanks a lot. This solved the problem...

Hi I too have some issue while connecting to the SQL SERVER 2000 database. My application runs perfectly for an hr r two. than stops connecting to the database, I don't have any Public Shared Methods/variables in my class. if i restart SQL server/ IIS 6.0 it again work for few hrs. Application is developed in .Net 2003 with 2K SQL Server as back end. Hosted on our own server with 2003 OS.Please help me regarding this issue. Thanks a lot in advance