Now i want to insert "Date_Load" on my table once the transaction was completed.
Condition:
On Excel Column :
1.Id
2.Name
3.Salary
On Table tblPersons :
1.PersonId
2.Name
3.Salary
4.BankName
protected void Submit_Click1(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[7] {new DataColumn("TranDate", typeof(DateTime)),
new DataColumn("ValueDate",typeof(DateTime)),
new DataColumn("ChekNo", typeof(string)),
new DataColumn("Remark", typeof(string)),
new DataColumn("Amount", typeof(decimal)),
new DataColumn("Type", typeof(string)),
new DataColumn("BranchName", typeof(string)),});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tbl_BankTransaction";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("BankName", ddlCategory.Text).ToString();
sqlBulkCopy.ColumnMappings.Add("Tran Date", "TranDate");
sqlBulkCopy.ColumnMappings.Add("Value Date", "ValueDate");
sqlBulkCopy.ColumnMappings.Add("CHQNO", "ChekNo");
sqlBulkCopy.ColumnMappings.Add("Transaction Particulars", "Remark");
sqlBulkCopy.ColumnMappings.Add("Amount", "Amount");
sqlBulkCopy.ColumnMappings.Add("DR|CR", "Type");
sqlBulkCopy.ColumnMappings.Add("Branch Name", "BranchName");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
Label1.Visible = false;
Label1.Text = " DETAILS ARE UPDATED SUCCESSFULLY";
}
}
}
}