用泛型创建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));

 谢谢阅读,一起学习探讨!与君共勉!

 

posted @ 2017-12-25 12:08  CaptainBruke  阅读(...)  评论(...编辑  收藏