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 RuPengWang.DAL
{
    public class SqlHelper
    {
        private static readonly string constr = ConfigurationManager.AppSettings["constr"];

      
        /// <summary>
        /// 数据表的增、删、改；
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] sp)
        {
            using (SqlConnection con = new SqlConnection(SqlHelper.constr))
            {
               
                return SqlHelper.ExecuteNonQuery(con, sql, sp);
            }

        }

        public static int ExecuteNonQuery(SqlConnection conn, string sql, params SqlParameter[] ps)
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                conn.Open();
                cmd.Parameters.AddRange(ps);
               return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行一条返回第一条记录第一列的SqlCommand命令，通过专用的连接字符串。 
        /// 使用参数数组提供参数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="ps"></param>
        /// <returns>返回一个object数据</returns>
        public static object ExecuteScale(SqlConnection conn,string sql, params SqlParameter[] ps)
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                conn.Open();
                cmd.Parameters.AddRange(ps);
                return cmd.ExecuteScalar();
            }
        
        }
        /// <summary>
        /// 执行一条返回第一条记录第一列的SqlCommand命令，通过专用的连接字符串。 
        /// 使用参数数组提供参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">参数</param>
        /// <returns>返回一个object数据</returns>
        public static object ExecuteScale(string sql, params SqlParameter[] ps)
        {
            using (SqlConnection conn = new SqlConnection(SqlHelper.constr))
            {
                return SqlHelper.ExecuteScale(conn, sql, ps);
            }
        }
        /// <summary>
        /// 该方法用于读取数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="sp">sql参数</param>
        /// <returns>SqlDataReader类型</returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] sp)
        {
            SqlConnection conn = new SqlConnection(constr);
           using(SqlCommand cmd=new SqlCommand(sql,conn))
           {
            cmd.Parameters.AddRange(sp);
               try{

                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
               }
               catch(Exception ex){

               conn.Close();
               conn.Dispose();
               throw ex;
               
               }
           
           }
        }

        /// <summary>
        /// 读取数据返回的是datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ps"></param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteTable(string sql, params SqlParameter[] ps)
        {
            DataTable dt=new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(sql, constr))
            {
                if (ps != null)
                {
                    da.SelectCommand.Parameters.AddRange(ps);
                }
                da.Fill(dt);
            
            }
            return dt;
        }
    
    }
}

　　

 

转载于:https://www.cnblogs.com/ZX-LMY/p/5822863.html