To write a complex query to a query configuration file--SOD Framework's SQL-MAP technology brief

Introduction

See a popular blog today. NET Senior Engineer face questions of SQL, asked to find the highest score of each department, and by the department number, the student number in ascending order. This query is more complex, but also more typical, since the use of ORM, a long time did not write SQL statements, so I studied the next, I also wrote a:

  withCte1 as ( SelectStu.deptid, D.depname, Stu.stuid, Stu.stuname, Score_sum. Allscore fromdbo. Student StuInner Join(SelectStuid,SUM(score) asAllscore fromDbo. ScoreGroup  bystuid) Score_sum onStu.stuid=Score_sum.stuidInner JoinDbo. Department D onStu.deptid=d.depid)SelectCte1.*  fromcte1Inner Join(SelectDeptID,Max(Allscore) Maxscore fromCte1Group  byDeptID) M onCte1. Allscore=M.maxscore andCte1.deptid=M.deptidOrder  byCte1.deptid, Cte1.stuid

We often encounter similar complex queries in our work, but the common practice is to write it into the BLL program, and some students like to write in the stored procedure, what is the problem?

• Write to the program,
  • Easy to form the habit of splicing SQL,
  • and the SQL statements scattered to the various parts of the program, poor maintenance,
  • Security issues may occur;
• Write to the stored procedure,
  • If more is not good maintenance,
  • And program calls are not as convenient as calling SQL statements directly,
  • When deployed, these stored procedures are deployed, and when the system is on line, it is often forgotten to update one or two stored procedures.

If all the SQL statements are centrally managed in a configuration file, then program writing and maintenance, publishing is much more convenient, even according to the SQL configuration file, write a code generator, automatically generate the DAL layer code. The famous ibatis,mybaits.net is such a function, but it defines a complex set of rules, configuration is very complex, the project may be 80% of the time to write query configuration, if there is no code tool development workload is very large.

sql-map--managing SQL queries centrally

The Sql-map function of the pdf.net SOD framework draws on the idea of ibaits, but greatly simplifies its configuration and provides a code generation tool that automates the generation of DAL code, following its working process:

The SOD framework provides the Sqlmap Profile Manager to manage and write the SQL configuration file, then generates the DAL layer code from the code generator, which is then handed to the SODK framework for execution.

Write the SQL configuration file, if done by the above-mentioned Configuration tool, you can refer to this article:

Pdf. NET Sql-map use diagram

But this program is too old, has not been updated, the tool is:

http://ft.codeplex.com/releases/view/65308

Tools are included in this "integrated development tool" and can be manipulated via menus or icons. Here is the interface for the integrated development tool to open the database and execute the query for this article:

SQL-MAP configuration management tools are not very useful, beginners learning concepts can try, but to be proficient in the application, it is recommended to use VS to write this SQL configuration file more convenient. This article will give you a detailed introduction to this process.

First, write Sql-map configuration file 1.1, build Sql-map Application project

As shown, create a new Sqlmapdemo project, add an XML file to it, name it sqlmap.config, and then locate the "Integrated development Tools" installation directory above and add the Sqlmap.xsd file. This file is important, and it's the key to having Smart tips for writing XML files in vs.

After adding this XSD file to the project, you also need to put the following content

Xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance"    xsi:nonamespaceschemalocation= "Sqlmap.xsd"  

Note: For more information, see PDF. NET (Pwmis Data Development Framework) SQL-MAP objectives and specifications.

1.2 Using XML Smart hints

Add the above to the Sqlmap.config file just now, so you can enter angle brackets, or enter a space, and the smart tip content will come out as follows:

This is to add the complete Sqlmap.config file, the file is fully handwritten, with the help of smart tips, writing is still very convenient. Note the contents of the properties of each node in the configuration file, please see the above linked file. Note that you use CDATA to write SQL queries.

Ii. using code Generator 2.1 To configure the code generator

Open the Pdf.net Integrated development tool, select the menu "Configuration"--sql-map code generator configuration, the following interface appears:

In the edit area, the right mouse button menu, enabled with editing, if it is the first configuration, be sure to delete the Chinese comment in the configuration file , otherwise the profile format is not recognized.

The main configuration here is the full path of the Sqlmap.config file, the directory of the output code, and the namespace.

When editing is complete, on the right mouse button menu in the Edit area , enable the "Save Files" feature .

2.2 Generating Code

If the above configuration is correct, click on the icon of the second toolbar, you can automatically call the PDFCodeMaker.exe program, read the configuration file information, automatically generate the DAL code file, such as:

6

After that, we add the DAL code file just generated into the project, such as.

Iii. How to use the Sqlmap configuration file 3.1 sqlmap configuration file

