/// <summary>
/// 根据类型生成Update 语句 必须输入条件
/// </summary>
/// <typeparam name="T">数据类型。</typeparam>
/// <param name="conditions">条件 "例如：ID=@ID"</param>
/// <param name="keyName">条件 主键列一般是不能更新的 所以要过滤掉。</param>
/// <returns></returns>
public static string GetUpdateCommandStringByType <T>(string conditions, string keyName) where T : class
{
if (string.IsNullOrEmpty(conditions) || string.IsNullOrWhiteSpace(conditions))
{ //防止误操作 更新所有数据~
throw new Exception("为保证数据库安全，更新语句必须提供更新范围条件！");
}
PropertyInfo[] properties = DapperPagerUtils.GetPropertyInfosByType(typeof(T)).ToArray();
var columns = properties.Where(o => !o.Name.Equals(keyName)).Select(p => p.Name).ToArray();
var parameters = columns.Select(name => "[" + name + "]" + "=@" + name).ToList();
string str = String.Format(updateString, typeof(T).Name, String.Join(",", parameters));
//if (!string.IsNullOrEmpty(keyName))
//{
// str = str.Replace("@" + keyName + ",", "").Replace(keyName + "=", "");
//}
if (string.IsNullOrEmpty(conditions))
{
return(str);
}
else
{
return(str + " AND " + conditions);
}
}
/// <summary>Query a list of data async from table with specified condition.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="condition"></param>
/// <param name="table"></param>
/// <param name="columns"></param>
/// <param name="isOr"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public static Task <IEnumerable <T> > QueryListAsync <T>(object condition, string table, string columns = "*", string connectionSettingName = null, bool isOr = false, IDbTransaction transaction = null, int?commandTimeout = null)
{
using (IDbConnection _conn = DapperConnectionFactory.CreateConnection(connectionSettingName))
{
return(_conn.QueryAsync <T>(DapperPagerUtils.BuildQuerySQL(condition, table, columns, isOr), condition, transaction, commandTimeout));
}
}
/// <summary>
/// 生成查询 WITH NO LOCK 语句 此模式在高并发下允许脏读 （相当于READ UNCOMMITTED）无法保证数据的一致性
/// 但是对性能较好。高并发情况下推荐使用此方法。
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static string GetSelectWithNoLockCommandStringByType <T>(string conditions = "") where T : class
{
PropertyInfo[] properties = DapperPagerUtils.GetPropertyInfosByType(typeof(T)).ToArray();
var columns = properties.Select(p => "" + p.Name + "").ToArray();
string str = string.Format(selectStringWithNoLock, String.Join(",", columns), typeof(T).Name);
if (string.IsNullOrEmpty(conditions))
{
return(str);
}
else
{
return(str + " AND " + conditions);
}
}
/// <summary>
/// 生成过滤掉标识列的SQL插入语句命令
/// </summary>
/// <typeparam name="T">泛型类名</typeparam>
/// <param name="keyName">标识列名称</param>
/// <returns></returns>
public static string GetInsertCommandFilteredIdentityColumn <T>(string keyName) where T : class
{
PropertyInfo[] properties = DapperPagerUtils.GetPropertyInfosByType(typeof(T)).ToArray();
if (properties.Count() < 1)
{
throw new Exception("传入对象不具有属性，请检查传递对象！");
}
var columnsCommand = properties.Where(o => !o.Name.Equals(keyName)).Select(p => "[" + p.Name + "]").ToArray();
var columnsValue = properties.Where(o => !o.Name.Equals(keyName)).Select(p => p.Name).ToArray();
return(string.Format(insertString,
typeof(T).Name,
String.Join(",", columnsCommand), properties[0].Name,
String.Join(",@", columnsValue)));
}
/// <summary>Query paged data async from a single table.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">传入的条件中，condition仅仅支持=操作。</param>
/// <param name="table"></param>
/// <param name="orderBy"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="columns"></param>
/// <param name="isOr"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public static Task <IEnumerable <T> > QueryPagedAsync <T>(dynamic condition, string table, string orderBy, int pageIndex, int pageSize, string columns = "*", bool isOr = false, IDbTransaction transaction = null, int?commandTimeout = null, string connectionSettingName = null)
{
var conditionObj = condition as object;
var whereFields = string.Empty;
var properties = DapperPagerUtils.GetProperties(conditionObj);
if (properties.Count > 0)
{
var separator = isOr ? " OR " : " AND ";
whereFields = " WHERE " + string.Join(separator, properties.Select(p => p + " = @" + p));
}
var sql = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber, {0} FROM {2}{3}) AS Total WHERE RowNumber >= {4} AND RowNumber <= {5}", columns, orderBy, table, whereFields, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
using (IDbConnection _conn = DapperConnectionFactory.CreateConnection(connectionSettingName))
{
return(_conn.QueryAsync <T>(sql, conditionObj, transaction, commandTimeout));
}
}