C#通用DAO实现(三)

最新推荐文章于 2020-01-31 23:47:30 发布

原创

最新推荐文章于 2020-01-31 23:47:30 发布 · 2.2k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#dao #c# #null #string #exception #sql

这个博客主要展示了C#中实现通用DAO操作的代码片段，包括根据非范型分页对象获取总条数的方法`GetMaxRecodeCount`，支持多个表名的查询方法，以及执行SQL语句的方法如`ExecuteNonQuery`和`ExecuteQuery`。代码中涉及了条件参数化查询，事务管理和数据库连接的打开、关闭。

第481行到998行代码:

/// <summary> /// 根据非范型分页对象获得总条数 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="pi"></param> /// <returns></returns> public int GetMaxRecodeCount(PaginationInfo pi) { DbConnection dcon = null; DbCommand dcom = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); List<Object> objs = new List<Object>(); string SqlStr = @"select count({pkey}) from {tablename} where 1=1 {condition}"; SqlStr = String.IsNullOrEmpty(pi.TableName) ? SqlStr.Replace("{pkey}", GetPrimaryKey(pi.ClassType)) : SqlStr.Replace("{pkey}", GetPrimaryKey<Object>(pi.TableName)); SqlStr = String.IsNullOrEmpty(pi.TableName) ? SqlStr.Replace("{tablename}", "[" + pi.ClassType.Name + "]") : SqlStr.Replace("{tablename}", "[" + pi.TableName + "]"); string conditionStr = ""; if (pi.Conditions != null) { foreach (Connditon condition in pi.Conditions) { if (condition != null) { if (condition.Value != null) { conditionStr += " and " + condition.Name + " " + Connditon.GetType(condition.OpType) + " @" + GetParname(condition); DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + GetParname(condition); if (condition.OpType != Op.Like) { par.Value = condition.Value; } else { par.Value = "%" + condition.Value + "%"; } par.DbType = GetDbType(pi.ClassType.GetProperty(condition.Name)); dcom.Parameters.Add(par); } } } } dcom.CommandText = SqlStr.Replace("{condition}", conditionStr); int i = (int)dcom.ExecuteScalar(); return i; } catch (Exception ex) { throw ex; } finally { dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } public int GetMaxRecodeCount(PaginationInfo pi,params String[] tbnames) { if (tbnames == null || tbnames.Length == 0) { throw new ArgumentException("获取总条数时表名不能为空"); } DbConnection dcon = null; DbCommand dcom = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); List<Object> objs = new List<Object>(); string sqlUnit = @"select 1 as pkey from {tablename} "+ " where 1=1 {condition} union all "; string sqlAll = ""; string SqlStr = @"select count(1) from ({tablename}) as model"; foreach (String tname in tbnames) { sqlAll += sqlUnit.Replace("{tablename}","["+tname+"]"); } sqlAll = sqlAll.Substring(0,sqlAll.Length-10); SqlStr = SqlStr.Replace("{tablename}",sqlAll);