I'm making a simple windows form site where a file can be uploaded and then displays in a gridview that can be reviewed and then the file can be inserted into a database table. This is something new I haven't tried before. I was originally trying to use a gridview to insert the file and it was fast to display but I could only insert with paging on and then it would only insert the first 10 displayed. I tried to turn off paging for the insert but I'd just get this on insert: HTTP Error 404.13 - Not Found The request filtering module is configured to deny a request that exceeds the request content length
The excel file is about 60,000 records. I tried switching to a datatable for doing the insert and everything has just slowed down. I added this to the web config but still no luck. I tried a file with just 13 records and while it loaded into the table it still seemed to be slow to initially load and it populates more pages than data. Ideally what would be 2 pages in the gridview continues to display 10+ blank pages past the data. So I'm wondering if the slow down is involved in that. Below is my code. The original way of using the gridview is commented out. Is there anything in here I'm doing wrong or could improve upon? Any help would be appreciated. Thanks!
protected void LoadFile(object sender, EventArgs e)
{
if(RehireFormUpload.HasFile)
{
string FileName = Path.GetFileName(RehireFormUpload.PostedFile.FileName);
string Extension = Path.GetExtension(RehireFormUpload.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
RehireFormUpload.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension, "Yes");
mainPanel.Update();
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03Constring"].ConnectionString;
break;
case ".xlsx": //Excel 07 and on
conStr = ConfigurationManager.ConnectionStrings["Excel07Constring"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of the first sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read the data from first sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//dt.Rows.Remove(dt.Rows[0]);
//dt.Rows.Remove(dt.Rows[0]);
DataTable dtCloned = dt.Clone();
dtCloned.Columns[0].DataType = typeof(String);
foreach (DataRow row in dt.Rows)
{
dtCloned.ImportRow(row);
}
//Bind Data to gridview
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dtCloned;
ViewState["DataTable"] = dtCloned;
GridView1.DataBind();
}
protected void pageIndexChanging(object sender, GridViewPageEventArgs e)
{
string folderPath = ConfigurationManager.AppSettings["FolderPath"];
string fileName = GridView1.Caption;
string Extension = Path.GetExtension(fileName);
string FilePath = Server.MapPath(folderPath + fileName);
Import_To_Grid(FilePath, Extension, "Yes");
GridView1.PageIndex = e.NewPageIndex;
DataTable dt = (DataTable)ViewState["DataTable"];
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void Submit(object sender, EventArgs e)
{
string employeeID;
string firstName;
string lastName;
string termCode;
string dateofBirth;
string eligibleForRehire;
string ssn;
string terminationDate;
SqlConnection conn1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["InternalDB"].ConnectionString);
//BindingSource bs = (BindingSource)GridView1.DataSource;
//DataTable bdt = (DataTable)bs.DataSource;
DataTable thisData = (DataTable)ViewState["DataTable"];
foreach (DataRow row in thisData.Rows)
{
employeeID = row[0].ToString(); //Convert.ToInt32(row.Cells[0].ToString());
if (employeeID != " " && employeeID != "" && !SearchRecord(employeeID))
{
firstName = row[1].ToString();
lastName = row[2].ToString();
termCode = row[3].ToString();
dateofBirth = row[4].ToString().Substring(0, 8);
eligibleForRehire = row[5].ToString();
ssn = row[6].ToString();
terminationDate = row[7].ToString();
InsertFile(employeeID, firstName, lastName, termCode, dateofBirth, eligibleForRehire, ssn, terminationDate);
}
else if (employeeID != " " && employeeID != "" && SearchRecord(employeeID))
{
firstName = row[1].ToString();
lastName = row[2].ToString();
termCode = row[3].ToString();
dateofBirth = row[4].ToString().Substring(0, 8);
eligibleForRehire = row[5].ToString();
ssn = row[6].ToString();
terminationDate = row[7].ToString();
UpdateRecord(employeeID, firstName, lastName, termCode, dateofBirth, eligibleForRehire, terminationDate);
}
else
{
//Create Message Upload done
//MessageBox.Show("End of Records");
}
}
//foreach (GridViewRow row in GridView1.Rows)
//{
// employeeID = row.Cells[0].Text.ToString(); //Convert.ToInt32(row.Cells[0].ToString());
// if (employeeID != " " && !SearchRecord(employeeID))
// {
// firstName = row.Cells[1].Text.ToString();
// lastName = row.Cells[2].Text.ToString();
// termCode = row.Cells[3].Text.ToString();
// dateofBirth = row.Cells[4].Text.Substring(0, 8).ToString();
// eligibleForRehire = row.Cells[5].Text.ToString();
// ssn = row.Cells[6].Text.ToString();
// terminationDate = row.Cells[7].Text.ToString();
// InsertFile(employeeID, firstName, lastName, termCode, dateofBirth, eligibleForRehire, ssn, terminationDate);
// }
// else if (employeeID != " " && SearchRecord(employeeID))
// {
// firstName = row.Cells[1].Text.ToString();
// lastName = row.Cells[2].Text.ToString();
// termCode = row.Cells[3].Text.ToString();
// dateofBirth = row.Cells[4].Text.Substring(0, 8).ToString();
// eligibleForRehire = row.Cells[5].Text.ToString();
// ssn = row.Cells[6].Text.ToString();
// terminationDate = row.Cells[7].Text.ToString();
// UpdateRecord(employeeID, firstName, lastName, termCode, dateofBirth, eligibleForRehire, terminationDate);
// }
// else
// {
// //Empty Column
// }
//}
MessageBox.Show("Record Insert Complete");
}
private void InsertFile(string employeeID, string firstName, string lastName, string termCode, string dateofBirth, string eligibleForRehire, string ssn, string terminationDate)
{
SqlConnection conn1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["InternalDB"].ConnectionString);
DateTime InsertedDate = DateTime.Now;
conn1.Open();
String query = "INSERT INTO [EMPLOYEE_REHIRES](EmployeeID, FirstName, LastName, TermCode, DateofBirth, EligibleforRehire, SSN, TerminationDate, INSERTED_DATE) VALUES ('" + employeeID + "','" + firstName + "', '" + lastName + "', '" + termCode + "', '" + dateofBirth + "', '" + eligibleForRehire + "' , '" + ssn + "' , '" + terminationDate + "', '" + InsertedDate + "');";
SqlCommand newCommand = new SqlCommand(query, conn1);
newCommand.ExecuteNonQuery();
conn1.Close();
}
private Boolean SearchRecord(string employeeID)
{
SqlConnection conn1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["InternalDB"].ConnectionString);
Boolean employeeInserted = true;
conn1.Open();
SqlCommand newCommand2 = new SqlCommand();
String isEmployeeInserted = "Select * FROM [EMPLOYEE_REHIRES] with (nolock) WHERE EmployeeID=" + employeeID;
newCommand2 = new SqlCommand(isEmployeeInserted, conn1);
SqlDataReader reader1 = newCommand2.ExecuteReader();
string[] employees = new string[1];
while (reader1.Read())
{
employees[0] = reader1[0].ToString();
}
conn1.Close();
if (employees[0] == null)
{
employeeInserted = false;
}
return employeeInserted;
}
private void UpdateRecord(string employeeID, string firstName, string lastName, string termCode, string dateofBirth, string eligibleForRehire, string terminationDate)
{
SqlConnection conn1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["InternalDB"].ConnectionString);
conn1.Open();
DateTime lastUpdatedDate = DateTime.Now;
String query = "UPDATE [EMPLOYEE_REHIRES] SET FirstName='" + firstName + "', LastName='" + lastName + "', TermCode='" + termCode + "', DateofBirth='" + dateofBirth + "', EligibleforRehire='" + eligibleForRehire + "', TerminationDate='" + terminationDate + "', LAST_UPDATED_DATE='" + lastUpdatedDate + "' WHERE EmployeeID='" + employeeID + "';";
SqlCommand newCommand = new SqlCommand(query, conn1);
newCommand.ExecuteNonQuery();
conn1.Close();
}
Aucun commentaire:
Enregistrer un commentaire