Writing Data in Excel File into a Database using a DataSet and SqlBulkCopy

In this post let’s see how we can write data in Excel file into a database using a DataSet and SqlBulkCopy.

The concept here is fairly simple. First, we are going to load data in the Excel file into a DataSet and then for each table in the DataSet, we are going to use SqlBulkCopy to push the data into the database. In this case, it will be a single table.

For demonstration purposes, we have the following Excel file which has only one sheet named “MySheet”.

Now let’s see the whole process in action. We're creating a new console application. We have the following method to get Excel data into a table DataSet.

public static DataSet ReadToDataSet(string filePath, string tableName)

{

    DataSet ds = new DataSet();

    OleDbCommand cmd = new OleDbCommand();

    OleDbDataAdapter adapter = new OleDbDataAdapter();

    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");

    try

    {

        conn.Open();

        cmd.Connection = conn;

        cmd.CommandType = CommandType.Text;

        // to select all the columns

        //cmd.CommandText = "SELECT * FROM [MySheet$]";

        // I am only selecting some columns

        cmd.CommandText = "SELECT FirstName, LastName, Department FROM [MySheet$]";

        adapter = new OleDbDataAdapter(cmd);

        adapter.Fill(ds, tableName);

        return ds;

    }

    catch (Exception ex)

    { 

    }

    finally

    {

        conn.Close();

    }

    return null;

}

Here when creating the OleDbConnection, in the query string along with the Excel file path, we have mentioned some properties.

• "HDR=Yes;" indicates that the first row contains column names. If "HDR=No;", it indicates the opposite.
• MEX=1 is a safer way to retrieve data for mixed data columns. (You can read more on Excel connection strings on http://www.connectionstrings.com/excel/).

Once we do that, now we have to push the data in a DataSet into the database. Before doing that here we have following two helper methods.

The following method will check if a table already exists with the given name in the database.

private static bool CheckWhetherTableExists(SqlConnection conn, string tableName)

{

    bool isExist = false;

    SqlCommand cmd;

    try

    {

        conn.Open();

        cmd = new SqlCommand("SELECT * FROM sysobjects where name = '" + tableName + "'", conn);

        isExist = cmd.ExecuteScalar() == null ? false : true;

        return isExist;

    }

    catch (Exception ex)

    {

    }

    finally

    {

        conn.Close();

    }

    return true;

}

If the table doesn't exist, following method will create a table with all the column names as in provided DataTable. Please note, here for all columns we're using “varchar(MAX)” as the column type.

private static void CreateTableInDatabase(SqlConnection conn, DataTable dt)

{

    bool isExist = false;

    SqlCommand cmd;

    try

    {

        conn.Open();

        foreach (DataColumn dc in dt.Columns)

        {

            if (!isExist)

            {

                cmd = new SqlCommand("CREATE TABLE " + dt.TableName + " (" + dc.ColumnName + " varchar(MAX))", conn);

                cmd.ExecuteNonQuery();

                isExist = true;

            }

            else

            {

                cmd = new SqlCommand("ALTER TABLE " + dt.TableName + " ADD " + dc.ColumnName + " varchar(MAX)", conn);

                cmd.ExecuteNonQuery();

            }

        }

    }

    catch (Exception ex)

    {

    }

    finally

    {

        conn.Close();

    }

}

Once we have the DataSet and the table created in the database, all we have to do now is push the data in DataSet into the database.

public static void PushDataSetToDatabase(SqlConnection conn, DataSet ds)

{

   try

   {

       foreach (DataTable dt in ds.Tables)

       {

           if (!CheckWhetherTableExists(conn, dt.TableName))

           {

               CreateTableInDatabase(conn, dt);

           }

           conn.Open();

           using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))

           {

               bulkcopy.DestinationTableName = dt.TableName;

               bulkcopy.WriteToServer(dt);

           }

       }

   }

   catch (Exception ex)

   {

   }

   finally

   {

       conn.Close();

   }

}

Finally, from the Main method we're initiating the process.

static void Main(string[] args)

{

    string filePath = @"D:\SampleExcelFile.xlsx";

    DataSet ds = ReadToDataSet(filePath, "MyTable");

    SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=MyTestDB;Integrated Security=True");

    PushDataSetToDatabase(conn, ds);

}

Once the program completed, we can see the data in the database table.

That's it. We have uploaded the sample to the MSDN Code Gallery.
   Download Sample
   Download Sample Excel Sheet

Happy Coding.