Pdf. Net (pwmis data development framework) SQL-MAP goals and specifications

SQL-MAP goals:

1. Centralized management of SQL statements. All SQL statements are managed in a dedicated configuration file;
2. By replacing the SQL configuration file, you can smoothly switch the database to another database, for example, migrating from an Oracle Application to sqlserver;
3. Written by DBAProgramFor complex queries, DBA write SQL statements and stored procedures more secure and more efficient, SQL-MAP tools allow DBA can also write. Net program;
4. CodeAutomatic Generation. Because many programming features are specified in the SQL configuration file, you can use a dedicated tool to map the configuration file to. Net code;

 

For introduction to the. NET data development framework (including instance program download), see:

Http://www.pwmis.com/sqlmap

 

For more information about the use of SQL-MAP code tools (illustration), see:
Http://www.pwmis.com/sqlmap/toolshelp.htm
----------------------
Specification of SQL-MAP:
1. General Configuration:

Sqlmap configuration section

 EmbedassemblysourceAttribute: name of the assembly and resource file to be embedded in this article. format:
Assembly name, default namespace. File Name. Extension

If you want to change the configuration fileCompile as an embedded file, Please specify this value. This configuration mainly guides the code generator to generate corresponding code information.

 

Xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xsi: nonamespaceschemalocation = "sqlmap. XSD"

Adding this sentence here can enable smart prompts when writing the sqlmap configuration file. The sqlmap. XSD file must exist in the current directory.

 

2. Node Type

Data CRUD operation type for SQL-MAP (CommandclassSubnode type ):
Select/read: Select data operations ;,
Update: update data;
Insert/create: new data operation;
Delete: delete data.

3. node attributes:

Note: <> indicates mandatory attributes, and [] indicates optional attributes.
<Commandname>: name of the query, which corresponds to the method name in the sqlmap data entity class;
<Commandtype>: query command type, value: Text, storedprocedure, tabledirect
[Parameterclass]: query parameter class, represented as a parameter class in sqlmap;
<# Paraname [: system. Type [, system. dbtype [, size [, parameterdirection [, precision, scale] #>:
Paraname: name of the parameter corresponding to the query statement. If the name prefix is @, this parameter is a replacement parameter and will be ignored;
System. Type: (optional) Name of the CSL-compliant type (CLT;
System. dbtype: (optional) enumeration that complies with dbtype, such as ansistring;
Size: optional, indicating the parameter length;
Parameterdirection: (optional) Enumeration value of the input/output type of the parameter.
Precision: Optional. It indicates the data precision of the parameter, which is usually used for the decimal type.
Scale: (optional) indicates the decimal point of the parameter. It is usually used for decimal type.

{Note:

Commonly used CLT: byte, int16, int32, int64, single, double, Boolean, Char, decimal, string
[General Type System (CLT): MS-help: // Ms. vscc.2003/ms. msdnqtr.2003feb. 2052/cpguide/html/cpconthecommontypesystem.htm]
[Dbtype enumeration: MS-help: // Ms. vscc.2003/ms. msdnqtr.2003feb. 2052/cpref/html/frlrfsystemdatadbtypeclasstopic.htm]
}

<Resultclass>: SELECT query result type,
If a single-value query is returned, specify it as resultclass = "valuetype ";
If multiple row result sets are returned, you can specify resultclass = "dataset", which will be filled in as system. Data. dataset;
You can also specify resultclass = "entityobject" to fill the result in the object class;
You can also specify resultclass = "entitylist" to fill the result in the object class list (SET.

Note: you do not need to specify this attribute for the insert/update/delete operation type.

<Resultmap>: only valid in resultclass = "entityobject"/"entitylist", indicating ing of object query results;
It usually indicates that the ing of a single row (multiple rows can also be returned) record to a data entity object;
For example, to film a result set to a custom object class named userinfo, you must use the full name of the class:
Resultclass = "entityobject" resultmap = "mydal. userinfo"

 

[Sqlpage]: whether to allow sqlmap paging. The default value is false. This attribute is only used by the code generator. However, the current Code Generator does not provide this function.

After this attribute is enabled, an array of paging information needs to be added to the sqlmapdal class method and the setpageinfo method of the commandinfo object is called, the following example code (the red part is the code to be added ):

 

///   <Summary>
/// Return transaction details (with pagination)
///   </Summary>
///   <Param name = "wherestring"> Query Conditions </Param>
///   <Param name = "pageinfo"> Set the page information, pageinfo [0] = number of records, pageinfo [1] = page number, pageinfo [2] = page size </Param>
///   <Returns> </returns>
Public Dataset gettradelist ( String Wherestring,   Int [] Pageinfo )
{
// Obtain Command Information
Commandinfo cmdinfo = Mapper. getcommandinfo ( " Gettradelist " );
// Execute parameter replacement
Using info. setparametervalue ( " Wherestring " , Wherestring, enumparamtype. replacedtext );
Raise info. setpageinfo (pageinfo );
// Execute Query
Return Currentdatabase. executedataset (currentdatabase. connectionstring, using info. commandtype, using info. commandtext, Null );
//
} // End Function

Note: You can add an overload for your existing sqlmapdal method and put the overloaded code intoPartial filesSo that you don't have to worry about the impact of the code generator.

4. version record:
Ver 1.0.2 modification:
Parameters with the same name are supported. If there are multiple parameters with the same name in the script, process the first parameter first and ignore the subsequent parameter definitions with the same name;
The parameter "string replacement" is supported to support the construction of complex dynamic SQL statements. The parameter names are separated by %,
For example, where # % wherestring % # Or where # % wherestring % #, the system ignores the blank characters.

Ver 4.0 modification:
<Sqlpage>: When sqlpage = "true" is used, the code generator generates the Dal code that supports paging. The specific process is based on the original parameters.
Add an optional parameter Params int [] pageinfo, and add the raise info. setpageinfo (pageinfo) Statement to the method body.

SQL-MAP Generation Tool

1. Use XSD in vs ide:

According to this specification, an XSD is written to check the configuration file format. File address:

Http://files.cnblogs.com/bluedoctor/SqlMap.xsd.xml

The blog does not allow you to upload files in the XSD format. Therefore, you can delete the. xml suffix after Downloading this file.

When writing the SQL-MAP configuration file, in vs2008 menu "XML"-> "architecture", select this file. In this way, you can write a configuration file with a smart prompt.

Note: If you have downloaded the pdf. Net integrated development tool, the sqlmap. XSD file is also available under the installation directory of the tool.

2, using the SQL-MAP Configuration Manager:

This is a visualized editing program, which can be found in the pdfnet integrated development tool or in the sample package of previous versions.

 

Is a sqlmap configuration file and program structure:

In this example, sqlmap. the config file is in the sqlmapdal directory. Therefore, when this file is compiled in a sneak peek, its full name in the Assembly is "default namespace of the Assembly. sqlmapdal. sqlmap. config ", note that this name is case sensitive. The "groupstatistics. repostitory" section in front of embedassemblysource is the name of the current Assembly.