2021-05-03

自己写的帮助类DbHelper(萌新一枚，第一次写博客，各位大佬请指正（：）)

 

1、将数据库连接语句写在配置文件config中

  

//数据库连接自己设置吧
<add key="connString" value="Server =.; User Id =sa; Pwd=root;DataBase=student"/>
  </appSettings>
  <system.codedom>web）//多余代码，为了方便知道代码块放哪

2、加入了一些重载，可用性更高，忘记加入try()... catch()

注意：1、引用的时候改这个命名空间，与自己项目名称一样就好

           2、有一些使用了静态方法static，根据实际情况自己改,看用不用静态方法

 

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

namespace DataBind     //引用的时候改这个命名空间，与自己项目名称一样
{
    public class Dbhelper
    {
       //数据库连接
        static string url = ConfigurationSettings.AppSettings["connString"];
        /// <summary>
        ///数据插入更新,返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] par)
        {
            using (SqlConnection conn = new SqlConnection(url))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = type;
                    if (par != null)
                    {
                        cmd.Parameters.AddRange(par);
                    }
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception ex)
                    {

                        throw ex;
                    }
                    int succ = cmd.ExecuteNonQuery();
                    return succ;
                }
            }
        }
        /// <summary>
        /// 数据更新删除
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        static public int ExecuteNonQuery(string sql, Hashtable ht)
        {

            using (SqlConnection conn = new SqlConnection(url))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (ht != null)
                    {
                        SqlParameter[] par = new SqlParameter[ht.Count];
                        int index = 0;
                        foreach (DictionaryEntry item in ht)
                        {
                            par[index++] = new SqlParameter(item.Key.ToString(), item.Value);
                        }
                        cmd.Parameters.AddRange(par);
                    }
                    try
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.Text;
                        return cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

                }
            }
        }
        /// <summary>
        /// 数据的更新删除插入，返回受影响行数(重载)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="par"></param>
        /// <returns></returns>
       static int ExecuteNonQuery(string sql, Dictionary<string, object> par)
        {
            using (SqlConnection conn = new SqlConnection(url))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    if (par != null)
                    {
                        SqlParameter[] sqlParametes = new SqlParameter[par.Count];
                        int index = 0;
                        foreach (var item in par)
                        {
                            sqlParametes[index++] = new SqlParameter(item.Key, item.Value);
                        }
                        cmd.Parameters.AddRange(sqlParametes);
                    }
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

                    int succ = cmd.ExecuteNonQuery();//执行sql语句
                    return succ;
                }
            }
        }
     

        /// <summary>
        /// 返回单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, CommandType type, params SqlParameter[] par)
        {
            using (SqlConnection conn = new SqlConnection(url))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = type;
                    if (par != null)
                    {
                        cmd.Parameters.AddRange(par);
                    }
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 重载ExecuteScalar
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        static public int ExecuteScalar(string sql, Hashtable ht)
        {

            using (SqlConnection conn = new SqlConnection(url))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    cmd.CommandType = CommandType.Text;
                    if (ht != null)
                    {
                        SqlParameter[] par = new SqlParameter[ht.Count];
                        int index = 0;
                        foreach (DictionaryEntry item in ht)
                        {
                            par[index++] = new SqlParameter(item.Key.ToString(), item.Value);
                        }
                        cmd.Parameters.AddRange(par);
                    }
                    return (int)cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 重载ExecuteScalar2
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public int ExecuteScalar(string sql, Dictionary<string, object> dt)
        {

            using (SqlConnection conn = new SqlConnection(url))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    cmd.CommandType = CommandType.Text;
                    if (dt != null)
                    {
                        SqlParameter[] sqls = new SqlParameter[dt.Count];
                        int index = 0;
                        foreach (var item in dt)
                        {
                            sqls[index++] = new SqlParameter(item.Key, item.Value);
                        }
                        cmd.Parameters.AddRange(sqls);
                    }
                    return (int)cmd.ExecuteScalar();
                }
            }
        }
        //返回数据集合,用于数据查询返回多条数据
        /// <summary>
        ///返回集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public List<T> GetPurchaseOrPurchaseLine<T>(string sql, Dictionary<string, object> paramList)
        {
            Type type = typeof(T);

            List<T> list = new List<T>();     //object集合
            using (SqlConnection conn = new SqlConnection(url))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();//打开数据库

                if (paramList != null)
                {
                    SqlParameter[] sqlParameter = new SqlParameter[paramList.Count];
                    int index = 0;
                    foreach (var item in paramList)
                    {
                        sqlParameter[index] = new SqlParameter(item.Key, item.Value);
                        index++;
                    }
                    cmd.Parameters.AddRange(sqlParameter);   //添加参数
                }
                SqlDataReader sda = cmd.ExecuteReader();        //执行sql语句
                while (sda.Read())
                {
                    object Object = Activator.CreateInstance(type);//通过反射创建实例
                    foreach (var prop in type.GetProperties())
                    {
                        prop.SetValue(Object, sda[prop.Name]);
                    }
                    list.Add((T)Object);//转换成自己的类
                }
                sda.Close();        //关闭读取缓存数据
            }
            return list;
        }

        /// <summary>
        /// 返回默认或者查找到的ID的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        internal T QueryFirstOrDefault<T>(string sql, Dictionary<string, object> paramList)
        {
            Type type = typeof(T);
            object Object = Activator.CreateInstance(type);//通过反射创建实例            
            try
            {
                using (SqlConnection conn = new SqlConnection(url))
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.CommandType = CommandType.Text;
                    conn.Open();//打开数据库

                    if (paramList != null)
                    {
                        SqlParameter[] sqlParameter = new SqlParameter[paramList.Count];
                        int index = 0;
                        foreach (var item in paramList)
                        {
                            sqlParameter[index] = new SqlParameter(item.Key, item.Value);
                            index++;
                        }
                        cmd.Parameters.AddRange(sqlParameter);   //添加参数
                    }
                    SqlDataReader sda = cmd.ExecuteReader();        //执行sql语句
                    while (sda.Read())
                    {
                        foreach (var prop in type.GetProperties())
                        {
                            //给属性赋值
                            prop.SetValue(Object, sda[prop.Name]);
                        }
                    }
                    sda.Close();        //关闭读取缓存数据              
                }
                return (T)Object;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 返回表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        static public DataTable ExecuteDataTable(string sql, Dictionary<string, object> paramList)
        {
            try
            {
                DataTable ds = new DataTable();//创建DataSet实例
                using (SqlConnection conn = new SqlConnection(url))
                {
                    conn.Open();//打开数据库
                    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                    if (paramList != null)
                    {
                        SqlParameter[] sqlParameter = new SqlParameter[paramList.Count];
                        int index = 0;
                        foreach (var item in paramList)
                        {
                            sqlParameter[index] = new SqlParameter(item.Key, item.Value);
                            index++;
                        }
                        sda.SelectCommand.Parameters.AddRange(sqlParameter);
                    }

                    sda.Fill(ds);//使用DataAdapter的Fill方法(填充)，调用SELECT命令
                    return ds;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}