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