public static string GetData(string firstName,string lastName,ref DataSet ds)
{
SqlConnection conn=new SqlConnection(connectionString) ;
SqlDataAdapter da=new SqlDataAdapter("Pro_GetUserInfo",conn);
para0=new SqlParameter("@firstName",firstName);
para1=new SqlParameter("@lastName",lastName);
da.SelectCommand.Parameters.Add(para0);
da.SelectCommand.Parameters.Add(para1);
da.SelectCommand.CommandType=CommandType.StoredProcedure;
try
{
conn.Open();
da.Fill(ds);
conn.Close();
}
catch(Exception ex)
{
If(conn.State == ConnectionState.Open)
{
conn.Close();
}
return ex.ToString();
}
}
{
SqlConnection conn=new SqlConnection(connectionString) ;
SqlDataAdapter da=new SqlDataAdapter("Pro_GetUserInfo",conn);
para0=new SqlParameter("@firstName",firstName);
para1=new SqlParameter("@lastName",lastName);
da.SelectCommand.Parameters.Add(para0);
da.SelectCommand.Parameters.Add(para1);
da.SelectCommand.CommandType=CommandType.StoredProcedure;
try
{
conn.Open();
da.Fill(ds);
conn.Close();
}
catch(Exception ex)
{
If(conn.State == ConnectionState.Open)
{
conn.Close();
}
return ex.ToString();
}
}
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
public class CSqlHelper
{
string ConnStr = String.Empty;
public CSqlHelper()
{
//ConnStr = ConfigurationSettings.AppSettings["sqlConstr"];
ConnStr = sqlConstr;
}
public static string sqlConstr = "";
public CSqlHelper(string Str)
{
try
{
this.ConnStr = Str;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 返回connection对象,并打开连接
/// </summary>
/// <returns></returns>
public SqlConnection ReturnConn()
{
SqlConnection Conn = new SqlConnection(this.ConnStr);
Conn.Open();
return Conn;
}
public void Dispose(SqlConnection Conn)
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
GC.Collect();
}
/// <summary>
/// 运行SQL语句(连接在函数中打开并且关闭)
/// 是sql语句,或者不带参数的存储过程.
/// </summary>
/// <param name="SQL"></param>
public void RunProc(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd;
Cmd = CreateCmd(SQL, Conn);
try
{
Cmd.ExecuteNonQuery();
}
catch
{
throw new Exception(SQL);
}
Dispose(Conn);
return;
}
/// <summary>
/// 运行SQL语句返回DataReader
/// 连接打开,但没有关闭.
/// </summary>
/// <param name="SQL"></param>
/// <returns>SqlDataReader对象.</returns>
public SqlDataReader RunProcGetReader(string SQL, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd;
Cmd = CreateCmd(SQL, Conn);
SqlDataReader Dr;
try
{
Dr = Cmd.ExecuteReader(CommandBehavior.Default);
}
catch
{
throw new Exception(SQL);
}
//Dispose(Conn);
OutConn = Conn;
return Dr;
}
/// <summary>
/// 生成Command对象
/// 没有打开连接
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
private SqlCommand CreateCmd(string SQL, SqlConnection Conn)
{
SqlCommand Cmd;
Cmd = new SqlCommand(SQL, Conn);
return Cmd;
}
/// <summary>
/// 生成Command对象
/// 打开连接,但是没有关闭.将连接以返回参数形式输出
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
private SqlCommand CreateCmd(string SQL, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd;
Cmd = new SqlCommand(SQL, Conn);
OutConn = Conn;
return Cmd;
}
/// <summary>
/// 返回adapter对象
/// 连接打开
/// 连接没有关闭.
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public SqlDataAdapter CreateDa(string SQL, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataAdapter Da;
Da = new SqlDataAdapter(SQL, Conn);
OutConn = Conn;
return Da;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// 已经关闭了连接
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
public DataSet RunProc(string SQL, DataSet Ds)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataAdapter Da;
//Da = CreateDa(SQL, Conn);
Da = new SqlDataAdapter(SQL, Conn);
try
{
Da.Fill(Ds);
}
catch (Exception Err)
{
throw Err;
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// 已经关闭了连接
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
/// <param name="dataReader">表名</param>
public DataSet RunProc(string SQL, DataSet Ds, string tablename)
{
SqlConnection Conn = new SqlConnection();
SqlDataAdapter Da;
Da = CreateDa(SQL, out Conn);
try
{
Da.Fill(Ds, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
Dispose(Conn);
return Ds;
}
public int RunSQL(string SQL, SqlParameter[] prams)
{
SqlConnection Conn;
SqlCommand Cmd = CreateSQLCmd(SQL, prams, out Conn);
return (Cmd.ExecuteNonQuery());
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// 已经关闭了连接
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
/// <param name="dataReader">表名</param>
public DataSet RunProc(string SQL, DataSet Ds, int StartIndex, int PageSize, string
tablename)
{
SqlConnection Conn = new SqlConnection();
SqlDataAdapter Da;
Da = CreateDa(SQL, out Conn);
try
{
Da.Fill(Ds, StartIndex, PageSize, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 检验是否存在数据
/// 已经关闭了连接
/// </summary>
/// <returns></returns>
public bool ExistDate(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataReader Dr;
Dr = CreateCmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
Dispose(Conn);
return true;
}
else
{
Dispose(Conn);
return false;
}
}
/// <summary>
/// 返回SQL语句执行结果的第一行第一列
/// 已经关闭了连接
/// </summary>
/// <returns>字符串</returns>
public object ReturnValue(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
object result;
SqlDataReader Dr;
try
{
Dr = CreateCmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
result = Dr[0];
Dr.Close();
}
else
{
result = "";
Dr.Close();
}
}
catch
{
throw new Exception(SQL);
}
Dispose(Conn);
return result;
}
/// <summary>
/// 返回SQL语句第一行,第ColumnI列,
/// 已经关闭了连接
/// </summary>
/// <returns>字符串</returns>
public string ReturnValue(string SQL, int ColumnI)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
string result;
SqlDataReader Dr;
try
{
Dr = CreateCmd(SQL, Conn).ExecuteReader();
}
catch
{
throw new Exception(SQL);
}
if (Dr.Read())
{
result = Dr[ColumnI].ToString();
}
else
{
result = "";
}
Dr.Close();
Dispose(Conn);
return result;
}
//-----------------------------------运行存储过程------------------------------------------
//=========================================================================================
//-----------------------------------------------------------------------------------------
/// <summary>
/// 生成一个存储过程使用的sqlcommand.
/// 并返回已经打开的连接
/// </summary>
/// <param name="procName">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>sqlcommand对象.</returns>
private SqlCommand CreateCmd(string procName, SqlParameter[] prams, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
OutConn = Conn;
return Cmd;
}
private SqlCommand CreateSQLCmd(string SQL, SqlParameter[] prams, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(SQL, Conn);
Cmd.CommandType = CommandType.Text;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
OutConn = Conn;
return Cmd;
}
//-----------------------------------------------------------------------------------
/// <summary>
/// 为存储过程生成一个SqlCommand对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <returns>SqlCommand对象</returns>
private SqlCommand CreateCmd(string procName, SqlParameter[] prams, SqlDataReader Dr)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
Cmd.Parameters.Add(parameter);
}
Cmd.Parameters.Add(
new SqlParameter("ReturnValue", System.Data.SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return Cmd;
}
private SqlCommand CreateCmd(string procName, SqlDataReader Dr)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
return Cmd;
}
private SqlCommand CreateSqlCmd(string procName, SqlDataReader Dr)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
return Cmd;
}
/// <summary>
/// 运行存储过程,返回.
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <param name="dataReader">SqlDataReader对象</param>
public SqlDataReader RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
{
SqlCommand Cmd = CreateCmd(procName, prams, Dr);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return Dr;
// CreateSqlCmd
}
/// <summary>
/// 执行
/// </summary>
/// <param name="procName"></param>
/// <param name="Dr"></param>
public SqlDataReader RunProc(string procName, SqlDataReader Dr)
{
SqlCommand Cmd = CreateCmd(procName, Dr);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return Dr;
}
public SqlDataReader RunSQL(string SQL, SqlDataReader Dr)
{
SqlCommand Cmd = CreateSqlCmd(SQL, Dr);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return Dr;
}
//-------------------------------------------------------------------------------------
/// <summary>
/// 运行存储过程
/// 查询
/// 只返回结果集的第一行第一列
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
public string RunProc(string procName, SqlParameter[] prams)
{
SqlDataReader Dr;
SqlConnection Conn = new SqlConnection();
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (Dr.Read())
{
return Dr.GetValue(0).ToString();
}
else
{
return "";
}
}
public void RunProcNoReturn(string procName, SqlParameter[] prams)
{
SqlConnection Conn = new SqlConnection();
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
Cmd.ExecuteNonQuery();
Conn.Close();
}
/// <summary>
/// 运行存储过程
/// 执行命令;
/// 返回SqlCommand
/// 用于存储过程有返回值的情况
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">参数</param>
/// <returns>SqlCommand</returns>
public object ReturnValueByProc(string procName, SqlParameter[] prams)
{
object result = null;
SqlConnection Conn;
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
result = Cmd.ExecuteScalar();
Dispose(Conn);
return result;
}
/// <summary>
/// 运行存储过程
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">参数</param>
/// <param name="OutCmd">初始化后SqlCommand</param>
/// <param name="rowAffect">命令执行影响的行数</param>
/// <returns>SqlCommand</returns>
public SqlCommand RunProc(string procName, SqlParameter[] prams, SqlCommand OutCmd, out int rowAffect)
{
rowAffect = -1;
SqlConnection Conn;
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
rowAffect = Cmd.ExecuteNonQuery();
OutCmd = Cmd;
Dispose(Conn);
return Cmd;
}
/// <summary>
/// 适用于没有返回值的存储过程
/// </summary>
/// <param name="procName"></param>
/// <param name="prams"></param>
/// <param name="rowAffect"></param>
/// <returns>返回影响的行数</returns>
public int RunProc(string procName, SqlParameter[] prams, int rowAffect)
{
SqlConnection Conn;
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
rowAffect = Cmd.ExecuteNonQuery();
this.Dispose(Conn);
return rowAffect;
}
/// <summary>
/// 运行存储过程,返回dataset.
/// </summary>
/// <param name="procName">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>dataset对象.</returns>
public DataSet RunProc(string procName, SqlParameter[] prams, DataSet Ds)
{
SqlConnection Conn = new SqlConnection();
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
try
{
Da.Fill(Ds);
}
catch (Exception Ex)
{
string dd = Ex.Message;
throw Ex;
}
this.Dispose(Conn);
return Ds;
}
public DataSet RunProc(string procName, SqlParameter[] prams, DataSet Ds, string tablename)
{
SqlConnection Conn = new SqlConnection();
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
try
{
Da.Fill(Ds, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
this.Dispose(Conn);
return Ds;
}
///--------------------------------------------------------都可运行的
///
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static DataSet ExecuteReturnDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adapt;
DataSet ds = new DataSet();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
adapt = new SqlDataAdapter(cmd);
//int val = cmd.ExecuteNonQuery();
adapt.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
catch (Exception ex)
{
string msg = ex.Message;
return null;
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">an existing sql transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a SqlCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="cacheKey">Key to the parameter cache</param>
/// <param name="cmdParms">an array of SqlParamters to be cached</param>
public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
/// <summary>
/// Retrieve cached parameters
/// </summary>
/// <param name="cacheKey">key used to lookup parameters</param>
/// <returns>Cached SqlParamters array</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>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
public class CSqlHelper
{
string ConnStr = String.Empty;
public CSqlHelper()
{
//ConnStr = ConfigurationSettings.AppSettings["sqlConstr"];
ConnStr = sqlConstr;
}
public static string sqlConstr = "";
public CSqlHelper(string Str)
{
try
{
this.ConnStr = Str;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 返回connection对象,并打开连接
/// </summary>
/// <returns></returns>
public SqlConnection ReturnConn()
{
SqlConnection Conn = new SqlConnection(this.ConnStr);
Conn.Open();
return Conn;
}
public void Dispose(SqlConnection Conn)
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
GC.Collect();
}
/// <summary>
/// 运行SQL语句(连接在函数中打开并且关闭)
/// 是sql语句,或者不带参数的存储过程.
/// </summary>
/// <param name="SQL"></param>
public void RunProc(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd;
Cmd = CreateCmd(SQL, Conn);
try
{
Cmd.ExecuteNonQuery();
}
catch
{
throw new Exception(SQL);
}
Dispose(Conn);
return;
}
/// <summary>
/// 运行SQL语句返回DataReader
/// 连接打开,但没有关闭.
/// </summary>
/// <param name="SQL"></param>
/// <returns>SqlDataReader对象.</returns>
public SqlDataReader RunProcGetReader(string SQL, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd;
Cmd = CreateCmd(SQL, Conn);
SqlDataReader Dr;
try
{
Dr = Cmd.ExecuteReader(CommandBehavior.Default);
}
catch
{
throw new Exception(SQL);
}
//Dispose(Conn);
OutConn = Conn;
return Dr;
}
/// <summary>
/// 生成Command对象
/// 没有打开连接
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
private SqlCommand CreateCmd(string SQL, SqlConnection Conn)
{
SqlCommand Cmd;
Cmd = new SqlCommand(SQL, Conn);
return Cmd;
}
/// <summary>
/// 生成Command对象
/// 打开连接,但是没有关闭.将连接以返回参数形式输出
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
private SqlCommand CreateCmd(string SQL, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd;
Cmd = new SqlCommand(SQL, Conn);
OutConn = Conn;
return Cmd;
}
/// <summary>
/// 返回adapter对象
/// 连接打开
/// 连接没有关闭.
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public SqlDataAdapter CreateDa(string SQL, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataAdapter Da;
Da = new SqlDataAdapter(SQL, Conn);
OutConn = Conn;
return Da;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// 已经关闭了连接
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
public DataSet RunProc(string SQL, DataSet Ds)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataAdapter Da;
//Da = CreateDa(SQL, Conn);
Da = new SqlDataAdapter(SQL, Conn);
try
{
Da.Fill(Ds);
}
catch (Exception Err)
{
throw Err;
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// 已经关闭了连接
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
/// <param name="dataReader">表名</param>
public DataSet RunProc(string SQL, DataSet Ds, string tablename)
{
SqlConnection Conn = new SqlConnection();
SqlDataAdapter Da;
Da = CreateDa(SQL, out Conn);
try
{
Da.Fill(Ds, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
Dispose(Conn);
return Ds;
}
public int RunSQL(string SQL, SqlParameter[] prams)
{
SqlConnection Conn;
SqlCommand Cmd = CreateSQLCmd(SQL, prams, out Conn);
return (Cmd.ExecuteNonQuery());
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// 已经关闭了连接
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
/// <param name="dataReader">表名</param>
public DataSet RunProc(string SQL, DataSet Ds, int StartIndex, int PageSize, string
tablename)
{
SqlConnection Conn = new SqlConnection();
SqlDataAdapter Da;
Da = CreateDa(SQL, out Conn);
try
{
Da.Fill(Ds, StartIndex, PageSize, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 检验是否存在数据
/// 已经关闭了连接
/// </summary>
/// <returns></returns>
public bool ExistDate(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataReader Dr;
Dr = CreateCmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
Dispose(Conn);
return true;
}
else
{
Dispose(Conn);
return false;
}
}
/// <summary>
/// 返回SQL语句执行结果的第一行第一列
/// 已经关闭了连接
/// </summary>
/// <returns>字符串</returns>
public object ReturnValue(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
object result;
SqlDataReader Dr;
try
{
Dr = CreateCmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
result = Dr[0];
Dr.Close();
}
else
{
result = "";
Dr.Close();
}
}
catch
{
throw new Exception(SQL);
}
Dispose(Conn);
return result;
}
/// <summary>
/// 返回SQL语句第一行,第ColumnI列,
/// 已经关闭了连接
/// </summary>
/// <returns>字符串</returns>
public string ReturnValue(string SQL, int ColumnI)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
string result;
SqlDataReader Dr;
try
{
Dr = CreateCmd(SQL, Conn).ExecuteReader();
}
catch
{
throw new Exception(SQL);
}
if (Dr.Read())
{
result = Dr[ColumnI].ToString();
}
else
{
result = "";
}
Dr.Close();
Dispose(Conn);
return result;
}
//-----------------------------------运行存储过程------------------------------------------
//=========================================================================================
//-----------------------------------------------------------------------------------------
/// <summary>
/// 生成一个存储过程使用的sqlcommand.
/// 并返回已经打开的连接
/// </summary>
/// <param name="procName">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>sqlcommand对象.</returns>
private SqlCommand CreateCmd(string procName, SqlParameter[] prams, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
OutConn = Conn;
return Cmd;
}
private SqlCommand CreateSQLCmd(string SQL, SqlParameter[] prams, out SqlConnection OutConn)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(SQL, Conn);
Cmd.CommandType = CommandType.Text;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
OutConn = Conn;
return Cmd;
}
//-----------------------------------------------------------------------------------
/// <summary>
/// 为存储过程生成一个SqlCommand对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <returns>SqlCommand对象</returns>
private SqlCommand CreateCmd(string procName, SqlParameter[] prams, SqlDataReader Dr)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
Cmd.Parameters.Add(parameter);
}
Cmd.Parameters.Add(
new SqlParameter("ReturnValue", System.Data.SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return Cmd;
}
private SqlCommand CreateCmd(string procName, SqlDataReader Dr)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
return Cmd;
}
private SqlCommand CreateSqlCmd(string procName, SqlDataReader Dr)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
return Cmd;
}
/// <summary>
/// 运行存储过程,返回.
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <param name="dataReader">SqlDataReader对象</param>
public SqlDataReader RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
{
SqlCommand Cmd = CreateCmd(procName, prams, Dr);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return Dr;
// CreateSqlCmd
}
/// <summary>
/// 执行
/// </summary>
/// <param name="procName"></param>
/// <param name="Dr"></param>
public SqlDataReader RunProc(string procName, SqlDataReader Dr)
{
SqlCommand Cmd = CreateCmd(procName, Dr);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return Dr;
}
public SqlDataReader RunSQL(string SQL, SqlDataReader Dr)
{
SqlCommand Cmd = CreateSqlCmd(SQL, Dr);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return Dr;
}
//-------------------------------------------------------------------------------------
/// <summary>
/// 运行存储过程
/// 查询
/// 只返回结果集的第一行第一列
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
public string RunProc(string procName, SqlParameter[] prams)
{
SqlDataReader Dr;
SqlConnection Conn = new SqlConnection();
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (Dr.Read())
{
return Dr.GetValue(0).ToString();
}
else
{
return "";
}
}
public void RunProcNoReturn(string procName, SqlParameter[] prams)
{
SqlConnection Conn = new SqlConnection();
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
Cmd.ExecuteNonQuery();
Conn.Close();
}
/// <summary>
/// 运行存储过程
/// 执行命令;
/// 返回SqlCommand
/// 用于存储过程有返回值的情况
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">参数</param>
/// <returns>SqlCommand</returns>
public object ReturnValueByProc(string procName, SqlParameter[] prams)
{
object result = null;
SqlConnection Conn;
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
result = Cmd.ExecuteScalar();
Dispose(Conn);
return result;
}
/// <summary>
/// 运行存储过程
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">参数</param>
/// <param name="OutCmd">初始化后SqlCommand</param>
/// <param name="rowAffect">命令执行影响的行数</param>
/// <returns>SqlCommand</returns>
public SqlCommand RunProc(string procName, SqlParameter[] prams, SqlCommand OutCmd, out int rowAffect)
{
rowAffect = -1;
SqlConnection Conn;
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
rowAffect = Cmd.ExecuteNonQuery();
OutCmd = Cmd;
Dispose(Conn);
return Cmd;
}
/// <summary>
/// 适用于没有返回值的存储过程
/// </summary>
/// <param name="procName"></param>
/// <param name="prams"></param>
/// <param name="rowAffect"></param>
/// <returns>返回影响的行数</returns>
public int RunProc(string procName, SqlParameter[] prams, int rowAffect)
{
SqlConnection Conn;
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
rowAffect = Cmd.ExecuteNonQuery();
this.Dispose(Conn);
return rowAffect;
}
/// <summary>
/// 运行存储过程,返回dataset.
/// </summary>
/// <param name="procName">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>dataset对象.</returns>
public DataSet RunProc(string procName, SqlParameter[] prams, DataSet Ds)
{
SqlConnection Conn = new SqlConnection();
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
try
{
Da.Fill(Ds);
}
catch (Exception Ex)
{
string dd = Ex.Message;
throw Ex;
}
this.Dispose(Conn);
return Ds;
}
public DataSet RunProc(string procName, SqlParameter[] prams, DataSet Ds, string tablename)
{
SqlConnection Conn = new SqlConnection();
SqlCommand Cmd = CreateCmd(procName, prams, out Conn);
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
try
{
Da.Fill(Ds, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
this.Dispose(Conn);
return Ds;
}
///--------------------------------------------------------都可运行的
///
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static DataSet ExecuteReturnDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adapt;
DataSet ds = new DataSet();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
adapt = new SqlDataAdapter(cmd);
//int val = cmd.ExecuteNonQuery();
adapt.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
catch (Exception ex)
{
string msg = ex.Message;
return null;
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">an existing sql transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a SqlCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="cacheKey">Key to the parameter cache</param>
/// <param name="cmdParms">an array of SqlParamters to be cached</param>
public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
/// <summary>
/// Retrieve cached parameters
/// </summary>
/// <param name="cacheKey">key used to lookup parameters</param>
/// <returns>Cached SqlParamters array</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>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}