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();
}
}
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);
}
 |
| Table in the Database |
So that's it. I have uploaded the sample into my SkyDrive.
Happy Coding.
Regards,
Jaliya
