DataEntities & Stored Procedures

There are lots of ways to connect to your data. I really like working with DataEntities but I still appreciate the efficiency of stored procedures, so don't forget that they can work peacefully together.

For this example I have selected a stored procedure that does some behind the scenes manipulation. It compares a table that I have just added records to from an Excel upload and it inserts those records into another table if they don't exist already, then it deletes the initial group of records.
It also returns the number of records that it moved from one table to the other.

Create PROCEDURE [dbo].[UpdateExtensions]
--This is the output parameter that we will pass from the method which will return the number of changed records
@row_count int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.


INSERT Account_StoreExtensions (AccountCode, ExtensionURL, Store_Extension)
Select AccountCode, ExtensionURL, StoreExtension
from ExtensionUpload WHERE
NOT EXISTS (SELECT AccountCode from Account_StoreExtensions A2 Where A2.AccountCode=ExtensionUpload.AccountCode and A2.Store_Extension=ExtensionUpload.StoreExtension);
SET @row_count = @@ROWCOUNT
--Notice that we are setting the @row_count right after the insert - if we just return the @@ROWCOUNT it will give us the number of deleted records instead of the number of records that we actually added

--Then I delete from my first table and return the record count
delete from ExtensionUpload

Return @row_count

END

When you pull in your DataEntities you will want to be sure and check to Import Stored Procedures and actually choose it from the list - you don't see it in this image as I have already imported it, but you get the idea!

---

Since I am using my own Context instead of the one the DataEntities Tool made for me I copy the lines shown below from the Context the system created over to mine:

    public virtual int UpdateExtensions(ObjectParameter row_count)
        {
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("UpdateExtensions", row_count);
        }

All that is left is to call it from your Controller

Remember that parameter we declared in the Sproc?  I am going to put the return value into my 'int rowcount' so I am declaring it here

int rowcount;
           
                    using (MYCustomContext context = new MYCustomContext())
                    {
                        //@row_count
                        ObjectParameter objParam = new ObjectParameter("row_count", typeof(int));
                        context.UpdateExtensions(objParam);
                                                rowcount = Convert.ToInt32(objParam.Value);
                    }
//Then I throw the returned results from the sproc into a ViewBag message
                    ViewBag.message = itemcount + " Records were inserted! And of all records " + rowcount + " were new and added to the table";
                }

And that is it!  This example used MVC 5 and Entity Framework 6