There are 2 ways to use the Sqlmap configuration file,

• Specify the file path inside the appseting
• Embedding into assembly files

In this example, embedded compilation is used so that the SQL statement is more secure, but not as easy to use as a standalone file. Such as:

This way, the name of the resource file is "assembly default namespace." Directory name. File name ", here's an example of SqlMapDemo.SqlMap.config, because there is no directory name under the directory.

Note that to use the embedded SQL configuration file correctly, you must also indicate in the file "the assembly name of the embedded file , the resource file name," as shown in the section of this example, red border markup:

After the Sqmap.config file has been modified, start the PDFCodeMaker.exe program here, regenerate the Dal file, and add the check-in file settings as follows:

3.2 Generating Sqlmapdal files

The check found that the namespace of the Dal class file is incorrect, where the RootNamespace section of the configuration file is modified, such as:

To regenerate the Dal class file, let's look at the contents of the last generated Sqlmapdal class:

//refer to the assembly before using the program: Pwmis.core, and do not use Pwmis for the namespace prefixes defined below, for more information, seeHttp://www.pwmis.com/sqlmap // ========================================================================//Copyright (c) 2008-2010 company name, All rights Reserved.// ========================================================================usingSystem;usingSystem.Data;usingSystem.Collections.Generic;usingPwmis. Datamap.sqlmap;usingPwmis. datamap.entity;usingPwmis.common;namespacesqlmapdemo.sqlmapdal{/// <summary>///file name: TestSqlMapClass.cs///class Name: Testsqlmapclass///Version: 1.0///creation time: 2015/5/12 17:16:32///Purpose Description: Sql-map sample test program///Additional information: This file is generated automatically by Pdf.net Code Maker, please back up before modifying! /// </summary> Public Partial classTestsqlmapclass:dbmapper {/// <summary>    ///default constructor/// </summary>     PublicTestsqlmapclass () {mapper.commandclassname="Testgroup"; //Currentdatabase.databasetype=database.enumdatabasetype.sqlserver;Mapper.embedassemblysource="Sqlmapdemo,sqlmapdemo.sqlmap.config";//the Sql-map file embeds the assembly name and resource name, if more than one Sql-map file is suggested here.     }    /// <summary>    ///find the highest score for each department, and the student number in ascending order by department number/// </summary>    /// <returns></returns>     PublicDataSet querystudentsores () {//Get command InformationCommandInfo Cmdinfo=mapper.getcommandinfo ("Querystudentsores"); //Execute Query            returnCurrentdatabase.executedataset (currentdatabase.connectionstring, Cmdinfo.commandtype, CmdInfo.CommandText,NULL); //}//End Function}//End Class}//End NameSpace

Sqlmapdemo.sqlmapdal
Iv. running the SQL-MAP program 4.1 Configuring the connection string

The Sql-map Dal class program is still executed by the Adohelper object, where an application configuration file is added:

<?XML version= "1.0" encoding= "Utf-8"?><Configuration>  <connectionStrings>    <Addname= "Default"connectionString= "Data source=.;i Nitial catalog=testdb;integrated security=true "ProviderName= "SQL Server"/>  </connectionStrings></Configuration>

4.2 Running the program

Then, you can write the following code to test the SQL-MAP program:

To set a breakpoint, let's take a look at what the Mapper object of the "sql-map Dal object " is:

Enter test in a timely window. Mapper, enter, you can see the above content. This shows the location of the Sql-map embedded file that you just configured and the unique path to the file: Sqlmapfile property.

Here are the detailed results:

Test. Mapper{pwmis. DataMap.SqlMap.SqlMapper}    _commandclassname: "Testgroup"    _database: {pwmis. DataProvider.Data.SqlServer}    _databasetype:sqlserver    _embedassemblysource: "Sqlmapdemo, SqlMapDemo.SqlMap.config "    _parachar:" @ "    _paramsmap: {pwmis. Common.parammaptype[0]}    _paraslenth:0    _resultclass:valuetype    _resultmap: "    _sqlmapfile:" @R:// Sqlmapdemo,sqlmapdemo.sqlmap.config "

Conclusion

is the test completely correct, is it simple to use the sql-map technique of the sod frame?

All you have to do is modify the SQL statements in the file.

Once configured, one key generation!

This is the feature of the Sql-map technology of the sod frame! Combined with the ORM function of the sod frame, it can replace the mybatis.net completely.

Thank you for your attention pdf.net sod frame, this is an open source free framework , more information please crossing network Http://www.pwmis.com/sqlmap

Framework Open Source Project address: http://pwmis.codeplex.com, this project sample code has been checked into the CodePlex, you can use SVN download.

To write a complex query to a query configuration file--SOD Framework's SQL-MAP technology brief