Getting Data table from the Excel Sheet Using C#

        string connectionString = "";

        bool flag = true;

        object value;

        try

        {

            string fileName = Path.GetFileName(fb_master.PostedFile.FileName);

            string fileExtension = Path.GetExtension(fb_master.PostedFile.FileName);

            string folder = ConfigurationManager.AppSettings["TargetFiles"];

            string fileLocation = ConfigurationManager.AppSettings["TargetFiles"]+System.DateTime.Today.ToString("dd-MMyyyy")+"_"+ fileName;

            DirectoryInfo dir = new DirectoryInfo(folder);

            if (!dir.Exists)

                dir.Create();

            fb_master.SaveAs(fileLocation);

            if (fb_master.HasFile)

            {

                //Check whether file extension is xls or xslx

                if (fileExtension == ".xls")

                {

                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

                }

                else if (fileExtension == ".xlsx")

                {

                    //connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

                }

                //Create OleDB Connection and OleDb Command

                OleDbConnection con = new OleDbConnection(connectionString);

                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 + "] where Finyear is not null ";

                dAdapter.SelectCommand = cmd;

                dAdapter.Fill(dtExcelRecords);

                con.Close();

          }

        }

        catch (Exception ex)

        {

            UserMessage.ShowUserMessage("Excel file cannot be uploaded.please check the data in Excel file.It may contains Null values.");

            _gb.ErrorLog(ex.Message);

        }

Avoiding special characters in C# using javascript

 function isNumber(evt)
       {
                   evt = (evt) ? evt : window.event;
                   var charCode = (evt.which) ? evt.which : evt.keyCode;
                   if (charCode > 59 && charCode < 64)     // here m checking for < > ? characters only
                  {
                      return false;
                   }
                   return true;
        }


// Call this javascript function onkeypress event of the text box like -   onkeypress="return isNumber(event)"

If any design issues in internet explorer because of version level , add the below code in Web.config

      <system.webServer>
    <httpProtocol>
      <customHeaders>
        <clear/>
   <!--This setting will make document mode to highest mode available we need have mode 8 and above-->
       <add name="X-UA-Compatible" value="IE=Edge"/>
      </customHeaders>
    </httpProtocol>
  </system.webServer>


Simple things effects a lot ... happy coding.........

Example for delegate in C# - here i will show how to call the child page button click on clicking the button which is inside user control of the parent page

In User Control lets name like horizantalmenu.ascx, i will define the event handler and i will use it in the particular button click event



 public event EventHandler ButtonClickDemo;   // define it globally

protected void Page_Load(object sender, EventArgs e)
 {
   
 }


protected void Button_Click(object sender, ImageClickEventArgs e)
    {
        ButtonClickDemo(sender, e);    // m using the event here
    }


--------------------------------------------------------------------------------------------------

In all the successive pages where you are calling  horizantalmenu.ascx in your page ,

trigger that event in the page load of your page

protected void Page_Load(object sender, EventArgs e)
    {
        HorizontalMenu.ButtonClickDemo += new EventHandler(GetMainQueryOnNotepad);
                                                                             // assigning with one function to call on button click
    }


 protected void GetMainQueryOnNotepad(object sender, EventArgs e)
    {
           /// you can do what ever you want to do in this function.
    }

Creating simple error log in Asp.net / C#

 public void ErrorLog(string sMessage)
    {
     
        string LogPath = ConfigurationManager.AppSettings["ErrorlagPath"].ToString();
        string filename = "ErrorLog_" + DateTime.Now.ToString("dd-MM-yyyy") + ".txt";
        string filepath = LogPath + filename;

        if (!Directory.Exists(LogPath))
            Directory.CreateDirectory(LogPath);
        string sFilePath = filepath;

        StreamWriter objSw = new StreamWriter(sFilePath, true);
        objSw.WriteLine("-------------------START-------------" + DateTime.Now + " -----------------");
        objSw.WriteLine(sMessage);
        objSw.WriteLine("-------------------END-------------" + DateTime.Now + " -----------------");
        objSw.Close();
    }

Sql server Date formats

