用泛型创建SqlServerHelper类实现增删改查（一）

使用泛型，可以构建对数据库单表的基本增删改查。

首先有一数据库 Test_SqlServerHelper ，有2表

接下来创建项目，对数据库进行增删改查。

直接贴代码：（SqlServerHelper.cs）

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

namespace SqlServerHelper
{
  public static class SqlServerHelper
  {
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static readonly string connString = ConfigurationManager.ConnectionStrings["Test"].ToString();  //数据库连接字符

    /// <summary>
    /// 根据id查询对象
    /// </summary>
    /// <typeparam name="T">对象类型</typeparam>
    /// <param name="id">对象实例的Id(泛型：类型int或string)</param>
    /// <param name="idName">条件的字段名称（主键名）</param>
    /// <returns></returns>
    public static T QueryById<T, I>(I id, string idName = "Id")
    {
      Type type = typeof(T);
      string columnString = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
      string sqlString = string.Format("select {0} from [{1}] where {2}={3}", columnString, type.Name, idName, id.GetType().Name.ToString() == "String" ? ("'" + id.ToString() + "'") : id.ToString());
      var t = Activator.CreateInstance(type);
      using (SqlConnection conn = new SqlConnection(connString))
      {
        conn.Open();
        SqlCommand sqlCommand = new SqlCommand(sqlString, conn);
        SqlDataReader reader = sqlCommand.ExecuteReader();
        reader.Read();
        SetValueByProperties(type, reader, t);
      }
      return (T)t;
    }

    /// <summary>
    /// 获取数据列表
    /// </summary>
    /// <typeparam name="T">对象类型</typeparam>
    /// <returns></returns>
    public static List<T> QueryAll<T>()
    {
      Type type = typeof(T);
      string columnString = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
      string sqlString = string.Format("select {0} from [{1}]", columnString, type.Name);
      List<T> dataList = new List<T>();
      using (SqlConnection conn=new SqlConnection(connString))
      {
        conn.Open();
        SqlCommand sqlCommand = new SqlCommand(sqlString,conn);
        SqlDataReader reader = sqlCommand.ExecuteReader();
        if (reader.HasRows)
        {
          while (reader.Read())
          {
            var t = Activator.CreateInstance(type);
            SetValueByProperties(type, reader, t);
            dataList.Add((T)t);
          }
        }
        else
        {
          return null;
        }
      }
      return dataList;
    }

    /// <summary>
    /// 插入对象
    /// </summary>
    /// <typeparam name="T">对象类型</typeparam>
    /// <param name="t">对象实例</param>
    /// <param name="idName">不插入的字段（自增键名）</param>
    /// <returns></returns>
    public static bool Insert<T>(T t, string idName = "Id")
    {
      Type type = typeof(T);
      string sqlString = "insert [{0}] ({1}) values ({2})";
      string columnString = string.Join(",", type.GetProperties().Where(p => p.Name != idName).Select(p => string.Format("[{0}]", p.Name)));
      string valueString = string.Join(",", type.GetProperties().Where(p => p.Name != idName).Select(p => string.Format("@{0}", p.Name)));
      sqlString = string.Format(sqlString, type.Name, columnString, valueString);
      using (SqlConnection conn = new SqlConnection(connString))
      {
        conn.Open();
        SqlCommand sqlCommand = new SqlCommand(sqlString, conn);
        SqlParameter[] sqlParameter = type.GetProperties().Where(p => p.Name != idName).Select(p=>new SqlParameter(string.Format("@{0}",p.Name),p.GetValue(t,null)??DBNull.Value)).ToArray();
        sqlCommand.Parameters.AddRange(sqlParameter);
        return sqlCommand.ExecuteNonQuery() > 0;
      }
    }

    /// <summary>
    /// 修改对象
    /// </summary>
    /// <typeparam name="T">对象类型</typeparam>
    /// <param name="t">对象实例</param>
    /// <param name="idName">自增键名或条件名</param>
    /// <returns></returns>
    public static bool Update<T>(T t, string idName = "Id")
    {
      Type type = typeof(T);
      string sqlString = "update [{0}] set {1} where {2}={3}";
      string setString = string.Join(",", type.GetProperties().Where(p => p.Name != idName).Select(p => string.Format("[{0}]=@{0}", p.Name)));
      sqlString = string.Format(sqlString, type.Name, setString, idName,"@"+idName);
      using (SqlConnection conn = new SqlConnection(connString))
      {
        conn.Open();
        SqlCommand sqlCommand = new SqlCommand(sqlString, conn);
        SqlParameter[] sqlParameter = type.GetProperties().Select(p => new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(t, null) ?? DBNull.Value)).ToArray();
        sqlCommand.Parameters.AddRange(sqlParameter);
        return sqlCommand.ExecuteNonQuery() > 0;
      }
    }

