/// <summary> /// Проверка существования столбца /// </summary> /// <param name="db"></param> /// <param name="table_name"></param> /// <param name="column_name"></param> /// <returns></returns> internal static bool CheckColumn(this DatabaseSqlite db, string table_name, string column_name) { if (!db.IsConnected()) { db.Connect(); } if (db.IsConnected()) { using (var command = db.connection.CreateCommand()) { try { string query = $"PRAGMA table_info({table_name});"; command.CommandText = query; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var name = reader["name"]?.ToString() ?? string.Empty; if (name.Equals(column_name)) { return(true); } } } } catch (Exception ex) { db.CallError(ex.Message, ex.StackTrace); } } } return(false); }
/// <summary> /// Инициализация таблиц базы данных /// </summary> /// <param name="db"></param> internal static void InitSchemas(this DatabaseSqlite db) { if (!db.IsConnected()) { db.Connect(); } foreach (var table in db.tables_schemas) { bool isupdate = false; if (CheckTable(db, table.TableName)) { IEnumerable <SchemasColumn> dif_columns = new SchemasColumn[0]; if (!TryCheckColumns(db, table.TableName, table.Columns, out dif_columns)) { foreach (var column in dif_columns) { CreatColumn(db, table.TableName, column); } isupdate = true; } if (isupdate && !string.IsNullOrWhiteSpace(table.UpdateQuery)) { db.ExecuteNonQuery(table.UpdateQuery); } } else { CreatTable(db, table); } } }
/// <summary> /// Создание столбца /// </summary> /// <param name="db"></param> /// <param name="table_name"></param> /// <param name="column"></param> /// <returns></returns> internal static bool CreatColumn(this DatabaseSqlite db, string table_name, SchemasColumn column) { if (!db.IsConnected()) { db.Connect(); } if (db.IsConnected()) { string query = $"ALTER TABLE {table_name} ADD COLUMN {column.Name} {column.ColumnType}"; if (column.DefaultContent != null) { query += $" DEFAULT ('{column.DefaultContent.ToString().Replace("'", "").Replace("\\", "")}')"; } return(db.ExecuteNonQuery(query)); } return(false); }
/// <summary> /// Проверка существования таблицы /// </summary> /// <param name="db"></param> /// <param name="table_name"></param> /// <returns></returns> internal static bool CheckTable(this DatabaseSqlite db, string table_name) { if (!db.IsConnected()) { db.Connect(); } if (db.IsConnected()) { string query = "SELECT name FROM sqlite_master WHERE type='table' AND name = @table_name;"; Dictionary <string, object> param = new Dictionary <string, object>() { { "table_name", table_name } }; string tbl_name = db.ExecuteScalar(query, param)?.ToString(); return(!string.IsNullOrWhiteSpace(tbl_name)); } return(false); }
/// <summary> /// Создание таблицы /// </summary> /// <param name="db"></param> /// <param name="structure"></param> /// <returns></returns> internal static bool CreatTable(this DatabaseSqlite db, SchemasTable structure) { if (!db.IsConnected()) { db.Connect(); } if (db.IsConnected()) { string columns = string.Join(", ", structure.Columns.Select(o => o.GetQueryString())); string query = $"CREATE TABLE {structure.TableName} ({columns});"; if (db.ExecuteNonQuery(query)) { if (!string.IsNullOrWhiteSpace(structure.FirstQuery)) { db.ExecuteNonQuery(structure.FirstQuery); } return(true); } } return(false); }
/// <summary> /// Проверка существования столбцов /// </summary> /// <param name="db"></param> /// <param name="table_name"></param> /// <param name="columns"></param> /// <param name="dif_columns">коллекция столбцов, который отсутствуют в таблице</param> /// <returns>true - если таблица соответствует схеме</returns> internal static bool TryCheckColumns(this DatabaseSqlite db, string table_name, IEnumerable <SchemasColumn> columns, out IEnumerable <SchemasColumn> dif_columns) { if (!db.IsConnected()) { db.Connect(); } if (db.IsConnected()) { using (var command = db.connection.CreateCommand()) { try { string query = $"PRAGMA table_info({table_name});"; command.CommandText = query; using (var reader = command.ExecuteReader()) { List <string> list_columns = new List <string>(); while (reader.Read()) { var name = reader["name"]?.ToString() ?? string.Empty; if (!string.IsNullOrWhiteSpace(name)) { list_columns.Add(name); } } dif_columns = columns.Where(o => list_columns.Find(x => x.Equals(o.Name)) == null).ToArray(); return(dif_columns.Count() == 0); } } catch (Exception ex) { db.CallError(ex.Message, ex.StackTrace); } } } dif_columns = Enumerable.Empty <SchemasColumn>(); return(false); }