C#操作oracle数据库带参数SQL语句通用类
namespace com.jshtxx.YiYuan.DAC
{
///
/// 住院主表操作类
///
class MainGeneralDAC
{
///
/// SQL字符串拼接对象
///
private StringBuilder g_sb;
///
/// 日志
///
private ILog log;
///
/// SQL参数对象
///
private OracleParameter[] g_orlParams;
///
/// 构造方法
///
public MainGeneralDAC()
{
g_sb = new StringBuilder(); // SQL字符串拼接对象
g_orlParams = null; // SQL参数对象
log = null;
}
///
/// 查询住院主表方法
///
/// 开票日期或作废日期查询开始日期
/// 开票日期或作废日期查询结束日期日期
///
public List
{
List
try
{
g_sb = new StringBuilder(); // 实例化SQL语句
strBegin = strBegin + " 00:00:00";
strEnd = strEnd + " 23:59:59";
g_sb.Append("SELECT lsh, fpdm, fphm, jddm, jdhm, kprq, hydm, hymc, xh, fkfbh, fkfmc, jine, bz, "); // 追加SQL字符串
g_sb.Append("bizhong, huil, pjr, wbjine, khyh, yhzh, kprmc, skfmc, skfsbh, yzm, fpzt, zfrdm, zfrmc, ");
g_sb.Append("zfrq, yfpdm, yfphm, errorcode, errmsg, sfyhc,kprdm ");
g_sb.Append("FROM TBL_INV_MAIN_GENERAL WHERE kprq BETWEEN :kprq1 AND :kprq2 ");
g_sb.Append("OR zfrq BETWEEN :zfrq1 AND :zfrq2");
g_strSql = g_sb.ToString();
g_orlParams = new OracleParameter[4]; // 实例化SQL参数
g_orlParams[0] = new OracleParameter(":kprq1", OracleType.DateTime); // 添加参数
g_orlParams[0].Value = strBegin; // 参数赋值
g_orlParams[1] = new OracleParameter(":kprq2", OracleType.DateTime);
g_orlParams[1].Value = strEnd;
g_orlParams[2] = new OracleParameter(":zfrq1", OracleType.DateTime);
g_orlParams[2].Value = strBegin;
g_orlParams[3] = new OracleParameter(":zfrq2", OracleType.DateTime);
g_orlParams[3].Value = strEnd;
OracleDataReader dr = ConnectionHelper.GetDataReader(g_strSql,g_orlParams); // 调用查询方法
// 将查询出的结果循环赋值给发票
列表
while (dr.Read())
{
lstMainGeneral.Add(LoadInvInfo(dr));
}
dr.Close();
return lstMainGeneral; // 返回发票列表信息
}
catch (Exception ex)
{
log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
log.Debug(ex.Message);
throw ex;
}
}
}
///
/// oracle数据库操作类
///
public class ConnectionHelper
{
public static System.Data.OracleClient.OracleConnection cnn;
public ConnectionHelper()
{
cnn = null;
}
///
/// 打开数据库连接
///
public static void Open()
{
cnn = new OracleConnection(Properties.Settings.Default.oracleConn);
cnn.Open();
}
///
/// 打开数据库连接，返回cnn
///
public static OracleConnection OpenCnn()
{
cnn = new OracleConnection(Properties.Settings.Default.oracleConn);
cnn.Open();
return (cnn);
}
///
/// 关闭数据库连接
///
public static void Close()
{
cnn.Close();
}
///
/// 返回DataSet
///
/// sql语句
/// 表名
/// sql查询参数
///
public static DataSet GetDataSet(string CmdString, string TableName, params OracleParameter[] orlpara)
{
Open();
OracleDataAdapter myDa = new OracleDataAdapter();
myDa.SelectCommand = GetCommand(CmdString, orlpara);
//myDa.SelectCommand = new OracleCommand(CmdString, cnn);
DataSet myDs = new DataSet();
myDa.Fill(myDs, TableName);
return myDs;
}
///
/// 返回OleDbDataReader
///
/// sql语句
/// sql查询参数
///
public static OracleDataReader GetDataReader(string CmdString, params OracleParameter[] orlpara)
{
Open();
//OracleCommand myCmd = new OracleCommand(CmdString, cnn);
OracleCommand myCmd = GetCommand(CmdString, orlpara);
OracleDataReader myDr = myCmd.ExecuteReader();
return myDr;
}
///
/// 返回单一查询值
///
/// sql语句
/// sql查询参数
///
public static object GetExecuteScalar(string CmdString, params OracleParameter[] orlpara)
{
Open();
//OracleCommand myCmd = new OracleCommand(CmdString, cnn);
OracleCommand myCmd = GetCommand(CmdString, orlpara);
object result = myCmd.ExecuteOracleScalar();
return result;
}
///
/// 返回影响数据库的行数
///
/// sql语句
/// sql查询参数
///
public static int ExecuteSQL(string CmdString, params OracleParameter[] orlpara)
{
Open();
//OracleCommand myCmd = new OracleCommand(CmdString, cnn);
OracleCommand myCmd = GetCommand(CmdString, orlpara);
int Cmd = myCmd.ExecuteNonQuery();
return Cmd;
}
///
/// 封装OracleCommand
///
/// sql语句
/// sql查询参数
///
public static OracleCommand GetCommand(string strCmd, OracleParameter[] orlPara)
{
OracleCommand orlCammd = new OracleCommand(strCmd, cnn);
if (null != orlPara)
foreach (OracleParameter p in orlPara)
orlCammd.Parameters.Add(p);
return orlCammd;
}
}
}