Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to realize the abstract addition, deletion and modification of C#". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "C# abstract addition, deletion and modification how to achieve" it!

Now there is a popular ORM framework in the industry, which is Dapper. I am also one of the fans of Dapper, and the framework I have summed up is also based on Daaper. Here is my code, starting with the Dapper Helper class, the database universal access class (use the Nuget tool to reference the Dapper class into the NetUtility.Dapper.Core project first):

NetUtility.Dapper.Core.DataBaseAccess.cs

Using System

Using System.Collections.Generic

Using System.Configuration

Using System.Data.SqlClient

Using System.Linq

Using System.Text

Using System.Threading.Tasks

Using Dapper

Using System.Data

Using NetUtility.Entity

Using System.Reflection

Namespace NetUtility.Dapper.Core

{

/ / /

/ / Database access class

/ / /

Public class DataBaseAccess

{

Public static SqlConnection CreateConnection ()

{

String connStr = ConfigurationManager.ConnectionStrings ["connString"] .ConnectionString

SqlConnection conn = new SqlConnection (connStr)

Conn.Open ()

Return conn

}

/ / /

/ / execute the methods of adding, deleting and changing

/ / /

/ / /

/ / /

/ / /

Public static int Execute (string sql, object parms = null)

{

Using (IDbConnection conn = CreateConnection ())

{

Return conn.Execute (sql,parms)

}

}

/ / /

/ / get single row and single column

/ / /

/ / /

/ / /

/ / /

Public static object ExecuteScalar (string sql, object parms = null)

{

Using (IDbConnection conn = CreateConnection ())

{

Return conn.ExecuteScalar (sql, parms)

}

}

/ / /

/ / single dataset query

/ / /

/ / /

/ / /

/ / /

Public static List Query (string sql,Func pre, object parms = null)

{

Using (IDbConnection conn = CreateConnection ())

{

Return conn.Query (sql, parms) .Where (pre) .ToList ()

}

}

/ / /

/ / single dataset query

/ / /

/ / /

/ / /

/ / /

Public static List Query (string sql, object parms = null)

{

Using (IDbConnection conn = CreateConnection ())

{

Return conn.Query (sql, parms). ToList ()

}

}

/ / /

/ / multiple dataset queries

/ / /

/ / /

/ / /

/ / /

Public static SqlMapper.GridReader MultyQuery (string sql, object parms = null)

{

Using (IDbConnection conn = CreateConnection ())

{

Return conn.QueryMultiple (sql, parms)

}

}

/ / /

/ / single dataset query

/ / /

/ / /

/ / /

/ / /

Public static TEntity FirstOrDefault (string sql,Func selector, object parms = null)

{

Using (IDbConnection conn = CreateConnection ())

{

Return conn.Query (sql, parms) .Where (selector) .FirstOrDefault ()

}

}

}

}

I abstracted the addition, deletion, modification and search, and what is necessary is the generation of SQL statements, generating SQL statements, either mapping or reflection, while I use reflection to give an Entity class, I read all its properties and fields, and then generate the corresponding SQL statement. NetUtility.Dapper.Core.DataMapping.cs

