Import excel with Column having multiple types in ASP.net

Note:

• Setting “IMEX=1” in the connection of the Excel sheet. This can be set in the connection string in case of Excel Source (Excel 2003) and can be set using ‘Extended Properties’ in case of OLE DB Source (Excel 2007). This setting tells that excel sheet is having mixed data types and thus import it as ‘Text’ values.

function getFileExtension(filename) {
    var ext = /^.+\.([^.]+)$/.exec(filename);
    return ext == null ? "" : ext[1];
}
function SelectFile() {
    var objFileName = $(".fileUploadCss").val();
    var extension = getFileExtension(objFileName);
    if (extension == 'XLS' || extension == 'xls' || extension == 'xlsx' || extension == 'XLSX') {
        if (objFileName != "") {
            $(".btnImp").removeAttr('disabled', 'false');
        }
        else {
            $(".btnImp").attr('disabled', 'true');
        }
    }
    else {
        $(".btnImp").attr('disabled', 'true');
        alert("Please select excel file only.");
    }

}
<asp:FileUpload ID="FileUpload1" runat="server" OnChange="SelectFile()" CssClass="fileUploadCss" />
<asp:Button ID="btnImport" runat="server" Text="Import Employees" CssClass="btnImp actWinForSelected-button-import"
                        OnClick="btnImport_Click" Enabled="false" CausesValidation="False" />

protected void btnImport_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
 try
 {
     string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
     string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
     string fileLocation = Server.MapPath("~/App_Data/" + fileName);
     FileUpload1.SaveAs(fileLocation);
     BusinessLayer.ExcelUpload excel = new BusinessLayer.ExcelUpload();
     gvEmployeeDetails.DataSource = excel.ExcelUploadSystem(fileLocation, fileExtension);
     gvEmployeeDetails.DataBind();
     //divGridBox.Visible = true;
    
 }
 catch (Exception ex)
 {
     ShowMessage("error", "Some error occur please try again: ");
     ExceptionLogger.Error("btnImport_Click(): Error occured while processing your request", ex);
     throw ex;
 }
    }
    else
    {
 ShowMessage("error", "Please select a excel file: ");
 ExceptionLogger.Info("btnImport_Click(): Select only excel file");
    }
}
public List<Entities.Employee> ExcelUploadSystem(string fileLocation, string fileExtension)
{
   
   DataAccess.ExcelUpload excelUpload = new DataAccess.ExcelUpload();
   DataTable dt = excelUpload.GetEmployeeList(fileLocation, fileExtension);
   StringBuilder excelData = new StringBuilder();
   string strData = string.Empty;
   if (dt.Rows.Count > 0)
   {
       for (int i = 0; i < dt.Rows.Count; i++)
       {
    excelData.Append("'" + appendLeadingZeros(dt.Rows[i][0].ToString()) + "'" + ",");
       }
       strData = excelData.ToString();
       strData = strData.Remove(strData.Length - 1, 1);             
   }          
   List<Entities.Employee> EmployeeList = GetEmployee(strData);
   return EmployeeList;

}
private string appendLeadingZeros(string BecRef)
{
   switch (BecRef.Trim().Length)
   {
       case 0:
    BecRef = "000000" + BecRef;
    break;
       case 1:
    BecRef = "00000" + BecRef;
    break;
       case 2:
    BecRef = "0000" + BecRef;
    break;
       case 3:
    BecRef = "000" + BecRef;
    break;
       case 4:
    BecRef = "00" + BecRef;
    break;
       case 5:
    BecRef = "0" + BecRef;
    break;
   }
   return BecRef;
}
//Match Excel becref with database
public List<Entities.Employee> GetEmployee(string BecRef)
{
      Entities.Employee data = null;
      List<Entities.Employee> empList = new List<Entities.Employee>();
       DataAccess.Employee emp = new DataAccess.Employee();
       using (IDataReader reader = emp.GetExcelEmployee(BecRef))
       {
    if (reader!=null)
    {
        while (reader.Read())
        {
     data = new Entities.Employee();
     data.BecRef = Convert.ToString(reader["BecRef"]);
     data.EmployeeNumber = appendLeadingZeros(reader["Employee_Number"].ToString());
     data.BunId = Convert.ToString(reader["Employee_Bun_Id"]);
     data.EmployeeName = Convert.ToString(reader["Employee_Name"]);
     //data.Grade = Convert.ToInt16(reader["Grade"]);
     data.BaseOrg = Convert.ToString(reader["Base_Org_Unit"]);
     data.FromData = "EXCEL";
     empList.Add(data);
        }
    }
       }
       return empList;
}
public string CreateConnectionString(string fileLocation, string fileExtension)
{
   string connectionString = string.Empty;
   if (fileExtension == ".xls")
   {
       connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
   }
   else if (fileExtension == ".xlsx")
   {
       connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
   }
   return connectionString;
}
public DataTable GetEmployeeList(string fileLocation, string fileExtension)
{
   OleDbConnection con = new OleDbConnection(CreateConnectionString(fileLocation,fileExtension));
   OleDbCommand cmd = new OleDbCommand();
   cmd.CommandType = System.Data.CommandType.Text;
   cmd.Connection = con;
   OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
   DataTable dtExcelRecords = new DataTable();
   con.Open();
   DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
   string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
   cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
   dAdapter.SelectCommand = cmd;
   dAdapter.Fill(dtExcelRecords);
   con.Close();
   return dtExcelRecords;
}
public IDataReader GetExcelEmployee(string BecRef)
{
    IDataReader reader = null;
    try
    {
 reader = dbHelper.ExecuteReader(GetCmdEmployeeList(BecRef));
    }
    catch
    {
 throw ;
    }
    return reader;
}
private DbCommand GetCmdEmployeeList(string BecRef)
{
    DbCommand cmd = null;
    try
    {
 string SQL_QUERY =
 "select Employee_Bun_Id,Employee_Name,BecRef,Employee_Number,Base_Org_Unit from EmployeeDetails where becRef in(" + BecRef + ") and ACTIVE='Y' And ISNULL(UPPER(EmpType), '') = 'PERMANENT'";
 cmd = dbHelper.CreateCommand(SQL_QUERY);
    }
    catch
    {
 throw ;
    }
    return cmd;
}

