SqlHelp帮助类

 public  class SqlHelp
    {
      public static string constr = ConfigurationManager.ConnectionStrings["PlatformConnection"].ToString();
        public static int ExecuteNonQuery(string constr, string cmdstr, CommandType type, params SqlParameter[] ps)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = constr;
            conn.Open();


            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdstr;
           
            cmd.Connection = conn;

            //设置操作类型
            cmd.CommandType = type;

            if (ps != null)
                cmd.Parameters.AddRange(ps);


            int i = cmd.ExecuteNonQuery();
            conn.Close();


            return i;
        }


        /// <summary>
        /// 执行数据库操作（查询）,返回数据集（DATASET）
        /// </summary>
        /// <param name="constr">数据库连接字符串</param>
        /// <param name="cmdstr">T-SQL语句（查询）或 存储过程名称</param>
        /// <param name="type">操作类型</param>
        /// <param name="ps">参数列表没有参数可以给个null</param>
        /// <returns>返回数据集</returns>
        /// 

        public static DataSet ExecuteDataset(string constr, string cmdstr, CommandType type, params SqlParameter[] ps)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = constr;


            SqlDataAdapter sda = new SqlDataAdapter(cmdstr, conn);
            //声明操作语句是一个存储过程（默认SQL文本）
            sda.SelectCommand.CommandType = type;


            if (ps != null)
                sda.SelectCommand.Parameters.AddRange(ps);


            //foreach (SqlParameter p in ps)
            //{
            //    sda.SelectCommand.Parameters.Add(p);
            //}


            DataSet ds = new DataSet();
            //将查询出的所有表，（按查询顺序）填充到数据集中
            sda.Fill(ds);
            return ds;

        }


        /// <summary>
        /// 执行数据库操作（查询一行一列），返回一行一列
        /// </summary>
        /// <param name="constr">数据库连接字符串</param>
        /// <param name="cmdstr">T-SQL语句 或 存储过程</param>
        /// <param name="type">操作类型</param>
        /// <param name="ps">参数列表</param>
        /// <returns>返回一行一列</returns>
        /// 

        public static object ExecuteScalar(string constr, string cmdstr, CommandType type, params SqlParameter[] ps)
        {
            SqlConnection conn = new SqlConnection(constr);


            conn.Open();
            SqlCommand cmd = new SqlCommand(cmdstr, conn);
            cmd.CommandType = type;


            cmd.Parameters.AddRange(ps);


            object o = cmd.ExecuteScalar();

            conn.Close();


            return o;
        }

        public static SqlDataReader ExecuteReader(string constr, string cmdstr, CommandType type, params SqlParameter[] ps)
        {
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(cmdstr, conn);

            cmd.CommandType = type;


            if (ps != null)
                cmd.Parameters.AddRange(ps);


            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            return dr;


        }
        public static SqlDataReader ExecuteReader(string constr, string cmdstr, params SqlParameter[] ps)
        {
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(cmdstr, conn);


            if (ps != null)
                cmd.Parameters.AddRange(ps);


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