using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace MQ.Helper
{
/// <summary>
/// SqlHelper 的摘要说明。
/// SqlServer数据库操作通用类。
/// </summary>
public class SqlHelper : DBHelper
{
#region 成员变量
/// <summary>
/// 数据库连接
/// </summary>
private SqlConnection conn;
/// <summary>
/// 事务处理
/// </summary>
private SqlTransaction trans;
/// <summary>
/// 指示当前是否正处于事务中
/// </summary>
private bool inTransaction = false;
#endregion
#region 初始化数据库连接
/// <summary>
/// 初始化数据库连接
/// </summary>
public SqlHelper(string strConn)
{
this.conn = new SqlConnection(strConn);
}
#endregion
#region 数据库连接
/// <summary>
/// 打开数据库连接
/// </summary>
private void Open()
{
if(conn.State != ConnectionState.Open)
this.conn.Open();
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private void Close()
{
if (conn.State == ConnectionState.Open)
this.conn.Close();
}
/// <summary>
/// 测试数据库连接
/// </summary>
/// <returns></returns>
public override bool test()
{
try
{
Open();
return true;
}
catch
{
return false;
}
}
#endregion
#region 事务
/// <summary>
/// 开始一个事务
/// </summary>
private void BeginTrans()
{
trans = conn.BeginTransaction() ;
inTransaction = true;
}
/// <summary>
/// 提交一个事务
/// </summary>
private void CommitTrans()
{
if (trans != null)
{
trans.Commit();
inTransaction = false;
}
}
/// <summary>
/// 回滚一个事务
/// </summary>
private void RollbackTrans()
{
if (trans != null)
{
trans.Rollback();
inTransaction = false;
}
}
#endregion
#region 执行非查询SQL语句或存储过程
/// <summary>
/// 执行无参数非查询SQL语句或存储过程
/// </summary>
/// <param name="strSql">SQL语句或存储过程名</param>
/// <param name="cmdType">命令字符串类型</param>
public override void exeSql(string strSql,CommandType cmdType)
{
try
{
SqlCommand cmd = PrepareCommand(true,strSql,cmdType,null);
cmd.ExecuteNonQuery();
CommitTrans();
}
catch (Exception exp)
{
RollbackTrans();
throw new Exception(exp.Message);
}
finally
{
Close();
}
}
/// <summary>
/// 执行带输入参数的非查询SQL语句或存储过程
/// </summary>
/// <param name="strSql">SQL语句或存储过程名</param>
/// <param name="cmdType">命令字符串类型</param>
/// <param name="paramsInput">输入参数集合</param>
public override void exeSql(string strSql,
CommandType cmdType,
CommonParameter[] paramsInput)
{
try
{
SqlCommand cmd = PrepareCommand(true,strSql,cmdType,paramsInput);
cmd.ExecuteNonQuery();
CommitTrans();
}
catch (Exception exp)
{
RollbackTrans();
throw new Exception(exp.Message);
}
finally
{
Close();
}
}
#endregion
#region 执行查询SQL语句或存储过程
/// <summary>
/// 执行无参数的查询SQL语句或存储过程
/// </summary>
/// <param name="strSql">SQL语句或存储过程名</param>
/// <param name="cmdType">命令字符串类型</param>
/// <returns>返回DataSet</returns>
public override DataSet exeSqlForDataSet(string strSql,CommandType cmdType)
{
try
{
SqlCommand cmd = PrepareCommand(false,strSql,cmdType,null);
DataSet ds = FillData(cmd);
if (cmdType.Equals(CommandType.StoredProcedure))
CommitTrans();
return ds;
}
catch (Exception exp)
{
string strErr = exp.Message;
RollbackTrans();
return null;
}
finally
{
Close();
}
}
/// <summary>
/// 执行无参数的填充指定DataTable的查询SQL语句或存储过程
/// </summary>
/// <param name="strSql">SQL语句或存储过程名</param>
/// <param name="cmdType">命令字符串类型</param>
/// <param name="ds">指定填充的DataSet</param>
/// <param name="strDataTableName">指定填充的DataTable名，如果为空字符串，则默认填充数据集的第一个表</param>
public override DataSet exeSqlForDataSet(string strSql, CommandType cmdType, DataSet ds, string strDataTableName)
{
try
{
SqlCommand cmd = PrepareCommand(false,strSql,cmdType,null);
ds = FillData(cmd,ds,strDataTableName);
if (cmdType.Equals(CommandType.StoredProcedure))
CommitTrans();
}
catch (Exception exp)
{
string strExp = exp.Message;
RollbackTrans();
ds = null;
}
finally
{
Close();
}
return ds;
}
/// <summary>
/// 执行带输入参数的查询SQL语句或存储过程
/// </summary>
/// <param name="strSql">SQL语句或存储过程名</param>
/// <param name="cmdType">命令字符串类型</param>
/// <param name="paramsInput">输入参数集合</param>
/// <returns>返回DataSet</returns>
public override DataSet exeSqlForDataSet(string strSql,
CommandType cmdType,
CommonParameter[] paramsInput)
{
try
{
SqlCommand cmd = PrepareCommand(false,strSql,cmdType,paramsInput);
DataSet ds = FillData(cmd);
if (cmdType.Equals(CommandType.StoredProcedure))
CommitTrans();
return ds;
}
catch (Exception exp)
{
string strErr = exp.Message;
RollbackTrans();
return null;
}
finally
{
Close();
}
}
/// <summary>
/// 执行带输入参数的填充指定DataTable的查询SQL语句或存储过程
/// </summary>
/// <param name="strSql">SQL语句或存储过程名</param>
/// <param name="cmdType">命令字符串类型</param>
/// <param name="paramsInput">输入参数集合</param>
/// <param name="ds">指定填充的DataSet</param>
/// <param name="strDataTableName">指定填充的DataTable名，如果为空字符串，则默认填充数据集的第一个表</param>
public override DataSet exeSqlForDataSet(string strSql,
CommandType cmdType,
CommonParameter[] paramsInput,
DataSet ds,
string strDataTableName)
{
try
{
SqlCommand cmd = PrepareCommand(false,strSql,cmdType,paramsInput);
ds = FillData(cmd,ds,strDataTableName);
if (cmdType.Equals(CommandType.StoredProcedure))
CommitTrans();
}
catch (Exception exp)
{
string strExp = exp.Message;
RollbackTrans();
ds = null;
}
finally
{
Close();
}
return ds;
}
#endregion
#region 执行纯存储过程
/// <summary>
/// 执行带输出参数的存储过程
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="paramsOutput">输出参数集合</param>
/// <returns>返回输出参数集合</returns>
public override CommonParameter[] exeProcWithOutputParams(string strSql,CommonParameter[] paramsOutput)
{
try
{
SqlCommand cmd = PrepareCommand(true,strSql,CommandType.StoredProcedure,paramsOutput);
cmd.ExecuteNonQuery();
CommitTrans();
for(int i = 0; i < paramsOutput.Length; i++)
{
paramsOutput.Value = cmd.Parameters.Value;
}
return paramsOutput;
}
catch (Exception exp)
{
RollbackTrans();
throw new Exception(exp.Message);
}
finally
{
Close();
}
}
/// <summary>
/// 执行带输入参数、输出参数的存储过程
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="paramsInput">参数集合</param>
/// <returns>返回输出参数集合</returns>
public override CommonParameter[] exeProcWithInputOutputParams(string strSql,
CommonParameter[] paramsInputOutput)
{
try
{
SqlCommand cmd = PrepareCommand(true,strSql,CommandType.StoredProcedure,paramsInputOutput);
cmd.ExecuteNonQuery();
CommitTrans();
CommonParameter[] cp = GetOutputParams(cmd);
return cp;
}
catch (Exception exp)
{
RollbackTrans();
throw new Exception(exp.Message);
}
finally
{
Close();
}
}
#endregion
#region 批量执行非查询SQL语句
/// <summary>
/// 批量执行无参数非查询SQL语句
/// </summary>
/// <param name="arrSql">SQL语句集合</param>
public override void exeBlockSql(ArrayList arrSql)
{
try
{
SqlCommand cmd = PrepareCommand(true,"",CommandType.Text,null);
for(int i = 0; i < arrSql.Count; i++)
{
cmd.CommandText = arrSql.ToString().Replace(@"?",@"@");
cmd.ExecuteNonQuery();
}
CommitTrans();
}
catch (Exception exp)
{
RollbackTrans();
throw new Exception(exp.Message);
}
finally
{
Close();
}
}
/// <summary>
/// 批量执行带参数非查询SQL语句
/// </summary>
/// <param name="arrSql">SQL语句集合</param>
/// <param name="arrSqlParam">SQL语句参数集合</param>
public override void exeBlockSql(ArrayList arrSql,ArrayList arrParam)
{
try
{
SqlCommand cmd = PrepareCommand(true,"",CommandType.Text,null);
for(int i = 0; i < arrSql.Count; i++)
{
cmd.CommandText = arrSql.ToString().Replace(@"?",@"@");
if(arrParam != null)
{
CommonParameter[] paramsInput = (CommonParameter[])arrParam;
cmd = AddParmas(cmd,paramsInput);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
CommitTrans();
}
catch (Exception exp)
{
RollbackTrans();
throw new Exception(exp.Message);
}
finally
{
Close();
}
}
/// <summary>
/// 批量执行无参数的向指定DataSet和DataTable填充的查询SQL语句
/// </summary>
/// <param name="arrSql">SQL语句集合</param>
/// <param name="ds">DataSet</param>
/// <param name="DataTableName">DataTable名字集合</param>
public override DataSet exeBlockSql(ArrayList arrSql, DataSet ds, ArrayList arrDataTableName)
{
try
{
SqlCommand cmd = PrepareCommand(false,"",CommandType.Text,null);
for(int i = 0; i < arrSql.Count; i++)
{
cmd.CommandText = arrSql.ToString().Replace(@"?",@"@");
ds = FillData(cmd,ds,arrDataTableName.ToString());
}
return ds;
}
catch(Exception exp)
{
throw new Exception(exp.Message);
}
}
/// <summary>
/// 批量执行有参数的向指定DataSet和DataTable填充的查询SQL语句
/// </summary>
/// <param name="arrSql">SQL语句集合</param>
/// <param name="arrParam">参数集合</param>
/// <param name="ds">DataSet</param>
/// <param name="arrDataTableName">DataTable名字集合</param>
public override DataSet exeBlockSql(ArrayList arrSql, ArrayList arrParam, DataSet ds, ArrayList arrDataTableName)
{
try
{
SqlCommand cmd = PrepareCommand(false,"",CommandType.Text,null);
for(int i = 0; i < arrSql.Count; i++)
{
cmd.CommandText = arrSql.ToString().Replace(@"?",@"@");
if(arrParam != null)
{
CommonParameter[] paramsInput = (CommonParameter[])arrParam;
cmd = AddParmas(cmd,paramsInput);
}
ds = FillData(cmd,ds,arrDataTableName.ToString());
cmd.Parameters.Clear();
}
return ds;
}
catch(Exception exp)
{
throw new Exception(exp.Message);
}
}
#endregion
#region 设置SQL命令
/// <summary>
/// 设置SQL命令
/// </summary>
/// <param name="bSqlType">SQL语句或存储过程的类型。</param>
/// 对于SQL语句，TRUE表示非查询，FALSE表示查询。
/// 对于存储过程，TRUE表示不返回数据集，FALSE表示返回数据集。
/// <param name="strSql">SQL语句或存储过程名</param>
/// <param name="cmdType">SQL命令的类型</param>
/// <param name="paramsInputOutput">SQL语句或存储过程的参数集合，空表示无参数</param>
/// <returns>设置后的SQL命令</returns>
private SqlCommand PrepareCommand(bool bSqlType,
string strSql,
CommandType cmdType,
CommonParameter[] paramsInputOutput)
{
strSql = strSql.Replace(@"?",@"@");
SqlCommand cmd = new SqlCommand();
if(bSqlType || cmdType.Equals(CommandType.StoredProcedure))
{
Open();
BeginTrans();
if(inTransaction)
cmd.Transaction = trans;
}
cmd.Connection = conn;
cmd.CommandType = cmdType;
cmd.CommandText = strSql;
if(paramsInputOutput != null)
{
return AddParmas(cmd,paramsInputOutput);
}
else
{
return cmd;
}
}
#endregion
#region 添加参数
/// <summary>
/// 添加SQL命令参数
/// </summary>
/// <param name="cmd">SQL命令</param>
/// <param name="paramsInputOutput">参数集合</param>
/// <returns></returns>
private SqlCommand AddParmas(SqlCommand cmd,CommonParameter[] paramsInputOutput)
{
foreach(CommonParameter cp in paramsInputOutput)
{
SqlParameter param = new SqlParameter();
param.ParameterName = @"@"+ @cp.Name;
if (cp.Value.ToString() !="")
param.Value = cp.Value;
else
param.Value = DBNull.Value;
param.Direction = ConvertParamDirection(cp.Direction);
param.SqlDbType = ConvertDbType(cp.Type);
param.Size = cp.Size;
cmd.Parameters.Add(param);
}
return cmd;
}
#endregion
#region 转换数据类型
/// <summary>
/// 将用户输入的数据类型字符串转换为SqlDbType
/// </summary>
/// <param name="strType">用户输入的数据类型字符串</param>
/// <returns></returns>
private SqlDbType ConvertDbType(string strDbType)
{
switch (strDbType.ToLower())
{
case "string":
return SqlDbType.VarChar;
case "int":
return SqlDbType.Int;
case "decimal":
return SqlDbType.Decimal;
case "money":
return SqlDbType.Money;
case "char":
return SqlDbType.Char;
case "datetime":
return SqlDbType.DateTime;
default:
return 0;
}
}
#endregion
#region 取得输出参数
/// <summary>
/// 取得SQL命令参数中的输出参数集合
/// </summary>
/// <param name="cmd">SQL命令</param>
/// <returns></returns>
private CommonParameter[] GetOutputParams(SqlCommand cmd)
{
int iParamsCount = cmd.Parameters.Count;
if (iParamsCount == 0)
{
return null;
}
CommonParameter[] cpOutput = new CommonParameter[iParamsCount];
for(int i = 0; i < iParamsCount; i++)
{
if (cmd.Parameters.Direction == ParameterDirection.Output)
{
cpOutput.Value = cmd.Parameters.Value;
}
}
return cpOutput;
}
#endregion
#region 填充数据集
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="cmd">SQL命令</param>
/// <returns>填充后的数据集</returns>
private DataSet FillData(SqlCommand cmd)
{
DataSet ds = new DataSet();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(ds);
return ds;
}
/// <summary>
/// 向指定的DataSet的DataTable填充数据集
/// </summary>
/// <param name="cmd">SQL命令</param>
/// <param name="ds">指定填充的DataSet</param>
/// <param name="strDataTableName">指定填充的DataTable名，如果为空字符串，则默认填充数据集的第一个表</param>
/// <returns>填充后的数据集</returns>
private DataSet FillData(SqlCommand cmd,DataSet ds,string strDataTableName)
{
SqlDataAdapter ad = new SqlDataAdapter(cmd);
if (strDataTableName != "")
ad.Fill(ds,strDataTableName);
else
ad.Fill(ds);
return ds;
}
#endregion
}
}
Code