1. SQL Executive class
  2. CURD Operation class
  3. Other soy sauce

This is to fill in the hole for the last article , I don't know if the bricks in the first part are enough , If it's not enough, please continue to smash it .

CURD The operation class is responsible for converting the condition provided by the user to SQL sentence , And provide IHelper perform , return Model aggregate .

CURD Class needs an interface to abstract public methods . Easy to modify and expand , Provides generic interfaces . Not available for the sake of simplicity JOIN The implementation of the , You can replace... With a database view

public interface IDbOper<T> : IDisposable where T : new()
{
object Insert(T m);// newly added MODEL, return ID, For the sake of simplicity, I just did INT Self increasing
int Update(string str);// Batch update
int Update(T m);//Model to update
int Delete();// Delete
/// Mosaic character version , You need to prevent injection yourself , especially Orderby Easily overlooked
IDbOper<T> Select(string sl);// Selection field
IDbOper<T> Where(string sl);
IDbOper<T> Orderby(string orby);
///Expression Version overload , Convert to parameter mode execution , There is no injection risk when splicing in a parametric way
 IDbOper<T> Select(Expression<Func<T, object>> sl);
IDbOper<T> Where(Expression<Func<T, bool>> sl);
///Dictionary Version overload , You need Newton JSON Help transform , There is no injection risk when splicing in a parametric way , This method is used for “ be equal to ” And so on , No greater than less than query
IDbOper<T> Orderby(Dictionary<string, string> dic);
IDbOper<T> Where(Dictionary<string, object> dic);
///
IDbOper<T> Index(int i);
IDbOper<T> Size(int i);
T First();// Get the first one model
void BegTran();
void RollBack();
void Commit();
 M ToObj<M>(Func<IDataReader, M> func,string sql);
List<T> ToList();
// Into other types , If the transaction is opened, this transformation is needed
IDbOper<M> ToOper<M>() where M : new();
int Count();
 // Direct execution SQL sentence 
 int DoCommand(string sql, bool issp);
}

Because I prefer JQ How to operate , So I want to bring this execution mode to the background operation database , Let's cut the crap and define it first 2 individual Model And instantiate an operation class

public class User
{
[Key]
public int ID{get;set;}
public string UserName{get;set;}
public string Password{get;set;}
}
public class NewUser
{
[Key]
public int ID{get;set;}
public string UserName{get;set;}
public string Password{get;set;}
}
var db=new DbOper<User>(new DbInfo(){DbType="…",DbConntion="…"}); 

Expression execution

User a=db.Select(u=>new{u.ID}).Where(u=>u.ID==).First();

The implementation of text stitching

User a=db.Select("*").Where("ID=54").First();

The implementation of dictionary splicing

User a=db.Select("*").Where(new Dictionary<string, object>(){Key="ID",Value=}).First();

Paging code

List<User> lt=db.Select("*").Where("ID>0").Orderby("ID Desc").Index().Size().ToList();

The use of transactions

db.BegTran();
try{
int b=db.Where("ID=54").Delete();//user Table delete ID=54
int c=db.ToOper<NewUser>().Insert(new NewUser(){UserName="…",Password="…"});//newuser Table add a record
db.Commit();
}
catch{db.RollBack();}

Only when called Insert,Update,Delete,Count,ToList Method will start splicing text and then call IHelper perform SQL sentence , When the call is complete, it will be called automatically Clear() To clean up the preserved where,select Etc .

Here is an implementation of an operation class that I provide , You can also implement your own operation class .

