This is web form code
public partial class UploadEmployee : System.Web.UI.Page
{
#region Object and Variable Declaration
Cls_UtilityPrograms clsObj = new Cls_UtilityPrograms();
Cls_EIS_Business_Update clsbupd = new Cls_EIS_Business_Update();
FileUpload FlUpd;
DataTable dt_exception;
DataColumn dc;
string str;
string filename,msg;
#endregion
protected void Page_Load(object sender, EventArgs e)
{
((PMSMasterInner)Page.Master).RegisterPostbackTrigger(BtnOpen);
if (!Page.IsPostBack)
{
}
LblMsg.Text = "";
}
protected void BtnOpen_Click(object sender, EventArgs e)
{
msg = "";
FlUpd = (FileUpload)Master.FindControl("ContentSection1").FindControl("Upload");
if (FlUpd.HasFile == true)
{
str = FlUpd.PostedFile.FileName.ToString();
filename = FlUpd.FileName.ToString();
dt_exception = new DataTable();
dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = "Employee Code";
dt_exception.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = "Error";
dt_exception.Columns.Add(dc);
clsbupd.UploadData(str, filename, Session[Cls_Constant.CONST_EMPLOYEE_CODE].ToString(), ref msg, ref dt_exception);
LblMsg.Text = msg;
if (dt_exception.Rows.Count > 0)
{
GdErrors.DataSource = dt_exception;
GdErrors.DataBind();
}
}
else
{
LblMsg.Text = "Please upload Template";
}
}
}
This is code for uploading data in database ..this method is calling on above code to insert the data...
public void UploadData(string str, string filename, string session_ecode, ref string msg, ref DataTable dt_exception)
{
dt = new DataTable();
string name = System.IO.Path.GetExtension(str);
table_ref = GetTableReference(filename);
if (table_ref == 0)
{
msg = "Invalid File";
return;
}
columnName = "";
columnValue = "";
if (name == Cls_Const.CONS_EXCELFILE1 name == Cls_Const.CONS_EXCELFILE2)
{
dt = clsup.GetTableExcel(str);
count_column = dt.Columns.Count;
count_row = dt.Rows.Count;
if (count_row == 0)
{
msg = "No Data in excel file";
return;
}
if (table_ref != -1)
{
for (int i = 0; i < columnname ="=" columnname =" dt.Columns[i].ToString();" columnname =" columnName" i =" 0;" empcode =" dt.Rows[i][0].ToString();" j =" 0;" columnvalue ="=" columnvalue =" Cls_Const.CONS_NULLSTRING;" columnvalue = "'" columnvalue =" columnValue" columnvalue =" columnValue" table_ref ="=" dt1 =" new" dt1 =" clsup.GetEmpCode(empcode);"> 0)
{
ecodee = dt1.Rows[0][Cls_Const.CONS_EMPCODE].ToString();
}
else
{
ecodee = "";
}
if (empcode == ecodee)
{
ecodee = "'" + empcode + "'";
clsup.InsertData(table_ref, columnName, columnValue, ecodee, Cls_Const.CONS_EMPCODE);
}
else
{
clsup.InsertData(table_ref, columnName, columnValue, Cls_Const.CONS_NULLSTRING, Cls_Const.CONS_NULLSTRING);
try
{ //Login Creation
ColumnValue = "'" + empcode.Trim() + "','" + empcode.Trim() + "','" + obj.base64Encode(Cls_Const.CONS_PWD).ToString() + "','" + DateTime.Now.ToString() + "','" + session_ecode + "'";
clsup.InsertData(48, Cls_Const.CONS_COLUMN_NAME, ColumnValue, Cls_Const.CONS_NULL, Cls_Const.CONS_NULL);
}
catch (Exception)
{
msg = Cls_Const.CONS_LOGIN_ERRORMSG;
}
}
}
else if (table_ref == -1)
{
int Bvid, Cmpid, desgid, subunitid, deptid, processid;
for (int j = 0; j < empcode =" dt.Rows[j][0].ToString();" bvid =" Convert.ToInt32(dt.Rows[j][1].ToString());" cmpid =" Convert.ToInt32(dt.Rows[j][2].ToString());" desgid =" Convert.ToInt32(dt.Rows[j][6].ToString());" subunitid =" Convert.ToInt32(dt.Rows[j][3].ToString());" processid =" Convert.ToInt32(dt.Rows[j][5].ToString());" deptid =" Convert.ToInt32(dt.Rows[j][4].ToString());" ec = "'" dt1 =" new" dt1 =" clsup.GetEmpCode(empcode);"> 0)
{
ecodee = dt1.Rows[0][Cls_Const.CONS_EMPCODE].ToString();
}
else
{
ecodee = "";
}
if (empcode == ecodee)
{
DataTable dt2 = new DataTable();
dt2=clsDalUtilPrg.GetDataTable_DAL(table_ref, 0, empcode, null, Cls_Const.CONS_EMPCODE);
string ecode1;
if (dt2.Rows.Count > 0)
{
ecode1 = dt2.Rows[0][Cls_Const.CONS_EMPCODE].ToString();
}
else
{
ecode1 = "";
}
if (empcode == ecode1)
{
ecodee = "'" + empcode + "'";
clsup.InsertData(table_ref, columnName, columnValue, ecodee, Cls_Const.CONS_EMPCODE);
}
else
{
clsup.InsertData(table_ref, columnName, columnValue, Cls_Const.CONS_NULLSTRING, Cls_Const.CONS_NULLSTRING);
}
}
}
}
catch (Exception ex)
{
dr = dt_exception.NewRow();
dr["Employee Code"] = empcode;
//dr["Employee Name"]=dt.Rows[i][2].ToString();
if (ex.Message.ToString() == "Conversion failed when converting the varchar value 'NULL' to data type int.")
error = "Invalid data in excel file";
else if (ex.Message.ToString() == "There is no row at position 0.")
error = "Employee does not exist";
else
error = ex.Message.ToString();
dr["Error"] = error;
dt_exception.Rows.Add(dr);
}
finally
{
columnValue = "";
}
}
if (dt_exception.Rows.Count == 0)
{
msg = "Data uploaded successfully";
}
else
{
msg = "Data uploaded excluding following rows";
}
}
else
{
msg = "Please upload excel files only";
}
}
protected int GetTableReference(string excelfilename)
{
switch (excelfilename)
{
case "Primary_Information.xls": table_ref = 5; break;
case "Primary_Information.xlsx": table_ref = 5; break;
case "Employee_Information.xls": table_ref = -1; break;
case "Employee_Information.xlsx": table_ref = -1; break;
case "Qualification.xls": table_ref = 41; break;
case "Qualification.xlsx": table_ref = 41; break;
case "Bank_Detail.xls": table_ref = 11; break;
case "Bank_Detail.xlsx": table_ref = 11; break;
case "Experience.xls": table_ref = 42; break;
case "Experience.xlsx": table_ref = 42; break;
case "Assets.xls": table_ref = 43; break;
case "Assets.xlsx": table_ref = 43; break;
default: table_ref = 0; break;
}
return table_ref;
}
