/// <summary>
/// 提供对数据库的操作服务:SQL语句的执行,存储过程的调用
/// </summary>
public abstract class SQLHelper
{
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}
private static SQLServer PrepareCommand(SqlCommand cmd,CommandType cmdType, string cmdText, SqlParameter[] intCmdParms,SqlParameter[] outCmdParms)
{
SQLServer sqlServer = SQLServer.Instance();
cmd.Connection = sqlServer.Connection;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (intCmdParms != null)
{
foreach (SqlParameter parm in intCmdParms)
cmd.Parameters.Add(parm);
}
if(outCmdParms != null)
{
foreach (SqlParameter parm in outCmdParms)
cmd.Parameters.Add(parm);
}
return sqlServer;
}
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 参数数组存入缓存
/// </summary>
/// <param name="cacheKey">键</param>
/// <param name="cmdParms">值:参数数组</param>
public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
{
parmCache[cacheKey] = cmdParms;
}
/// <summary>
/// 取出参数数组
/// </summary>
/// <param name="cacheKey">键</param>
/// <returns>参数数组</returns>
public static SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
/// <summary>
/// 对连接执行 Transact-SQL 语句并返回受影响的行数。
/// </summary>
/// <param name="cmdType">指定如何解释命令字符串</param>
/// <param name="cmdText">T-SQL 命令行 或 存储过程名</param>
/// <param name="cmdParms">参数数组</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SQLServer sqlServer = PrepareCommand(cmd, cmdType, cmdText, cmdParms,null);
try
{
sqlServer.Connection.Open();
sqlServer.BeginTransaction();
int val = cmd.ExecuteNonQuery();
sqlServer.CommitTransaction();
cmd.Parameters.Clear();
return val;
}
catch
{
sqlServer.RollbackTransaction();
throw;
}
finally
{
sqlServer.Connection.Close();
}
}
/// <summary>
/// 生成DataReader
/// </summary>
/// <param name="cmdType">指定如何解释命令字符串</param>
/// <param name="cmdText">T-SQL 命令行 或 存储过程名</param>
/// <param name="cmdParms">参数数组</param>
/// <returns>受影响的行数</returns>
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SQLServer sqlServer = PrepareCommand(cmd, cmdType, cmdText, cmdParms,null);
try
{
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch
{
sqlServer.Connection.Close();
throw;
}
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
/// </summary>
/// <param name="cmdType">指定如何解释命令字符串</param>
/// <param name="cmdText">T-SQL 命令行 或 存储过程名</param>
/// <param name="cmdParms">参数数组</param>
/// <returns>结果集中第一行的第一列</returns>
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SQLServer sqlServer = PrepareCommand(cmd, cmdType, cmdText, cmdParms,null);
try
{
sqlServer.Connection.Open();
sqlServer.BeginTransaction();
object val = cmd.ExecuteScalar();
sqlServer.CommitTransaction();
cmd.Parameters.Clear();
return val;
}
catch
{
sqlServer.RollbackTransaction();
throw;
}
finally
{
sqlServer.Connection.Close();
}
}
/// <summary>
/// 执行存储过程,带输出参数
/// </summary>
/// <param name="StoredProcedure">存储过程名</param>
/// <param name="outValue">输出值引用</param>
/// <param name="outCmdParms">输出参数数组</param>
/// <param name="cmdParms">输入参数数组</param>
public static void ExecuteStoredProcedure(string StoredProcedure,ref object[] outValue,SqlParameter[] outCmdParms, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SQLServer sqlServer = PrepareCommand(cmd, CommandType.StoredProcedure, StoredProcedure, cmdParms,outCmdParms);
try
{
sqlServer.Connection.Open();
sqlServer.BeginTransaction();
cmd.ExecuteNonQuery();
sqlServer.CommitTransaction();
if(outCmdParms != null)
for(int i=0;i<outCmdParms.Length;i++)
outValue[i] = outCmdParms[i].Value;
cmd.Parameters.Clear();
}
catch
{
sqlServer.RollbackTransaction();
throw;
}
finally
{
sqlServer.Connection.Close();
}
}
/// <summary>
/// 执行存储过程,返回结果集
/// </summary>
/// <param name="StoredProcedure">存储过程名</param>
/// <param name="cmdParms">参数数组</param>
/// <returns>结果集</returns>
public static DataTable ExecuteStoredProcedure(string StoredProcedure,params SqlParameter[] cmdParms)
{
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
SQLServer sqlServer = PrepareCommand(cmd, CommandType.StoredProcedure, StoredProcedure, cmdParms,null);
DataTable dt = new DataTable();
sda.SelectCommand = cmd;
try
{
sqlServer.Connection.Open();
sqlServer.BeginTransaction();
sda.Fill(dt);
sqlServer.CommitTransaction();
cmd.Parameters.Clear();
return dt;
}
catch
{
sqlServer.RollbackTransaction();
throw;
}
finally
{
sqlServer.Connection.Close();
}
}