Monday, February 3, 2014

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

Picture
Table in the Database

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


Happy Coding.

Regards,
Jaliya