Authenticate User using Windows login credentials

 public static bool ValidateW(string UserName, string Password, string DomainName)
        {
            bool valid = false;
            try
            {
               
                if (UserName.IndexOf("\\") != -1)
                {
                    string[] arrT = UserName.Split(SPLIT_1[0]);
                    UserName = arrT[1];
                }
                using (PrincipalContext context = new PrincipalContext(ContextType.Domain, DomainName))
                {
                    valid = context.ValidateCredentials(DomainName+"\\" + UserName, Password);
                   
                }
               
            }
            catch (Exception ex)
            {
                throw ex;               
            }
            return valid;
        }

        bool GetEmployee()
        {
            bool flag = false;
            Entities.Employee data = null;
            string userName = UserName.Text;
            try
            {
                if (userName.IndexOf("@") != -1)
                {
                    string[] arrT = userName.Split(SPLIT_2[0]);
                    userName = arrT[0];
                }
                BusinessLayer.Employee emp = new BusinessLayer.Employee();
                data = emp.GetEmployee(userName);               
                flag = true;
            }
            catch (Exception ex)
            {
               
            }
            return flag;
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (ValidateW(UserName.Text, Password.Text, Domain.SelectedValue))
                {
                    if (GetEmployee())
                    {
                        if (Request.QueryString["ReturnUrl"] != null)
                        {
                            FormsAuthentication.RedirectFromLoginPage(UserName.Text, false);
                        }
                        else
                        {
                            FormsAuthentication.SetAuthCookie(UserName.Text, false);
                        }
                    }
                    else
                    {
                        FailureText.Text = "Your details does not exist in the system. Please contact Administrator.";
                    }
                }
                else
                {
                    FailureText.Text = "Your login attempt was not successful. Please try again.";
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

FormsAuthentication.SignOut() does not log the user out

FormsAuthentication.SignOut();
Session.Abandon();
// clear authentication cookie
HttpCookie cookie1 = new HttpCookie(FormsAuthentication.FormsCookieName, "");
cookie1.Expires = DateTime.Now.AddYears(-1);
Response.Cookies.Add(cookie1);
// clear session cookie (not necessary for your current problem but i would recommend you do it anyway)
HttpCookie cookie2 = new HttpCookie("ASP.NET_SessionId", "");
cookie2.Expires = DateTime.Now.AddYears(-1);
Response.Cookies.Add(cookie2);
FormsAuthentication.RedirectToLoginPage();

Insert bulk data in SQL Server using XML in ASP.Net

protected void InsertFlexyDetails(string BecRefID)
        {
            int errRet = 0;
            try
            {
                IList<Entities.EmployeeCompensation> lstEmployeeCompensation = new List<Entities.EmployeeCompensation>();
               
               
                string strXML = string.Empty;
                objEmpComp = new BusinessLayer.EmployeeCompensation();
               
                foreach (DataGridItem dgItem in grdComponent.Items)
                {
                    if (dgItem.ItemType == ListItemType.Item || dgItem.ItemType == ListItemType.AlternatingItem)
                    {
                        DropDownList ddlFlexyAmount = (DropDownList)dgItem.FindControl("ddlFlexyAmount");
                        TextBox txtFlexyAmount = (TextBox)dgItem.FindControl("txtFlexyAmount");
                        Label lblFcID = (Label)dgItem.FindControl("lblFcID");
                        entEmpComp = new Entities.EmployeeCompensation();
                        entEmpComp.Id = Convert.ToInt32(lblFcID.Text);                       
                        if (ddlFlexyAmount.Visible == true)
                        {
                            entEmpComp.Amount = Convert.ToInt32(ddlFlexyAmount.SelectedValue);
                        }
                        else if (txtFlexyAmount.Visible == true)
                        {
                            entEmpComp.Amount = Convert.ToInt32(txtFlexyAmount.Text);
                        }
                        lstEmployeeCompensation.Add(entEmpComp);
                    }
                }
                errRet = objEmpComp.InsertCompensation(lstEmployeeCompensation);  
            }
            catch
            {
                throw;
            }
        }
public int InsertCompensation(IList<Entities.EmployeeCompensation> lstEmployeeCompensation)
        {
            int retVal = 0;
            string strFlexiAmntXML = string.Empty;
            try
            {
                if (lstEmployeeCompensation.Count > 0)
                {
                    strFlexiAmntXML = "<FlexiComponentDetails>";
                    for (int i = 0; i <= lstEmployeeCompensation.Count - 1; i++)
                    {
                        strFlexiAmntXML += "<FlexiComponent>";
                        strFlexiAmntXML += "<FC_ID>" + lstEmployeeCompensation[i].Id + "</FC_ID>";
                        strFlexiAmntXML += "<EC_Amount>" + lstEmployeeCompensation[i].Amount + "</EC_Amount>";
                        strFlexiAmntXML += "</FlexiComponent>";
                    }
                    strFlexiAmntXML += "</FlexiComponentDetails>";
                }
                DataAccess.EmployeeCompensation empCompensation = new DataAccess.EmployeeCompensation();
                retVal = empCompensation.InsertCompensation(strFlexiAmntXML);
            }
            catch (Exception ex)
            {
            }
            return retVal;
        }
public int InsertCompensation(string strFlexiAmntXML)
        {
            int errRet = 0;
            try
            {
                DbCommand cmd = dbHelper.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_Insert_FlexiComponents";
               
                DbParameterBuilder.Add(cmd, "InputXML", DbType.String, ParameterDirection.Input, strFlexiAmntXML);
                errRet = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                errRet = 1;
            }
            return errRet;
        }
CREATE PROCEDURE [dbo].[sp_Insert_FlexiComponents]
 -- Add the parameters for the stored procedure here
 @InputXML AS VARCHAR(MAX)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    -- Insert statements for procedure here
 
  DECLARE @XML AS XML
       
        DECLARE @AmountTable TABLE
        (
            ID INT IDENTITY(1,1),
            EC_FC_ID int,
            EC_Amount int
        )
       
        SELECT @XML = @InputXML
       
        INSERT INTO @AmountTable (EC_FC_ID, EC_Amount)
        SELECT M.Item.query('./FC_ID').value('.','INT') EC_FC_ID,
               M.Item.query('./EC_Amount').value('.','INT') EC_Amount
        FROM @XML.nodes('/FlexiComponentDetails/FlexiComponent') AS M(Item)
END

Eanble Disable control on checkbox click

<script type="text/javascript">
        function EnableControl(val) {
            var sbmt = document.getElementById("<%=btnSubmit.ClientID %>");
            if (val.checked == true) {
                sbmt.disabled = false;
            }
            else {
                sbmt.disabled = true;
            }
        }
       
       
    </script>
<div style="float: left;">
            <asp:CheckBox ID="chkAgree" runat="server" Text="I Agree" onclick="EnableControl(this)"
                Checked="false" />&nbsp;&nbsp;&nbsp;
            <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" /></div>

Show/hide control if a certain selection is made in previous drop down

<script type="text/javascript">
   
    function function1(colors) {
        var col = (colors.options[colors.selectedIndex].value);
        if (col == 0 || col == 2) {
            document.getElementById("<%=divStatus.ClientID %>").style.display = "none";
        }
        else {
            document.getElementById("<%=divStatus.ClientID %>").style.display = "block";
        }
        //alert("SelectedIndex value = " + col);
    }
</script>
<asp:DropDownList ID="ddlReport" runat="server" Width="164px" onchange="function1(this);" >
                    <asp:ListItem Text="--Select--" Selected="True" Value="0" />
                    <asp:ListItem Text="Allocation Status" Value="1" />
                    <asp:ListItem Text="Compensation Structure" Value="2" />
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfvReport" runat="server" ControlToValidate="ddlReport"
                    InitialValue="0" ErrorMessage="*"></asp:RequiredFieldValidator>
<div id="divStatus" runat="server"  style="margin-top: 7em;display:none"></div>

Save Customer Product details with subreport in .pdf on button click

 protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable dtEmployeeDetail = report.GetEmployeeDetailCompensation(2, 4, "a");
                if (dtEmployeeDetail.Rows.Count > 0)
                {
                    strSubmitDate = dtEmployeeDetail.Rows[0]["EAP_ClosedOn"].ToString();
                    ReportDataSource datasource = new ReportDataSource("DataSet1", dtEmployeeDetail);
                    ReportViewer ReportViewer1 = new ReportViewer();
                    ReportViewer1.Reset();
                    ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/EmpReports/rptEmployeeCompensationDetails.rdlc");
                    ReportViewer1.LocalReport.EnableExternalImages = true;
                    ReportViewer1.LocalReport.DataSources.Clear();
                    ReportViewer1.LocalReport.DataSources.Add(datasource);
                    ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(LocalReport_SubreportProcessing);
                    ReportViewer1.LocalReport.Refresh();
                    string fileName = "EmpCompensationDetails_" + Convert.ToDateTime(DateTime.Now).ToString("MMddyyyyhhmm") + ".pdf";
                    //string fileName = Server.MapPath("~/EmpReports/TempReportOutputs/EmpCompensationDetails_") + Convert.ToDateTime(DateTime.Now).ToString("MMddyyyyhhmm") + ".pdf";
                   // bool success = ByteArrayToFile(fileName, GetExportedBytes(ReportViewer1.LocalReport, ReportFormat.PDF));
                    //if (success)
                    //{
                        try
                        {
                            SendMail(fileName, GetExportedBytes(ReportViewer1.LocalReport,ReportFormat.PDF));
                           
                        }
                        catch (Exception ex)
                        {
                            System.IO.File.Delete(fileName);
                        }
                   // }
                }
            }
            catch (Exception ex)
            {
            }
        }
 void LocalReport_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
        {
          
                DataTable dtEmployeeCompensation = report.GetEmployeeCompensationStructure(2,4, "a");
                if (dtEmployeeCompensation.Rows.Count > 0)
                {
                    // string strParameter = e.Parameters["BecRef"].Values[0].ToString();
                    ReportDataSource rdS = new ReportDataSource("DataSet1", dtEmployeeCompensation);
                    e.DataSources.Add(rdS);
                }
        }
