/// <summary>
/// Удаление модели данных командой
/// </summary>
/// <returns></returns>
public virtual Exception Delete()
{
object keyValue = GetProperty <object>(SchemaTableManager.GetPrimaryKeyField(this.GetType()));
if (_dataAdapter != null && _dataAdapter.DeleteCommand != null)
{
UniDbCommand deleteCmd = new UniDbCommand(_dataAdapter.DeleteCommand.CommandText, this.Connection);
if (_dataAdapter.DeleteCommand.Parameters.Count > 0)
{
UniParameter p = _dataAdapter.DeleteCommand.Parameters[0];
deleteCmd.Parameters.Add(p.ParameterName, p.UniDbType, keyValue);
}
else
{
deleteCmd.Parameters.Add("p_" + SchemaTableManager.GetPrimaryKeyField(this.GetType()), UniDbType.Decimal, keyValue);
}
return(ExecuteDeleteCommand(deleteCmd));
}
else
{
UniDbCommand deleteCmd = UniCommandBuilder.GetDeleteCommand(SchemaTable, TableName, SchemaTableManager.GetUpdatedColumns(TableName, DataTable));
deleteCmd.Parameters[0].Value = keyValue;
deleteCmd.Parameters[0].SourceColumn = string.Empty;
return(ExecuteDeleteCommand(deleteCmd));
}
}
public static void RunAsyncWithWaitDialog(DependencyObject sender, string caption, DoWorkEventHandler dowork,
object argument, UniDbCommand executingCommand, RunWorkerCompletedEventHandler workComplete, bool inQueue = true)
{
AbortableBackgroundWorker bw = new AbortableBackgroundWorker();
bw.WorkerSupportsCancellation = true;
bw.WorkerReportsProgress = true;
bw.DoWork += dowork;
bw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bw_RunWorkerCompleted);
bw.RunWorkerCompleted += workComplete;
bw.ProgressChanged += Bw_ProgressChanged;
bw.ExecutingCommand = executingCommand;
bw.Argument = argument;
bw.InQueue = inQueue;
bw.CurrentStatus = caption;
WaitWindow f = new WaitWindow(bw);
f.Owner = Window.GetWindow(sender);
bw.waitDialog = f;
f.Show();
if (bw.InQueue)
{
Queue.Enqueue(bw);
}
else
{
bw.RunWorkerAsync(argument);
}
}
/// <summary>
/// Получаем команду обновления данных согласно правилам
/// </summary>
/// <param name="schemaName">имя схемы</param>
/// <param name="tableName">имя таблицы</param>
/// <param name="columns">колонки по схеме</param>
/// <returns>Возвращает команду</returns>
public static UniDbCommand GetUpdateCommand(string schemaName, string tableName, IEnumerable <UniSchemaColumn> columns)
{
string proc_parameters = string.Join(", ", columns.Select(r => $"p_{r.DbColumnName}=>:p_{r.DbColumnName}"));
UniDbCommand updateCommand = new UniDbCommand($"begin {schemaName}.{tableName}_UPDATE({proc_parameters});end;");
updateCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
foreach (var p in columns)
{
UniParameter current_par = updateCommand.Parameters.Add("p_" + p.DbColumnName, p.DbColumnType);
current_par.SourceColumn = p.DbColumnName;
if (p.IsPrimaryKey)
{
current_par.Direction = ParameterDirection.InputOutput;
if (p.DbColumnType == UniDbType.Decimal || p.DbColumnType == UniDbType.Int)
{
current_par.DbType = DbType.Decimal;
}
}
else
{
current_par.Direction = ParameterDirection.Input;
}
}
return(updateCommand);
}
/// <summary>
/// Загрузка справочника доступных пользователю команд (компонентов)
/// </summary>
public static void LoadControlRoles()
{
if (UniDbConnection.Current != null)
{
_controlRoles.Clear();
DataTable t = new DataTable();
UniDbCommand com = new UniDbCommand(
$@"select distinct COMPONENT_NAME from {AppConstants.Schema_Name_Handbook}.CONTROL_USER_VIEW
where APP_NAME_ID = :p_APP_NAME_ID
order by COMPONENT_NAME",
UniDbConnection.Current);
com.Parameters.Add("p_APP_NAME_ID", UniDbType.Int, AppConstants.App_Name_ID);
try
{
new UniDbAdapter(com).Fill(t);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
for (int i = 0; i < t.Rows.Count; ++i)
{
if (!_controlRoles.Contains(t.Rows[i]["Component_Name"].ToString().ToUpper()))
{
_controlRoles.Add(t.Rows[i]["Component_Name"].ToString().ToUpper());
}
}
}
}
/// <summary>
/// Создание структуры таблицы по имени таблицы в БД
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataTable CreateTable(string tableName, string schemaTable = null)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("Ошибка получения схемы. Не задано имя таблицы");
}
UniSchemaTable st = GetTable(tableName);
if (st != null)
{
DataTable table = new DataTable(tableName);
foreach (var item in st.Columns)
{
table.Columns.Add(item.DbColumnName, item.ColumnType);
}
table.DisplayExpression = st.TableComment;
return(table);
}
else
{
DataTable t = new DataTable(tableName);
UniDbAdapter oda = new UniDbAdapter(UniDbCommand.GetSelectCommand(tableName, schemaTable, "1=2"));
oda.Fill(t);
return(t);
}
}
public virtual void CreateSelectCommand()
{
if (string.IsNullOrWhiteSpace(TableName) || string.IsNullOrWhiteSpace(SchemaTable))
{
return;
}
//throw new Exception("Не установлено имя таблицы и схемы для получения данных")
_selectCommand = UniDbCommand.GetSelectCommand(TableName, SchemaTable, $"{TableName}_id=:p_{TableName}_id");
_selectCommand.Parameters.Add("p_" + TableName + "_id", UniDbType.Decimal, null);
}
/// <summary>
/// Загрузка справочника предоставленных ролей
/// </summary>
public static void LoadGrantedRoles()
{
_grantedRoles.Clear();
if (UniDbConnection.Current != null)
{
UniDbDataReader drGrantedRoles = new UniDbCommand(string.Format(
$@"select COLUMN_VALUE as GRANTED_ROLE from TABLE({AppConstants.Schema_Name_Handbook}.GET_GRANTED_ROLES)"), UniDbConnection.Current).ExecuteReader();
while (drGrantedRoles.Read())
{
_grantedRoles.Add(drGrantedRoles["GRANTED_ROLE"].ToString());
}
}
}
public static UniDbCommand GetDeleteCommand(string schemaName, string tableName, IEnumerable <UniSchemaColumn> columns)
{
UniSchemaColumn primary_column = columns.Where(r => r.IsPrimaryKey).FirstOrDefault();
if (primary_column == null)
{
throw new Exception($"Невозможно реализовать команду удаления без первичного ключа. Таблица {tableName}");
}
UniDbCommand cmd = new UniDbCommand();
cmd = new UniDbCommand($"begin {schemaName}.{tableName}_DELETE(p_{primary_column.DbColumnName}=>:p_{primary_column.DbColumnName});end;");
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
cmd.Parameters.Add("p_" + primary_column.DbColumnName, primary_column.DbColumnType, ParameterDirection.Input, primary_column.DbColumnName);
return(cmd);
}
internal Exception ExecuteDeleteCommand(UniDbCommand cmd)
{
IDbTransaction transact = Connection.BeginTransaction();
try
{
cmd.ExecuteNonQuery();
DataRow.Delete();
DataRow.AcceptChanges();
transact.Commit();
return(null);
}
catch (Exception ex)
{
transact.Rollback();
return(ex);
}
}
/// <summary>
/// Метод создания команды выбора данных из таблицы
/// </summary>
/// <param name="tableName">наименование таблицы</param>
/// <param name="schemaName">схема таблицы</param>
/// <param name="filter">фильтр данных таблицы</param>
/// <returns>Возвращает команду выбора данных по условию</returns>
public static UniDbCommand GetSelectCommand(string tableName, string schemaName, string filter)
{
UniDbCommand cmd = null;
switch (ProviderSetting.CurrentDBProvider)
{
case DbProviderType.OracleOdpNetManaged:
case DbProviderType.OracleOdpNetUnmanaged:
case DbProviderType.OracleMicrosoft:
if (string.IsNullOrWhiteSpace(filter))
{
cmd = new UniDbCommand($"select * from {schemaName}.{tableName}");
}
else
{
cmd = new UniDbCommand($"select * from {schemaName}.{tableName} where {filter}");
}
return(cmd);
break;
}
return(cmd);
}
/// <summary>
/// Асинхронно выполняет команду и при ошибках показывает сообщение, иначе если все успешно прошло, выполняет делегат
/// </summary>
/// <param name="sender"></param>
/// <param name="caption"></param>
/// <param name="argument"></param>
/// <param name="execCommand"></param>
/// <param name="workSuccessEnded"></param>
public static void RunAsyncWithWaitDialog(DependencyObject sender, string caption, UniDbCommand execCommand, RunWorkerCompletedEventHandler workSuccessEnded)
{
AbortableBackgroundWorker.RunAsyncWithWaitDialog(sender, caption,
(p, pw) =>
{
execCommand.ExecuteNonQuery();
}, null, execCommand,
(p, pw) =>
{
if (pw.Cancelled)
{
return;
}
else if (pw.Error != null)
{
MessageBox.Show(pw.Error.GetFormattedException(), "Ошибка получения данных");
}
else
{
workSuccessEnded(p, pw);
}
});
}
/// <summary>
/// Асинхронно выполняет загрузку данных и при ошибках показывает сообщение, иначе если все успешно прошло, выполняет делегат
/// </summary>
/// <param name="sender"></param>
/// <param name="caption"></param>
/// <param name="argument"></param>
/// <param name="execCommand"></param>
/// <param name="workSuccessEnded"></param>
public static void RunAsyncWithWaitDialog(DependencyObject sender, string caption, object argument, UniDbCommand execCommand, RunWorkerCompletedEventHandler workSuccessEnded)
{
AbortableBackgroundWorker.RunAsyncWithWaitDialog(sender, caption,
(p, pw) =>
{
UniDbAdapter a = pw.Argument as UniDbAdapter;
DataSet ds = new DataSet();
a.Fill(ds);
pw.Result = ds;
}, argument, execCommand,
(p, pw) =>
{
if (pw.Cancelled)
{
return;
}
else if (pw.Error != null)
{
MessageBox.Show(pw.Error.GetFormattedException(), "Ошибка получения данных");
}
else
{
workSuccessEnded(p, pw);
}
});
}
public UniDbAdapter(UniDbCommand cmd) : this()
{
SelectCommand = cmd;
}