/// <summary>
///
/// </summary>
/// <param name="model"></param>
/// <param name="keys"></param>
/// <returns></returns>
public static bool Create <TElement>(this IRawSqlSearchService service, string tableName, string primaryKey,
TElement model, params string[] keys) where TElement : class, new()
{
var dic = ToSqlDictionary(service.GetDictionary(model));
List <string> primaryKeyList = new List <string>();
if (keys != null)
{
primaryKeyList.AddRange(keys);
}
if (keys == null || !keys.Any())
{
primaryKeyList.Add(primaryKey);
}
var whereClause = "";
string sqlClause;
foreach (var key in primaryKeyList)
{
var tempKey = key.ToLower();
if (dic.ContainsKey(tempKey))
{
whereClause = string.Format("{0}{1}{2}='{3}'", whereClause, string.IsNullOrEmpty(whereClause) ? "" : " AND ", tempKey, dic[tempKey]);
}
}
if (string.IsNullOrEmpty(whereClause))
{
return(false);
}
sqlClause = BuildCreateSql(tableName, primaryKey, dic);
if (string.IsNullOrEmpty(sqlClause))
{
return(false);
}
var propertyList = typeof(TElement).GetProperties().Where(x => x.CanWrite);
var property =
propertyList.FirstOrDefault(
x => string.Equals(x.Name, primaryKey, StringComparison.OrdinalIgnoreCase));
if (property != null && property.PropertyType == typeof(int))
{
var value = service.GetSingle(sqlClause);
property.SetValue(model, value, new object[] { });
}
else
{
service.ExcuteSql(sqlClause);
}
return(true);
}
public void InsertOrUpdate(string tablename, Guid storeId, IRawSqlSearchService service, DataTable table, List <string> primaryKey)
{
string chargeIdList = GetChargeNoList(table);
var whereClause = string.Format(" WHERE ChargeNo IN({0}) and StoreId='{1}'", chargeIdList, storeId);
var existsData = service.GetListWithSingleValue(tablename, whereClause, "ChargeNo", string.Empty, short.MaxValue)
.Select(x => Convert.ToString(x).ToLower()).ToList();
if (!table.Columns.Contains("ChargeNo"))
{
return;
}
List <DataRow> willRemoveIndexs = new List <DataRow>();
for (int i = 0; i < table.Rows.Count; i++)
{
var item = table.Rows[i];
var chargeNo = item["ChargeNo"].ToString().ToLower();
if (existsData.Contains(chargeNo))
{
willRemoveIndexs.Add(item);
continue;
}
}
foreach (var row in willRemoveIndexs)
{
table.Rows.Remove(row);
}
if (table.Rows.Count > 0)
{
SyncBigDataServiceHelper.BatchInsert(service.DbProviderConfig.DbConnectionString, tablename, table, table.Rows.Count);
}
//账单重交时需要清理原来账单数据
service.ExcuteSql("delete from Sync_charge " + whereClause);
service.ExcuteSql("delete from Sync_consumeperm " + whereClause);
service.ExcuteSql("delete from Sync_consumeperm2 " + whereClause);
}
public void Update(IRawSqlSearchService service, string tablename, DataRow newRow, List <string> primaryKey, string whereClaue)
{
if (!_willUpdate)
{
return;
}
var colmunsCount = newRow.Table.Columns.Count;
if (colmunsCount <= primaryKey.Count)
{
return;
}
StringBuilder builder = new StringBuilder(string.Format("update {0} set ", tablename));
bool flag = false;
foreach (DataColumn column in newRow.Table.Columns)
{
if (primaryKey.Any(x => string.Equals(x, column.ColumnName, StringComparison.OrdinalIgnoreCase)))
{
continue;
}
var value = newRow[column.ColumnName];
if (value == null)
{
value = GetDefaultValue(column.DataType);
}
if (value != null)
{
builder.AppendFormat("{0}{1}='{2}'", flag ? "," : "", column.ColumnName, value);
flag = true;
}
}
if (!flag)
{
return;
}
builder.AppendFormat(" WHERE {0}", whereClaue);
service.ExcuteSql(builder.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="model"></param>
/// <param name="keys"></param>
/// <returns></returns>
public static bool Update <TElement>(this IRawSqlSearchService service, string tableName, string primaryKey, TElement model, params string[] keys) where TElement : class, new()
{
var dic = ToSqlDictionary(service.GetDictionary(model));
List <string> primaryKeyList = new List <string>();
if (keys != null)
{
primaryKeyList.AddRange(keys);
}
if (keys == null || !keys.Any())
{
primaryKeyList.Add(primaryKey);
}
var whereClause = "";
string sqlClause;
foreach (var key in primaryKeyList)
{
var tempKey = key.ToLower();
if (dic.ContainsKey(tempKey))
{
whereClause = string.Format("{0}{1}{2}='{3}'", whereClause, string.IsNullOrEmpty(whereClause) ? "" : " AND ", tempKey, dic[tempKey]);
}
}
if (string.IsNullOrEmpty(whereClause))
{
return(false);
}
sqlClause = BuildUpdateSql(tableName, primaryKey, dic, keys);
if (string.IsNullOrEmpty(sqlClause))
{
return(false);
}
service.ExcuteSql(sqlClause);
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="storeId"></param>
/// <param name="useLanIpAddress"></param>
/// <returns></returns>
public MqClientConfigInfo GetMqConfig(Guid storeId, bool useLanIpAddress)
{
int minutes = 1440;// 24*60分钟=1440分钟
ConfigCenterClusterComputer configComputer = null;
var item = GetMqTable(storeId);
if (item != null && item.MqInstanceId.HasValue)
{
configComputer = _computerAccess.Get(item.MqInstanceId.Value);
}
if (configComputer != null && configComputer.DelFlag == false)
{
//如果当前配置有效，那么刷新配置
item.CreateTime = DateTime.Now;
item.Expired = minutes;
_searchService.ExcuteSql(string.Format("UPDATE ConfigCenter_MqTable SET CreateTime=GETDATE(),Expired={0} where StoreId='{1}'", minutes, storeId));
return(FillModel(item, configComputer, useLanIpAddress));
}
var count = _computerAccess.Count(ClusterComputerType.MessageQueue);
if (count == 0)
{
return(null);
}
int index = storeId.GetHashCode() % count;
if (index >= count - 1)
{
index = count - 1;
}
if (index < 0)
{
index = 0;
}
configComputer = _computerAccess.GetComputerList(ClusterComputerType.MessageQueue, index).LastOrDefault();
if (configComputer != null)
{
if (item != null)
{
//如果当前配置有效，那么刷新配置
item.CreateTime = DateTime.Now;
item.Expired = minutes;
item.MqInstanceId = configComputer.Id;
_searchService.ExcuteSql(
string.Format("UPDATE ConfigCenter_MqTable SET CreateTime=GETDATE(),Expired={0},MqInstanceId={2} where StoreId='{1}'",
minutes, storeId, configComputer.Id));
}
else
{
//如果当前配置有效，那么刷新配置
item = new ConfigCenterMqTable()
{
CreateTime = DateTime.Now,
StoreId = storeId,
MqInstanceId = configComputer.Id
};
item.CreateTime = DateTime.Now;
item.Expired = minutes;
_searchService.Create("ConfigCenter_MqTable", "StoreId", item);
}
return(FillModel(item, configComputer, useLanIpAddress));
}
return(null);
}
public void ExcuteSql(string sqlClause)
{
_searchService.ExcuteSql(sqlClause);
}