CYQ. Data lightweight Data layer V3.5 release-[enable support for running multiple databases with the same statement]

Preface:

After the official release of V3.0, it has been another 20 days since the Refresh. With the development of the "Autumn version blog", the framework has never been stopped and is moving forward !!!

Currently, blogs are being developed slowly ~~~ I spent a lot of time watching cartoons recently. Haha ~~~~

The following describes the V3.5 Update and the new syntax content not introduced in V3.0.

 

General description of this version upgrade:

1: Modify access Date and Time Processing [the previous value is only Date, no Time]

2: Improves the where condition keyword Filtering for all executed statements and enhances security.

3: The transaction RollBack method is added for MAction, and the transaction method consistent with MAction is added for MProc.

4: MDataTable: added the static LoadFromJson method, which allows you to restore data from a json string to an MDataTable.

5: Detailed syntax for Xml operation in the xml namespace to improve exception handling.

6: multi-database support and custom syntax Introduction

 

Detailed explanation of the key points below

 

I. Description of where condition keywords for external input

 

1: The Framework processes all SQL Execution statements with strict keywords for external where conditions, and directly rejects SQL injection from the underlying layer.

Keyword:

{"Select", "master", "delete", "drop", "update", "truncate", "create", "exists", "insert ", "asc (", "while", "xp_cmdshell", "add", "declare", "exec", "ch", "ch (", "delay ", "waitfor", "sleep "};

In other words, the external where condition cannot be used with subqueries similar to the following, for example:

MAction action = new MAction ("Users ");
Int count = 0;
Action. Select (1, 10, "where ID in (select ID from xxx)", out count );

Because the select statement is filtered out, the query results will fail, and other keywords are the same.

 

Solution?

You can use the custom Table Syntax:

MAction action = new MAction ("(select * from Users where id in (select id from xxx) v ");
Int count = 0;
Action. Select (1, 10, "", out count );

The system only performs basic filtering on user-defined tables and supports multi-database conversion. Therefore, when external conditions contain subqueries and filter keywords, you can consider converting them into user-defined tables.

Ii. added the LoadFromJson method to the MDataTable.

 

Example:

MAction action = new MAction (Table Name );
Int count = 0;

String json = action. Select (, "", out count). ToJson (); // convert it to a json string

MDataTable table = MDataTable. LoadFromJson (json); // restore data from json to MDataTable;

List <entity> entityList = table. ToList <entity> (); // convert the List of generic entities from the MDataTable.

 

Iii. syntaxes supported by multiple databases

Note: In order to execute the same statement in multiple databases, the keyword function parsing description is pushed.

 

Currently, the system defines the following keywords:

Public class DalValue // namespace CYQ. Data. DAL
{
/// <Summary>
/// Sort the Bit type [Yes/No]
/// </Summary>
Public const string Desc = "[# DESC]";
Public const string Asc = "[# ASC]";
/// <Summary>
/// Condition value for Bit type [Yes/No]
/// </Summary>
Public const string True = "[# TRUE]";
Public const string False = "[# FALSE]";
Public const string Len = "[# LEN]"; // length
Public const string Substring = "[# SUBSTRING]"; // substr
Public const string GetDate = "[# GETDATE]"; // length
Public const string Year = "[# YEAR]"; // length
Public const string Month = "[# MONTH]"; // length
Public const string Day = "[# DAY]"; // length
Public const string CharIndex = "[# CHARINDEX]";
Public const string DateDiff = "[# DATEDIFF]";
}

 

The keyword usage method is to set [# function name] on the original function to replace it with the keyword. The specific parsing instructions are as follows:

 

1: Sorting keywords: [# DESC] and [# ASC]

Note: this is because Access/MSSQL/Oracle processes bit types differently, resulting in sorting problems.
The bit type of access is true or false, which is the opposite of mssql/volume el.

Therefore, using (select * from xxx order by istop [# DESC]), the system will parse different database types internally for correct resolution and restoration.

 

2. Key Value Replacement: [# TRUE] and [# FALSE]

Note: It is caused by the different bit type processing methods of Access,
The bit type of access is IsTop = true during query, and mssql and oracle are IsTop = 1.

Therefore, using (select * from xxx where istop = [# TRUE]), the system will parse different database types internally for correct resolution and restoration.

 

3. Replace [# LEN], [# SUBSTRING], [# GETDATE], [# YEAR], [# MONTH], [# DAY], and [# CHARINDEX] with standard functions.

Note: different database functions need to be parsed.
For example, different databases use names such as len (xx), length (xx), sub (...), and substring (...).

Therefore, using (select * from xxx where [# LEN] (body)> 10), the system will parse different database types internally for correct resolution and restoration.

Special: For the CharIndex function, the location of parameters in different database sequence is exactly different. Here, mssql is used as the standard. For other types of databases, the parameters are reversed, and Oracle is replaced with instr.
In short, the usage is: [# CHARINDEX] ('looking for something ', 'long and long text, original text ')

The usage is the same as before, but the function name is added with [#]

 

4: Special [# DATEDIFF]

Note: Due to the large difference in this function, you need to explain it separately.
There are differences between access and mssql. One is that the type requires quotation marks and no quotation marks, and the other is that the parameter expression is different.
This function does not exist in Oracle. If you use this function, create a DateDiff function/stored procedure on your own.
Example: select * from users where [# DATEDIFF] ([# d], CreateTime, [# GETDATE]) <3
Note:
[# DATADIFF] is the same as the normal function name.
[# D] the database is parsed to 'D' or d. The standard Parsing is as follows: [# yyyy], [# q], [# m], [# y], [# d], [# h], [# ww], [# n], [# s]
The common parameter expressions of access and oracle are used for parsing.
[# GETDATE] is parsed from different databases to now (), getdate (), sysdate

 

Framework: [pin to the top] CYQ. Data lightweight Data layer-path bug feedback, optimization suggestions, latest framework download

 

Postscript:

The blog of the Fall edition replaces the special Syntax of multiple databases based on this method, so you only need to replace the database link when switching the database, without modifying any statements or code.

 

At present, blog Development is in the process of developing a new homepage... I believe the next version will be available soon ~~~