using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using System.Data.SqlClient;
|
using System.Data;
|
using System.Windows.Forms;
|
|
namespace Pub_Class
|
{
|
public class ClsSqlHelper : IDisposable
|
{
|
private SqlConnection MainCn;//创建连接对象
|
public string strCon;
|
SqlTransaction MainTran;
|
public string sServer;
|
public string sDataBase;
|
public string sUser;
|
public string sPassword;
|
//public string sTextBox = "HXErp_Test.ini";
|
public string sTextBox = "HXErp.ini";
|
//开始事务
|
public void BeginTran()
|
{
|
if (!this.CnOpen())
|
return;
|
MainTran = MainCn.BeginTransaction();
|
}
|
//结束事务
|
public void Commit()
|
{
|
MainTran.Commit();
|
CnClose();
|
}
|
//回滚事务
|
public void RollBack()
|
{
|
MainTran.Rollback();
|
CnClose();
|
}
|
|
public ClsSqlHelper()
|
{
|
if (ClsPub.isStrNull(ClsPub.AppPath) == "")
|
{
|
ClsPub.AppPath = System.Environment.CurrentDirectory;
|
//ClsPub.AppPath = @"C:\Windows\System32";
|
}
|
sServer = ClsIni.ReadIni("Erp", "Server", ClsPub.AppPath + @"\" + sTextBox);
|
sDataBase = ClsIni.ReadIni("Erp", "DataBase", ClsPub.AppPath + @"\" + sTextBox);
|
sUser = ClsIni.ReadIni("Erp", "UserName", ClsPub.AppPath + @"\" + sTextBox);
|
sPassword = ClsIni.ReadIni("Erp", "PassWord", ClsPub.AppPath + @"\" + sTextBox);
|
//
|
}
|
|
public bool CheckOpen()
|
{
|
if (sServer == "" || sServer == "没有找到！")
|
{
|
throw new Exception("错误的服务器名!!" + System.Environment.CurrentDirectory);
|
}
|
if (sDataBase == "" || sDataBase == "没有找到！")
|
{
|
throw new Exception("错误的数据库名!!" + System.Environment.CurrentDirectory);
|
}
|
SqlConnection oCn = new SqlConnection("Data Source=" + sServer + ";DataBase=master;User ID=" + sUser + ";PWD=" + sPassword + ";max pool size=32767");
|
try
|
{
|
oCn.Open();
|
return true;
|
}
|
catch (Exception e)
|
{
|
//e.Message = e.Message + "," + sServer + "," + sDataBase;
|
//e.
|
//throw (e);
|
ClsPub.sErrInfo = e.Message + "," + sServer + "," + sDataBase;
|
throw new Exception(e.Message + "," + sServer + "," + sDataBase);
|
}
|
}
|
|
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)
|
{
|
ClsPub.sErrInfo = e.Message + "," + sServer + "," + sDataBase;
|
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>
|
/// 转换参数
|
/// </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
|
{
|
ClsPub.sSQLInfo = procName;
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.CommandTimeout = 600;
|
cmd.Transaction = MainTran;
|
cmd.ExecuteNonQuery();
|
return;
|
}
|
catch (Exception e)
|
{
|
|
ClsPub.sErrInfo = e.Message;
|
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;
|
}
|
}
|
|
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
|
{
|
if (this.CnOpen())
|
{
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.CommandType = CommandType.Text;
|
cmd.CommandTimeout = 600;
|
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;
|
}
|
|
public bool CreateCommand(string procName, byte[] FileList, ref string sErr)
|
{
|
try
|
{
|
if (this.CnOpen())
|
{
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.CommandTimeout = 600;
|
cmd.Parameters.AddWithValue("@FileList", FileList);
|
cmd.ExecuteNonQuery();
|
return true;
|
}
|
else
|
{
|
sErr = "连接失败！";
|
return false;
|
}
|
}
|
catch (Exception e)
|
{
|
sErr = e.Message;
|
return false;
|
}
|
|
}
|
|
public SqlDataReader RunReader(string sSQL, string tbName, ref string sErr)
|
{
|
if (!this.CnOpen())
|
{
|
sErr = "连接数据库失败！";
|
return null;
|
}
|
try
|
{
|
ClsPub.sSQLInfo = sSQL;
|
SqlCommand cmd = new SqlCommand(sSQL, MainCn);
|
cmd.CommandTimeout = 600;
|
//cmd.Transaction = MainTran;
|
return cmd.ExecuteReader();
|
}
|
catch (Exception e)
|
{
|
ClsPub.sErrInfo = e.Message;
|
sErr = e.Message;
|
return null;
|
}
|
}
|
|
|
/// <summary>
|
/// 批量插入数据库
|
/// </summary>
|
/// <param name="TableName">目标表</param>
|
/// <param name="dt">源数据</param>
|
public bool SqlBulkCopyByDatatable(string TableName, DataTable dt)
|
{
|
string connectionString = $"Data Source={sServer};DataBase={sDataBase};User ID={sUser};PWD={sPassword}";
|
using (SqlConnection conn = new SqlConnection(connectionString))
|
{
|
using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
|
{
|
try
|
{
|
sqlbulkcopy.DestinationTableName = TableName;
|
for (int i = 0; i < dt.Columns.Count; i++)
|
{
|
sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
|
}
|
sqlbulkcopy.WriteToServer(dt);
|
return true;
|
}
|
catch (System.Exception ex)
|
{
|
//ex.Message;
|
return false;
|
}
|
}
|
}
|
}
|
#region IDisposable 成员
|
|
public void Dispose()
|
{
|
throw new Exception("The method or operation is not implemented.");
|
}
|
|
#endregion
|
}
|
}
|
