Dapper learning, dapper

Dapper learning, dapper

In the previous article, we introduced the Create method of Rainbow. Here we will introduce the Update method. After all, it is the dual-cell brother to add and modify the method.

I. Update

1. Test code:

var conStr = ConfigurationManager.ConnectionStrings["Cons"].ToString();using (var conn = new MySqlConnection(conStr)){    var db = Rainbow.Init(conn, 2000);    try    {        db.BeginTransaction();        for (int i = 0; i < 10; i++)        {            var res = db.Teacher.Update(i + 1, new            {                //BId = Guid.NewGuid().ToString(),                //CreateDate = DateTime.Now,                // IsDoublePosition = Convert.ToBoolean(i % 2),                Name = "Xixi" + i,                No = (i * 100 + i).ToString("000"),                //Sex = i % 2            });        }        db.CommitTransaction();    }    catch    {        db.RollbackTransaction();        Console.WriteLine("Error happened");    }}

The usage method is similar to the Insert method. The Update method consists of two parameters, the first parameter is Id, and the second parameter is dynamic data. If you do not want to Update all columns, you only need to create a new object, it only contains the columns you want to update. In this way, it will not be modified to other columns.

Result:

 

2. source code parsing

/// <summary>/// Update a record in the DB/// </summary>/// <param name="id"></param>/// <param name="data"></param>/// <returns></returns>public int Update(TId id, dynamic data){    List<string> paramNames = GetParamNames((object)data);    var builder = new StringBuilder();    builder.Append("update ").Append(TableName).Append(" set ");    builder.AppendLine(string.Join(",", paramNames.Where(p => p != "Id").Select(p => p + "= @" + p)));    builder.Append(" where " + "Id" + " = @Id");    DynamicParameters parameters = new DynamicParameters(data);    parameters.Add("Id", id);    return database.Execute(builder.ToString(), parameters);}

Dapper transmits the parameter data through reflection to extract the field name, generate a List <string> set, and then splice the SQL statement according to the set.

In your framework, the database design follows the following rules: the CreateBy and CreateTime fields. When we get the object, they are in the Tch_Teacher format, but we don't want to write new {} again, or when dynamic obj = new ExpandoObject (), we can start with the Update method and modify it to save a lot of trouble.

 

 builder.AppendLine(string.Join(",", paramNames.Where(p => p != "Id" && p != "CreateBy" && p != "CreateTime").Select(p => p + "= @" + p)));

 

Ii. Delete

1. Test code

var conStr = ConfigurationManager.ConnectionStrings["Cons"].ToString();using (var conn = new MySqlConnection(conStr)){    var db = Rainbow.Init(conn, 2000);    try    {        db.BeginTransaction();        for (int i = 0; i < 10; i+=2)        {            var res = db.Teacher.Delete(i);        }        db.CommitTransaction();    }    catch    {        db.RollbackTransaction();        Console.WriteLine("Error happened");    }}

The deletion method is very simple. Rainbow only provides a method to delete data based on the primary key. Here we can extend it to the Update method and match and delete data based on the input fields.

 

2. source code parsing

Let's take a look at the source code of Rainbow. There is no good explanation here.

/// <summary>/// Delete a record for the DB/// </summary>/// <param name="id"></param>/// <returns></returns>public bool Delete(TId id){    return database.Execute("delete from " + TableName + " where " + "Id" + " = @id", new { id }) > 0;}

Next, let's take a look at the Delete custom extension method.

/// <Summary> /// custom deletion method /// delete data based on input fields /// </summary> /// <param name = "data"> </param> /// <returns> </returns> public bool Delete (dynamic data) {List <string> paramNames = GetParamNames (object) data); var builder = new StringBuilder (); builder. append ("delete from "). append (TableName ). append ("where"); builder. append (string. join ("and", paramNames. select (n => n + "= @" + n); return database. execute (builder. toString (), data)> 0 ;}

All the deletions here are and connections. If you want to use the or, instr ()> 0 method, you only need to write your own SQL statements. This method is not provided here.

/// <summary>/// Delete a record for the DB/// </summary>/// <param name="id"></param>/// <returns></returns>public bool DeleteWhere(string where, dynamic param = null){    string wherestr = "";    if (!string.IsNullOrEmpty(where))        wherestr = " where " + where;    return database.Execute("delete from " + TableName + wherestr, param) > 0;}

You can write one by yourself, as long as the condition is given, but this method is quite risky. If the where field is empty, the table will be cleared.