Import Excel Data to SQL Server Table using c#

Using  Microsoft.Office.Interop.Excel Reading Excel Data by Rows and Columns

  #region getExcelDataUsing Interop
        private DataTable getexceldata()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;

            string str;
            int rCnt;
            int cCnt;
            int rw = 0;
            int cl = 0;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open("EXCELPATHMENTIONHERE", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;
            rw = range.Rows.Count;
            cl = range.Columns.Count;

            DataTable dt = new DataTable();
            dt.Columns.Add("Column1");
            dt.Columns.Add("Column2");
            dt.Columns.Add("Column3");
            dt.Columns.Add("Column4");
            dt.Columns.Add("Column5");
            dt.Columns.Add("Column6");
            dt.Columns.Add("Column7");
            dt.Columns.Add("Column8");
            dt.Columns.Add("Column9");
            dt.Columns.Add("Column10");

            for (int i = 0; i < cl; i++)
            {
                dt.Rows.Add("", "", "", "", "", "", "", "", "", "");
            }

            for (rCnt = 2; rCnt <= rw; rCnt++)
            {
                for (cCnt = 1; cCnt < 10; cCnt++)
                {
                    str = Convert.ToString((range.Cells[rCnt, cCnt] as Excel.Range).Value2);

                    dt.Rows[rCnt - 2][cCnt - 1] = str;
                }
            }
            DataTable dd = dt;

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);
            return dt;
        }
        #endregion


#region testOLEDBConnImportDataFromExcel
        private void ImportDataFromExcel()
        {
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.DataSet DtSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFilePath.Text.ToString() + ";Extended Properties=Excel 8.0;");
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
                MyCommand.TableMappings.Add("Table", "TestTable");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        #endregion

#region testExportToExcel(DataTable dt)
        public void ExportToExcel(DataTable dt)
        {
            //open file
            StreamWriter wr = new StreamWriter(@"D\TestManualUpload.xls");

            try
            {

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
                }

                wr.WriteLine();

                //write rows to excel file
                for (int i = 0; i < (dt.Rows.Count); i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (dt.Rows[i][j] != null)
                        {
                            wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
                        }
                        else
                        {
                            wr.Write("\t");
                        }
                    }
                    //go to next line
                    wr.WriteLine();
                }
                //close file
                wr.Close();
                MessageBox.Show("File Saved Successfully");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

 private void btnBrowse_Click(object sender, EventArgs e)
        {
            lblmsg.Visible = false;
            lblmsg.ForeColor = System.Drawing.Color.Green;
            OpenFileDialog file = new OpenFileDialog();
            if (file.ShowDialog() == DialogResult.OK)
            {
                txtFilePath.Text = file.FileName;
            }
        }

 public void ImportDataFromExcel(string excelFilePath)
        {
            lblmsg.Visible = true;
            lblmsg.Text = "Importing Data from Excel.... Please Wait...";
            DataTable dtExcelData = new DataTable();
            //declare variables - edit these based on your particular situation 
            string ssqltable = "MedicalEligUploadPkg";
            // make sure your sheet name is correct, here sheet name is sheet1,
            // so you can change your sheet name if have    different 
            string myexceldataquery = "select * from [Sheet1$]";
            try
            {
                //create our connection strings   Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=YES'
                string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties = Excel 12.0 Xml;HDR=YES;";
                string ssqlconnectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                //execute a query to erase any previous data from our destination table 
                string sclearsql = "delete from " + ssqltable;
                SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
                SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
                sqlconn.Open();
                sqlcmd.ExecuteNonQuery();
                sqlconn.Close();
                //series of commands to bulk copy data from the excel file into our sql table 
                string conString = string.Empty;
                string extension = Path.GetExtension(txtFilePath.Text);

                string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.MedicalEligUploadPkg";

                        //[OPTIONAL]: Map the Excel columns with that of the database table

                        /* sqlBulkCopy.ColumnMappings.Add("Subscriber ID", "Subscriber ID");
                         sqlBulkCopy.ColumnMappings.Add("Birth Date", "Birth Date");
                         sqlBulkCopy.ColumnMappings.Add("Issue Date", "Issue Date");
                         sqlBulkCopy.ColumnMappings.Add("Service Date", "Service Date");
                         sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                         sqlBulkCopy.ColumnMappings.Add("Primary Aid Code", "Primary Aid Code");
                         sqlBulkCopy.ColumnMappings.Add("First Special Aid Code", "First Special Aid Code");
                         sqlBulkCopy.ColumnMappings.Add("Subscriber County", "Subscriber County");
                         sqlBulkCopy.ColumnMappings.Add("Eligibility Message", "Eligibility Message");
                         sqlBulkCopy.ColumnMappings.Add("Eligibility", "Eligibility");
                         sqlBulkCopy.ColumnMappings.Add("Comments", "Comments");*/
                        DataTable dtvalues = getexceldata();
                        sqlBulkCopy.ColumnMappings.Add(0, 0);
                        sqlBulkCopy.ColumnMappings.Add(1, 1);
                        sqlBulkCopy.ColumnMappings.Add(2, 2);
                        sqlBulkCopy.ColumnMappings.Add(3, 3);
                        sqlBulkCopy.ColumnMappings.Add(4, 4);
                        sqlBulkCopy.ColumnMappings.Add(5, 5);
                        sqlBulkCopy.ColumnMappings.Add(6, 6);
                        sqlBulkCopy.ColumnMappings.Add(7, 7);
                        sqlBulkCopy.ColumnMappings.Add(8, 8);
                        sqlBulkCopy.ColumnMappings.Add(9, 9);
                        con.Open();
                        sqlBulkCopy.WriteToServer(dtvalues);
                        con.Close();
                    }
                }

                lblmsg.Text = "Eligibility Uploaded successfully.";
                sclearsql = "delete  from medicaleliguploadpkg  where (([Subscriber ID] = '') or ([Subscriber ID] is null))";
                sqlconn = new SqlConnection(ssqlconnectionstring);
                sqlcmd = new SqlCommand(sclearsql, sqlconn);
                sqlconn.Open();
                sqlcmd.ExecuteNonQuery();
                sqlconn.Close();
               
                sclearsql = "RunMedicalUploadPkg";
                sqlconn = new SqlConnection(ssqlconnectionstring);
                sqlcmd = new SqlCommand(sclearsql, sqlconn);
                sqlconn.Open();
                sqlcmd.ExecuteNonQuery();
                sqlconn.Close();
               

            }
            catch (Exception ex)
            {
                lblmsg.Text = "Upload Failed";
                lblmsg.ForeColor = System.Drawing.Color.Red;
                MessageBox.Show(ex.ToString());
            }
        }

