加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

自制SQLServer CRUD处理类 方便无法使用EF或者其他ORM框架时使用

发布时间：2020-12-12 14:17:07 所属栏目：MsSql教程 来源：网络整理
导读：public class DBHelper:IDisposable { /// summary /// 连接字符串 /// /summary private string connString { get ; set ; } public DBHelper() { this .connString = System.Configuration.ConfigurationManager.ConnectionStrings[ " ConnectionString "
   public class DBHelper:IDisposable
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string connString { get; set; }

        public DBHelper()
        {
            this.connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 
        }

        public DBHelper(string connectionString)
        {
            this.connString = connectionString;
        }

        /// <summary>
        /// 得到一个连接
        /// </summary>
        public SqlConnection GetConn()
        {
            SqlConnection conn = new SqlConnection(this.connString);         
            conn.Open();
            return conn;
        }



        /// <summary>
        /// 获取一个DataReader
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public SqlDataReader GetDataReader(string sql)
        {
           
            using (SqlCommand cmd = new SqlCommand(sql,GetConn()))
            {
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        /// <summary>
        /// 获取一个DataReader,带参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public SqlDataReader GetDataReader(string sql,SqlParameter[] param)
        {
        
            using (SqlCommand cmd = new SqlCommand(sql,GetConn()))
            {
                if (param != null && param.Length > 0)
                    cmd.Parameters.AddRange(param);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
        }


  
        /// <summary>
        /// 获取一个DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
              using (SqlConnection conn = GetConn())
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {                
                    using (SqlDataAdapter Dap = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        Dap.Fill(dt);
                        cmd.Parameters.Clear();
                        return dt;
                    }
                }
            }
              
            
        }
        /// <summary>
        /// 获取一个DataTable,带参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql,SqlParameter[] param)
        {

            using (SqlConnection conn = GetConn())
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    if (param != null && param.Length > 0)
                        cmd.Parameters.AddRange(param);
                    using (SqlDataAdapter Dap = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        Dap.Fill(dt);
                        cmd.Parameters.Clear();
                        return dt;
                    }
                }
            }
        }
        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(string sql)
        {
          
            using (SqlConnection conn = GetConn())
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 执行SQL(事务)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(Dictionary<string,SqlParameter[]> sqlList)
        {
            using (SqlConnection conn = GetConn())
            {
                int i = 0;
                SqlTransaction trans = conn.BeginTransaction();
                try
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        foreach (var sql in sqlList)
                        {

                            cmd.Connection = conn;
                            cmd.CommandText = sql.Key;
                            if (sql.Value != null && sql.Value.Length > 0)
                                cmd.Parameters.AddRange(sql.Value.ToArray());
                            i += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                    trans.Commit();
                    return i;
                }
                catch (SqlException err)
                {
                    trans.Rollback();
                    throw new Exception(err.Message);
                }
            }
        }
        /// <summary>
        /// 执行SQL(事务)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string ExecuteScalar(Dictionary<string,SqlParameter[]> sqlList)
        {
            using (SqlConnection conn = GetConn())
            {
                object value = null;
                SqlTransaction trans = conn.BeginTransaction();
                try
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        foreach (var sql in sqlList)
                        {
                            cmd.Connection = conn;
                            cmd.CommandText = sql.Key;
                            if (sql.Value != null && sql.Value.Length > 0)
                                cmd.Parameters.AddRange(sql.Value.ToArray());
                            value = cmd.ExecuteScalar();
                            cmd.Parameters.Clear();
                        }
                    }
                    trans.Commit();
                    return value != null ? value.ToString() : string.Empty;
                }
                catch (SqlException err)
                {
                    trans.Rollback();
                    throw new Exception(err.Message);
                }
            }
        }
        /// <summary>
        /// 执行带参数的SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Execute(string sql,SqlParameter[] param)
        {
         
            using (SqlConnection conn = GetConn())
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    cmd.Parameters.AddRange(param);
                    int r = -1;
                    r = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return r;
                }
            }
        }

        /// <summary>
        /// 执行带参数的SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public string ExecuteScalar(string sql,SqlParameter[] param)
        {
            
            using (SqlConnection conn = GetConn())
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    cmd.Parameters.AddRange(param);
                    string r = string.Empty;
                    object obj = cmd.ExecuteScalar();
                    r = obj != null ? obj.ToString() : string.Empty;
                    cmd.Parameters.Clear();
                    return r;
                }
            }
        }
        /// <summary>
        /// 执行带参数的SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Execute(string sql,SqlParameter[] param,out int identity)
        {
          
            using (SqlConnection conn = GetConn())
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    if (param != null && param.Length > 0)
                        cmd.Parameters.AddRange(param);
                    int i = cmd.ExecuteNonQuery();
                    SqlCommand cmd1 = new SqlCommand("SELECT @@IDENTITY",conn);
                    object MaxIDObject = cmd1.ExecuteScalar();
                    int MaxID;
                    identity = MaxIDObject != null && int.TryParse(MaxIDObject.ToString(),out MaxID) ? MaxID : -1;
                    cmd1.Dispose();
                    cmd.Parameters.Clear();
                    return i;
                }
            }
        }
        /// <summary>
        /// 得到一个字段的值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetFieldValue(string sql)
        {
           
            using (SqlConnection conn = GetConn())
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return obj != null ? obj.ToString() : string.Empty;
                }
            }
        }
        /// <summary>
        /// 得到一个字段的值,带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetFieldValue(string sql,SqlParameter[] param)
        {
           
            using (SqlConnection conn = GetConn())
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    if (param != null && param.Length > 0)
                        cmd.Parameters.AddRange(param);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return obj != null ? obj.ToString() : string.Empty;
                }
            }
        }


        /// <summary>
        /// 释放数据库连接
        /// </summary>
        public void Dispose()
        {
            if (new SqlConnection(this.connString) is SqlConnection)
            {
                if (new SqlConnection(this.connString) != null && new SqlConnection(this.connString).State != ConnectionState.Closed)
                {
                    try
                    {
                        new SqlConnection(this.connString).Close();
                    }
                    catch (SqlException err)
                    { }
                }
                new SqlConnection(this.connString).Dispose();
            }
        }
    }
