OakLeaf Systems: Testing Stored Procedure Replacements for Entity SQL Statements

Saturday, March 29, 2008

Testing Stored Procedure Replacements for Entity SQL Statements

The Migrating to SQL Server Stored Procedures with the EDM Designer December CTP 2 post of March 27, 2008 shows you how to move from dynamic Entity SQL (eSQL) CRUD (create, retrieve, update and delete) operations to Transact-SQL stored procedures (sprocs). This post shows the code used to verify sproc execution, data retrieval, and persistence of changes to the back-end data store (SQL Server 2005 Express for these examples.)

Update 3/30/3008: The Minimize Stored Procedure Calls and Improve Performance with EF Extensions post of March 30, 2008 describes a more efficient implementation using the Entity Framework Extensions.

Retrieving Products and Attaching Category and Supplier EntityReferences

Sproc FunctionImports return ObjectResult<T> collections, which are the result of an ObjectQuery. Products, Categories, and Suppliers EntitySets are reference (catalog) data and are candidates for caching in a generic List<Product> collection for WinForm projects. Caching is necessary because operations on Context.EntitySet collections, such as Count(), invoke dynamic SQL ObjectQuery() methods to retrieve current data from the store.

The following event handler for the Get Products button with 80 products issues 161 sproc calls: 1 for GetAllProducts and 80 each for GetCategoryForProductById and GetSupplierForProductByID:

private void btnGetProducts_Click(object sender, EventArgs e)
{
    ctxNwind = new NorthwindEntities();
    prodList = new List&lt;Product&gt;();
    ObjectResult&lt;Product&gt; prods = ctxNwind.GetAllProducts();
    foreach (Product prod in prods)
    {
        prod.CategoryReference
            .Attach(ctxNwind.GetCategoryForProductById(prod.ProductID).First());
        prod.SupplierReference
            .Attach(ctxNwind.GetSupplierForProductById(prod.ProductID).First());
        prodList.Add(prod);
    }
    productBindingSource.DataSource = prodList;
}

Note: Retrieving the eight Category and 29 Supplier Entities with their Products EntitySets as a second resultset is an alternative approach that reduces to 39 the number of sproc calls and doesn't transfer duplicate data. (Two sprocs are required to get a resultset containing the primary key values for the two sprocs.)

This approach requires using the approach shown in the "Multiple result sets" section of Colin Meek's ADO.Entity Framework: Stored Procedure Customization post of March 26, 2008 and will be the subject of another OakLeaf post.

Event Handlers for INSERT, UPDATE, and DELETE Sproc Execution

Following is the code for adding a standard Product Entity with Beverages as the CategoryReference and Exotic Liquids as the SupplierReference:

private void btnAddProduct_Click(object sender, EventArgs e)
{
    Product addProd = new Product();
    addProd.ProductName = "New Product";
    addProd.Category = prodList[0].Category;
    addProd.Supplier = prodList[0].Supplier;
    addProd.UnitPrice = 15.00M;
    addProd.QuantityPerUnit = "24 250-ml bottles";
    addProd.UnitsInStock = 5;
    addProd.UnitsOnOrder = 15;
    addProd.ReorderLevel = 10;
    addProd.Discontinued = false;

    // Add to the ObjectContext
    ctxNwind.AddToProducts(addProd);

    // Persist the changes
    ctxNwind.SaveChanges();

    // Add to the cache
    prodList.Add(addProd);
}

Here's the code for the Edit Product button's event handler, which changes simple type values and EntityReferences to Condiments and Grandma Kelly's Homestead:

private void btnEditProduct_Click(object sender, EventArgs e)
{
    // Retrieve last Product from cache
    Product editProd = prodList.Last();
    if (editProd.ProductID &gt; 77)
    {
        ctxNwind.AttachTo("Products", editProd);
        editProd.ProductName = "Edited Product";
        editProd.Category = prodList[5].Category;
        editProd.Supplier = prodList[5].Supplier;
        editProd.UnitPrice += 5;
        editProd.QuantityPerUnit = "36 250-ml bottles";
        editProd.UnitsInStock -= 1;

        // Persist the changes
        ctxNwind.SaveChanges();

        // Update the cache
        prodList.RemoveAt(prodList.Count() - 1);
        prodList.Add(editProd);

        // Refresh the bindings
        productBindingSource.ResetBindings(false);
        productBindingSource.MoveLast();
    }
    else MessageBox.Show("Can't edit products with ProductID <= 77");
}

Here's a screen capture of the test harness after adding and editing a new product.

Finally, here's the code for the Remove Product button's event handler:

private void btnRemoveProduct_Click(object sender, EventArgs e)
{
    Product delProd = prodList.Last();
    if (delProd.ProductID &gt; 77)
    {
        ctxNwind.DeleteObject(delProd);

        // Persist the changes
        ctxNwind.SaveChanges();

        // Update the cache
        prodList.RemoveAt(prodList.Count() - 1);

        // Refresh the record pointer
        productBindingSource.MoveLast();
    }
    else MessageBox.Show("Can't delete products with ProductID <= 77");
}

Here's a capture of the SQL Server Profiler after retrieving all Product entities, then adding, editing and deleting one:

Notice the required foreign key values in the usp_DeleteProduct command.

Stay tuned for the version that uses the EF Extensions.

Updated 3/30/2008: As noted at the beginning of this post, the Minimize Stored Procedure Calls and Improve Performance with EF Extensions post of March 30, 2008 describes a more efficient implementation using the Entity Framework Extensions.