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(this, new 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(this, new 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