C# OracleDataAdapter.Update方法代码示例

 private void button2_Click(object sender, EventArgs e)
 {
     oracle.LinkToOracle("orcl", "hui", "oracle");
     OracleCommand cmd = oracle.getConnection().CreateCommand();
     if (radioButton1.Checked)
     {
         OracleDataAdapter sda = new OracleDataAdapter("select * from department", oracle.getConnection());
         DataTable query = new DataTable();
         query = (DataTable)dataGridView1.DataSource;
         OracleCommandBuilder sb = new OracleCommandBuilder(sda);
         sda.Update(query);
         query.Clear();
         sda.Fill(query);
     }
     if (radioButton2.Checked)
     {
         OracleDataAdapter sda = new OracleDataAdapter("select * from teacher", oracle.getConnection());
         DataTable query = new DataTable();
         query = (DataTable)dataGridView1.DataSource;
         OracleCommandBuilder sb = new OracleCommandBuilder(sda);
         sda.Update(query);
         query.Clear();
         sda.Fill(query);
     }
     oracle.LinkClose();
     cmd.Dispose();
 }

        private void AddMenu()
        {
            try
            {
                this.Open();
                string sql_insetmenu = "insert into SYS_MODEL (ID,MODELNAME,PARENTMODEL,MODEL_DLL,DBTYPE,SYSTYPE) values(MENU_SEQ.nextval,'" + txtModelName.Text.Trim().ToString() + "','" + 0 + "','" + GetFormName() + "','" + this.txtModelSortno.Text.Trim().ToString() + "','" + this.combSystem.Tag.ToString() + "')";
                string str1 = "select * from SYS_MODEL ";
                OracleDataAdapter adp1 = new OracleDataAdapter();
                OracleCommand comm3 = new OracleCommand(str1, MyConn);
                adp1.SelectCommand = comm3;
                DataSet ds1 = new DataSet();
                adp1.Fill(ds1, "SYS_MODEL");
                adp1.InsertCommand = new OracleCommand(sql_insetmenu, MyConn);
                adp1.InsertCommand.ExecuteNonQuery();
                adp1.Update(ds1, "SYS_MODEL");

            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
            finally
            { this.sClose(); }
        }

        private void AleartMenu()
        {
            try
            {
                this.Open();
                string str1 = "select * from SYS_MODEL ";
                OracleDataAdapter adp1 = new OracleDataAdapter();
                OracleCommand comm1 = new OracleCommand(str1, MyConn);
                adp1.SelectCommand = comm1;
                DataSet ds1 = new DataSet();
                adp1.Fill(ds1, "SYS_MODEL");
                string str2 = "update SYS_MODEL set MODELNAME='" + this.txtModelName.Text.Trim().ToString() + "',MODEL_DLL='" + this.txtModelFrom.Text.Trim().ToString() + "',DBTYPE='" + this.txtModelSortno.Text.Trim().ToString() + "',SYSTYPE='" + this.combSystem.Tag.ToString() + "',PARENTMODEL='" + this.combParentModel.Tag.ToString() + "' where id='" + this.textBox4.Tag + "'";
                adp1.UpdateCommand = new OracleCommand(str2, MyConn);
                adp1.UpdateCommand.ExecuteNonQuery();
                adp1.Update(ds1, "SYS_MODEL");

            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
            finally
            { this.sClose(); }
        }

 /// <summary>
 /// Updates the underlying data of the <see cref="DataTable"/> using <see cref="OracleConnection"/>, and
 /// returns the number of rows successfully updated.
 /// </summary>
 /// <param name="sourceData">The <see cref="DataTable"/> used to update the underlying data source.</param>
 /// <param name="sourceSql">The SQL statement used initially to populate the <see cref="DataTable"/>.</param>
 /// <param name="connection">The <see cref="OracleConnection"/> to use for updating the underlying data source.</param>
 /// <returns>The number of rows successfully updated from the <see cref="DataTable"/>.</returns>
 public static int UpdateData(this OracleConnection connection, DataTable sourceData, string sourceSql)
 {
     OracleDataAdapter dataAdapter = new OracleDataAdapter(sourceSql, connection);
     OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
     return dataAdapter.Update(sourceData);
 }

        private void button3_Click(object sender, EventArgs e)
        {
            if (dataGridView1.DataSource == null || dataGridView1.CurrentRow == null)
            {
                return;
            }
            else
            {
                if (this.dataGridView1.SelectedRows.Count > 0)
                {
                    DialogResult dr = MessageBox.Show("确定删除选中的记录   ", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (dr == DialogResult.OK)
                    {
                        try
                        {
                            oracle.LinkToOracle("orcl", "hui", "oracle");
                            OracleCommand cmd = oracle.getConnection().CreateCommand();
                            if (radioButton1.Checked)
                            {
                                foreach (DataGridViewRow row in this.dataGridView1.SelectedRows) //遍历所选中的dataGridView记录行
                                {

                                    string strName = row.Cells[1].Value.ToString();  //取dataGridView1中的第三列的值
                                    cmd.CommandText = string.Format("delete from department where tno='{0}'", strName); //SQL语句

                                    cmd.ExecuteNonQuery();  //执行删除操作
                                    cmd.CommandText = String.Format("select * from department");
                                    OracleDataAdapter sda = new OracleDataAdapter(cmd);
                                    DataTable query = new DataTable();
                                    sda.Update(query);
                                    query.Clear();
                                    sda.Fill(query);
                                    dataGridView1.DataSource = query;
                                    cmd.Dispose();
                                }
                            }
                            if (radioButton2.Checked)
                            {
                                foreach (DataGridViewRow row in this.dataGridView1.SelectedRows) //遍历所选中的dataGridView记录行
                                {

                                    string strName = row.Cells[0].Value.ToString();  //取dataGridView1中的第三列的值
                                    cmd.CommandText = string.Format("delete from teacher where tno='{0}'", strName); //SQL语句

                                    cmd.ExecuteNonQuery();  //执行删除操作
                                    cmd.CommandText = String.Format("select * from teacher");
                                    OracleDataAdapter sda = new OracleDataAdapter(cmd);
                                    DataTable query = new DataTable();
                                    sda.Update(query);
                                    query.Clear();
                                    sda.Fill(query);
                                    dataGridView1.DataSource = query;
                                    cmd.Dispose();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.ToString(), "提示");
                        }
                        finally
                        {
                            oracle.LinkClose();
                        }

                    }
                    else
                    {
                        return;
                    }
                }
            }
        }

        private void DelectCurrentMenu(string code)
        {
            try
            {

                string str1 = "select * from SYS_MODEL ";
                OracleDataAdapter adp1 = new OracleDataAdapter();
                OracleCommand comm3 = new OracleCommand(str1, MyConn);
                adp1.SelectCommand = comm3;
                DataSet ds1 = new DataSet();
                adp1.Fill(ds1, "SYS_MODEL");
                string str2 = "delete from SYS_MODEL where id='" + code + "'";
                string str3 = "select id from SYS_MODEL where PARENTMODEL='" + code + "'";
                OracleCommand comm1 = new OracleCommand(str3, MyConn);
                OracleDataReader reader1 = comm1.ExecuteReader();
                while (reader1.Read())
                {
                    string nextcode = reader1.GetValue(0).ToString();
                    DelectCurrentMenu(nextcode);
                }
                adp1.DeleteCommand = new OracleCommand(str2, MyConn);
                adp1.DeleteCommand.ExecuteNonQuery();
                adp1.Update(ds1, "SYS_MODEL");

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

		public static void DataAdapterTest2_Delete (OracleConnection con) 
		{
			Console.WriteLine("================================");
			Console.WriteLine("=== Adapter Delete =============");
			Console.WriteLine("================================");
			OracleTransaction transaction = con.BeginTransaction ();
		
			Console.WriteLine("   Create adapter...");
			OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM mono_adapter_test", con);
			Console.WriteLine("   Create command builder...");
			OracleCommandBuilder mycb = new OracleCommandBuilder(da);
			Console.WriteLine("   set transr...");
			da.SelectCommand.Transaction = transaction;

			Console.WriteLine("   Create data set ...");
			DataSet ds = new DataSet();
		
			Console.WriteLine("Fill data set via adapter...");
			da.Fill(ds, "mono_adapter_test");

			Console.WriteLine("delete row...");
			ds.Tables["mono_adapter_test"].Rows[0].Delete();

			Console.WriteLine("da.Update(table...");
			da.Update(ds, "mono_adapter_test");

			Console.WriteLine("Commit...");
			transaction.Commit();

			mycb.Dispose();
			mycb = null;
		}

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="data">需要更新的数据集(只要指定表名与ORM,支持更新多个数据表)</param>
        /// <returns></returns>
        public virtual bool Update(DataSet data)
        {
            //非用户手动事务模式，预设启用事务
            if (_UserManualControlTrans == false) this.BeginTransaction();

            if (_CurrentTrans == null) throw new Exception("用户手动控制事务模式下，但您没有启用事务！");

            try
            {
                foreach (DataTable dt in data.Tables)
                {
                    if (dt.GetChanges() == null) continue; //没有数据更新

                    IGenerateSqlCommand gen = this.CreateSqlGenerator(dt.TableName);
                    if (gen == null) throw new Exception("创建IGenerateSqlCommand失败！");
                    if (_Loginer.DbType == DbAcessTyp.SQLServer)
                    {
                        SqlDataAdapter adp = new SqlDataAdapter();
                        adp.RowUpdating += new SqlRowUpdatingEventHandler(OnAdapterRowUpdating);
                        adp.UpdateCommand = (SqlCommand)GetUpdateCommand(gen, _CurrentTrans);
                        adp.InsertCommand = (SqlCommand)GetInsertCommand(gen, _CurrentTrans);
                        adp.DeleteCommand = (SqlCommand)GetDeleteCommand(gen, _CurrentTrans);
                        adp.Update(dt);
                    }
                    else
                    {
                        OracleDataAdapter adp = new OracleDataAdapter();
                        //adp.RowUpdating += adp_RowUpdating;

                        adp.UpdateCommand = (OracleCommand)GetUpdateCommand(gen, _CurrentTrans);
                        adp.InsertCommand = (OracleCommand)GetInsertCommand(gen, _CurrentTrans);
                        adp.DeleteCommand = (OracleCommand)GetDeleteCommand(gen, _CurrentTrans);
                        adp.Update(dt);
                    }

                }

                if (_UserManualControlTrans == false) this.CommitTransaction(); //提交事务

                return true;
            }
            catch (Exception ex)
            {
                if (_UserManualControlTrans == false) this.RollbackTransaction();//回滚事务

                throw new Exception("更新数据发生错误！Event:Update()\r\n\r\n" + ex.Message);
            }
        }

		public static void DataAdapterTest2_Insert (OracleConnection con) 
		{
			Console.WriteLine("================================");
			Console.WriteLine("=== Adapter Insert =============");
			Console.WriteLine("================================");
			OracleTransaction transaction = con.BeginTransaction ();
		
			Console.WriteLine("   Create adapter...");
			OracleDataAdapter da = new OracleDataAdapter("select * from mono_adapter_test", con);
			da.SelectCommand.Transaction = transaction;
		
			Console.WriteLine("   Create command builder...");
			OracleCommandBuilder mycb = new OracleCommandBuilder(da);

			Console.WriteLine("   Create data set ...");
			DataSet ds = new DataSet();
		
			Console.WriteLine("   Fill data set via adapter...");
			da.Fill(ds, "mono_adapter_test");

			Console.WriteLine("   New Row...");
			DataRow myRow;
			myRow = ds.Tables["mono_adapter_test"].NewRow();

			byte[] bytes = new byte[] { 0x45,0x46,0x47,0x48,0x49,0x50 };

			Console.WriteLine("   Set values in the new DataRow...");
			myRow["varchar2_value"] = "OracleClient";
			myRow["number_whole_value"] = 22;
			myRow["number_scaled_value"] = 12.34;
			myRow["number_integer_value"] = 456;
			myRow["float_value"] = 98.76;
			myRow["date_value"] = new DateTime(2001,07,09);
			Console.WriteLine("   *** FIXME; char value not working");
			//myRow["char_value"] = "Romeo";
			myRow["clob_value"] = "clobtest";
			myRow["blob_value"] = bytes;
		
			Console.WriteLine("    Add DataRow to DataTable...");		
			ds.Tables["mono_adapter_test"].Rows.Add(myRow);

			Console.WriteLine("da.Update(ds...");
			da.Update(ds, "mono_adapter_test");

			transaction.Commit();

			mycb.Dispose();
			mycb = null;
		}

		public void run()
		{
			Exception exp = null;

			OracleDataAdapter oleDBda = new OracleDataAdapter();
			oleDBda.SelectCommand = new OracleCommand("",new OracleConnection());

			base.OracleDataAdapter_BuildUpdateCommands(ref oleDBda);		
			// --------- get data from DB -----------------
			DataSet ds = base.PrepareDBData_Update((DbDataAdapter)oleDBda);


			// add event handler
			oleDBda.RowUpdated += new OracleRowUpdatedEventHandler(oleDBda_RowUpdated);
			
			//insert ,delete, update
			drInsert = ds.Tables[0].NewRow();
			drInsert.ItemArray = new object[] {9991,"Ofer","Borshtein","Insert"};
			drDelete = ds.Tables[0].Rows.Find(9992);
			drUpdate = ds.Tables[0].Rows.Find(9993);
		
			ds.Tables[0].Rows.Add(drInsert);
			drDelete.Delete();
			drUpdate["Title"] = "Jack the ripper"; 

			//execute update to db, will raise events
			oleDBda.Update(ds);

			try
			{
				BeginCase("EventCounter ");
				Compare(EventCounter ,3);
			}
			catch(Exception ex)	{exp = ex;}
			finally	{EndCase(exp); exp = null;}
		
			oleDBda.RowUpdated -= new OracleRowUpdatedEventHandler(oleDBda_RowUpdated);
		
			//close connection
			if (  ((IDbDataAdapter)oleDBda).SelectCommand.Connection.State != ConnectionState.Closed )
				((IDbDataAdapter)oleDBda).SelectCommand.Connection.Close();
		}

 /// <summary>
 /// 将DataTable插入数据库中
 /// </summary>
 /// <param name="dbtablename">数据库中要插入的表名</param>
 /// <param name="dt">DataTable</param>
 /// <param name="con">数据库连接字符串</param>
 /// <returns></returns>
 public bool InsertDatatable(string queryString, DataTable dt, string con)
 {
     try
     {
         using (OracleConnection connection = new OracleConnection(con))
         {
             OracleDataAdapter adapter = new OracleDataAdapter();
             adapter.SelectCommand = new OracleCommand(queryString, connection);
             OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
             connection.Open();
             foreach (DataRow dr in dt.Rows)
             {
                 dr.AcceptChanges();
                 dr.SetAdded();
             }
             adapter.Update(dt);
             connection.Close();
         }
         return true;
     }
     catch (Exception e)
     {
         Log.Error(String.Format("检疫监管{0}表统计数据保存时", queryString.Replace("select * from", "")), e);
         return false;
     }
 }

 //批量更新
 public static bool MultiUpdateData(DataTable data, string Columns, string tableName)
 {
     //string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
     string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString();
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         string SQLString = string.Format("select {0} from {1} where rownum=0", Columns, tableName);
         using (OracleCommand cmd = new OracleCommand(SQLString, connection))
         {
             try
             {
                 connection.Open();
                 OracleDataAdapter myDataAdapter = new OracleDataAdapter();
                 myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection);
                 OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);
                 custCB.ConflictOption = ConflictOption.OverwriteChanges;
                 custCB.SetAllValues = true;
                 foreach (DataRow dr in data.Rows)
                 {
                     if (dr.RowState == DataRowState.Unchanged)
                     {
                         dr.SetModified();
                     }
                 }
                 myDataAdapter.Update(data);
                 data.AcceptChanges();
                 myDataAdapter.Dispose();
                 return true;
             }
             catch (System.Data.OracleClient.OracleException E)
             {
                 connection.Close();
                 return false;
             }
         }
     }
 }

        //批量插入
        public static bool MultiInsertData(DataSet ds, string Columns, string tableName)
        {
            //string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString();
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                string SQLString = string.Format("select {0} from {1} where rownum=0", Columns, tableName);

                using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        OracleDataAdapter myDataAdapter = new OracleDataAdapter();
                        myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection);
                        myDataAdapter.UpdateBatchSize = 0;
                        OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);
                        DataTable dt = ds.Tables[0].Copy();
                        DataTable dtTemp = dt.Clone();

                        int times = 0;
                        for (int count = 0; count < dt.Rows.Count; times++)
                        {
                            for (int i = 0; i < 400 && 400 * times + i < dt.Rows.Count; i++, count++)
                            {
                                dtTemp.Rows.Add(dt.Rows[count].ItemArray);
                            }
                            myDataAdapter.Update(dtTemp);
                            dtTemp.Rows.Clear();
                        }
                        dt.Dispose();
                        dtTemp.Dispose();
                        myDataAdapter.Dispose();
                        return true;
                    }
                    catch (System.Data.OracleClient.OracleException E)
                    {
                        connection.Close();
                        return false;
                    }
                }
            }
        }

        /// <summary>
        /// 功能：把dt表中的数据全部追加到Oracle数据中指定的表
        /// 注意：要保证dt表的列与数据库中指定表默认检索的列的次序相同
        /// </summary>
        /// <param name="strconn">链接字符串</param>
        /// <param name="dt">要追加的数据</param>
        /// <param name="tb_name">数据库中的表名</param>
        public void insertDB(DataTable dt, string tb_name)
        {
            try
            {
                conn = new OracleConnection(strconn);
                conn.Open();

                //获取表结构，构造new_dt
                com = conn.CreateCommand();
                com.CommandText = "select * from " + tb_name + " where 0=1"; ;
                com.CommandType = CommandType.Text;

                da = new OracleDataAdapter(com);
                DataTable new_dt = new DataTable();
                da.Fill(new_dt);

                //复制dt的数据到new_dt中
                DataRow new_dr = new_dt.NewRow();
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < dt.Columns.Count; ++i)
                    {
                        new_dr[i] = dr[i];
                    }
                    new_dt.Rows.Add(new_dr);
                }

                //自动构造InsertCommand命令
                ocb = new OracleCommandBuilder(da);
                da.InsertCommand = ocb.GetInsertCommand();

                da.UpdateBatchSize = 5000;
                da.Update(new_dt);
            }
            catch (Exception ex)
            {
                //暂不处理，抛给上层做处理
                throw (ex);
            }
            finally
            {
                conn.Close();
                ocb.Dispose();
                da.Dispose();
                com.Dispose();
                conn.Dispose();
            }
        }

        /// <summary>
        /// 保存跟踪的字段定义数据字典
        /// </summary>
        public bool SaveFieldDef(DataTable data)
        {
            string sqlAdd="";
            string sqlEdt = "";
            string sqlDel = "";
            int ret = 0;
            IDbConnection conn = DataProvider.Instance.CreateConnection(_Loginer.DBName);
            if (_Loginer.DbType == DbAcessTyp.SQLServer)
            {
                sqlAdd = "INSERT INTO sys_Log_TableFields(fid,TableName,FieldName) VALUES (@fid,@TableName,@FieldName)";
                sqlEdt = "UPDATE sys_Log_TableFields SET [email protected],[email protected] WHERE [email protected] ";
                sqlDel = "DELETE sys_Log_TableFields WHERE [email protected]";
                SqlCommand cmdUpdate = new SqlCommand(sqlEdt, (SqlConnection)conn);
                cmdUpdate.Parameters.Add("@TableName", SqlDbType.VarChar, 50, "TableName");
                cmdUpdate.Parameters.Add("@FieldName", SqlDbType.VarChar, 20, "FieldName");
                cmdUpdate.Parameters.Add("@fid", SqlDbType.Int, 4, "fid");

                SqlCommand cmdDelete = new SqlCommand(sqlDel, (SqlConnection)conn);
                cmdDelete.Parameters.Add("@fid", SqlDbType.Int, 4, "fid");

                SqlCommand cmdInsert = new SqlCommand(sqlAdd, (SqlConnection)conn);
                cmdInsert.Parameters.Add("@TableName", SqlDbType.VarChar, 50, "TableName");
                cmdInsert.Parameters.Add("@FieldName", SqlDbType.VarChar, 20, "FieldName");
                cmdInsert.Parameters.Add("@fid", SqlDbType.Int, 4, "fid");
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.UpdateCommand = cmdUpdate;
                adp.DeleteCommand = cmdDelete;
                adp.InsertCommand = cmdInsert;
                ret = adp.Update(data);
            }
            else
            {
                sqlAdd = "INSERT INTO sys_Log_TableFields(fid,TableName,FieldName) VALUES (:TableName,:FieldName)";
                sqlEdt = "UPDATE sys_Log_TableFields SET TableName=:TableName,FieldName=:FieldName WHERE Fid=:isid ";
                sqlDel = "DELETE sys_Log_TableFields WHERE fid=:isid";
                OracleCommand cmdUpdate = new OracleCommand(sqlEdt, (OracleConnection)conn);
                OracleCommand cmdDelete = new OracleCommand(sqlDel, (OracleConnection)conn);
                OracleCommand cmdInsert = new OracleCommand(sqlAdd, (OracleConnection)conn);
                OracleParameter para = new OracleParameter("TableName", OleDbType.VarChar);
                para.SourceColumn = "TableName";
                cmdUpdate.Parameters.Add(para);
                cmdInsert.Parameters.Add(para);
                para = new OracleParameter("FieldName", OleDbType.VarChar);
                para.SourceColumn = "FieldName";
                cmdUpdate.Parameters.Add(para);
                cmdInsert.Parameters.Add(para);
                para = new OracleParameter("fid", OleDbType.VarChar);
                para.SourceColumn = "fid";
                cmdUpdate.Parameters.Add(para);
                cmdDelete.Parameters.Add(para);
                cmdInsert.Parameters.Add(para);

                OracleDataAdapter adp = new OracleDataAdapter();
                adp.UpdateCommand = cmdUpdate;
                adp.DeleteCommand = cmdDelete;
                adp.InsertCommand = cmdInsert;
                ret = adp.Update(data);
            }

            return ret > 0;
        }