Attempting to migrate data from a spreadsheet to a Database using C# / ASP.NET

I have an Excel spreadsheet that I receive that I need to import into a table in our database. I have previously asked about pulling a single cell of data from a spreadsheet (Read a single cell from Excel to a string using C# and ASP.NET) and I am attempting to build off of this in order to move an entire spreadsheet into the database.

The format of the information is Column 1 = Name, Column 2 = Wage, Column 3 = Department

The existing code is as follows:

#region Initialize Connection
var Class_Connection = new SQL_Connection();
var sql = new SQL_Statements();

string properties = String.Format(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \HeraPublicLumberPriceslbr_ems.xls; Extended Properties = 'Excel 8.0; HDR = NO;'");
string Price = "";

using (OleDbConnection conn = new OleDbConnection(properties))
    string sqlpull = "SELECT * FROM [" + worksheet + "$" + Cell1 + ":" + Cell2 + "]";

    DataSet ds = new DataSet();
    //string columns = String.Join(",", columnNames.ToArray());

    using (OleDbDataAdapter da = new OleDbDataAdapter(sqlpull, properties))
        string temp2 = "";

        var dt = new DataTable();

        for (int i = 0; i < dt.Columns.Count; i++)
            temp2 = dt.Columns[i].ColumnName.ToString();

        foreach (DataRow dr in dt.Rows)
            Price = dr[temp2].ToString();

int Freight = GetFreight(LumbDesc);
int price1 = Convert.ToInt32(Price);
int addon = Convert.ToInt32(AddTo);
int Total = price1 + addon + Freight;

//TODO: insert into TLumberprice
string sqlStatement = "insert table([LumberCode], [Price], [PriceDate], [UserName], [Factor], [Op])" +
                      "values ('" + LumbDesc + "', '" + Total + "', '" + DateTime.Now + "', '" + LoginSession.userName.Remove(LoginSession.userName.Length - 1) + "', '" + Factor + "', '" + OP + "')";

#region Insert Lumber Prices

SqlCommand InsertLumberPrices = new SqlCommand(sqlStatement, Class_Connection.cnn);


This works as intended. but I have questions.

Can I read the DataTable(dt) straight into the database, or do I have to use something like the following?

for (int i = 0; i < dt.Columns.Count; i++)
    SName = dt.Columns[0].ColumnName.ToString();
    SWage = dt.Columns[1].ColumnName.ToString();
    SDept = dt.Columns[2].ColumnName.ToString();

foreach (DataRow dr in dt.Rows)
    Name = dr[SName].tostring();
    Wage = dr[SWage].tostring();
    Dept = dr[SDept].tostring();


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

There is a way of importing the data from a data table into the SQL Table.
You need to use SqlBulkCopy

var sqlBulkCopy = new SqlBulkCopy(conn);

You can even map which data table column goes to which SQL Table column as well.

Here is an example.

using(var sqlBulkCopy = new SqlBulkCopy(conn))
    sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Column 1", "Name"));
    sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Column 2", "Wage"));
    sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Column 3", "Department"));
    sqlBulkCopy.DestinationTableName = "table" // table name in SQL


Where conn is your SqlConnection.

If you don’t want to use the data table, you could go directly to the BulkImport.

Just change the OleDbDataAdapter to OleDbCommand and put the value in an IDataReader variable, which then you can use in the code above instead of dt.

With the last part, I only did it in the past between 2 SQL servers that did not have a link to each other. I’m not sure 100% sure if it will work with OleDb.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x