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