using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace WFormSynchronizeData_SMR
|
{
|
|
public class DBHelper
|
{
|
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 DBHelper()
|
{
|
sServer = GetConfigKey(AppDomain.CurrentDomain.BaseDirectory + "Config/SQLAPI.config", "sServer"); //"10.11.18.195";
|
sDataBase = GetConfigKey(AppDomain.CurrentDomain.BaseDirectory + "Config/SQLAPI.config", "sDataBase"); //"HX_LMESsys_test";
|
sUser = GetConfigKey(AppDomain.CurrentDomain.BaseDirectory + "Config/SQLAPI.config", "sUser"); //"HX_USER";
|
sPassword = GetConfigKey(AppDomain.CurrentDomain.BaseDirectory + "Config/SQLAPI.config", "sPassword");// "lc@841022";
|
}
|
|
public static String GetConfigKey(String configPath, String key)
|
{
|
Configuration ConfigurationInstance = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap()
|
{
|
ExeConfigFilename = configPath
|
}, ConfigurationUserLevel.None);
|
|
|
if (ConfigurationInstance.AppSettings.Settings[key] != null)
|
return ConfigurationInstance.AppSettings.Settings[key].Value;
|
else
|
|
return string.Empty;
|
}
|
|
private bool CnOpen()//OPEN数据库连接
|
{
|
if (sServer == "" || sServer == "没有找到！")
|
{
|
throw new Exception("错误的服务器名!" + System.Environment.CurrentDirectory);
|
}
|
if (sDataBase == "" || sDataBase == "没有找到！")
|
{
|
throw new Exception("错误的数据库名!");
|
}
|
if (MainCn == null)
|
{
|
MainCn = new SqlConnection("Data Source=" + sServer + ";DataBase=" + sDataBase + ";User ID=" + sUser + ";PWD=" + sPassword + ";max pool size=32767");
|
}
|
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;
|
}
|
|
public void CnClose()//关闭数据库连接
|
{
|
if (MainCn != null)
|
MainCn.Close();
|
}
|
|
public void CnDispose()//释放资源
|
{
|
if (MainCn != null)
|
{
|
MainCn.Dispose();
|
MainCn = null;
|
}
|
}
|
|
/// <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.CommandTimeout = 600;
|
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
|
{
|
//ClsPub.sSQLInfo = procName;
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.CommandTimeout = 600;
|
cmd.Transaction = MainTran;
|
cmd.ExecuteNonQuery();
|
return;
|
}
|
catch (Exception e)
|
{
|
sErr = e.Message;
|
//ClsPub.sErrInfo = 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
|
{
|
//ClsPub.sSQLInfo = procName;
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
//ClsPub.sErrInfo = e.Message;
|
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
|
{
|
//ClsPub.sSQLInfo = procName;
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
//ClsPub.sErrInfo = e.Message;
|
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
|
{
|
//ClsPub.sSQLInfo = procName;
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
sErr = e.Message;
|
//ClsPub.sErrInfo = e.Message;
|
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 = 600;
|
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;
|
}
|
}
|
}
|
}
|
