Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
himanshupancholi
Starting Member
3 Posts |
 Posted - 2008-03-16 : 07:47:24
|
| 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. |
|
|
himanshupancholi
Starting Member
3 Posts |
Posted - 2008-03-17 : 03:00:58
|
| 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"]; }}and Connectionstring is:Data Source=CYPAPRM2SRV\SQLEXPRESS;Initial Catalog=Portal;Integrated Security=True |
 |
|
|
himanshupancholi
Starting Member
3 Posts |
Posted - 2008-03-17 : 15:14:23
|
| Guys, the problem is solved. Below is the code, if it can help anyone: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;} |
|
|
|
|
|
|
Connection crash if multiple user click same link.