.Net's easy-to-use lightweight tools to execute SQL stored procedures

text

Since this tool has been recently adopted by several companies in Guangdong Province's 2B projects, and its performance is stable and well verified, it is recommended here.

This tool is provided in the IDataAccess interface.

The namespace where IDataAccess is located is: (You need to refer to the DeveloperSharp package from NuGet)

It mainly provides the following four major functions:

（1）     Execute Sql statements

（2）     Execute Sp stored procedures

（3）     Create parameters (input/output/return)

（4）     Transactions

Its initialization code is as follows:

using ;
--------------------------
   DatabaseInfo DIF;
    = ; //Set database type    = "Server=localhost;Database=YZZ;Uid=sa;Pwd=123";
   IDataAccess IDA = (DIF);

Note: By setting the DatabaseType attribute, support for all types of databases is provided (including: MySql, Oracle, PostgreSQL, SqlServer, Sqlite, Firebird, Dameng, as well as Renmin University Jincang KingbaseES, Shenzhou General, Nanda General, Hangao, Access, etc.)

【Example 1: Query】

Below, first, we will give a use example of "querying multiple data + selecting single data + parameters". The code is as follows:

//Query multiple data   var Students1 = <stu>("select * from t_Student");
   //Query multiple data (with parameters)   var Students2 = <stu>("select * from t_Student where Id>@IdMin and Name like @LikeName", new { IdMin = 2, LikeName = "%week%" });
   //Another way of writing 1   var IdMin = ("IdMin", DbType.Int32, 2);
   var LikeName = ("LikeName", , 50, "%week%");
   var Students3 = <stu>("select * from t_Student where Id>@IdMin and Name like @LikeName", LikeName, IdMin);
   //Another way of writing 2   var Students4 = <stu>("select * from t_Student").Where(t =>  > 2 && ("week"));
   //Select single data   var OneStudent = ();

The stu entity class code is as follows:

public class stu
{
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
}
//In this entity classId、Name、AgeAttribute name，To be with the data tableId、Name、AgeField name corresponds to

Note: (1) The namespace of DbType type is

(2) If the stu entity class is not defined, dynamic can also be replaced by

【Example 2: Pagination】

Follow the code of "Example 1" above. If we want to paginate Students1 and Students2 (for example: 20 items per page, take out page 5), the relevant codes are as follows:

using ;//Calling the "pagination function" requires reference to this namespace--------------------------
   var Page1 = (20, 5);
   var Page2 = (20, 5);
   //A one-time writing method   var Page3 = <stu>("select * from t_Student").PagePartition(20, 5);

For the various attributes/parameters contained in the PagePiece object obtained after paging, please refer to this article:Efficient pagination

[Example 3: Add/Delete/Revise]

After talking about "query", let's talk about the use of "add/delete/modify". The following is an example of "modify data + parameters + transactions":

try
   {
       //Open transaction       ();
       //Modify data (multiple statements)       int affectedRows1 = ("insert into t_Student(Name,Age)values('ww','96');update t_Student set Age=100 where Id=1006");
       //Modify data (with parameters)       int affectedRows2 = ("insert into t_Student(Name,Age)values(@N,@A)", new { N = "Sun Wukong", A = 200 });
       //Another way of writing       var NewAge = ("NewAge", DbType.Int32, 200);
       var NewName = ("NewName", , 50, "Sun Wukong");
       int affectedRows3 = ("insert into t_Student(Name,Age)values(@NewName,@NewAge)", NewName, NewAge);
       //Complete transaction       ();
   }
   catch
   {
       //Rolleate the transaction       ();
   }

【Example 4: Output Parameters】

The previous examples only use "input parameters". Let's take a look at how to use "output parameters". The example code is as follows:

var op1 = ("TotalCount", DbType.Int32);//This item is the output parameter   var op2 = ("MyName", , 50);//This item is the output parameter   //The following sql statements are mixed with multiple "input" and "output" parameters, please pay attention to   ("insert into Friend(Birth,Name,height)values(@B,@N,@h);" +
       "select @TotalCount=count(*) from Friend;" +
       "select @MyName=Name from Friend where Id=@Id",
       new { N = "Yang Xiaowei", B = "1999-02-28 12:03:45", h = 11.023, Id = 2 },
       op1, op2);
   int tc = Convert.ToInt32();
   string mn = ();

[Example 5: Stored Procedures]

