使用泛型，可以构建对数据库单表的基本增删改查。
首先有一数据库 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() > ;
}
} /// <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() > ;
}
} /// <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>();//查到芒果的所有信息 //修改
oneFruits.Name = "芒果：修改";
bool updateRes = SqlServerHelper.Update<Dt_Fruits>(oneFruits);//修改了芒果--》芒果：修改 //添加
var newUser = new Dt_User()
{
Name = "博客君",
Sex = "男",
Mob = "",
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>();//查到芒果的所有信息
查询到的数据就不截图了
修改某一id 的数据
//修改
oneFruits.Name = "芒果：修改";
bool updateRes = SqlServerHelper.Update<Dt_Fruits>(oneFruits);//修改了芒果--》芒果：修改
添加一条数据
//添加
var newUser = new Dt_User()
{
Name = "博客君",
Sex = "男",
Mob = "",
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>(,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 = "",
Birthday = DateTime.Now,
AddTime = DateTime.Now
};
bool addRes = SqlServerHelper.Insert<Dt_User>(newUser,nameof(Dt_Fruits.Id));
谢谢阅读，一起学习探讨！与君共勉！