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; }
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 static void DataAdapterTest2_Update (OracleConnection con) { Console.WriteLine("================================"); Console.WriteLine("=== Adapter Update ============="); Console.WriteLine("================================"); OracleTransaction transaction = con.BeginTransaction (); Console.WriteLine(" Create adapter..."); OracleCommand selectCmd = con.CreateCommand (); selectCmd.Transaction = transaction; selectCmd.CommandText = "SELECT * FROM mono_adapter_test"; OracleDataAdapter da = new OracleDataAdapter(selectCmd); Console.WriteLine(" Create command builder..."); OracleCommandBuilder mycb = new OracleCommandBuilder(da); Console.WriteLine(" Create data set ..."); DataSet ds = new DataSet(); Console.WriteLine(" Set missing schema action..."); Console.WriteLine(" Fill data set via adapter..."); da.Fill(ds, "mono_adapter_test"); DataRow myRow; Console.WriteLine(" New Row..."); myRow = ds.Tables["mono_adapter_test"].Rows[0]; Console.WriteLine("Tables Count: " + ds.Tables.Count.ToString()); DataTable table = ds.Tables["mono_adapter_test"]; DataRowCollection rows; rows = table.Rows; Console.WriteLine(" Row Count: " + rows.Count.ToString()); myRow = rows[0]; byte[] bytes = new byte[] { 0x62,0x63,0x64,0x65,0x66,0x67 }; Console.WriteLine(" Set values in the new DataRow..."); myRow["varchar2_value"] = "Super Power!"; myRow["number_scaled_value"] = 12.35; myRow["number_integer_value"] = 457; myRow["float_value"] = 198.76; myRow["date_value"] = new DateTime(2002,08,09); //myRow["char_value"] = "Juliet"; myRow["clob_value"] = "this is a clob"; myRow["blob_value"] = bytes; Console.WriteLine("da.Update(ds..."); da.Update(ds, "mono_adapter_test"); transaction.Commit(); mycb.Dispose(); mycb = null; }
/// <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(); } }