[. Net framework] – Advanced Application of dapper framework (2)

Time:2020-10-1

1.1. Dapper calls stored procedure

A stored procedure is a set of precompiled SQL statements

Using stored procedures has the following advantages:

1. Allow modular programming, that is to say, it only needs to be created once, and the procedure can be called any time in the future program.

2. Allows faster execution. If an operation requires a large number of SQL statements or repeatedly executes the corresponding SQL statements, the stored procedure can execute faster than the SQL statement.

3. Reduce network traffic. For example, an operation that requires hundreds of lines of SQL code is completed with an execution statement, and it is not necessary to send hundreds of lines of code in the network.

4. Better security mechanism, for users who have no permission to execute stored procedures, they can also be authorized to execute stored procedures.

1.1.1. Using dapper to call parameterized stored procedures

Here, I first create a non parameter stored procedure based on dapperdemo database: dbo. P_ Stumarkinfo, the specific stored procedure statements have been uploaded to GitHub: https://github.com/devyf/Dapper-.git

 

 

Here, I call my background stored procedure based on the button operation of the wiform interface,

Click the “failed student list” button to call the “dbo. P” created in the database in the background_ Stumarkinfo “stored procedure:

 

 

Corresponding to the background nonparametric stored procedure, click to call the execution code:

/// 
        ///Click the button to query the list of students who failed to pass the exam, and call the stored procedure without parameters. The default passing line is 60 points
        /// 
        /// 
        /// 
        private void button1_Click(object sender, EventArgs e)
        {
            List stuList = new List();
            using (IDbConnection db = new SqlConnection(DBHelper.ConnString))
            {
                stuList =  db.Query ("dbo.P_ Stumarkinfo ", // the name of the stored procedure
                    Null, // parameters of stored procedure
                    Null, // transaction object
                    True, // whether to cache
                    Null, // gets or sets the waiting time between terminating an attempt to execute a command and generating an error
                    CommandType.StoredProcedure  //The specified SQL statement is a stored procedure type
                    ).ToList();
            }

            if (stuList.Count > 0)
            {
                stuList.ForEach(stu => this.textBox1.Text += stu.StuName + " ");
            }
        }

The implementation effect is as follows:

 

1.1.2. Using dapper to call stored procedures with parameters (return values)

Here, I continue to add SQL to create a stored procedure with parameters and return values, as follows:

if exists (select * from sysobjects where name='P_stuMarkInfo1')
drop proc P_stuMarkInfo1
go
create proc P_stuMarkInfo1
@writeLevel int,  
@labLevel int,    
@examNum int output     
as

select @examNum = count(*) from stuinfo where stuNo not in
(select stuNo from stumark where writtenExam >= @writeLevel and labExam>[email protected])



declare @countNum int
exec P_stuMarkInfo1 60, 60, @countNum output

Print 'number of people who failed the exam:' + convert (varchar (20), (@ countnum))

Stored procedure statements have been uploaded to GitHub: https://github.com/devyf/Dapper-.git

When creating a specific stored procedure, it needs to be executed and saved in the current database, as shown in the following figure:

 

 

Here, the input and output parameters corresponding to the front desk WinForm interface are set as follows. Click the “number of failed students” button to call the background stored procedure:

 

 

The code of calling the stored procedure with parameters in the background is as follows:

