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;
}
}
}
}