How to handle multiple connection to asp project

Question

I have a ASP project which is been hosted on godaddy . There are multiple user who will be logging in because of it the project behaves awkwardly as in it wont login , updating any records says it already exists but when single user uses it works fine. Also an server error , which I understood by is there were mulitple connection to the SQL database and it was not properly closed. I have to make it a multiuser .

SO is there any particular code that I need to write

If you need any part of the code will be glad to share, do let me know.

Heres my connection string in web.config file :

<connectionStrings>

<add name="dbconnection" connectionString="Data Source=SQLOLEDB;Server=184.168.47.15;Database=ab;UID=abc;PWD=abc" providerName="System.Data.SqlClient"/>

 </connectionStrings>

Update code:

 string strcon = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
    SqlConnection conn;
    SqlCommand cmd;
                conn = new SqlConnection(strcon);
                conn.Open();
                if (UserId != txtenrolleecode.Text)
                {
                    string sqlqry = "select * from Newenrollee where enrollecode=@enrollecode ";
                    cmd = new SqlCommand(sqlqry, conn);
                    cmd.Parameters.AddWithValue("@enrollecode ", txtenrolleecode.Text);
                    da = new SqlDataAdapter(cmd);
                    da.Fill(dt);

                    if (dt.Rows.Count > 0)
                    {
                        ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Enrolee Code Already Exist !!');", true);
                        txtenrolleecode.Focus();
                        return;
                    }
                }
                string enroleename = (txtfirstname.Text + " " + txtmiddle.Text + " " + txtsurname.Text).ToString();
                string spousename = (lblspouse.Text + " " + lblspousemiddlename.Text).ToString();
                cmd = new SqlCommand("Update Newenrollee set Date=@Date,HMOID= @HMOID,HMOName=@HMOName,Createdate=(CONVERT (datetime, @Createdate,103)),surname= @surname,firstname= @firstname,middlename=@middlename,designation=@designation,occupation=@occupation,martialstatus= @martialstatus,DOB= (CONVERT (datetime, @DOB,103)),age= @age,nationalid= @nationalid,bloodgroup= @bloodgroup,gl=@gl,sex=@sex,address=@address,mob1=@mob1,mob2=@mob2 where enrollecode=@enrollecode", conn);
                cmd.Parameters.AddWithValue("@Date", System.DateTime.Now);
                cmd.Parameters.AddWithValue("@HMOID", txthmocode.Text);
                cmd.Parameters.AddWithValue("@HMOName", ddhmoname.Text);
                cmd.Parameters.AddWithValue("@Createdate", dtcreate.Text);
                cmd.Parameters.AddWithValue("@enrollecode", txtenrolleecode.Text);
                cmd.Parameters.AddWithValue("@surname", txtsurname.Text);
                cmd.Parameters.AddWithValue("@firstname", txtfirstname.Text);
                cmd.Parameters.AddWithValue("@middlename", txtmiddle.Text);
                cmd.Parameters.AddWithValue("@designation", designation.Text);
                cmd.Parameters.AddWithValue("@occupation", occupation.Text);
                cmd.Parameters.AddWithValue("@martialstatus", cmbmartialstatus.Text);
                cmd.Parameters.AddWithValue("@DOB", DOB.Text);
                cmd.Parameters.AddWithValue("@age", txtage.Text);
                cmd.Parameters.AddWithValue("@nationalid", nationalid.Text);
                cmd.Parameters.AddWithValue("@bloodgroup", bloodgroup.Text);
                cmd.Parameters.AddWithValue("@gl", txtgl.Text);
                cmd.Parameters.AddWithValue("@sex", cmbsex.Text);
                cmd.Parameters.AddWithValue("@address", txtaddress.Text);
                cmd.Parameters.AddWithValue("@mob1", txtmob1.Text);
                cmd.Parameters.AddWithValue("@mob2", txtmob2.Text);                

 ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Records Updated Successfully')</script>");

                conn.Close();
                clear();

the code is pretty long as lot of fields to update made it short if I miss any bracket please pardon

error:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Stack Trace :

[InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.]
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +5364576
      System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +146
 System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +16
 System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +94
 System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +110
 System.Data.SqlClient.SqlConnection.Open() +96
 FAHIS_Data_Entry_Software_in_WEB.Master2.Page_Load(Object sender, EventArgs e) +375
 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
 System.Web.UI.Control.OnLoad(EventArgs e) +92
 System.Web.UI.Control.LoadRecursive() +54
 System.Web.UI.Control.LoadRecursive() +145
 System.Web.UI.Page.ProcessRequestMain(Boolean   includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772

I would paste some code to demonstrate how your dealing with users and an example of how your updating records etc — horHAY, Apr 15 '15 at 10:59
@horHAY I have not made any special consideration regarding user handling. Basically thats my question — mark, Apr 15 '15 at 11:08
Post the code where the value is inserted/updated in database. — Sukanya1991, Apr 15 '15 at 11:08
This is often due to forgetting that `static` members would be shared among sessions — Alex K., Apr 15 '15 at 11:09
@AlexK. can you please share a link or explain 'static' members. — mark, Apr 15 '15 at 11:13
See if this applies to you: [Static variable in asp.net page](http://stackoverflow.com/questions/5217697/static-variable-in-asp-net-page) — Alex K., Apr 15 '15 at 11:14
@Sukanya this not specific for insert/update, its for whole application however will share it to get better perspective — mark, Apr 15 '15 at 11:29
I agree with @AlexK. It can be a case of `static` variable being shared. — Sukanya1991, Apr 15 '15 at 11:40
I am using `public static string userName = ""` and then keep session in same `userName` variable `userName = Convert.ToString(Session["username"]);` — mark, Apr 15 '15 at 11:44
If username is static then **all** users are effectively using the same variable & last written value; if userB sets the value after userA has set the value, then when userA reads it later they will see the username of userB. See the post I linked above. — Alex K., Apr 15 '15 at 11:59
@AlexK. right understood `static` any suggestion to improve ? — mark, Apr 15 '15 at 12:01
@AlexK. some places I have directly used `if(session["username"]!="")` and some as mentioned above. Once app is idle it gives error where I have used directy. — mark, Apr 16 '15 at 13:43

Shyju · Answer 1 · 2015-04-15T12:12:02.043

The first thing i would do is to make sure the SqlConnection and SqlCommand objects are properly disposed immediately after use. You may use the using statement to take care of this.

This has nothing to do with connection pooling, but it is better to use a simple SqlDataReader if you are looking to read forward only data.

Also, open the connection just before you really need it.

using(var conn=new SqlConnection(yourConnString))
{
   using(var cmd=new SqlCommand(sqlQry,conn))
   {
      conn.Open();
      using (IDataReader sqlDataReader = cmd.ExecuteReader())
      {
        //read from sqlDataReader now
      }

   }   

}

With this approach, you do not need to worry about closing the connection explicitly as it will be taken care by the using statement.

Where do I open and close connection in your example – mark Apr 15 '15 at 12:09 — mark, Apr 15 '15 at 12:09

How to handle multiple connection to asp project

1 Answers1