public class operateDB {private static string conStr; public static OleDbConnection cnn; OleDbDataAdapter da; OleDbCommandBuilder cb; OleDbCommand cmd; // Constructors #region initialize public operateDB () {// // TODO: Add constructor logic here // CNN = new OLEDBCONNECTION (); DA = New OLEDBDataAdapter (); // Do not have to update to the database without OLEDBCOMMAND objects, there must be the following line CB = New OLEDBCommandBuilder (DA); cmd = new oledbcommand ();} #ENDREGION INITIALIZE / / Connection String #Region Get & setConnectionstring /// /// Get connection string /// public string myconstr {get {return constr;} set {constr = value;}} #ENDREGON GET & setConnectionstring // Get Table Name #Region acquiRetablenames /// /// Gets table names in the database /// /// public dataable tablescollection () {dataable TBL = New DataTable ); Try {cnn.connectionstring = constr; cnn.open (); TBL = cnn.getoledbschematable (OLEDBSChemaGuid.tables, new object [] {null, null, null, "table"}); } Catch ("EXCEPTION CE) {Console.Writeline (" Generated Error: / N {0} ", CE.MESSAGE);} finally {cnn.close ();} return tbl;} #ENDREGION AcquiRetablenames // Plugging data #Region FillTable /// /// Plip the DataTable query /// /// data table (must enter the name existing in the database, or view) /// SQL statement /// record number public int select (datatable tblname, string sqlstr) {INT i = 0; ///// Try // {// tblname.clear (); da.dispose (); if (cnn.connectionstring == "
) Cnn.connectionstring = constr; if (cnn.state! = ConnectionState.open) cnn.open (); // oleDbcommand cmd = new oledbcommand ("SELECT *" TBLNAME.TABLENAME "Where" SQLSTR, CNN); cmd.Connection = cnn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from" tblName.TableName "where" sqlStr; da.SelectCommand = cmd; i = da.Fill (tblName); // //} // Catch (Exception CE) // {// Console.WriteLine ("Generated Error: / n {0}", CE.MESSAGE); //} // finally /// {// this. Da.dispose (); cnn.close (); //} Return i;} #endregion fixtable // Insert record #Region INSERT (Use commandbuilder) /// /// Insert record (with oledbcommandbuilder) //// / /// Data table /// New line ///// The number of rows of influence public int insert (DataTable TBLNAME, DATAROW NEWROW) {cnn.open (); int i = 0; // // // {// How to determine if oledbdataadapter is Dispose // If you don't generate new OLE DbDataAdapter, OleDbCommandBuilder, OleDbCommand, // but with the original global da, cb, cmd, then in one operation only update a table OleDbDataAdapter daIn = new OleDbDataAdapter (); OleDbCommandBuilder cbIn = new OleDbCommandBuilder (daIn); OleDbCommand cmdIn = New OLEDBCOMMAND ("Select * from" TBLNAME.TABLENAME, CNN); dain.selectcommand = cmdin; // foreach (dataable dt in da.tablemappings) // {// if (dt.tablename! = tblname.tablename) / / Dt.clear (); //} TBLName.Rows.Add (newrow); i = dain.Update (tblname);
// //} // Catch (Exception CE) // {// Console.Writeline ("Generated Error: / n {0}", CE.MESSAGE); //} // finally // {// CNN. Close (); //} // cnn.close (); Return i;} #endregion insert (use commandbuilder) // Insert #Region INSERT (Use Insidetraction, DataTable []) Public String Insert (DataTable [] TBLS, DataRow [] newrows) {int [] num = new int [TBLS.LENGTH]; int sum = 0; BOOL JUDGE = false; string str = ""; if (tbls.length == newrows.Length) {cnn.open (); OLEDBTRANSACTION TRAN = cnn.begintransaction (); for (int i = 0; i /// populate DATATABLE (for transaction) /// /// table /// SQL statement /// Transaction object /// Row
Public int SELECT (DataTable TBLNAME, STRING SQLSTR, OLEDBTRANSACTION TRS) {INT i = 0; // Try // {// TBLName.cle (); da.dispose (); if (cnn.connectionstring == ") .Connectionstring = constr; if (cnn.state! = ConnectionState.open) cnn.open (); // oledbcommand cmd = new oledbcommand ("Select * from" tblname.tablename "Where" SQLSTR, CNN); cmd. Connection = cnn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from" tblName.TableName "where" sqlStr; da.SelectCommand = cmd; cmd.Transaction = trs; i = da.Fill (tblName ); RETURN I;} /// /// Insert record (with oledbdataadapter.Update method and oledbtransaction) /// /// Data table /// New row /// Transaction object /// public int insert (DataTable TBLNAME , DATAROW NEWROW, OLEDBTRANSACTION TRS) {Da.insertCommand = INSERTCMD (TBLNAME, "NOO"); int Num = 0; try {tblname.rows .Add (newrow); da.insertcommand.transaction = trs; Num = da.Update (tblname);} catch {} return number;} #endregion insert (use outsidetractions) // Constructed insertion of Command #Region InsertCommand ///// /// Construct INSERTCOMMAND / / Data table /// Identity column name ///
private static OleDbCommand insertCmd (DataTable dtl, string identityCol) {OleDbCommand inCmd = new OleDbCommand (); inCmd.Connection = cnn; string sqlStr = ""; string strValue = ""; sqlStr = "INSERT" dtl.TableName.ToString ( ) "("; Strval = ") VALUES ("; for (int i = 0; i // / modified record /// /// Data table /// SQL statement /// The number of rows affects public int update (DataTable TBLNAME, STRING STRUP) {CNN.Close ); Return i;} #endregion update // Delete #Region del (Use commandbuilder) /// /// delete record /// /// <
Param name = "tblname"> Data table /// SQL statement /// The number of rows affects the number of rows affects (DataTable TBLNAME) String strdel) // strdel is delete condition {int ROWS = 0; // Automatic update with oleDbdataadapter.Update method must have primary key or unique value in WHEREE or unique value // try // {// cnn.open (); rows = TBLNAME.ROWS.COUNT; for (int i = 0; i
New oledbcommand (); delcmd.connection = cnn; string sqlstr = ""; sqlstr = "delete from" DTL.TABLENAME.TOSTRING () "where"; for (int i = 0; i
TblName.TableName, cnn); // da.SelectCommand = cmdS; // OleDbCommandBuilder cb = new OleDbCommandBuilder (da); // DataColumn colItem = new DataColumn (strUp, Type.GetType ( "System.String")); / / // tblname.columns.add (color); // Why is the above method not, can only use the SQL statement directly? Da.update (TBLNAME);} #ENDREGION amendDatabase // Call Storage Process #Region Execproc (Return DataTable) /// /// Execute stored procedure /// /// Stored Procedure Name Parameter value /// parameter name /// Parameter type // / public DataTable ExecProc (string procName, string [] ParaValue, string [] ParaName, string [] ParaType) {OleDbCommand cmdp = new OleDbCommand (); cmdp.Connection = cnn; cmdp.CommandType = CommandType .StoredProcedure; cmdp.commandtext = procname; for (int i = 0; i /// Set OLEDBParameter object DBTYPE (Type of the OLEDBTYPE Type of the string) /// /// Incoming parameters ///
