C#泛型SQLHelperT类

2023年6月21日发(作者：)

C#泛型SQLHelperT类⽰例【1】1、创建SQLHelper类using c;using uration;using ent;using tion;namespace Db{ public class SQLHelper where T : class, new() { ///

/// 链接数据库字符串 ///

public static string strConnectionString = tionStrings["DataConnectionString"].ToString(); ///

/// 数据库连接 ///

public SqlConnection Connection = null; public SQLHelper() { OpenConnect(); } ///

/// 打开数据库连接 ///

public void OpenConnect() { if (Connection == null || != ) { Connection = new SqlConnection(strConnectionString); (); } } ///

/// 关闭数据库连接 ///

public void CloseConnect() { if (Connection != null && != ) { (); } } #region 对数据库进⾏读写操作 ///

/// 执⾏查询语句 ///

/// SQL语句 /// SQL参数的值 /// public SqlDataReader ExecReader(string strSQL, object obQuery) { SqlCommand command = new SqlCommand(strSQL, Connection); if (obQuery != null) { PropertyInfo[] pis = e().GetProperties(); foreach (var p in pis) { (new SqlParameter(, ue(obQuery, null))); } } SqlDataReader reader = eReader(); return reader; } ///

/// 执⾏返回单值的查询语句 ///

/// SQL语句 /// SQL参数的值 /// public object ExecSingleValue(string strSQL, object obQuery) { SqlCommand command = new SqlCommand(strSQL, Connection); if (obQuery != null) { PropertyInfo[] pis = e().GetProperties(); foreach (var p in pis) { (new SqlParameter(, ue(obQuery, null))); } } return eScalar(); } ///

/// 执⾏⾮查询语句 ///

/// SQL语句 /// SQL参数的值 /// public int ExecNoQuery(string strSQL, object obQuery) { SqlCommand command = new SqlCommand(strSQL, Connection); if (obQuery != null) { PropertyInfo[] pis = e().GetProperties(); foreach (var p in pis) { (new SqlParameter(, ue(obQuery, null))); } } return eNonQuery(); } #endregion #region 返回列表List，利⽤"泛型+反射" ///

/// 获取列表 ///

/// SQL语句 /// SQL参数的值 /// public List GetList(string strSQL, object obQuery) { //调⽤执⾏查询语句函数，返回SqlDataReader SqlDataReader reader = ExecReader(strSQL, obQuery); //定义返回的列表 List list = new List(); //定义T类型的实体 T model = new T(); //获取T类型实体的属性类型和值 PropertyInfo[] pis = e().GetProperties(); //获取数据库返回的列数 int intColCount = ount; //遍历SqlDataReader while (()) { //定义 int value_number = 0; //重新实例化T model = new T(); //从数据库拿出⼀条数据后，循环遍历T类型的属性类型和值 for (int i = 0; i < intColCount; i++) { //判断第⼀列是否为row_number，此为分页使⽤ if (e(i) == "row_number") value_number++; //设置T对应属性的值 pis[i].SetValue(model, ue(value_number), null); value_number++; } //将T添加到列表中 (model); } return list; } #endregion ///

/// 获取分页 ///

/// 总共个数的SQL /// 总共个数的SQL参数的值 /// 分页的SQL /// 分页SQL参数的值 /// 分页编号 /// 分页⼤⼩ /// 返回PagesList public PagedList GetPageList(string strTotalSQL, object obTotalQuery, string strSQL, object obQuery, int intPageIndex, int intPageSize) { //定义分页对象的编号和⼤⼩ PagedList pageList = new PagedList(intPageIndex, intPageSize); //执⾏获取单个值的函数，设置分页对象的总元素 alCount = (int)ExecSingleValue(strTotalSQL, obTotalQuery); //设置分页对象的分页数 if (alCount % intPageSize == 0) es = alCount / intPageSize; else es = alCount / intPageSize + 1; //定义列表，调⽤获取列表的函数获取此分页的元素 List list = GetList(strSQL, obQuery); //将列表元素添加到分页对象当中 ge(list); //设置分页对象是否有上⼀页和下⼀页 tPage = eIndex < es ? true : false; age = eIndex > 1 ? true : false; return pageList; } ///

/// 获取单个实体 ///

/// SQL语句 /// SQL参数的值 /// public T GetTM(string strSQL, object obQuery) { //调⽤执⾏查询语句，返回SqlDataReader SqlDataReader reader = ExecReader(strSQL, obQuery); //新建⼀个T类型 T model = new T(); //获取T类型的属性类型和值 PropertyInfo[] pis = e().GetProperties(); //获取数据库返回数据的列数 int intColCount = ount; //读取数据，填充T if (()) { int value_number = 0; for (int i = 0; i < intColCount; i++) { pis[i].SetValue(model, ue(value_number), null); value_number++; } } return model; } } ///

