Dapper learning, dapper

Dapper learning, dapper

We have already introduced the addition, modification, and deletion. Next we will introduce the Read method of Rainbow.

I. Read -- native Rainbow

1. Check the test code first.

Var conStr = ConfigurationManager. connectionStrings ["Cons"]. toString (); using (var conn = new MySqlConnection (conStr) {var db = Rainbow. init (conn, 2000); // the method provided by Rainbow var entity = db. teacher. get (8); entity = db. teacher. first (); var listAll = db. teacher. all ();}

In terms of reading data, Rainbow only provides these methods. Of course, the Dapper method can still be used here. It can be used through db. Query.

 

2. source code parsing

/// <summary>/// Grab a record with a particular Id from the DB /// </summary>/// <param name="id"></param>/// <returns></returns>public T Get(TId id){    return database.Query<T>("select * from " + TableName + " where " + "Id" + " = @id", new { id }).FirstOrDefault();}public virtual T First(){    return database.Query<T>("select * from " + TableName + " LIMIT 1;").FirstOrDefault();    //return database.Query<T>("select top 1 * from " + TableName).FirstOrDefault();}public IEnumerable<T> All(){    return database.Query<T>("select * from " + TableName);}

The First method needs to be modified to adapt to mysql. however, the First method is a virtual method. without modifying the source code, you can use the class to inherit and override it, which is more convenient. if the source code is available, it is more convenient.

The several packages here are very simple, almost naked SQL.

 

Ii. Custom Extension

1. Test code:

Var conStr = ConfigurationManager. connectionStrings ["Cons"]. toString (); using (var conn = new MySqlConnection (conStr) {var db = Rainbow. init (conn, 2000); // custom Extension Method var list = db. teacher. getListBy ("No> @ No", new {No = 6060}); var pageList = db. teacher. getPageList (1, 10, "No> @ No", new {No = 6060 });}

After testing, it can be used normally. Next let's take a look at the code

 

2. Custom Methods

/// <Summary> /// query by condition /// </summary> /// <param name = "whereStr"> without the where keyword </param> // /<returns> </returns> public IEnumerable <T> GetListBy (string whereStr, dynamic param = null) {string SQL = "select * from" + TableName; if (! String. IsNullOrWhiteSpace (whereStr) SQL + = "where" + whereStr; return database. Query <T> (SQL, param );}

The method here is only applicable to a single table. For multi-table queries, you can directly use the Dapper method to write SQL statements.

/// <Summary> /// paging method /// </summary> /// <param name = "pageIndex"> current page number </param> /// <param name = "pageSize"> Number of entries displayed per page </param> // <param name = "whereCondition"> where condition </param> // <param name = "param "> parameter list in the where condition </param> // <param name =" orderbyProperty "> sort field; such as name DESC or id DESC, name ASC </param> // <returns> </returns> public virtual PagedResult <T> GetPageList (int pageIndex, int pageSize, string whereConditio N = "", dynamic param = null, string orderbyProperty = "") {if (pageIndex <1) pageIndex = 1; var startItem = (pageIndex-1) * pageSize; string where = string. isNullOrEmpty (whereCondition )? "": "WHERE" + whereCondition; string orderby = string. IsNullOrEmpty (orderbyProperty )? "": "Order by" + orderbyProperty; string SQL = string. format (@ "SELECT * FROM {0} {1} {2} LIMIT {3}, {4}; select count (1) FROM {0} {1 }", tableName, where, orderby, startItem, pageSize); PagedResult <T> pagingResult = new PagedResult <T> (pageSize, pageIndex); using (var result = database. queryMultiple (SQL, param) {List <T> list = result. read <T> (); var totalCount = Convert. toInt32 (result. read <long> () [0]); pagingResult. data = list; pagingResult. totalItemCount = totalCount;} return pagingResult ;}

This is the paging Method for a single table. If you want to add multiple tables, you only need to modify this method a little.

The QueryMultiple method is not mentioned in the Dapper parsing Section. This method is used for multiple queries. If the SQL statement is concatenated by Multiple SQL statements, you can read multiple times to obtain multiple query results.