To write a complex query to a SQL configuration file--sod Framework 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:

 With Cte1As(SelectStu.deptid, D.depname, Stu.stuid, Stu.stuname, Score_sum. AllscoreFromDbo. Student StuInnerJoin (Select Stuid,SUM (Score)As AllscoreFROM dbo. ScoreGroupBySTUID) Score_sumOn Stu.stuid=Score_sum.stuidInnerJoin dbo. Department DOn Stu.deptid= D.depid) select cte1.from cte1 inner join (select DeptID, max (Allscore) Maxscore from cte1 byon cte1. Allscore = m.maxscore and cte1.deptid=m.deptid order by< Span style= "color: #000000;" > Cte1.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.

In addition, theSQL statement either written to the program or written in the stored procedure, there is a great disadvantage, the program can not cross the database platform!

If all the SQL statements are centrally managed in a configuration file, then the program writing and maintenance, publishing is much more convenient, but also easy to cross the database platform, 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.

Of course, unlike MyBatis, the SOD framework does not claim that the query of the project is written to the Sqlmap file with SQL, it is recommended that you only write the complex SQL query to Sqlmap configuration file , general project such complex query is about 20%, For 80% of the general query, using ORM can, framework at the same time support ORM and SQLMAP technology, which is a major feature of the sod frame!

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.

Note: If you want to execute a query that obtains a result set, you need to use the Select node in the command node of the Sql-map file, and you need to indicate the type of resultclass, which has the following optional values:

1. ValueType-The result is a value object, such as a query single-value result such as Int,string
2. DataSet--DataSet, default
3. Entityobject--Single Entity class object
4. Entitylist--List of entity classes

Note: If you specify resultclass= "Entityobject"/resultclass= "Entitylist", then you need to declare the Resultmap attribute at the same time to indicate the specific type to which the result will be mapped, such as:

resultclass= "Entityobject" resultmap= "Xxxnamespace.yyyentityclass"

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

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 in the Appseting configuration section of the application configuration file
• Embed the assembly file as a resource file using

In this example, embedded compilation is used so that the SQL statement is more secure and supports the use of multiple Sqlmap profiles in one project, but is less convenient 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>PublicPartialClassTestsqlmapclass: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 information CommandInfo cmdinfo=mapper.getcommandinfo ( Span style= "color: #800000;" > "querystudentsores ) ; // Execute query return Currentdatabase.executedataset (currentdatabase.connectionstring, Cmdinfo.commandtype, CmdInfo.CommandText, null); //} //end function}//end class}//end NameSpace              

Sqlmapdemo.sqlmapdal3.3 Team Development

Team development often involves file modification conflicts, SQLMAP configuration files will also encounter, if you do not modify the same command configuration, with the source Code Manager automatic merging function is not a problem.

In the case of exclusive check-out management, one developer uses the Sqlmap file, and the other one waits, but the framework provides a workaround to solve the problem.

• Divide the DAL assembly by function module, one sqlmap.config file per DAL assembly, select embedded compilation;
• Build multiple Sqlmap.config files on a DAL program project and choose Embedded Compilation.

Note: It is recommended that each developer be responsible for the management of a sqlmap.config file and then configure their own PDFCodeMaker.exe.config

3.4 Switching databases

Add a new <script type= "" node in the same Sqlmap.config file, such as Oracle, and then overwrite the SQL query of the original database type with the current type of SQL query statement. The program automatically selects the correct <script type= "" node based on the actual type of Dbmapper Adohelper, thus completing the database switchover and implementing the cross-database functionality of your application system.

In addition, you can also create a sqlmap.config file, which specifically writes the new database below the SQL statement, our previous bank project, that is, from SQL Server ported to PostgreSQL.

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> <add name = "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:

 Classprogram {Staticvoid Main (String[] args) {Console.WriteLine ("Pdf.net Sod Frame Sqlmap Example program--2015.5.12-------"); Console.WriteLine ("Http://www.pwmis.com/sqlmap---------"); SqlMapDemo.SqlMapDAL.TestSqlMapClass test =NewSqlmapdal.testsqlmapclass ();//adohelper db = new SQL Server (); // Take the last connection configuration Adohelper db =  Mydb.getdbhelper (); //sql-map DAL will also take the last connection configuration by default, so the following line of code can comment //test. CurrentDatabase = db; DataSet data = test. Querystudentsores (); Console.WriteLine ( " query to record count: {0} 0 " test complete.  "); Console.read (); } } 

The results run as follows:

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.

This program has been provided with a separate download: Sql-map Demo

To write a complex query to a SQL configuration file--sod Framework Sql-map Technology Brief