using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using System.Data.SqlClient;
|
using System.Data;
|
|
namespace Pub_Class
|
{
|
public class ClsSqlHelper:IDisposable
|
{
|
private SqlConnection MainCn;//创建连接对象
|
SqlTransaction MainTran;
|
public string sServer;
|
public string sDataBase ;
|
public string sUser;
|
public string sPassword;
|
//开始事务
|
public void BeginTran()
|
{
|
if (!this.CnOpen())
|
return;
|
MainTran=MainCn.BeginTransaction();
|
}
|
//结束事务
|
public void Commit()
|
{
|
MainTran.Commit();
|
CnClose();
|
}
|
//回滚事务
|
public void RollBack()
|
{
|
MainTran.Rollback();
|
CnClose();
|
}
|
|
public ClsSqlHelper()
|
{
|
sServer = ClsIni.ReadIni("Erp", "Server", ClsPub.AppPath + @"\"+"HXErp.ini");
|
sDataBase = ClsIni.ReadIni("Erp", "DataBase", ClsPub.AppPath + @"\" + "HXErp.ini");
|
sUser = ClsIni.ReadIni("Erp", "UserName", ClsPub.AppPath + @"\" + "HXErp.ini");
|
sPassword = ClsIni.ReadIni("Erp", "PassWord", ClsPub.AppPath + @"\" + "HXErp.ini");
|
|
}
|
|
public bool CheckOpen()
|
{
|
if (sServer == "" || sServer == "没有找到！")
|
{
|
throw new Exception("错误的服务器名!");
|
}
|
if (sDataBase == "" || sDataBase == "没有找到！")
|
{
|
throw new Exception("错误的数据库名!");
|
}
|
SqlConnection oCn = new SqlConnection("Data Source=" + sServer + ";DataBase=master;User ID=" + sUser + ";PWD=" + sPassword + " ");
|
try
|
{
|
oCn.Open();
|
return true;
|
}
|
catch (Exception e)
|
{
|
//e.Message = e.Message + "," + sServer + "," + sDataBase;
|
//e.
|
//throw (e);
|
throw new Exception(e.Message + "," + sServer + "," + sDataBase);
|
}
|
}
|
|
private bool CnOpen()//OPEN数据库连接
|
{
|
if (sServer == "" || sServer == "没有找到！")
|
{
|
throw new Exception("错误的服务器名!");
|
}
|
if (sDataBase == "" || sDataBase == "没有找到！")
|
{
|
throw new Exception("错误的数据库名!");
|
}
|
if (MainCn==null)
|
{
|
MainCn = new SqlConnection("Data Source=" + sServer + ";DataBase=" + sDataBase + ";User ID=" + sUser + ";PWD=" + sPassword + " ");
|
}
|
if (MainCn.State == System.Data.ConnectionState.Closed)
|
{
|
try
|
{
|
MainCn.Open();
|
return true;
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message + "," + sServer + "," + sDataBase);
|
}
|
}
|
else
|
return true;
|
}
|
private void CnClose()//关闭数据库连接
|
{
|
if (MainCn != null)
|
MainCn.Close();
|
}
|
private void CnDispose()//释放资源
|
{
|
if(MainCn!=null)
|
{
|
MainCn.Dispose();
|
MainCn=null;
|
}
|
}
|
/// <summary>
|
/// 转换参数
|
/// </summary>
|
/// <param name="ParamName">存储过程名称或命令文本</param>
|
/// <param name="DbType">参数类型</param></param>
|
/// <param name="Size">参数大小</param>
|
/// <param name="Value">参数值</param>
|
/// <returns>新的 parameter 对象</returns>
|
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
|
{
|
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
|
}
|
/// <summary>
|
/// 初始化参数值
|
/// </summary>
|
/// <param name="ParamName">存储过程名称或命令文本</param>
|
/// <param name="DbType">参数类型</param>
|
/// <param name="Size">参数大小</param>
|
/// <param name="Direction">参数方向</param>
|
/// <param name="Value">参数值</param>
|
/// <returns>新的 parameter 对象</returns>
|
private SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
|
{
|
SqlParameter param;
|
if (Size > 0)
|
param = new SqlParameter(ParamName, DbType, Size);
|
else
|
param = new SqlParameter(ParamName, DbType);
|
param.Direction = Direction;
|
if (!(Direction == ParameterDirection.Output && Value == null))
|
param.Value = Value;
|
return param;
|
}
|
/// <summary>
|
/// 执行SQL无返回值
|
/// </summary>
|
/// <param name="procName"></param>
|
/// <param name="sErrMessage"></param>
|
/// <returns></returns>
|
public void RunProc(string procName)
|
{
|
if (!this.CnOpen())
|
{
|
return;
|
//sErrMessage = "连接数据库失败！";
|
}
|
try
|
{
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.Transaction = MainTran;
|
cmd.ExecuteNonQuery();
|
return;
|
}
|
catch (Exception e)
|
{
|
|
throw (e);
|
return;
|
}
|
}
|
/// <summary>
|
/// 执行
|
/// </summary>
|
/// <param name="procName"></param>
|
/// <param name="sErrMessage"></param>
|
/// <returns></returns>
|
public void RunProc(string procName,ref string sErr)
|
{
|
if (!this.CnOpen())
|
{
|
sErr = "连接数据库失败！";
|
return;
|
|
}
|
try
|
{
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.Transaction = MainTran;
|
cmd.ExecuteNonQuery();
|
return;
|
}
|
catch (Exception e)
|
{
|
sErr =e.Message;
|
throw (e);
|
return;
|
}
|
}
|
|
/// <summary>
|
/// 递入参数执行SQL并返回DATASET
|
/// </summary>
|
/// <param name="procName">执行语句</param>
|
/// <param name="prams">参数</param>
|
/// <param name="tbName">表名</param>
|
/// <param name="sErrMessage">异常信息</param>
|
/// <returns></returns>
|
public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName )
|
{
|
SqlDataAdapter dap = CreateDataAdaper(procName, prams);
|
if (dap == null)
|
return null;
|
DataSet ds = new DataSet();
|
try
|
{
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
throw (e);
|
return null;
|
}
|
}
|
/// <summary>
|
/// 执行SQL并返回DATASET
|
/// </summary>
|
/// <param name="procName">执行语句</param>
|
/// <param name="tbName">表名</param>
|
/// <param name="sErrMessage">异常信息</param>
|
/// <returns></returns>
|
public DataSet RunProcReturn(string procName, string tbName )
|
{
|
SqlDataAdapter dap = CreateDataAdaper(procName, null);
|
DataSet ds = new DataSet();
|
try
|
{
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
throw (e);
|
return null;
|
}
|
}
|
/// <summary>
|
/// 执行SQL并返回DATASET和错误信息
|
/// </summary>
|
/// <param name="procName">执行语句</param>
|
/// <param name="tbName">表名</param>
|
/// <param name="sErrMessage">异常信息</param>
|
/// <returns></returns>
|
public DataSet RunProcReturn(string procName, string tbName,ref string sErr)
|
{
|
SqlDataAdapter dap = CreateDataAdaper(procName, null);
|
DataSet ds = new DataSet();
|
try
|
{
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
sErr = e.Message;
|
throw (e);
|
return null;
|
}
|
}
|
private SqlDataAdapter CreateDataAdaper(string procName, SqlParameter[] prams)
|
{
|
if (this.CnOpen())
|
{
|
SqlDataAdapter dap = new SqlDataAdapter(procName, MainCn);
|
dap.SelectCommand.CommandType = CommandType.Text;
|
dap.SelectCommand.CommandTimeout = 120;
|
dap.SelectCommand.Transaction = MainTran;
|
if (prams != null)
|
{
|
foreach (SqlParameter parameter in prams)
|
dap.SelectCommand.Parameters.Add(parameter);
|
}
|
dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
|
ParameterDirection.ReturnValue, false, 0, 0,
|
string.Empty, DataRowVersion.Default, null));
|
return dap;
|
}
|
else
|
{
|
return null;
|
}
|
}
|
|
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
|
{
|
if (this.CnOpen())
|
{
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.CommandType = CommandType.Text;
|
cmd.Transaction = MainTran;
|
if (prams != null)
|
{
|
foreach (SqlParameter parameter in prams)
|
cmd.Parameters.Add(parameter);
|
}
|
cmd.Parameters.Add(
|
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
|
ParameterDirection.ReturnValue, false, 0, 0,
|
string.Empty, DataRowVersion.Default, null));
|
return cmd;
|
}
|
else
|
return null;
|
}
|
|
#region IDisposable 成员
|
|
public void Dispose()
|
{
|
throw new Exception("The method or operation is not implemented.");
|
}
|
|
#endregion
|
}
|
}
|
