用泛型创建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));
谢谢阅读，一起学习探讨！与君共勉！
