protected void btn_upload_Click(object sender, EventArgs e)
{
if (ViewState["CSRF"] != null && Convert.ToString(ViewState["CSRF"]) == Convert.ToString(Session["randomno"]))
{
DataTable dtExcelData = new DataTable();
if (FileUpload1.HasFile)
{
//Upload and save the file
try
{
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();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[] {
new DataColumn("Training_partner_id",typeof(string)),
new DataColumn("Training_Partner_Name",typeof(string)),
new DataColumn("Course_Name",typeof(string)),
new DataColumn("Training_Partner_State",typeof(string)),
new DataColumn("KIA_Name",typeof(string)),
new DataColumn("Batch_Code",typeof(string)),
new DataColumn("Salutation",typeof(string)),
new DataColumn("StudentFirstName",typeof(string)),
new DataColumn("StudentMiddleName",typeof(string)),
new DataColumn("StudentLastName",typeof(string)),
new DataColumn("Father_Name",typeof(string)),
new DataColumn("Mother_Name",typeof(string)),
new DataColumn("Guardian_Name",typeof(string)),
new DataColumn("Gender",typeof(string)),
new DataColumn("DOB",typeof(string)),
new DataColumn("Marital_Status",typeof(string)),
new DataColumn("catogory",typeof(string)),
new DataColumn("Phone_No",typeof(string)),
new DataColumn("MobileNo",typeof(string)),
new DataColumn("Email_id",typeof(string)),
new DataColumn("Adharcard_No",typeof(string)),
new DataColumn("Permanent_Address_Line",typeof(string)),
new DataColumn("Country",typeof(string)),
new DataColumn("Permanent_state_Name",typeof(string)),
new DataColumn("Permanent_district_Name",typeof(string)),
new DataColumn("Permanent_City",typeof(string)),
new DataColumn("Permanent_Landmark",typeof(string)),
new DataColumn("Permanent_Pincode",typeof(string)),
new DataColumn("Present_Address_Line",typeof(string)),
new DataColumn("Present_Country",typeof(string)),
new DataColumn("Present_State_Name",typeof(string)),
new DataColumn("Present_district_id",typeof(string)),
new DataColumn("Present_City",typeof(string)),
new DataColumn("Present_Landmark",typeof(string)),
new DataColumn("Present_Pincode",typeof(string)),
new DataColumn("Bank_Name",typeof(string)),
new DataColumn("Branch_Name",typeof(string)),
new DataColumn("Benificiary_Name",typeof(string)),
new DataColumn("Beneficiary_Code",typeof(string)),
new DataColumn("Account_number",typeof(string)),
new DataColumn("Ifsc_Code",typeof(string)),
new DataColumn("MICR_Code",typeof(string)),
new DataColumn("Bank_Address",typeof(string)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
int existingsize = FetchSize(drp_batch.SelectedIndex > 0 ? drp_batch.SelectedItem.Text : null);
int Studentinexcel = dtExcelData.Rows.Count;
if (Studentinexcel > existingsize)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('Excess Student uploaded according to max batch size.....');", true);
}
else
{
string consString = Luminious.Connection.Configuration.ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
for (int i = 0; i < dtExcelData.Rows.Count; i++)
{
dtExcelData.Rows[i]["Training_partner_id"] = Session["trainingpartnercode"] != null ? Convert.ToString(Session["trainingpartnercode"]) : null;
dtExcelData.Rows[i]["Training_Partner_Name"] = Session["TP_Name"] != null ? Convert.ToString(Session["TP_Name"]) : null;
dtExcelData.Rows[i]["Course_Name"] = drp_course.SelectedIndex > 0 ? drp_course.SelectedItem.Text : null;
dtExcelData.Rows[i]["Training_Partner_State"] = Session["TP_State"] != null ? Convert.ToString(Session["TP_State"]) : null;
dtExcelData.Rows[i]["KIA_Name"] = drp_KIA.SelectedIndex > 0 ? drp_KIA.SelectedItem.Text : null;
dtExcelData.Rows[i]["Batch_Code"] = drp_batch.SelectedIndex > 0 ? drp_batch.SelectedItem.Text : null;
}
//Set the database table name
sqlBulkCopy.DestinationTableName = "Student_Registraion_Temp";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Training_partner_id", "Training_partner_id");
sqlBulkCopy.ColumnMappings.Add("Training_Partner_Name", "Training_Partner_Name");
sqlBulkCopy.ColumnMappings.Add("Course_Name", "Course_Name");
sqlBulkCopy.ColumnMappings.Add("Training_Partner_State", "Training_Partner_State");
sqlBulkCopy.ColumnMappings.Add("KIA_Name", "KIA_Name");
sqlBulkCopy.ColumnMappings.Add("Batch_Code", "Batch_Code");
sqlBulkCopy.ColumnMappings.Add("Salutation", "Salutation");
sqlBulkCopy.ColumnMappings.Add("StudentFirstName", "StudentFirstName");
sqlBulkCopy.ColumnMappings.Add("StudentMiddleName", "StudentMiddleName");
sqlBulkCopy.ColumnMappings.Add("StudentLastName", "StudentLastName");
sqlBulkCopy.ColumnMappings.Add("Father_Name", "Father_Name");
sqlBulkCopy.ColumnMappings.Add("Mother_Name", "Mother_Name");
sqlBulkCopy.ColumnMappings.Add("Guardian_Name", "Guardian_Name");
sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
sqlBulkCopy.ColumnMappings.Add("DOB", "DOB");
sqlBulkCopy.ColumnMappings.Add("Marital_Status", "Marital_Status");
sqlBulkCopy.ColumnMappings.Add("catogory", "catogory");
sqlBulkCopy.ColumnMappings.Add("Phone_No", "Phone_No");
sqlBulkCopy.ColumnMappings.Add("MobileNo", "MobileNo");
sqlBulkCopy.ColumnMappings.Add("Email_id", "Email_id");
sqlBulkCopy.ColumnMappings.Add("Adharcard_No", "Adharcard_No");
sqlBulkCopy.ColumnMappings.Add("Permanent_Address_Line", "Permanent_Address_Line");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
sqlBulkCopy.ColumnMappings.Add("Permanent_state_Name", "Permanent_state_Name");
sqlBulkCopy.ColumnMappings.Add("Permanent_district_Name", "Permanent_district_Name");
sqlBulkCopy.ColumnMappings.Add("Permanent_City", "Permanent_City");
sqlBulkCopy.ColumnMappings.Add("Permanent_Landmark", "Permanent_Landmark");
sqlBulkCopy.ColumnMappings.Add("Permanent_Pincode", "Permanent_Pincode");
sqlBulkCopy.ColumnMappings.Add("Present_Address_Line", "Present_Address_Line");
sqlBulkCopy.ColumnMappings.Add("Present_Country", "Present_Country");
sqlBulkCopy.ColumnMappings.Add("Present_State_Name", "Present_State_Name");
//sqlBulkCopy.ColumnMappings.Add("Present_district_Name", "Present_district_Name");
sqlBulkCopy.ColumnMappings.Add("Present_City", "Present_City");
sqlBulkCopy.ColumnMappings.Add("Present_Landmark", "Present_Landmark");
sqlBulkCopy.ColumnMappings.Add("Present_Pincode", "Present_Pincode");
sqlBulkCopy.ColumnMappings.Add("Bank_Name", "Bank_Name");
sqlBulkCopy.ColumnMappings.Add("Branch_Name", "Branch_Name");
sqlBulkCopy.ColumnMappings.Add("Benificiary_Name", "Benificiary_Name");
sqlBulkCopy.ColumnMappings.Add("Beneficiary_Code", "Beneficiary_Code");
sqlBulkCopy.ColumnMappings.Add("Account_number", "Account_number");
sqlBulkCopy.ColumnMappings.Add("Ifsc_Code", "Ifsc_Code");
sqlBulkCopy.ColumnMappings.Add("MICR_Code", "MICR_Code");
sqlBulkCopy.ColumnMappings.Add("Bank_Address", "Bank_Address");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
if (dtExcelData != null)
{
grd.DataSource = dtExcelData;
grd.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
ExceptionHandler.WriteException(ex.Message);
lbl_message.ForeColor = System.Drawing.Color.Red;
lbl_message.Text = ex.Message;
}
finally
{
CrossSiteRequestForgery();
CreateRandomHashing();
if (dtExcelData != null)
{
dtExcelData.Dispose();
}
}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('Upload File ');", true);
}
}
}
string rno = null;
protected void CrossSiteRequestForgery()
{
//Code start for Cross Site Request Forgery
Random randomobj = new Random();
Session["randomno"] = randomobj.Next();
ViewState["CSRF"] = Session["randomno"].ToString();
//End Code
}
private void CreateRandomHashing()
{
Random rd = new Random();
Session["rnumb"] = rd.Next();
rno = Convert.ToString(Session["rnumb"]);
}
No comments:
Post a Comment