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