I use asp.net mvc ef code first. I upload the file to the server all i need is inserting that excel data to code first database. What is the best way to bulk insert excel data to database? Would appreciate your consultation.
Thanks in advance.
2 Answers
You can user LinqToExcel to get data from the Excel file and map it in your entity class.
If you are looking for alternative methods, these are some:
- OLEDB, see an example
- OPEN XML 2.0
- Some third party DLL like ExcelDataReader or EPPlus
5 Comments
Ensar Turkoglu
Is this a way for bulk insert? I need performance as well. Thanks by the way.
Ensar Turkoglu
LinqToExcel is a good way but when i have model, repositories, entities, services I better not use it. So what is another way?
user449689
@nsarchar if you are looking for a fast way to read, use OLEDB, read the data from the excel, map the value to your entity model and commit everything in just one transaction
Ensar Turkoglu
Thanks for all. But... What about the other question? Another way except any structure linq2excel etc.
Ensar Turkoglu
I actually am looking for not a component, just a manual reading. The excel i have is kind of complex, so i need to read cell by cell.
Using an ORM like Entity Framework is not efficient to perform bulk operations. To bulk insert efficiently, the SqlBulkCopy class must be used. To insert a generic list it must be converted to a DataTable:
To insert a generic list it must be converted to a DataTable:
public static DataTable ConvertToDataTable<T>(IList<T> list)
{
PropertyDescriptorCollection propertyDescriptorCollection = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < propertyDescriptorCollection.Count; i++)
{
PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
Type propType = propertyDescriptor.PropertyType;
if (propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
table.Columns.Add(propertyDescriptor.Name, Nullable.GetUnderlyingType(propType));
}
else
{
table.Columns.Add(propertyDescriptor.Name, propType);
}
}
object[] values = new object[propertyDescriptorCollection.Count];
foreach (T listItem in list)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = propertyDescriptorCollection[i].GetValue(listItem);
}
table.Rows.Add(values);
}
return table;
}
Then the SqlBulkCopy can be used. In the example the user table is bulk inserted:
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
{
sqlBulkCopy.ColumnMappings.Add("UserID", "UserID");
sqlBulkCopy.ColumnMappings.Add("UserName", "UserName");
sqlBulkCopy.ColumnMappings.Add("Password", "Password");
sqlBulkCopy.DestinationTableName = "User";
sqlBulkCopy.WriteToServer(dt);
}
}
1 Comment
Ensar Turkoglu
Thanks for your interests. I guess i need to accurately read the data. Then i'll take care of these chickens.