Bulk Import from Excel file to SQl Server in ASP.NET

<asp:FileUpload ID="FileUpload1" runat="server" Width="250px" Height="25px" /><br />
<asp:RequiredFieldValidator ID="rfv" runat="server" ControlToValidate="FileUpload1"
ErrorMessage="Please select file to upload" Display="Dynamic" ValidationGroup="vgInsert" SetFocusOnError="True"></asp:RequiredFieldValidator>
<asp:Button ID="btnInsert" runat="server" OnClientClick="showPleaseWait()" OnClick="btnInsert_Click"
Text="Save" Width="85px" Visible="false" ValidationGroup="vgInsert" />
protected void btnInsert_Click(object sender, EventArgs e)
{
    if (Page.IsValid)
    {
 if (FileUpload1.HasFile)
 {
     foreach (Process process in Process.GetProcessesByName("EXCEL"))
     {
  if (process.MainModule.ModuleName.ToUpper().Equals("EXCEL.EXE"))
  {
      process.Kill();
      Thread.Sleep(2000);
      break;
  }
     }
     try
     {
  sFilePath = Path.GetFileName(FileUpload1.FileName);
  if (Path.GetExtension(sFilePath) == ".xlsx" || Path.GetExtension(sFilePath) == ".xls")
  {
      FileUpload1.SaveAs(Server.MapPath(this.UploadFolderPath) + "\\" + sFilePath);
  }
  else
  {
      Label1.Text = "Please upload excel file only.";
      return;
  }
  DateTime dDate = Convert.ToDateTime(txtDate.Text);
  using (OleDbConnection connection = new OleDbConnection(CreateConnectionString(Server.MapPath(this.UploadFolderPath) + "\\" +sFilePath, Path.GetExtension(FileUpload1.PostedFile.FileName))))
  {
      OleDbCommand command = new OleDbCommand("Select * , '" + dDate.ToString("yyyy-MM-dd") + "' as DateExtracted, '" + ddlProjectName.SelectedValue + "' As ProjectName, '" + ddlArea.SelectedValue + "' As Area" + " FROM [Sheet1$]", connection);
      connection.Open();
      // Create DbDataReader to Data Worksheet
      using (DbDataReader dr = command.ExecuteReader())
      {
   // SQL Server Connection String
   string sqlConnectionString = connectionString;
   // Bulk Copy to SQL Server
   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
   {
       bulkCopy.DestinationTableName = TABLENAME;
       ColumnMapping(bulkCopy);
       try
       {
    bulkCopy.WriteToServer(dr);
       }
       catch (Exception ex)
       {
    Console.WriteLine(ex.Message);
       }
       finally
       {
    connection.Close();
       }
       Label5.Text = "File Data imported succesfully";
   }
      }
  }
     }
     catch (Exception ex)
     {
  Label1.Text = "Upload status: The file could not be uploaded.The following error occured:" + ex.Message;
     }
 }
 else
 {
     Label1.Text = "Upload status: The file could not be uploaded.";
 }
    }
}
private void ColumnMapping(SqlBulkCopy bulkCopy)
{
    bulkCopy.ColumnMappings.Add("ID", "ClashID");
    bulkCopy.ColumnMappings.Add("Status", "ClashStatus");
    bulkCopy.ColumnMappings.Add("Type", "ClashType");
    bulkCopy.ColumnMappings.Add("P G", "ClashPG");
    bulkCopy.ColumnMappings.Add("OID", "ClashOID");
    bulkCopy.ColumnMappings.Add("Remark", "ClashRemarks");
    bulkCopy.ColumnMappings.Add("A-System Path", "PartASystemPath");
    bulkCopy.ColumnMappings.Add("A-Name", "PartAName");
    //bulkCopy.ColumnMappings.Add("A-Parent Name", "L_Name");
    bulkCopy.ColumnMappings.Add("A-Aspect", "PartAAspect");
    bulkCopy.ColumnMappings.Add("A-Clashes", "PartAClashes");
    bulkCopy.ColumnMappings.Add("A-P G", "PartAPG");
    bulkCopy.ColumnMappings.Add("A-OID", "PartAOID");
    bulkCopy.ColumnMappings.Add("B-System Path", "PartBSystemPath");
    bulkCopy.ColumnMappings.Add("B-Name", "PartBName");
    //bulkCopy.ColumnMappings.Add("B-Parent Name B", "City");
    bulkCopy.ColumnMappings.Add("B-Aspect", "PartBAspect");
    bulkCopy.ColumnMappings.Add("B-Clashes", "PartBClashes");
    bulkCopy.ColumnMappings.Add("B-P G", "PartBPG");
    bulkCopy.ColumnMappings.Add("B-OID", "PartBOID");
    bulkCopy.ColumnMappings.Add("DateExtracted", "DateExtracted");
    bulkCopy.ColumnMappings.Add("ProjectName", "ProjectName");
    bulkCopy.ColumnMappings.Add("Area", "Area");
}
public string CreateConnectionString(string fileLocation, string fileExtension)
{
    return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileLocation + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
}

