C# OdbcDataAdapter.Update方法代码示例

        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)
//.........这里部分代码省略.........

        /// <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 = ?");

//.........这里部分代码省略.........

        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())
//.........这里部分代码省略.........

        /// <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);
            }
        }