/// 分页实体类 ///

/// public class PagedList : List { ///

/// 分页编号 ///

public int intPageIndex { get; set; } ///

/// 分页⼤⼩ ///

public int intPageSize { get; set; } ///

/// 分页数 ///

public int intPages { get; set; } ///

/// 总元素的个数 ///

public int intTotalCount { get; set; } ///

/// 此分页元素的个数 ///

public int intCount { get; set; } ///

/// 是否有下⼀页 ///

public bool HasNextPage { get; set; } ///

/// 是否有上⼀页 ///

public bool HasPrPage { get; set; } public PagedList(int intPageIndex, int intPageSize) { eIndex = intPageIndex; eSize = intPageSize; } }}2、调⽤⽅法// 调⽤⽅法：ResumeTM 为数据库表的实体类public List GetResumeByJobInfoId(int intJobInfoId){ SQLHelper helper = new SQLHelper(); string strSQL = @"select * from Resume where FirstJobId=@JobInfoId or SecondJobId=@JobInfoId order by CreateTime desc"; object obQuery = new { JobInfoId = intJobInfoId }; List list = t(strSQL, obQuery); onnect(); return list;}public ResumeTM GetResumeById(int intId){ SQLHelper helper = new SQLHelper(); string strSQL = @"select * from Resume where Id=@Id"; object obQuery = new { Id = intId }; ResumeTM tm = (strSQL, obQuery); onnect(); return tm;}public PagedList GetResume(int intPageIndex, int intPaegSize){ SQLHelper helper = new SQLHelper(); string strTotalCount = @"select count(*) from Resume"; string strSQL = @"select * from (select row_number() over(order by CreateTime desc) as row_number,* from Resume) as t0 where _number between @intPageSize*(@intPageIndex-1)+1 and @ingPageSize*@intPageIndex"; object obQuery = new { intPageSize = intPaegSize, intPageIndex = intPageIndex }; PagedList list = eList(strTotalCount, null, strSQL, obQuery, intPageIndex, intPaegSize); onnect(); return list;}public void Delete(int intId){ SQLHelper helper = new SQLHelper(); string strSQL = @"delete from Resume where Id=@Id"; object obQuery = new { Id = intId }; Query(strSQL, obQuery); onnect();}⽰例【2】1、定义实体类public class BaseModel{ public int Id { set; get; }}public class Company : BaseModel{ public string Name { get; set; } public me CreateTime { get; set; } public int CreatorId { get; set; } public int? LastModifierId { get; set; } public DateTime? LastModifyTime { get; set; }}public class User : BaseModel{ public string Name { get; set; } public string Account { get; set; } public string Password { get; set; } public string Email { get; set; } public string Mobile { get; set; } public int? CompanyId { get; set; } public string CompanyName { get; set; } public int State { get; set; } public int UserType { get; set; } public DateTime? LastLoginTime { get; set; } public DateTime CreateTime { get; set; } public int CreatorId { get; set; } public int? LastModifierId { get; set; }}2、编写通⽤接⼝public interface IBaseDAL{ T FindT(int id) where T : BaseModel; List FindAll() where T : BaseModel; bool Add(T t) where T : BaseModel; bool Update(T t) where T : BaseModel; bool Delete(T t) where T : BaseModel;}3、使⽤"泛型+反射"实现接⼝public class BaseDAL : IBaseDAL{ public T FindT(int id) where T : BaseModel { Type type = typeof(T); string sql = $"SELECT {(",", perties().Select(p => $"[{}]"))} FROM [{}] WHERE ID={id}"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); (); var reader = eReader(); if (()) { return (type, reader); } else { return null; } } } private T Trans(Type type, SqlDataReader reader) { object oObject = Instance(type); foreach (var prop in perties()) { ue(oObject, reader[] is DBNull ? null : reader[]); } } public List FindAll() where T : BaseModel { Type type = typeof(T); string sql = $"SELECT {(",", perties().Select(p => $"[{}]"))} FROM [{}]"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); (); var reader = eReader(); List tList = new List(); while (()) { ((type, reader)); } return tList; } } public bool Add(T t) where T : BaseModel { //id是⾃增的 所以不能新增 Type type = e(); string columnString = (",", perties(edOnly | ce | ) .Select(p => $"[{}]")); string valueColumn = (",", perties(edOnly | ce | ) .Select(p => $"@{}")); var parameterList = perties(edOnly | ce | ) .Select(p => new SqlParameter($"@{}", ue(t) ?? ));//注意可空类型 string sql = $"Insert [{}] ({columnString}) values({valueColumn})"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); ge(y()); (); return eNonQuery() == 1; //新增后把id拿出来？ 可以的，在sql后⾯增加个 Select @@Identity; ExecuteScalar } }}

