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 a excel file into a database using a DataSet and SqlBulkCopy.

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

For demonstration purpose I have the following excel file, which has only one sheet which is named as “MySheet”.

Now let’s see the whole process in action. Create a new console application. Write the following method to get excel data into a table in 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, some properties are.

• "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.

Once it’s done, now We need to push the data in my DataSet into the database. Before doing that here we have following two helper methods.

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 I am 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 the DataSet and the table created in database, all I 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, initiate 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, the data can be seen in database table.

About Author:

Lovina Dodti is a consultant in Systems Plus Pvt. Ltd. Within Systems Plus, she actively contributes to the areas of Technology and Information Security. She can be contacted at lovina.d@spluspl.com