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