(Pdf. NET Framework example) ing any complex SQL queries into Entity classes

This article from: http://www.cnblogs.com/bluedoctor/archive/2011/08/10/2133983.html

 

In general, our ORM framework usesSingle TableOrViewMap to oneEntity classSometimes stored procedures are mapped to object classes. If you do not want to write the stored procedures, how can these complex SQL queries be mapped to object classes?

In fact, whether it is a single table, view, or stored procedure,SQL Server Table value functions, Custom SQL queries, or evenAny complicated SQL queryCan be expressed using an SQL statement. As long as our ORM framework can map the query results of SQL statements into Entity classes, it is very easy to use Orm. We use pdf. Net (pwmis data development framework) to explain this process.

 

1. First download and install a "pdf.. Net integrated development environment. For more information, see PDF. NET data development framework Integrated Development Tool ver 4.1 release, the following is the tool running interface:

2. on the "Data Connection" tab, select or create a connection group, and then add a connection (note the right-click menu) to open the connection, shows the databases, tables, views, and stored procedures under the connection, as shown in. Tools support various types of databases.

 

3. Create a New query, enter your SQL statement in the content area on the right, and press the "F5" key. If it is correct, the result grid is displayed.

So far, you can use this tool as a "query analyzer" that supports multiple databases. You can also extend its data to provideProgramTo support your own data sources.

4. In the "query window", right-click the pop-up menu and select "generate object class"

 

Then, a new window is displayed to set the object class generation:

Check "SQL query validity", enter the object class name to be mapped, and click "OK ".

 

In the "Property Browser", make some settings, including the file path andCodeAfter setting the language, click "preview" on the grid to display the following interface:

 

At this time, we can see the original appearance of the entity class we have generated. If you think it is not good, you can modify it and try again.

After closing the window, if you want to add more custom queries, click the "advanced" button to repeat the preceding steps and add a new query. The result is as follows:

 

Click Generate to generate two selected object class files.

Note: In addition to the object class file generated, a fixed-name object class configuration file entitysqlmap. config is generated. The following describes its usage.

 

5. After the above steps, our object class file has been generated, and some preparation work is done below to see how to use it in the project.

First, open the entitysqlmap. config file of the object configuration file for the custom query. Let's modify it:

Modify the content of the file and replace all the original funds with the SQL query parameter name @ jjdm, as shown in.

Next, add the three files to our model project:

 

 

Next, set entitysqlmap. the config file is compiled as an embedded resource file. Note that if you are using a winform program, you can skip this step, but the file must be included during the program running. Therefore, we recommend that you compile the config file as an embedded file, this provides better security. In addition, this file and the object class file should be placed under the same directory.

 

Finally, let's look at how to use such entity classes in the project:

The object class of the custom query is similar to the ordinary PDF.. Net object classes are used in a similar way. They all require oql expressions for operations. For example, you can select the field to be used and set the where condition. For convenience, only oql is called. select () method to select all queried columns.

Note: If you need to use parameters for your SQL query statement, such as local @ jjdm, you should also set the initparameters attribute of oql, as shown in.

Finally, you can query it directly. Anyone who has used the pdf. NET Framework knows that there is only one line of code, as shown in this example:

 

List <jjdanganinfo> List = entityquery <jjdanganinfo>. querylist (Q );

 

6. After the above steps, our code can be run. The current instance uses an ASP. NET + MVC 2.0 project. The following code runs:

For more information about how to use a pdf. Net custom query object class instance, see

Sqlserver accounts for more than 500 mb of memory, and the original program cannot query more than million data at a time, recording the solution to this problem.

 

In addition to ing SQL statements to object classes, you can also use PDF. net built-in SQL-MAP technology to realize object class ing, just like ibaits, but much simpler than ibaits, For details, see:

 

SQL-MAP usage stored procedures for PDF. NET data development framework

 

 

Note: The Custom query described in this article is in PDF. net3.5 and later support, the instance code must be in PDF.. Net 4.1 or above, PDF. net itself only needs. NET 2.0 support, the latest version of the Framework is not publicly released, for more information about the framework, please see the official website Introduction: http://www.pwmis.com/sqlmap