I have an error which i 'm trying to sort out in different methods but getting the same error.
Before Asking this question i searched through all forumns and discussions and finally writing my error.
Functionality:
After Uploading a excel csv File into the server using asp c# application, the records in the excel file are to be read and inserted into the database(sql server) into a particular table.So There is no issue with the uploading and inserting.But the issue is,
Error:
After Uploading the file, its throwing an error like 'Unable to serve this Request-->Site Temporarly Unavailable-->Connection Timed Out Please Try again'
And Meanwhile the records are been inserting into db as expected.
I have Changed the web.Config as
Connect Timeout=216000;Pooling=true;Min Pool Size=5;Max Pool Size=5000"
private void Page_Init(object sender, System.EventArgs e)
{
timeOut = Server.ScriptTimeout;
// Give it 1 hour = 3600 seconds
Server.ScriptTimeout = 3600;
}
private void Page_Unload(object sender, System.EventArgs e)
{
Server.ScriptTimeout = timeOut;
}
But After Changing All These in my application I am Getting the same page,
Please Anyone Tell me How to Solve this issue.
naresh venkatPosted Apr 10, 2013, 12:21 AM
string strRequestString = "url of the file";
WebRequest req = WebRequest.Create(strRequestString);
req.Timeout = 999999999;
WebResponse resp = req.GetResponse();
objFileInfo = new FileInfo();
string strDocument = fleUpload.PostedFile.FileName;
strDocument = DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + strDocument.Substring(strDocument.LastIndexOf("\\") + 1);
string strAbsolutePathOfFile = Server.MapPath("DomainFile/" + strDocument);
string strPathofFileCsv = Server.MapPath("DomainFile/");
string ext = Path.GetExtension(strDocument);
if(ext == ".csv")
{
string sCSVConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPathofFileCsv + @";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";
using (OleDbConnection connection = new OleDbConnection(sCSVConnectionString))
{
connection.ConnectionString = sCSVConnectionString;
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM [" + strDocument + "]";
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
command.CommandTimeout = 0;
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.BulkCopyTimeout = 0;
sqlBulk.BatchSize = 5000;
sqlBulk.DestinationTableName = "tablename";
sqlBulk.WriteToServer(dr);
}
dr.Close();
}
}
connection.Close();
}
}
And One More Thing, My Minimum excel File Size is 60mb and minimum of 70k records
Posted Apr 9, 2013, 1:10 PM
Read one record from excel and insert into db? If so, don't do that.
Read the records from excel ,using in memory structure like datatable or custom object / list. Once you're completed read the data, use the in memory structure to insert the records into database. Use data adapter for bulk insert.
Try the following steps to identify the cause.
1) Read the excel file.
2) Create a data table and fill it from excel file.
3) Display last top 10 records in a grid to make sure, all the records imported from excel.
4) Now insert this datatable into your sql server.
If possible, please post code here.