private void SendMail(string fileName, byte[] _ByteArray)
        {
            System.IO.MemoryStream stream = new System.IO.MemoryStream(_ByteArray);
            System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(stream, fileName);
                   
            //BusinessLayer.Mail mail = new BusinessLayer.Mail();
            //mail.EmailSend("from", "to", "bcc", "subject", CreateEmailBody(), new System.Net.Mail.Attachment(fileName));
           
        }
public static byte[] GetExportedBytes(LocalReport lr, ReportFormat fmt)
        {
            byte[] toReturn = null;
            Warning[] warnings;
            string[] streamids;
            string mimeType;
            string encoding;
            string filenameExtension;
            toReturn = lr.Render(fmt.ToString(), null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);
            return toReturn;
        }
public enum ReportFormat
        {
            PDF,
            Word,
            Excel,
            Image
        }
//Save bytes in a File
public bool ByteArrayToFile(string _FileName, byte[] _ByteArray)
        //{
        //    try
        //    {
        //        // Open file for reading
        //        System.IO.FileStream _FileStream = new System.IO.FileStream(_FileName, System.IO.FileMode.Create, System.IO.FileAccess.Write);
        //        // Writes a block of bytes to this stream using data from a byte array.
        //        _FileStream.Write(_ByteArray, 0, _ByteArray.Length);
        //        // close file stream .
        //        _FileStream.Close();
        //        return true;
        //    }
        //    catch (Exception _Exception)
        //    {
        //        // Error            
        //        //lblMsg.Text = string.Format("Exception caught in process: {0}", _Exception.ToString());
        //    }
        //    return false;
        //}