Part 3: Invoke store procedure in ADO.NET Entity Framework 4
-
Come with Entity Framework extensions
-
Talk about Materializer and DbCommand
We need a Materializer for build a static function that mapping a model contract with the result return from the store procedure. And we need the DbCommand for executing a store procedure. That is all things I knew about it. For more information, go to http://blogs.msdn.com/b/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx. I think a expert at here will explain easily understanding than me. The theory is enough; I should focus on my work.
-
Build Contract Model for storing result that return from Store-Proc
I must build the Contract Model for mapping result that invoke the store procedure
public class SumarizeCategoriesAndNews
{
/// <summary>
/// Gets or sets the category id.
/// </summary>
/// <value>The category id.</value>
public int CategoryId { get; set; }
/// <summary>
/// Gets or sets the name of the category.
/// </summary>
/// <value>The name of the category.</value>
public string CategoryName { get; set; }
/// <summary>
/// Gets or sets the category created date.
/// </summary>
/// <value>The category created date.</value>
public DateTime CategoryCreatedDate { get; set; }
/// <summary>
/// Gets or sets a value indicating whether [category is delete].
/// </summary>
/// <value><c>true</c> if [category is delete]; otherwise, <c>false</c>.</value>
public bool CategoryIsDelete { get; set; }
/// <summary>
/// Gets or sets the new id.
/// </summary>
/// <value>The new id.</value>
public int NewId { get; set; }
/// <summary>
/// Gets or sets the news title.
/// </summary>
/// <value>The news title.</value>
public string NewsTitle { get; set; }
/// <summary>
/// Gets or sets the content of the news.
/// </summary>
/// <value>The content of the news.</value>
public string NewsContent { get; set; }
/// <summary>
/// Gets or sets the news created date.
/// </summary>
/// <value>The news created date.</value>
public DateTime NewsCreatedDate { get; set; }
/// <summary>
/// Gets or sets a value indicating whether [news is delete].
/// </summary>
/// <value><c>true</c> if [news is delete]; otherwise, <c>false</c>.</value>
public bool NewsIsDelete { get; set; }
}
Next, I built the CSPDbContext partial class with named CSPDbContext.StoreProc and its code here:
public partial class CSPDbContext
{
private static readonly Materializer<SumarizeCategoriesAndNews> s_sumarizeCatAndNewsMaterializer = new Materializer<SumarizeCategoriesAndNews>(r =>
new SumarizeCategoriesAndNews
{
CategoryId = r.Field<int>("CategoryId"),
CategoryName = r.Field<string>("CategoryName"),
CategoryCreatedDate = r.Field<DateTime>("CategoryCreatedDate"),
CategoryIsDelete = r.Field<bool>("CategoryIsDelete"),
NewId = r.Field<int>("NewId"),
NewsTitle = r.Field<string>("NewsTitle"),
NewsContent = r.Field<string>("NewsContent"),
NewsCreatedDate = r.Field<DateTime>("NewsCreatedDate"),
NewsIsDelete = r.Field<bool>("NewsIsDelete"),
});
/// <summary>
/// Gets the sumarize category and news.
/// </summary>
/// <returns></returns>
public SumarizeCategoriesAndNews GetSumarizeCategoryAndNews()
{
// The CreateStoreCommand utility method simplified creation
DbCommand command = this.ObjectContext.CreateStoreCommand(
"SumarizeCategoryAndNews",
CommandType.StoredProcedure,
null);
SumarizeCategoriesAndNews catAndNews = s_sumarizeCatAndNewsMaterializer
.Materialize(command) // Returns typed results given a DB command.
.SingleOrDefault(); // We expect at most a single match for this stored procedure.
return catAndNews;
}
}
As you see, I invoked the store-proc with named [SumarizeCategoryAndNews]. I think it is clear with anyone that read this code.
And the Repository, we only need called it as:
public SumarizeCategoriesAndNews GetSumarizeCategoriesAndNews()
{
Contract.Assert(DbContext != null, "DbContext is null");
var temp = DbContext as CSPDbContext;
return temp.GetSumarizeCategoryAndNews();
}
Finally I do unit testing for this function:
[TestMethod()]
public void GetSumarizeCategoriesAndNewsTesting()
{
var result = CategoryRepository.GetSumarizeCategoriesAndNews();
Assert.IsNotNull(result);
Assert.AreEqual(result.CategoryName, "test");
Assert.AreEqual(result.CategoryIsDelete, false);
}
Notes:
You must run the Databasescript.sql to created the store-proc after you created successful the database schema
You must set the command
Database.SetInitializer<CSPDbContext>(newRecreateDatabaseIfModelChanges<CSPDbContext>());
//Database.SetInitializer<CSPDbContext>(new DontTouchMyDatabase<CSPDbContext>());
at Testing base class for created new database schema
More developing:
Read further: