SQL Server Single-table Crud common method

First, the application scenario

① when you write simple additions and deletions to check the heart tired

② when your project does not consider concurrency, high performance

③ when you pursue faster development efficiency

④ when your business involves only a single table

Second, the Code show

① Single-Table insert

　　

1  Public BOOLInsert<t>(T t)2 {3     varo =T.gettype ();4     varSbulider =NewStringBuilder ("Insert into");5Sbulider.append ("["+ O.name +"]");6Sbulider.append ("(");7     varProperties =o.getproperties ();8     foreach(varPinchproperties)9     {Ten sbulider.append (p.name); OneSbulider.append (","); A     } -sbulider.length--; -Sbulider.append (") VALUES ("); the     foreach(varPinchproperties) -     { -Sbulider.appendformat ("' {0} '", P.getvalue (T,NULL)); -Sbulider.append (","); +     } -sbulider.length--; +Sbulider.append (")"); A     returnSqldbhelper.executesql (Sbulider.tostring ()) >0; at}

View Code

② Single-Table Delete

　　

1  Public BOOLDelete (stringTableName,stringidlist)2 {3     varids =NewStringBuilder ();4     foreach(varIdinchIdlist.split (','))5     {6Ids. Append ("'");7 IDs. Append (ID);8Ids. Append ("',");9     }TenIds. length--; One     varsql =string. Format ("Delete from [{0}] where Id in ({1})", TableName, IDs. ToString ()); A     returnSqldbhelper.executesql (SQL) >0; -}

View Code

③ Single-table update

　　

1  Public BOOLUpdate<t>(T t)2 {3     varo =T.gettype ();4     varSbulider =NewStringBuilder ("Update");5Sbulider.append ("["+ O.name +"]");6Sbulider.append ("Set");7     varID ="";8     varProperties =o.getproperties ();9     foreach(varPinchproperties)Ten     { One         varColumnName =P.name; A         varColumnvalue = P.getvalue (t,NULL); -         if(ColumnName = ="Id") -ID =columnvalue.tostring (); the sbulider.append (columnName); -Sbulider.appendformat ("= ' {0} '", columnvalue); -Sbulider.append (","); -     } +sbulider.length--; -Sbulider.appendformat ("where id= ' {0} '", id); +     returnSqldbhelper.executesql (Sbulider.tostring ()) >0; A}

View Code

④ Single-Table Select

　　

1  PublicList<t> serarchlist<t> (string where="")whereT:New()2 {3     varTableName =NewT (). GetType (). Name;4     varSqlString =NewStringBuilder ();5Sqlstring.appendformat ("SELECT * from [{0}]", tableName);6     if(!string. IsNullOrEmpty (where))7     {8Sqlstring.appendformat ("where ' {0} '",where);9     }Ten     varList =NewList<t>(); One     using(varDataReader =Sqldbhelper.executereader (sqlstring.tostring ())) A     { -          while(Datareader.read ()) -         { the             vart =NewT (); -             varProperties =T.gettype (). GetProperties (); -             foreach(varPinchproperties) -             { +P.setvalue (t, Datareader[p.name],NULL); -             } + list. ADD (t); A         } at     } -     returnlist; -}

View Code

Third, limitations

① entity class name must match the table name

② primary Key Name must be an ID

③ not suitable for self-increment primary keys

④ problem is difficult to debug (can add log trace)

⑤ Low security (burst table name and field name)

⑥ achieve paging too violent

⑦ Low flexibility (universal and flexible forever contradictory body)

Full table update when ⑧update operation

⑨select Operation full Field query

Iv. Source code Download

SourceCode

SQL Server Single-table Crud common method