Timer in c#

  private void button1_Click(object sender, EventArgs e)
        {
            label2.Text = "Player 1 Rolling";
            timer1.Tick += timer1_Tick;
            timer1.Tick += new EventHandler(test);
            timer1.Interval = 3000;
            timer1.Enabled = true;
            timer1.Start();
        }

        private void test(object sender, EventArgs e)
        {
            label1.Text = "You";
            label2.Text = "CPU is Rolling";
            Random rnd = new Random();
            int dice = rnd.Next(1, 7);
            textBox1.Text = dice.ToString();
            timer1.Tick += timer1_Tick;
            timer1.Interval = 5000;
            timer1.Enabled = true;
            timer1.Start();
        }

        void timer1_Tick(object sender, EventArgs e)
        {
            timer1.Stop();
            label2.Text = "";
            label1.Text = "CPU";
            Random rnd = new Random();
            int dice = rnd.Next(1, 7);
            textBox1.Text = dice.ToString();
        }

Password Validation Minimum and Maximum Numbers

JS
<SCRIPT>
$('#pass1').on('blur', function () {
if (this.value.length !=0){
    if (this.value.length < 3 || this.value.length >8 ) {
        alert('Passsword length should be between 3 and 8');
        this.value ="";
        return false;
    }
    }
});
</SCRIPT>

HTML

<input type="password" name="password" id="pass1" placeholder="password" />

configuring Log4Net For Web Applications

UPDATE: Mea Culpa! It seems like Log4Net has no problems with medium trust and an external log4net file. I have written an updated post that talks about the problem I did run into and how I solved it.

A while ago I wrote a quick and dirty guide to configuring Log4Net for ASP.NET. Unfortunately, this technique does not work with ASP.NET 2.0 when running in medium trust.. This technique continues to work with medium trust!

While digging into the problem I found this blog post (from an aptly titled blog) by Kevin Jones.

This article from Microsoft discusses the ramifications of running ASP.NET 2.0 in medium trust more thoroughly. Here is a list of constraints placed on medium trust applications.

The main constraints placed on medium trust Web applications are:

• OleDbPermission is not available. This means you cannot use the ADO.NET managed OLE DB data provider to access databases. However, you can use the managed SQL Server provider to access SQL Server databases.
• EventLogPermission is not available. This means you cannot access the Windows event log.
• ReflectionPermission is not available. This means you cannot use reflection.
• RegistryPermission is not available. This means you cannot access the registry.
• WebPermission is restricted. This means your application can only communicate with an address or range of addresses that you define in the <trust> element.
• FileIOPermission is restricted. This means you can only access files in your application’s virtual directory hierarchy. Your application is granted Read, Write, Append, and PathDiscovery permissions for your application’s virtual directory hierarchy.

You are also prevented from calling unmanaged code or from using Enterprise Services.

Fortunately there is a way to specify that a configuration section within web.config should not require ConfigurationPermission. Simply add therequirePermission="false" attribute to the <section> declaration within the <configSections> area like so:

<configSections>
    <section name="log4net" 
      type="log4net.Config.Log4NetConfigurationSectionHandler
      , log4net"     
      requirePermission="false"/>
</configSections>

Unfortunately this applies to configuration sections within the web.config file. I have not found a way to specify that ASP.NET should not requireConfigurationPermission on an external configuration file. As I stated in my post on Log4Net, I prefer to put my Log4Net configuration settings in a separate configuration file. If anyone knows a way to do this, please let me know!

So in order to get Log4Net to work, I added the declaration above to the web.config file and copied the settings within the Log4Net.config file (pretty much cut and paste everything except the top xml declaration) into the web.config file. I then removed the assembly level XmlConfiguratorattribute from AssemblyInfo.cs as it is no longer needed. Instead, I added the following line to the Application_Start method in Global.asax.cs.

protected void Application_Start(Object sender, EventArgs e)
{
    log4net.Config.XmlConfigurator.Configure();
}

So in summary, here are the changes I made to get Log4Net to work again in medium trust.

• Added the log4Net section declaration in the configSections section of web.config and made sure the requirePermission attribute is set to the value false.
• Moved the log4Net settings into web.config.
• Removed the assembly attribute XmlConfigurator
• Added the call to XmlConfigurator.Configure() to theApplication_Start method in Global.asax.cs.

I have been working on getting the version of Subtext in our Subversion trunk to run in a medium trust environment, but there have been many challenges. Some of the components we use do not appear to run in a medium trust environment such as the FreeTextBox. Fortunately, we have a workaround for that issue which is to change the RichTextEditor node in web.config to use the PlainTextRichTextEditorProvider (which is a mouthful and should probably be renamed to PlainTextEditorProvider).

Log4net is not working on production machine, How to fix that?

Log4net is commonly used tool to log custom exceptions, same is in my current project. It is useful to write trace information or exception. By the way for un handled exceptions i am using Elmah which is doing pretty well.

Problem

We have smart deployment system to our test and development servers, but currently issue was that locally log4net works pretty cool but on production it does not. To configure I am using assembly attribute 

[assembly: log4net.Config.XmlConfigurator(Watch=true)]

Solving

Firstly i thought issue is with permission because file was not created, i added iis_iusrs and finally everyone with full permissions but it does not help.

Finally i started reading log4net faq and found interesting topic about release configuration which says 

For a command line application "as early as possible" probably is the class holding the Main method, for a Web-Application it would be your Global.asax class and for a Windows Service it would be the class deriving from ServiceBase.

Going further i found other topic which says

Using attributes can be a clearer method for defining where the application's configuration will be loaded from. However it is worth noting that attributes are purely passive. They are information only. Therefore if you use configuration attributes you must invoke log4net to allow it to read the attributes. A simple call toLogManager.GetLogger will cause the attributes on the calling assembly to be read and processed. Therefore it is imperative to make a logging call as early as possible during the application start-up, and certainly before any external assemblies have been loaded and invoked.

So i added code to global.asax

  protected void Application_Start()
        {
           var logger = LogManager.GetLogger("Default");        }

And finally everything works on all my servers. I can log my trace info, which helps me to solve issues if any.

log4net not working on production server

This is the log4net configuration in the web.config of the website.

<configuration>
      <log4net>
        <root>
          <level value="DEBUG" />
          <appender-ref ref="LogFileAppender" />
        </root>
        <appender name="LogFileAppender" type="log4net.Appender.RollingFileAppender">
          <param name="File" value="log.txt" />
          <param name="AppendToFile" value="true" />
          <rollingStyle value="Size" />
          <maxSizeRollBackups value="10" />
          <maximumFileSize value="30MB" />
          <staticLogFileName value="false" />
          <layout type="log4net.Layout.PatternLayout">
            <param name="ConversionPattern" value="%-5p%d{yyyy-MM-dd hh:mm:ss} – %m%n" />
          </layout>
        </appender>
      </log4net>
    </configuration>

I also have a class library and this is its App.config file




