.Net中使用OracleDataAdapter

本來只想簡單記錄一下OracleDataAdapter的批量增加和修改用法的，在園子里看到一篇比較詳細的就在這分享了(Oracle Data PRovider for .NET)，雖然用的是Update(DataSet dataSet, string srcTable);，其實都差不多；dataSet為新數據集，srcTable為對應數據庫表名

值得提一句的就是，對于新的DataTable數據，根據情況要使用AcceptChanges();方法，然后設置表中每行數據狀態，如修改數據操作

foreach (DataRow dr in data.Rows) { if (dr.RowState == DataRowState.Unchanged) dr.SetModified(); }

然后再用Update(DataTable dataTable)

使用前保證新DataTabele用AcceptChanges();方法保存了

 1 public static bool MultiUpdateData(DataTable data, string Columns, string tableName) 2         { 3             using (OracleConnection connection = new OracleConnection(connStr)) 4             { 5                 string SQLString = string.Format("select {0} from {1} where rownum=0", Columns, tableName); 6                 using (OracleCommand cmd = new OracleCommand(SQLString, connection)) 7                 { 8                     try 9                     {10                         connection.Open();11                         OracleDataAdapter myDataAdapter = new OracleDataAdapter();12                         myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection);13                         OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);14                         custCB.ConflictOption = ConflictOption.OverwriteChanges;15                         custCB.SetAllValues = true;16                         foreach (DataRow dr in data.Rows)17                         {18                             if (dr.RowState == DataRowState.Unchanged)19                                 dr.SetModified();20                         }21                         myDataAdapter.Update(data);22                         data.AcceptChanges();23                         myDataAdapter.Dispose();24                         return true;25                     }26                     catch (System.Data.OracleClient.OracleException E)27                     {28                         connection.Close();29                         return false;30                     }31                 }32             }33         }

一下是鏈接文章，抄在這是怕丟失吧(多慮了)

1. 通過DataAdapter訪問數據庫DataAdapter有四個屬性SelectCommand,DeleteCommand,InsertCommand,UpdateCommand1.1 當做檢索處理的時候， 執行SelectCommand的操作，返回數據集。

// C# 例子using System;using System.Data;using System.xml;using Oracle.Dataaccess.Client;class testSample{static void Main(){//數據庫連接打開 OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;PassWord=tiger;Data Source=oracle;"; con.Open(); Console.WriteLine("Connected Successfully");

// Create the command // sql文 OracleCommand cmd = new OracleCommand("", con);//select statement string strSelectSql = "SELECT STU_ID, STU_NAME, AGE, BIRTHDAY, SEX FROM TBL_STUDENT " + " WHERE SEX = :I_SEX AND BIRTHDAY >= :I_BIRTHDAY AND AGE = :I_AGE ";

//command和檢索sql文 關聯 cmd.CommandText = strSelectSql;//sql文中變量通過oracle參數傳遞 OracleParameter oraParameter;

oraParameter = new OracleParameter("I_SEX",OracleDbType.Varchar2, 2); oraParameter.Value = "01"; cmd.Parameters.Add(oraParameter);//字符型

oraParameter = new OracleParameter("I_BIRTHDAY",OracleDbType.Date);//日期型 oraParameter.Value = "1986/01/01"; cmd.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_AGE",OracleDbType.Int32); oraParameter.Value = 20; cmd.Parameters.Add(oraParameter);

DataSet dtTmp = new DataSet();

using(OracleDataAdapter dataAdapter = new OracleDataAdapter()) { dataAdapter.SelectCommand = cmd;//檢索command設置 dataAdapter.Fill(dtTmp);//檢索結果保存在dtTmp數據集中 }

// Console.WriteLine("Number of rows : {0} ", dtTmp.Tables[0].Rows.Count); // Close and Dispose OracleConnection object con.Close(); con.Dispose();

}

1.2 如果sql文只是想count(*) 獲得數據庫中記錄件數，可以直接使用OracleCommand.ExecuteScalar()來快速取得。

// C# 例子

Cmd = new OracleCommand( "SELECT COUNT(*) FROM TBL_STUDENT", Conn );Object o = Cmd.ExecuteScalar();int nRecordCount = Convert.ToInt32(o.ToString());1.3 當做更新處理的時候，調用 OracleDataAdapter 的Update方法，對輸入參數DataTable中每行進行循環， 根據當前行的狀態調用相應的 INSERT、UPDATE 或 DELETE 語句。

