C# OracleDataAdapter.Update方法代码示例

        public void AddNewBroker(Broker brokerToAdd)
        {
            // disconnected version
            string sqlStatement = "INSERT INTO brokers(broker_id, first_name, last_name) VALUES (:broker_id, :first_name, :last_name)";
            IDbConnection connection = new OracleConnection(connectionString);
            OracleCommand command = new OracleCommand(sqlStatement, (OracleConnection)connection);
            command.BindByName = true;
            IDbDataParameter param = new OracleParameter(":first_name", OracleDbType.Varchar2, 25);
            param.Value = brokerToAdd.firstName;
            command.Parameters.Add(param);
            param = new OracleParameter(":last_name", OracleDbType.Varchar2, 25);
            param.Value = brokerToAdd.lastName;
            command.Parameters.Add(param);
            param = new OracleParameter(":broker_id", OracleDbType.Int16, 50);
            param.Value = brokerToAdd.id;
            command.Parameters.Add(param);

            IDbDataAdapter da = new OracleDataAdapter();
            da.InsertCommand = command;

            DataRow newRow = dataSet.Tables[0].NewRow();
            newRow["broker_id"] = brokerToAdd.id;
            newRow["first_name"] = brokerToAdd.firstName;
            newRow["last_name"] = brokerToAdd.lastName;
            dataSet.Tables[0].Rows.Add(newRow);

            // it's not clear from this code but we could make many changes to
            // the dataset before submitting back to the database.
            connection.Open();
            da.Update(dataSet);
            connection.Close();
        }

        void MedStoreLoad()
        {
            try
            {
                conn.Open();
                string sqlquery = "SELECT * FROM MED_STORE";
                //string sqlquery = "SELECT * FROM MED_INFO";
                OracleCommand cmd = new OracleCommand(sqlquery, conn);
                OracleDataAdapter oda = new OracleDataAdapter();
                oda.SelectCommand = cmd;
                dt = new DataTable();
                //     dt.Columns["MED_NAME"].ColumnName = "Name";

                oda.Fill(dt);
                BindingSource bsource = new BindingSource();
                bsource.DataSource = dt;
                dataGridView1.DataSource = bsource;
                oda.Update(dt);
                dt.Columns[0].ColumnName = "ID";
                dt.Columns[1].ColumnName = "Name";
                dt.Columns[2].ColumnName = "Quantity";
                dt.Columns[3].ColumnName = "Damage Qty";
                dt.Columns[4].ColumnName = "Reorder Level";

                dt.AcceptChanges();

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        void MgfLoad()
        {
            try
            {
                conn.Open();
              //  string sqlquery = "SELECT MED_ID,MED_NAME,MED_STG,MED_MGF,MED_BATCH,MED_GROUP,MED_TYPE,COST_PRICE,SELL_PRICE,NOTES FROM MED_INFO";
                string sqlquery = "SELECT * FROM MFG_INFO";
                OracleCommand cmd = new OracleCommand(sqlquery, conn);
                OracleDataAdapter oda = new OracleDataAdapter();
                oda.SelectCommand = cmd;
                dt = new DataTable();
                //     dt.Columns["MED_NAME"].ColumnName = "Name";

                oda.Fill(dt);
                BindingSource bsource = new BindingSource();
                bsource.DataSource = dt;
                dataGridView1.DataSource = bsource;
                oda.Update(dt);
                dt.Columns[0].ColumnName = "ID";
                dt.Columns[1].ColumnName = "Name";
                dt.Columns[2].ColumnName = "Address";
                dt.Columns[3].ColumnName = "Mobile";
                dt.AcceptChanges();

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        void stuffload()
        {
            try
            {
                conn.Open();
                string sqlquery = "SELECT EMP_ID,EMP_NAME,MOBILE,JOIN_DATE,POSITION,SALARY FROM EMPLOYE_INFO";
                //string sqlquery = "SELECT * FROM MED_INFO";
                OracleCommand cmd = new OracleCommand(sqlquery, conn);
                OracleDataAdapter oda = new OracleDataAdapter();
                oda.SelectCommand = cmd;
                dt = new DataTable();
                //     dt.Columns["MED_NAME"].ColumnName = "Name";

                oda.Fill(dt);
                BindingSource bsource = new BindingSource();
                bsource.DataSource = dt;
                dataGridView1.DataSource = bsource;
                oda.Update(dt);
                dt.Columns[0].ColumnName = "ID";
                dt.Columns[1].ColumnName = "Name";
                dt.Columns[2].ColumnName = "Mobile";
                dt.Columns[3].ColumnName = "Joining Date";
                dt.Columns[4].ColumnName = "Position";
                dt.Columns[4].ColumnName = "Salary";

                dt.AcceptChanges();

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void MetroWindow_Loaded(object sender, RoutedEventArgs e)
        {

           
            OracleConnection Oracon = new OracleConnection(oradb);
            try
            {
                //abrir conexao com o banco de dados
                Oracon.Open();
                
                OracleCommand createCommand = new OracleCommand(SQL_SELECT_ALL, Oracon);
                createCommand.ExecuteNonQuery();

                OracleDataAdapter adapter = new OracleDataAdapter(createCommand);

                DataTable dt = new DataTable("viatura");
                adapter.Fill(dt);
                dgvConteudo.ItemsSource = dt.DefaultView;
                dgvConteudo.Columns[0].Header = "ID";
                dgvConteudo.Columns[1].Header = "Fabricante Modelo";
                dgvConteudo.Columns[2].Header = "Placa";
                dgvConteudo.Columns[3].Header = "Chassi";
                dgvConteudo.Columns[4].Header = "Status";

                //Ocultar colunas
                dgvConteudo.Columns[4].Visibility = Visibility.Hidden;

                adapter.Update(dt);

                //Fechar conexao com o banco de dados
                Oracon.Close();

            }
            catch (Exception ex)
            {

                System.Windows.MessageBox.Show(ex.Message);
            }   
        }

        void MedLoad()
        {
            try
            {
                conn.Open();
                string sqlquery = "SELECT MED_ID,MED_NAME,MED_STG,MED_MGF,MED_BATCH,MED_GROUP,MED_TYPE,COST_PRICE,SELL_PRICE,NOTES FROM MED_INFO";
                //string sqlquery = "SELECT * FROM MED_INFO";
                OracleCommand cmd = new OracleCommand(sqlquery, conn);
                OracleDataAdapter oda = new OracleDataAdapter();
                oda.SelectCommand = cmd;
                dt = new DataTable();
               //     dt.Columns["MED_NAME"].ColumnName = "Name";

                oda.Fill(dt);
                BindingSource bsource = new BindingSource();
                bsource.DataSource = dt;
                dataGridView1.DataSource = bsource;
                oda.Update(dt);
                dt.Columns[0].ColumnName = "ID";
                dt.Columns[1].ColumnName = "Name";
                dt.Columns[2].ColumnName = "Strenght";
                dt.Columns[3].ColumnName = "Manufacturer";
                dt.Columns[4].ColumnName = "Batch No";
                dt.Columns[5].ColumnName = "Group";
                dt.Columns[6].ColumnName = "Type";
                dt.Columns[7].ColumnName = "Cost Price";
                dt.Columns[8].ColumnName = "Sell Price";
                dt.Columns[9].ColumnName = "Note";
                dt.AcceptChanges();

                conn.Close();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void MetroWindow_Loaded(object sender, RoutedEventArgs e)
        {
            OracleConnection Oracon = new OracleConnection(oradb);
            try
            {
                //abrir conexao com o banco de dados
                Oracon.Open();

                OracleCommand createCommand = new OracleCommand(SQL_SELECT_ALL, Oracon);
                createCommand.ExecuteNonQuery();

                OracleDataAdapter adapter = new OracleDataAdapter(createCommand);
                DataTable dt = new DataTable("agente");
                adapter.Fill(dt);
                dgvConteudo.ItemsSource = dt.DefaultView;
                dgvConteudo.Columns[0].Header = "ID";
                dgvConteudo.Columns[1].Header = "Nome Completo";
                dgvConteudo.Columns[2].Header = "Sexo";
                dgvConteudo.Columns[3].Header = "Data de Nascimento";
                dgvConteudo.Columns[4].Header = "RG";
                dgvConteudo.Columns[5].Header = "CPF";
                dgvConteudo.Columns[6].Header = "Tipo Sanguíneo";
                dgvConteudo.Columns[7].Header = "Raça";
                dgvConteudo.Columns[8].Header = "Estado Civil";
                dgvConteudo.Columns[9].Header = "CEP";
                dgvConteudo.Columns[10].Header = "Logradouro";
                dgvConteudo.Columns[11].Header = "Numero";
                dgvConteudo.Columns[12].Header = "Complemento";
                dgvConteudo.Columns[13].Header = "Bairro";
                dgvConteudo.Columns[14].Header = "Cidade";
                dgvConteudo.Columns[15].Header = "UF";
                dgvConteudo.Columns[16].Header = "Foto Agente";
                dgvConteudo.Columns[17].Header = "Impressão Digital";
                dgvConteudo.Columns[18].Header = "Cargo";
                dgvConteudo.Columns[19].Header = "STATUS";


                //Escondendo colunas
                dgvConteudo.Columns[16].Visibility = Visibility.Hidden;
                dgvConteudo.Columns[17].Visibility = Visibility.Hidden;
                dgvConteudo.Columns[19].Visibility = Visibility.Hidden;

                adapter.Update(dt);

                Oracon.Close();

            }
            catch (Exception ex)
            {

                System.Windows.MessageBox.Show(ex.Message);
            }

            //Variaveis de Foto e Digital nao podem ser nulas
            destinationPathFoto = "pack://application:,,,/IMAGES/User_Profile.png";
            destinationPathFinger = "pack://application:,,,/IMAGES/Finger_Print.png";
            // Variaveis para o padrão
            namefoto = "";
            namefinger = "";
            fingerpath = "";
            imagepath = "";
            checkFinger = false;
            checkFoto = false;
            Armamento = false;
            fotoAgentesource = "";
            digitalsource = "";
            alterFinger = false;
            alterPhoto = false;
        }

		public int Update(OracleDataAdapter oracleAdapter, DataRow[] dataRow)
		{
			int tickCount = Environment.TickCount;
			int retVal;

			try
			{
				SetupOracleDataAdapter(oracleAdapter);
				retVal = oracleAdapter.Update(dataRow);
			}
			catch( Exception ex )
			{
				_errorLog.WriteExceptionLog(ex, "OracleDbAgent:");
				throw ex;
			}
			finally
			{
				_counter.CounterName = "Update";
				_counter.RawValue = Environment.TickCount - tickCount;
			}
			return retVal;
		}

        public static bool UpdateTidbitSDR(int tidbitTypeId, Int32 refId, int tidbitOrder, string tidbitText = "", string timecode = "", bool enabled = false, int newTidbitOrder = 0)
        {
            OracleConnection cn = null;
            OracleCommand cmd = null;
            OracleDataAdapter adp = null;
            OracleCommandBuilder bldr = null;
            DataTable tbl = null;
            DataRow row = null;

            bool updated = false;

            try
            {
                cn = createConnectionSDR();

                string sql = "select * from drafttidbits where referencetype = " + tidbitTypeId + " and referenceid = " + refId + " and tidbitorder = " + tidbitOrder;
                cmd = new OracleCommand(sql, cn);
                adp = new OracleDataAdapter(cmd);
                bldr = new OracleCommandBuilder(adp);
                tbl = new DataTable();

                adp.Fill(tbl);

                if (tbl.Rows.Count == 0)
                {
                    row = tbl.Rows.Add();
                }
                else
                {
                    row = tbl.Rows[0];
                }

                row["referencetype"] = tidbitTypeId;
                row["referenceid"] = refId;

                if (newTidbitOrder > 0)
                {
                    row["tidbitorder"] = newTidbitOrder;
                }
                else
                {
                    row["tidbitorder"] = tidbitOrder;
                }

                row["text"] = tidbitText;
                row["enabled"] = enabled;

                //row["timecode"] = timecode;

                adp.Update(tbl.GetChanges());
                tbl.AcceptChanges();

                updated = true;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (adp != null) adp.Dispose();
                if (bldr != null) bldr.Dispose();
                if (tbl != null) tbl.Dispose();
            }

            return updated;
        }

        public static bool SelectPlayer(Player player)
        {
            OracleConnection cn = null;
            OracleCommand cmd = null;
            OracleDataReader rdr = null;
            OracleDataAdapter adp = null;
            OracleCommandBuilder bldr = null;
            DataTable tbl = null;
            DataRow row = null;
            string sql;
            Pick currentPick = GlobalCollections.Instance.OnTheClock;
            int totalPicks = 0;

            bool saved = false;

            try
            {
                cn = createConnectionSDR();

                sql = "select count(*) from draftorder";
                cmd = new OracleCommand(sql, cn);
                rdr = cmd.ExecuteReader();

                if (rdr.HasRows)
                {
                    rdr.Read();
                    totalPicks = Convert.ToInt16(rdr[0]);
                }

                rdr.Close();
                rdr.Dispose();

                if (currentPick.OverallPick > 0 && currentPick.OverallPick <= totalPicks)
                {
                    sql = "select * from draftplayers where playerid = " + player.PlayerId;

                    cmd = new OracleCommand(sql, cn);
                    adp = new OracleDataAdapter(cmd);
                    bldr = new OracleCommandBuilder(adp);
                    tbl = new DataTable();

                    adp.Fill(tbl);

                    if (tbl.Rows.Count > 0)
                    {
                        row = tbl.Rows[0];

                        if (row["pick"] == DBNull.Value || row["pick"].ToString() == "0")
                        {
                            row["pick"] = currentPick.OverallPick;

                            adp.Update(tbl.GetChanges());
                            tbl.AcceptChanges();

                            saved = true;
                        }

                    }

                    //disable all the team finish/ranks pages for NFL
                    if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NFL")
                    {
                        sql = "update drafttidbits set enabled = 0 where referencetype = 2 and referenceid = " + currentPick.Team.ID + " and tidbitorder >= 10 and tidbitorder < 40";
                        cmd = new OracleCommand(sql, cn);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        GlobalCollections.Instance.LoadTeams();
                    }

                }
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (adp != null) adp.Dispose();
                if (bldr != null) bldr.Dispose();
                if (tbl != null) tbl.Dispose();
                if (cn != null) cn.Close(); cn.Dispose();
            }

            return saved;
        }

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

                            if (xmlRow["kiperrank"].ToString() != "")
                            {
                                row["kiperrank"] = Convert.ToInt16(xmlRow["kiperrank"].ToString());
                            }
                            else
                            {
                                row["kiperrank"] = DBNull.Value;
                            }

                            if (xmlRow["mcshayrank"].ToString() != "")
                            {
                                row["mcshayrank"] = Convert.ToInt16(xmlRow["mcshayrank"].ToString());
                            }
                            else
                            {
                                row["mcshayrank"] = DBNull.Value;
                            }

                            row["height"] = xmlRow["height"].ToString();

                            if (xmlRow["weight"].ToString().Trim() != "")
                            {
                                row["weight"] = xmlRow["weight"].ToString() + " LBS";
                            }
                            else
                            {
                                row["weight"] = "";
                            }

                            row["class"] = xmlRow["class"].ToString();

                            adp.Update(tbl.GetChanges());
                            tbl.AcceptChanges();

                            cmd.Dispose();
                            adp.Dispose();
                            bldr.Dispose();
                            tbl.Dispose();

                            DataTable tblTids = null;

                            try
                            {
                                int noteCount;

                                if (ConfigurationManager.AppSettings["DraftType"].ToString().ToUpper() == "NBA")
                                {
                                    noteCount = 2;
                                }
                                else
                                {
                                    noteCount = 4;
                                }

                                for (int i = 1; i <= noteCount; i++)
                                {
                                    if (xmlRow.Table.Columns["matrixnote" + i.ToString()] != null)
                                    {
                                        if (xmlRow["matrixnote" + i.ToString()].ToString().Trim() != "")
                                        {
                                            sql = "select * from espnews.drafttidbits where referenceid = " + xmlRow["playerid"] + " and referencetype = 1 and tidbitorder = " + i;
                                            cmd = new OracleCommand(sql, cn);
                                            adp = new OracleDataAdapter(cmd);
                                            bldr = new OracleCommandBuilder(adp);

        public static bool AddPlayerToDraftPlayers(Int64 playerId, string firstName, string lastName, string position, Int32 schoolId)
        {
            bool saved = false;

            OracleConnection cn = null;
            OracleCommand cmd = null;
            OracleDataAdapter adp = null;
            OracleCommandBuilder bldr = null;
            DataTable tbl = null;

            string sql = "";

            try
            {
                cn = createConnectionSDR();

                sql = "select * from draftplayers where playerid = " + playerId;
                cmd = new OracleCommand(sql, cn);

                adp = new OracleDataAdapter(cmd);
                bldr = new OracleCommandBuilder(adp);

                tbl = new DataTable();

                DataRow row;

                adp.Fill(tbl);

                if (tbl.Rows.Count == 0)
                {
                    row = tbl.Rows.Add();
                    row["playerid"] = playerId;
                }
                else
                {
                    row = tbl.Rows[0];
                }

                row["schoolid"] = schoolId;
                row["firstname"] = firstName;
                row["lastname"] = lastName;
                row["position"] = position;

                adp.Update(tbl.GetChanges());
                tbl.AcceptChanges();

                saved = true;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (cn != null) cn.Close(); cn.Dispose();
            }

            return saved;
        }

        /// <summary>
        /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
        /// </remarks>
        /// <param name="insertCommand">A valid transact-SQL statement or stored procedure to insert new records into the data source</param>
        /// <param name="deleteCommand">A valid transact-SQL statement or stored procedure to delete records from the data source</param>
        /// <param name="updateCommand">A valid transact-SQL statement or stored procedure used to update records in the data source</param>
        /// <param name="dataSet">The DataSet used to update the data source</param>
        /// <param name="tableName">The DataTable used to update the data source.</param>
        public static void UpdateDataset(OracleCommand insertCommand, OracleCommand deleteCommand, OracleCommand updateCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null) throw new ArgumentNullException("insertCommand");
            if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

            // Create a OracleDataAdapter, and dispose of it after we are done
            using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
            {
                // Set the data adapter commands
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;

                // Update the dataset changes in the data source
                dataAdapter.Update(dataSet, tableName);

                // Commit all the changes made to the DataSet
                dataSet.AcceptChanges();
            }
        }

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

                                #region 6 Matrix Notes

                                //import the 4 matrix notes
                                for (i = 1; i <= 6; i++)
                                {
                                    if (xmlRow["note" + i.ToString()].ToString().Trim() != "")
                                    {
                                        sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = " + i.ToString() + " and referenceid = " + xmlRow["teamid"];
                                        cmd = new OracleCommand(sql, cn);
                                        adp = new OracleDataAdapter(cmd);
                                        bldr = new OracleCommandBuilder(adp);

                                        tbl = new DataTable();

                                        adp.Fill(tbl);

                                        if (tbl.Rows.Count == 0)
                                        {
                                            row = tbl.Rows.Add();
                                            row["referencetype"] = 2;
                                            row["referenceid"] = xmlRow["teamid"];
                                            row["tidbitorder"] = i;
                                            row["enabled"] = 1;
                                        }
                                        else
                                        {
                                            row = tbl.Rows[0];
                                        }

                                        row["text"] = xmlRow["note" + i.ToString()].ToString();

                                        adp.Update(tbl.GetChanges());
                                        tbl.AcceptChanges();

                                        cmd.Dispose();
                                        adp.Dispose();
                                        bldr.Dispose();
                                        tbl.Dispose();
                                    }
                                }

                                #endregion

                                #region MySql team ranks/results

                                cnMySql = createConnectionMySql();

                                sql = "select * from teams where id = " + xmlRow["teamid"];
                                cmdMySql = new MySqlCommand(sql, cnMySql);
                                adpMySql = new MySqlDataAdapter(cmdMySql);
                                bldrMySql = new MySqlCommandBuilder(adpMySql);

                                tbl = new DataTable();

                                adpMySql.Fill(tbl);

                                if (tbl.Rows.Count > 0)
                                {
                                    row = tbl.Rows[0];

                                    row["totalpicks"] = totalPicks;
                                    row["overallrecord"] = xmlRow["record"];
                                    row["divisionresult"] = xmlRow["divresult"];
                                    row["playoffs"] = xmlRow["playoffs"];

        public bool UpdateTable(DataTable table, string tableName)
        {
            try
            {
                TableHelper.SetDefaultColumnValues(table);

                var con = CONNECTION.OpenCon();

                var adapter = new OracleDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con);
                var cmd = new OracleCommandBuilder(adapter);
                adapter.Update(table);

                cmd.Dispose();
                adapter.Dispose();
                CONNECTION.CloseCon(con);

                return true;
            }
            catch (DBConcurrencyException cex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "UpdateTable DBConcurrencyError!",
                    Ex = cex,
                });
                return false;
            }
            catch (Exception ex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "UpdateTable Error!",
                    Ex = ex,
                });
                return false;
            }
        }