.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