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 I am going to load data in the excel file into a DataSet and then for each tables in the DataSet, I am going to use SqlBulkCopy to push the data into the database. In my 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”.

Excel Sheet

Now let’s see the whole process in action. I’m creating a new console application. I have 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, I have mentioned some properties.

• "HDR=Yes;" indicates that the first row contains columnnames. 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 I do that, now I have to push the data in my DataSet into the database. Before doing that here I 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 I have 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, I am 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, I can see the data in my database table.

Table in the Database

So that's it. I have uploaded the sample into my SkyDrive.


Happy Coding.

Regards,
Jaliya