// 一个自用的 SqlHelper 类 利用了刚学习到的 扩展方法 http://technet.microsoft.com/zh-cn/bb383977
/// <summary>
/// The SqlHelper class is intended to encapsulate high performance,
/// scalable best practices for common uses of SqlClient.
/// </summary>
public abstract class SqlHelper
{
//Database connection strings
public static readonly string connStr = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
public static int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
//if (conn.State != ConnectionState.Open) conn.Open();
conn.QuickOpen();
cmd.Parameters.Clear();
foreach (SqlParameter item in cmdParameters)
{
cmd.Parameters.Add(item);
}
return cmd.ExecuteNonQuery();
}
}
}
/// <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();
return val;
}
/// <summary>
/// 执行一个查询，返回一个object
/// </summary>
/// <param name="cmdText">查询文本</param>
/// <returns>结果</returns>
public static object ExecuteScalar(string cmdText, params SqlParameter[] cmdParameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
//if (conn.State != ConnectionState.Open) conn.Open();
conn.QuickOpen();
cmd.Parameters.Clear();
foreach (SqlParameter item in cmdParameters)
{
cmd.Parameters.Add(item);
}
return cmd.ExecuteScalar();
}
}
}
/// <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 object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
return cmd.ExecuteScalar();
}
/// <summary>
/// 执行一个查询，返回一个结果集
/// </summary>
/// <param name="cmdText">查询文本</param>
/// <returns>结果集</returns>
public static DataTable DB_Select(string cmdText, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = BuildQueryCommand(conn, cmdText, CommandType.Text, parameters);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
/// <summary>
/// 执行一个查询，返回一个结果集
/// </summary>
/// <param name="cmdText">查询文本</param>
/// <returns>结果集</returns>
public static DataTable DB_Select(string cmdText, CommandType type, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = BuildQueryCommand(conn, cmdText, type, parameters);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataTable RunProcedure(string storedProcName, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.QuickOpen();
DataTable dt = new DataTable();
//conn.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);
sqlDA.Fill(dt);
return dt;
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, string tableName, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.QuickOpen();
DataSet dataSet = new DataSet();
//conn.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
conn.Close();
return dataSet;
}
}
/// <summary>
/// 构建 SqlCommand 对象
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, params SqlParameter[] parameters)
{
return BuildQueryCommand(connection, storedProcName, CommandType.StoredProcedure, parameters);
}
/// <summary>
/// 构建 SqlCommand 对象
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string cmdText, CommandType type, params SqlParameter[] parameters)
{
SqlCommand command = new SqlCommand(cmdText, connection);
command.CommandType = type;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
/// <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();
conn.QuickOpen();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
cmd.Parameters.Clear();
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
}
}
public static class SqlExtensions
{
public static void QuickOpen(this SqlConnection conn)
{
int timeout = Generic.ToInt32(ConfigurationManager.AppSettings["TimeOut"]);
timeout = Math.Max(Math.Min(30000, timeout), 3000);
// We'll use a Stopwatch here for simplicity. A comparison to a stored DateTime.Now value could also be used
Stopwatch sw = new Stopwatch();
bool connectSuccess = false;
// Try to open the connection, if anything goes wrong, make sure we set connectSuccess = false
Thread t = new Thread(delegate()
{
try
{
sw.Start();
conn.Open();
connectSuccess = true;
}
catch { }
});
// Make sure it's marked as a background thread so it'll get cleaned up automatically
t.IsBackground = true;
t.Start();
// Keep trying to join the thread until we either succeed or the timeout value has been exceeded
while (timeout > sw.ElapsedMilliseconds)
if (t.Join(1))
break;
// If we didn't connect successfully, throw an exception
if (!connectSuccess)
throw new Exception("连接超时！\r\n未能连接到数据库！\r\n如有需要，可更改App.config 中的 TimeOut 值");
}
}
浙公网安备 33010602011771号