WHEN 'mmm dd yyyy hh:mm AM/PM' THEN 100
WHEN 'mm/dd/yy' THEN 1
WHEN 'mm/dd/yyyy' THEN 101
WHEN 'yy.mm.dd' THEN 2
WHEN 'dd/mm/yy' THEN 3
WHEN 'dd.mm.yy' THEN 4
WHEN 'dd-mm-yy' THEN 5
WHEN 'dd Mmm yy' THEN 6
WHEN 'Mmm dd, yy' THEN 7
WHEN 'hh:mm:ss' THEN 8
WHEN 'yyyy.mm.dd' THEN 102
WHEN 'dd/mm/yyyy' THEN 103
WHEN 'dd.mm.yyyy' THEN 104
WHEN 'dd-mm-yyyy' THEN 105
WHEN 'dd Mmm yyyy' THEN 106
WHEN 'Mmm dd, yyyy' THEN 107
WHEN 'Mmm dd yyyy hh:mm:ss:ms AM/PM' THEN 9
WHEN 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' THEN 9
WHEN 'Mmm dd yy hh:mm:ss:ms AM/PM' THEN 109
WHEN 'mm-dd-yy' THEN 10
WHEN 'mm-dd-yyyy' THEN 110
WHEN 'yy/mm/dd' THEN 11
WHEN 'yyyy/mm/dd' THEN 111
WHEN 'yymmdd' THEN 12
WHEN 'yyyymmdd' THEN 112
WHEN 'dd Mmm yyyy hh:mm:ss:Ms' THEN 113
WHEN 'hh:mm:ss:Ms' THEN 14
WHEN 'yyyy-mm-dd hh:mm:ss' THEN 120
WHEN 'yyyy-mm-dd hh:mm:ss.Ms' THEN 121
WHEN 'yyyy-mm-ddThh:mm:ss.Ms' THEN 126
WHEN 'dd Mmm yyyy hh:mm:ss:ms AM/PM' THEN 130
WHEN 'dd/mm/yy hh:mm:ss:ms AM/PM' THEN 131
WHEN 'RFC822' THEN 2
WHEN 'dd Mmm yyyy hh:mm' THEN 4

