How to take Excel file uploaded from website and import data to SQL Server programmatically?

Question

I'm trying to do something like this:

public void ImportClick(object sender, EventArgs e) //the button used after selecting the spreadsheet file
{
    if (fileUpload.HasFile) //ASP.Net FileUpload control
    {
        if (fileUpload.FileName.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) || fileUpload.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
        {
            Excel sheet = new Excel(fileUpload.Open()); //not sure how to do this part

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["our_database"].ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("INSERT INTO table_name SELECT * FROM " + sheet, connection))
                {
                    connection.Open();
                    command.ExecuteQuery(); //Intellisense only has "ExecuteNonQuery()" method, but that's for T-SQL
                    connection.Close();
                }
            }
        }
        else
        {
            error.Text = "File must be either *.xls or *.xlsx";
            error.Visible = true;
        }
    }
    else
    {
        error.Text = "No file was selected";
        error.Visible = true;
    }
}

There are a lot of classes and interfaces in the Microsoft.Office.Interop.Excel namespace, and I don't know which one to use.

I know making the Excel object, along with the SQL command, probably won't be as easy as what I have here, but those are the two things I need help with.

Any suggestions/advice would be greatly appreciated!

Answer 1

I would suggest using Microsoft Jet Engine.

private static void UploadExcelToDB(string p)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(DBConnString))
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    Log("Opened connection to DB");
                }
                SqlBulkCopy sbk = new SqlBulkCopy(conn);
                sbk.BulkCopyTimeout = 600;
                sbk.DestinationTableName = DbTableName;
                DataTable excelDT = new DataTable();
                OleDbConnection excelConn = new OleDbConnection(ExcelConnString.Replace("xFILEx",p));
                excelConn.Open();
                if (excelConn.State == ConnectionState.Open)
                {
                    Log("Opened connection to Excel");
                }
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                cmdExcel.CommandText = "SELECT * FROM ["+ExcelTableName+"]";
                cmdExcel.Connection = excelConn;
                oda.SelectCommand = cmdExcel;
                oda.Fill(excelDT);
                if (excelDT != null)
                {
                    Log("Fetched records to local Data Table");
                }
                excelConn.Close();
                SqlCommand sqlCmd = new SqlCommand("TRUNCATE TABLE ICN_NUGGET_REPORT_RAW",conn);
                sqlCmd.CommandType = CommandType.Text;
                Log("Trying to clear current data in table");
                int i = sqlCmd.ExecuteNonQuery();
                Log("Table flushed");
                Log("Trying write new data to server");
                sbk.WriteToServer(excelDT);
                Log("Written to server");
                conn.Close();
            }
        }
        catch (Exception ex)
        {
            Log("ERROR: " + ex.Message);
            SendErrorReportMail();
        }
        finally
        {
            #if (DEBUG)
            {
            }
            #else
            {
            string archive_file = ArchiveDir+"\\" + DateTime.Now.ToString("yyyyMMdd-Hmmss") + ".xlsx";
            File.Move(p, archive_file);
            Log("Moved processed file to archive dir");
            Log("Starting archive process...");
            }
            #endif
        }
    }

This is how ExcelConnString looks like:

public static string ExcelConnString { get { return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xFILEx;Extended Properties=\"Excel 12.0;HDR=YES\";";} }

HDR=YES - this means that if you have column names in spreadsheet it will be treated as target table column names for matching each other.