C# Oracle数据库操作类实例详解
本文所述为C#实现的Oracle数据库操作类，可执行超多常用的Oracle数据库操作，包含了基础数据库连接、关闭连接、输出记录集、执行Sql语句，返回带分页功能的dataset、取表里字段的类型和长度等，同时还有哈稀表自动插入数据库等高级任务。需要特别指出的是：在执行SQL语句，返回DataReader之前一定要先用.read()打开,然后才能读到数据，再用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"。
完整的C#Oracle数据库类实例代码如下：
usingSystem;
usingSystem.Data;
usingSystem.Data.OracleClient;
usingSystem.Collections;
usingSystem.Reflection;
namespaceMyOraComm
{
///ConnDbForOracle的摘要说明。
publicclassConnForOracle
{
protectedOracleConnectionConnection;
privatestringconnectionString;
publicConnForOracle()
{
stringconnStr;
connStr=System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();
connectionString=connStr;
Connection=newOracleConnection(connectionString);
}
#region带参数的构造函数
///带参数的构造函数
///数据库联接字符串
publicConnForOracle(stringConnString)
{
stringconnStr;
connStr=System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();
Connection=newOracleConnection(connStr);
}
#endregion
#region打开数据库
///打开数据库
publicvoidOpenConn()
{
if(this.Connection.State!=ConnectionState.Open)
this.Connection.Open();
}
#endregion
#region关闭数据库联接
///关闭数据库联接
publicvoidCloseConn()
{
if(Connection.State==ConnectionState.Open)
Connection.Close();
}
#endregion
#region执行SQL语句，返回数据到DataSet中
///执行SQL语句，返回数据到DataSet中
///sql语句
///自定义返回的DataSet表名
///返回DataSet
publicDataSetReturnDataSet(stringsql,stringDataSetName)
{
DataSetdataSet=newDataSet();
OpenConn();
OracleDataAdapterOraDA=newOracleDataAdapter(sql,Connection);
OraDA.Fill(dataSet,DataSetName);
//CloseConn();
returndataSet;
}
#endregion
#region执行Sql语句,返回带分页功能的dataset
///执行Sql语句,返回带分页功能的dataset
///Sql语句
///每页显示记录数
///<当前页/param>
///返回dataset表名
///返回DataSet
publicDataSetReturnDataSet(stringsql,intPageSize,intCurrPageIndex,stringDataSetName)
{
DataSetdataSet=newDataSet();
OpenConn();
OracleDataAdapterOraDA=newOracleDataAdapter(sql,Connection);
OraDA.Fill(dataSet,PageSize*(CurrPageIndex-1),PageSize,DataSetName);
//CloseConn();
returndataSet;
}
#endregion
#region执行SQL语句，返回DataReader,用之前一定要先.read()打开,然后才能读到数据
///执行SQL语句，返回DataReader,用之前一定要先.read()打开,然后才能读到数据
///sql语句
///返回一个OracleDataReader
publicOracleDataReaderReturnDataReader(Stringsql)
{
OpenConn();
OracleCommandcommand=newOracleCommand(sql,Connection);
returncommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
#endregion
#region执行SQL语句，返回记录总数数
///执行SQL语句，返回记录总数数
///sql语句
///返回记录总条数
publicintGetRecordCount(stringsql)
{
intrecordCount=0;
OpenConn();
OracleCommandcommand=newOracleCommand(sql,Connection);
OracleDataReaderdataReader=command.ExecuteReader();
while(dataReader.Read())
{
recordCount++;
}
dataReader.Close();
//CloseConn();
returnrecordCount;
}
#endregion
#region取当前序列,条件为seq.nextval或seq.currval
///
///取当前序列
publicdecimalGetSeq(stringseqstr)
{
decimalseqnum=0;
stringsql="select"+seqstr+"fromdual";
OpenConn();
OracleCommandcommand=newOracleCommand(sql,Connection);
OracleDataReaderdataReader=command.ExecuteReader();
if(dataReader.Read())
{
seqnum=decimal.Parse(dataReader[0].ToString());
}
dataReader.Close();
//CloseConn();
returnseqnum;
}
#endregion
#region执行SQL语句,返回所影响的行数
///执行SQL语句,返回所影响的行数
publicintExecuteSQL(stringsql)
{
intCmd=0;
OpenConn();
OracleCommandcommand=newOracleCommand(sql,Connection);
try
{
Cmd=command.ExecuteNonQuery();
}
catch
{
}
finally
{
//CloseConn();
}
returnCmd;
}
#endregion
//＝＝用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"
#region根据表名及哈稀表自动插入数据库用法：Insert("test",ht)
publicintInsert(stringTableName,Hashtableht)
{
OracleParameter[]Parms=newOracleParameter[ht.Count];
IDictionaryEnumeratoret=ht.GetEnumerator();
DataTabledt=GetTabType(TableName);
System.Data.OracleClient.OracleTypeotype;
intsize=0;
inti=0;
while(et.MoveNext())//作哈希表循环
{
GetoType(et.Key.ToString().ToUpper(),dt,outotype,outsize);
System.Data.OracleClient.OracleParameterop=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
Parms[i]=op;//添加SqlParameter对象
i=i+1;
}
stringstr_Sql=GetInsertSqlbyHt(TableName,ht);//获得插入sql语句
intval=ExecuteNonQuery(str_Sql,Parms);
returnval;
}
#endregion
#region根据相关条件对数据库进行更新操作用法：Update("test","Id=:Id",ht);
publicintUpdate(stringTableName,stringht_Where,Hashtableht)
{
OracleParameter[]Parms=newOracleParameter[ht.Count];
IDictionaryEnumeratoret=ht.GetEnumerator();
DataTabledt=GetTabType(TableName);
System.Data.OracleClient.OracleTypeotype;
intsize=0;
inti=0;
//作哈希表循环
while(et.MoveNext())
{
GetoType(et.Key.ToString().ToUpper(),dt,outotype,outsize);
System.Data.OracleClient.OracleParameterop=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
Parms[i]=op;//添加SqlParameter对象
i=i+1;
}
stringstr_Sql=GetUpdateSqlbyHt(TableName,ht_Where,ht);//获得插入sql语句
intval=ExecuteNonQuery(str_Sql,Parms);
returnval;
}
#endregion
#regiondel操作,注意此处条件个数与hash里参数个数应该一致用法：Del("test","Id=:Id",ht)
publicintDel(stringTableName,stringht_Where,Hashtableht)
{
OracleParameter[]Parms=newOracleParameter[ht.Count];
IDictionaryEnumeratoret=ht.GetEnumerator();
DataTabledt=GetTabType(TableName);
System.Data.OracleClient.OracleTypeotype;
inti=0;
intsize=0;
//作哈希表循环
while(et.MoveNext())
{
GetoType(et.Key.ToString().ToUpper(),dt,outotype,outsize);
System.Data.OracleClient.OracleParameterop=MakeParam(":"+et.Key.ToString(),et.Value.ToString());
Parms[i]=op;//添加SqlParameter对象
i=i+1;
}
stringstr_Sql=GetDelSqlbyHt(TableName,ht_Where,ht);//获得删除sql语句
intval=ExecuteNonQuery(str_Sql,Parms);
returnval;
}
#endregion
//＝＝＝＝＝＝＝＝上面三个操作的内部调用函数＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
#region根据哈稀表及表名自动生成相应insert语句(参数类型的)
///根据哈稀表及表名自动生成相应insert语句
///要插入的表名
///哈稀表
///返回sql语句
publicstaticstringGetInsertSqlbyHt(stringTableName,Hashtableht)
{
stringstr_Sql="";
inti=0;
intht_Count=ht.Count;//哈希表个数
IDictionaryEnumeratormyEnumerator=ht.GetEnumerator();
stringbefore="";
stringbehide="";
while(myEnumerator.MoveNext())
{
if(i==0)
{
before="("+myEnumerator.Key;
}
elseif(i+1==ht_Count)
{
before=before+","+myEnumerator.Key+")";
}
else
{
before=before+","+myEnumerator.Key;
}
i=i+1;
}
behide="Values"+before.Replace(",",",:").Replace("(","(:");
str_Sql="Insertinto"+TableName+before+behide;
returnstr_Sql;
}
#endregion
#region根据表名，where条件，哈稀表自动生成更新语句(参数类型的)
publicstaticstringGetUpdateSqlbyHt(stringTable,stringht_Where,Hashtableht)
{
stringstr_Sql="";
inti=0;
intht_Count=ht.Count;//哈希表个数
IDictionaryEnumeratormyEnumerator=ht.GetEnumerator();
while(myEnumerator.MoveNext())
{
if(i==0)
{
if(ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)
{
str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
else
{
if(ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+"").ToLower())==-1)
{
str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
i=i+1;
}
if(ht_Where==null||ht_Where.Replace("","")=="")//更新时候没有条件
{
str_Sql="update"+Table+"set"+str_Sql;
}
else
{
str_Sql="update"+Table+"set"+str_Sql+"where"+ht_Where;
}
str_Sql=str_Sql.Replace("set,","set").Replace("update,","update");
returnstr_Sql;
}
#endregion
#region根据表名，where条件，哈稀表自动生成del语句(参数类型的)
publicstaticstringGetDelSqlbyHt(stringTable,stringht_Where,Hashtableht)
{
stringstr_Sql="";
inti=0;
intht_Count=ht.Count;//哈希表个数
IDictionaryEnumeratormyEnumerator=ht.GetEnumerator();
while(myEnumerator.MoveNext())
{
if(i==0)
{
if(ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)
{
str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
else
{
if(ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+"").ToLower())==-1)
{
str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
i=i+1;
}
if(ht_Where==null||ht_Where.Replace("","")=="")//更新时候没有条件
{
str_Sql="Delete"+Table;
}
else
{
str_Sql="Delete"+Table+"where"+ht_Where;
}
returnstr_Sql;
}
#endregion
#region生成oracle参数
///
///生成oracle参数
///字段名
///数据类型
///数据大小
///值
publicstaticOracleParameterMakeParam(stringParamName,System.Data.OracleClient.OracleTypeotype,intsize,ObjectValue)
{
OracleParameterpara=newOracleParameter(ParamName,Value);
para.OracleType=otype;
para.Size=size;
returnpara;
}
#endregion
#region生成oracle参数
publicstaticOracleParameterMakeParam(stringParamName,stringValue)
{
returnnewOracleParameter(ParamName,Value);
}
#endregion
#region根据表结构字段的类型和长度拼装oraclesql语句参数
publicstaticvoidGetoType(stringkey,DataTabledt,outSystem.Data.OracleClient.OracleTypeotype,outintsize)
{
DataViewdv=dt.DefaultView;
dv.RowFilter="column_name='"+key+"'";
stringfType=dv[0]["data_type"].ToString().ToUpper();
switch(fType)
{
case"DATE":
otype=OracleType.DateTime;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case"CHAR":
otype=OracleType.Char;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case"LONG":
otype=OracleType.Double;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case"NVARCHAR2":
otype=OracleType.NVarChar;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case"VARCHAR2":
otype=OracleType.NVarChar;
size=int.Parse(dv[0]["data_length"].ToString());
break;
default:
otype=OracleType.NVarChar;
size=100;
break;
}
}
#endregion
#region动态取表里字段的类型和长度,此处没有动态用到connstr,是默认的！by/文少
publicSystem.Data.DataTableGetTabType(stringtabnale)
{
stringsql="selectcolumn_name,data_type,data_lengthfromall_tab_columnswheretable_name='"+tabnale.ToUpper()+"'";
OpenConn();
return(ReturnDataSet(sql,"dv")).Tables[0];
}
#endregion
#region执行sql语句
publicintExecuteNonQuery(stringcmdText,paramsOracleParameter[]cmdParms)
{
OracleCommandcmd=newOracleCommand();
OpenConn();
cmd.Connection=Connection;
cmd.CommandText=cmdText;
if(cmdParms!=null)
{
foreach(OracleParameterparmincmdParms)
cmd.Parameters.Add(parm);
}
intval=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//conn.CloseConn();
returnval;
}
#endregion
}
}
使用时可将上述代码保存成oracle_dbconn.cs文件，再进行调用。