SQLHelper /// <summary> /// 获取连接字符串 /// </summary> PRivate static readonly string constr = ConfigurationManager.ConnectionStrings["dbUser"].ConnectionString; /// <summary> /// 返回受影响行数（非查询语句） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="parameters">参数化查询</param> /// <returns></returns> public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(constr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } } /// <summary> /// 返回查询结果集中的第一行第一列 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="parameters">参数化查询</param> /// <returns></returns> public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(constr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } } /// <summary> /// 返回查询结果有多条数据，数据放在数据库中 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="parameters">参数化查询</param> /// <returns></returns> public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection()) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteReader(); } } } /// <summary> /// 返回查询结果有多条数据，数据放在本地缓存中 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="parameters">参数化查询</param> /// <returns></returns> public static DataTable ExecuteDataSet(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(constr)) { conn.Open() using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } } /// <summary> /// 将数据库中的Null转化为null /// </summary> /// <param name="value"></param> /// <returns></returns> public static object OutputNull(object value) { if (value == DBNull.Value) return null; else return value; } /// <summary> /// 将输入数据的null转化为数据库中Null /// </summary> /// <param name="value"></param> /// <returns></returns> public static object InputNull(object value) { if (value == null) return DBNull.Value; else return value; } }