/// <summary> /// Метод, проверяющий наличие объекта в базе данных по имени /// </summary> /// <param name="obj_name">Искомый объект</param> /// <param name="obj_type">Тип объекта(таблица, триггер и т.д.)</param> /// <param name="CaseSensivity">Чувствительность регистра</param> /// <returns></returns> public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { string sql = ""; switch (obj_type) { case DataBaseObjects.table: if (CaseSensivity) { sql = string.Format("select 1 from information_schema.TABLES where table_name='{0}'", obj_name); } else { sql = string.Format("select 1 from information_schema.TABLES where lower(table_name)=lower('{0}')", obj_name); } break; } var res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (res != null && res != DBNull.Value) { return(true); } return(false); }
/// <summary> /// Метод, проверяющий наличие объекта в базе данных по имени /// </summary> /// <param name="obj_name">Искомый объект</param> /// <param name="obj_type">Тип объекта(таблица, триггер и т.д.)</param> /// <param name="CaseSensivity">Чувствительность регистра</param> /// <returns></returns> public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { if (Connected) { //--Тут надо переделывать, т.к. в объектах нет колонок, да и ф-ции тоже разных типов! так что пока только таблички можно искать string sql = ""; if (CaseSensivity) { sql = string.Format("select 1 from sys.objects where type='{0}' and name='{1}'", GetObjectTypeCode(obj_type), obj_name); } else { sql = string.Format("select 1 from sys.objects where type='{0}' and lower(name)=lower('{1}')", GetObjectTypeCode(obj_type), obj_name); } var exec_res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (exec_res != null && exec_res != DBNull.Value) { return(true); } } else { if (MessagePrinter != null) { MessagePrinter.PrintMessage(ErrorsManager.Messages.NotConnectedMsg, ErrorsManager.Messages.ErrorMsg, 1, 1); } } return(false); }
public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { //--надо смотреть табличку sqlite_master if (Connected) { string sql = ""; if (CaseSensivity) { sql = string.Format("select 1 from sqlite_master where type='{0}' and name='{1}'", GetObjectTypeCode(obj_type), obj_name); } else { sql = string.Format("select 1 from sqlite_master where type='{0}' and lower(name)=lower('{1}')", GetObjectTypeCode(obj_type), obj_name); } var exec_res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (exec_res != null && exec_res != DBNull.Value) { return(true); } } else { if (MessagePrinter != null) { MessagePrinter.PrintMessage(ErrorsManager.Messages.NotConnectedMsg, ErrorsManager.Messages.ErrorMsg, 1, 1); } } return(false); }
public void Obj() { using (var conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=sakila;User Id=test;Password=test;")) { conn.Open(); var tables = new DataBaseObjects(conn, new ITableFilter[] { }, new ITableFilter[] { }); using (var filef = File.Open("out.txt", FileMode.Create)) using (var file = new StreamWriter(filef)) { file.WriteLine("tables"); file.WriteLine(String.Join(Environment.NewLine, tables.Tables.Values .Select(p => p.TableName).ToArray())); file.WriteLine("primal keys"); file.WriteLine(String.Join(Environment.NewLine, new PrimalKeyAnalysis().PrimalPrimaryKeys(tables.Tables.Values, tables.ForeignKeys) .Select(p => p.Key + ": " + p.Value).ToArray())); var fks = new PropableForeignKeyAnalysis().GetProbableForeignKeys(tables.Tables.Values); file.WriteLine("fks"); file.WriteLine(String.Join(Environment.NewLine, fks.Select( fk => string.Format("{0}->{1} : {2}", fk.TableName, fk.ConstraintKeys.First().From.ColumnName, fk.ConstraintKeys.First().To.TableName /*fk.OtherTable.TableName*/)).ToArray())); } } }
public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { if (Connected) { string sql = ""; if (CaseSensivity) { sql = "select 1 from all_objects where object_name='{0}' and object_type='{1}'"; if (!string.IsNullOrEmpty(_owner)) { sql += " and owner='{2}'"; } } else { sql = "select 1 from all_objects where upper(object_name)=upper('{0}') and object_type='{1}'"; if (!string.IsNullOrEmpty(_owner)) { sql += " and upper(owner)=upper('{2}')"; } } if (string.IsNullOrEmpty(_owner)) { sql = string.Format(sql, obj_name, GetObjectTypeCode(obj_type)); } else { sql = string.Format(sql, obj_name, GetObjectTypeCode(obj_type), _owner); } var exec_res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (exec_res != null && exec_res != DBNull.Value) { return(true); } } else { if (MessagePrinter != null) { MessagePrinter.PrintMessage(ErrorsManager.Messages.NotConnectedMsg, ErrorsManager.Messages.ErrorMsg, 1, 1); } } return(false); }
protected string GetObjectTypeCode(DataBaseObjects db_object) { string res = "TABLE"; switch (db_object) { case DataBaseObjects.foreign_key: res = ""; break; case DataBaseObjects.function: res = ""; break; case DataBaseObjects.index: res = ""; break; case DataBaseObjects.primary_key: res = ""; break; case DataBaseObjects.procedure: res = ""; break; case DataBaseObjects.table: res = "TABLE"; break; case DataBaseObjects.trigger: res = "TRIGGER"; break; case DataBaseObjects.type: res = ""; break; } return(res); }
protected string GetObjectTypeCode(DataBaseObjects db_object) { string res = "table"; switch (db_object) { /*case DataBaseObjects.foreign_key: * res = "foreing key"; * break; * * case DataBaseObjects.function: * res = "FN"; * break; * * case DataBaseObjects.index: * res = "UQ"; * break; * * case DataBaseObjects.primary_key: * res = "PK"; * break; * * case DataBaseObjects.procedure: * res = "P"; * break;*/ case DataBaseObjects.table: res = "table"; break; /*case DataBaseObjects.trigger: * res = "TR"; * break; * * case DataBaseObjects.type: * res = "T"; * break;*/ } return(res); }
public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { string search_tablename = ""; string search_pars = ""; switch (obj_type) { case DataBaseObjects.table: search_tablename = "information_schema.tables"; if (!CaseSensivity) { search_pars = string.Format("lower(table_name)=lower('{0}')", obj_name); if (!string.IsNullOrEmpty(_owner)) { search_pars = string.Format("{0} and lower(table_scheam)=lower('{0}')", search_pars, _owner); } } else { search_pars = string.Format("table_name='{0}'", obj_name); if (!string.IsNullOrEmpty(_owner)) { search_pars = string.Format("{0} and table_scheam='{0}'", search_pars, _owner); } } break; } string sql = string.Format("select 1 from {0} where {1}", search_tablename, search_pars); var res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (res != null && res != DBNull.Value) { return(true); } return(false); }
/// <summary> /// Метод, проверяющий наличие объекта в базе данных по имени /// </summary> /// <param name="obj_name">Искомый объект</param> /// <param name="obj_type">Тип объекта(таблица, триггер и т.д.)</param> /// <param name="CaseSensivity">Чувствительность регистра</param> /// <returns></returns> public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { string sql = ""; switch (obj_type) { case DataBaseObjects.table: if (CaseSensivity) sql = string.Format("select 1 from information_schema.TABLES where table_name='{0}'", obj_name); else sql = string.Format("select 1 from information_schema.TABLES where lower(table_name)=lower('{0}')", obj_name); break; } var res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (res != null && res != DBNull.Value) return true; return false; }
protected string GetObjectTypeCode(DataBaseObjects db_object) { string res = "U"; switch (db_object) { case DataBaseObjects.foreign_key: res = "F"; break; case DataBaseObjects.function: res = "FN"; break; case DataBaseObjects.index: res = "UQ"; break; case DataBaseObjects.primary_key: res = "PK"; break; case DataBaseObjects.procedure: res = "P"; break; case DataBaseObjects.table: res = "U"; break; case DataBaseObjects.trigger: res = "TR"; break; case DataBaseObjects.type: res = "T"; break; } return res; }
/// <summary> /// Метод, проверяющий наличие объекта в базе данных по имени /// </summary> /// <param name="obj_name">Искомый объект</param> /// <param name="obj_type">Тип объекта(таблица, триггер и т.д.)</param> /// <param name="CaseSensivity">Чувствительность регистра</param> /// <returns></returns> public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { if (Connected) { //--Тут надо переделывать, т.к. в объектах нет колонок, да и ф-ции тоже разных типов! так что пока только таблички можно искать string sql = ""; if (CaseSensivity) { sql = string.Format("select 1 from sys.objects where type='{0}' and name='{1}'", GetObjectTypeCode(obj_type), obj_name); } else { sql = string.Format("select 1 from sys.objects where type='{0}' and lower(name)=lower('{1}')", GetObjectTypeCode(obj_type), obj_name); } var exec_res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (exec_res != null && exec_res != DBNull.Value) return true; } else { if (MessagePrinter != null) MessagePrinter.PrintMessage(ErrorsManager.Messages.NotConnectedMsg, ErrorsManager.Messages.ErrorMsg, 1, 1); } return false; }
public abstract bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity);
public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { if (Connected) { string sql = ""; if (CaseSensivity) { sql = "select 1 from all_objects where object_name='{0}' and object_type='{1}'"; if (!string.IsNullOrEmpty(_owner)) sql += " and owner='{2}'"; } else { sql = "select 1 from all_objects where upper(object_name)=upper('{0}') and object_type='{1}'"; if (!string.IsNullOrEmpty(_owner)) sql += " and upper(owner)=upper('{2}')"; } if (string.IsNullOrEmpty(_owner)) sql = string.Format(sql, obj_name, GetObjectTypeCode(obj_type)); else sql = string.Format(sql, obj_name, GetObjectTypeCode(obj_type), _owner); var exec_res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (exec_res != null && exec_res != DBNull.Value) return true; } else { if (MessagePrinter != null) MessagePrinter.PrintMessage(ErrorsManager.Messages.NotConnectedMsg, ErrorsManager.Messages.ErrorMsg, 1, 1); } return false; }
public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { string search_tablename = ""; string search_pars = ""; switch (obj_type) { case DataBaseObjects.table: search_tablename = "information_schema.tables"; if (!CaseSensivity) { search_pars = string.Format("lower(table_name)=lower('{0}')", obj_name); if (!string.IsNullOrEmpty(_owner)) search_pars = string.Format("{0} and lower(table_scheam)=lower('{0}')", search_pars, _owner); } else { search_pars = string.Format("table_name='{0}'", obj_name); if (!string.IsNullOrEmpty(_owner)) search_pars = string.Format("{0} and table_scheam='{0}'", search_pars, _owner); } break; } string sql = string.Format("select 1 from {0} where {1}", search_tablename, search_pars); var res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (res != null && res != DBNull.Value) return true; return false; }
public override bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity) { //--надо смотреть табличку sqlite_master if (Connected) { string sql = ""; if (CaseSensivity) { sql = string.Format("select 1 from sqlite_master where type='{0}' and name='{1}'", GetObjectTypeCode(obj_type), obj_name); } else { sql = string.Format("select 1 from sys.objects where type='{0}' and lower(name)=lower('{1}')", GetObjectTypeCode(obj_type), obj_name); } var exec_res = ExecuteScalarCommand(CreateCommand(sql).ExecuteScalar); if (exec_res != null && exec_res != DBNull.Value) return true; } else { if (MessagePrinter != null) MessagePrinter.PrintMessage(ErrorsManager.Messages.NotConnectedMsg, ErrorsManager.Messages.ErrorMsg, 1, 1); } return false; }