/// <summary>
/// 数据处理辅助累,包含增删改查!
/// </summary>
public class DBCom
{
    public DBCom()
    {       
    }
    private List<Dictionary<string,object>> Getproperties<T>()
    {
        System.Reflection.PropertyInfo[] properties =
      typeof(T).GetProperties(System.Reflection.BindingFlags.Instance |
        System.Reflection.BindingFlags.Public);
        var ts = typeof(T);
        Dictionary<string,object> dicatpk = new Dictionary<string,object>();
        Dictionary<string,object> dicpk = new Dictionary<string,object>();
        foreach (System.Reflection.PropertyInfo item in properties)
        {
            string name = item.Name;
            var pName = ts.GetProperty(name);
            var attrs = pName.GetCustomAttributes(true);        
            string DisplayName = "";
            foreach (DisplayNameAttribute attr in attrs)
            {
                DisplayName = attr.DisplayName;
            }
            if (DisplayName.ToLower().Equals("atpk"))
            {
                dicatpk.Add(name,"");
            }
            if (DisplayName.ToLower().Equals("pk"))
            {
                dicpk.Add(name,"");
            }
        }
        List<Dictionary<string,object>> list = new List<Dictionary<string,object>>();
        list.Add(dicatpk);
        list.Add(dicpk);
        return list;
    }
    private List<Dictionary<string,object>> Getproperties<T>(T t)
    {
        System.Reflection.PropertyInfo[] properties =
        t.GetType().GetProperties(System.Reflection.BindingFlags.Instance |
        System.Reflection.BindingFlags.Public);
        var ts = typeof(T);
        Dictionary<string,object> dic = new Dictionary<string,object>();
        foreach (System.Reflection.PropertyInfo item in properties)
        {
            string name = item.Name;
            object value = item.GetValue(t,null);
            var pName = ts.GetProperty(name);
            var attrs = pName.GetCustomAttributes(true);      
            string DisplayName = "";
            foreach (DisplayNameAttribute attr in attrs)
            {
                DisplayName = attr.DisplayName;
            }
            if (value != null && !DisplayName.ToLower().Equals("atpk"))
            {
                dic.Add(name,value);
            }
            if (DisplayName.ToLower().Equals("atpk"))
            {
                dicatpk.Add(name,value);
            }
            if (DisplayName.ToLower().Equals("pk"))
            {
                dicpk.Add(name,value);
            }
        }
        List<Dictionary<string,object>>();
        list.Add(dic);
        list.Add(dicatpk);
        list.Add(dicpk);
        return list;
    }
    /// <summary>
    /// 根据实体向数据库中添加数据
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="t">实体</param>
    /// <returns>影响记录数</returns>
    public int Add<T>(T t)
    {
        var list = Getproperties<T>(t);
        if (list.Count > 0)
        {
            var ts = typeof(T);
            var dic = list[0];
            string tStr = "insert into " + ts.Name + " (";
            foreach (var item in dic)
            {
                tStr += string.Format("{0}",item.Key) + ",";
            }
            tStr = tStr.TrimEnd(‘,‘) + ") values (";
            foreach (var item in dic)
            {
                tStr += string.Format("{0}","@" + item.Key) + ",‘) + ") ;";
            List<SqlParameter> listPM = new List<SqlParameter>();
            foreach (var item in dic)
            {
                listPM.Add(new SqlParameter("@" + item.Key,item.Value));
            }
            return new DBHelper().Execute(tStr,listPM.ToArray());
        }
        else
        {
            throw new Exception("没找到数据表");
        }
    }

