/// <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 abstract bool IsDBObjectExists(string obj_name, DataBaseObjects obj_type, bool CaseSensivity);
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;
}