Generating pdf document from C#

       Document pdfDoc = new Document(PageSize.A4, 30, 30, 40, 25);
        System.IO.MemoryStream mStream = new System.IO.MemoryStream();
        PdfWriter writer = PdfWriter.GetInstance(pdfDoc, mStream);
        int cols = 4;
        int rows = GvTicketHistory.Rows.Count;
        pdfDoc.Open();

      // create the table to add in pdf





        objCustomerHistoryViewBAL = new CustomerHistoryViewBAL();
        string refno = Convert.ToString(Request.QueryString["refno"]);
        DataTable dt = objCustomerHistoryViewBAL.getpatientdetails(refno);

        float[] anchoDeColumnas = new float[] { 15f,25f, 12f, 28f,10f,10f };

        string imagepath = ConfigurationManager.AppSettings["CRMFilesLocation"];

        iTextSharp.text.Table pdfTable = new iTextSharp.text.Table(cols, rows);
        iTextSharp.text.Table pdfTable1 = new iTextSharp.text.Table(cols, rows);

        //pdfTable1.BorderWidth = 0;
       // pdfTable1.Padding = 0;
       // pdfTable1.Spacing = 0;
        pdfTable1.Width = 100f;

        pdfTable.BorderWidth = 0;    
        pdfTable.Padding = 0;
        pdfTable.Spacing = 0;
        pdfTable.Width = 100f;
        pdfTable.Cellpadding = 1f;
      

        string filepath = Server.MapPath("~/Images_V2/PreAuth_V2/");

        iTextSharp.text.Image gif = iTextSharp.text.Image.GetInstance(filepath + "logo.gif");
        gif.Alignment = Element.ALIGN_RIGHT;
        pdfDoc.Add(gif);

        Font contentfont = FontFactory.GetFont("Times New Roman", 10,Font.BOLD | Font.UNDERLINE,Color.RED);
     

        //Paragraph p = new Paragraph("REMEDINET TECHNOLOGIES PVT.LTD.", contentfont);
        //p.IndentationLeft = 180f;
        //pdfDoc.Add(p);

        Font lblb = FontFactory.GetFont("Times New Roman", 7,Font.BOLD);
        Font lblv= FontFactory.GetFont("Times New Roman", 7);

        PdfPTable table = new PdfPTable(6);
        table.SetWidths(anchoDeColumnas);
        PdfPCell cell = new PdfPCell(new Phrase("REMEDINET TECHNOLOGIES PVT. LTD.",      contentfont));
        cell.BorderWidth = 0;
        cell.Colspan =6;
        cell.HorizontalAlignment = 1; //0=Left, 1=Centre, 2=Right
        table.AddCell(cell);


        PdfPCell cell1 = new PdfPCell(new Phrase("Patient Name:", lblb));
        cell1.BorderWidth = 0;
        table.AddCell(cell1);
        PdfPCell cell2 = new PdfPCell(new Phrase(dt.Rows[0]["PR_NAME"].ToString(), lblv));
        cell2.BorderWidth = 0;
        table.AddCell(cell2);
        PdfPCell cell3 = new PdfPCell(new Phrase("Age:", lblb));
        cell3.BorderWidth = 0;
        table.AddCell(cell3);
        PdfPCell cell4 = new PdfPCell(new Phrase(dt.Rows[0]["PR_AGE"].ToString(), lblv));
        cell4.BorderWidth = 0;
        table.AddCell(cell4);
        PdfPCell cell5 = new PdfPCell(new Phrase("Gender:", lblb));
        cell5.BorderWidth = 0;
        table.AddCell(cell5);
        PdfPCell cell6 = new PdfPCell(new Phrase(dt.Rows[0]["PR_SEX"].ToString(), lblv));
        cell6.BorderWidth = 0;
        table.AddCell(cell6);

        PdfPCell cell7 = new PdfPCell(new Phrase("Member ID:", lblb));
        cell7.BorderWidth = 0;
        table.AddCell(cell7);
        PdfPCell cell8 = new PdfPCell(new Phrase(dt.Rows[0]["PR_TPAID"].ToString(), lblv));
        cell8.BorderWidth = 0;
        table.AddCell(cell8);
        PdfPCell cell9 = new PdfPCell(new Phrase("Policy No", lblb));
        cell9.BorderWidth = 0;
        table.AddCell(cell9);
        PdfPCell cell10 = new PdfPCell(new Phrase(dt.Rows[0]["PR_POLNO"].ToString(), lblv));
        cell10.BorderWidth = 0;
        table.AddCell(cell10);
        PdfPCell cell11 = new PdfPCell(new Phrase("", lblb));
        cell11.BorderWidth = 0;
        table.AddCell(cell11);
        PdfPCell cell12 = new PdfPCell(new Phrase("", lblb));
        cell12.BorderWidth = 0;
        table.AddCell(cell12);


        PdfPCell cell13 = new PdfPCell(new Phrase("Corporate:", lblb));
        cell13.BorderWidth = 0;
        table.AddCell(cell13);
        PdfPCell cell14 = new PdfPCell(new Phrase(dt.Rows[0]["PR_CORP"].ToString(), lblv));
        cell14.BorderWidth = 0;
        table.AddCell(cell14);
        PdfPCell cell15 = new PdfPCell(new Phrase("Employee ID:", lblb));
        cell15.BorderWidth = 0;
        table.AddCell(cell15);
        PdfPCell cell16 = new PdfPCell(new Phrase(dt.Rows[0]["PR_CORPID"].ToString(), lblv));
        cell16.BorderWidth = 0;
        table.AddCell(cell16);
        PdfPCell cell17 = new PdfPCell(new Phrase("", lblb));
        cell17.BorderWidth = 0;
        table.AddCell(cell17);
        PdfPCell cell18 = new PdfPCell(new Phrase("", lblb));
        cell18.BorderWidth = 0;
        table.AddCell(cell18);


        PdfPCell cell19 = new PdfPCell(new Phrase("Case Reference ID:", lblb));
        cell19.BorderWidth = 0;
        table.AddCell(cell19);
        PdfPCell cell20 = new PdfPCell(new Phrase(dt.Rows[0]["REFNO"].ToString(), lblv));
        cell20.BorderWidth = 0;
        table.AddCell(cell20);
        PdfPCell cell21 = new PdfPCell(new Phrase("Provider Name:", lblb));
        cell21.BorderWidth = 0;
        table.AddCell(cell21);
        PdfPCell cell22 = new PdfPCell(new Phrase(dt.Rows[0]["HOSPNAME"].ToString(), lblv));
        cell22.BorderWidth = 0;
        table.AddCell(cell22);
        PdfPCell cell23 = new PdfPCell(new Phrase("", lblb));
        cell23.BorderWidth = 0;
        table.AddCell(cell23);
        PdfPCell cell24 = new PdfPCell(new Phrase("", lblb));
        cell24.BorderWidth = 0;
        table.AddCell(cell24);    
        table.WidthPercentage = 100;
    
  //      add the table here

       pdfDoc.Add(table);
        pdfDoc.Close();
        Response.ContentType = "application/octet-stream";
        Response.AddHeader("Content-Disposition", "attachment; filename=Report.pdf");
        Response.Clear();
        Response.BinaryWrite(mStream.ToArray());
        Response.End();