C#SQLServer访问接口封装,包括普通的增删改查,储存过程访问以及SQL事务。
public class SqlHelper { //Database connection strings public static readonly string ConnectionStringLocal= ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString; // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// /// /// /// /// /// /// public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionStringLocal)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// /// /// /// /// /// /// /// 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; } /// /// /// /// /// /// /// /// 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; } /// /// /// /// /// /// /// public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectionStringLocal); // 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; } } /// /// 执行存储过程返回值 /// /// 存储过程名称 /// 参数集合 /// 执行结果 public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] commandParameters) { using (SqlConnection conn = new SqlConnection(ConnectionStringLocal)) { SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, CommandType.StoredProcedure, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); conn.Close(); return ds.Tables[0]; } catch { conn.Close(); throw; } } } public static DataSet ExecuteDataSetNo(string cmdText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(ConnectionStringLocal); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); return ds; } catch { conn.Close(); throw; } } /// /// 执行存储过程返回值 /// /// 存储过程名称 /// 参数集合 /// 执行结果 public static DataTable ExecuteDataTable(CommandType cmtype, string cmdText, params SqlParameter[] commandParameters) { using (SqlConnection conn = new SqlConnection(ConnectionStringLocal)) { SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, cmtype, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); conn.Close(); return ds.Tables[0]; } catch { conn.Close(); throw; } } } /// /// 执行存储过程返回值 /// /// 存储过程名称 /// 参数集合 /// 执行结果 public static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] commandParameters) { using (SqlConnection conn = new SqlConnection(ConnectionStringLocal)) { SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, CommandType.StoredProcedure, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); conn.Close(); return ds; } catch { conn.Close(); throw; } } } /// /// /// /// /// /// /// /// /// public static DataView ExecuteDataSet(string sortExpression, string direction, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(ConnectionStringLocal); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); DataView dv = ds.Tables[0].DefaultView; dv.Sort = sortExpression + " " + direction; return dv; } catch { conn.Close(); throw; } } /// /// /// /// /// /// /// public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { return NewExecuteScalar(cmdType, cmdText, commandParameters); } /// /// /// /// /// /// /// public static object NewExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection(ConnectionStringLocal)) { PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// /// /// /// /// /// /// /// 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; } /// /// /// /// /// public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// /// /// /// /// 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; } /// /// /// /// commond对象 /// 连接对象 /// /// /// /// 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); } } }
彭亚欧个人博客原创文章,转载请注明出处
文章关键词:C#数据库访问,SqlHelper,C#SQL访问接口封装
文章固定链接:https://www.pengyaou.com/homeart/MTIz.html
下一篇 月光族们的福利来啦!