RowState屬性相關處理的OracleCommandDataRowState.Added 該行已添加 :InsertCommandDataRowState.Deleted 該行已刪除 :DeleteCommandDataRowState.Modified 該行已被修改 :UpdateCommand// C# 例子using System;using System.Data;using System.Xml;using Oracle.DataAccess.Client;class testSample{public void updateStuData(DataSet i_Data){ //數據庫連接打開int nRecCount = 0;OracleConnection con = new OracleConnection();con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";con.Open();Console.WriteLine("Connected Successfully");

// Create the command// sql文OracleCommand cmdUpdate = new OracleCommand("", con);

// sql文OracleCommand cmdDelete = new OracleCommand("", con);

// sql文OracleCommand cmdInsert = new OracleCommand("", con);

//select statementstring strUpdateSql = "UPDATE TBL_STUDENT SET STU_NAME = :I_STU_NAME WHERE STU_ID = :I_STU_ID ";string strDeleteSql = "DELETE FROM TBL_STUDENT WHERE STU_ID = :I_STU_ID";string strInsertSql = "INSERT TBL_STUDENT VALUES (:I_STU_ID, :I_STU_NAME, :I_AGE,:I_BIRTHDAY, :I_SEX )";

//command和檢索sql文 關聯cmdUpdate.CommandText = strUpdateSql ;cmdDelete.CommandText = strDeleteSql ;cmdInsert.CommandText = strInsertSql ;

//sql文中變量通過oracle參數傳遞OracleParameter oraParameter;

//當設置參數的時候//oracle參數值可通過SourceColumn屬性與更新數據行的字段綁定//取得數據值的版本可通過SourceVersion來設定。//DataRowVersion.Current 取得數據行中修改后的數據值//DataRowVersion.Original 取得數據行中原始的數據值//Where條件里面的參數值往往設置為取DataRowVersion.Original版本//更新或者插入的參數值往往設置為取DataRowVersion.Current版本

//--------------------------------------------------//更新用的oracle commandoraParameter = new OracleParameter("I_STU_NAME", OracleDbType.Varchar2, 20);oraParameter.SourceColumn = "STU_NAME";//更新字段值oraParameter.SourceVersion = DataRowVersion.Current;cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_STU_ID", OracleDbType.Varchar2, 5);oraParameter.SourceColumn = "STU_ID";//檢索字段值oraParameter.SourceVersion = DataRowVersion.Original;cmdUpdate.Parameters.Add(oraParameter);

//--------------------------------------------------//刪除用的oracle commandoraParameter = new OracleParameter("I_STU_ID", OracleDbType.Varchar2, 5);oraParameter.SourceColumn = "STU_ID";//檢索字段值oraParameter.SourceVersion = DataRowVersion.Original;cmdDelete.Parameters.Add(oraParameter);

//--------------------------------------------------//追加用的oracle commandoraParameter = new OracleParameter("I_STU_ID", OracleDbType.Varchar2, 5);oraParameter.SourceColumn = "STU_ID";oraParameter.SourceVersion = DataRowVersion.Current;cmdInsert.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_STU_NAME", OracleDbType.Varchar2, 20);oraParameter.SourceColumn = "STU_NAME";oraParameter.SourceVersion = DataRowVersion.Current;cmdInsert.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_AGE", OracleDbType.Int32);oraParameter.SourceColumn = "AGE";oraParameter.SourceVersion = DataRowVersion.Current;cmdInsert.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_BIRTHDAY", OracleDbType.Date);oraParameter.SourceColumn = "BIRTHDAY";oraParameter.SourceVersion = DataRowVersion.Current;cmdInsert.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_SEX", OracleDbType.Varchar2, 2);oraParameter.SourceColumn = "SEX";oraParameter.SourceVersion = DataRowVersion.Current;cmdInsert.Parameters.Add(oraParameter);

using(OracleDataAdapter dataAdapter = new OracleDataAdapter()){dataAdapter.InsertCommand= cmdUpdate;//追加command設置dataAdapter.DeleteCommand= cmdDelet