/// <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);
}
}
