Using stored procedures with ADO.NET entity framework

最新推荐文章于 2024-09-15 08:03:51 发布

weixin_30239339

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量64

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/JosephLiu/archive/2010/02/17/1668853.html

版权

Introduction

At Info Support we work with stored procedures in SQL 2000/2005 to access our data, this has a couple of advantages, like decoupling the interface from the actual table structure, reduce the number of errors in queries, etc. As you can imagine, I want this good practice to continue even when using ORM frameworks. Not all of them allow this, but luckily ADO.NET entity framework does. Here's how.

The EDMX file

Since the last CTP Microsoft has begun using the edmx filetype to store the store specification model (originally the SSDL file), the concept - store mapping model (originally the CSDL file) and the conceptual model (originally the CSDL file). The new file format is more bulky because you have more XML on your screen when editing it, however it does make managing the whole model a bit easier.

The code I use here works with both the new file format and the old format, however you the place where you make the changes varies a bit. In the old format you have to change the file that belongs to the part of the model that you are editing, with the new format it's all on one big pile.

Modifying the store model

The first thing you need to do to use stored procedures for modifying data in the database is to define the stored procedures in the store model (or the SSDL file in the old format). Adding a new function is done by adding a new <Function> element to the Schema element of the database where the stored procedure is defined.

There are two important attributes to configure here, first the name of the function within the model and second the name of the actual stored procedure. Configuring a function will not actually make the model use the function for modifying the data, it just makes the function visible to the mapping model, where you will be using the function. In the store model you can specify which parameters the function has, the type and how to use the parameters (input,output or bi-directional).

TIP: Mark the function with IsComposable="false", this way the framework knows it's a stored procedure and not a function, which are composable within queries.

Mapping types

After you specified how the functions look in the store model you can start modifying the mapping to use the functions for inserting, updating and deleting entities. Mapping functions to the insert,update and delete operations is done using a ModificationMapping element in the EntityTypeMapping element of the mapping model. A sample of these elements is shown below:

The most basic form of a function mapping is to just map the parameters of the function to the properties of the type that you need as input of the stored procedure in the database. However you can do a lot more. For example, in an insert stored procedure it's pretty common to see a piece of code to return the inserted entity back to the user with the generated fields. You can map this back to the entity in the conceptual model by supplying a result binding for each fields in the returned resultset within the InsertFunction/UpdateFunction/DeleteFunction mapping. E.g.

For a lot of applications there are several associations that are mapped in the conceptual model. It's possible to use these associations in the function mapping to, these are mapped using the AssociationEnd element. Within this element you specify which relation you want to be mapped and how it should be navigated. Next you specify which field of the relation should be mapped to a parameter in the function. The ADO.NET entity framework will automatically navigate the relation and find the correct value for you. The framework will also use this information to generate the calls to the stored procedures in the correct order when completing a unit-of-work. Mapping a relation to parameters can be useful to map entities that have a one to one relation with another entity and both entities have to exist together.

Implementing optimistic concurrency

When you have setup the mapping correctly you will now see in the SQL profiler that ADO.NET is now using the stored procedures for inserts, updates and deletes. However there are some more features that have not yet been addressed. One of them is optimistic concurrency. There are several methods that you can use to implement this. One of them is using original and current values to detect if a record in the database has been changed when you last retrieved it. This can be implemented by modifying the ModificationFunctionMapping element a bit. Assuming your stored procedure has parameters prefixed with Original_, you can do this by creating the mapping like this:

You also need to include the RowsAffectedParameter. In the stored procedure you set this parameter to the number of rows affected, ADO.NET entity framework will use this to detect concurrency problems. If the value of this parameter is greater than zero the operation succeeded, otherwise the operation failed and the client is notified of the problem.

What about selections?

Yep even selections can be performed using stored procedures in ADO.NET entity framework. You simply map the stored procedure in the store model, but instead of mapping it in the mapping file you import it in the conceptual model. This causes a new method to be generated on the container class that will call the stored procedure in the database. A sample of a function import looks like this:

<Schema Namespace="WeSchedule247Model" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
<EntityContainer Name="WeSchedule247Entities">
…
<FunctionImport EntitySet="Task" Name="GetTasksByIteration" ReturnType="Collection(Self.Task)">
<Parameter Name="IterationId" Mode="in" Type="Int32"/>
</FunctionImport>
…

Function imports can either return scalars, mapped entities or simply nothing depending on your needs. When you want to use mapped entities it's important that the columns returned in the resultset have the correct name, otherwise you end up with a load of exceptions telling you that the resultset could not be mapped to the requested entity.

The big difference between function imports and using LINQ is that LINQ doesn't use stored procedures for selecting the data whereas function imports solely use stored procedures and cannot use dynamic queries. I personally like the fact that you can use LINQ to query the entities, that way I can do very complex stuff without even having to write a single line of SQL to perform my selection. I will not be using the function imports a lot, unless I have to.

One of the things you have to keep in mind is whether you have an existing database where all procedures are already defined or whether you can start from scratch. Also you need to be aware of the fact that execution plan caching in stored procedures can help to further optimize performance and can be a better option when you need to execute the same query a lot. Otherwise LINQ is a very good option when using ADO.NET entity framework.

Last things

ADO.NET Entity framework is a powerful framework that allows developers to work with a database in an object oriented way without having to write the complex object relational mapping code. The possibility to use stored procedures makes it even better, because this allows developers to adhere to good practices when it comes to accessing data in the database.

One last tip: ADO.NET Entity framework requires you to use stored procedures for modification for all related entities. So once you start using stored procedures in one spot you have to use them everywhere. Although it's not a problem when you want software that is standardized and maintainable, it's a thing to keep in mind when you are experimenting with the framework.

Posted 11-09-2007 21:29by willemm Filed under: .NET 3.5, ADO.NET

转载于:https://www.cnblogs.com/JosephLiu/archive/2010/02/17/1668853.html