Upload Excel file to SQL Server from Local to Remote PC

Sometimes we may need to upload data files from user end to Server side. The following program will perform that task.

Here is the .aspx file code :

Here is the .cs file code :

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Collections;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

public partial class DataTransfer : System.Web.UI.Page
{

    protected HtmlInputFile myInputFile;
    SqlConnection connection = null;
    static string fileNameWithLocation = “”;

    protected void Page_Init(object sender, EventArgs e)
    {

       connection = new SqlConnection(“server=’SERVERNAME’;uid= ‘USERID’;” +    

        “pwd=’PASSWORD’;database=DATABASE″);
    }

    protected void Page_Load(object sender, EventArgs e)

    {
    }

    protected void upButton_Click(object sender, EventArgs e)
    {
        HttpPostedFile myFile = FileUpload1.PostedFile;
        if (myFile.FileName != “” && myFile.ContentLength > 0)
        {
           String ServerFileName = Path.GetFileName(myFile.FileName);
           fileNameWithLocation = “C:\\” + ServerFileName;
           myFile.SaveAs(fileNameWithLocation);

           SendDataToTable( );
           DeleteXLSFile( );
        }
        else
        {
           lblTransferMsg.ForeColor = System.Drawing.Color.Red;
           lblTransferMsg.Text = “Select a File !!!”;
        }
    }

    private void DeleteXLSFile( )
    {
       try
       {
          FileInfo theFile = new FileInfo(“C:\\a.xls”);
          if (theFile.Exists)
            File.Delete(“C:\\a.xls”);
          else
             throw new FileNotFoundException();
       }
       catch (FileNotFoundException fnfe)
       {
          Response.Write(fnfe.Message);
       }
       catch (Exception ex)
       {
           Response.Write(ex.Message);
       }
  }

  private void SendDataToTable( )
  {
     DeleteDataFromTable( );
     String strSQL = “”;
     strSQL = “Insert into ReportTable Select * FROM OPENROWSET”;
     strSQL += “(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=” + fileNameWithLocation + “;”;
     strSQL += “HDR = YES ‘,’SELECT * FROM [a$]‘)”;

    SqlCommand myCommand = new SqlCommand(strSQL, connection);
    try
    {
       connection.Open( );
       myCommand.ExecuteNonQuery( );
       connection.Close( );

       lblTransferMsg.ForeColor = System.Drawing.Color.Green;
       lblTransferMsg.Text = “Transfer success”;
    }
    catch (Exception ex) { Response.Write(ex.ToString()); }

 }

 private void DeleteDataFromTable( )
  {
    String strSQL = “”;
    strSQL = “DELETE FROM ReportTable”;
    SqlCommand myCommand = new SqlCommand(strSQL, connection);
    try
    {
       connection.Open( );
       myCommand.ExecuteNonQuery( );
       connection.Close( );
   }
   catch (Exception ex) { Response.Write(ex.ToString( )); }
}

//before uploading u have to create the table