The most annoying in database development is entry and editing, written a long SQL does not say, where there is a type conversion, the SQL statement written is not known to himself, but also to personally debug for a long time, and only in runtime I only know that SQL is right. Once the database is changed because of the demand changes, the whole SQL statement has a large change, and the database development is simply in the splicing SQL string. Is there any way to leave this nightmare? If you use the object The database is fine. Below is a little experience in the accumulation of my development, and I have to share it with everyone, I hope to improve it together.
Purpose: You don't have to write SQL for INSERT and UPDATE. You don't have to debug. A success. If there is 100 fields in a table, you don't have to worry about writing a long SQL string, there is no need to do any data type in the middle. Conversion, if you want to make it more convenient, you can use ORM to refer to ORM.NET,
-----------------ASP.NET usage is as follows --------------------------- ----------------
MIS DB = New Mis (); db.tablename = "test"; or MIS DB = New MIS ("TableName"); db.Save ("Column1", INTTYPEVALUE1); db.save (" Column2 ", DoubleTypevalue2; db.save (" Column3 ", DateTimeTyPeValue3); db.endinsert ()
Update example:
MIS DB = New MIS ("TableName"); db.SAGINUPDATE (); DB.SAVE ("Column1", 123.221); // floatdb.save ("Coilumn", "Test"); // Character DB.Updatewhere "ID = 100"); // Update Table1 set a = 11 where id =? Db.endupdate ();
-------------------------------------------------public Code ------------------------------------------
// Copyright, if you need to reprint, please indicate it, you can notify himself chaiwei net@hotmail.com 2004.8 Publish
Using system.collections; using system.data; using system.data.sqlclient;
Namespace chaiwei.db {///
Public mis () {m_hstable = new hashtable ();
Public MIS (String Table) {m_table = table; m_hstable = new hashtable ();} protected string m_table; protected hashtable m_hstable; // Hayi key value table protected string m_strwhere; // Update Condition Settings
// // public string Tablename {get {return m_table;} set {m_table = value;}}
public static int getLastID (string tableName) {DbClass db = new DbClass (); db.InitConnection ( "conn"); string sql = "select IDENT_CURRENT ( '" tableName "')"; SqlDataReader reader = db.ExecuteSqlDataReader ( SQL); if (Reader.Read ()) {IF (Reader.Indbnull (0)) Return -1; Else {Int Ret = System.Convert.Toint32 (Reader.getValue (0)); Return Ret;
}} Return -1;}
/// // public static string getSheettype1 (int ID) {string retval = ""; dbclass db = new dbclass (); db.initconnection ("conn"); string sql = "select description from shop sheettype1 where id =" id.Tostring () ""; sqldataareader reader = db.executesqldatareader (SQL); while (Reader.Read ()) {retval = reader.getstring (0);}
Return retval;} // // public void begininsert () {// can do some check}
// // public void endinsert () {if (m_table == null || m_table.Length <1) Return; string sql = getInsertsql ();
DBCLASS DB = New dbclass (); db.initconnection ("conn"); db.ExecutenonQuery (SQL); db.closeconn ();
// // public void beginupdate () {} // // public void updatewhere (string where) {m_strwhere = where;} // // public void endupdate () {if (m_table == null || m_table.length <1 || m_strwhere == NULL || m_strwhere.length <1) Return; string SQL = getUpdatesql (); dbclass db = new dbclass (); db.initconnection ("conn"); db.executenonquery (sql); db.closeconn ();
// / / Specialized one, no type of conversion on the page // Public Virtual Void Save (String ColumnName, String ColumnValue, Bool ISTXT) {if (ISTXT == True) {m_hstable.add (colornname, "'" ColumnValue "'");} else {m_hstable.add (columnName, "'" columnValue "');}} /// // Public Virtual Void Save (String ColumnValue) {////// M_Hstable.Add (ColumnName, "'" ColumnValue "'");} // // Public Virtual Void Save (String ColumnName, Bool ColumnValue) {if (ColumnValue == True) M_HSTABLE .Add (ColumnName, "1"); else m_hstable.add (ColumnName, "0");
} // // // public virtual void save (string ColumnName, int ColumnValue) {string val = System.Convert.ToString (ColumnValue); m_hstable.Add (ColumnName, val);} // // // public virtual void save (string ColumnName, float ColumnValue) {m_hstable.Add (ColumnName, ColumnValue.ToString ());} // // // protected string getInsertSQL () {IDictionaryEnumerator myEnumerator = m_hstable.GetEnumerator (); StringBuilder sql_key = new StringBuilder ( ); Stringbuilder sql_val = new stringbuilder (); string key = "; string val ="; int count = 0; // enumeration count while (myenumerator.movenext ()) {count ;
Key = myenumerator.key.tostring (); val = myenumerator.value.tostring (); sql_key.append (key); sql_val.append (val); if (m_hstable.count <= count) {Continue;} else {SQL_Key .Append (","); sql_val.append (",");}} String SQL; SQL = "INSERT INTO" M_TABLE "(" SQL_KEY ") VALUES (" SQL_VAL ")"; M_HSTABLE .Clear (); return sql;} // // // protected string getUpdateSQL () {if (m_strWhere == null || m_strWhere.Length <1) return null; string sql_set; IDictionaryEnumerator myEnumerator = m_hstable.GetEnumerator (); Stringbuilder SQL_A = New StringBuilder (); String Key = "; String Val =" "; int count = 0; // enumeration count while (myenumerator.movenext ()) {count ; key = myenumerator.key.tostring (); Val = myenumerator.value.tostring (); string setVal = key "=" val; sql_a.append (setVal); if (m_hstable.count <= count) {Continue;} else {SQL_A.Append (",");}} SQL_SET = SQL_A.TOSTRING (); string sql = "update" m_table "set" SQL_SET "Where" m_strwhere; return SQL;} } // end class}