Finally, let's talk about how to call stored procedures. We create a stored procedure with three types of parameters: input, output, and return. The code is as follows:

CREATE PROCEDURE Test5
    @B as datetime,
    @N as nvarchar(50),
    @h as float,
    @TotalCount as int output,
    @MyName as nvarchar(50) output,
    @Id as int
AS
BEGIN
    insert into Friend(Birth,Name,height)values(@B,@N,@h);
    select @TotalCount=count(*) from Friend;
    select @MyName=Name from Friend where Id=@Id;
    return @TotalCount+100;
END

The sample code for calling this stored procedure is as follows:

var op1 = ("TotalCount", DbType.Int32);//Output parameters   var op2 = ("MyName", , 50);//Output parameters   var op3 = ();//Return parameters   ("Test5", new { N = "Yang Xiaowei", B = "1999-02-28 12:03:45", h = 11.023, Id = 2 }, op1, op2, op3);
   int tc = Convert.ToInt32();
   string mn = ();
   int ret = Convert.ToInt32();

Learn + Flexible use of the above 5 examples can meet almost all data operation needs and are very simple to operate.

Detailed description of the function method in IDataAccess (Auxiliary reference):

SqlExecute<T>
Statement: IEnumerable<T> SqlExecute<T>(string cmdText, params IDataParameter[] Params) where T : class, new()
Purpose: Execute Sql statement (Select class)
Parameters: (1) string  cmdText                --  Sql statement
(2) params IDataParameter[] Params -- Params group
Return: IEnumerable<T> -- Multi-data result set
SqlExecute<T>
Statement: IEnumerable<T> SqlExecute<T>(string cmdText, object InputParams, params IDataParameter[] Params) where T : class, new()
Purpose: Execute Sql statement (Select class)
Parameters: (1) string  cmdText                --  Sql statement
(2) object InputParams            --  Input parameter object
(3) params IDataParameter[] Params -- Params group
Return: IEnumerable<T> -- Multi-data result set
SqlExecute
Statement: int SqlExecute(string cmdText, params IDataParameter[] Params)
Purpose: Execute Sql statements (Insert/Update/Delete class)
Parameters: (1) string  cmdText                --  Sql statement
(2) params IDataParameter[] Params -- Params group
Return: int --  The number of affected rows
SqlExecute
Statement: int SqlExecute(string cmdText, object InputParams, params IDataParameter[] Params)
Purpose: Execute Sql statements (Insert/Update/Delete class)
Parameters: (1) string  cmdText                --  Sql statement
(2) object InputParams            --  Input parameter object
(3) params IDataParameter[] Params -- Params group
Return: int --  The number of affected rows
SpExecute<T>
Statement: IEnumerable<T> SpExecute<T>(string cmdText, params IDataParameter[] Params) where T : class, new()
Purpose: Execute Sp stored procedure (Select class)
Parameters: (1) string  cmdText                --  Sp stored procedure name
(2) params IDataParameter[] Params -- Params group
Return: IEnumerable<T> -- Multi-data result set
SpExecute<T>
Statement: IEnumerable<T> SpExecute<T>(string cmdText, object InputParams, params IDataParameter[] Params) where T : class, new()
Purpose: Execute Sp stored procedure (Select class)
Parameters: (1) string  cmdText                --  Sp stored procedure name
(2) object InputParams            --  Input parameter object
(3) params IDataParameter[] Params -- Params group
Return: IEnumerable<T> -- Multi-data result set
SpExecute
Statement: int SpExecute(string cmdText, params IDataParameter[] Params)
Purpose: Execute Sp stored procedures (Insert/Update/Delete class)
Parameters: (1) string  cmdText                --  Sp stored procedure name
(2) params IDataParameter[] Params -- Params group
Return: int --  The number of affected rows
SpExecute
Statement: int SpExecute(string cmdText, object InputParams, params IDataParameter[] Params)
Purpose: Execute Sp stored procedures (Insert/Update/Delete class)
Parameters: (1) string  cmdText                --  Sp stored procedure name
(2) object InputParams            --  Input parameter object
(3) params IDataParameter[] Params -- Params group
Return: int --  The number of affected rows

The above is a detailed explanation of the .Net's easy-to-use and lightweight tools for executing SQL stored procedures. For more information about the .Net's SQL stored procedures tool, please pay attention to my other related articles!