internal class DbOper<T> :IDbPhysiceOper<T>, IDisposable where T : new()
{
internal IHelper db;
internal StringBuilder where;
internal StringBuilder select;
internal StringBuilder orderby;
internal List<IDataParameter> ps;
internal StringBuilder sqlinfo;
internal int index = ;
internal int size = OrmGlobal.PageSize;// Provide a default paging size
private DbOper(IHelper h, StringBuilder w, StringBuilder s, StringBuilder or, List<IDataParameter> p,StringBuilder sql)
{
db = h;
where = w;
select = s;
orderby = or;
sqlinfo = sql;
ps = p;
}
internal DbOper(DbInfo info)
{ //db For the database operation class defined in the previous article , Switch to other databases in different ways if (info.DbType.Equals("mssql"))
{
db = new Helper.Mssql(info.DbConntion);
}
else if (info.DbType.Equals("msmars"))
{
db = new Helper.MsMars(info.DbConntion);
}
else if (info.DbType.Equals("mysql"))
{
db = new Helper.Mysql(info.DbConntion);
}
where = new StringBuilder();
select = new StringBuilder();
orderby = new StringBuilder();
sqlinfo = new StringBuilder();
ps = new List<IDataParameter>();
}
public object Insert(T m)
{
try
{
StringBuilder fields = new StringBuilder();
StringBuilder values = new StringBuilder();
List<IDataParameter> lt = new List<IDataParameter>();
string tp = string.Empty; object o = null;
foreach (var n in m.GetType().GetProperties())
{
if (n.GetCustomAttributes(typeof(ExcludeColumn), false).Length > ) { continue; }
if (n.GetCustomAttributes(typeof(Key), false).Length > ) { continue; }
o = n.GetValue(m,null);//4.5o = n.GetValue(m);
if (o == null) { continue; }
fields.Append(n.Name + ",");
tp = db.ParStr(n.Name);
values.Append(tp + ",");
lt.Add(db.Cp(tp, o));
}
if (fields.Length > ) { fields.Length--; }
if (values.Length > ) { values.Length--; }
tp = "INSERT INTO " + typeof(T).Name + "(" + fields.ToString() + ")VALUES(" + values.ToString() + ") " + db.GetIdStr;
if (OrmGlobal.isrecord) { Record(tp); }
object a = db.ExectueScalar(tp, lt, false);
Clear();
return a;
}
catch
{
OrmGlobal.DoErr(sqlinfo.ToString()); throw;
}
}
public int Update(string str)
{
try
{
string tp = "UPDATE " + typeof(T).Name + " SET " + str + (where.Length > ? " WHERE " + where : string.Empty);
if (OrmGlobal.isrecord) { Record(tp); }
int i = db.ExecuteQuery(tp, ps, false);
Clear();
return i;
}
catch
{
OrmGlobal.DoErr(sqlinfo.ToString()); throw;
}
}
public int Update(T m)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE " + typeof(T).Name + " SET ");
List<IDataParameter> lt = new List<IDataParameter>();
object o = null;
foreach (var n in m.GetType().GetProperties())
{// You need to define a feature Key, To update Model o = n.GetValue(m,null);//4.5o = n.GetValue(m);
if (o == null) { continue; }
if (n.GetCustomAttributes(typeof(Key), false).Length > )
{
where.Append((where.Length > ? " AND " : string.Empty) + n.Name + "=" + db.ParStr(n.Name));
lt.Add(db.Cp(db.ParStr(n.Name), o));
continue;
}
sb.Append(n.Name + "=" + db.ParStr(n.Name) + ",");
lt.Add(db.Cp(db.ParStr(n.Name), o));
}
if (sb.Length > ) { sb.Length--; }
if (where.Length > ) { sb.Append(" WHERE " + where); }
var sql = sb.ToString();
if (OrmGlobal.isrecord) { Record(sql); }
int i = db.ExecuteQuery(sql, lt, false);
Clear();
return i;
}
catch
{
OrmGlobal.DoErr(sqlinfo.ToString()); throw;
}
}
public int Delete()
{
try
{
string sql = "DELETE FROM " + typeof(T).Name + (where.Length > ? " WHERE " + where : string.Empty);
if (OrmGlobal.isrecord) { Record(sql); }
int i = db.ExecuteQuery(sql, ps, false);
Clear();
return i;
}
catch
{
OrmGlobal.DoErr(sqlinfo.ToString()); throw;
}
}
public IDbOper<T> Select(string sl)
{
if (string.IsNullOrEmpty(sl)) { return this; }
select.Append((select.Length > ? "," : string.Empty) + sl); return this;
}
public IDbOper<T> Select(Expression<Func<T, object>> sl)
{
string tp=null;
using (var tp1 = new LinqVisitor())
{
tp=tp1.VisitNew(sl.Body as NewExpression);
}
return Select(tp);
}
public IDbOper<T> Where(Dictionary<string, object> dic)
{
if (dic == null || dic.Count == ) { return this; }
var sb = new StringBuilder(); string tp;
foreach (var n in dic)
{
if (sb.Length > ) { sb.Append(" AND "); }
sb.Append(n.Key);
if (n.Value is string)
{
tp = n.Value.ToString();
if (tp.Substring(tp.Length - , ) == "*")
{
sb.Append(" LIKE ");
tp = tp.Substring(, tp.Length - ) + "%";
}
else { sb.Append("="); }
ps.Add(db.Cp(db.ParStr(n.Key), tp));
}
else
{
sb.Append("=");
ps.Add(db.Cp(db.ParStr(n.Key), n.Value));
}
sb.Append(db.ParStr(n.Key));
}
Where(sb.ToString());
return this;
}
public IDbOper<T> Where(string sl)
{
if (string.IsNullOrEmpty(sl)) { return this; }
where.Append((where.Length > ? " AND " : string.Empty) + sl); return this;
}
public IDbOper<T> Where(Expression<Func<T, bool>> sl)
{
List<object> tp=null; // You need to parse the expression tree using (var tp1 = new LinqVisitor())
{
tp = tp1.Visit(sl) as List<object>;
StringBuilder sb = new StringBuilder(); string s = string.Empty;
for (int i = ; i < tp.Count; i += )
{
s = db.ParStr(tp[i].ToString());
sb.Append(tp[i].ToString() + tp[i + ].ToString() + s);
if (i + < tp.Count) { sb.Append(tp[i + ]); }
ps.Add(db.Cp(s, tp[i + ]));
}
Where(sb.ToString());
}
return this;
}
public IDbOper<T> Orderby(string orby)
{
if (string.IsNullOrEmpty(orby)) { return this; }
orderby.Append((orderby.Length > ? "," : string.Empty) + orby); return this;
}
public IDbOper<T> Orderby(Dictionary<string, string> dic)
{
if (dic.Count == ) { return this; }
StringBuilder sb = new StringBuilder();
foreach (var n in dic.Keys)
{
if(string.Compare("DESC",dic[n],true)!= && string.Compare("ASC",dic[n],true)!=){continue;}
sb.Append(n + " " + dic[n] + ",");
}
if (sb.Length > ) { sb.Length--; }
Orderby(sb.ToString()); return this;
}
public IDbOper<T> Index(int i) { if (i > ) { index = i; } return this; }
public IDbOper<T> Size(int i) { if (i > ) { size = i; } return this; }
public void BegTran() { db.BegTran(); }
public void RollBack() { db.RollBack(); }
public void Commit() { db.Commit(); }
public void Clear()
{
where.Length = ; select.Length = ; orderby.Length = ; ps.Clear(); index = ; size = OrmGlobal.size;
}
public M ToObj<M>(Func<IDataReader, M> func, string sql)
{
try
{
if (OrmGlobal.isrecord) { Record(sql); }
var rd = db.ExectueReader(sql, ps, false);
M t = func(rd);
rd.Close(); Clear();
return t;
}
catch
{
OrmGlobal.DoErr(sqlinfo.ToString()); throw;
}
}
public List<T> ToList()
{
string sql = GetSql();
return ToObj<List<T>>(rd => ToList(rd),sql);
} // return List<T> type public List<T> ToList(IDataReader rd)
{
var lt = new List<T>();
var set = DelegateExpr.SetMethod(typeof(T));//ExpressTree Implement property binding , In order to improve the Model Assignment performance , It can be replaced by reflection
while (rd.Read())
{
var m = new T();
for (var i = ; i < rd.FieldCount; i++)
{
if (rd[i] == DBNull.Value || rd[i] == null) { continue; }
set(m, rd.GetName(i).ToLower(), rd[i]);
}
lt.Add(m);
}
return lt;
}
public string GetSql()
{
return db.CreateSql(select.ToString(), typeof(T).Name, where.ToString(), orderby.ToString(), size, index);
}
public IDbOper<M> ToOper<M>() where M:new()
{
Clear();
return new DbOper<M>(db,where,select,orderby,ps,sqlinfo);
}
public int Count()
{
try
{
string sql = "SELECT COUNT(*) FROM " + typeof(T).Name + (where.Length > ? " WHERE " + where : string.Empty);
if (OrmGlobal.RecordLog) { Record(sql); }
int i= (int)db.ExectueScalar(sql, ps, false);
Clear();
return i;
}
catch
{
OrmGlobal.DoErr(sqlinfo.ToString()); throw;
}
}
public int DoCommand(string sql,bool issp)
{
int i=db.ExecuteQuery(sql,ps,issp);
Clear();
return i;
}
public void Dispose()
{
where = null; select = null; orderby = null; db.Dispose(); ps = null; sqlinfo = null;
GC.SuppressFinalize(this);
}
public T First()
{
var lt=Size().Index().ToList();
if (lt.Count > ) { return lt[]; }
return default(T);
}
~DbOper()
{
Dispose();
}
}
 
 

