Knowledgebase

Let’s say your product has a many to many relation between products en customers, then the the Entity Framework 4.2 model would look like:

A night process imports customers and the products they bought into a reporting database. During the night process the reporting database is closed, so we can lock the tables, then you can use the following C# code to insert data:

Result

Inserting 1.000 products, 1.000 customers and 1.000.000 sales records takes about 42 seconds on my laptop

Laptop cpu utilization:

Laptop disk utilization:

If you would use Entity Framework to insert, then this would take fore ever

Code

The code could use the entity framework 4.2 entities and the data annotations applied to those entities, to validate the data to insert. That will be explained in an other post. The code uses one transaction to put all the data in the database and it will lock the tables to get a continuous identity on the tables. When an exceptions occurs the transaction will be roll backed.

[TestMethod]
public void TestMethod1()
{
    BulkInsertManyToManyRelationship();
}
public void BulkInsertManyToManyRelationship()
{
    // Connect to the database.
    using (var connection = new SqlConnection("data source=.;initial catalog=Research;integrated security=True;multipleactiveresultsets=True;"))
    {
        connection.Open();

        SqlTransaction transaction = null;
        DataTable productTable = null;
        DataTable customerTable = null;
        DataTable saleTable = null;
        try
        {
            transaction = connection.BeginTransaction(); // Use one transaction to put all the data in the database

            // Create 1.000 products.
            int lastProductIdentity = 0;
            int productCount = 1000;
            string productTableName = "Product";
            productTable = GetSchemaInfo(productTableName, connection, transaction);
            for (int i = 0; i < productCount; i++) 
            {
                DataRow row = productTable.NewRow();
                row[1] = string.Format("{0}_{1}", productTableName, i.ToString().PadLeft(3, '0'));
                productTable.Rows.Add(row);
            }
            BulkInsertTable(productTableName, productTable, connection, transaction);
            lastProductIdentity = GetIdentity(productTableName, connection, transaction);


            // Create 1.000 customers.
            int lastCustomerIdentity = 0;
            int customerCount = 1000;
            string customerTableName = "Customer";
            customerTable = GetSchemaInfo(customerTableName, connection, transaction);
            for (int i = 0; i < customerCount; i++) 
            {
                DataRow row = customerTable.NewRow();
                row[1] = string.Format("{0}_{1}", customerTableName, i.ToString().PadLeft(3, '0'));
                customerTable.Rows.Add(row);
            }
            BulkInsertTable(customerTableName, customerTable, connection, transaction);
            lastCustomerIdentity = GetIdentity(customerTableName, connection, transaction);


            // Add all products to all customers, this will create 1.000.000 records
            string saleTableName = "Sale";
            saleTable = GetSchemaInfo(saleTableName, connection, transaction);
            for (int c = lastCustomerIdentity - customerCount; c < lastCustomerIdentity; c++)
            {
                for (int p = lastProductIdentity - productCount; p < lastProductIdentity; p++)
                {
                    DataRow row = saleTable.NewRow();
                    row[1] = p;
                    row[2] = c;
                    saleTable.Rows.Add(row);
                }
            }
            BulkInsertTable(saleTableName, saleTable, connection, transaction);

            transaction.Commit();
        }
        catch(Exception ex)
        {
            transaction.Rollback(); // This will not reset IDENT_CURRENT
        }
        finally
        {
            if (productTable != null) { productTable.Dispose(); }
            if (customerTable != null) { customerTable.Dispose(); }
            if (saleTable != null) { saleTable.Dispose(); }
            if (transaction != null) { transaction.Dispose(); }
        }
    }
}
public DataTable GetSchemaInfo(string tableName, SqlConnection connection, SqlTransaction transaction)
{
    DataTable dataTable = new DataTable();
    using (SqlCommand selectSchemaCommand = connection.CreateCommand())
    {
        selectSchemaCommand.CommandText = string.Format("set fmtonly on; select * from {0}", tableName);
        selectSchemaCommand.Transaction = transaction;

        using (var adapter = new SqlDataAdapter(selectSchemaCommand)) // Get only the schema information for table [Sale]
        {
            adapter.FillSchema(dataTable, SchemaType.Source);
        }
    }
    return dataTable;
}
public int GetIdentity(string tableName, SqlConnection connection, SqlTransaction transaction)
{
    int identity = 0;

    // Get the last customer identity
    using (SqlCommand sqlCommand = connection.CreateCommand())
    {
        sqlCommand.CommandText = string.Format("SELECT IDENT_CURRENT('{0}')", tableName);
        sqlCommand.Transaction = transaction;
        identity = Convert.ToInt32(sqlCommand.ExecuteScalar());
    }

    return identity;
}
public void BulkInsertTable(string tableName, DataTable dataTable, SqlConnection connection, SqlTransaction transaction)
{
    using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction)) // Lock the table
    {
        sqlBulkCopy.DestinationTableName = tableName;
        sqlBulkCopy.WriteToServer(dataTable);
    }
}