.NET中的数据库连接用的类

using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Data;using System.Collections;namespace DalImplements{internal static class SqlHelper{ private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); private static string staticConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; /// <summary> /// 获取连接字符串 /// </summary> internal static string ConnectionString { get { return staticConnectionString; } } /// <summary> /// 获取服务器时间 /// </summary> /// <returns></returns> internal static DateTime GetServerTime() { using (SqlConnection con = new SqlConnection(ConnectionString)) { SqlCommand cmd = new SqlCommand("SELECT GetDate()", con); con.Open(); DateTime dt = (DateTime)cmd.ExecuteScalar(); return dt; } } /// <summary> /// 执行非查询语句 /// </summary> /// <param name="cmdText">命令文本</param> /// <param name="commandParameters">参数列表</param> /// <returns>执行命令所影响的数据行数</returns> internal static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters) { using (SqlConnection conn = new SqlConnection(ConnectionString)) { SqlCommand cmd = PrepareCommand(conn, null, CommandType.StoredProcedure, cmdText, commandParameters); int rv = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rv; } } /// <summary> /// 执行非查询语句 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令文本</param> /// <param name="commandParameters">参数列表</param> /// <returns>执行命令所影响的数据行数</returns> internal static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = PrepareCommand(conn, null, cmdType, cmdText, commandParameters); int rv = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rv; } } /// <summary> /// 执行非查询语句 /// </summary> /// <remarks> /// 例如: int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="conn">已存在的数据库连接</param> /// <param name="commandType">命令类型</param> /// <param name="commandText">命令文本</param> /// <param name="commandParameters">命令参数</param> /// <returns>执行命令所影响的数据行数</returns> internal static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = PrepareCommand(connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 在事务中执行非查询语句 /// </summary> /// <param name="trans">事务</param> /// <param name="commandType">命令类型</param> /// <param name="commandText">命令文本</param> /// <param name="commandParameters">命令参数</param> /// <returns>执行命令所影响的数据行数</returns> internal static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = PrepareCommand(trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 执行查询命令 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令文本</param> /// <param name="commandParameters">参数列表</param> /// <returns>数据读取器</returns> internal static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(connectionString); try { SqlCommand cmd = PrepareCommand(conn, null, cmdType, cmdText, commandParameters); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return dr; } catch { conn.Close(); throw; } } /// <summary> /// 执行查询命令 /// </summary> /// <param name="cmdText">命令文本</param> /// <param name="commandParameters">参数列表</param> /// <returns>数据读取器</returns> internal static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(ConnectionString); try { SqlCommand cmd = PrepareCommand(conn, null, CommandType.StoredProcedure, cmdText, commandParameters); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return dr; } catch { conn.Close(); throw; } } /// <summary> /// 执行单值查询语句 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令文本</param> /// <param name="commandParameters">参数列表</param> /// <returns>查询到的标量值</returns> internal static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = PrepareCommand(connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 执行单值查询语句 /// </summary> /// <param name="connection">现有的连接</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令文本</param> /// <param name="commandParameters">参数列表</param> /// <returns>查询到的标量值</returns> internal static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = PrepareCommand(connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// <summary> /// 缓存参数数组 /// </summary> /// <param name="cacheKey">缓存键</param> /// <param name="commandParameters">参数数组</param> internal static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// <summary> /// 获取缓存的参数数组 /// </summary> /// <param name="cacheKey">缓存键</param> /// <returns>缓存的参数数组，或者如果给定的键不存在则返回null</returns> internal 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 = (SqlParameter)((ICloneable)cachedParms).Clone(); return clonedParms; } /// <summary> /// 帮助器方法，准备命令参数 /// </summary> /// <param name="cmd">命令对象</param> /// <param name="conn">连接对象</param> /// <param name="trans">事务对象</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令文本</param> /// <param name="cmdParms">命令参数</param> private static SqlCommand PrepareCommand(SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); SqlCommand cmd = new SqlCommand(); 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); } return cmd; }}}