A simple ORM Make (CURD Operation class ) More articles about

  1. A simple ORM Make (SQL Helper classes )

    A simple ORM The production needs the following categories : SQL Executive class CURD Operation class Other soy sauce First from SQL Execution class , It may involve database migration and other issues , So you need to define an interface to facilitate migration to other databases , The transaction does not provide a name , if ...

  2. Python Metaclass programming implements a simple ORM

    summary What is? ORM? ORM Full name "Object Relational Mapping", Namely object - Relation mapping , Mapping a row of a relational database to an object , That is, a class corresponds to a table , such , Write code ...

  3. Share a simple C# Common to DbHelper class ( Support data connection pool )

    Every time there's a new project , We have to look for the database tool class from the beginning . Here is a simple and practical C# Common to DbHelper Tool class , Support data connection pool . Connection pool configuration <connectionStrings> <add ...

  4. Laravel Simple user management framework [CURD] operation

    One is based on laravel and bootstrap Simple user management , It's suitable for us who just started , In the process of doing it, you can deepen your understanding of laravel Basic understanding , There are some problems , It hasn't been revised yet , such as css and js The introduction of , The form submission address is not standardized ...

  5. Recommend a good one HTTP Operation class

    /// <summary> /// Class description :HttpHelps class , Used to implement Http visit ,Post perhaps Get The way of , Direct access , belt Cookie Of , With certificate, etc , You can set up a proxy /// Important note ...

  6. C# Using the reflection + Feature to implement a simple entity mapping database operation class

    Attach source code : using System; using System.Collections.Generic; using System.Data; using System.Linq; using S ...

  7. use php Realize a simple chain operation

    Recently read <php Core technologies and best practices > This book , The first chapter of the book mentions the use of __call() Method can implement a simple string chain operation , such as , Here's the operation of filtering strings and then calculating the length , It's usually written like this : strlen( ...

  8. PHP Database operation class :ezSQL

    EZSQL Class introduction : Download address :http://www.jb51.net/codes/26393.htmlezsql Is a small fast database operation class , It's easy for you to use PHP Operate various databases ( MySQL.o ...

  9. Java Large number operation class

    Java The large number operation of is divided into BigInteger and BigDecimal, But these two classes are used separately , Sometimes it seems a little cumbersome when programming , Now I've written a large number operation class that combines the two . The code of large number operation class is as follows : packag ...

Random recommendation

  1. Linux Forget password solution

    Many friends often forget Linux Systematic root password ,linux System forgetting root What to do with the password ? Re install the system ? Of course not. ! Go into single user mode and change root password . Steps are as follows : restart linux System 3  ...

  2. Java Inner classes and anonymous classes Realization JButton action ActionListener class

    import javax.swing.*; import java.awt.*; import java.awt.event.*; public class ControlCircle2 extend ...

  3. sense cocos2d-x-3.0 Architecture design Cocos2d (v.3.0) rendering pipeline roadmap( original text )

    Cocos2d (v.3.0) rendering pipeline roadmap Why (the vision) The way currently Cocos2d does rendering ...

  4. pom.xml Configuration file configuration jar( Don't have to remember , Quick configuration )

    1: website :http://mvnrepository.com/ 2: Search the search bar for the frame you want to use ; for example spring * Here is an example

  5. Varnish Of vcl Subroutines

    Varnish Of VCL Subroutines Please refer to : http://book.varnish-software.com/4.0/ VCL Subprocesses , In which to customize Varnish act .VCL Subroutines can be used to : Add custom tags ...

  6. Use requireJS

    What is? require? require yes AMD The concrete implementation of modular specification . at present , The passage of the js There are two kinds of modular specifications ,CommonJS and AMD. CommonJS and AMD What's the difference ? CommonJS Mainly for services ...

  7. Implementation of various sorting algorithms C# edition

    using System;using System.CodeDom;using System.Collections.Generic;using System.Linq;using System.Ru ...

  8. Linux The data structure in the kernel : Double linked list

    original text :https://blog.csdn.net/qq_33487044/article/details/78827260 Double linked list Linux The kernel itself implements a two-way linked list , Can be in include/lin ...

  9. AVL Understanding and self writing of trees AVL Trees

    AVL Tree is the first self balanced binary search tree . stay AVL The maximum height difference between two subtrees of any node in the tree is one , So it's also known as the height balance tree . lookup . Insert and delete are... On average and at worst O(log n). Additions and deletions may need to go through one or more ...

  10. BZOJ.2301.[HAOI2011]Problem B( Mobius inversion A class )

    [Update] I don't seem to understand what I was writing now =-= \(Description\) seek \(\sum_{i=a}^b\sum_{j=c}^d[(i,j)=k]\) \(Solution\) First ...