常用DBHelper类

 public class DBHelper
    {
        SqlConnection conn = null;

        public DBHelper()
        {
             string str = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
             conn = new SqlConnection(str);
        }

        /// 创建执行sql语句命令方法
        /// <summary>
        /// 创建执行sql语句命令方法
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数集合</param>
        /// <returns>返回cmd命令</returns>
        protected SqlCommand CreateCommand(string sql, SqlParameter[] param)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            return cmd;
        }

        /// 创建储存过程命令方法
        /// <summary>
        /// 创建储存过程命令方法
        /// </summary>
        /// <param name="procName">储存过程名字</param>
        /// <param name="param">参数集合</param>
        /// <returns>返回cmd命令</returns>
        protected SqlCommand CreateProcCommand(string procName, SqlParameter[] param)
        {
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            return cmd;
        }

        /// 执行增删改command命令
        /// <summary>
        /// 执行增删改command命令
        /// </summary>
        /// <param name="cmd">cmd命令</param>
        /// <returns>返回受影响行数</returns>
        protected int ExecuteCommand(SqlCommand cmd)
        {
            int i = 0;
            cmd.Connection.Open();
            i = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            return i;
        }

        /// 创建查看command命令
        /// <summary>
        /// 创建查看command命令
        /// </summary>
        /// <param name="cmd">cmd命令</param>
        /// <returns>返回读取命令</returns>
        protected SqlDataReader ExecuteReaderCommand(SqlCommand cmd)
        {

            cmd.Connection.Open();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }

        /// 创建查询Command命令
        /// <summary>
        /// 创建查询Command命令
        /// </summary>
        /// <param name="cmd">cmd命令</param>
        /// <returns>返回查询内容</returns>
        protected object ExecuteScalarCommand(SqlCommand cmd)
        {
            object i = 0;
            cmd.Connection.Open();
            i = cmd.ExecuteScalar();
            cmd.Connection.Close();
            return i;
        }

        /// 创建参数方法
        /// <summary>
        /// 创建参数方法
        /// </summary>
        /// <param name="paramName">参数名字</param>
        /// <param name="type">参数类型</param>
        /// <param name="length">类型长度</param>
        /// <param name="value">参数值</param>
        /// <returns>返回参数</returns>
        protected SqlParameter CreateInputParam(string paramName, SqlDbType type, int length, object value)
        {
            SqlParameter param = new SqlParameter(paramName, type, length);
            param.Value = value;
            return param;
        }

    }

/调用事例：

        /// 分页查询收藏夹

        /// <summary>
        /// 分页查询收藏夹
        /// </summary>
        /// <param name="name">用户名</param>
        /// <param name="pageindex">当前页下标</param>
        /// <param name="pagesize">每页显示数量</param>
        /// <returns>商品集合</returns>
        public List<CollectInfoModel> GetGroupCollectByUserName(string name,int pageindex,int pagesize)
        {
            List<CollectInfoModel> list = new List<CollectInfoModel>();
            string sql = "select top(@size) * from CollectInfo where cid not in (select top(@size*(@index-1)) cid from CollectInfo) and name=@name";
            SqlParameter[] pram = new SqlParameter[]
            {
                new SqlParameter("@name",name),
                new SqlParameter("@size",pagesize),
                new SqlParameter("@index",pageindex)
            };
            SqlDataReader dr = ExecuteReaderCommand(CreateCommand(sql, pram));
            while (dr.Read())
            {
                CollectInfoModel c = new CollectInfoModel();
                c.Cid = (int)dr["cid"];
                c.CPid = new ProductInfoModel();
                ProductInfoService pser = new ProductInfoService();
                c.CPid = pser.SelectProductById(Convert.ToInt32(dr["cPid"]));
                c.Name = (string)dr["name"];
                c.Time = (DateTime)dr["time"];
                list.Add(c);
            }
            dr.Close();
            return list;
        }

转载于:https://www.cnblogs.com/shinggang/archive/2011/03/31/2001320.html