C# sqlhelper 整理
以下代码是参考几个不同人的写法总结写成的，肯定还有很大的优化空间，暂存该版本；有建议的欢迎提出；
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FirstBI { public abstract class SqlHelpertrain//定义为abstract抽象类，不能被实例化，在应用时直接调用 { /*1.获取数据库连接字符串； * 2.Connection 链接绑定，open/close； * 3.实例化一个Command命令，给定命令执行的超时时间 ，命令执行的连接；命令的类型（增/删/改/查） * ，是否带事务，执行用到哪些参数，还有最重要的命令执行的sql字符串； * 4.SqlDataAdapter承接命令返回的结果集 */ private static int TimeOut = 5000; private static string connStrs = ConfigurationManager.ConnectionStrings["FirstBI.Properties.Settings.BaseERPConnectionString"].ConnectionString; /// <summary> /// 获取SqlCommand /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="tran"></param> /// <param name="sqlParams"></param> /// <returns></returns> public static SqlCommand GetSqlCommand(string sql, SqlConnection conn, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandTimeout = TimeOut; cmd.CommandType = cmdType; if (tran != null) cmd.Transaction = tran; if (sqlParams != null && sqlParams.Length > 0) cmd.Parameters.AddRange(sqlParams); return cmd; } /// <summary> /// DataTable /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="tran"></param> /// <param name="sqlParams"></param> /// <returns></returns> public static DataTable GetDataTable(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams) { using (SqlConnection conn = new SqlConnection(connStrs)) { try { conn.Open(); using (SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams)) { using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); da.Fill(dt); return dt; } } } catch (SqlException ex) { StringBuilder log = new StringBuilder(); log.Append("查询数据错误："); log.Append(ex); throw new Exception(log.ToString()); } finally { conn.Close(); } } } /// <summary> /// DataSet /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="tran"></param> /// <param name="sqlParams"></param> /// <returns></returns> public static DataSet GetDataSet(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams) { using (SqlConnection conn = new SqlConnection(connStrs)) { using (SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams)) { try { conn.Open(); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds); return ds; } } catch (SqlException ex) { StringBuilder log = new StringBuilder(); log.Append("查询数据错误："); log.Append(ex); throw new Exception(log.ToString()); } finally { conn.Close(); } } } } /// <summary> /// 返回影响的行数 /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="tran"></param> /// <param name="sqlParams"></param> /// <returns></returns> public static int ExecNonQuery(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams) { /*定义： SqlCommand cmd = new SqlCommand()；//或者SqlCommand cmd = new SqlConnection().CreateCommand(); 注意参数 cmd.CommandType = CommandType.StoredProcedure; //存储过程 cmd.CommandType = CommandType.Text; //sql语句 cmd.CommandType = CommandType.TableDirect; System.Data.CommandType.TableDirect表示要执行的是表 ，此时，cmd.CommandText 的值应该是要查询表的的名称。查询结果返回的是整个表。 */ int count = 0; using (SqlConnection conn = new SqlConnection(connStrs)) { using (SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams)) { try { conn.Open(); if (cmdType == CommandType.StoredProcedure) cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue; count = cmd.ExecuteNonQuery(); if (count <= 0) if (cmdType == CommandType.StoredProcedure) count = (int)cmd.Parameters["@RETURN_VALUE"].Value; } catch (SqlException ex) { StringBuilder log = new StringBuilder(); log.Append("查询数据错误："); log.Append(ex); throw new Exception(log.ToString()); } finally { conn.Close(); } } } return count; }
//返回查询结果的第一行第一列 public static object QueryScalar(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams) { using (SqlConnection conn = new SqlConnection(connStrs)) { using (SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams)) { try { conn.Open();//创建cmd之前或之后Open都是可以的 return cmd.ExecuteScalar(); } catch (SqlException ex) { StringBuilder log = new StringBuilder(); log.Append("查询数据出错："); log.Append(ex); throw new Exception(log.ToString()); } finally { conn.Close(); } } } } /// <summary> /// 使用完应关闭Reader；因为SQLDataReader为在线操作数据库，所以这个封装的方法中不能关闭连接； /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="tran"></param> /// <param name="sqlParams"></param> /// <returns></returns> public static SqlDataReader GetDataReader(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams) { //SqlDataReader要求，它读取数据的时候有，它独占它的SqlConnection对象，而且SqlConnection必须是Open状态 //SqlDataReader与SqlDataAdapter的区别：SqlDataReader为在线操作数据库，SqlDataAdapter为离线操作（打开连接获取数据集之后关闭连接，然后离线操作数据库，然后再打开连接将数据更新到数据库） SqlConnection conn = new SqlConnection(connStrs);//不可以使用using SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams); try { conn.Open(); //CommandBehavior.CloseConnection当SqlDataReader释放的时候，顺便把SqlConnection对象也释放掉 return cmd.ExecuteReader(CommandBehavior.CloseConnection);//在执行该命令时，如果关闭关联的 DataReader 对象，则关联的 Connection 对象也将关闭 } catch (SqlException ex) {
conn.Close();//异常时要关闭连接 StringBuilder log = new StringBuilder(); log.Append("查询数据错误："); log.Append(ex); throw new Exception(log.ToString()); } } } }
