SQL reflection addition, deletion, query, Modification

Using system; using system. collections. generic; using system. text; using system. reflection; using system. data; using system. data. oledb; namespace Dal {public class sqlbuilder <t> where T: Class {# region prop private list <propertyinfo> props = new list <propertyinfo> (); Private propertyinfo PID; private string _ id; Public String ID {get {return _ id;} set {_ id = value ;}} private string _ tablename; Pu BLIC string tablename {get {return _ tablename;} set {_ tablename = value ;}} public ienumerable <string> getnamesexceptid () {foreach (propertyinfo P in props) yield return p. name;} public sqlbuilder (string ID, string tablename) {This. _ id = ID; this. _ tablename = tablename; foreach (propertyinfo P in typeof (t ). getproperties () {If (! P. name. equals (ID, stringcomparison. ordinalignorecase) This. props. add (p); else this. PID = P ;}# endregion # Region insert private const string insert_ SQL = "insert into [{0}] ({1}) values ({2 })"; public String buildparinsert () {string F1 = getmergefields (false); string F2 = getmergefields (true); Return string. format (insert_ SQL, tablename, F1, F2);} public oledbparameter [] getinsertparameter (T ob J) {list <oledbparameter> List = new list <oledbparameter> (); foreach (propertyinfo P in props) {list. add (New oledbparameter ("@" + P. name, P. getvalue (OBJ, null);} return list. toarray ();} Public String buildinsert (t obj) {list <string> List = new list <string> (); foreach (propertyinfo P in props) {If (P! = NULL) list. add (getsqlfield (OBJ, p);} string F1 = getmergefields (false); string F2 = string. join (",", list. toarray (); Return string. format (insert_ SQL, tablename, F1, F2) ;}# endregion # region update private const string update_ SQL = "Update [{0}] set {1} Where {2 }"; public String buildupdate (t obj) {list <string> List = new list <string> (); foreach (propertyinfo P in props) {string key = P. name; String value = getsqlfield (OBJ, P); list. add (string. format ("[{0}] = {1}", key, value);} string F1 = string. join (",", list. toarray (); string F2 = string. format ("[{0}] = {1}", ID, getsqlfield (OBJ, pid); Return string. format (update_ SQL, tablename, F1, F2 );} # endregion # region utils // <summary> // obtain the insert parameters /// </Summary> private string getmergefields (bool ISPAR) {list <string> List = new List <string> (); foreach (string key in this. getnamesexceptid () {If (ISPAR) list. add ("@" + key); else list. add ("[" + key + "]");} return string. join (",", list. toarray ();} // <summary> // obtain the parameter of update (parameters) // </Summary> private string getfieldspair () {list <string> List = new list <string> (); foreach (string key in this. getnamesexceptid () {list. add (string. format ("{0 }=@{ 0}", key ));} Return string. join (",", list. toarray ();} Private Static string getsqlfield (t obj, propertyinfo prop) {If (prop. propertytype. equals (typeof (string) Return "'" + Prop. getvalue (OBJ, null) as string + "'"; if (prop. propertytype. equals (typeof (datetime) Return "#" + Prop. getvalue (OBJ, null ). tostring () + "#"; return prop. getvalue (OBJ, null ). tostring () ;}/// <summary> // supports string datetime double Int // </Summary> Private Static void setsqlfield (ref t OBJ, propertyinfo prop, object Value) {If (value = NULL | value = dbnull. value) return; If (prop. propertytype. equals (typeof (string) {string S = (string) value; prop. setvalue (OBJ, S, null);} else if (prop. propertytype. equals (typeof (datetime) {datetime time = (datetime) value; prop. setvalue (OBJ, time, null);} else if (prop. propertyty PE. equals (typeof (INT) {int I = (INT) value; prop. setvalue (OBJ, I, null);} else if (prop. propertytype. equals (typeof (double) {// double I = (double) value; prop. setvalue (OBJ, (double) value, null) ;}# endregion # region parser public static t Parser (idatareader reader) {If (! Reader. read () return NULL; t OBJ = activator. createinstance <t> (); foreach (propertyinfo P in typeof (t ). getproperties () {int I = readerexists (reader, P. name); if (I! =-1) setsqlfield (ref OBJ, P, reader [I]);} return OBJ;} public static t Parser (datarow row) {If (ROW = NULL) return NULL; t OBJ = activator. createinstance <t> (); foreach (propertyinfo P in typeof (t ). getproperties () {int I = tableexists (row. table, P. name); if (I! =-1) setsqlfield (ref OBJ, P, row [I]);} return OBJ;} public static list <t> Parser (datatable DT) {list <t> List = new list <t> (); foreach (datarow row in DT. rows) {t obj = Parser (ROW); If (OBJ! = NULL) list. add (OBJ);} return list ;} /// <summary> /// determine whether the datatable contains the specified column // </Summary> Public static int tableexists (datatable DT, string columnname) {for (INT I = 0; I <DT. columns. count; I ++) {string name = DT. columns [I]. columnname; If (string. equals (name, columnname, stringcomparison. ordinalignorecase) return I;} return-1 ;} /// <summary> /// determine whether the specified column is contained in the datareader. // </Summary> Public static int readerexists (idatareader DR, string columnname) {int COUNT = dr. fieldcount; For (INT I = 0; I <count; I ++) {string name = dr. getname (I); If (string. equals (name, columnname, stringcomparison. ordinalignorecase) return I;} return-1 ;}# endregion }}

Using system; using system. collections. generic; using system. data; using system. text; namespace Dal {public abstract class topservice <t> where T: Class {# region abstract protected abstract sqlbuilder <t> getbuilder (); protected abstract string gettablename (); protected abstract string getidname (); Public String tablename {get {return gettablename () ;}} Public String idname {get {return getidname ();}} public sqlbuilder <t> builder {get {return getbuilder ();}} # endregion # Add, query, modify, and delete a region SQL statement // <summary> // insert a data record // </Summary> Public Virtual int insert (T OBJ) {string SQL = getbuilder (). buildinsert (OBJ); Return dbhelper. executecommand (SQL);} // <summary> // update a data record // </Summary> Public Virtual int Update (T OBJ) {string SQL = getbuilder (). buildupdate (OBJ); Return dbhelper. executecommand (SQL) ;}/// <summary> /// delete a piece of data /// </Summary> Public int Delete (int id) {string SQL = string. format ("delete from [{0}] Where [{1}] = {2}", this. tablename, this. idname, id. tostring (); Return dbhelper. executecommand (SQL) ;}/// <summary> /// obtain the object based on the ID /// </Summary> Public t getbyid (int id) {string SQL = string. format ("select * from [{0}] Where [{1}] = {2}", this. tablename, this. idname, ID); idatareader reader = dbhelper. getreader (SQL); Return sqlbuilder <t>. parser (Reader) ;}/// <summary> /// get the datatable of all objects /// </Summary> Public datatable selectall () {string SQL = string. format ("select * from [{0}]", this. tablename); Return gettablebysql (SQL );} /// <summary> /// return the resolved object list based on the SQL statement // </Summary> public list <t> getobjbysql (string SQL) {datatable dt = gettablebysql (SQL); Return sqlbuilder <t>. parser (DT) ;}# endregion # other region functions /// <summary> /// obtain the first n items /// </Summary> Public datatable gettop (int n, bool isdesc) {string SQL = string. format ("select top {2} * from [{0}] Order by [{1}]", this. tablename, this. idname, n); If (isdesc) SQL + = "DESC"; return gettablebysql (SQL );} /// <summary> /// obtain the first n objects by ID sorting // </Summary> public list <t> gettopobjorderbyid (int n, bool isdesc) {string SQL = string. format ("select top {2} * from [{0}] Order by [{1}]", this. tablename, this. idname, n); If (isdesc) SQL + = "DESC"; return getobjbysql (SQL);} public datatable gettablebysql (string SQL) {try {return dbhelper. getdataset (SQL);} catch (exception e) {console. writeline (E. message); throw ;}# endregion }}

The efficiency may not be as good as the stored procedure, but the amount of code is greatly reduced.

Applicable to access MSSQL (time #-> quotation marks)