2023年6月21日发(作者：)

C#泛型SQLHelperT类⽰例【1】1、创建SQLHelper类using c;using uration;using ent;using tion;namespace Db{ public class SQLHelper where T : class, new() { ///

/// 链接数据库字符串 ///

public static string strConnectionString = tionStrings["DataConnectionString"].ToString(); ///

/// 数据库连接 ///

public SqlConnection Connection = null; public SQLHelper() { OpenConnect(); } ///

/// 打开数据库连接 ///

public void OpenConnect() { if (Connection == null || != ) { Connection = new SqlConnection(strConnectionString); (); } } ///

/// 关闭数据库连接 ///

public void CloseConnect() { if (Connection != null && != ) { (); } } #region 对数据库进⾏读写操作 ///

/// 执⾏查询语句 ///

/// SQL语句 /// SQL参数的值 /// public SqlDataReader ExecReader(string strSQL, object obQuery) { SqlCommand command = new SqlCommand(strSQL, Connection); if (obQuery != null) { PropertyInfo[] pis = e().GetProperties(); foreach (var p in pis) { (new SqlParameter(, ue(obQuery, null))); } } SqlDataReader reader = eReader(); return reader; } ///

/// 执⾏返回单值的查询语句 ///

/// SQL语句 /// SQL参数的值 /// public object ExecSingleValue(string strSQL, object obQuery) { SqlCommand command = new SqlCommand(strSQL, Connection); if (obQuery != null) { PropertyInfo[] pis = e().GetProperties(); foreach (var p in pis) { (new SqlParameter(, ue(obQuery, null))); } } return eScalar(); } ///

/// 执⾏⾮查询语句 ///

/// SQL语句 /// SQL参数的值 /// public int ExecNoQuery(string strSQL, object obQuery) { SqlCommand command = new SqlCommand(strSQL, Connection); if (obQuery != null) { PropertyInfo[] pis = e().GetProperties(); foreach (var p in pis) { (new SqlParameter(, ue(obQuery, null))); } } return eNonQuery(); } #endregion #region 返回列表List，利⽤"泛型+反射" ///

/// 获取列表 ///

/// SQL语句 /// SQL参数的值 /// public List GetList(string strSQL, object obQuery) { //调⽤执⾏查询语句函数，返回SqlDataReader SqlDataReader reader = ExecReader(strSQL, obQuery); //定义返回的列表 List list = new List(); //定义T类型的实体 T model = new T(); //获取T类型实体的属性类型和值 PropertyInfo[] pis = e().GetProperties(); //获取数据库返回的列数 int intColCount = ount; //遍历SqlDataReader while (()) { //定义 int value_number = 0; //重新实例化T model = new T(); //从数据库拿出⼀条数据后，循环遍历T类型的属性类型和值 for (int i = 0; i < intColCount; i++) { //判断第⼀列是否为row_number，此为分页使⽤ if (e(i) == "row_number") value_number++; //设置T对应属性的值 pis[i].SetValue(model, ue(value_number), null); value_number++; } //将T添加到列表中 (model); } return list; } #endregion ///

/// 获取分页 ///

/// 总共个数的SQL /// 总共个数的SQL参数的值 /// 分页的SQL /// 分页SQL参数的值 /// 分页编号 /// 分页⼤⼩ /// 返回PagesList public PagedList GetPageList(string strTotalSQL, object obTotalQuery, string strSQL, object obQuery, int intPageIndex, int intPageSize) { //定义分页对象的编号和⼤⼩ PagedList pageList = new PagedList(intPageIndex, intPageSize); //执⾏获取单个值的函数，设置分页对象的总元素 alCount = (int)ExecSingleValue(strTotalSQL, obTotalQuery); //设置分页对象的分页数 if (alCount % intPageSize == 0) es = alCount / intPageSize; else es = alCount / intPageSize + 1; //定义列表，调⽤获取列表的函数获取此分页的元素 List list = GetList(strSQL, obQuery); //将列表元素添加到分页对象当中 ge(list); //设置分页对象是否有上⼀页和下⼀页 tPage = eIndex < es ? true : false; age = eIndex > 1 ? true : false; return pageList; } ///

/// 获取单个实体 ///

/// SQL语句 /// SQL参数的值 /// public T GetTM(string strSQL, object obQuery) { //调⽤执⾏查询语句，返回SqlDataReader SqlDataReader reader = ExecReader(strSQL, obQuery); //新建⼀个T类型 T model = new T(); //获取T类型的属性类型和值 PropertyInfo[] pis = e().GetProperties(); //获取数据库返回数据的列数 int intColCount = ount; //读取数据，填充T if (()) { int value_number = 0; for (int i = 0; i < intColCount; i++) { pis[i].SetValue(model, ue(value_number), null); value_number++; } } return model; } } ///

