c 连接oracle 通用类,c#连接oracle 的一个通用类（转）

#regionSql分页函数//构造分页查询SQL语句//主键///所有需要查询的字段(field1,field2...)///库名.拥有者.表名///查询条件1(where ...)///排序条件2(order by ...)///当前页号///页宽///SQL语句publicstringJoinPageSQL(stringKeyField,stringFieldStr,stringTableName,stringWhere,stringOrder,intCurrentPage,intPageSize)

{stringsql=null;if(CurrentPage==1)

{

sql="select"+CurrentPage*PageSize+""+FieldStr+"from"+TableName+""+Where+""+Order+"";

}else{

sql="select * from (";

sql+="select"+CurrentPage*PageSize+""+FieldStr+"from"+TableName+""+Where+""+Order+") a";

sql+="where"+KeyField+"not in (";

sql+="select"+(CurrentPage-1)*PageSize+""+KeyField+"from"+TableName+""+Where+""+Order+")";

}returnsql;

}//构造分页查询SQL语句//字段名(非主键)///库名.拥有者.表名///查询条件1(where ...)///排序条件2(order by ...)///当前页号///页宽///SQL语句publicstringJoinPageSQL(stringField,stringTableName,stringWhere,stringOrder,intCurrentPage,intPageSize)

{stringsql=null;if(CurrentPage==1)

{

sql="select rownum"+CurrentPage*PageSize+""+Field+"from"+TableName+""+Where+""+Order+"group by"+Field;

}else{

sql="select * from (";

sql+="select rownum"+CurrentPage*PageSize+""+Field+"from"+TableName+""+Where+""+Order+"group by"+Field+") a";

sql+="where"+Field+"not in (";

sql+="select rownum"+(CurrentPage-1)*PageSize+""+Field+"from"+TableName+""+Where+""+Order+"group by"+Field+")";

}returnsql;

}#endregion#region根据系统时间动态生成各种查询语句(现已经注释掉，以备以后使用)//#region 根据查询时间的条件，动态生成查询语句// 根据查询时间的条件，动态生成查询语句// 开始时间/ 结束时间/ 单位/ 开始线损率/ 结束线损率/ 单位字段/ 线损率字段/ 表后缀/ SQL语句//public string SQL(DateTime starttime,DateTime endtime,string dw,float startxsl,float endxsl,string danwei,string xiansunlv,string tablehz)//{string sql=null;将输入的时间格式转换成固定的格式"yyyy-mm-dd"//string zstarttime=starttime.GetDateTimeFormats('D')[1].ToString();//string zendtime=endtime.GetDateTimeFormats('D')[1].ToString();//string nTime=DateTime.Now.GetDateTimeFormats('D')[1].ToString();取日期值的前六位，及年月值//string sTime=zstarttime.Substring(0,4)+zstarttime.Substring(5,2);//string eTime=zendtime.Substring(0,4)+zendtime.Substring(5,2);//string nowTime=nTime.Substring(0,4)+nTime.Substring(5,2);分别取日期的年和月//int sy=Convert.ToInt32(zstarttime.Substring(0,4));//int ey=Convert.ToInt32(zendtime.Substring(0,4));//int sm=Convert.ToInt32(zstarttime.Substring(5,2));//int em=Convert.ToInt32(zendtime.Substring(5,2));相关变量定义//int s;//int e;//int i;//int j;//int js;//int nz;//string x;一，取当前表生成SQL语句//if(sTime==nowTime&&eTime==nowTime)//{//sql="select * from "+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" ";//}二，取当前表和其他表生成SQL语句//else if(sTime==nowTime&&eTime!=nowTime)//{//sql="select * from "+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";如果年份相等//if(sy==ey)//{//s=Convert.ToInt32(sTime);//e=Convert.ToInt32(eTime);//for(i=s+1;i//{//i=i++;//sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";//}//sql+="select * from "+e.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" ";//}结束年份大于开始年份//else//{1，先循环到起始时间和起始时间的12月//s=Convert.ToInt32(sTime);//x=zstarttime.Substring(0,4)+"12";//nz=Convert.ToInt32(x);//for(i=s+1;i<=nz;i++)//{//i=i++;//sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";//}2，循环两者相差年份//for(i=sy+1;i//{for(j=1;j<=12;j++)//{//if(j<10)//{//sql+="select * from "+i.ToString()+"0"+j.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";//}//else//{//sql+="select * from "+i.ToString()+j.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";//}//}//}3，循环到结束的月份//js=Convert.ToInt32(zendtime.Substring(0,4)+"00");//for(i=js;i//{//i++;//sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";}//sql+="select * from "+eTime.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" ";}//}三，取其他表生成生成SQL语句//else//{1，先循环到起始时间和起始时间的12月//s=Convert.ToInt32(sTime);//x=zstarttime.Substring(0,4)+"12";//nz=Convert.ToInt32(x);//for(i=s;i<=nz;i++)//{//i=i++;//sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";//}2，循环两者相差年份//for(i=sy+1;i//{for(j=1;j<=12;j++)//{//if(j<10)//{//sql+="select * from "+i.ToString()+"0"+j.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";//}//else//{//sql+="select * from "+i.ToString()+j.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";//}//}//}3，循环到结束的月份//js=Convert.ToInt32(zendtime.Substring(0,4)+"00");//for(i=js;i//{//i++;//sql+="select * from "+i.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" "+"union"+" ";}//sql+="select * from "+eTime.ToString()+'_'+tablehz+" "+"where"+" "+danwei+"="+dw+" "+" "+"and"+" "+xiansunlv+">="+startxsl+" "+"and"+" "+xiansunlv+"<="+endxsl+" ";}//return sql;//}//#endregion