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();
Class_Connection.cnn.Close();
#endregion

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

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

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

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

        var dt = new DataTable();
        da.Fill(dt);

        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
Class_Connection.cnn.Open();

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

Class_Connection.cnn.Close();
#endregion

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();
}

1 answer

  • answered 2021-01-19 22:17 George

    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
    
        conn.Open();
        sqlBulkCopy.WriteToServer(dt);
        conn.Close();
    }
    

    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.