[置顶] 自己写sqlhelper类

自己写sqlhelper类

 

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace birthday
{
    /// <summary>
    /// SqlHelper
    /// </summary>
    [Serializable]
    public class SqlHelper
    {
        //数据库连接字符串
        private static string strConn = ConfigurationManager.ConnectionStrings["strConn"].ConnectionString;

        #region ExecuteNonQuery

        /// <summary>
        /// 执行T-sql语句 并返回受影响行数
        /// </summary>
        /// <param name="sql">T-sql语句</param>
        /// <returns>没有受影响行数返回-1</returns>
        public static int ExecuteNonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行T-sql语句 并返回受影响行数
        /// </summary>
        /// <param name="sql">T-sql语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns>没有受影响行数返回-1</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] SqlParameter)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(SqlParameter);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 执行T-sql语句 并返回结果的第一行第一列(忽略其他列)
        /// </summary>
        /// <param name="sql">T-sql语句</param>
        /// <returns>没有列则返回null</returns>
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行T-sql语句 并返回结果的第一行第一列(忽略其他列)
        /// </summary>
        /// <param name="sql">T-sql语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns>没有列则返回null</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] SqlParameter)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(SqlParameter);
                    return cmd.ExecuteScalar();
                }
            }
        }

        #endregion

        #region ExecuteReader

        /// <summary>
        /// 以只进流方式读取数据，查询到的数据存储在服务器中
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <returns>数据流</returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    return cmd.ExecuteReader();
                }
            }
        }

        /// 以只进流方式读取数据，查询到的数据存储在服务器中
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <param name="cbh">查询结果、查询数据的说明</param>
        /// <returns>数据流</returns>
        public static SqlDataReader ExecuteReader(string sql, CommandBehavior CommandBehavior)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    return cmd.ExecuteReader(CommandBehavior);
                }
            }
        }

        /// <summary>
        /// 以只进流方式读取数据，查询到的数据存储在服务器中
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns>数据流</returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] SqlParameter)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(SqlParameter);
                    return cmd.ExecuteReader();
                }
            }
        }

        /// <summary>
        /// 以只进流方式读取数据，查询到的数据存储在服务器中
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns>数据流</returns>
        public static SqlDataReader ExecuteReader(string sql, CommandBehavior CommandBehavior, params SqlParameter[] SqlParameter)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(SqlParameter);
                    return cmd.ExecuteReader(CommandBehavior);
                }
            }
        }

        #endregion

        #region ExeccutDataSet

        /// <summary>
        /// 返回以xml描述的不依赖于数据库的独立数据集合，数据存放于客户端，相当于内存中的数据库
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <returns>数据集合</returns>
        public static DataSet ExeccutDataSet(string sql)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    SqlDataAdapter _sda = new SqlDataAdapter(cmd);
                    DataSet _ds = new DataSet();
                    _sda.Fill(_ds);
                    return _ds;
                }
            }
        }

        /// <summary>
        /// 返回以xml描述的不依赖于数据库的独立数据集合，数据存放于客户端，相当于内存中的数据库
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns>数据集合</returns>
        public static DataSet ExeccutDataSet(string sql, params SqlParameter[] SqlParameter)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(SqlParameter);
                    SqlDataAdapter _sda = new SqlDataAdapter(cmd);
                    DataSet _ds = new DataSet();
                    _sda.Fill(_ds);
                    return _ds;
                }
            }
        }

        #endregion

        #region ExeccutDataTable

        /// <summary>
        /// 返回临时保存数据的网格虚拟表(表示内存中数据的一个表)
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <returns>数据虚拟表</returns>
        public static DataTable ExeccutDataTable(string sql)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    SqlDataAdapter _sda = new SqlDataAdapter(cmd);
                    DataTable _data = new DataTable();
                    _sda.Fill(_data);
                    return _data;
                }
            }
        }

        /// <summary>
        /// 返回临时保存数据的网格虚拟表(表示内存中数据的一个表)
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns>数据虚拟表</returns>
        public static DataTable ExeccutDataTable(string sql, params SqlParameter[] SqlParameter)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(SqlParameter);
                    SqlDataAdapter _sda = new SqlDataAdapter(cmd);
                    DataTable _data = new DataTable();
                    _sda.Fill(_data);
                    return _data;
                }
            }
        }

        #endregion

        #region ExecuteDataRow

        /// <summary>
        /// 返回数据行(返回第一行，忽略其他行)
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <returns>返回数据行</returns>
        public static DataRow ExecuteDataRow(string sql)
        {

            return SqlHelper.ExeccutDataTable(sql).Rows[0];
        }

        /// <summary>
        /// 返回数据行(返回第一行，忽略其他行)
        /// </summary>
        /// <param name="sql">T-sql 语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns>返回数据行</returns>
        public static DataRow ExecuteDataRow(string sql, params SqlParameter[] SqlParameter)
        {
            return SqlHelper.ExeccutDataTable(sql, SqlParameter).Rows[0];
        }

        #endregion

        #region GetRecordCount

        /// <summary>
        /// 得到数据总条数
        /// </summary>
        /// <param name="table">表名称</param>
        /// <returns>数据总条数</returns>
        public static int GetRecordCount(string table)
        {
            int RecordCount = 0;
            string sql = "select count(*) from " + table;
            if (int.TryParse(SqlHelper.ExecuteScalar(sql).ToString(), out RecordCount))
            {
                return RecordCount;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 得到数据总条数
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="where">过滤条件</param>
        /// <returns>得到数据总条数</returns>
        public static int GetRecordCount(string table, string where)
        {
            int RecordCount = 0;
            string sql = "select count(*) from " + table + " where 1=1 and " + where;
            if (int.TryParse(SqlHelper.ExecuteScalar(sql).ToString(), out RecordCount))
            {
                return RecordCount;
            }
            else
            {
                return 0;
            }
        }

        #endregion

        #region GetRecordMax

        /// <summary>
        /// 得到指定表指定列最大值
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="field">列名称</param>
        /// <returns>最大值</returns>
        public static int GetRecordMax(string table, string field)
        {
            int _max = 0;
            string sql = "select max(" + field + ") from " + table;
            if (int.TryParse(SqlHelper.ExecuteScalar(sql).ToString(), out _max))
            {
                return _max;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 得到指定表指定列最大值
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="field">列名称</param>
        /// <param name="where">过滤条件</param>
        /// <returns>最大值</returns>
        public static int GetRecordMax(string table, string field, string where)
        {
            int _max = 0;
            string sql = "select max(" + field + ") from " + table + " where 1=1 and " + where;
            if (int.TryParse(SqlHelper.ExecuteScalar(sql).ToString(), out _max))
            {
                return _max;
            }
            else
            {
                return 0;
            }
        }

        #endregion

        #region GetRecordMin

        /// <summary>
        /// 得到指定表指定列最小值
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="field">列名称</param>
        /// <returns>最小值</returns>
        public static int GetRecordMin(string table, string field)
        {
            int _min = 0;
            string sql = "select min(" + field + ") from " + table;
            if (int.TryParse(SqlHelper.ExecuteScalar(sql).ToString(), out _min))
            {
                return _min;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 得到指定表指定列最小值
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="field">列名称</param>
        /// <param name="where">过滤条件</param>
        /// <returns>最小值</returns>
        public static int GetRecordMin(string table, string field, string where)
        {
            int _min = 0;
            string sql = "select min(" + field + ") from " + table + " where 1=1 and " + where;
            if (int.TryParse(SqlHelper.ExecuteScalar(sql).ToString(), out _min))
            {
                return _min;
            }
            else
            {
                return 0;
            }
        }

        #endregion

        #region GetRecordAvg

        /// <summary>
        /// 得到指定表指定列平均值
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="field">列名称</param>
        /// <returns>平均值</returns>
        public static int GetRecordAvg(string table, string field)
        {
            int _avg = 0;
            string sql = "select avg(" + field + ") from " + table;
            if (int.TryParse(SqlHelper.ExecuteScalar(sql).ToString(), out _avg))
            {
                return _avg;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 得到指定表指定列平均值
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="field">列名称</param>
        /// <param name="where">过滤条件</param>
        /// <returns>平均值</returns>
        public static int GetRecordAvg(string table, string field, string where)
        {
            int _avg = 0;
            string sql = "select avg(" + field + ") from " + table + " where 1=1 and " + where;
            if (int.TryParse(SqlHelper.ExecuteScalar(sql).ToString(), out _avg))
            {
                return _avg;
            }
            else
            {
                return 0;
            }
        }

        #endregion
    }
}