<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,Log4net"/>
  </configSections>
  <log4net>
    <root>
      <level value="DEBUG" />
      <appender-ref ref="LogFileAppender" />
    </root>
    <appender name="LogFileAppender" type="log4net.Appender.RollingFileAppender" >
      <param name="File" value="log.txt" />
      <param name="AppendToFile" value="true" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="30MB" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <param name="ConversionPattern" value="%-5p%d{yyyy-MM-dd hh:mm:ss} – %m%n" />
      </layout>
    </appender>
  </log4net>
</configuration>




This is how I call the log function on every class:

private static readonly ILog log = LogManager.GetLogger(typeof(AppDomain));

...and this is how i call it :

log.Error("\n\t=>" + ex.GetBaseException().Message + "\n\r" + " @ " + Environment.StackTrace);




Solution 1

It could be that you do not have permissions to write to the file 'log.txt'.

I don't know what the current directory would be but it's unlikely to be somewhere IIS can write to.

You need to create a folder somewhere and grant access for IIS to write to it, I understand you need to grant access to the IIS_IUSRS group and then specify the absolute path to that file. e.g.

<param name="File" value="D:\Logs\log.txt" />

..using the path to your preferred location.

Solution 2 

If the directory and the file is not being created, then most likely, the configuration is not being read (and therefore used) at runtime.

I always forget to add the single line of code for Log4net that hooks up the configuration. This code usually appears in the bootstrap class in the application (e.g. Global.asax for an ASP.NET app).

XmlConfigurator.Configure(new System.IO.FileInfo(configFile));  // configFile being the path to the file.

Instead of the above in-line, you can add this attribute to the AssemblyInfo.cs file:

[assembly: log4net.Config.XmlConfigurator(Watch = true)]

Either way, this will wire up log4net. More information is found in the Manual Configuration section of the log4net docs.

I am using VS2005, a website project, a web deployment project and Log4Net. I can use logging when I am developing locally. I can see the log files and everything is fine. When I build my website, (using the web deployment project), I use the deploy as a single DLL option. When I then check the locations of where my log files should be I cannot see any files. Is there a way to troubleshoot this. I don't think adding the debug value to the App Settings will help because I don't have a console because it is a website. EDIT I don't want the 150 rep to go to waste so one last time. I compared the internal trace from my dev environment to the trace from the production. My dev environment trace shows the call the Xml Configurator where the production one does not. I have code in the global.asax on application_start() method. I put debug code in there and it is getting called in dev but not in production. I think this is where the web deployment project is causing some issues. Does the global.asax get compiled into the single DLL? When I do a build in the deployment directory I see a global.compiled file. Must this go into the bin folder in production? Or is the global.asax code in the single DLL? Having both in the bin folder or the just the DLL didn't change anything. SOLUTION 3  Permissions were CORRECT. Turns out that using a web deployment project also creates a precompliled.config file in the root. I had not copied that to the production enivornment. As soon as that was in the everything worked. Sorry no one got the bounty. SOLUTION 4 When ASP.NET application is deployed on IIS, its identity is IIS account (It is NETWORK SERVICE for IIS 7 by default). You can use Impersonation to impersonate a specific user to perform operations by using the authenticated user's identity or by using a specific Windows identity. For more information, see How To: Use Impersonation and Delegation in ASP.NET 2.0 (http://msdn.microsoft.com/en-us/library/ms998351.aspx).  You also can check Window Event Viewer which records the exception of application in Application section and check out IIS logs.

switch in c#

switch (ds.Tables[0].Rows[0]["STATE"].ToString())
                        {
                            case "CA": strPhone = "(877) 702-6666"; break;
                            case "CO": strPhone = "(877) 730-6666"; break;
                            case "NV": strPhone = "(877) 782-6666"; break;
                            case "UT": strPhone = "(877) 950-6666"; break;
                            case "AZ": strPhone = "(877) 272-6666"; break;
                            case "TX": strPhone = "(877) 575-6666"; break;
                        }

Javascript Date Mask with mm/dd/yyyy format in text box

onkeyup="return fnDateMask(event,this);"

function fnDateMask(event,control) {          
        var KeyID = event.keyCode;
        if (KeyID != 8)
        {    
            var s = new String(control.value);
            if (s.length == 2 || s.length == 5) {
                control.value = s + "/";
            }
        }
    } 

Javascript Number Validation on Key Press

onkeypress="return isNumber(event)"

function isNumber(evt) {
    var charCode = (evt.which) ? evt.which : event.keyCode;
    if (charCode > 31 && (charCode < 48 || charCode > 57))
        return false;
    else
        return true;
}