CODEWITHSUNDEEP
asp.net-mvcexcelentity-frameworkef-code-firstbulkinsert
Ensar Turkoglu
Ensar Turkoglu

Reputation: 124

Bulk Inserting Excel Data to Database Using EF Code First

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.

Upvotes: 0

Views: 2362

Answers (2)

Ravi
Ravi

Reputation: 316

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);
    }
}

Upvotes: 1

user449689
user449689

Reputation: 3154

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:

Upvotes: 1

Related Questions