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 the OLEDBCOMMAND object, you must have a row of CB = New OLEDBCommandBuilder (DA);
CMD = New OLEDBCommand ();
} #ENDREGION INITIALIZE
/ / Connecting string #Region Get & setConnectionString
///
#ndregion get & setConnectionString
/ / Get the name of the table #Region AcquiRetableNames
///
TBL = cnn.getoledbschematable (OLEDBSChemaGuid.tables, new object [] {null, null, null, "table"});} catch (exception ce) {console.writeline ("Generate error: / n {0}", CE . Loading);} finally {cnn.close ();} Return TBL;
#ndregion acquiretablenames // Plip data #Region FillTable
///
// try // {// tblname.clear (); da.dispose (); if (cnn.connectionstring == ") cnn.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 * " TBLNAME.TABLENAME " where " sqlstr; da.selectcommand = cmd; i = da.fill (tblname); // ///} // Catch (Exception CE) // {// console.writeline "Generate error: / n {0}", ce.Message); //} // finally // {//this.da.dispose (); cnn.close (); //} return i;} #ENDREGION FillTable // Insert #Region INSERT (Use CommandBuilder) ///
// If you do not generate new OLEDBDataAdapter, OLEDBCommandBuilda, OLEDBCommand, // and use the original global DA, CB, CMD, you can 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 (DataTable dt in da.TableMappings) // {// if (dt. Tablename! = Tblname.tablename) // dt.clear (); //} TBLNAME.ROWS.ADD (new); 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 Da.insertCommand.Transaction = Tran; try {num [i] = da.UPDate (TBLS [i]); SUM = NUM [I];} catCH {SUM = -1;} IF (NUM [i] == 0) Judge = true; IF (judge) {TRAN. ROLLBACK (); str = "Update Failed"; SUM = 0;} else {TRAN.COMMIT (); Str = "Update Success";}} cnn.close (); Return Str ", Impact" Sum.ToString () "Sum Record";} #endregion insert (Use InsIDetraction, DataTable []) // Insert Record #Region Insert (Use outsidetractions) /// // try // {// tblname.clear (); da.dispose (); if (cnn.connectionstring == ") cnn.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 * " TBLNAME.TABLENAME " Where " sqlstr; da.selectcommand = cmd; Cmd.Transaction = TRS; I = da.fill (TBLNAME); Return i;} /// Da.insertCommand = INSERTCMD (TBLNAME, "NOO"); int Num = 0; Try {TBLNAME.ROWS.ADD (NewRow); Da.insertCommand.Transaction = TRS; Num = da.Update (TBLNAME);} catch { } Return num; #ndregion insert (use outsidetraction) // Constructed inserted Command #Region InsertCommand /// String SQLSTR = ""; string strongue = ""; SQLSTR = "INSERT" DTL.TABLENAME.TOSTRING () "("; strval = ") VALUES ("; for (int i = 0; i SQLSTR = STRVALUE ")" Incmd.commandtext = SQLSTR; RETURN INCMD; } #ndregion insertcommand // Modify #Region Update /// // Automatic update to the OLEDBDataAdapter.Update method must exist in WHERE or unique value // try // {// cnn.open (); rows = tblname.rows.count; for (int i = 0; i / (Note, as in front of Da.Update, the following AcceptChanges method is used, because the record is deleted - updated to the database failed //TBLNAME.ACCEPTCHANGES (); da.Update (tblname); ////} // catch (Exception CE) // {// Console.writeLine ("Generated Error: / N {0}", CE.MESSAGE); //} // Finally // {CNN.Close (); //} /// // Directly update // try // {// string str = "delete from" tblname.tablename "where" strDel; // cnn.open (); // oledbcommand cmdd = new OLEDBCOMMAND (STR, CNN) ); // cmdd.commandtype = commandType.text; // rows = cmdd.executenonQuery (); //} // // {// CONSOLE.WRITELINE ("Generation error: / n { 0} ", ce.Message); //} // finally // {// cnn.close (); //} Return Rows;} #endregion del (Use commandbuilder) // Construct Deleted Command #Region Delcommand Create OledbDataAdapter.deleteCommand) Public int delete (DataTable TBLNAME) {int ROWS = 0; da.deleteCommand = DELCMD (TBLNAME); for (int i = 0; i Rows = da.Update (TBLNAME); Return rows; Private static oledbcommand delcmd (DATATABLE DTL) {oledbcommand delcmd = new oledbcommand (); Delcmd.connection = CNN; String SQLSTR = ""; sqlstr = "delete from" DTL.TABLENAME.TOSTRING () "where"; for (int i = 0; i INT J = Delcmd.Parameters.count; Bool B = dtl.columns [i] .allowdbnull; if (b) {oledbparameter mypara1 = new oledbParameter (); mypara1.parametername = "OR2_" DTL.COLUMNS [i] .tostring (); myPara1.OleDbType = GetOleDbType (dtl.Columns [i] .DataType.ToString ()); myPara1.Direction = ParameterDirection.Input; myPara1.SourceColumn = dtl.Columns [i] .ToString (); myPara1.SourceVersion = DataRowVersion.original; delcmd.parameters.add (mypara1); j = delcmd.parameters.count;}} SQLSTR = SQLSTR.SUBSTRING (0, SQLSTR.LENGTH-3); Delcmd.commandtext = SQLSTR; Return Delcmd; } #ndregion delcommand (Create Oledbdataadapter.deleteCommand) #Region amendDatabase public void addColumn (data) / / modify the structure of the table, update to the database {cnn.open (); // oleDbcommand cmds = new OLEDBCOMMAND ("SELECT *" TBLNAME.TABLENAME, CNN) ; // da.SelectCommand = cmdS; // OleDbCommandBuilder cb = new OleDbCommandBuilder (da); // DataColumn colItem = new DataColumn (strUp, Type.GetType ( "System.String")); // // tblName.Columns. Add (// Why is the above method not, can you use the SQL statement directly? Da.update (TBLNAME);} #ENDREGION AmendDatabase // Call the stored procedure #REGON EXECPROC (Return DataTable) /// PARANAME [I] = "@" para] [// Parameter name //pt.parametername =paraname []; pt.sourceColumn = paraName [i]; Pt.oledbType = getoledbtype (Paratype [i]); Pt.Value = Paravalue [i]; CMDP.Parameters.Add (Pt); DataTable DTL = New DataTable (); cnn.open (); da.selectcommand = cmdp; da.fill (dtl); cnn.close (); return dtl;} ///
