/// <summary> /// 通过operationType将实体对象转换成参数化的Sql语句 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="operationType">数据库操作类型</param> /// <param name="entity">实体对象(INSERT,UPDATE时使用)</param> /// <param name="paramsStartIndex">参数化Sql开始的索引</param> /// <param name="dynamicParameters">参数列表(INSERT,UPDATE,DELETE,SELECT时使用)</param> /// <returns></returns> private string ToParameterizedSqlString <T>(DbOperationType operationType, T entity, int paramsStartIndex, ref DynamicParameters dynamicParameters) where T : class { Type t = typeof(T); MapperPropertyInfo[] mappers = SqlServerUtilities.GetMapperPropertyInfo <T>(entity); string[] fields = new string[0]; string[] parameterized = new string[0]; string sql = string.Empty; switch (operationType) { case DbOperationType.INSERT: parameterized = SqlServerUtilities.GetParameterized(mappers, paramsStartIndex, ref dynamicParameters, out fields); sql = string.Format("INSERT INTO dbo.[{0}] ({1}) VALUES ({2})", t.Name, fields.ToString(p => "[" + p + "]"), parameterized.ToString(null) ); break; case DbOperationType.UPDATE: parameterized = SqlServerUtilities.GetParameterized(mappers, paramsStartIndex, ref dynamicParameters, out fields); sql = string.Format("UPDATE dbo.[{0}] SET {1}", t.Name, fields.Combine(parameterized, (p, q) => "[" + p + "]" + "=" + q).ToString(null) ); break; case DbOperationType.DELETE: sql = string.Format("DELETE FROM dbo.[{0}]", t.Name); break; case DbOperationType.SELECT: fields = SqlServerUtilities.GetFields(mappers); sql = string.Format("SELECT {0} FROM dbo.[{1}] (NOLOCK)", fields.ToString(p => "[" + p + "]"), t.Name ); break; case DbOperationType.COUNT: sql = string.Format("SELECT COUNT(*) FROM dbo.[{0}] (NOLOCK)", t.Name); break; case DbOperationType.EXISTS: sql = string.Format("SELECT 1 FROM dbo.[{0}] (NOLOCK)", t.Name); break; } return(sql); }
public List <T> GetPagedListExtensions <T>(int offset, int limit, out int total, bool isDesc = false, Expression <Func <T, bool> > condition = null, string orderBy = null) where T : class { MapperPropertyInfo[] mappers = SqlServerUtilities.GetMapperPropertyInfo <T>(null); //fields string[] fields = SqlServerUtilities.GetFields(mappers); //tableName string tableName = "dbo.[" + typeof(T).Name + "]"; //orderBy string order = string.IsNullOrEmpty(orderBy) ? string.Format("[{0}] ASC", mappers.First().Name) : orderBy.IndexOf("[") > -1 ? string.Format("{0} {1}", orderBy, (isDesc ? "DESC" : "ASC")) : string.Format("[{0}] {1}", orderBy, (isDesc ? "DESC" : "ASC")); //分页sql语句 string sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY " + order + " ) AS rownumber ,"; sql += fields.ToString(p => "[" + p + "]"); sql += " FROM " + tableName; //where条件 string where = condition.ToMSSqlString(); if (!string.IsNullOrEmpty(where)) { sql += " WHERE " + where; } sql += " ) tmp"; sql += " WHERE tmp.rownumber BETWEEN ( " + (offset + 1).ToString() + " ) AND ( " + (offset + limit).ToString() + " );"; //查询total string sqlTotal = "SELECT COUNT(*) FROM " + tableName; if (!string.IsNullOrEmpty(where)) { sqlTotal += " WHERE " + where; } sqlTotal += ";"; using (SqlConnection connection = new SqlConnection(m_connectionString)) { List <T> list = connection.Query <T>(sql).ToList(); total = connection.ExecuteScalar <int>(sqlTotal); return(list); } }