    /// <summary>
    /// 设置值by属性（SQLreader）
    /// </summary>
    /// <param name="type">对象类型</param>
    /// <param name="reader">sqlreader</param>
    /// <param name="t">对象</param>
    private static void SetValueByProperties(Type type, SqlDataReader reader, object t)
    {
      foreach (var item in type.GetProperties())
      {
        if (reader[item.Name] is DBNull) //判空
        {
          item.SetValue(t, null);
        }
        else
        {
          item.SetValue(t, reader[item.Name]);
        }
      }
    }
  }
}

这里使用二个数据库表类实体：（我是通过工具直接在数据库上导出来的）

Dt_Fruits.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace SqlServerHelper
{
    public class Dt_Fruits
    {
        private int id;
        public int Id
        {
            get { return id; }
            set { id = value; }
        }
    
        private string name;
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
    
        private int sort;
        public int Sort
        {
            get { return sort; }
            set { sort = value; }
        }
    
        private DateTime addTime;
        public DateTime AddTime
        {
            get { return addTime; }
            set { addTime = value; }
        }
    }
}

Dt_User.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace SqlServerHelper
{
    public class Dt_User
    {
        private int id;
        public int Id
        {
            get { return id; }
            set { id = value; }
        }
    
        private string name;
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
    
        private string mob;
        public string Mob
        {
            get { return mob; }
            set { mob = value; }
        }
    
        private string sex;
        public string Sex
        {
            get { return sex; }
            set { sex = value; }
        }
    
        private DateTime birthday;
        public DateTime Birthday
        {
            get { return birthday; }
            set { birthday = value; }
        }
    
        private DateTime addTime;
        public DateTime AddTime
        {
            get { return addTime; }
            set { addTime = value; }
        }
    }
}

 以上都是准备工作，准备工作做好了，接下来就是，面向对象，直接操作类，快速对数据库：增删改查：

直接上代码：

 //查询所有：
      var userList = SqlServerHelper.QueryAll<Dt_User>();

      //查询某一id:
      var oneFruits = SqlServerHelper.QueryById<Dt_Fruits,int>(1);//查到芒果的所有信息

      //修改
      oneFruits.Name = "芒果：修改";
      bool updateRes = SqlServerHelper.Update<Dt_Fruits>(oneFruits);//修改了芒果--》芒果：修改

      //添加
      var newUser = new Dt_User()
      {
        Name = "博客君",
        Sex = "男",
        Mob = "16816816888",
        Birthday = DateTime.Now,
        AddTime=DateTime.Now
      };
      bool addRes = SqlServerHelper.Insert<Dt_User>(newUser);

以下是增删改查的详细讲解：

查询整表  所有（数据量超过5000的不推荐这么查询，一般查询一下类型表什么的）

 //查询所有：
      var userList = SqlServerHelper.QueryAll<Dt_User>();

查询某一id  的数据

 //查询某一id:
      var oneFruits = SqlServerHelper.QueryById<Dt_Fruits,int>(1);//查到芒果的所有信息

查询到的数据就不截图了

修改某一id 的数据

  //修改
      oneFruits.Name = "芒果：修改";
      bool updateRes = SqlServerHelper.Update<Dt_Fruits>(oneFruits);//修改了芒果--》芒果：修改

 添加一条数据 

 //添加
      var newUser = new Dt_User()
      {
        Name = "博客君",
        Sex = "男",
        Mob = "16816816888",
        Birthday = DateTime.Now,
        AddTime=DateTime.Now
      };
      bool addRes = SqlServerHelper.Insert<Dt_User>(newUser);

 最后数据变化：

 

是不是很棒，很轻松的对数据库，简单的增删改查！

这里可能会有博友会说，哎呀，拼接SQL语句的，好像性能，或者某方面看起来不好，其实呀，ADO.net 对于数据库访问，底层也是对SQL语句的封装的，好用就行了。以后如过有新表，就添加一对应新的C#模型就可以了，这种思想就衍生了现在很流行的EF框架哦。

还有！细心的朋友会发现：

我要查询一条数据，为什么还要传int,和1，其实是这样的，如果数据库主键名如果不是“id”，不用这样查询会拼接出错误的sql，因为id名是多变的，所以这里要传。int,表示数据库表的主键是int,对应传就行了。假如有些数据库表主键是“nvarchar，varchar 等”列如大型数据订单id等,这时候传string,（“10000101”）即可。

完整的写法是这样的：

 　　　　//查询所有：
        var userList = SqlServerHelper.QueryAll<Dt_User>();

        //查询某一id:
        var oneFruits = SqlServerHelper.QueryById<Dt_Fruits, int>(1,nameof(Dt_Fruits.Id));//查到芒果的所有信息

        //修改
        oneFruits.Name = "芒果：修改";
        bool updateRes = SqlServerHelper.Update<Dt_Fruits>(oneFruits,nameof(Dt_Fruits.Id));//修改了芒果--》芒果：修改

        //添加
        var newUser = new Dt_User()
        {
          Name = "博客君",
          Sex = "男",
          Mob = "16816816888",
          Birthday = DateTime.Now,
          AddTime = DateTime.Now
        };
        bool addRes = SqlServerHelper.Insert<Dt_User>(newUser,nameof(Dt_Fruits.Id));

 谢谢阅读，一起学习探讨！与君共勉！