Using System; using System.Collections.Generic; using System.Configuration; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks Namespace NetUtility.Dapper.Core {internal class DataMapping where TModel: class {# region database type + DataBaseType / database type / public static string DataBaseType {get {string strType = ConfigurationManager.AppSettings ["DataBaseType"] If (! string.IsNullOrEmpty (strType)) {return strType;} else {return string.Empty } # endregion # region primary key attribute field + PrimaryKey / primary key field name / public static string PrimaryKey {get {Type t = typeof (TModel) TableInfoAttribute tableInfo = t.GetCustomAttribute (typeof (TableInfoAttribute), true) as TableInfoAttribute; if (tableInfooted information null) / / if the table information property is not identified, the primary key information {return tableInfo.PrimaryKey is obtained from the database through the table name } else {string tableName = TableName (); return DataBaseAccess.ExecuteScalar ("SELECT name FROM SysColumns WHERE id=Object_Id ('" + tableName + "') and colid= (select top 1 colid from sysindexkeys where id=Object_Id ('" + tableName + ")") .ToString () } # endregion # region get table name + TableName / database table name prefix / public static string TableName (string prev = "") {Type t = typeof (TModel) TableInfoAttribute tableInfo = t.GetCustomAttribute (typeof (TableInfoAttribute), true) as TableInfoAttribute; return tableInfo! = null? TableInfo.TableName: string.Concat (prev, t.Name);} # endregion # region Select query statement + GetQuerySql / Select query statement / public static string GetQuerySql () {StringBuilder sql = new StringBuilder ("select * from"); sql.Append (TableName ()) Return sql.ToString () } # endregion # object instance with non-Null attribute Sql statement + GetInsertSql / Insert object instance with non-Null attribute Sql statement / public static string GetInsertSql (TModel model) {StringBuilder sql = new StringBuilder ("insert into") String [] props = Propertys (model); sql.Append (TableName ()); sql.Append ("("); sql.Append (string.Join (", props)); sql.Append (") values (@ "); sql.Append (string.Join (", @ ", props)); sql.Append ("); select @ @ IDENTITY ") Return sql.ToString ();} # endregion # region DeleteSql statement + GetDeleteSql / DeleteSql statement / public static string GetDeleteSql () {return string.Format (@ "delete from {0} where {1} in @ IdList", TableName (), PrimaryKey) } # endregion # object instance with non-Null attribute Sql statement + GetUpdateSql / Update object instance with non-Null attribute Sql statement / public static string GetUpdateSql (TModel model) {StringBuilder sql = new StringBuilder ("update") String [] props = Propertys (model); sql.Append (TableName ()); sql.Append ("set"); foreach (string propName in props) {sql.Append (propName + "= @" + propName + ",");} sql.Remove (sql.Length-1,1) Sql.Append ("where" + PrimaryKey + "= @ Id"); return sql.ToString () } # endregion # region non-primary key and non-Null attribute collection + Propertys / non-primary key and non-Null attribute / public static string [] Propertys (TModel model) {PropertyInfo [] props = typeof (TModel). GetProperties () List list = new List (); string key = PrimaryKey If (props! = null & & props.Length > 0) {foreach (PropertyInfo prop in props) {if (prop.GetValue (model, null)! = null & &! prop.Name.Equals (key, StringComparison.OrdinalIgnoreCase)) {list.Add (prop.Name) } return list.ToArray ();} # endregion}}

The TableInfoAttribute class in the code is an attribute property class built by me, which is used to identify the table name and primary key name. If the Entity entity class does not identify the primary key name, the framework will use the Entity class name as the table name by default. It is recommended to * identify the table name and primary key name.

NetUtility.Dapper.Core.TableInfoAttribute.cs

Using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks Namespace NetUtility.Dapper.Core {[AttributeUsage (AttributeTargets.Class | AttributeTargets.Property, AllowMultiple = true, Inherited = true)] / identifies the table name, primary key and other information properties class / public class TableInfoAttribute: Attribute {/ database table name / public string TableName {get; set } / Primary key name / public string PrimaryKey {get; set;} public TableInfoAttribute () {} public TableInfoAttribute (string tableName, string key) {this.TableName = tableName; this.PrimaryKey = key;} using System; using System.Collections.Generic Using System.Linq; using System.Text; using System.Threading.Tasks; namespace NetUtility.Dapper.Core {[AttributeUsage (AttributeTargets.Class | AttributeTargets.Property, AllowMultiple = true, Inherited = true)] / identifies the table name, primary key and other information properties class / public class TableInfoAttribute: Attribute {/ database table name / public string TableName {get Set;} / Primary key name / public string PrimaryKey {get; set;} public TableInfoAttribute () {} public TableInfoAttribute (string tableName, string key) {this.TableName = tableName; this.PrimaryKey = key;}

OK, here is a new abstract class, which is used to abstract the ExecuteSql generic abstract class that adds, deletes and modifies.

NetUtility.Dapper.Core.ExecuteSql.cs

Using Dapper; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks Namespace NetUtility.Dapper.Core {public abstract class ExecuteSql where TModel: object instance of class {# region Insert non-Null attribute + Insert (TModel model) / Insert object instance of non-Null attribute / public virtual int Insert (TModel model) {string sql = DataMapping.GetInsertSql (model) Object res = DataBaseAccess.ExecuteScalar (sql, model); if (res! = null) {return Convert.ToInt32 (res);} return 0 } # endregion # region Select * query + Query () / Select * query / public virtual List Query () {string sql = DataMapping.GetQuerySql (); return DataBaseAccess.Query (sql) } # endregion # region Select query with query conditions + Query (Func selector) / Select query with query conditions / public virtual List Query (Func selector) {string sql = DataMapping.GetQuerySql () Return DataBaseAccess.Query (sql, selector) } # endregion # region get an instance of an object + FirstOrDefault (Func selector = null) / public virtual TModel FirstOrDefault (Func selector = null) {string sql = DataMapping.GetQuerySql () Return DataBaseAccess.FirstOrDefault (sql, selector) } # endregion # region batch delete + Delete (string [] IdList) / batch delete / public virtual int Delete (string [] IdList) {return DataBaseAccess.Execute (DataMapping.GetDeleteSql (), new {IdList = IdList}) } # endregion # region Update an object with non-Null attributes + Update (TModel model) / Update an object with non-Null attributes / public virtual int Update (TModel model) {return DataBaseAccess.Execute (DataMapping.GetUpdateSql (model), model) } # endregion # region get multiple datasets + MultyQuery (string sql, object param = null) / public virtual SqlMapper.GridReader MultyQuery (string sql, object param = null) {return DataBaseAccess.MultyQuery (sql) Param) } # endregion}}

The methods in the ExecuteSql.cs class are all virsual methods, and users can override them, especially query methods, which are bound to be overridden. Now that all the classes in the NetUtility.Dapper.Core project have been written, it is now a reference to my business class. I just need to build a business class to inherit this abstract class. There are all these additions, deletions, modifications and queries, and there is no need to write!

Here are two of my entity classes, which identify the primary key name and table name with the TableInfoAttribute attribute class:

Using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NetUtility.Dapper.Core; namespace NetUtility.Entity {[TableInfo (PrimaryKey = "Id", TableName = "Classes")] public class Classes {public int Id {get; set;} public string Name {get; set;} public string Code {get; set;} using System; using System.Collections.Generic; using System.Linq Using System.Text; using System.Threading.Tasks; using NetUtility.Dapper.Core; namespace NetUtility.Entity {[TableInfo (PrimaryKey = "Id", TableName = "Student")] public class Student {public int Id {get; set;} public string Name {get; set;} public string Code {get; set;} public int? Age {get; set;} public DateTime? JoinDate {get; set;} public int? ClassesId {get; set;}

I create a new StudentRepertories business class that inherits the ExecuteSql abstract class.

NetUtility.Repertories.StudentRepertories.cs

Using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NetUtility.Dapper.Core; using NetUtility.Entity; using NetUtility.Entity.ExstendEntity;// is an extension of the entity class. Using System.Data; using Dapper; namespace NetUtility.Repertories {public class StudentRepertories: ExecuteSql {public override List Query () {return base.Query () can be removed in the project if it is not needed. } public List QueryInfo () {string sql = "select * from Student a left join Classes b on a.ClassesId=b.Id"; using (IDbConnection conn = DataBaseAccess.CreateConnection ()) {return conn.Query (sql, (stu, classes) = > {stu.ClassesModel = classes; return stu;}) .ToList () }

All right, now we just need to set up a console to test whether there is a problem, personally, there is no problem.

NetUtility.ConsoleItem

Using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NetUtility.Repertories; using NetUtility.Entity; namespace NetUtility.ConsoleItem {class Program {static void Main (string [] args) {/ / Business object StudentRepertories stu = new StudentRepertories () / / entity object var model = new Student () {Age = 100 a.Id ClassesId = 1 Magi Code = "3200020021", JoinDate = DateTime.Now,Name = "Xu"}; / / add an object int StudentId = stu.Insert (model); var newModel = stu.FirstOrDefault (a = > a.Id = = StudentId) / / Lambda expression query var list = stu.Query (a = > a.Age = = 100); / / query var studentInfoList = stu.QueryInfo () without parameters; # region update newModel.Code = "1111111111"; newModel.Id = StudentId; stu.Update (newModel) # endregion / / Delete stu.Delete (new string [] {newModel.Id.ToString ()}); Console.ReadKey ();} at this point, I believe you have a better understanding of "how to implement C# abstract additions, deletions and modifications", so you might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.