/// <summary>
/// 获取指定的数据库连接
/// </summary>
/// <param name="dataBaseType">数据库类型</param>
/// <param name="connectionString">数据库连接串</param>
/// <returns>数据库访问类</returns>
public static IDbHelper GetHelper(DbTypes dbType = DbTypes.SqlServer, string connectionString = null)
{
// 这里是每次都获取新的数据库连接,否则会有并发访问的问题存在
string dbHelperClass = DbHelper.GetDbHelperClass(dbType);
IDbHelper dbHelper = (IDbHelper)Assembly.Load("DotNet.DbUtilities").CreateInstance(dbHelperClass, true);
if (!string.IsNullOrEmpty(connectionString))
{
dbHelper.ConnectionString = connectionString;
}
else
{
dbHelper.ConnectionString = BaseSystemInfo.BusinessDbConnection;
}
return dbHelper;
}
/// <summary>
/// 获取数据表 一参 参数为数组
/// </summary>
/// <param name="dbHelper">数据库类型</param>
/// <param name="dbHelper">数据库连接</param>
/// <param name="tableName">数据来源表名</param>
/// <param name="name">字段名</param>
/// <param name="value">字段值</param>
/// <param name="order">排序</param>
/// <returns>数据表</returns>
public static DataTable GetDataTable(DbTypes DbType,IDbHelper dbHelper, string tableName, string name, object[] values, string order = null)
{
string sqlQuery = " SELECT * "
+ " FROM " + tableName;
if (values == null)
{
sqlQuery += " WHERE " + name + " IS NULL";
}
else
{
sqlQuery += " WHERE " + name + " IN (" + BaseBusinessLogic.ObjectsToList(DbType,values) + ")";
}
if (!String.IsNullOrEmpty(order))
{
sqlQuery += " ORDER BY " + order;
}
return dbHelper.Fill(sqlQuery);
}
/// <summary>
/// Convert a DateTime to a string formatted for the specified database type.
/// </summary>
/// <param name="dbType">The type of database.</param>
/// <param name="ts">The timestamp.</param>
/// <returns>A string formatted for use with the specified database.</returns>
public static string DbTimestamp(DbTypes dbType, DateTime ts)
{
switch (dbType)
{
case DbTypes.Mysql:
return(Mysql.DatabaseClient.DbTimestamp(ts));
case DbTypes.Postgresql:
return(Postgresql.DatabaseClient.DbTimestamp(ts));
case DbTypes.Sqlite:
return(Sqlite.DatabaseClient.DbTimestamp(ts));
case DbTypes.SqlServer:
return(SqlServer.DatabaseClient.DbTimestamp(ts));
default:
return(null);
}
}
static public IDbConnection CreateInstance(DbTypes dbType, string connectionString)
{
switch (dbType)
{
case DbTypes.SqlServer:
return(new SqlConnection(connectionString));
case DbTypes.PostGres:
return(new NpgsqlConnection(connectionString));
case DbTypes.Oracle:
return(new OracleConnection(connectionString));
case DbTypes.Sqlite:
return(new SQLiteConnection(connectionString));
default:
throw new Exception($"Unknown DatabaseType: { dbType }");
}
}
public int SaveAttachedFile(ItemFileLink link)
{
if (link == null)
{
throw new ArgumentNullException("ItemFileLink", "can not be null");
}
var dsAfterInsertLink = new DataSet();
var existsFileId = FindFileId(link.File);
if (existsFileId > 0)
{
//Присваиваем fileId файла, обновляем линк и возвращаем Id файла
link.File.ItemId = existsFileId;
dsAfterInsertLink = _environment.Execute(ItemFileLinksQueries.GetMergeQuery(), ItemFileLinksQueries.GetParameters(link));
link.ItemId = DbTypes.ToInt32(dsAfterInsertLink.Tables[0].Rows[0][0]);
return(existsFileId);
}
//Запоминаем fileId старого файла
var oldFileId = link.File.ItemId;
var ds = _environment.Execute(AttachedFileQueries.GetInsertQuery(), AttachedFileQueries.GetParameters(link.File));
//Запоминаем fileId нового файла
var newFileId = DbTypes.ToInt32(ds.Tables[0].Rows[0][0]);
link.File.ItemId = newFileId;
dsAfterInsertLink = _environment.Execute(ItemFileLinksQueries.GetMergeQuery(), ItemFileLinksQueries.GetParameters(link));
link.ItemId = DbTypes.ToInt32(dsAfterInsertLink.Tables[0].Rows[0][0]);
if (oldFileId > 0)
{
DeleteFileIfFileHasNoLinks(oldFileId);
}
return(newFileId);
}
/// <summary>
/// Create an instance of the database client.
/// </summary>
/// <param name="dbType">The type of database.</param>
/// <param name="serverIp">The IP address or hostname of the database server.</param>
/// <param name="serverPort">The TCP port of the database server.</param>
/// <param name="username">The username to use when authenticating with the database server.</param>
/// <param name="password">The password to use when authenticating with the database server.</param>
/// <param name="instance">The instance on the database server (for use with Microsoft SQL Server).</param>
/// <param name="database">The name of the database with which to connect.</param>
public ClienteBanco(
DbTypes dbType,
string serverIp,
int serverPort,
string username,
string password,
string instance,
string database)
{
//
// MsSql, MySql, and PostgreSql will use server IP, port, username, password, database
// Sqlite will use just database and it should refer to the database file
//
if (String.IsNullOrEmpty(serverIp))
{
throw new ArgumentNullException(nameof(serverIp));
}
if (serverPort < 0)
{
throw new ArgumentOutOfRangeException(nameof(serverPort));
}
if (String.IsNullOrEmpty(database))
{
throw new ArgumentNullException(nameof(database));
}
_DbType = dbType;
_ServerIp = serverIp;
_ServerPort = serverPort;
_Username = username;
_Password = password;
_Instance = instance;
_DatabaseName = database;
PopulaConnectionString();
CarregaTabelas();
CarregaDetalhesTabela();
CarregaProcedures();
}
/// <summary>
/// Добавление данных в запрос из списка Id
/// </summary>
/// <param name="ids">список Id</param>
/// <param name="tableName">Имя таблицы</param>
/// <returns>строку подмены запроса</returns>
public static string TIdsListAdd(IEnumerable <long> ids, string tableName, DbTypes dbt = DbTypes.MsSql)
{
if ((ids != null) && (ids.Count() > 0))
{
string tableInsertBegin = "insert into " + tableName + " values ";
StringBuilder res = new StringBuilder("");
bool isFirst = true;
int row = 0;
int rowMax = 500;
foreach (long id in ids)
{
if (isFirst)
{
isFirst = !isFirst;
res.AppendLine(tableInsertBegin);
}
else
{
res.Append(",");
}
res.Append("(");
res.Append(FieldHandler.ConvertValueToDB(id, dbt));
res.Append(")");
row++;
if (row >= rowMax)
{
row = 0;
isFirst = true;
}
}
return(res.ToString());
}
else
{
return("");
}
}
/// <summary>
/// Create an instance of the database client.
/// </summary>
/// <param name="dbType">The type of database.</param>
/// <param name="serverIp">The IP address or hostname of the database server.</param>
/// <param name="serverPort">The TCP port of the database server.</param>
/// <param name="username">The username to use when authenticating with the database server.</param>
/// <param name="password">The password to use when authenticating with the database server.</param>
/// <param name="instance">The instance on the database server (for use with Microsoft SQL Server).</param>
/// <param name="database">The name of the database with which to connect.</param>
public DatabaseClient(
DbTypes dbType,
string serverIp,
int serverPort,
string username,
string password,
string instance,
string database)
{
//
// MsSql, MySql, and PostgreSql will use server IP, port, username, password, database
// Sqlite will use just database and it should refer to the database file
//
if (String.IsNullOrEmpty(serverIp))
{
throw new ArgumentNullException(nameof(serverIp));
}
if (serverPort < 0)
{
throw new ArgumentOutOfRangeException(nameof(serverPort));
}
if (String.IsNullOrEmpty(database))
{
throw new ArgumentNullException(nameof(database));
}
DbType = dbType;
ServerIp = serverIp;
ServerPort = serverPort;
Username = username;
Password = password;
Instance = instance;
Database = database;
PopulateConnectionString();
LoadTableNames();
LoadTableDetails();
}
/// <summary>
///
/// </summary>
/// <param name="connStr"></param>
/// <param name="type"></param>
/// <returns> if type not exists then the return is null </returns>
public static IDbConnection GetConnection(string connStr, DbTypes type)
{
IDbConnection conn = null;
switch (type)
{
case DbTypes.Mysql:
conn = new MySqlConnection(connStr);
break;
case DbTypes.Oracle:
//#pragma warning disable 618
conn = new OracleConnection(connStr);
//#pragma warning restore 618.
break;
case DbTypes.Sqlserver:
conn = new SqlConnection(connStr);
break;
}
return(conn);
}
private static IEnumerable <Type> GetEnumTypes(DbTypes dbTypes, Assembly assembly)
{
foreach (Type type in assembly.GetTypes().Where(t => t.IsEnum))
{
object[] attributes = type.GetCustomAttributes(typeof(DbEntityAttribute), inherit: true);
if (attributes.Length == 0)
{
continue;
}
foreach (object attribute in attributes)
{
DbEntityAttribute dbEntity = attribute as DbEntityAttribute;
if (dbTypes != dbEntity.DbTypes)
{
continue;
}
yield return(type);
}
}
}
public MyDbUtil(DbTypes dbType = DbTypes.SqlServer)
{
_connectionString = ConfigurationManager.AppSettings["DefaultConnectionString"];
_paramPrefix = DbTypeUtil.GetDbParamPrefix(dbType);
}
public static int Encode(int localId, DbTypes dbType)
{
return localId * Coefficient + (int) dbType;
}
public static Func <Dictionary <string, object>, object> GetObjectMapper(TableMeta tableMeta)
{
return(objectMappers.Get(tableMeta.Type, () =>
{
var constructor = tableMeta.Type.GetDefaultConstructor()
?? tableMeta.Type.GetConstructors().FirstOrDefault();
if (constructor == null)
{
throw Error.Exception("类型" + tableMeta.Type + "没有公开的构造函数。");
}
var parameter = constructor.GetParameters();
Func <Dictionary <string, object>, object> map = (param) =>
{
var columns = tableMeta.Columns.ToList();
var args = new List <object>();
foreach (var info in parameter)
{
var t = info.ParameterType;
var n = info.Name;
var col = columns.FirstOrDefault(x => x.Name.IsSameField(n));
var val = t.GetDefaultValue();
if (col != null)
{
var key = col.ColumnName;
if (param.ContainsKey(key))
{
val = param[key];
if (val != null && !t.IsInstanceOfType(val))
{
var mapper = GetMapper(t, val.GetType());
val = mapper(val);
}
param.Remove(key);
}
columns.Remove(col);
}
else if (!DbTypes.IsSimpleType(t))
{
var vs = param.Where(x => x.Key.StartsWith(n + "-"))
.ToDictionary(x => x.Key.Substring(n.Length + 1), x => x.Value);
var pMeta = TableMeta.From(t);
var mapper = GetObjectMapper(pMeta);
val = mapper(vs);
foreach (var vsKey in vs.Keys)
{
param.Remove(n + "-" + vsKey);
}
}
args.Add(val);
}
var instance = constructor.FastCreate(args.ToArray());
foreach (var col in columns.Where(x => x.CanWrite))
{
var key = col.ColumnName;
if (param.ContainsKey(key))
{
var val = param[key];
if (val != null && !col.Type.IsInstanceOfType(val))
{
var mapper = GetMapper(col.Type, val.GetType());
val = mapper(val);
}
col.SetValue(instance, val);
param.Remove(key);
}
}
var keys = param.Where(x => x.Key.Contains("-")).ToList();
if (keys.Any())
{
var inners = keys.GroupBy(x => x.Key.Split('-')[0]);
foreach (var inner in inners)
{
var prop = tableMeta.Type.GetProperty(inner.Key);
if (prop != null && prop.CanWrite)
{
var vs = inner.ToDictionary(x => x.Key.Substring(inner.Key.Length + 1), x => x.Value);
var propMeta = TableMeta.From(prop.PropertyType);
var mapper = GetObjectMapper(propMeta);
var propValue = mapper(vs);
prop.FastSetValue(instance, propValue);
}
foreach (var result in inner.Select(x => x.Key))
{
param.Remove(result);
}
}
}
return instance;
};
return map;
}));
}
/// <summary>
/// Получает свойства из массива байт
/// </summary>
/// <param name="data"></param>
/// <param name="serializedDataLength"></param>
private void ConvertFromSerializedData(byte[] data, int serializedDataLength)
{
if (data == null)
{
data = new byte[serializedDataLength];
}
byte[] serializedFirstPerformance = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, 0, serializedFirstPerformance, 0, Lifelength.SerializedDataLength);
FirstPerformanceSinceNew = Lifelength.ConvertFromByteArray(serializedFirstPerformance);
byte[] serializedFirstNotification = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength, serializedFirstNotification, 0, Lifelength.SerializedDataLength);
FirstNotification = Lifelength.ConvertFromByteArray(serializedFirstNotification);
byte[] serializedrepeatPerform = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength * 2, serializedrepeatPerform, 0, Lifelength.SerializedDataLength);
RepeatInterval = Lifelength.ConvertFromByteArray(serializedrepeatPerform);
byte[] serializedNotification = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength * 3, serializedNotification, 0, Lifelength.SerializedDataLength);
RepeatNotification = Lifelength.ConvertFromByteArray(serializedNotification);
byte[] serializedWarranty = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength * 4, serializedWarranty, 0, Lifelength.SerializedDataLength);
Warranty = Lifelength.ConvertFromByteArray(serializedWarranty);
byte[] serializedWarrantyNotification = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength * 5, serializedWarrantyNotification, 0, Lifelength.SerializedDataLength);
WarrantyNotification = Lifelength.ConvertFromByteArray(serializedWarrantyNotification);
_performRepeatedly = data[Lifelength.SerializedDataLength * 6] == 1;
FirstPerformanceConditionType = data[Lifelength.SerializedDataLength * 6 + 1] == 1 ? ThresholdConditionType.WhicheverLater : ThresholdConditionType.WhicheverFirst;
// если еще есть данные то рекурсивно считываем и его
int dataIndex = serializedDataLength;
int dataLeft = data.Length - dataIndex;
if (dataLeft >= sizeof(long))
{
byte[] serializedEffectivityDate = new byte[sizeof(long)];
Array.Copy(data, dataIndex, serializedEffectivityDate, 0, sizeof(long));
EffectiveDate = new DateTime(DbTypes.Int64FromByteArray(serializedEffectivityDate, 0));
}
dataLeft -= sizeof(long);
dataIndex += sizeof(long);
if (dataLeft >= Lifelength.SerializedDataLength)
{
byte[] serializedRepeatNotificaction = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, dataIndex, serializedRepeatNotificaction, 0, Lifelength.SerializedDataLength);
FirstPerformanceSinceEffectiveDate = Lifelength.ConvertFromByteArray(serializedRepeatNotificaction);
}
}
internal static int ToGlobalId(int localId, DbTypes dbType)
{
return localId*codeConstant + (int) dbType;
}
public virtual DataTable GetDataTable(DbTypes DbType, string[] ids)
{
return(DbLogic.GetDataTable(DbType, DbHelper, this.CurrentTableName, BaseBusinessLogic.FieldId, ids));
}
/// <summary>
/// Конвертирует в строку готовую ко вставке в запрос в качестве параметра
/// </summary>
/// <param name="value">Входящее значение</param>
/// <returns>строка сконвентированная</returns>
public static string ConvertValueToDB(object value, DbTypes dbt = DbTypes.MsSql)
{
if (value == null)
{
return("null");
}
else if (value is DateTime)
{
return("'" + ((DateTime)value).ToString("yyyyMMdd HH:mm:ss.fffffff") + "'");
}
else if (value is TimeSpan)
{
return("'" + ((TimeSpan)value).ToString(@"hh\:mm\:ss\.fffffff") + "'");
}
else if (value is string)
{
return("'" + ((string)value).Replace("'", "''") + "'");
}
else if (value is byte[])
{
if (dbt == DbTypes.PG)
{
StringBuilder sb = new StringBuilder("E'\\\\x");
foreach (byte b in (byte[])value)
{
sb.Append(b.ToString("X2"));
}
sb.Append("'");
return(sb.ToString());
}
else
{
StringBuilder sb = new StringBuilder("0x");
foreach (byte b in (byte[])value)
{
sb.Append(b.ToString("X2"));
}
sb.Append("");
return(sb.ToString());
}
}
else if (value is int)
{
return(((int)value).ToString());
}
else if (value is long)
{
return(((long)value).ToString());
}
else if (value is long?)
{
if (((long?)value).HasValue)
{
return(((long?)value).Value.ToString());
}
else
{
return(null);
}
}
else if (value is byte)
{
return(((byte)value).ToString());
}
else if (value is short)
{
return(((short)value).ToString());
}
else if (value is short?)
{
if (((short?)value).HasValue)
{
return(((short?)value).Value.ToString());
}
else
{
return(null);
}
}
else if (value is bool)
{
if (dbt == DbTypes.PG)
{
if (((bool)value))
{
return("true");
}
else
{
return("false");
}
}
else
{
if (((bool)value))
{
return("1");
}
else
{
return("0");
}
}
}
else if (value is double)
{
return(((double)value).ToString().Replace(",", "."));
}
else if (value is decimal)
{
return(((decimal)value).ToString().Replace(",", "."));
}
if (value is IEnumerable collection)
{
if (dbt == DbTypes.PG)
{
return($"array[{string.Join(",", collection.Cast<object>().Select(x => x.ConvertToDB(dbt)))}]");
}
else
{
return($"values{string.Join(",", collection.Cast<object>().Select(x => "(" + x.ConvertToDB(dbt) + ")"))}");
}
}
else
{
return("'" + value.ToString() + "'");
}
}
/// <summary>
/// Получает свойства из массива байт
/// </summary>
/// <param name="data"></param>
/// <param name="serializedDataLength"></param>
public static Threshold ConvertForComponentDirective(byte[] data)
{
var item = new Threshold();
if (data == null)
{
data = new byte[SerializedDataLengthComponentDirective];
}
var serializedFirstPerformance = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, 0, serializedFirstPerformance, 0, Lifelength.SerializedDataLength);
item.FirstPerformanceSinceNew = Lifelength.ConvertFromByteArray(serializedFirstPerformance);
var serializedFirstNotification = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength, serializedFirstNotification, 0, Lifelength.SerializedDataLength);
item.FirstNotification = Lifelength.ConvertFromByteArray(serializedFirstNotification);
var serializedrepeatPerform = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength * 2, serializedrepeatPerform, 0, Lifelength.SerializedDataLength);
item.RepeatInterval = Lifelength.ConvertFromByteArray(serializedrepeatPerform);
var serializedNotification = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength * 3, serializedNotification, 0, Lifelength.SerializedDataLength);
item.RepeatNotification = Lifelength.ConvertFromByteArray(serializedNotification);
var serializedWarranty = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength * 4, serializedWarranty, 0, Lifelength.SerializedDataLength);
item.Warranty = Lifelength.ConvertFromByteArray(serializedWarranty);
var serializedWarrantyNotification = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, Lifelength.SerializedDataLength * 5, serializedWarrantyNotification, 0, Lifelength.SerializedDataLength);
item.WarrantyNotification = Lifelength.ConvertFromByteArray(serializedWarrantyNotification);
item.PerformRepeatedly = data[Lifelength.SerializedDataLength * 6] == 1;
item.FirstPerformanceConditionType = data[Lifelength.SerializedDataLength * 6 + 1] == 1 ? ThresholdConditionType.WhicheverLater : ThresholdConditionType.WhicheverFirst;
// если еще есть данные то рекурсивно считываем и его
var dataIndex = SerializedDataLengthComponentDirective;
var dataLeft = data.Length - dataIndex;
if (dataLeft >= sizeof(long))
{
var serializedEffectivityDate = new byte[sizeof(long)];
Array.Copy(data, dataIndex, serializedEffectivityDate, 0, sizeof(long));
item.EffectiveDate = new DateTime(DbTypes.Int64FromByteArray(serializedEffectivityDate, 0));
}
dataLeft -= sizeof(long);
dataIndex += sizeof(long);
if (dataLeft >= Lifelength.SerializedDataLength)
{
var serializedRepeatNotificaction = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, dataIndex, serializedRepeatNotificaction, 0, Lifelength.SerializedDataLength);
item.FirstPerformanceSinceEffectiveDate = Lifelength.ConvertFromByteArray(serializedRepeatNotificaction);
}
return(item);
}
/// <summary>
/// Convierte una expresión a una cadena que es compatible para usarla como parte de una clausula WHERE.
/// </summary>
/// <param name="dbType">Tipo de base de datos. Default MSSQL</param>
/// <returns>Cadena que contiene una version de la expresion como clausula WHERE.</returns>
public string ToWhereClause(DbTypes dbType = DbTypes.MsSql)
{
string clause = "";
if (LeftTerm == null)
{
return(null);
}
clause += "(";
if (LeftTerm is WrapperExpression wrapperExpression)
{
clause += wrapperExpression.ToWhereClause(dbType) + " ";
}
else
{
if (!(LeftTerm is string))
{
throw new ArgumentException(LeftTermToBeStringOrExpression);
}
if (Operator != Operators.Contains &&
Operator != Operators.ContainsNot &&
Operator != Operators.StartsWith &&
Operator != Operators.EndsWith)
{
//
// These operators will add the left term
//
clause += PreparedFieldname(dbType, LeftTerm.ToString()) + " ";
}
}
switch (Operator)
{
#region Process-By-Operators
case Operators.And:
#region And
if (RightTerm == null)
{
return(null);
}
clause += "AND ";
if (RightTerm is WrapperExpression expression)
{
clause += expression.ToWhereClause(dbType);
}
else
{
if (RightTerm is DateTime || RightTerm is DateTime?)
{
clause += "'" + DbTimestamp(dbType, RightTerm) + "'";
}
else if (RightTerm is int || RightTerm is long || RightTerm is decimal)
{
clause += RightTerm.ToString();
}
else
{
clause += PreparedStringValue(dbType, RightTerm.ToString());
}
}
break;
#endregion
case Operators.Or:
#region Or
if (RightTerm == null)
{
return(null);
}
clause += "OR ";
if (RightTerm is WrapperExpression expression1)
{
clause += expression1.ToWhereClause(dbType);
}
else
{
if (RightTerm is DateTime || RightTerm is DateTime?)
{
clause += "'" + DbTimestamp(dbType, RightTerm) + "'";
}
else if (RightTerm is int || RightTerm is long || RightTerm is decimal)
{
clause += RightTerm.ToString();
}
else
{
clause += PreparedStringValue(dbType, RightTerm.ToString());
}
}
break;
#endregion
case Operators.Equals:
#region Equals
if (RightTerm == null)
{
return(null);
}
clause += "= ";
if (RightTerm is WrapperExpression)
{
clause += ((WrapperExpression)RightTerm).ToWhereClause(dbType);
}
else
{
if (RightTerm is DateTime || RightTerm is DateTime?)
{
clause += "'" + DbTimestamp(dbType, RightTerm) + "'";
}
else if (RightTerm is int || RightTerm is long || RightTerm is decimal)
{
clause += RightTerm.ToString();
}
else
{
clause += PreparedStringValue(dbType, RightTerm.ToString());
}
}
break;
#endregion
case Operators.NotEquals:
#region NotEquals
if (RightTerm == null)
{
return(null);
}
clause += "<> ";
if (RightTerm is WrapperExpression)
{
clause += ((WrapperExpression)RightTerm).ToWhereClause(dbType);
}
else
{
if (RightTerm is DateTime || RightTerm is DateTime?)
{
clause += "'" + DbTimestamp(dbType, RightTerm) + "'";
}
else if (RightTerm is int || RightTerm is long || RightTerm is decimal)
{
clause += RightTerm.ToString();
}
else
{
clause += PreparedStringValue(dbType, RightTerm.ToString());
}
}
break;
#endregion
case Operators.In:
#region In
if (RightTerm == null)
{
return(null);
}
int inAdded = 0;
if (!IsList(RightTerm))
{
return(null);
}
List <object> inTempList = ObjectToList(RightTerm);
clause += " IN ";
if (RightTerm is WrapperExpression)
{
clause += ((WrapperExpression)RightTerm).ToWhereClause(dbType);
}
else
{
clause += "(";
foreach (object currObj in inTempList)
{
if (inAdded > 0)
{
clause += ",";
}
if (currObj is DateTime || currObj is DateTime?)
{
clause += "'" + DbTimestamp(dbType, currObj) + "'";
}
else if (currObj is int || currObj is long || currObj is decimal)
{
clause += currObj.ToString();
}
else
{
clause += PreparedStringValue(dbType, currObj.ToString());
}
inAdded++;
}
clause += ")";
}
break;
#endregion
case Operators.NotIn:
#region NotIn
if (RightTerm == null)
{
return(null);
}
int notInAdded = 0;
if (!IsList(RightTerm))
{
return(null);
}
List <object> notInTempList = ObjectToList(RightTerm);
clause += " NOT IN ";
if (RightTerm is WrapperExpression)
{
clause += ((WrapperExpression)RightTerm).ToWhereClause(dbType);
}
else
{
clause += "(";
foreach (object currObj in notInTempList)
{
if (notInAdded > 0)
{
clause += ",";
}
if (currObj is DateTime || currObj is DateTime?)
{
clause += "'" + DbTimestamp(dbType, currObj) + "'";
}
else if (currObj is int || currObj is long || currObj is decimal)
{
clause += currObj.ToString();
}
else
{
clause += PreparedStringValue(dbType, currObj.ToString());
}
notInAdded++;
}
clause += ")";
}
break;
#endregion
case Operators.Contains:
#region Contains
if (RightTerm == null)
{
return(null);
}
if (RightTerm is string)
{
clause +=
"(" +
PreparedFieldname(dbType, LeftTerm.ToString()) + " LIKE " + PreparedStringValue(dbType, "%" + RightTerm.ToString()) +
" OR " + PreparedFieldname(dbType, LeftTerm.ToString()) + " LIKE " + PreparedStringValue(dbType, "%" + RightTerm.ToString() + "%") +
" OR " + PreparedFieldname(dbType, LeftTerm.ToString()) + " LIKE " + PreparedStringValue(dbType, RightTerm.ToString() + "%") +
")";
}
else
{
return(null);
}
break;
#endregion
case Operators.ContainsNot:
#region ContainsNot
if (RightTerm == null)
{
return(null);
}
if (RightTerm is string)
{
clause +=
"(" +
PreparedFieldname(dbType, LeftTerm.ToString()) + " NOT LIKE " + PreparedStringValue(dbType, "%" + RightTerm.ToString()) +
" OR " + PreparedFieldname(dbType, LeftTerm.ToString()) + " NOT LIKE " + PreparedStringValue(dbType, "%" + RightTerm.ToString() + "%") +
" OR " + PreparedFieldname(dbType, LeftTerm.ToString()) + " NOT LIKE " + PreparedStringValue(dbType, RightTerm.ToString() + "%") +
")";
}
else
{
return(null);
}
break;
#endregion
case Operators.StartsWith:
#region StartsWith
if (RightTerm == null)
{
return(null);
}
if (RightTerm is string)
{
clause +=
"(" +
PreparedFieldname(dbType, LeftTerm.ToString()) + " LIKE " + PreparedStringValue(dbType, RightTerm.ToString() + "%") +
")";
}
else
{
return(null);
}
break;
#endregion
case Operators.EndsWith:
#region EndsWith
if (RightTerm == null)
{
return(null);
}
if (RightTerm is string)
{
clause +=
"(" +
PreparedFieldname(dbType, LeftTerm.ToString()) + " LIKE " + "%" + PreparedStringValue(dbType, RightTerm.ToString()) +
")";
}
else
{
return(null);
}
break;
#endregion
case Operators.GreaterThan:
#region GreaterThan
if (RightTerm == null)
{
return(null);
}
clause += "> ";
if (RightTerm is WrapperExpression)
{
clause += ((WrapperExpression)RightTerm).ToWhereClause(dbType);
}
else
{
if (RightTerm is DateTime || RightTerm is DateTime?)
{
clause += "'" + DbTimestamp(dbType, RightTerm) + "'";
}
else if (RightTerm is int || RightTerm is long || RightTerm is decimal)
{
clause += RightTerm.ToString();
}
else
{
clause += PreparedStringValue(dbType, RightTerm.ToString());
}
}
break;
#endregion
case Operators.GreaterThanOrEqualTo:
#region GreaterThanOrEqualTo
if (RightTerm == null)
{
return(null);
}
clause += ">= ";
if (RightTerm is WrapperExpression)
{
clause += ((WrapperExpression)RightTerm).ToWhereClause(dbType);
}
else
{
if (RightTerm is DateTime || RightTerm is DateTime?)
{
clause += "'" + DbTimestamp(dbType, RightTerm) + "'";
}
else if (RightTerm is int || RightTerm is long || RightTerm is decimal)
{
clause += RightTerm.ToString();
}
else
{
clause += PreparedStringValue(dbType, RightTerm.ToString());
}
}
break;
#endregion
case Operators.LessThan:
#region LessThan
if (RightTerm == null)
{
return(null);
}
clause += "< ";
if (RightTerm is WrapperExpression)
{
clause += ((WrapperExpression)RightTerm).ToWhereClause(dbType);
}
else
{
if (RightTerm is DateTime || RightTerm is DateTime?)
{
clause += "'" + DbTimestamp(dbType, RightTerm) + "'";
}
else if (RightTerm is int || RightTerm is long || RightTerm is decimal)
{
clause += RightTerm.ToString();
}
else
{
clause += PreparedStringValue(dbType, RightTerm.ToString());
}
}
break;
#endregion
case Operators.LessThanOrEqualTo:
#region LessThanOrEqualTo
if (RightTerm == null)
{
return(null);
}
clause += "<= ";
if (RightTerm is WrapperExpression)
{
clause += ((WrapperExpression)RightTerm).ToWhereClause(dbType);
}
else
{
if (RightTerm is DateTime || RightTerm is DateTime?)
{
clause += "'" + DbTimestamp(dbType, RightTerm) + "'";
}
else if (RightTerm is int || RightTerm is long || RightTerm is decimal)
{
clause += RightTerm.ToString();
}
else
{
clause += PreparedStringValue(dbType, RightTerm.ToString());
}
}
break;
#endregion
case Operators.IsNull:
#region IsNull
clause += " IS NULL";
break;
#endregion
case Operators.IsNotNull:
#region IsNotNull
clause += " IS NOT NULL";
break;
#endregion
#endregion
}
clause += ")";
return(clause.Replace(" ", " "));
}
async static Task <int> Main(string[] args)
{
try
{
string serverUrl = null, indexName = null;
DbTypes dbType = DbTypes.Unknown;
string connectionString = null, sqlStatement = null;
Dictionary <string, string> fields = new Dictionary <string, string>();
#region Parse Arguments
for (int i = 0; i < args.Length; i++)
{
switch (args[i].ToLower())
{
//case "-remove":
// removeIndex = true;
// break;
}
if (i < args.Length - 1)
{
switch (args[i].ToLower())
{
case "-server":
case "-s":
serverUrl = args[++i];
break;
case "-index":
case "-i":
indexName = args[++i];
break;
case "-db-type":
if (!Enum.TryParse <DbTypes>(args[++i], true, out dbType))
{
throw new Exception($"Unknown Db-Type { args[i] }");
}
break;
case "-db":
case "-db-connectionstring":
connectionString = args[++i];
break;
case "-sql":
case "-sql-statement":
sqlStatement = args[++i];
break;
}
}
if (i < args.Length - 2)
{
switch (args[i].ToLower())
{
case "-f":
fields[args[++i]] = args[++i];
break;
}
}
}
if (String.IsNullOrEmpty(serverUrl) ||
String.IsNullOrEmpty(indexName) ||
dbType == DbTypes.Unknown ||
String.IsNullOrEmpty(connectionString) ||
String.IsNullOrEmpty(sqlStatement) ||
fields.Count == 0)
{
Console.WriteLine("Usage:");
Console.WriteLine("ImportDbTable.exe -server[-s] {server}");
Console.WriteLine(" -index[-i] {indexname}");
Console.WriteLine(" -db-type {dbType}");
Console.WriteLine(" -db-connectionstring[-db] {connectionString}");
Console.WriteLine(" -sql-statement[-sql] {sqlStatement}");
Console.WriteLine(" -f {indexfield} {expression} // {expression}: \"lorem {{DB_FIELD1}} ipsum {{DB_FIELD2}}\"");
Console.WriteLine();
//Console.WriteLine($"FieldTypes: { String.Join(", ", FieldTypes.Values()) }");
return(1);
}
#endregion
var startTime = DateTime.Now;
int counter = 0;
using (var client = new LuceneServerClient(serverUrl, indexName))
{
var items = new List <IDictionary <string, object> >();
string regexDbFieldsPattern = @"{{(.*?)}}";
using (var connection = DbConnectionFactory.CreateInstance(dbType, connectionString))
{
foreach (IDictionary <string, object> row in connection.Query(sqlStatement, buffered: false))
{
if (row != null)
{
var item = new Dictionary <string, object>();
foreach (var indexField in fields.Keys)
{
string expression = fields[indexField];
var matches = Regex.Matches(expression, regexDbFieldsPattern).Select(m => m.ToString().Substring(2, m.ToString().Length - 4)).ToArray();
foreach (var match in matches)
{
expression = expression.Replace($"{{{{{ match }}}}}", row[match]?.ToString() ?? String.Empty);
}
item[indexField] = expression;
}
items.Add(item);
counter++;
}
if (counter % 1000 == 0)
{
await IndexItems(client, items, counter);
}
}
}
await client.IndexDocumentsAsync(items);
}
Console.WriteLine($"{ counter } records ... { Math.Round((DateTime.Now-startTime).TotalMinutes, 2) } minutes");
Console.WriteLine("finished successfully");
return(0);
}
catch (Exception ex)
{
Console.WriteLine();
Console.WriteLine("Exception:");
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
return(1);
}
}
public Field(string columnName, DbTypes fieldType, object fieldValue)
: this(columnName, fieldValue)
{
this.DataType = fieldType;
}
public ColumnDefinition(string name, DbTypes fieldType)
{
this.Name = name;
this.FieldType = fieldType;
}
private static IEnumerable <Type> GetEnumTypes(DbTypes dbTypes)
{
Assembly assembly = Assembly.Load($"{nameof(SocialEventManager)}.{nameof(DLL)}");
return(GetEnumTypes(dbTypes, assembly));
}
public static string InsertIntoTable(string tableName, List <string> columns, List <Dictionary <string, object> > vals, DbTypes dbt = DbTypes.MsSql)
{
StringBuilder sb = new StringBuilder("");
string cols = string.Join(",", columns);
foreach (var v in vals)
{
sb.Append("insert into " + tableName + "(" + cols + ") select ");
for (int i = 0; i < columns.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(v.GetElement(columns[i]).ConvertToDB(dbt));
}
sb.AppendLine(";");
}
return(sb.ToString());
}
/// <summary>
/// Получает свойства из массива байт
/// </summary>
/// <param name="data"></param>
public static DirectiveThreshold ConvertFromByteArray(byte[] data)
{
DirectiveThreshold item = new DirectiveThreshold();
if (data == null)
{
data = new byte[SerializedDataLength];
}
byte[] serializedEffectivityDate = new byte[sizeof(long)];
Array.Copy(data, 0, serializedEffectivityDate, 0, sizeof(long));
item.EffectiveDate = new DateTime(DbTypes.Int64FromByteArray(serializedEffectivityDate, 0));
byte[] serializedPerformSinceNew = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, sizeof(long), serializedPerformSinceNew, 0, Lifelength.SerializedDataLength);
item.FirstPerformanceSinceNew = Lifelength.ConvertFromByteArray(serializedPerformSinceNew);
byte[] serializedrepeatPerform = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, sizeof(long) + Lifelength.SerializedDataLength, serializedrepeatPerform, 0, Lifelength.SerializedDataLength);
item.RepeatInterval = Lifelength.ConvertFromByteArray(serializedrepeatPerform);
byte[] serializedNotification = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, sizeof(long) + Lifelength.SerializedDataLength * 2, serializedNotification, 0, Lifelength.SerializedDataLength);
item.FirstNotification = Lifelength.ConvertFromByteArray(serializedNotification);
item.PerformSinceNew = data[sizeof(long) + Lifelength.SerializedDataLength * 3] == 1;
item.PerformSinceEffectiveDate = data[sizeof(long) + Lifelength.SerializedDataLength * 3 + 1] == 1;
item.PerformRepeatedly = data[sizeof(long) + Lifelength.SerializedDataLength * 3 + 2] == 1;
byte[] serializedSinceEffectivityLifelength = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, sizeof(long) + Lifelength.SerializedDataLength * 3 + 3, serializedSinceEffectivityLifelength, 0, Lifelength.SerializedDataLength);
item.FirstPerformanceSinceEffectiveDate = Lifelength.ConvertFromByteArray(serializedSinceEffectivityLifelength);
// если у нас еще есть данные то рекурсивно считываем и его
int dataIndex = SerializedDataLength;
int dataLeft = data.Length - SerializedDataLength;
if (dataLeft >= Lifelength.SerializedDataLength)
{
byte[] serializedRepeatNotificaction = new byte[Lifelength.SerializedDataLength];
Array.Copy(data, sizeof(long) + Lifelength.SerializedDataLength * 4 + 3, serializedRepeatNotificaction, 0, Lifelength.SerializedDataLength);
item.RepeatNotification = Lifelength.ConvertFromByteArray(serializedRepeatNotificaction);
dataLeft -= Lifelength.SerializedDataLength;
dataIndex += Lifelength.SerializedDataLength;
}
if (dataLeft >= sizeof(byte))
{
item.FirstPerformanceConditionType = data[dataIndex] == 0
? ThresholdConditionType.WhicheverFirst
: ThresholdConditionType.WhicheverLater;
dataLeft -= sizeof(byte);
dataIndex += sizeof(byte);
}
if (dataLeft >= sizeof(byte))
{
item.RepeatPerformanceConditionType = data[dataIndex] == 0
? ThresholdConditionType.WhicheverFirst
: ThresholdConditionType.WhicheverLater;
}
return(item);
}
public virtual DataTable GetDataTable(DbTypes DbType, string[] ids)
{
return DbLogic.GetDataTable(DbType, DbHelper, this.CurrentTableName, BaseBusinessLogic.FieldId, ids);
}
public static IEnumerable <RelationalEvent> ProcessJsonObject(JToken t, string eventName, RelationalEvent parent)
{
var relation = new RelationalEvent();
relation._Id_ = RelationalEvent.GetKey();
relation._At_ = DateTime.UtcNow;
relation.Name = eventName.ToLowerInvariant();
if (parent != null)
{
relation._ParentId_ = parent._Id_;
relation.ParentType = parent.Name;
relation.Name = relation.ParentType + "_" + relation.Name;
}
else
{
relation._ParentId_ = null;
relation.ParentType = null;
}
if (t.Type == JTokenType.Object)
{
// If we have an object, we want to create fields for all the
// "simple" types, and child items for all the complex ones
foreach (var child in t.Children())
{
if (child.Type == JTokenType.Property)
{
var property = child as JProperty;
var dbType = DbTypes.FromJsonType(property.Value.Type);
if (dbType != DbType.Unknown)
{
relation.AddField(property.Name, dbType, GetValue(property, dbType));
}
else
{
if (property.Value.Type == JTokenType.Array)
{
foreach (var r in ProcessJsonArray(property.Value, property.Name, relation))
{
yield return(r);
}
}
if (property.Value.Type == JTokenType.Object)
{
foreach (var r in ProcessJsonObject(property.Value, property.Name, relation))
{
yield return(r);
}
}
}
}
}
}
yield return(relation);
}
public Field(string columnName, DbTypes fieldType, object fieldValue)
: this(columnName, fieldValue)
{
this.DataType = fieldType;
}
public DbActions(string connectionString, DbTypes dbType)
{
_connectionString = connectionString;
_dbType = dbType;
}
public static string ConvertToDB <T>(this T value, DbTypes dbt = DbTypes.MsSql)
{
return(ConvertValueToDB(value, dbt));
}
public ColumnDefinition(string name, DbTypes fieldType)
{
this.Name = name;
this.FieldType = fieldType;
}
public DatabaseConnectionBuilder withType(DbTypes type)
{
this.type = type;
return(this);
}
public MySearchUtil(DbTypes dbType = DbTypes.SqlServer)
{
_sqlParameterPrefix = DbTypeUtil.GetDbParamPrefix(dbType);
}
public static MySearchUtil New(DbTypes dbType = DbTypes.SqlServer)
{
return(new MySearchUtil(dbType));
}
public void UseDatabaseFromConfiguration_should_configure_context_per_db_type(DbTypes dbTypes)
{
var configuration = new ConfigurationBuilder().AddInMemoryCollection(new Dictionary <string, string>
{
["ConnectionStrings:DefaultConnection"] = "invalid",
["DbType"] = dbTypes.ToString(),
["Migrate"] = "true",
}).Build();
var environementMock = new Mock <IWebHostEnvironment>();
var storeMock = new Mock <IDynamicProviderStore <SchemeDefinition> >();
storeMock.SetupGet(m => m.SchemeDefinitions).Returns(Array.Empty <SchemeDefinition>().AsQueryable()).Verifiable();
var sut = new Startup(configuration, environementMock.Object);
using var host = WebHost.CreateDefaultBuilder()
.ConfigureServices(services =>
{
sut.ConfigureServices(services);
services.AddTransient(p => storeMock.Object);
})
.Configure(builder => sut.Configure(builder))
.UseSerilog((hostingContext, configuration) =>
configuration.ReadFrom.Configuration(hostingContext.Configuration))
.Build();
if (dbTypes != DbTypes.InMemory)
{
Assert.ThrowsAny <Exception>(() => host.Start());
}
}
public bool GetDiscover(DbTypes dbType, out IDbDiscover dbDiscover)
{
dbDiscover = _dbDiscovers.FirstOrDefault(a => a.DbType == dbType);
return(dbDiscover != null);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
string connectionString = _configuration.GetConnectionString("DefaultConnection");
Debug.Assert(!String.IsNullOrEmpty(connectionString), "connection string is null or empty");
if (DbTypes.IsInMemory() || _environment.IsTest())
{
services.AddDbContext <BooksDbContext>(options =>
{
options.UseInMemoryDatabase("books");
});
}
else if (DbTypes.IsMySql())
{
services.AddDbContext <BooksDbContext>(options =>
{
options.UseMySql(connectionString);
});
}
else if (DbTypes.IsSqlite())
{
services.AddDbContext <BooksDbContext>(options =>
{
options.UseSqlite(connectionString);
});
}
else
{
services.AddDbContext <BooksDbContext>(options =>
{
options.UseSqlServer(connectionString);
});
}
services.AddIdentity <IdentityUser <Guid>, IdentityRole <Guid> >(config =>
{
config.SignIn.RequireConfirmedEmail = true;
})
.AddEntityFrameworkStores <BooksDbContext>()
.AddUserManager <UserManager <IdentityUser <Guid> > >()
.AddRoleManager <RoleManager <IdentityRole <Guid> > >();
services.AddTransient <TokenService>();
services.AddCors(o => o.AddPolicy("AllowAll", builder =>
{
builder.AllowAnyOrigin()
.AllowAnyMethod()
.AllowAnyHeader();
}));
services.AddControllers()
.AddNewtonsoftJson(options =>
{
options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
})
.AddMvcOptions(options =>
{
options.EnableEndpointRouting = false;
options.RespectBrowserAcceptHeader = true;
});
services.AddLogging();
services.AddAuthentication(x =>
{
x.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
x.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
x.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
})
.AddJwtBearer(x =>
{
var jwtSection = _configuration.GetSection("Jwt");
Debug.Assert(jwtSection.Exists(), "Jwt section missing in appsettings");
string secret = _configuration.GetSection("Jwt")["Secret"];
Debug.Assert(!String.IsNullOrEmpty(secret), "Jwt secret missing in appsettings");
string audience = _configuration.GetSection("Jwt")["Audience"];
Debug.Assert(!String.IsNullOrEmpty(audience), "Jwt audience missing in appsettings");
string authority = _configuration.GetSection("Jwt")["Authority"];
var key = Encoding.UTF8.GetBytes(secret);
Microsoft.IdentityModel.Logging.IdentityModelEventSource.ShowPII = _environment.IsTestOrDevelopment();
x.Events = new JwtBearerEvents()
{
OnAuthenticationFailed = context =>
{
context.Response.StatusCode = 401;
context.Response.ContentType = "application/json; charset=utf-8";
var message = _environment.IsTestOrDevelopment() ?
context.Exception.ToString() :
"An error occurred processing your authentication.";
var result = Newtonsoft.Json.JsonConvert.SerializeObject(new { message });
return(HttpResponseWritingExtensions.WriteAsync(context.Response, result));
}
};
x.RequireHttpsMetadata = !_environment.IsTestOrDevelopment();
x.SaveToken = true;
x.Audience = audience;
if (String.IsNullOrEmpty(authority))
{
// use local validation
x.TokenValidationParameters = new TokenValidationParameters
{
ValidateIssuerSigningKey = true,
IssuerSigningKey = new SymmetricSecurityKey(key),
ValidateIssuer = false,
ValidateAudience = false
};
}
else
{
// use sso validation
x.Authority = authority;
}
});
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo
{
Version = "v1",
Title = "Books API",
Description = "API for Books",
TermsOfService = new Uri("http://tempuri.org/terms"),
Contact = new OpenApiContact()
{
Name = "Mykeels.",
Email = "*****@*****.**",
Url = new Uri("https://twitter.com/mykeels")
}
});
var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
c.IncludeXmlComments(xmlPath);
});
}
/// <summary>
/// 字段值数组转换为字符串列表
/// </summary>
/// <param name="ids">字段值</param>
/// <returns>字段值字符串</returns>
public static string ObjectsToList(DbTypes DbType,Object[] ids)
{
string returnValue = string.Empty;
string stringList = string.Empty;
for (int i = 0; i < ids.Length; i++)
{
switch (DbType)
{
case DbTypes.Access:
stringList += ids[i] + ", ";
break;
default:
stringList += "'";
stringList += ids[i] + "',";
break;
}
}
if (ids.Length == 0)
{
returnValue = " NULL ";
}
else
{
returnValue = CutLastDot(stringList.Substring(0, stringList.Length - 1));
}
return returnValue;
}
