SQL Server 2008, Entity Framework, DAL, LINQ and Unit Test

Microsoft is making our lives too easy :)  Below I show all of the components necessary to transfer a XML Parameter to a SQL Server 2008 stored procedure:

Once this code is in place there are only two places that will require update if there is a structure change - the following data class (implements IXMLSqlParameter) and the actual stored procedure that recieves the resulting XML record.

namespace Business.Interface.Entities

{

    /// <summary>

    /// Facility Data object

    /// </summary>

    public class FacilityData : IXMLSqlParameter

    {

        public int ID { get; set; }

        public string Name { get; set; }

        /// <summary>

        /// Overridden for easier viewing in debugger

        /// </summary>

        /// <returns></returns>

        public override string ToString()

        {

            return string.Format("ID:{0} Name:{1}", ID, Name);

        }

        #region IXMLSqlParameter Members

        /// <summary>

        /// Build XML Parameter string

        /// </summary>

        /// <returns></returns>

        public string GetXMLSqlParameter()

        {

            XElement customer = new XElement("facility");

            customer.Add(new XElement("facilityID"new XAttribute("value", ID)));

            customer.Add(new XElement("facilityName"new XAttribute("value", Name)));

            return customer.ToString();

        }

        #endregion

    }

}

 

 

Below I show the unit test that will initialize the above class, create a new record from it and then modify the newly created record:

 

 

[TestMethod]

public void CanCreateFacilityRecordEF()

{

    // Get configuration string (CnEF - base pulls from Web.Config)

    using (PatientDBEntities context = new PatientDBEntities(CnEF))

    {

        string MockCreate = "UnitTestFacility";

        string MockUpdate = "UPDATEUnitTestFacility";

 

        // TEST CREATE

        FacilityData data = new FacilityData { ID = 0, Name = MockCreate };

        // Call Facility Create/Update method (ID=0 is CREATE)

        context.Facility_CU(thisnew FacilityDataEventArgs { FacilityData = data });

        // Find the record we just created

        FacilityData recordExists = context.GetFacilityList()

            .FirstOrDefault<FacilityData>(f => f.Name == MockCreate);

        Assert.IsNotNull(recordExists, "Could not find CREATE record!");

        // TEST UPDATE

        data.ID = recordExists.ID;

        data.Name = MockUpdate;

        context.Facility_CU(thisnew FacilityDataEventArgs { FacilityData = data });

        recordExists = context.GetFacilityList()

            .FirstOrDefault<FacilityData>(f => f.Name == MockUpdate);

        Assert.IsNotNull(recordExists, "Could not find UPDATE record!");

    }

}

 

 

 

 

To simplify life my DAL is actually an Entity Framework context partial class - it follows:

  

 

 

namespace Data.WCF

{

    public partial class PatientDBEntities : ObjectContext

    {

        public ResponseEventArgs Facility_CU(
               object sender, 
               FacilityDataEventArgs e)

        {

            ResponseEventArgs args =
                new ResponseEventArgs { Message = "Updating Facility" };

            try

            {

                // Setup SQL XML Parameter

                object[] para = { new SqlParameter("xmlData",

                                              e.FacilityData.GetXMLSqlParameter()) };

                // Create command for stored procedure

                DbCommand command = this.CreateStoreCommand(
                      "usp_Facility_CU", CommandType.StoredProcedure, para);

                // Execute stored procedure

                using (Connection.CreateConnectionScope())

                    args.StatusID = command.ExecuteNonQuery();

            }

            catch (Exception ex)

            {

                args.Exception = ex;

            }

            return args;

        }

    }

}

 

 

All that remains is a stored procedure CLICK HERE FOR WEBCAST of stored procedure in action:

 

 

 

ALTER PROCEDURE [dbo].[usp_Facility_CU](@xmlData xml) AS

BEGIN

-- Retrieve FacilityID and FacilityName fields from XML parameter

declare @id int = @xmlData.value('/facility[1]/facilityID[1]/@value', 'int')

declare @name varchar(50) = @xmlData.value('/facility[1]/facilityName[1]/@value', 'varchar(50)')

-- If the ID already exists then update the record

if EXISTS (select @id from Facility where FacilityID = @id)

  BEGIN

    update Facility

      set FacilityName = @name

    where FacilityID = @id

    -- return ID for consistent return value

    select @id as PrimaryKey

  END

else -- If the ID doesn't exists then create it

 

  BEGIN

    insert into Facility (FacilityName) values (@name)

    -- return newly inserted record ID

    select @@IDENTITY as PrimaryKey

  END 

END

 


Tags: , , ,
Categories:


Actions: E-mail | Permalink |  Grammar/Typo/Better way? Please let me know

Entity Framework - how to use stored procedures (made easy) SP1

