/// <summary>
///
/// </summary>
public MqConfigService()
{
_searchService = new RawSqlSearchService(DbConfigHelper.GetConfig());
_computerAccess = new ConfigCenterClusterComputerAccess(DbConfigHelper.GetConfig());
_cacheManager = new RedisCacheManager((int)CacheRegionName.MqConfig);
_logger = LoggerFactory.GetLog();
}
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);
}
}
/// <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 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="providerConfig"></param>
protected BusinessService(IDbProviderConfig providerConfig)
{
_searchService = new RawSqlSearchService(providerConfig);
_primatyKeyIsInt = null;
}
/// <summary>
///
/// </summary>
/// <param name="providerConfig"></param>
public DatabaseInitialise(IDbProviderConfig providerConfig)
{
_providerConfig = providerConfig;
_searchService = new RawSqlSearchService(_providerConfig);
}
public void InsertOrUpdate(string tablename, Guid storeId, IRawSqlSearchService service, DataTable table, List <string> primaryKey)
{
primaryKey = primaryKey.Where(x => table.Columns.Contains(x)).ToList();
if (primaryKey.Count == 0)
{
return;
}
List <DataRow> willRemoveIndexs = new List <DataRow>();
if (primaryKey.Count == 1)
{
string currentPrimaryKey = primaryKey[0];
if (string.IsNullOrEmpty(currentPrimaryKey))
{
return;
}
var whereClause = BuildWhereCluase(table, storeId, currentPrimaryKey);
if (string.IsNullOrEmpty(whereClause))
{
return;
}
var existsData = service.GetListWithSingleValue(tablename, whereClause, currentPrimaryKey, string.Empty, short.MaxValue)
.Select(x => Convert.ToString(x).ToLower()).ToList();
for (int i = 0; i < table.Rows.Count; i++)
{
var item = table.Rows[i];
var currentPrimaryKeyValue = Convert.ToString(item[currentPrimaryKey]) ?? string.Empty;
if (existsData.Contains(currentPrimaryKeyValue.ToLower()))
{
willRemoveIndexs.Add(item);
whereClause = BuildWhereCluase(item, storeId, primaryKey);
if (string.IsNullOrEmpty(whereClause))
{
continue;
}
Update(service, tablename, item, primaryKey, whereClause);
}
continue;
}
}
else
{
for (int i = 0; i < table.Rows.Count; i++)
{
var item = table.Rows[i];
var whereClause = BuildWhereCluase(item, storeId, primaryKey);
if (service.Exists(tablename, whereClause))
{
willRemoveIndexs.Add(item);
if (string.IsNullOrEmpty(whereClause))
{
continue;
}
Update(service, tablename, item, primaryKey, whereClause);
}
}
}
foreach (var row in willRemoveIndexs)
{
table.Rows.Remove(row);
}
if (table.Rows.Count > 0)
{
SyncBigDataServiceHelper.BatchInsert(service.DbProviderConfig.DbConnectionString, tablename, table, table.Rows.Count);
}
}