/// 
        ///Click the button to dynamically call the stored procedure with parameters through the set written test results and computer test results
        /// 
        /// 
        /// 
        private void button2_Click(object sender, EventArgs e)
        {
            //There are three parameters to prepare the stored procedure: two are input parameters and one is output parameter
            Var param = new dynamicparameters(); // dynamic parameter class
            try
            {
                param.Add ("@writeLevel",  int.Parse ( this.writeLev.Text )); // input parameter assignment of stored procedure
                param.Add("@labLevel", int.Parse(this.labLev.Text));
                param.Add ("@examNum", 0,  DbType.Int32 , ParameterDirection.Output ); // marked as output parameter
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
          

            using (IDbConnection db = new SqlConnection(DBHelper.ConnString))
            {
                db.Execute ("dbo.P_ Stumarkinfo1 ", // specifies the stored procedure name
                    Param, // stored procedure parameters
                    Null, // stored procedure transaction
                    Null, // execution waiting time
                    CommandType.StoredProcedure   //Specifies the execution as a stored procedure type
                    );
                //Call the get method through the parameter to get the return value
                int outNum = param.Get("@examNum");
                //Place in text box
                this.nopassNum.Text = outNum.ToString();
                MessageBox.Show ("the stored procedure was executed successfully! "";
            }
        }

1.2. Dapper performs transaction operations

Concept of transaction: in relational database, a transaction can be a SQL statement, a group of SQL statements or the whole program.

Transaction characteristics:

Transaction is the basic unit of recovery and concurrency control.

Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four attributes are often referred to as acid properties.

Atomicity: a transaction is an indivisible unit of work. All operations involved in the transaction are either done or not done.

Consistency: a transaction must change a database from one consistent state to another. Consistency is closely related to atomicity.

Isolation: cannot be interrupted by another transaction. In other words, the operation and data used in a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

Durability: persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it.

The database transaction is operated by using dapper. Here, two tables (main table stuInfo and slave table stumark) in the database are executed based on WinForm interface, and the operation of submitting, deleting and rolling back is performed by using dapper operation transaction. The specific background code is as follows:

/// 
        ///Click Delete button to execute transaction operation
        /// 
        /// 
        /// 
        private void delBtn_Click(object sender, EventArgs e)
        {
            //Execute the interface operation to delete the associated information according to the ID of the student table in the main table
            //Transaction operation: according to the input student ID number, delete the secondary table and the data information column in the main table
            int delId =  int.Parse ( this.txtDelID.Text ); // take out the student number of the student table to be deleted
            using (IDbConnection db = new SqlConnection(DBHelper.ConnString))
            {
                db.Open (); // based on the particularity of transaction operation: it is necessary to open the connection before executing the transaction
                //The try catch statement uses the outer code: shortcut keys: Ctrl + K, Ctrl + s
                //Create transaction object
                IDbTransaction transaction =  db.BeginTransaction (); // start database transaction
                try
                {
                    //Delete according to the student ID input by the user: first delete the secondary table, and then delete the information of the main table
                    String delsql1 = "delete from stuInfo where stuNo = @ stuNo"; // main table
                    String delsql2 = "delete from stumark where stuNo = @ stuNo"; // from table

                    //Perform the delete operation
                    db.Execute(delSql2, new { stuNo = delId }, transaction, null, null);
                    db.Execute(delSql1, new { stuNo = delId }, transaction, null, null);

                    //Commit transaction
                    transaction.Commit();
                    MessageBox.Show ("deleted successfully! "";
                }
                catch (Exception ex)
                {
                    //An exception occurred and the transaction needs to be rolled back
                    transaction.Rollback();
                    MessageBox.Show (exception occurred:+ ex.Message );
                }
                finally
                {
                    db.Close();
                }
            }
        }

Delete the slave table and master table, and successfully delete the data with ID “1006” from the front interface

 

 

The corresponding database will also be deleted:

 

 

 

 

If the execution order of delsql1 and delsql2 is exchanged, because the primary table is deleted first and there is a foreign key Association on the slave table, the data rollback rollback operation will be performed, and the data deletion operation cannot be performed

 

We can see that after the catch exception, the data “1005” in the database does not delete the data column

 

1.3. Dapper queries multiple tables

Dapper framework can map database fields based on database SQL statements, and use spliton to partition types and return values.

Based on two examples, this paper briefly introduces the case of multipart query return value partition:

Example 1:

① First, based on the previous dbo.stuinfo Table and dbo.stumark Table for multi table join query, the corresponding SQL statement and query results are as follows:

 

② Based on the result of the join query, the dapper framework is used to encapsulate the query mapping. The specific code is as follows:

private void button1_Click(object sender, EventArgs e)
        {
            using (IDbConnection db = new SqlConnection(DBHelper.ConnString))
            {
                var sql = "select * from stuinfo inner join stumark on stuinfo.stuNo = stumark.stuNo";

                //Execute query: multiple tables (type 1, type 2, return value)
                var list = db.Query(
                    sql,
                    (students, score) = > {return students;}, // the stuInfo type corresponding to the variable students, and grades corresponding to the stumark type
                    Null, // parameters of stored procedure
                    Null, // transaction
                    True, // cache
                    Spliton: "stuNo" // this parameter is used to divide which table the fields in the query belong to. Spliton can be omitted
                    );

                / * splitOn:stuNo  To divide which table the fields in the query belong to, that is, to which entity the query structure is mapped to. When the upper SQL runs, all the data will be collected from the query results
                 The last field in the field list is matched. The stuNo field is always found (regardless of case). The first stuNo field found is in the query parameter
                 For the stuNo property of the stuInfo class, the stuNo to the last field belongs to stuInfo, and the fields before stuNo are mapped to the stumark table
                 Through (T, P) = > (return T), the instances of the two classes are resolved*/
                this.dgvContent.DataSource = list;
            }
        }

Click on the interface to load the database query mapping results, as shown in the following figure:

 

Example 2:

Continue to use the query statement to split the data. Here, we also create two data interfaces to query by clicking the display button

 

View data background code:

private void button1_Click(object sender, EventArgs e)
        {
            using (IDbConnection db = new SqlConnection(DBHelper.ConnString))
            {
                List userList = new List();
                string sql = @"select u.*, r.* from UserInfo u 
                               inner join UserRole ur on ur.UserId = u.UserId
                               inner join Role r on r.RoleId = ur.RoleId";
                userList =  db.Query (// the third parameter is the return value type
                    sql,
                    (user, role) => { user.Role = role; return user; },
                    null,
                    null,
                    true,
                    "Roleid", // split data column string
                    null,
                    null
                    ).ToList();
                this.dataGridView1.DataSource = userList;
                //Print a single character
                if (userList.Count > 0)
                {
                    userList.ForEach(item => Console.WriteLine("userName:" + item.Username + "passWord:" + item.PasswordHash +
                        "Role:" + item.Role));
                }
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (IDbConnection db = new SqlConnection(DBHelper.ConnString))
            {
                List userList = new List();
                string sql = @"select u.UserId, u.UserName, u.PasswordHash, r.RoleId, r.RoleName from UserInfo u
                               inner join UserRole ur on ur.UserId = u.UserId
                               inner join Role r on r.RoleId = ur.RoleId";
                Dictionary dic = new Dictionary();
               
                userList = db.Query(
                    sql,
                    (user, role) => 
                    {
                        User tempUser;
                        if (!dic.TryGetValue(user.UserId, out tempUser))
                        {
                            tempUser = user;
                            dic.Add(user.UserId, tempUser);
                        }
                        tempUser.Role.Add(role);
                        return user;
                    },
                    null,
                    null,
                    true,
                    "RoleId",
                    null,
                    null
                    ).ToList();

                    this.dataGridView2.DataSource = userList;
                //Print a single character
                if (userList.Count > 0)
                {
                    userList.ForEach(item => Console.WriteLine("userName:" + item.Username + "passWord:" + item.PasswordHash +
                        "Role:" + item.Role.First().RoleName));
                }
            }
        }

The query results are as follows:

 

Recommended Today

Redis (1)

Redis About redis Remote dictionary server (redis) is a key value storage system. Redis is an open source log type, key value database written in ANSI C language, complying with BSD protocol, supporting network, memory based and persistent, and providing API in multiple languages. It is often referred to as a data structure server, because […]