Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

Question

I have a problem when i uploaded the excel sheet containing the submitted urls, i want to match each record of excel with db data it is working fine when i upload a small file it is woking fine but if there is 2MB file then there is exception :Details 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.

Here is my code

 protected void btnUpload_Click(object sender, EventArgs e)
    {
        if ((txtFilePath.HasFile))
        {

            OleDbConnection conn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            string query = null;
            string connString = "";
            string strFileName = DateTime.Now.ToString("sddMMyyyy_LOFTY");
            string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();

            //Check file type
            if (strFileType == ".xls" || strFileType == ".xlsx")
            {
                txtFilePath.SaveAs(Server.MapPath("~/AdminCpanel/UploadedExcel/" + strFileName + strFileType));
            }
            else
            {
                lblMessage.Text = "Only excel files allowed";
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Visible = true;
                return; 
            }

            string strNewPath = Server.MapPath("~/AdminCpanel/UploadedExcel/" + strFileName + strFileType);

            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            // Sheet name is Ads Posted
            query = "SELECT * FROM [Ads Posted$]";
            //Create the connection object
            conn = new OleDbConnection(connString);
            //Open connection
            if (conn.State == ConnectionState.Closed) conn.Open();
            //Create the command object
            cmd = new OleDbCommand(query, conn);
            da = new OleDbDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "validateLog");


            //// Sheet name is Details
            //query = "SELECT * FROM [Details$]";
            ////Create the connection object
            //conn = new OleDbConnection(connString);
            ////Open connection
            //if (conn.State == ConnectionState.Closed) conn.Open();
            ////Create the command object
            //cmd = new OleDbCommand(query, conn);
            //da = new OleDbDataAdapter(cmd);
            //DataSet dsdetails = new DataSet();
            //da.Fill(dsdetails, "Details");

            DateTime dtStartdate =Convert.ToDateTime(txtFromDate.Text);
            DateTime dtEndDate = Convert.ToDateTime(txtEndDate.Text);






            DataColumn dColumn = new DataColumn();
            dColumn.DataType = System.Type.GetType("System.String");
            dColumn.ColumnName = "Status";
            ds.Tables[0].Columns.Add(dColumn);


            bool flag = false;
            if (ds.Tables[0].Rows.Count > 0)
            {

                RegistrationDB db = new RegistrationDB();
                DataTable dtAds = db.GetPostedAdstoValidate(ds.Tables[0].Rows[1]["LoftyID"].ToString());
                //excel sheet
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (Convert.ToDateTime(ds.Tables[0].Rows[i]["Date"].ToString()) <= dtEndDate && Convert.ToDateTime(ds.Tables[0].Rows[i]["Date"].ToString()) >= dtStartdate)
                    {
                        //db table
                        for (int z = 0; z < dtAds.Rows.Count; z++)
                        {


                            if (ds.Tables[0].Rows[i]["LoftyAddURL"].ToString() == dtAds.Rows[z]["URL"].ToString())
                            {
                                ds.Tables[0].Rows[i]["Status"] = "Validated!";
                                flag = true;
                                break;
                            }
                            else if (ds.Tables[0].Rows[i]["IPAddress"].ToString() == dtAds.Rows[z]["IPAddress"].ToString())
                            {
                                ds.Tables[0].Rows[i]["IPAddress"] = "Valid IP!";
                                flag = true;
                                break;
                            }

                            flag = false;
                        }
                    }
                    else
                    {
                        flag = true;
                        ds.Tables[0].Rows[i]["Status"] = "Date does not lie between Project start and end date!";
                        ds.Tables[0].Rows[i]["IPAddress"] = "InvalidIP / EmptyIP";

                    }
                    if (!flag)
                        ds.Tables[0].Rows[i]["Status"] = "Not Validated!";
                       //ds.Tables[0].Rows[i]["IPAddress"] = "Invalid IP";

                }

            }

            //ds.Tables[0].Columns.Remove("F5");
            //ds.Tables[0].Columns.Remove("F6");
            //ds.Tables[0].Columns.Remove("F7");
            grvExcelData.DataSource = ds.Tables[0];
            grvExcelData.DataBind();
            //dump to database for logging
            ViewState["MyGridViewDate"]=ds.Tables[0];
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
               RegistrationDB dump = new RegistrationDB();
               string Loftyid = ds.Tables[0].Rows[i]["Loftyid"].ToString();
               string Date = ds.Tables[0].Rows[i]["Date"].ToString();
               string LoftyAddURL = ds.Tables[0].Rows[i]["LoftyAddURL"].ToString();
               string Status = ds.Tables[0].Rows[i]["Status"].ToString();
               string Addno = ds.Tables[0].Rows[i]["Addno"].ToString();
               string IPAddress = ds.Tables[0].Rows[i]["IPAddress"].ToString();
               dump.CreateLogValidation(Loftyid, Date, LoftyAddURL, Status, Addno,IPAddress); 
            }




            lblMessage.Text = "Data retrieved successfully! Total Recodes:" + ds.Tables[0].Rows.Count;
            lblMessage.ForeColor = System.Drawing.Color.Green;
            lblMessage.Visible = true;

            //da.Dispose();
            //conn.Close();
           // conn.Dispose();
        }
        else
        {
            lblMessage.Text = "Please select an excel file first";
            lblMessage.ForeColor = System.Drawing.Color.Red;
            lblMessage.Visible = true;
        }
    }

thanks for help in advance , will be very thankful to all .

Answer 1

Increasing the Maximum Upload Size

The 4MB default is set in machine.config, but you can override it in you web.config. For instance, to expand the upload limit to 20MB, you'd do this:

   <system.web>
   <httpRuntime executionTimeout="240" maxRequestLength="20480" />
   </system.web>

Since the maximum request size limit is there to protect your site, it's best to expand the file-size limit for specific directories rather than your entire application. That's possible since the web.config allows for cascading overrides. You can add a web.config file to your folder which just contains the above, or you can use the tag in your main web.config to achieve the same effect:

   <location path="Upload">
   <system.web>
    <httpRuntime executionTimeout="110" maxRequestLength="20000" />
   </system.web>
  </location>