/// <summary> /// 更新记录(无事物) /// </summary> /// <param name="condition">条件(格式字段=?),例:b=? and c = ? and d =?</param> /// <param name="parameters">参数值,建议采用parameters参数,parameters防Sql注入</param> /// <returns>受影响的行数</returns> public int UpdateNoTrans(string condition, params object[] parameters) { if (__EditColumns.Count == 0 || !condition.HasValue()) { return(0); } var paras = new List <object>(); var sb = new StringBuilder(); sb.Append("update ").Append(TableName).Append(" set "); foreach (var column in __EditColumns) { sb.Append("[").Append(column.Key).Append("] = ?,"); paras.Add(column.Value); } if (sb.ToString().EndsWith(",")) { sb.Remove(sb.Length - 1, 1); } sb.Append(" where ").Append(condition); paras.AddRange(parameters); Clear(); return(DbBase2 <TK> .ExecuteNonQueryNoTrans(sb.ToString(), paras.ToArray())); }
/// <summary> /// 添加记录(存在condition就不添加) /// </summary> /// <returns>受影响的行数</returns> public int Insert(string condition = null) { if (__EditColumns.Count == 0) { return(-1); } var sb = new StringBuilder(); var parameters = new List <object>(); if (condition.NotNullOrEmpty()) { sb.Append("if not exists(select 1 from ").Append(TableName).Append(" where ").Append(condition).Append(")\n"); } sb.Append("insert into ").Append(TableName).Append(" ("); foreach (var column in __EditColumns) { sb.Append("[").Append(column.Key).Append("],"); parameters.Add(column.Value); } sb.Remove(sb.Length - 1, 1); sb.Append(")values("); var columnCount = __EditColumns.Count; for (var i = 0; i < columnCount; i++) { sb.Append("?,"); } sb.Remove(sb.Length - 1, 1); sb.Append(")"); __EditColumns.Clear(); return(DbBase2 <TK> .ExecuteNonQuery(sb.ToString(), parameters.ToArray())); }
/// <summary> /// 删除记录 /// </summary> /// <param name="condition">条件(格式字段=?),例:b=? and c = ? and d =?</param> /// <param name="parameters">参数值,建议采用parameters参数,parameters防Sql注入</param> /// <returns>受影响的行数</returns> public static int Delete(string condition, params object[] parameters) { var sb = new StringBuilder(); sb.Append("delete from ").Append(typeof(T).Name); sb.Append(" where ").Append(condition); return(DbBase2 <TK> .ExecuteNonQuery(sb.ToString(), parameters)); }
/// <summary> /// 更新记录(存在更新,不存在添加) /// </summary> /// <returns>返回id</returns> public int InsertOrUpdateIdNoTrans(string condition, string idName = "id") { if (__EditColumns.Count == 0) { return(-1); } var sb = new StringBuilder(); var parameters = new List <object>(); if (condition.NotNullOrEmpty()) { sb.Append("if not exists(select 1 from ").Append(TableName).Append(" where ").Append(condition).Append(")\nbegin\n"); } sb.Append("insert into ").Append(TableName).Append(" ("); foreach (var column in __EditColumns) { sb.Append("[").Append(column.Key).Append("],"); parameters.Add(column.Value); } sb.Remove(sb.Length - 1, 1); sb.Append(")values("); var columnCount = __EditColumns.Count; for (var i = 0; i < columnCount; i++) { sb.Append("?,"); } sb.Remove(sb.Length - 1, 1); sb.Append(")\nselect SCOPE_IDENTITY()\nend else begin\n"); sb.Append("update ").Append(TableName).Append(" set "); foreach (var column in __EditColumns) { sb.Append("[").Append(column.Key).Append("] = ?,"); parameters.Add(column.Value); } if (sb.ToString().EndsWith(",")) { sb.Remove(sb.Length - 1, 1); } if (condition.HasValue()) { sb.Append(" where ").Append(condition); } sb.Append("\nselect max(").Append(idName).Append(") from ").Append(TableName); if (condition.NotNullOrEmpty()) { sb.Append(" where ").Append(condition); } sb.Append(" end"); __EditColumns.Clear(); return(DbBase2 <TK> .ExecuteScalarNoTrans(-1, sb.ToString(), parameters.ToArray())); }
/// <summary> /// 列最大值 /// </summary> public static TR Max <TR>(string columnName, TR defaultValue = default(TR)) { var sql = "SELECT MAX([{0}]) FROM {1}".Formats(columnName, typeof(T).Name); return(DbBase2 <TK> .ExecuteScalarNoTrans(defaultValue, sql)); }
/// <summary> /// 返回表的记录数 /// </summary> public static int GetCount() { var sql = "SELECT COUNT(1) FROM " + typeof(T).Name; return(DbBase2 <TK> .ExecuteScalarNoTrans(0, sql)); }
/// <summary> /// 返回表中所有数据 /// </summary> public static List <T> GetDataAll() { var sql = "SELECT * FROM " + typeof(T).Name; return(DbBase2 <TK> .SelectCache <T>(sql, 600)); }
/// <summary> /// 返回表中上次更新标识之后更新过的数据 /// </summary> public static List <T> GetDataAfterFlag(string lastUpdateFlag) { var sql = "SELECT * FROM {0} WHERE UpdateFlag>{1}".Formats(typeof(T).Name, lastUpdateFlag); return(DbBase2 <TK> .Select <T>(sql)); }
/// <summary> /// 批量更新记录 Dictionary[条件,表] /// </summary> public static int Update(IEnumerable <KeyValuePair <string, T> > tables) { var tabs = tables.Select(a => new KeyValuePair <string, Table2 <T, TK> >(a.Key, a.Value as Table2 <T, TK>)); var updateTabs = tabs.Where(a => a.Value.__EditColumns.Count > 0).ToArray(); if (updateTabs.Length == 0) { return(0); } var sw = Stopwatch.StartNew(); var result = -1; var sqls = new List <KeyValuePair <string, List <DbParameter> > >(); try { var sb = new StringBuilder(); var paraIndex = 0; var parameters = new List <DbParameter>(); foreach (var table in updateTabs) { if (table.Value.__EditColumns.Count > 0) { sb.Append("\nupdate ").Append(table.Value.TableName).Append(" set "); foreach (var column in table.Value.__EditColumns) { sb.Append("[").Append(column.Key).Append("]=@p").Append(parameters.Count.ToString()).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + parameters.Count, column.Value)); paraIndex += 2; } sb.Remove(sb.Length - 1, 1); sb.Append(" where ").Append(table.Key); if (paraIndex > 1500) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); sb = new StringBuilder(); paraIndex = 0; parameters = new List <DbParameter>(); } } } if (sb.Length > 0) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); } var option = new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted, Timeout = new TimeSpan(0, 2, 0) }; using (var trans = new TransactionScope(TransactionScopeOption.Required, option)) { using (var conn = DbBase2 <TK> .GetSqlConnection(false)) { foreach (var sql in sqls) { var cmd = new SqlCommand(sql.Key, conn) { CommandType = CommandType.Text }; cmd.Parameters.AddRange(sql.Value.ToArray()); result += cmd.ExecuteNonQuery(); } trans.Complete(); } } } catch (Exception e) { Log.Error(e.Message + e.StackTrace); } var useTime = sw.ElapsedMilliseconds; var logStr = "Table.Update({0}) 执行时间 {1} 毫秒".Formats(sqls.Join("\n"), useTime.ToString("F0")); if (useTime > DbBase2 <TK> .WornigMilliseconds) { Log.Warn(logStr); } Log.Debug(logStr); return(result); }