/// 分页实体类 ///

/// public class PagedList : List { ///

/// 分页编号 ///

public int intPageIndex { get; set; } ///

/// 分页⼤⼩ ///

public int intPageSize { get; set; } ///

/// 分页数 ///

public int intPages { get; set; } ///

/// 总元素的个数 ///

public int intTotalCount { get; set; } ///

/// 此分页元素的个数 ///

public int intCount { get; set; } ///

/// 是否有下⼀页 ///

public bool HasNextPage { get; set; } ///

/// 是否有上⼀页 ///

public bool HasPrPage { get; set; } public PagedList(int intPageIndex, int intPageSize) { eIndex = intPageIndex; eSize = intPageSize; } }}2、调⽤⽅法// 调⽤⽅法：ResumeTM 为数据库表的实体类public List GetResumeByJobInfoId(int intJobInfoId){ SQLHelper helper = new SQLHelper(); string strSQL = @"select * from Resume where FirstJobId=@JobInfoId or SecondJobId=@JobInfoId order by CreateTime desc"; object obQuery = new { JobInfoId = intJobInfoId }; List list = t(strSQL, obQuery); onnect(); return list;}public ResumeTM GetResumeById(int intId){ SQLHelper helper = new SQLHelper(); string strSQL = @"select * from Resume where Id=@Id"; object obQuery = new { Id = intId }; ResumeTM tm = (strSQL, obQuery); onnect(); return tm;}public PagedList GetResume(int intPageIndex, int intPaegSize){ SQLHelper helper = new SQLHelper(); string strTotalCount = @"select count(*) from Resume"; string strSQL = @"select * from (select row_number() over(order by CreateTime desc) as row_number,* from Resume) as t0 where _number between @intPageSize*(@intPageIndex-1)+1 and @ingPageSize*@intPageIndex"; object obQuery = new { intPageSize = intPaegSize, intPageIndex = intPageIndex }; PagedList list = eList(strTotalCount, null, strSQL, obQuery, intPageIndex, intPaegSize); onnect(); return list;}public void Delete(int intId){ SQLHelper helper = new SQLHelper(); string strSQL = @"delete from Resume where Id=@Id"; object obQuery = new { Id = intId }; Query(strSQL, obQuery); onnect();}⽰例【2】1、定义实体类public class BaseModel{ public int Id { set; get; }}public class Company : BaseModel{ public string Name { get; set; } public me CreateTime { get; set; } public int CreatorId { get; set; } public int? LastModifierId { get; set; } public DateTime? LastModifyTime { get; set; }}public class User : BaseModel{ public string Name { get; set; } public string Account { get; set; } public string Password { get; set; } public string Email { get; set; } public string Mobile { get; set; } public int? CompanyId { get; set; } public string CompanyName { get; set; } public int State { get; set; } public int UserType { get; set; } public DateTime? LastLoginTime { get; set; } public DateTime CreateTime { get; set; } public int CreatorId { get; set; } public int? LastModifierId { get; set; }}2、编写通⽤接⼝public interface IBaseDAL{ T FindT(int id) where T : BaseModel; List FindAll() where T : BaseModel; bool Add(T t) where T : BaseModel; bool Update(T t) where T : BaseModel; bool Delete(T t) where T : BaseModel;}3、使⽤"泛型+反射"实现接⼝public class BaseDAL : IBaseDAL{ public T FindT(int id) where T : BaseModel { Type type = typeof(T); string sql = $"SELECT {(",", perties().Select(p => $"[{}]"))} FROM [{}] WHERE ID={id}"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); (); var reader = eReader(); if (()) { return (type, reader); } else { return null; } } } private T Trans(Type type, SqlDataReader reader) { object oObject = Instance(type); foreach (var prop in perties()) { ue(oObject, reader[] is DBNull ? null : reader[]); } } public List FindAll() where T : BaseModel { Type type = typeof(T); string sql = $"SELECT {(",", perties().Select(p => $"[{}]"))} FROM [{}]"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); (); var reader = eReader(); List tList = new List(); while (()) { ((type, reader)); } return tList; } } public bool Add(T t) where T : BaseModel { //id是⾃增的 所以不能新增 Type type = e(); string columnString = (",", perties(edOnly | ce | ) .Select(p => $"[{}]")); string valueColumn = (",", perties(edOnly | ce | ) .Select(p => $"@{}")); var parameterList = perties(edOnly | ce | ) .Select(p => new SqlParameter($"@{}", ue(t) ?? ));//注意可空类型 string sql = $"Insert [{}] ({columnString}) values({valueColumn})"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); ge(y()); (); return eNonQuery() == 1; //新增后把id拿出来？ 可以的，在sql后⾯增加个 Select @@Identity; ExecuteScalar } }}