c#实现的操作oracle通用类
修改整理的一个通用类,用来操作oracle数据库十分的方便,支持直接操作sql语句和Hash表操作.现在修补MIS我都用这个类,节约了大量的代码与工作良!在老孙的指点下,偶将操作oracle,sqlserver,access三种数据库的通用类集成在一起写了个数据抽象工厂,同时支持三种数据库无缝切换...以后整理出来.
usingSystem;
usingSystem.Data;
usingSystem.Data.OracleClient;
usingSystem.Collections;
usingSystem.Reflection;
namespaceMyOraComm
{
///<summary>
///ConnDbForOracle的摘要说明。
///</summary>
publicclassConnForOracle
{
protectedOracleConnectionConnection;
privatestringconnectionString;
publicConnForOracle()
{
stringconnStr;
connStr=System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();
connectionString=connStr;
Connection=newOracleConnection(connectionString);
}
#region带参数的构造函数
///<summary>
///带参数的构造函数
///</summary>
///<paramname="ConnString">数据库联接字符串</param>
publicConnForOracle(stringConnString)
{
stringconnStr;
connStr=System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();
Connection=newOracleConnection(connStr);
}
#endregion
#region打开数据库
///<summary>
///打开数据库
///</summary>
publicvoidOpenConn()
{
if(this.Connection.State!=ConnectionState.Open)
this.Connection.Open();
}
#endregion
#region关闭数据库联接
///<summary>
///关闭数据库联接
///</summary>
publicvoidCloseConn()
{
if(Connection.State==ConnectionState.Open)
Connection.Close();
}
#endregion
#region执行SQL语句，返回数据到DataSet中
///<summary>
///执行SQL语句，返回数据到DataSet中
///</summary>
///<paramname="sql">sql语句</param>
///<paramname="DataSetName">自定义返回的DataSet表名</param>
///<returns>返回DataSet</returns>
publicDataSetReturnDataSet(stringsql,stringDataSetName)
{
DataSetdataSet=newDataSet();
OpenConn();
OracleDataAdapterOraDA=newOracleDataAdapter(sql,Connection);
OraDA.Fill(dataSet,DataSetName);
//CloseConn();
returndataSet;
}
#endregion
#region执行Sql语句,返回带分页功能的dataset
///<summary>
///执行Sql语句,返回带分页功能的dataset
///</summary>
///<paramname="sql">Sql语句</param>
///<paramname="PageSize">每页显示记录数</param>
///<paramname="CurrPageIndex"><当前页/param>
///<paramname="DataSetName">返回dataset表名</param>
///<returns>返回DataSet</returns>
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()打开,然后才能读到数据
///<summary>
///执行SQL语句，返回DataReader,用之前一定要先.read()打开,然后才能读到数据
///</summary>
///<paramname="sql">sql语句</param>
///<returns>返回一个OracleDataReader</returns>
publicOracleDataReaderReturnDataReader(Stringsql)
{
OpenConn();
OracleCommandcommand=newOracleCommand(sql,Connection);
returncommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
#endregion
#region执行SQL语句，返回记录总数数
///<summary>
///执行SQL语句，返回记录总数数
///</summary>
///<paramname="sql">sql语句</param>
///<returns>返回记录总条数</returns>
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
///<summary>
///取当前序列
///</summary>
///<paramname="seqstr"></param>
///<paramname="table"></param>
///<returns></returns>
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语句,返回所影响的行数
///<summary>
///执行SQL语句,返回所影响的行数
///</summary>
///<paramname="sql"></param>
///<returns></returns>
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语句(参数类型的)
///<summary>
///根据哈稀表及表名自动生成相应insert语句
///</summary>
///<paramname="TableName">要插入的表名</param>
///<paramname="ht">哈稀表</param>
///<returns>返回sql语句</returns>
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参数
///<summary>
///生成oracle参数
///</summary>
///<paramname="ParamName">字段名</param>
///<paramname="otype">数据类型</param>
///<paramname="size">数据大小</param>
///<paramname="Value">值</param>
///<returns></returns>
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
//＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
//＝＝＝＝＝＝＝＝＝内部调用函数完＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
//＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
}
}