    /// <summary>
    /// 多条件修改实体内容
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="t">实体</param>
    /// <param name="wkey">修改数据必须满足的字段,用,隔开例: id,name</param>
    /// <returns>影响行数</returns>
    public int Update<T>(T t,string wkey)
    {
        var list = Getproperties<T>(t);
        if (list.Count > 0)
        {
            var ts = typeof(T);
            var dic = list[0];
            string tStr = "UPDATE  " + ts.Name + " SET ";
            foreach (var item in dic)
            {
                tStr += string.Format("{0}",item.Key) + "=" + string.Format("{0}",‘);
            string where = " 1=1 ";         
            if(wkey!=null){
                var skey = wkey.Split(‘,‘);
                foreach (var item in skey)
                {
                    foreach (var idic in dic)
                    {
                        if (idic.Key.ToLower().Equals(item.Trim().ToLower())) {
                            where += " and " + string.Format("{0}",idic.Key) + "=" + string.Format("{0}","@" + idic.Key);
                        }                       
                    }                    
                }            
            }
            tStr += " where " + where + ";";
            List<SqlParameter> listPM = new List<SqlParameter>();
            foreach (var item in dic)
            {
                listPM.Add(new SqlParameter("@" + item.Key,item.Value));
            }
            if (list.Count > 1)
            {
                foreach (var item in list[1])
                {
                    listPM.Add(new SqlParameter("@" + item.Key,item.Value));
                }
            }
            return new DBHelper().Execute(tStr,listPM.ToArray());
        }
        else
        {
            throw new Exception("没找到数据表");
        }
    }

    /// <summary>
    /// 根据主键修改实体
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="t">实体</param>
    /// <returns>影响记录数</returns>
    public int Update<T>(T t)
    {
        var list = Getproperties<T>(t);
        if (list.Count > 0)
        {
            var ts = typeof(T);
            var dic = list[0];
            string tStr = "UPDATE  " + ts.Name + " SET ";
            foreach (var item in dic)
            {
                tStr += string.Format("{0}",‘);
            string where = " 1=1 ";
            if (list.Count > 1)
            {
                foreach (var item in list[1])
                {
                    where += " and " + string.Format("{0}","@" + item.Key);
                }
            }
            if (list.Count > 2)
            {
                foreach (var item in list[2])
                {
                    where += " and " + string.Format("{0}","@" + item.Key);
                }
            }
            tStr += " where " + where + ";";
            List<SqlParameter> listPM = new List<SqlParameter>();
            foreach (var item in dic)
            {
                listPM.Add(new SqlParameter("@" + item.Key,listPM.ToArray());
        }
        else
        {
            throw new Exception("没找到数据表");
        }
    }

    /// <summary>
    /// 根据主键删除实体
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="pk">主键</param>
    /// <returns>影响行数</returns>
    public int Delete<T>(object pk)
    {
        var list = Getproperties<T>();
        var ts = typeof(T);
        string tStr = "delete     from " + ts.Name;
        string where = " 1=1 ";
        if (list.Count > 0)
        {
            foreach (var item in list[0])
            {
                where += " and " + string.Format("{0}","@" + item.Key);
            }
        }
        if (list.Count > 1)
        {
            foreach (var item in list[1])
            {
                where += " and " + string.Format("{0}","@" + item.Key);
            }
        }
        tStr += " where " + where + ";";
        List<SqlParameter> listPM = new List<SqlParameter>();
        if (list.Count > 0)
        {
            foreach (var item in list[0])
            {
                listPM.Add(new SqlParameter("@" + item.Key,pk));
            }
        }
        if (list.Count > 1)
        {
            foreach (var item in list[1])
            {
                listPM.Add(new SqlParameter("@" + item.Key,pk));
            }
        }
        return  new DBHelper().Execute(tStr,listPM.ToArray());
    }

    /// <summary>
    /// 根据满足的实体字段删除实体
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="t">实体数据</param>
    /// <param name="wkey">删除数据必须满足的字段,name</param>
    /// <returns>影响行数</returns>
    public int Delete<T>(T t,string wkey)
    {
        var list = Getproperties<T>(t);
        if (list.Count > 0)
        {
            var ts = typeof(T);
            var dic = list[0];
            string tStr = "delete from  " + ts.Name + "  ";
          
            tStr = tStr.TrimEnd(‘,‘);
            string where = " 1=1 ";
            if (wkey != null)
            {
                var skey = wkey.Split(‘,‘);
                foreach (var item in skey)
                {
                    foreach (var idic in dic)
                    {
                        if (idic.Key.ToLower().Equals(item.Trim().ToLower()))
                        {
                            where += " and " + string.Format("{0}","@" + idic.Key);
                        }
                    }
                }
            }
            tStr += " where " + where + ";";
            List<SqlParameter> listPM = new List<SqlParameter>();
            foreach (var item in dic)
            {
                listPM.Add(new SqlParameter("@" + item.Key,listPM.ToArray());
        }
        else
        {
            throw new Exception("没找到数据表");
        }
    }


    /// <summary>
    /// 获取当前实体对应表的所有记录
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="names">查询的字段,隔开 例如:id,name</param>
    /// <param name="order">排序 例如:id desc</param>
    /// <returns>实体集合</returns>
    public List<T> GetAll<T>(string names = "*",string order = "")
    {
        var list = Getproperties<T>();
        var ts = typeof(T);
        string tStr = "select   " + names + " from " + ts.Name;
        string orderby = " ";
        if (order.Trim().Length > 3)
        {
            orderby = " order by " + order;
        }
        tStr += orderby + ";";
        return ToList<T>(new DBHelper().GetDataTable(tStr));
    }

    /// <summary>
    /// 根据主键返回一个实体
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="pk">主键值</param>
    /// <param name="names">查询的字段,name</param>
    /// <returns>一个实体</returns>
    public T GetOne<T>(object pk,string names = "*") where T : new()
    {
        var list = Get<T>(pk,names);

        if (list != null)
        {
            return list[0];
        }
        else
        {
            return new T();
        }
    }

    /// <summary>
    /// 根据主键返回一个实体集合
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="pk">主键值</param>
    /// <param name="names">查询的字段,name</param>
    /// <returns>一个实体集合</returns>
    public List<T> Get<T>(object pk,string names = "*")
    {
        var list = Getproperties<T>();
        var ts = typeof(T);
        string tStr = "select   " + names + " from " + ts.Name;
        string where = " 1=1 ";
        if (list.Count > 0)
        {
            foreach (var item in list[0])
            {
                where += " and " + string.Format("{0}",pk));
            }
        }
        return ToList<T>(new DBHelper().GetDataTable(tStr,listPM.ToArray()));

    }

    /// <summary>
    /// 根据sql返回一个实体集合
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="Sql">sql语句</param>
    /// <param name="listPM">sql参数</param>
    /// <returns>实体集合</returns>
    public List<T> GetSql<T>(string Sql,List<SqlParameter> listPM)
    {
        return ToList<T>(new DBHelper().GetDataTable(Sql,listPM.ToArray()));
    }
    /// <summary>
    /// 根据sql返回一个实体集合
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="Sql">sql语句</param> 
    /// <returns>实体集合</returns>
    public List<T> GetSql<T>(string Sql)
    {
        return ToList<T>(new DBHelper().GetDataTable(Sql));
    }
    /// <summary>
    /// DataTable转换为实体集合
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="dt">DataTable</param>
    /// <returns>实体集合</returns>
    public static List<T> ToList<T>(DataTable dt)
    {
        if (dt == null || dt.Rows.Count == 0) { return new List<T>(); }
        List<T> mList = new List<T>();
        PropertyInfo[] pInfos = typeof(T).GetProperties();
        foreach (DataRow dr in dt.Rows)
        {
            T model = (T)Activator.CreateInstance(typeof(T));
            foreach (PropertyInfo info in pInfos)
            {
                try
                {
                    if (dr[info.Name] == DBNull.Value)
                    {
                        continue;
                    }
                }
                catch { continue; }
                try
                {
                    info.SetValue(model,dr[info.Name],null);
                }
                catch
                {
                    try
                    {
                        var FullName = info.PropertyType.FullName;
                        if (FullName.Contains("String"))
                        {
                            info.SetValue(model,dr[info.Name].ToString(),null);
                        }
                        else if (FullName.Contains("Int32"))
                        {
                            info.SetValue(model,Convert.ToInt32(dr[info.Name]),null);
                        }
                        else if (FullName.Contains("Int64"))
                        {
                            info.SetValue(model,Convert.ToInt64(dr[info.Name]),null);
                        }
                        else if (FullName.Contains("Decimal"))
                        {
                            info.SetValue(model,Convert.ToDecimal(dr[info.Name]),null);
                        }
                        else if (FullName.Contains("Double"))
                        {
                            info.SetValue(model,Convert.ToDouble(dr[info.Name]),null);
                        }
                        else if (FullName.Contains("Single"))
                        {
                            info.SetValue(model,Convert.ToSingle(dr[info.Name]),null);
                        }
                        else if (FullName.Contains("DateTime"))
                        {
                            info.SetValue(model,Convert.ToDateTime(dr[info.Name]),null);
                        }
                        else if (FullName.Contains("Guid"))
                        {
                            info.SetValue(model,new Guid(dr[info.Name].ToString()),null);
                        }
                        else
                        {
                            info.SetValue(model,null);
                        }

                    }
                    catch (Exception ex)
                    {
                        throw new Exception(dr.Table.Columns[info.Name].ColumnName + "(" + info.PropertyType.Name + ")" + "=" + dr[info.Name] + "=" + ex.Message);
                    }
                }
            }
            mList.Add(model);
        }
        return mList;
    }
}

?

//用法新建一个实体,继承辅助类即可

/// <summary>
/// cProject 的摘要说明
/// </summary>
[Serializable]
public class Project: DBCom
{
    public Project()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }

    [DisplayName("pk")]//主键pk 自增主键atpk
    public int id { get; set; }
    public string projectName { get; set; }
    public string tagCode { get; set; }


}

//在要使用得地方调用
var project = new Project();
project.projectName = "xx";
project.Add<Project>(project);
project.Update<Project>(project);
project=project.GetOne<Project>(1);
var list0=project.GetAll<Project>();
var list1=project.Get<Project>(1);
var list2=project.GetSql<Project>("select id from Project");//如果只需要填装部分字段 只需查出需要字段即可 多表联合查询也可用此方法包装

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同