Use of stored procedures in the ADO. NET object data framework

CSDN posts can be copied, saved, but lost! There are no more than a dozen pieces of documents. Let's take a look at it. You are too reluctant to edit it if you are not in the mood!

We have been using devart products for a long time and have excellent support for the stored procedures, entity sets, and functions of mainstream databases such as Oracle, mysql, and SQL Server. But recently I am a little clean and don't want to reference too many external components, so I tried to use the legendary "EF" (although the devart I used earlier was already used for EF, however, I am a lazy person. I am not used to understanding things that are too deep. I just want to know how to use them. So I am very vague about concepts ).

If you want to gain a deeper understanding of the structure of the ADO.net Entity Framework, there will be a lot of online content. I don't understand it here, and I won't make any comments!

In the following example, the mysql and mvc4 frameworks used in the database are semi-finished products. The goal is to show the provinces in China!

1. Select create ADO. NET object data model.

You need to make sure that the Mysql connection component is the latest. I am using 6.8.3. A too old version may not support the ado.net object data framework. As a result, there is no Mysql option when "changing the data source.

Download here: mysql-connector-net-6.8.3.msi

 

 

 

Next we will make a selection of the database, all the way to next.

 

The final Model Browser

 

This includes Object ing for all tables, which is very convenient to use.

2. Use the ing object of the data table to obtain all provinces

The file structure is as follows. Model1.edmx is the generated database ing, and HopeContext is the context used later (used as a database ).

 

 

Using System; using System. Collections. Generic; using System. Linq; using System. Text; using System. Threading. Tasks; namespace DAL {public class ProvinceDAL {public static List
 
  
GetProvince () {HopeContext context = new HopeContext (); // obtain all data in the province table var result = context. provinces. ToList (); return result ;}}}
 

3. Use stored procedures to retrieve all provinces

 

Here I will explain why stored procedures are used. In oracle, there is the concept of package. The major point is the object-oriented Implementation of databases. In the past, we used to focus on data processing on the DAL layer and logic processing on the BLL layer. However, in the project process, the requirements may change at any time. Modifying the stored procedure can easily achieve the functions we want, therefore, the business process and data analysis are all written in the Oracle stored procedure in the project, and are classified by package, so that the program can obtain the processed data. This mode has been applied in a mature project, and maintenance is indeed very convenient.

It seems that the stored procedure is supported only after mysql5.0 and above. If the unsupported version is not supported, upgrade the version by yourself.

Creating a stored procedure makes it very convenient for mysql to return the cursor in the stored procedure. The last sentence is select, which is much more convenient than oracle.

 

DELIMITER $ create procedure getprovince () BEGINSELECT id, p. name, 'test 'pcontent FROM provinces p; END $ DELIMITER;

A field is added to distinguish it from the province table.

 

Update model from database

Update the Stored Procedure

If the function is not displayed in Function Import, you can double-click the corresponding stored procedure to generate the function.

Unfortunately, ado.net object data does not support the complex type return values of stored procedures. This is also the motivation for writing this article, because I have solved this problem for a long time.

First, declare that no data display can solve the problem of Automatically Generating complex types of entities returned by the stored procedure.

Double-click "getprovince" under "Function Import"

The red circle shows that this data type is not supported, but it does not matter. Select "Create a new complex type" to modify the type name, select OK and press ctrl + s to save the changes. Save the changes to edmx ).

The two red boxes are automatically generated and corresponding.

I tested it. It is invalid to edit "getprovince_Result.cs. You must edit the file on the view page. The reason is unknown.

We need to add a scalar attribute in a complex type. The name must correspond to the return value in the stored procedure; otherwise, the generation fails.

 

Using System; using System. Collections. Generic; using System. Linq; using System. Text; using System. Threading. Tasks; namespace DAL {public class ProvinceDAL {public static List
 
  
GetProvince () {HopeContext context = new HopeContext (); // obtain all data of the province table through the Stored Procedure var result = context. getprovince (). toList (); return result ;}}}
 

Controller:

Using System; using System. collections. generic; using System. linq; using System. web; using System. web. mvc; using DAL; namespace Hope. controllers {public class HomeController: Controller {public ActionResult Index () {ViewBag. message = modify this template to quickly start your ASP.. net mvc application .; Var models = ProvinceDAL. getProvince (); return View (models);} public ActionResult About () {ViewBag. Message = your application description page .; Return View ();} public ActionResult Contact () {ViewBag. Message = your Contact information page .; Return View ();}}}

View:

 

 

@ Model List
 
  
@ {ViewBag. Title = home page;} @ section featured {
  
   
    
@ ViewBag.Title.@ViewBag.Message
   
   
For more information about ASP. net mvc, visit http://asp.net/mvc. This page providesVideos, tutorials, and examplesTo help you make full use of ASP. net mvc. If you have any questions about ASP. net mvc, visit our forum.
  }
  
 

   
  
@ Foreach (var item in Model ){
   
  
@ Item. name
   
  
}
  
 
 

Result