Exporting to excel with formating and preserving starting(Leading) zeros

public static void ExportToExcel(DataTable dt, string fileNameWithoutExtension)
{
    try
    {
 if (dt.Rows.Count > 0)
 {
     //Note: For simple Export uncomment the below code and use it.
     //////System.IO.StringWriter tw = new System.IO.StringWriter();
     //////System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
     //////DataGrid dgGrid = new DataGrid();
     //////dgGrid.DataSource = dt;
     //////dgGrid.DataBind();
     ////////Get the HTML for the control.
     //////dgGrid.RenderControl(hw);
     ////////Write the HTML back to the browser.
     ////////Response.ContentType = application/vnd.ms-excel;
     //////HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
     //////HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileNameWithoutExtension.Trim() + ".xls" + "");
     ////////this.EnableViewState = false;
     //////HttpContext.Current.Response.Write(tw.ToString());
     //////HttpContext.Current.Response.End();

     //Note : For Exporting to excel with formating and preserving starting(Leading) zeros use below code.
     String style = "<style>.text{mso-number-format:\\@;}</style>";
     HttpContext.Current.Response.Clear();
     HttpContext.Current.Response.Buffer = true;
     HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileNameWithoutExtension.Trim() + ".xls" + "");
     HttpContext.Current.Response.ContentType = "application/excel";
     StringWriter sw = new StringWriter();
     System.IO.StringWriter ioSw = new System.IO.StringWriter();
     System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(ioSw);
     GridView GridView1 = new GridView();
     GridView1.AllowPaging = false;
     GridView1.DataSource = dt;
     GridView1.DataBind();
    
     //Change the Header Row
     GridView1.HeaderRow.Style.Add("background-color", "#cc0000");
    
     //Apply style to Individual Cells    
     GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
     for (int i = 0; i < GridView1.Rows.Count; i++)
     {
  GridViewRow row = GridView1.Rows[i];
  //Apply text style to each Row        
  row.Attributes.Add("class", "textmode");
  //Apply style to Individual Cells of Alternating Row        
  if (i % 2 != 0)        
  {
   row.Cells[1].Attributes.Add("class", "text");//Format individual Column as text to preserve leading zeros
   row.Cells[2].Style.Add("background-color", "#C2D69B");
  }
     }
     //Remove Controls    
     this.RemoveControls(GridView1);
     GridView1.RenderControl(htw);
     HttpContext.Current.Response.Output.Write(style + ioSw.ToString());
     HttpContext.Current.Response.Flush();
     HttpContext.Current.Response.End();
 }
 else
 {
     throw new Exception("No records to export");
 }
    }
    catch (Exception ex)
    {
 throw ex;
    }
}
private void RemoveControls(Control grid)
{
    Literal literal = new Literal();
    for (int i = 0; i < grid.Controls.Count; i++)
    {
 if (grid.Controls[i] is LinkButton)
 {
     literal.Text = (grid.Controls[i] as LinkButton).Text;
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 else if (grid.Controls[i] is DropDownList)
 {
     literal.Text = (grid.Controls[i] as DropDownList).SelectedItem.Text;
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 else if (grid.Controls[i] is CheckBox)
 {
     literal.Text = (grid.Controls[i] as CheckBox).Checked ? "True" : "False";
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 else if (grid.Controls[i] is HyperLink)
 {
     literal.Text = (grid.Controls[i] as HyperLink).Text;
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 else if (grid.Controls[i] is Button)
 {
     literal.Text = (grid.Controls[i] as Button).Text;
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 if (grid.Controls[i].HasControls())
 {
     RemoveControls(grid.Controls[i]);
 }
    }
}

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;
        //}

Copy Constructor C#

C# does not provide a copy constructor. If you create a new object and want to copy the values from an existing object, you have to write the appropriate method yourself.
class Person
{
    private string name;
    private int age;
    // Copy constructor.
    public Person(Person previousPerson)
    {
        name = previousPerson.name;
        age = previousPerson.age;
    }
    // Instance constructor.
    public Person(string name, int age)
    {
        this.name = name;
        this.age = age;
    }
    // Get accessor.
    public string Details
    {
        get
        {
            return name + " is " + age.ToString();
        }
    }
}
class TestPerson
{
    static void Main()
    {
        // Create a new person object.
        Person person1 = new Person("George", 40);
        // Create another new object, copying person1.
        Person person2 = new Person(person1);
        System.Console.WriteLine(person2.Details);
    }
}

 

Constructors in C#

Constructor Overloading
public class mySampleClass
{
    public mySampleClass()
    {
        // This is the no parameter constructor method.
        // First Constructor
    }
    public mySampleClass(int Age)
    {
        // This is the constructor with one parameter.
        // Second Constructor

    }
    public mySampleClass(int Age, string Name)
    {
        // This is the constructor with two parameters.
        // Third Constructor
    }
    // rest of the class members goes here.
}
mySampleClass obj = new mySampleClass()
// At this time the code of no parameter 
// constructor (First Constructor)will be executed

mySampleClass obj = new mySampleClass(12)
Calling Constructor from another Constructor
public class mySampleClass
{
    public mySampleClass(): this(10)
    {
        // This is the no parameter constructor method.
        // First Constructor
    }
    public mySampleClass(int Age)
    {
        // This is the constructor with one parameter.
        // Second Constructor
    }
}
public mySampleClass(): this(10)
if I instantiate the object as:
mySampleClass obj = new mySampleClass()
Then the code of public mySampleClass(int Age) will be executed before the code of mySampleClass(). So, practically the definition of the method:
public mySampleClass(): this(10)
{
    // This is the no parameter constructor method.
    // First Constructor
}
is equivalent to:
public mySampleClass()
{
    mySampleClass(10)
    // This is the no parameter constructor method.
    // First Constructor
}
Note: Above (just above this line) code is mentioned there for pure analogy and will not compile.
We cannot make an explicit call to the constructors in C#, The only way you can call one constructor from another is through initializers.
Behavior of Constructors in Inheritance
public class myBaseClass
{
    public myBaseClass()
    {
        // Code for First Base class Constructor
    }
    public myBaseClass(int Age)
    {
        // Code for Second Base class Constructor
    }
    // Other class members goes here
}
public class myDerivedClass : myBaseClass
// Note that I am inheriting the class here.
{
    public myDerivedClass()
    {
        // Code for the First myDerivedClass Constructor.
    }
    public myDerivedClass(int Age):base(Age)
    {
        // Code for the Second myDerivedClass Constructor.
    }
    // Other class members goes here
}
Now, what will be the execution sequence here:
If I create the object of the derived class as:
myDerivedClass obj = new myDerivedClass()
Then the sequence of execution will be:
1.public myBaseClass() method.
2.and then public myDerivedClass() method.
Note: If we do not provide initializer referring to the base class constructor then it executes the no parameter constructor of the base class.
Note one thing here: we are not making any explicit call to the constructor of base class neither by initializer nor by the base keyword, but it is still executing. This is the normal behavior of the constructor.
If I create an object of the derived class as:
myDerivedClass obj = new myDerivedClass(15)
Then the sequence of execution will be:
1.public myBaseClass(int Age) method
2.and then public myDerivedClass(int Age) method
Here, the new keyword base has come into picture. This refers to the base class of the current class. So, here it refers to the myBaseClass. And base(10) refers to the call to myBaseClass(int Age) method.
Private Constructors
Private constructors, the constructors with the "private" access modifier, are a bit special case. It is because we can neither create the object of the class, nor can we inherit the class with only private constructors. But yes, we can have the set of public constructors along with the private constructors in the class and the public constructors can access the private constructors from within the class through constructor chaining.
public class myClass
{
    private MyClass()
    {
        Console.WriteLine("This is no parameter Constructor");
    }
    public MyClass(int var):this()
    {
        Console.WriteLine("This is one parameter Constructor");
    }   
    // Other class methods goes here
}
Then we can create the object of this class by the statement:
MyClass obj = new MyClass(10);
The above statement will work fine, but the statement
MyClass obj = new MyClass();
will raise an error : 'Constructors.MyClass.MyClass()' is inaccessible due to its protection level
It is possible to have the class with only the private constructors. But yes as I said, such class can neither be instantiated nor be inherited. If we try to inherit the class with only private constructors then we will get the same error as above. Also recall, once you provide constructor from your side the compiler will not add the no-parameter public constructor to your class.
Well, one of the usage scenarios of such class could be – when you have only static members in the class and you don't need to instantiate it.
Static Constructors
This is a special constructor and gets called before the first object is created of the class. The time of execution cannot be determined, but it is definitely before the first object creation - could be at the time of loading the assembly.
public class myClass
{
    static myClass()
    {
        // Initialization code goes here.
        // Can only access static members here.
    }
    // Other class methods goes here
}
Notes for Static Constructors:
1.There can be only one static constructor in the class.
2.The static constructor should be without parameters.
3.It can only access the static members of the class.
4.There should be no access modifier in static constructor definition.
Ok fine, all the above points are fine, but why is it like that? Let us go step by step here.
Firstly, the call to the static method is made by the CLR and not by the object, so we do not need to have the access modifier to it.
Secondly, it is going to be called by CLR, who can pass the parameters to it, if required. So we cannot have parameterized static constructor.
Thirdly, non-static members in the class are specific to the object instance. So static constructor, if allowed to work on non-static members, will reflect the changes in all the object instances, which is impractical. So static constructor can access only static members of the class.
Fourthly, overloading needs the two methods to be different in terms of methods definition, which you cannot do with Static Constructors, so you can have at the most one static constructor in the class.
Now, one question raises here, can we have two constructors as:
public class myClass
{
    static myClass()
    {
        // Initialization code goes here.
        // Can only access static members here.
    }
    public myClass()
    {
        // Code for the First myDerivedClass Constructor.
    }
    // Other class methods goes here
}
This is perfectly valid, though doesn't seem to be in accordance with overloading concepts. But why? Because the time of execution of the two methods are different. One is at the time of loading the assembly and one is at the time of object creation.
Constructors FAQs
1.Is the constructor mandatory for a class?
Yes, it is mandatory to have the constructor in the class and that too should be accessible for the object i.e., it should have a proper access modifier. Say, for example, we have only private constructor(s) in the class and if we are interested in instantiating the class, i.e., want to create an object of the class, then having only private constructor will not be sufficient and in fact it will raise an error. So, proper access modifies should be provided to the constructors.
2.What if I do not write the constructor?
In such case, the compiler will try to supply the no parameter constructor for your class, behind the scene. Compiler will attempt this only if you do not write the constructor for the class. If you provide any constructor (with or without parameters), then compiler will not make any such attempt.
3.What if I have the constructor public myDerivedClass(), but not the public myBaseClass()?
It will raise an error. If either the no parameter constructor is absent or it is in-accessible (say it is private), it will raise an error. You will have to take the precaution here.
4.Can we access static members from the non-static (normal) constructors?
Yes, we can. There is no such restriction on non-static constructors. But there is one on static constructors that it can access only static members.

 

Prevent double click using Jquery

$(document).ready(function(){

       

            $('#ctl00_hdnPostbackInProgress').val('0');

           

            $('.Button').each(function(){

                $(this).bind('click', function(e){

                    ValidateButtonPostBack($(this), e);

                });

            });

        });

       

       

        function ValidateButtonPostBack(control , e)

        {

            if($('#ctl00_hdnPostbackInProgress').val() == '0')

            {

                $('#ctl00_hdnPostbackInProgress').val('1');

               

                if(control[0].href.indexOf("WebForm_DoPostBackWithOptions") != -1)

                {

                    var startIndex = control[0].href.indexOf("WebForm_PostBackOptions") + 'WebForm_PostBackOptions'.length + 1;

                    var valGroup = control[0].href.substr(startIndex).split(',')[3].replace(/"/g,'').replace(/ /g,'');

                    if(!Page_ClientValidate(valGroup))

                    {

                        $('#ctl00_hdnPostbackInProgress').val('0');

                    }

                }

            }

            else

            {

                e.preventDefault();

            }

        }

Overriding Virtual methods

 private void Test()
{

a obja = new c();obja.ao();
}

class a{public virtual void ao() { Console.WriteLine("a"); }}

class b:a{public virtual void ao() { Console.WriteLine("b"); }}

class c:b{public override void ao() { Console.WriteLine("c"); }}
//Output : a


private void Test()
{

a obja = new c();obja.ao();
}

class a{public virtual void ao() { Console.WriteLine("a"); }}

class b:a{public override void ao() { Console.WriteLine("b"); }}

class c:b{public override void ao() { Console.WriteLine("c"); }}
//Output : b


private void Test()
{

a obja = new c();obja.ao();
}

class a{public virtual void ao() { Console.WriteLine("a"); }}

class b:a{//public virtual void ao() { Console.WriteLine("b"); }}

class c:b{public override void ao() { Console.WriteLine("c"); }}
//Output : c

private void Test()
{

a obja = new c();obja.ao();
}

class a{public virtual void ao() { Console.WriteLine("a"); }}

class b:a{public new void ao() { Console.WriteLine("b"); }}

class c:b{public void ao() { Console.WriteLine("c"); }}
//Output : a

Access Modifiers in C#

public
    The type or member can be accessed by any other code in the same assembly or another assembly that references it.
private
    The type or member can be accessed only by code in the same class or struct.
protected
    The type or member can be accessed only by code in the same class or struct, or in a class that is derived from that class.
internal
    The type or member can be accessed by any code in the same assembly, but not from another assembly.
protected internal
    The type or member can be accessed by any code in the assembly in which it is declared, or from within a derived class in another assembly. Access from another assembly must take place within a class declaration that derives from the class in which the protected internal element is declared, and it must take place through an instance of the derived class type.

Create Captcha Image using javascript in ASP.net C#

Default3.aspx

 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
    <script type="text/javascript" language="javascript">       
        function show()
        {
      
                var chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXTZabcdefghiklmnopqrstuvwxyz";
                var string_length = 5;
                var randomstring = '';
                for (var i=0; i<string_length; i++)
                {
                    var rnum = Math.floor(Math.random() * chars.length);
                    randomstring += chars.substring(rnum,rnum+1);
                }   
                var main = document.getElementById('txt1');
                main.value = randomstring;
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
   <div>
        <input type="text" id="txt1" runat="server" style="border-style: none; border-color: inherit;
            border-width: medium; background-color: black; color: red; font-family: 'Curlz MT';
            font-size: x-large; font-weight: bold; font-variant: normal; letter-spacing: 10pt;padding-left:10px;
            width: 135px;" value="1AsD" />
        <input type="button" onclick="show()" value="Change Capcha Image" />
    </div>
    <asp:TextBox ID="txtverification" runat="server"></asp:TextBox>
    &nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Button ID="Button1" runat="server" Text="Verification" OnClick="Button1_Click" />
    &nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Label ID="lblmsg" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>
    </form>
</body>
</html>
 

Default3.aspx.cs

protected void Button1_Click(object sender, EventArgs e)
    {
        if (txtverification.Text == txt1.Value)
        {
            lblmsg.Text = "Successfull";
        }
        else
        {
            lblmsg.Text = "Failure";
        }
    }

Split Text And Return Table With Values from function in SQL Server

CREATE FUNCTION [dbo].[

SplitTextAndReturnTableWithValues]
(
    @sTextToSplit    NVARCHAR(MAX),
    @Delimiter        NVARCHAR(50)
) 
RETURNS @tbReturn table
(
    RowId INT IDENTITY(1,1),
    Value NVARCHAR(MAX)
)
AS 
BEGIN
    SET @Delimiter = LTRIM(RTRIM(@Delimiter))
    DECLARE @sSplitedValue NVARCHAR(MAX), @Pos INT      
        
    SET @sTextToSplit = LTRIM(RTRIM(@sTextToSplit))+ @Delimiter         
    SET @Pos = CHARINDEX(@Delimiter, @sTextToSplit, 1)         
         
    IF REPLACE(@sTextToSplit, @Delimiter, '') <> ''         
        BEGIN         
            WHILE @Pos > 0         
                BEGIN         
                    SET @sSplitedValue = LTRIM(RTRIM(LEFT(@sTextToSplit, @Pos - 1)))         
                    IF @sSplitedValue <> ''         
                        BEGIN    
                             INSERT INTO @tbReturn VALUES (@sSplitedValue)
                        END         
                    SET @sTextToSplit = RIGHT(@sTextToSplit, LEN(@sTextToSplit) - @Pos)         
                    SET @Pos = CHARINDEX(@Delimiter, @sTextToSplit, 1)              
                END           
        END
    RETURN
END