public void load_list() { try { string workerName = "admin"; //Session["USER_ID"].ToString(); string connection = ConfigurationSettings.AppSettings["ConnectionString"]; OdbcConnection dbCon = new OdbcConnection(connection); dbCon.Open(); string sql = "select count(*) from Board"; OdbcCommand cmd = new OdbcCommand(sql, dbCon); total = int.Parse(cmd.ExecuteScalar().ToString()); cmd.CommandText = "select * from Board order by headnum DESC, depth ASC"; OdbcDataAdapter dbAdap = new OdbcDataAdapter(cmd); DataSet ds = new DataSet(); dbAdap.Fill(ds); dbAdap.Update(ds); GridView1.DataSource = ds; GridView1.DataBind(); dbCon.Close(); } catch { } }
public void GetUpdateCommandDBConcurrencyExceptionTest () { OdbcConnection conn = (OdbcConnection) ConnectionManager.Singleton.Connection; try { string selectQuery = "select id, lname from employee where id = 3"; OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn); DataSet ds = new DataSet (); da.Fill (ds, "IntTest"); Assert.AreEqual (1, ds.Tables.Count); OdbcCommandBuilder cb = new OdbcCommandBuilder (da); Assert.IsNotNull (cb); DataRow [] rows = ds.Tables [0].Select ("id=1"); rows [0] [0] = 6660; // non existent ds.Tables [0].AcceptChanges (); // moves 6660 to original value rows [0] [0] = 1; // moves 6660 as search key into db table try { da.Update (rows); Assert.Fail ("#1"); } catch (DBConcurrencyException ex) { // Concurrency violation: the UpdateCommand // affected 0 records Assert.AreEqual (typeof (DBConcurrencyException), ex.GetType (), "#2"); Assert.IsNull (ex.InnerException, "#3"); Assert.IsNotNull (ex.Message, "#4"); Assert.AreSame (rows [0], ex.Row, "#5"); Assert.AreEqual (1, ex.RowCount, "#6"); } } finally { ConnectionManager.Singleton.CloseConnection (); } }
// Sauvegarde tous les changements effectué dans le dataset public void SaveDataSet(string tableName, DataSet dataSet) { if (dataSet.HasChanges() == false) return; switch (connType) { case ConnectionType.DATABASE_MSSQL: { try { var conn = new SqlConnection(connString); var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn); var builder = new SqlCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; DataSet changes = dataSet.GetChanges(); adapter.Update(changes, tableName); PrintDatasetErrors(changes); dataSet.AcceptChanges(); } conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table " + tableName, ex); } break; } case ConnectionType.DATABASE_ODBC: { try { var conn = new OdbcConnection(connString); var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn); var builder = new OdbcCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); DataSet changes; lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; changes = dataSet.GetChanges(); adapter.Update(changes, tableName); dataSet.AcceptChanges(); } PrintDatasetErrors(changes); conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table ", ex); } break; } case ConnectionType.DATABASE_MYSQL: { return; } case ConnectionType.DATABASE_OLEDB: { try { var conn = new OleDbConnection(connString); var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn); var builder = new OleDbCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); DataSet changes; lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; changes = dataSet.GetChanges(); adapter.Update(changes, tableName); dataSet.AcceptChanges(); } PrintDatasetErrors(changes); conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table", ex); } break; } } }
/// <summary> ///Updates the Database. ///</summary> public void UpdateAll() { m_daCao = new OdbcDataAdapter(); //Update //OdbcCommand myUpdateCommand = new OdbcCommand("UPDATE adressen SET fldStudentNr = ?, fldName = ?, fldVorname = ?, fldKlassenNr = ?, fldKlassenBez = ?, fldNotenNr = ?, fldNote = ?, fldFachNr = ?, fldBezeichnung = ? WHERE fldStudentNr = ?", m_cnCao); #region string build for update commant StringBuilder sbUpdate = new StringBuilder(); sbUpdate.Append("UPDATE adressen SET REC_ID = ?, "); #region not in use //sbUpdate.Append("MATCHCODE = ?, "); //sbUpdate.Append("KUNDENGRUPPE = ?, "); //sbUpdate.Append("SPRACH_ID = ?, "); //sbUpdate.Append("GESCHLECHT = ?, "); //sbUpdate.Append("KUNNUM1 = ?, "); //sbUpdate.Append("KUNNUM2 = ?, "); #endregion sbUpdate.Append("NAME1 = ?, "); sbUpdate.Append("PLZ = ?, "); sbUpdate.Append("ORT = ?, "); #region not in use //sbUpdate.Append("LAND = ?, "); //sbUpdate.Append("NAME2 = ?, "); //sbUpdate.Append("NAME3 = ?, "); //sbUpdate.Append("ABTEILUNG = ?, "); #endregion sbUpdate.Append("ANREDE = ?, "); sbUpdate.Append("STRASSE = ?, "); #region not in use //sbUpdate.Append("POSTFACH = ?, "); //sbUpdate.Append("PF_PLZ = ?, "); //sbUpdate.Append("DEFAULT_LIEFANSCHRIFT_ID = ?, "); //sbUpdate.Append("GRUPPE = ?, "); #endregion sbUpdate.Append("TELE1 = ?, "); sbUpdate.Append("TELE2 = ?, "); sbUpdate.Append("FAX = ?, "); sbUpdate.Append("FUNK = ?, "); sbUpdate.Append("EMAIL = ?, "); //sbUpdate.Append("EMAIL2 = ?, "); sbUpdate.Append("INTERNET = ?, "); //sbUpdate.Append("DIVERSES = ?, "); sbUpdate.Append("BRIEFANREDE = ?, "); #region not in use //sbUpdate.Append("BLZ = ?, "); //sbUpdate.Append("KTO = ?, "); //sbUpdate.Append("BANK = ?, "); //sbUpdate.Append("IBAN = ?, "); //sbUpdate.Append("SWIFT = ?, "); //sbUpdate.Append("KTO_INHABER = ?, "); //sbUpdate.Append("DEB_NUM = ?, "); //sbUpdate.Append("KRD_NUM = ?, "); //sbUpdate.Append("STATUS = ?, "); //sbUpdate.Append("NET_SKONTO = ?, "); //sbUpdate.Append("NET_TAGE = ?, "); #endregion sbUpdate.Append("BRT_TAGE = ?, "); #region not in use //sbUpdate.Append("WAEHRUNG = ?, "); //sbUpdate.Append("UST_NUM = ?, "); //sbUpdate.Append("VERTRETER_ID = ?, "); //sbUpdate.Append("PROVIS_PROZ = ?, "); #endregion sbUpdate.Append("INFO = ?, "); //sbUpdate.Append("GRABATT = ?, "); //sbUpdate.Append("KUN_KRDLIMIT = ?, "); sbUpdate.Append("KUN_LIEFART = ?, "); sbUpdate.Append("KUN_ZAHLART = ?, "); #region not in use //sbUpdate.Append("KUN_PRLISTE = ?, "); //sbUpdate.Append("KUN_LIEFSPERRE = ?, "); //sbUpdate.Append("LIEF_LIEFART = ?, "); //sbUpdate.Append("LIEF_ZAHLART = ?, "); //sbUpdate.Append("LIEF_PRLISTE = ?, "); //sbUpdate.Append("LIEF_TKOSTEN = ?, "); //sbUpdate.Append("LIEF_MBWERT = ?, "); //sbUpdate.Append("PR_EBENE = ?, "); //sbUpdate.Append("BRUTTO_FLAG = ?, "); //sbUpdate.Append("MWST_FREI_FLAG = ?, "); //sbUpdate.Append("KUNPREIS_AUTO = ?, "); #endregion sbUpdate.Append("KUN_SEIT = ?, "); sbUpdate.Append("KUN_GEBDATUM = ?"); #region not in use //sbUpdate.Append("ENTFERNUNG = ?, "); //sbUpdate.Append("ERSTELLT = ?, "); //sbUpdate.Append("ERST_NAME = ?, "); //sbUpdate.Append("GEAEND = ?, "); //sbUpdate.Append("GEAEND_NAME = ?, "); //sbUpdate.Append("SHOP_KUNDE = ?, "); //sbUpdate.Append("SHOP_ID = ?, "); //sbUpdate.Append("SHOP_NEWSLETTER = ?, "); //sbUpdate.Append("SHOP_KUNDE_ID = ?, "); //sbUpdate.Append("SHOP_CHANGE_FLAG = ?, "); //sbUpdate.Append("SHOP_DEL_FLAG = ?, "); //sbUpdate.Append("SHOP_PASSWORD = ?, "); //sbUpdate.Append("USERFELD_01 = ?, "); //sbUpdate.Append("USERFELD_02 = ?, "); //sbUpdate.Append("USERFELD_03 = ?, "); //sbUpdate.Append("USERFELD_04 = ?, "); //sbUpdate.Append("USERFELD_05 = ?, "); //sbUpdate.Append("USERFELD_06 = ?, "); //sbUpdate.Append("USERFELD_07 = ?, "); //sbUpdate.Append("USERFELD_08 = ?, "); //sbUpdate.Append("USERFELD_09 = ?, "); //sbUpdate.Append("USERFELD_10 = ?"); #endregion sbUpdate.Append(" WHERE REC_ID = ?"); #endregion OdbcCommand myUpdateCommand = new OdbcCommand(sbUpdate.ToString(), m_cnCao); #region parameters myUpdateCommand.Parameters.Add("@REC_ID", OdbcType.Int, 11, "@REC_ID"); #region not in use //myUpdateCommand.Parameters.Add("@MATCHCODE", OdbcType.VarChar, 255, "@MATCHCODE"); //myUpdateCommand.Parameters.Add("@KUNDENGRUPPE", OdbcType.Int, 11, "@KUNDENGRUPPE"); //myUpdateCommand.Parameters.Add("@SPRACH_ID", OdbcType.Int, 11, "@SPRACH_ID"); //myUpdateCommand.Parameters.Add("@GESCHLECHT", OdbcType.Char, 1, "@GESCHLECHT"); //myUpdateCommand.Parameters.Add("@KUNNUM1", OdbcType.VarChar, 20, "@KUNNUM1"); //myUpdateCommand.Parameters.Add("@KUNNUM2", OdbcType.VarChar, 20, "@KUNNUM2"); #endregion myUpdateCommand.Parameters.Add("@NAME1", OdbcType.VarChar, 40, "@NAME1"); myUpdateCommand.Parameters.Add("@PLZ", OdbcType.VarChar, 10, "@PLZ"); myUpdateCommand.Parameters.Add("@ORT", OdbcType.VarChar, 40, "@ORT"); #region not in use //myUpdateCommand.Parameters.Add("@LAND", OdbcType.VarChar, 5, "@LAND"); //myUpdateCommand.Parameters.Add("@NAME2", OdbcType.VarChar, 40, "@NAME2"); //myUpdateCommand.Parameters.Add("@NAME3", OdbcType.VarChar, 40, "@NAME3"); //myUpdateCommand.Parameters.Add("@ABTEILUNG", OdbcType.VarChar, 40, "@ABTEILUNG"); #endregion myUpdateCommand.Parameters.Add("@ANREDE", OdbcType.VarChar, 40, "@ANREDE"); myUpdateCommand.Parameters.Add("@STRASSE", OdbcType.VarChar, 40, "@STRASSE"); #region not in use //myUpdateCommand.Parameters.Add("@POSTFACH", OdbcType.VarChar, 40, "@POSTFACH"); //myUpdateCommand.Parameters.Add("@PF_PLZ", OdbcType.VarChar, 10, "@PF_PLZ"); //myUpdateCommand.Parameters.Add("@DEFAULT_LIEFANSCHRIFT_ID", OdbcType.Int, 11, "@DEFAULT_LIEFANSCHRIFT_ID"); //myUpdateCommand.Parameters.Add("@GRUPPE", OdbcType.VarChar, 4, "@GRUPPE"); #endregion myUpdateCommand.Parameters.Add("@TELE1", OdbcType.VarChar, 100, "@TELE1"); myUpdateCommand.Parameters.Add("@TELE2", OdbcType.VarChar, 100, "@TELE2"); myUpdateCommand.Parameters.Add("@FAX", OdbcType.VarChar, 100, "@FAX"); myUpdateCommand.Parameters.Add("@FUNK", OdbcType.VarChar, 100, "@FUNK"); myUpdateCommand.Parameters.Add("@EMAIL", OdbcType.VarChar, 100, "@EMAIL"); //myUpdateCommand.Parameters.Add("@EMAIL2", OdbcType.VarChar, 100, "@EMAIL2"); myUpdateCommand.Parameters.Add("@INTERNET", OdbcType.VarChar, 100, "@INTERNET"); //myUpdateCommand.Parameters.Add("@DIVERSES", OdbcType.VarChar, 100, "@DIVERSES"); myUpdateCommand.Parameters.Add("@BRIEFANREDE", OdbcType.VarChar, 100, "@BRIEFANREDE"); #region not in use //myUpdateCommand.Parameters.Add("@BLZ", OdbcType.VarChar, 20, "@BLZ"); //myUpdateCommand.Parameters.Add("@KTO", OdbcType.VarChar, 20, "@KTO"); //myUpdateCommand.Parameters.Add("@BANK", OdbcType.VarChar, 40, "@BANK"); //myUpdateCommand.Parameters.Add("@IBAN", OdbcType.VarChar, 100, "@IBAN"); //myUpdateCommand.Parameters.Add("@SWIFT", OdbcType.VarChar, 100, "@SWIFT"); //myUpdateCommand.Parameters.Add("@KTO_INHABER", OdbcType.VarChar, 40, "@KTO_INHABER"); //myUpdateCommand.Parameters.Add("@DEB_NUM", OdbcType.Int, 11, "@DEB_NUM"); //myUpdateCommand.Parameters.Add("@KRD_NUM", OdbcType.Int, 11, "@KRD_NUM"); //myUpdateCommand.Parameters.Add("@STATUS", OdbcType.Int, 11, "@STATUS"); ////myUpdateCommand.Parameters.Add("@NET_SKONTO", OdbcType.Decimal, 5, "@NET_SKONTO"); //OdbcParameter myParam1 = new OdbcParameter(); //myParam1.ParameterName = "@NET_SKONTO"; //myParam1.SourceColumn = "@NET_SKONTO"; //myParam1.DbType = DbType.Decimal; //myParam1.Precision = 5; //myParam1.Scale = 2; //myUpdateCommand.Parameters.Add(myParam1); //myUpdateCommand.Parameters.Add("@NET_TAGE", OdbcType.TinyInt, 4, "@NET_TAGE"); #endregion myUpdateCommand.Parameters.Add("@BRT_TAGE", OdbcType.TinyInt, 4, "@BRT_TAGE"); #region not in use //myUpdateCommand.Parameters.Add("@WAEHRUNG", OdbcType.VarChar, 5, "@WAEHRUNG"); //myUpdateCommand.Parameters.Add("@UST_NUM", OdbcType.VarChar, 25, "@UST_NUM"); //myUpdateCommand.Parameters.Add("@VERTRETER_ID", OdbcType.Int, 11, "@VERTRETER_ID"); ////myUpdateCommand.Parameters.Add("@PROVIS_PROZ", OdbcType.Decimal, 5,, "@PROVIS_PROZ"); //OdbcParameter myParam2 = new OdbcParameter(); //myParam2.ParameterName = "@PROVIS_PROZ"; //myParam2.SourceColumn = "@PROVIS_PROZ"; //myParam2.DbType = DbType.Decimal; //myParam2.Precision = 5; //myParam2.Scale = 2; //myUpdateCommand.Parameters.Add(myParam2); #endregion myUpdateCommand.Parameters.Add("@INFO", OdbcType.Text, 65537, "@INFO"); #region not in use ////myUpdateCommand.Parameters.Add("@GRABATT", OdbcType.Decimal, 5, "@GRABATT"); //OdbcParameter myParam3 = new OdbcParameter(); //myParam3.ParameterName = "@GRABATT"; //myParam3.SourceColumn = "@GRABATT"; //myParam3.DbType = DbType.Decimal; //myParam3.Precision = 5; //myParam3.Scale = 2; //myUpdateCommand.Parameters.Add(myParam3); ////myUpdateCommand.Parameters.Add("@KUN_KRDLIMIT", OdbcType.Decimal, 10, "@KUN_KRDLIMIT"); //OdbcParameter myParam4 = new OdbcParameter(); //myParam4.ParameterName = "@KUN_KRDLIMIT"; //myParam4.SourceColumn = "@KUN_KRDLIMIT"; //myParam4.DbType = DbType.Decimal; //myParam4.Precision = 10; //myParam4.Scale = 2; //myUpdateCommand.Parameters.Add(myParam4); #endregion myUpdateCommand.Parameters.Add("@KUN_LIEFART", OdbcType.Int, 11, "@KUN_LIEFART"); myUpdateCommand.Parameters.Add("@KUN_ZAHLART", OdbcType.Int, 11, "@KUN_ZAHLART"); #region not in use ////myUpdateCommand.Parameters.Add("@KUN_PRLISTE", OdbcType.enum('N','Y'), "@KUN_PRLISTE"); ////myUpdateCommand.Parameters.Add("@KUN_LIEFSPERRE", OdbcType.enum('N','Y'), , "@KUN_LIEFFSPERRE"); //myUpdateCommand.Parameters.Add("@LIEF_LIEFART", OdbcType.Int, 11, "@LIEF_LIEFART"); //myUpdateCommand.Parameters.Add("@LIEF_ZAHLART", OdbcType.Int, 11, "@LIEF_ZAHLART"); ////myUpdateCommand.Parameters.Add("@LIEF_PRLISTE", OdbcType.enum('N','Y'), , "@LIEF_PRLISTE"); #endregion #region not in use ////myUpdateCommand.Parameters.Add("@LIEF_TKOSTEN", OdbcType.Decimal, 10, "@LIEF_TKOSTEN"); //OdbcParameter myParam5 = new OdbcParameter(); //myParam5.ParameterName = "@LIEF_TKOSTEN"; //myParam5.SourceColumn = "@LIEF_TKOSTEN"; //myParam5.DbType = DbType.Decimal; //myParam5.Precision = 10; //myParam5.Scale = 2; //myUpdateCommand.Parameters.Add(myParam5); ////myUpdateCommand.Parameters.Add("@LIEF_MBWERT", OdbcType.Decimal, 10, "@LIEF_MBWERT"); //OdbcParameter myParam6 = new OdbcParameter(); //myParam6.ParameterName = "@LIEF_MBWERT"; //myParam6.SourceColumn = "@LIEF_MBWERT"; //myParam6.DbType = DbType.Decimal; //myParam6.Precision = 10; //myParam6.Scale = 2; //myUpdateCommand.Parameters.Add(myParam6); //myUpdateCommand.Parameters.Add("@PR_EBENE", OdbcType.TinyInt, 1, "@PR_EBENE"); ////myUpdateCommand.Parameters.Add("@BRUTTO_FLAG", OdbcType.enum('N','Y'), , "@BRUTTO_FLAG"); ////myUpdateCommand.Parameters.Add("@MWST_FREI_FLAG", OdbcType.enum('N','Y') , , "@MWST_FREI_FLAG"); ////myUpdateCommand.Parameters.Add("@KUNPREIS_AUTO", OdbcType.enum('N','Y') , , "@KUNPREIS_AUTO"); #endregion //myUpdateCommand.Parameters.Add("@KUN_SEIT", OdbcType.Date, , "@KUN_SEIT"); OdbcParameter myParam7 = new OdbcParameter(); myParam7.ParameterName = "@KUN_SEIT"; myParam7.SourceColumn = "@KUN_SEIT"; myParam7.DbType = DbType.Date; myUpdateCommand.Parameters.Add(myParam7); //myUpdateCommand.Parameters.Add("@KUN_GEBDATUM", OdbcType.Date, , "@KUN_GEBDATUM"); OdbcParameter myParam8 = new OdbcParameter(); myParam8.ParameterName = "@KUN_GEBDATUM"; myParam8.SourceColumn = "@KUN_GEBDATUM"; myParam8.DbType = DbType.Date; myUpdateCommand.Parameters.Add(myParam8); #region not in use ////myUpdateCommand.Parameters.Add("@ERSTELLT", OdbcType.Date, , "@ERSTELLT"); //OdbcParameter myParam9 = new OdbcParameter(); //myParam9.ParameterName = "@ERSTELLT"; //myParam9.SourceColumn = "@ERSTELLT"; //myParam9.DbType = DbType.Date; //myUpdateCommand.Parameters.Add(myParam9); //myUpdateCommand.Parameters.Add("@ERST_NAME", OdbcType.VarChar, 20, "@ERST_NAME"); ////myUpdateCommand.Parameters.Add("@GEAEND", OdbcType.Date, , "@GEAND"); ////myUpdateCommand.Parameters.Add("@GEAEND_NAME", OdbcType.VarChar, 20, "@GEAEND_NAME"); ////myUpdateCommand.Parameters.Add("@SHOP_KUNDE", enum('N','Y'), , "@SHOP_KUNDE"); //myUpdateCommand.Parameters.Add("@SHOP_ID", OdbcType.TinyInt, 3, "@SHOP_ID"); ////myUpdateCommand.Parameters.Add("@SHOP_NEWSLETTER", enum('N','Y'), , "@SHOP_NEWSLETTER"); //myUpdateCommand.Parameters.Add("@SHOP_KUNDE_ID", OdbcType.Int, 11, "@SHOP_KUNDE_ID"); //myUpdateCommand.Parameters.Add("@SHOP_CHANGE_FLAG", OdbcType.TinyInt, 1, "@SHOP_CHANGE_FLAG"); ////myUpdateCommand.Parameters.Add("@SHOP_DEL_FLAG", enum('N','Y'), , "@SHOP_DEL_FLAG"); //myUpdateCommand.Parameters.Add("@SHOP_PASSWORD", OdbcType.VarChar, 20, "@SHOP_PASSWORD"); //myUpdateCommand.Parameters.Add("@USERFELD_01", OdbcType.VarChar, 255, "@USERFELD_01"); //myUpdateCommand.Parameters.Add("@USERFELD_02", OdbcType.VarChar, 255, "@USERFELD_02"); //myUpdateCommand.Parameters.Add("@USERFELD_03", OdbcType.VarChar, 255, "@USERFELD_03"); //myUpdateCommand.Parameters.Add("@USERFELD_04", OdbcType.VarChar, 255, "@USERFELD_04"); //myUpdateCommand.Parameters.Add("@USERFELD_05", OdbcType.VarChar, 255, "@USERFELD_05"); //myUpdateCommand.Parameters.Add("@USERFELD_06", OdbcType.VarChar, 255, "@USERFELD_06"); //myUpdateCommand.Parameters.Add("@USERFELD_07", OdbcType.VarChar, 255, "@USERFELD_07"); //myUpdateCommand.Parameters.Add("@USERFELD_08", OdbcType.VarChar, 255, "@USERFELD_08"); //myUpdateCommand.Parameters.Add("@USERFELD_09", OdbcType.VarChar, 255, "@USERFELD_09"); //myUpdateCommand.Parameters.Add("@USERFELD_10", OdbcType.VarChar, 255, "@USERFELD_10"); #endregion #endregion m_daCao.UpdateCommand = myUpdateCommand; #region example /* myUpDateCommand.Parameters.Add("@fldStudentNr", OleDbType.Integer, 2, "fldStudentNr"); myUpdateCommand.Parameters.Add("@fldName", OleDbType.VarChar, 255, "fldName"); myUpdateCommand.Parameters.Add("@fldVorname", OleDbType.VarChar, 255, "fldVorname"); myUpdateCommand.Parameters.Add("@fldKlassenNr", OleDbType.Integer, 2, "fldKlassenNr"); myUpdateCommand.Parameters.Add("@fldKlassenBez", OleDbType.VarChar, 255, "fldKlassenBez"); myUpdateCommand.Parameters.Add("@fldNotenNr", OleDbType.Integer, 2, "fldNotenNr"); myUpdateCommand.Parameters.Add("@fldNote", OleDbType.Single, 2, "fldNote"); myUpdateCommand.Parameters.Add("@fldFachNr", OleDbType.Integer, 2, "fldFachNr"); myUpdateCommand.Parameters.Add("@fldBezeichnung", OleDbType.VarChar, 255, "fldBezeichnung"); m_daCao.UpdateCommand = myUpdateCommand; */ #endregion //delete //OdbcCommand myDeleteCommand = new OdbcCommand("delete from ADRESSEN_LIEF Where ADDR_ID=3;delete from ADRESSEN_TO_MERK Where ADDR_ID=3;DELETE FROM ADRESSEN WHERE REC_ID=3.DeleteCommand = myDeleteCommand;", myConnection); //myDeleteCommand.Parameters.Add("@fldStudentNr", OleDbType.Integer, 2, "fldStudentNr"); m_daCao.Update(m_dsCao, m_const_strCaoTableSearchAllPrivate); }
protected void ImageButton1_Click(object sender, ImageClickEventArgs e) { //search try { string connection = ConfigurationSettings.AppSettings["ConnectionString"]; OdbcConnection dbCon = new OdbcConnection(connection); dbCon.Open(); string sql = "select count(*) from Board"; OdbcCommand cmd = new OdbcCommand(sql, dbCon); total = int.Parse(cmd.ExecuteScalar().ToString()); //search func if (chkName.Checked == true) { //sql = "select name from bygem_board_"+boardname+ " Like '%" +txtSearch.Text + "%'"; sql = " name like '%" + txtSearch.Text + "%'"; } if (chkSubject.Checked == true) { sql = " subject like '%" + txtSearch.Text + "%'"; } if (chkMemo.Checked == true) { sql = " memo like '%" + txtSearch.Text + "%'"; } //cmd.CommandText = sql+ " order by headnum DESC,depth"; cmd.CommandText = "select * from Board where" + sql + "order by seq DESC,depth"; //"select * from bygem_board_free where name like '%ca%' order by headnum DESC,depth"; //"select * from bygem_board_"+boardname+" where" +sql+ "order by headnum DESC,depth"; OdbcDataAdapter dbAdap = new OdbcDataAdapter(cmd); DataSet ds = new DataSet(); dbAdap.Fill(ds); dbAdap.Update(ds); GridView1.DataSource = ds; GridView1.DataBind(); dbCon.Close(); } catch { string script; script = "<script>alert('search failed.')</script>"; this.RegisterStartupScript("error", script); } }
public void SaveDataSetThroughAdapter(System.Data.DataSet dsSetRef, Boolean blnRequiredTransaction, String ExcludeTableName, String strConName) { Boolean blnBeginTrans = false; OleDbDataAdapter objOleDBAdpater; OdbcDataAdapter objOdbcDBAdpater; SqlDataAdapter objSqlDBAdpater; OdbcCommandBuilder objOdbcDBCommandBuilder; OleDbCommandBuilder objOleDBCommandBuilder; SqlCommandBuilder objSqlDBCommandBuilder; IDbCommand IMainCommand; DataTable dtInsert; DataTable dtUpdate; DataTable dtDelete; Boolean TableExist; String strQuery; try { if (dsSetRef == null) { throw new Exception("DataSet not Initialized"); } String[] TableName; char[] delimeter; String seperator; seperator = ","; delimeter = seperator.ToCharArray(); TableName = ExcludeTableName.Split(delimeter); if (blnRequiredTransaction.IsFalse()) { if (strConName.Length > 0) { OpenConnection(strConName); } else { OpenConnection(String.Empty); } } if (disconnection.IsNotNull()) { if (blnRequiredTransaction.IsFalse()) { transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted); blnBeginTrans = true; } else { if (transaction == null) { throw new Exception("Transaction is not initialized"); } else { blnBeginTrans = true; } } if (ProviderType == Util.ConnectionLibrary.SQlClient) { IMainCommand = new SqlCommand(); } else if (ProviderType == Util.ConnectionLibrary.Oledb) { IMainCommand = new OleDbCommand(); } else if (ProviderType == Util.ConnectionLibrary.ODBC) { IMainCommand = new OdbcCommand(); } else { IMainCommand = null; } IMainCommand.Connection = disconnection; IMainCommand.Transaction = transaction; } else { throw new Exception("Connection is not initialized"); } IMainCommand.CommandTimeout = CommandTimeOutValue; foreach (DataTable dtRef in dsSetRef.Tables) { TableExist = false; foreach (String tablename in TableName) { if (dtRef.TableName.ToUpper() == tablename.ToUpper()) { TableExist = true; break; } } if (TableExist) continue; if ((Boolean)dtRef.ExtendedProperties[JoinedQuery]) { strQuery = dtRef.ExtendedProperties[UpdateQuery].ToString(); } else { strQuery = dtRef.ExtendedProperties[Query].ToString(); } if ((strQuery.Trim()).Length == 0) { throw new Exception("Query is blank"); } IMainCommand.CommandText = strQuery; dtInsert = dtRef.GetChanges(DataRowState.Added); dtUpdate = dtRef.GetChanges(DataRowState.Modified); dtDelete = dtRef.GetChanges(DataRowState.Deleted); if (ProviderType == Util.ConnectionLibrary.SQlClient) { objSqlDBAdpater = new SqlDataAdapter((SqlCommand)IMainCommand); objSqlDBCommandBuilder = new SqlCommandBuilder(objSqlDBAdpater); if (dtDelete.IsNotNull()) { objSqlDBCommandBuilder.GetDeleteCommand(); objSqlDBAdpater.Update(dtDelete); dtDelete.Dispose(); dtDelete = null; } if (dtInsert.IsNotNull()) { objSqlDBCommandBuilder.GetInsertCommand(); objSqlDBAdpater.Update(dtInsert); dtInsert.Dispose(); dtInsert = null; } if (dtUpdate.IsNotNull()) { objSqlDBCommandBuilder.GetUpdateCommand(); objSqlDBAdpater.Update(dtUpdate); dtUpdate.Dispose(); dtUpdate = null; } } else if (ProviderType == Util.ConnectionLibrary.Oledb) { objOleDBAdpater = new OleDbDataAdapter((OleDbCommand)IMainCommand); objOleDBCommandBuilder = new OleDbCommandBuilder(objOleDBAdpater); if (dtInsert.IsNotNull()) { objOleDBCommandBuilder.GetInsertCommand(); objOleDBAdpater.Update(dtInsert); dtInsert.Dispose(); dtInsert = null; } if (dtUpdate.IsNotNull()) { objOleDBCommandBuilder.GetUpdateCommand(); objOleDBAdpater.Update(dtUpdate); dtUpdate.Dispose(); dtUpdate = null; } if (dtDelete.IsNotNull()) { objOleDBCommandBuilder.GetDeleteCommand(); objOleDBAdpater.Update(dtDelete); dtDelete.Dispose(); dtDelete = null; } } else if (ProviderType == Util.ConnectionLibrary.ODBC) { objOdbcDBAdpater = new OdbcDataAdapter((OdbcCommand)IMainCommand); objOdbcDBCommandBuilder = new OdbcCommandBuilder(objOdbcDBAdpater); if (dtInsert.IsNotNull()) { objOdbcDBCommandBuilder.GetInsertCommand(); objOdbcDBAdpater.Update(dtInsert); dtInsert.Dispose(); dtInsert = null; } if (dtUpdate.IsNotNull()) { objOdbcDBCommandBuilder.GetUpdateCommand(); objOdbcDBAdpater.Update(dtUpdate); dtUpdate.Dispose(); dtUpdate = null; } if (dtDelete.IsNotNull()) { objOdbcDBCommandBuilder.GetDeleteCommand(); objOdbcDBAdpater.Update(dtDelete); dtDelete.Dispose(); dtDelete = null; } } else { objSqlDBAdpater = null; objOleDBAdpater = null; objOdbcDBAdpater = null; objSqlDBCommandBuilder = null; objOleDBCommandBuilder = null; objOdbcDBCommandBuilder = null; } } if (blnRequiredTransaction.IsFalse()) { if (blnBeginTrans) { transaction.Commit(); blnBeginTrans = false; } disconnection.Close(); disconnection.Dispose(); disconnection = null; } } catch (System.Data.OleDb.OleDbException exOleDb) { if (blnBeginTrans && blnRequiredTransaction.IsFalse()) { transaction.Rollback(); if (disconnection.IsNotNull()) { if (disconnection.State == System.Data.ConnectionState.Open) { disconnection.Close(); } disconnection.Dispose(); disconnection = null; } } throw (exOleDb); } catch (System.Data.DBConcurrencyException exDBCE) { if (blnBeginTrans && blnRequiredTransaction.IsFalse()) { transaction.Rollback(); if (disconnection.IsNotNull()) { if (disconnection.State == System.Data.ConnectionState.Open) { disconnection.Close(); } disconnection.Dispose(); disconnection = null; } } throw (exDBCE); } catch (System.Exception ex) { if (blnBeginTrans && blnRequiredTransaction.IsFalse()) { transaction.Rollback(); if (disconnection.IsNotNull()) { if (disconnection.State == System.Data.ConnectionState.Open) { disconnection.Close(); } disconnection.Dispose(); disconnection = null; } } throw (ex); } finally { if (ProviderType == Util.ConnectionLibrary.SQlClient) { IMainCommand = null; objSqlDBAdpater = null; objSqlDBCommandBuilder = null; } else if (ProviderType == Util.ConnectionLibrary.Oledb) { IMainCommand = null; objOleDBAdpater = null; objOleDBCommandBuilder = null; } else if (ProviderType == Util.ConnectionLibrary.ODBC) { IMainCommand = null; objOdbcDBAdpater = null; objOdbcDBCommandBuilder = null; } } }
/// <summary> /// 插入数据通过Datatable /// </summary> /// <param name="_dt"></param> /// <returns>影响记录条数</returns> public override int DataTableInsert(DataTable _dt) { bool flag = false; int _nResult = 0; if (_dt == null) return _nResult; string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName); OdbcCommand _Command = (OdbcCommand)CreateCommand(_sCmdText, CommandType.Text); OdbcDataAdapter _adapter = new OdbcDataAdapter(_Command); OdbcDataAdapter _adapter1 = new OdbcDataAdapter(_Command); OdbcCommandBuilder _builder = new OdbcCommandBuilder(_adapter1); DataTable dt = new DataTable(_dt.TableName); _adapter.Fill(dt); System.IO.MemoryStream ms = new System.IO.MemoryStream(); _dt.WriteXml(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); dt.ReadXml(ms); ms.Dispose(); _adapter.InsertCommand = _builder.GetInsertCommand(); if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count) { flag = true;//因为表中有自增字段,所以CommandBuild生成的InserttCommand的参数中少了自增字段 foreach (DataColumn _dc in _dt.Columns) { if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName)) { _adapter.InsertCommand.CommandText = _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName); _adapter.InsertCommand.CommandText = _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",?"); _adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, OdbcType.Decimal, _dc.MaxLength, _dc.ColumnName); if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count) break; } } } if (flag) this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName)); this.BeginTransaction(); try { _Command.CommandText = "delete from " + _dt.TableName; _Command.ExecuteNonQuery(); _adapter.InsertCommand.Transaction = _Command.Transaction; _nResult = _adapter.Update(dt); this.CommitTransaction(); } catch (Exception ex) { this.RollbackTransaction(); throw ex; } finally { if (flag) this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName)); dt.Dispose(); } return _nResult; }
private void BultInsertOdbc(int pageSize, string sqltest, DataTable dataTable) { // Connect to the AdventureWorks database. using (OdbcConnection connectiontest = new OdbcConnection(sqltest)) { // Create a SqlDataAdapter. OdbcDataAdapter adaptertest = new OdbcDataAdapter(); // Set the INSERT command and parameter. adaptertest.InsertCommand = new OdbcCommand( "INSERT INTO ATWO.BGDTBIK " + "(BIK_ENTIDAD,BIK_CENTRO_ALTA,BIK_CUENTA,BIK_LAST_NUM,BIK_LAST_DATE,BIK_STATUS,BIK_BANK_MOD,BIK_CENTER_MOD,BIK_USERID_MOD,BIK_NETNAME_MOD,BIK_TIMEST_MOD) " + "VALUES (" + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" + ")", connectiontest); adaptertest.InsertCommand.Parameters.Add("@BIK_ENTIDAD", OdbcType.NChar); adaptertest.InsertCommand.Parameters.Add("@BIK_CENTRO_ALTA", OdbcType.NChar); adaptertest.InsertCommand.Parameters.Add("@BIK_CUENTA", OdbcType.NChar); adaptertest.InsertCommand.Parameters.Add("@BIK_LAST_NUM", OdbcType.Numeric); adaptertest.InsertCommand.Parameters.Add("@BIK_LAST_DATE", OdbcType.Date); adaptertest.InsertCommand.Parameters.Add("@BIK_STATUS", OdbcType.NChar); adaptertest.InsertCommand.Parameters.Add("@BIK_BANK_MOD", OdbcType.NChar); adaptertest.InsertCommand.Parameters.Add("@BIK_CENTER_MOD", OdbcType.NChar); adaptertest.InsertCommand.Parameters.Add("@BIK_USERID_MOD", OdbcType.NChar); adaptertest.InsertCommand.Parameters.Add("@BIK_NETNAME_MOD", OdbcType.NChar); adaptertest.InsertCommand.Parameters.Add("@BIK_TIMEST_MOD", OdbcType.DateTime); adaptertest.InsertCommand.UpdatedRowSource = UpdateRowSource.None; // Set the batch size. adaptertest.UpdateBatchSize = pageSize; // Execute the update. int rows = adaptertest.Update(dataTable); } }