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