I'll show two ways to use stored procedures for updating data using the Entity Framework; first I'll show the "built-in" way and then I'll show my preferred way using Extensions (provided by Microsoft). 

Entity Framework with .NET 3.5 and VS 2008 SP1 

To easily configure your model to use stored procedures right click on the Entity and select "Stored Procedure Mapping". 

You'll then click to the right of the <Select Insert Function> and point it to the stored procedure that you want to use (all imported stored procedures will show in the list).   Do the same for the Update and Delete functions.

Note:  During compile VS complained because I didn't have an Insert function (my Update stored procedure handles the uniqueness constraints internally; it does both insert and update).   I had to create a stub stored procedure and update my model to make it happy

Once your stored procedures are mapped you can do the following to add and commit changes

[TestMethod]

public void User_CanAddUserToTable()

{

    User usr = new User();

    usr.ID = -1;

    usr.FirstName = "Jane";

    usr.LastName = "Doe";

    usr.LoginName = "JaneDoe";

    db.AddToUser(usr);

    db.SaveChanges(true);

}

I ran the test a second time to ensure my stored procedure was being run; I knew it was because I didn't have a duplicate record; I have built in uniqueness constraints on LoginName - inserts become updates if LoginName already exist.

Updating Entity Framework using EF Extensions

I won't duplicate what was a very comprehensive blog HERE which has download link.  Once I followed the steps provided I was able to update my Unit Test to look as follows:

[TestMethod]

public void User_CanAddUserToTable()

{

    object[] parameters =

    {   

        new SqlParameter("ID",-1),

        new SqlParameter("FirstName","John"),

        new SqlParameter("LastName","Doe"),

        new SqlParameter("LoginName","JohnDoe")

    };

 

    DbCommand command = db.CreateStoreCommand("uspUser_Update",

        CommandType.StoredProcedure, parameters);

 

    using (db.Connection.CreateConnectionScope())

    {

        int retValue = (int)command.ExecuteScalar();

    }

}

 


Tags: ,
Categories:


Actions: E-mail | Permalink |  Grammar/Typo/Better way? Please let me know

Entity Framework - The type 'xxxx' already contains a definition for 'x'

I was tasked by my manager to implement NHibernate for our next project.   I've heard a lot about it and was looking forward to learning NHibernate to meet requirements.  Seemed like most of the learning curve would be spent in XML configuration files;  in my research I stumbled upon fluent-nhibernate which is the route I would have taken had I not run into Scott Guthries comprehensive blog on .NET 3.5 SP1 (beta) - excerpt included at the end of this blog.

Since .NET 3.5 SP1 was now released it fell into the realm of the only rule my manager has for me - it must be released, no CTP or beta.   Having worked with the Web Service Software Factory (WCF) Modeling edition  it was an easy decision regarding whether to use the Entity Framework (EF) modeling tool over NHibernate.   I still plan to spend time with the source for fluent-nhibernate because it does some pretty impressive things that makes XML configuration go way but EF is what we'll be using.

The process was so simple, and quick, it was impressive.  From my DAL project I right clicked and selected Add,  New Item and selected "ADO.NET Entity Data Model".  From there I used the easy to follow setup wizard and within a few minutes had my EF container containing all of my database objects presented nicely in a Class Diagram.   I compiled and everything looked great.

I then added my second EF container and the process went just as quick/easy until I went to compile.  I had over 90 errors mostly reporting that xxxx.dtproperties already contains a definition for 'xx'.  Further investigation revealed that the format used is  <AppNamespace>.<TableName> and it just so happened that we had duplicate table names (and fields) in the two separate databases.  

Ideally <AppNamespace>.<DatabaseName>.<TableName> would have been nice but the solution will lend itself to a better design; each Database container will be in it's own DAL project along with it's unit test.   If however you did need/want to keep your databases in one project you can manually update the namespace after code generation to include the database name.

An excerpt from Scott's blog on the .NET 3.5 SP1 follows:

http://weblogs.asp.net/scottgu/archive/2008/05/12/visual-studio-2008-and-net-framework-3-5-service-pack-1-beta.aspx

ADO.NET Entity Framework and LINQ to Entities:

.NET 3.5 SP1 includes the new ADO.NET Entity Framework, which allows developers to define a higher-level Entity Data Model over their relational data, and then program in terms of this model.  Concepts like inheritance, complex types and relationships (including M:M support) can be modeled using it.  VS 2008 SP1 now includes built-in designer support to help with this modeling:

The ADO.NET Entity Framework and the VS 2008 Entity Framework Designer both support a pluggable provider model that allows them to be used with any database (including Oracle, DB2, MySql, PostgreSQL, SQLite, VistaDB, Informix, Sybase, and others).

Developers can then use LINQ and LINQ to Entities to query, manipulate, and update these entity objects.


Tags:
Categories:


Actions: E-mail | Permalink |  Grammar/Typo/Better way? Please let me know