• Oracle数据库Helper类


    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.Linq;
    using System.Web;

    namespace CRM.RCMDLL
    {
    public class OraclHelper
    {

    public OleDbTransaction MyTransaction;
    public OleDbConnection MyConnection;
    public OleDbCommand MyCommand;
    //数据库连接关键字
    public readonly string strOledbCon;
    public OraclHelper()
    {
    //public static readonly string connString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
    //System.Configuration.ConfigurationManager.AppSettings["EmailFrom"]
    strOledbCon = System.Configuration.ConfigurationManager.AppSettings["DBConnectionString"];
    MyConnection = new OleDbConnection(strOledbCon);
    }
    public OraclHelper(string as_ConnectString) {
    strOledbCon = System.Configuration.ConfigurationManager.AppSettings[as_ConnectString];
    MyConnection = new OleDbConnection(strOledbCon);
    }

    /// <summary>
    /// 读取数据表
    /// </summary>
    /// <param name="as_sqlstr">sql语句</param>
    /// <returns></returns>
    public OleDbDataReader GetDR(string as_sqlstr)
    {
    as_sqlstr = ChangeSQLStatement(as_sqlstr);

    MyCommand = new OleDbCommand(as_sqlstr, MyConnection);

    MyCommand.CommandTimeout = 600;

    MyConnection.Open();
    OleDbDataReader dr = MyCommand.ExecuteReader();
    return dr;
    }

    /// <summary>
    /// 获取数据视图
    /// </summary>
    /// <param name="as_sqlstr"></param>
    /// <returns></returns>
    public DataView GetDV(string as_sqlstr)
    {
    as_sqlstr = ChangeSQLStatement(as_sqlstr);

    OleDbDataAdapter MyDA = new OleDbDataAdapter(as_sqlstr, MyConnection);

    MyDA.SelectCommand.CommandTimeout = 600;

    DataSet ds = new DataSet();
    MyDA.Fill(ds, "Result");
    DataView dv = ds.Tables["Result"].DefaultView;

    MyDA.SelectCommand.Connection.Close();

    return dv;
    }

    /// <summary>
    /// 获取数据表
    /// </summary>
    /// <param name="as_sqlstr"></param>
    /// <returns></returns>
    public DataTable GetDT(string as_sqlstr)
    {
    as_sqlstr = ChangeSQLStatement(as_sqlstr);

    OleDbDataAdapter MyDA = new OleDbDataAdapter(as_sqlstr, MyConnection);

    MyDA.SelectCommand.CommandTimeout = 600;

    DataSet ds = new DataSet();
    MyDA.Fill(ds, "Result");
    DataTable dv = ds.Tables["Result"];

    MyDA.SelectCommand.Connection.Close();

    return dv;
    }

    /// <summary>
    /// 获取数据表集
    /// </summary>
    /// <param name="as_sqlstr"></param>
    /// <returns></returns>
    public DataSet GetDT(string as_sqlstr)
    {
    as_sqlstr = ChangeSQLStatement(as_sqlstr);

    OleDbDataAdapter MyDA = new OleDbDataAdapter(as_sqlstr, MyConnection);

    MyDA.SelectCommand.CommandTimeout = 600;

    DataSet ds = new DataSet();
    MyDA.Fill(ds, "Result");

    MyDA.SelectCommand.Connection.Close();
    return ds;
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="as_StoreProcedureName">存储过程名称</param>
    /// <param name="param">参数</param>
    /// <returns></returns>
    public bool ExecStoreProcedure(string as_StoreProcedureName, ref OleDbParameter[] param)
    {
    bool lbl_rtn;

    try
    {
    MyCommand = new OleDbCommand(as_StoreProcedureName, MyConnection);
    MyCommand.CommandType = CommandType.StoredProcedure;

    if (param != null)
    {
    for (int i = 0; i < param.Length; i++)
    {
    MyCommand.Parameters.Add(param[i]);
    }
    }

    MyConnection.Open();

    MyCommand.ExecuteNonQuery();

    MyConnection.Close();

    lbl_rtn = true;

    }
    catch (Exception ex)
    {
    MyConnection.Close();
    lbl_rtn = false;
    }

    return lbl_rtn;
    }


    /// <summary>
    /// 获得存储过程返回结果集
    /// </summary>
    /// <param name="as_StoreProcedureName">存储过程名称</param>
    /// <param name="inParam">参数</param>
    /// <returns></returns>
    public DataTable GetDTFromStoreProcedure(string as_StoreProcedureName, OleDbParameter[] inParam)
    {
    DataTable dt = new DataTable();

    try
    {
    MyCommand = new OleDbCommand(as_StoreProcedureName, MyConnection);
    MyCommand.CommandType = CommandType.StoredProcedure;

    if (inParam != null)
    {
    for (int i = 0; i < inParam.Length; i++)
    {
    MyCommand.Parameters.Add(inParam[i]);
    }
    }


    OleDbDataAdapter MyDA = new OleDbDataAdapter(MyCommand);
    MyDA.Fill(dt);
    }
    catch (Exception)
    {

    }

    return dt;
    }

    /// <summary>
    /// 事务
    /// </summary>
    /// <param name="as_sqlstr"></param>
    /// <param name="as_ErrMsg"></param>
    /// <returns></returns>
    public bool UpdateData(string as_sqlstr, ref string as_ErrMsg)
    {

    bool lbl_rtn;

    as_ErrMsg = "";

    as_sqlstr = ChangeSQLStatement(as_sqlstr);

    MyCommand = new OleDbCommand();
    MyCommand.Connection = MyConnection;
    MyCommand.Transaction = MyTransaction;

    try
    {
    MyCommand.CommandText = as_sqlstr;
    MyCommand.ExecuteNonQuery();
    CommitData();
    lbl_rtn = true;
    }
    catch (Exception ex)
    {
    RollbackData();
    lbl_rtn = false;
    as_ErrMsg = ex.Message;

    }
    finally
    {
    MyCommand.Dispose();
    }

    return lbl_rtn;
    }

    /// <summary>
    /// 关闭连接
    /// </summary>
    /// <param name="dr"></param
    public void CloseDR(OleDbDataReader dr)
    {
    dr.Close();
    MyConnection.Close();
    }

    public void OpenConnection()
    {
    MyConnection.Open();
    }

    public void StartTransaction()
    {
    MyTransaction = MyConnection.BeginTransaction(IsolationLevel.ReadCommitted);
    }

    /// <summary>
    /// 事务提交
    /// </summary>
    public void CommitData()
    {
    MyTransaction.Commit();
    MyConnection.Close();
    }

    /// <summary>
    /// 回滚数据
    /// </summary>
    public void RollbackData()
    {
    MyTransaction.Rollback();
    MyConnection.Close();
    }
    /// <summary>
    /// 字符替换
    /// </summary>
    /// <param name="as_sqlstr"></param>
    /// <returns></returns>
    public string ChangeSQLStatement(string as_sqlstr)
    {
    string ls_newSqlStr;

    ls_newSqlStr = as_sqlstr;

    if (strOledbCon.ToUpper().IndexOf("SQLOLEDB") >= 0)
    {
    ls_newSqlStr = ls_newSqlStr.Replace("sysdate", "getdate()");
    ls_newSqlStr = ls_newSqlStr.Replace("nvl(", "isnull(");
    ls_newSqlStr = ls_newSqlStr.Replace("to_char(", "convert(varchar,");
    ls_newSqlStr = ls_newSqlStr.Replace("to_date(", "convert(date,");
    ls_newSqlStr = ls_newSqlStr.Replace("to_number(", "convert(numeric,");
    ls_newSqlStr = ls_newSqlStr.Replace("substr(", "substring(");
    ls_newSqlStr = ls_newSqlStr.Replace("||", "+");
    ls_newSqlStr = ls_newSqlStr.Replace("length(", "len(");
    ls_newSqlStr = ls_newSqlStr.Replace("rownum", "row_number() over(order by getdate())");
    ls_newSqlStr = ls_newSqlStr.Replace(", 'yyyy-MM-dd'", "");
    ls_newSqlStr = ls_newSqlStr.Replace(", 'yyyy-mm-dd'", "");
    ls_newSqlStr = ls_newSqlStr.Replace(",'yyyy-MM-dd'", "");
    ls_newSqlStr = ls_newSqlStr.Replace(",'yyyy-mm-dd'", "");
    ls_newSqlStr = ls_newSqlStr.Replace("'HH24:mm:SS'", "108");
    ls_newSqlStr = ls_newSqlStr.Replace("lineno,", ""lineno",");
    ls_newSqlStr = ls_newSqlStr.Replace("lineno=", ""lineno"=");
    ls_newSqlStr = ls_newSqlStr.Replace(" lineno ", " "lineno" ");
    }


    return ls_newSqlStr;
    }
    /// <summary>
    /// 获得用户名
    /// </summary>
    /// <param name="as_staffcode"></param>
    /// <returns></returns>

    public string GetStaffName(string vip_id)
    {
    string ls_StaffName = "";

    OleDbDataReader dr = GetDR("Select staffname from staff where staffcode = '" + vip_id + "'");

    if (dr.Read())
    {
    ls_StaffName = dr[0].ToString();

    CloseDR(dr);
    return ls_StaffName;

    }
    else
    {
    CloseDR(dr);
    return ls_StaffName;
    }
    }
    }
    }

  • 相关阅读:
    ArcGIS 9.4的技术展望 (转)
    逻辑斯蒂方程的应用
    Logistic混沌映射(转)
    投入产出分析的应用和编制方法
    在matlab中实现逻辑斯蒂映射
    国内的城市GIS面临的机遇和挑战(转载)
    arcgis中曼哈顿距离、路网距离、near工具、Point Distance工具的使用
    apache no services installed 安装apache后没有服务
    LoadRunner录制脚本问题收集
    16进制枚举值 转换为枚举类型
  • 原文地址:https://www.cnblogs.com/liwp/p/6650220.html
Copyright © 2020-2023  润新知