Design idea and implementation of base component of database writing library

Code farmers will encounter the tedious operation of writing library, the data Access layer of the SQL statement package is good to implement, but once the field, such as more than 10 more than 20 fields, the SQL package will be a huge problem, not to say how difficult, but this operation is very cumbersome, and very prone to error, SQL statements can be difficult to troubleshoot once they are faulted. I also encountered the same problem in development, such problems will always waste a lot of unnecessary time, so I would like to be able to provide a common basic components to implement the cumbersome underlying SQL statement operations, we only need to invoke some simple excuses to implement the database of a quick write library.
First, the necessary information to write the library contains: the name of the column to write, and the data entity.
(1) The name of the column to be written varies according to the business requirements, so this parameter needs to be passed in by the user, and the data model must be passed in by the user.
(2) The basic components must have a relatively wide range of use, so the data model must not be fixed value, so it needs generics to implement.
(3) How do we get the field values we need to write from the data model? You can reflect the property values of an object by using the reflected method from the inserted column name entered by the user.
In fact, the technical points used in this component include: Generics, Reflection, ADO. NET and some basic techniques for data processing, such as LINQ, lambda expressions.

The underlying component code is as follows:

Using system;using system.collections.generic;using system.data;using system.linq;using System.Text;namespace yibaobao.basickit{public class SqlHelper {//<summary>//Insert a single object into the database///</summary        >//<typeparam name= "T" > Object type </typeparam>//<param name= "conn" > Database links </param>        <param name= "tablename" > Table name </param>//<param name= "Columns" > Inserted columns </param> <param name= "obj" > Parameter objects </param>//<returns> affect rows </returns> public static int add& Lt T> (IDbConnection conn, string tablename, string columns, T obj) {using (IDbCommand cmd = conn. CreateCommand ()) {Conn.                Open (); String sql = string.                Empty; sql = string.                Format ("insert into {0} ({1}) VALUES (", tablename, columns); var fields = columns.                Split (', ');              var t = typeof (T);  sql = fields. Aggregate (SQL, (current, item) = + current + string. Format ("' {0} ',", obj.) GetType (). GetProperty (item).                GetValue (obj, null))); sql = SQL. Substring (0,sql.                LENGTH-1);                SQL + = ");";                Cmd.commandtext = SQL; int res = cmd.                ExecuteNonQuery ();            return res; }}////<summary> Insert a DataSet into the database///</summary>//<typeparam name= "T" > Collection subtypes </typeparam>//<param name= "conn" > Database links </param>//<param name= "tablename" & gt; table name </param>//<param name= "columns" > Insert column </param>//<param name= "obj" > Parameter list Object < /param>//<returns> database operations affect line </returns> public static int add<t> (IDbConnection conn, St Ring tablename, string columns, list<t> obj) {using (IDbCommand cmd = conn. CreateCommand ()) {Conn.        Open ();        String sql = string.                Empty; sql = string.                Format ("insert into {0} ({1}) values", TableName, columns); var fields = columns.                Split (', ');                var t = typeof (T);                    foreach (var buff in obj) {sql + = "("; sql = fields. Aggregate (SQL, (current, item) = + current + string. Format ("' {0} ',", buff. GetType (). GetProperty (item).                    GetValue (buff, null))); sql = SQL. Substring (0, SQL.                    LENGTH-1);                SQL + = "),"; sql = SQL. Substring (0, SQL.                LENGTH-1);                Cmd.commandtext = SQL; int res = cmd.                ExecuteNonQuery ();            return res; }        }            }}

The table structure of the test is as follows:

To invoke the sample code:

Using system;using system.collections.generic;using system.data;using system.data.odbc;using System.Linq;using System.text;using mysql.data.mysqlclient;using yibaobao.basickit;using Yibaobao.model;namespace ConsoleTest{class            program {static void Main (string[] args) {var data=new treatementexp (); Data.            Age = "23"; Data.            Hospitalname = "Huaxi Hospital"; Data.            Certificate = "XXXXX"; Data.            Createtime = DateTime.Now.ToString (); Data.            Department = "Facial branch"; Data.            Doctor = "XXX"; Data.            Sex = "male"; Data.            Symptom = "XXXXXX"; Data.            Diagnosisconclusion = "XXXXXX"; Data.            Remark = "XXXXX";            String connstr = "Your MySQL connection string"; using (IDbConnection conn =new mysqlconnection (connstr)) {var res = Sqlhelper.add<treatement Exp> (conn, "Treatmentexperience", "sex,age,hospitalname,department,doctor,certifIcate,diagnosisconclusion,symptom,remark,createtime ", data); }                   }    }}

A pro-Test!!

Design idea and implementation of base component of database writing library