/// <summary>
/// Обновить свойства параметра
/// </summary>
/// <param name="Identifier">Идентификатор параметра</param>
/// <param name="Description">Свойства параметра</param>
public void UpdateParameterDescription(Guid Identifier, DataBaseDescription Description)
{
bool blocked = false;
try
{
if (mutex.WaitOne(1000))
{
blocked = true;
if (state == DataBaseState.Loaded || state == DataBaseState.Saving)
{
dataBase.UpdateParameter(Description, Identifier);
}
else
{
throw new InvalidOperationException("База данных не загружена.");
}
}
else
{
throw new TimeoutException();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (blocked)
{
mutex.ReleaseMutex();
}
}
}
/// <summary>
/// Добавить свойства параметра. Свойство добавляется как есть, то есть поля не актуализируются
/// поэтому создавать свойство лучше на основе имеющегося свойства настроенного!!!
/// </summary>
/// <param name="connection">Соединение с которым работать</param>
/// <param name="transaction">Транзакция через которую осуществлять запись в БД</param>
/// <param name="db_description">Свойство, которое добавить</param>
/// <param name="table">Таблица, в которую добавить свойство</param>
protected void UpdateParameterHistory(SqlConnection connection, SqlTransaction transaction,
DataBaseDescription db_description, string table)
{
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
db_description.ID = GetNextId(table, "id");
db_description.dtCreate = DateTime.Now;
command.Parameters.Add(new SqlParameter("id", db_description.ID));
command.Parameters.Add(new SqlParameter("dtCreate", db_description.dtCreate.ToOADate()));
command.Parameters.Add(new SqlParameter("main_key", db_description.MainKey));
command.Parameters.Add(new SqlParameter("numbe_prm", db_description.NumberParameter));
command.Parameters.Add(new SqlParameter("name_prm", db_description.NameParameter));
command.Parameters.Add(new SqlParameter("type_prm", db_description.TypeParameter));
command.CommandText = string.Format("Insert Into dbo.{0} Values (@id, @dtCreate, @main_key, @numbe_prm, @name_prm, @type_prm, ", table);
if (command.ExecuteNonQuery() != 1)
{
throw new Exception("Не удалось добавить описание параметра в БД.");
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// Получить копию объекта
/// </summary>
/// <returns></returns>
public DataBaseDescription Clone()
{
DataBaseDescription description = new DataBaseDescription();
description.id = id;
description.dt_create = dt_create;
description.main_key = main_key;
description.numbe_prm = numbe_prm;
description.name_prm = name_prm;
description.type_prm = type_prm;
description.val_block_up = val_block_up;
description.val_block_down = val_block_down;
description.val_avar = val_avar;
description.val_max = val_max;
description.val_min = val_min;
description.calibr_1 = calibr_1;
description.calibr_2 = calibr_2;
description.calibr_3 = calibr_3;
description.calibr_4 = calibr_4;
description.calibr_5 = calibr_5;
description.calibr_6 = calibr_6;
description.calibr_7 = calibr_7;
description.calibr_8 = calibr_8;
description.calibr_9 = calibr_9;
description.calibr_10 = calibr_10;
description.snd_avar = snd_avar;
description.snd_max = snd_max;
description.graf_switch = graf_switch;
description.graf_diapz = graf_diapz;
description.graf_min = graf_min;
description.graf_max = graf_max;
description.contr_par = contr_par;
description.res_str = res_str;
description.res_float1 = res_float1;
description.res_float2 = res_float2;
description.res_int1 = res_int1;
description.res_int2 = res_int2;
return(description);
}
/// <summary>
/// Создать таблицу описания параметра
/// </summary>
/// <param name="connection">Соединение с которым работать</param>
/// <param name="transaction">Транзакция через которую осуществлять запись в БД</param>
/// <param name="db_parameter">Параметр, который добавить в БД</param>
protected void CreateParameterHistoryTable(SqlConnection connection, SqlTransaction transaction,
DataBaseParameter db_parameter)
{
try
{
if (db_parameter.Descriptions.Count == 0)
{
DataBaseDescription description = new DataBaseDescription();
description.dtCreate = db_parameter.Created;
description.ID = 0;
description.MainKey = db_parameter.ID;
description.NameParameter = string.Empty;
description.NumberParameter = db_parameter.Numbe_Prm;
description.TypeParameter = string.Empty;
description.NameParameter = "Параметр " + db_parameter.ID.ToString();
db_parameter.Descriptions.Add(description);
}
else
{
foreach (DataBaseDescription desc in db_parameter.Descriptions)
{
desc.NumberParameter = db_parameter.ID;
desc.MainKey = db_parameter.ID;
desc.dtCreate = db_parameter.Created;
}
}
using (SqlCommand tblCommand = connection.CreateCommand())
{
tblCommand.Transaction = transaction;
tblCommand.CommandText = string.Format(sql_query_create_history_table, db_parameter.tblHistory);
tblCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// Добавить параметр в загруженную БД
/// </summary>
/// <param name="Identifier">Идентификатор параметра</param>
/// <param name="Name">Имя параметра</param>
public void InsertParameter(Guid Identifier, String Name)
{
bool blocked = false;
try
{
if (mutex.WaitOne(1000))
{
blocked = true;
if (state == DataBaseState.Loaded || state == DataBaseState.Saving)
{
DataBaseParameter parameter = new DataBaseParameter();
DataBaseDescription description = new DataBaseDescription();
parameter.Identifier = Identifier;
description.NameParameter = Name;
parameter.Descriptions.Add(description);
dataBase.Insert(parameter);
}
else
{
throw new InvalidOperationException("База данных не загружена.");
}
}
else
{
throw new TimeoutException();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (blocked)
{
mutex.ReleaseMutex();
}
}
}
/// <summary>
/// Обновить значение свойств параметра.
/// База данных должна быть загруженна.
/// </summary>
/// <param name="Description">Обновляемое свойство параметра</param>
/// <param name="Identifier">Идентификатор параметра</param>
public void UpdateParameter(DataBaseDescription Description, Guid Identifier)
{
SqlConnection connection = null;
SqlTransaction transaction = null;
try
{
connection = new SqlConnection(adapter.ConnectionString);
connection.Open();
if (connection.State == ConnectionState.Open)
{
if (Peek(Identifier))
{
transaction = connection.BeginTransaction();
using (SqlCommand fParameter = connection.CreateCommand())
{
fParameter.Transaction = transaction;
fParameter.Parameters.Add(new SqlParameter("guid", Identifier.ToString()));
fParameter.Parameters[0].SqlDbType = SqlDbType.VarChar;
fParameter.CommandText = string.Format("Select * From {0} Where guid = @guid", t_main);
using (SqlDataReader reader = fParameter.ExecuteReader())
{
if (reader != null)
{
if (reader.IsClosed == false)
{
string tblHistory = string.Empty;
while (reader.Read())
{
tblHistory = reader.GetString(3);
}
reader.Close();
Description.dtCreate = DateTime.Now;
UpdateParameterHistory(connection, transaction, Description, tblHistory);
transaction.Commit();
}
}
}
}
}
else
throw new Exception("Данного параметра нет в БД.");
}
else
throw new Exception("Не удалось установить соединение с БД.");
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception ex1)
{
throw new Exception(ex1.Message, ex1);
}
throw new Exception(ex.Message, ex);
}
finally
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
if (transaction != null)
transaction.Dispose();
}
}
/// <summary>
/// Загрузить описание параметра из БД
/// </summary>
/// <param name="db_parameter">Параметр для которого загрузить описание</param>
private void LoadParameterDescription(DataBaseParameter db_parameter)
{
SqlConnection connection = null;
try
{
string sql_query = string.Format("SELECT * FROM {0}", db_parameter.tblHistory);
connection = new SqlConnection(adapter.ConnectionString);
connection.Open();
if (connection.State == ConnectionState.Open)
{
using (SqlCommand command = new SqlCommand(sql_query, connection))
{
using (SqlDataReader result = command.ExecuteReader())
{
if (result != null)
{
if (result.IsClosed == false)
{
while (result.Read())
{
DataBaseDescription db_description = new DataBaseDescription();
db_description.ID = result.GetInt32(0);
db_description.dtCreate = DateTime.FromOADate(result.GetDouble(1));
db_description.MainKey = result.GetInt32(2);
db_description.NumberParameter = result.GetInt32(3);
db_description.NameParameter = result.GetString(4);
db_description.TypeParameter = result.GetString(5);
db_parameter.Descriptions.Add(db_description);
}
}
}
else
throw new Exception("Не удалось считать данные описания параметра");
}
}
}
else
throw new Exception("Не удалось подключиться к серверу БД.");
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
}
}
/// <summary>
/// Добавить свойства параметра. Свойство добавляется как есть, то есть поля не актуализируются
/// поэтому создавать свойство лучше на основе имеющегося свойства настроенного!!!
/// </summary>
/// <param name="connection">Соединение с которым работать</param>
/// <param name="transaction">Транзакция через которую осуществлять запись в БД</param>
/// <param name="db_description">Свойство, которое добавить</param>
/// <param name="table">Таблица, в которую добавить свойство</param>
protected void UpdateParameterHistory(SqlConnection connection, SqlTransaction transaction,
DataBaseDescription db_description, string table)
{
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
db_description.ID = GetNextId(table, "id");
db_description.dtCreate = DateTime.Now;
command.Parameters.Add(new SqlParameter("id", db_description.ID));
command.Parameters.Add(new SqlParameter("dtCreate", db_description.dtCreate.ToOADate()));
command.Parameters.Add(new SqlParameter("main_key", db_description.MainKey));
command.Parameters.Add(new SqlParameter("numbe_prm", db_description.NumberParameter));
command.Parameters.Add(new SqlParameter("name_prm", db_description.NameParameter));
command.Parameters.Add(new SqlParameter("type_prm", db_description.TypeParameter));
command.CommandText = string.Format("Insert Into dbo.{0} Values (@id, @dtCreate, @main_key, @numbe_prm, @name_prm, @type_prm, ", table);
if (command.ExecuteNonQuery() != 1)
{
throw new Exception("Не удалось добавить описание параметра в БД.");
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// Получить копию объекта
/// </summary>
/// <returns></returns>
public DataBaseDescription Clone()
{
DataBaseDescription description = new DataBaseDescription();
description.id = id;
description.dt_create = dt_create;
description.main_key = main_key;
description.numbe_prm = numbe_prm;
description.name_prm = name_prm;
description.type_prm = type_prm;
description.val_block_up = val_block_up;
description.val_block_down = val_block_down;
description.val_avar = val_avar;
description.val_max = val_max;
description.val_min = val_min;
description.calibr_1 = calibr_1;
description.calibr_2 = calibr_2;
description.calibr_3 = calibr_3;
description.calibr_4 = calibr_4;
description.calibr_5 = calibr_5;
description.calibr_6 = calibr_6;
description.calibr_7 = calibr_7;
description.calibr_8 = calibr_8;
description.calibr_9 = calibr_9;
description.calibr_10 = calibr_10;
description.snd_avar = snd_avar;
description.snd_max = snd_max;
description.graf_switch = graf_switch;
description.graf_diapz = graf_diapz;
description.graf_min = graf_min;
description.graf_max = graf_max;
description.contr_par = contr_par;
description.res_str = res_str;
description.res_float1 = res_float1;
description.res_float2 = res_float2;
description.res_int1 = res_int1;
description.res_int2 = res_int2;
return description;
}
/// <summary>
/// Обновить свойства параметра
/// </summary>
/// <param name="Identifier">Идентификатор параметра</param>
/// <param name="Description">Свойства параметра</param>
public void UpdateParameterDescription(Guid Identifier, DataBaseDescription Description)
{
bool blocked = false;
try
{
if (mutex.WaitOne(1000))
{
blocked = true;
if (state == DataBaseState.Loaded || state == DataBaseState.Saving)
{
dataBase.UpdateParameter(Description, Identifier);
}
else
throw new InvalidOperationException("База данных не загружена.");
}
else
throw new TimeoutException();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (blocked) mutex.ReleaseMutex();
}
}
/// <summary>
/// Загрузить описание параметра из БД
/// </summary>
/// <param name="db_parameter">Параметр для которого загрузить описание</param>
private void ParameterDescriptionLoad(DataBaseParameter db_parameter)
{
SqlConnection connection = null;
try
{
string sql_query = string.Format("SELECT * FROM {0}", db_parameter.tblHistory);
connection = new SqlConnection(adapter.ConnectionString);
connection.Open();
if (connection.State == ConnectionState.Open)
{
SqlCommand command = new SqlCommand(sql_query, connection);
SqlDataReader result = command.ExecuteReader();
if (result != null)
{
if (result.IsClosed == false)
{
while (result.Read())
{
DataBaseDescription db_description = new DataBaseDescription();
db_description.ID = result.GetInt32(structure.HistoryTable["id"].IndexInTable);
db_description.dtCreate = DateTime.FromOADate(result.GetDouble(structure.HistoryTable["dtCreate"].IndexInTable));
db_description.MainKey = result.GetInt32(structure.HistoryTable["main_key"].IndexInTable);
db_description.NumberParameter = result.GetInt32(structure.HistoryTable["numbe_prm"].IndexInTable);
db_description.NameParameter = result.GetString(structure.HistoryTable["name_prm"].IndexInTable);
db_description.TypeParameter = result.GetString(structure.HistoryTable["type_prm"].IndexInTable);
db_description.Val_block_up = result.GetFloat(structure.HistoryTable["val_block_up"].IndexInTable);
db_description.Val_block_down = result.GetFloat(structure.HistoryTable["val_block_down"].IndexInTable);
db_description.Val_avar = result.GetFloat(structure.HistoryTable["val_avar"].IndexInTable);
db_description.Val_max = result.GetFloat(structure.HistoryTable["val_max"].IndexInTable);
db_description.Val_min = result.GetFloat(structure.HistoryTable["val_min"].IndexInTable);
db_description.Calibr_1 = result.GetFloat(structure.HistoryTable["calibr_1"].IndexInTable);
db_description.Calibr_2 = result.GetFloat(structure.HistoryTable["calibr_2"].IndexInTable);
db_description.Calibr_3 = result.GetFloat(structure.HistoryTable["calibr_3"].IndexInTable);
db_description.Calibr_4 = result.GetFloat(structure.HistoryTable["calibr_4"].IndexInTable);
db_description.Calibr_5 = result.GetFloat(structure.HistoryTable["calibr_5"].IndexInTable);
db_description.Calibr_6 = result.GetFloat(structure.HistoryTable["calibr_6"].IndexInTable);
db_description.Calibr_7 = result.GetFloat(structure.HistoryTable["calibr_7"].IndexInTable);
db_description.Calibr_8 = result.GetFloat(structure.HistoryTable["calibr_8"].IndexInTable);
db_description.Calibr_9 = result.GetFloat(structure.HistoryTable["calibr_9"].IndexInTable);
db_description.Calibr_10 = result.GetFloat(structure.HistoryTable["calibr_10"].IndexInTable);
db_description.Snd_avar = result.GetString(structure.HistoryTable["snd_avar"].IndexInTable);
db_description.Snd_max = result.GetString(structure.HistoryTable["snd_max"].IndexInTable);
db_description.Graf_switch = result.GetInt32(structure.HistoryTable["graf_switch"].IndexInTable);
db_description.Graf_diapz = result.GetFloat(structure.HistoryTable["graf_diapz"].IndexInTable);
db_description.Graf_min = result.GetFloat(structure.HistoryTable["graf_min"].IndexInTable);
db_description.Graf_max = result.GetFloat(structure.HistoryTable["graf_max"].IndexInTable);
db_description.Contr_par = result.GetInt32(structure.HistoryTable["contr_par"].IndexInTable);
db_description.Res_str = result.GetString(structure.HistoryTable["res_str"].IndexInTable);
db_description.Res_float1 = result.GetFloat(structure.HistoryTable["res_float1"].IndexInTable);
db_description.Res_float2 = result.GetFloat(structure.HistoryTable["res_float2"].IndexInTable);
db_description.Res_int1 = result.GetInt32(structure.HistoryTable["res_int1"].IndexInTable);
db_description.Res_int2 = result.GetInt32(structure.HistoryTable["res_int2"].IndexInTable);
db_parameter.Descriptions.Add(db_description);
}
}
}
else
throw new Exception("Не удалось считать данные описания параметра");
}
else
throw new Exception("Не удалось подключиться к серверу БД.");
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
}
}
/// <summary>
/// Добавить свойства параметра. Свойство добавляется как есть, то есть поля не актуализируются
/// поэтому создавать свойство лучше на основе имеющегося свойства настроенного!!!
/// </summary>
/// <param name="connection">Соединение с которым работать</param>
/// <param name="transaction">Транзакция через которую осуществлять запись в БД</param>
/// <param name="db_description">Свойство, которое добавить</param>
/// <param name="table">Таблица, в которую добавить свойство</param>
protected void UpdateParameterHistory(SqlConnection connection, SqlTransaction transaction,
DataBaseDescription db_description, string table)
{
try
{
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
db_description.ID = GetNextId(table, "id");
db_description.dtCreate = DateTime.Now;
command.Parameters.Add(structure.HistoryTable["id"].Parameter);
command.Parameters[0].Value = db_description.ID;
command.Parameters.Add(structure.HistoryTable["dtCreate"].Parameter);
command.Parameters[1].Value = db_description.dtCreate.ToOADate();
command.Parameters.Add(structure.HistoryTable["main_key"].Parameter);
command.Parameters[2].Value = db_description.MainKey;
command.Parameters.Add(structure.HistoryTable["numbe_prm"].Parameter);
command.Parameters[3].Value = db_description.NumberParameter;
command.Parameters.Add(structure.HistoryTable["name_prm"].Parameter);
command.Parameters[4].Value = db_description.NameParameter;
command.Parameters.Add(structure.HistoryTable["type_prm"].Parameter);
command.Parameters[5].Value = db_description.TypeParameter;
command.Parameters.Add(structure.HistoryTable["val_block_up"].Parameter);
command.Parameters[6].Value = db_description.Val_block_up;
command.Parameters.Add(structure.HistoryTable["val_block_down"].Parameter);
command.Parameters[7].Value = db_description.Val_block_down;
command.Parameters.Add(structure.HistoryTable["val_avar"].Parameter);
command.Parameters[8].Value = db_description.Val_avar;
command.Parameters.Add(structure.HistoryTable["val_max"].Parameter);
command.Parameters[9].Value = db_description.Val_max;
command.Parameters.Add(structure.HistoryTable["val_min"].Parameter);
command.Parameters[10].Value = db_description.Val_min;
command.Parameters.Add(structure.HistoryTable["calibr_1"].Parameter);
command.Parameters[11].Value = db_description.Calibr_1;
command.Parameters.Add(structure.HistoryTable["calibr_2"].Parameter);
command.Parameters[12].Value = db_description.Calibr_2;
command.Parameters.Add(structure.HistoryTable["calibr_3"].Parameter);
command.Parameters[13].Value = db_description.Calibr_3;
command.Parameters.Add(structure.HistoryTable["calibr_4"].Parameter);
command.Parameters[14].Value = db_description.Calibr_4;
command.Parameters.Add(structure.HistoryTable["calibr_5"].Parameter);
command.Parameters[15].Value = db_description.Calibr_5;
command.Parameters.Add(structure.HistoryTable["calibr_6"].Parameter);
command.Parameters[16].Value = db_description.Calibr_6;
command.Parameters.Add(structure.HistoryTable["calibr_7"].Parameter);
command.Parameters[17].Value = db_description.Calibr_7;
command.Parameters.Add(structure.HistoryTable["calibr_8"].Parameter);
command.Parameters[18].Value = db_description.Calibr_8;
command.Parameters.Add(structure.HistoryTable["calibr_9"].Parameter);
command.Parameters[19].Value = db_description.Calibr_9;
command.Parameters.Add(structure.HistoryTable["calibr_10"].Parameter);
command.Parameters[20].Value = db_description.Calibr_10;
command.Parameters.Add(structure.HistoryTable["snd_avar"].Parameter);
command.Parameters[21].Value = db_description.Snd_avar;
command.Parameters.Add(structure.HistoryTable["snd_max"].Parameter);
command.Parameters[22].Value = db_description.Snd_max;
command.Parameters.Add(structure.HistoryTable["graf_switch"].Parameter);
command.Parameters[23].Value = db_description.Graf_switch;
command.Parameters.Add(structure.HistoryTable["graf_diapz"].Parameter);
command.Parameters[24].Value = db_description.Graf_diapz;
command.Parameters.Add(structure.HistoryTable["graf_min"].Parameter);
command.Parameters[25].Value = db_description.Graf_min;
command.Parameters.Add(structure.HistoryTable["graf_max"].Parameter);
command.Parameters[26].Value = db_description.Graf_max;
command.Parameters.Add(structure.HistoryTable["contr_par"].Parameter);
command.Parameters[27].Value = db_description.Contr_par;
command.Parameters.Add(structure.HistoryTable["res_str"].Parameter);
command.Parameters[28].Value = db_description.Res_str;
command.Parameters.Add(structure.HistoryTable["res_float1"].Parameter);
command.Parameters[29].Value = db_description.Res_float1;
command.Parameters.Add(structure.HistoryTable["res_float2"].Parameter);
command.Parameters[30].Value = db_description.Res_float2;
command.Parameters.Add(structure.HistoryTable["res_int1"].Parameter);
command.Parameters[31].Value = db_description.Res_int1;
command.Parameters.Add(structure.HistoryTable["res_int2"].Parameter);
command.Parameters[32].Value = db_description.Res_int2;
command.CommandText = string.Format("Insert Into dbo.{0} Values (@id, @dtCreate, @main_key, @numbe_prm, @name_prm, " +
"@type_prm, @val_block_up, @val_block_down, @val_avar, @val_max, @val_min, @calibr_1, @calibr_2, @calibr_3, @calibr_4, " +
"@calibr_5, @calibr_6, @calibr_7, @calibr_8, @calibr_9, @calibr_10, @snd_avar, @snd_max, @graf_switch, @graf_diapz, " +
"@graf_min, @graf_max, @contr_par, @res_str, @res_float1, @res_float2, @res_int1, @res_int2)", table);
if (command.ExecuteNonQuery() != 1)
{
throw new Exception("Не удалось добавить описание параметра в БД.");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// Обновить значение свойств параметра.
/// База данных должна быть загруженна.
/// </summary>
/// <param name="Description">Обновляемое свойство параметра</param>
/// <param name="Identifier">Идентификатор параметра</param>
public void UpdateParameter(DataBaseDescription Description, Guid Identifier)
{
SqlConnection connection = null;
SqlTransaction transaction = null;
try
{
connection = new SqlConnection(adapter.ConnectionString);
connection.Open();
if (connection.State == ConnectionState.Open)
{
if (Peek(Identifier))
{
transaction = connection.BeginTransaction();
using (SqlCommand fParameter = connection.CreateCommand())
{
fParameter.Transaction = transaction;
fParameter.Parameters.Add(new SqlParameter("guid", Identifier.ToString()));
fParameter.Parameters[0].SqlDbType = SqlDbType.VarChar;
fParameter.CommandText = string.Format("Select * From {0} Where guid = @guid", t_main);
using (SqlDataReader reader = fParameter.ExecuteReader())
{
if (reader != null)
{
if (reader.IsClosed == false)
{
string tblHistory = string.Empty;
while (reader.Read())
{
tblHistory = reader.GetString(3);
}
reader.Close();
Description.dtCreate = DateTime.Now;
UpdateParameterHistory(connection, transaction, Description, tblHistory);
transaction.Commit();
}
}
}
}
}
else
{
throw new Exception("Данного параметра нет в БД.");
}
}
else
{
throw new Exception("Не удалось установить соединение с БД.");
}
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception ex1)
{
throw new Exception(ex1.Message, ex1);
}
throw new Exception(ex.Message, ex);
}
finally
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
}
}
}
/// <summary>
/// Загрузить описание параметра из БД
/// </summary>
/// <param name="db_parameter">Параметр для которого загрузить описание</param>
private void LoadParameterDescription(DataBaseParameter db_parameter)
{
SqlConnection connection = null;
try
{
string sql_query = string.Format("SELECT * FROM {0}", db_parameter.tblHistory);
connection = new SqlConnection(adapter.ConnectionString);
connection.Open();
if (connection.State == ConnectionState.Open)
{
using (SqlCommand command = new SqlCommand(sql_query, connection))
{
using (SqlDataReader result = command.ExecuteReader())
{
if (result != null)
{
if (result.IsClosed == false)
{
while (result.Read())
{
DataBaseDescription db_description = new DataBaseDescription();
db_description.ID = result.GetInt32(0);
db_description.dtCreate = DateTime.FromOADate(result.GetDouble(1));
db_description.MainKey = result.GetInt32(2);
db_description.NumberParameter = result.GetInt32(3);
db_description.NameParameter = result.GetString(4);
db_description.TypeParameter = result.GetString(5);
db_parameter.Descriptions.Add(db_description);
}
}
}
else
{
throw new Exception("Не удалось считать данные описания параметра");
}
}
}
}
else
{
throw new Exception("Не удалось подключиться к серверу БД.");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
}
}
/// <summary>
/// Создать таблицу описания параметра
/// </summary>
/// <param name="connection">Соединение с которым работать</param>
/// <param name="transaction">Транзакция через которую осуществлять запись в БД</param>
/// <param name="db_parameter">Параметр, который добавить в БД</param>
protected void CreateParameterHistoryTable(SqlConnection connection, SqlTransaction transaction,
DataBaseParameter db_parameter)
{
try
{
if (db_parameter.Descriptions.Count == 0)
{
DataBaseDescription description = new DataBaseDescription();
description.dtCreate = db_parameter.Created;
description.ID = 0;
description.MainKey = db_parameter.ID;
description.NameParameter = string.Empty;
description.NumberParameter = db_parameter.Numbe_Prm;
description.TypeParameter = string.Empty;
description.NameParameter = "Параметр " + db_parameter.ID.ToString();
db_parameter.Descriptions.Add(description);
}
else
foreach (DataBaseDescription desc in db_parameter.Descriptions)
{
desc.NumberParameter = db_parameter.ID;
desc.MainKey = db_parameter.ID;
desc.dtCreate = db_parameter.Created;
}
using (SqlCommand tblCommand = connection.CreateCommand())
{
tblCommand.Transaction = transaction;
tblCommand.CommandText = string.Format(sql_query_create_history_table, db_parameter.tblHistory);
tblCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// Создать таблицу описания параметра
/// </summary>
/// <param name="connection">Соединение с которым работать</param>
/// <param name="transaction">Транзакция через которую осуществлять запись в БД</param>
/// <param name="db_parameter">Параметр, который добавить в БД</param>
protected void CreateParameterHistoryTable(SqlConnection connection, SqlTransaction transaction,
DataBaseParameter db_parameter)
{
try
{
if (db_parameter.Descriptions.Count == 0)
{
DataBaseDescription description = new DataBaseDescription();
description.dtCreate = db_parameter.Created;
description.ID = 0;
description.MainKey = db_parameter.ID;
description.NameParameter = string.Empty;
description.NumberParameter = db_parameter.Numbe_Prm;
description.TypeParameter = string.Empty;
description.NameParameter = "Параметр " + db_parameter.ID.ToString();
db_parameter.Descriptions.Add(description);
}
else
foreach (DataBaseDescription desc in db_parameter.Descriptions)
{
desc.NumberParameter = db_parameter.ID;
desc.MainKey = db_parameter.ID;
desc.dtCreate = db_parameter.Created;
}
SqlCommand tblCommand = connection.CreateCommand();
tblCommand.Transaction = transaction;
tblCommand.CommandText = string.Format(structure.HistoryTable.SqlQueryForCreateTable, db_parameter.tblHistory);
tblCommand.ExecuteNonQuery();
foreach (DataBaseDescription desc in db_parameter.Descriptions)
{
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
command.Parameters.Add(structure.HistoryTable["id"].Parameter);
command.Parameters[0].Value = desc.ID;
command.Parameters.Add(structure.HistoryTable["dtCreate"].Parameter);
command.Parameters[1].Value = desc.dtCreate.ToOADate();
command.Parameters.Add(structure.HistoryTable["main_key"].Parameter);
command.Parameters[2].Value = desc.MainKey;
command.Parameters.Add(new SqlParameter("numbe_prm", desc.NumberParameter));
command.Parameters[3].SqlDbType = SqlDbType.Int;
command.Parameters.Add(structure.HistoryTable["name_prm"].Parameter);
command.Parameters[4].Value = desc.NameParameter;
command.Parameters.Add(structure.HistoryTable["type_prm"].Parameter);
command.Parameters[5].Value = desc.TypeParameter;
command.Parameters.Add(structure.HistoryTable["val_block_up"].Parameter);
command.Parameters[6].Value = desc.Val_block_up;
command.Parameters.Add(structure.HistoryTable["val_block_down"].Parameter);
command.Parameters[7].Value = desc.Val_block_down;
command.Parameters.Add(structure.HistoryTable["val_avar"].Parameter);
command.Parameters[8].Value = desc.Val_avar;
command.Parameters.Add(structure.HistoryTable["val_max"].Parameter);
command.Parameters[9].Value = desc.Val_max;
command.Parameters.Add(structure.HistoryTable["val_min"].Parameter);
command.Parameters[10].Value = desc.Val_min;
command.Parameters.Add(structure.HistoryTable["calibr_1"].Parameter);
command.Parameters[11].Value = desc.Calibr_1;
command.Parameters.Add(structure.HistoryTable["calibr_2"].Parameter);
command.Parameters[12].Value = desc.Calibr_2;
command.Parameters.Add(structure.HistoryTable["calibr_3"].Parameter);
command.Parameters[13].Value = desc.Calibr_3;
command.Parameters.Add(structure.HistoryTable["calibr_4"].Parameter);
command.Parameters[14].Value = desc.Calibr_4;
command.Parameters.Add(structure.HistoryTable["calibr_5"].Parameter);
command.Parameters[15].Value = desc.Calibr_5;
command.Parameters.Add(structure.HistoryTable["calibr_6"].Parameter);
command.Parameters[16].Value = desc.Calibr_6;
command.Parameters.Add(structure.HistoryTable["calibr_7"].Parameter);
command.Parameters[17].Value = desc.Calibr_7;
command.Parameters.Add(structure.HistoryTable["calibr_8"].Parameter);
command.Parameters[18].Value = desc.Calibr_8;
command.Parameters.Add(structure.HistoryTable["calibr_9"].Parameter);
command.Parameters[19].Value = desc.Calibr_9;
command.Parameters.Add(structure.HistoryTable["calibr_10"].Parameter);
command.Parameters[20].Value = desc.Calibr_10;
command.Parameters.Add(structure.HistoryTable["snd_avar"].Parameter);
command.Parameters[21].Value = desc.Snd_avar;
command.Parameters.Add(structure.HistoryTable["snd_max"].Parameter);
command.Parameters[22].Value = desc.Snd_max;
command.Parameters.Add(structure.HistoryTable["graf_switch"].Parameter);
command.Parameters[23].Value = desc.Graf_switch;
command.Parameters.Add(structure.HistoryTable["graf_diapz"].Parameter);
command.Parameters[24].Value = desc.Graf_diapz;
command.Parameters.Add(structure.HistoryTable["graf_min"].Parameter);
command.Parameters[25].Value = desc.Graf_min;
command.Parameters.Add(structure.HistoryTable["graf_max"].Parameter);
command.Parameters[26].Value = desc.Graf_max;
command.Parameters.Add(structure.HistoryTable["contr_par"].Parameter);
command.Parameters[27].Value = desc.Contr_par;
command.Parameters.Add(structure.HistoryTable["res_str"].Parameter);
command.Parameters[28].Value = desc.Res_str;
command.Parameters.Add(structure.HistoryTable["res_float1"].Parameter);
command.Parameters[29].Value = desc.Res_float1;
command.Parameters.Add(structure.HistoryTable["res_float2"].Parameter);
command.Parameters[30].Value = desc.Res_float2;
command.Parameters.Add(structure.HistoryTable["res_int1"].Parameter);
command.Parameters[31].Value = desc.Res_int1;
command.Parameters.Add(structure.HistoryTable["res_int2"].Parameter);
command.Parameters[32].Value = desc.Res_int2;
command.CommandText = string.Format("Insert Into dbo.{0} Values (@id, @dtCreate, @main_key, @numbe_prm, @name_prm, " +
"@type_prm, @val_block_up, @val_block_down, @val_avar, @val_max, @val_min, @calibr_1, @calibr_2, @calibr_3, @calibr_4, " +
"@calibr_5, @calibr_6, @calibr_7, @calibr_8, @calibr_9, @calibr_10, @snd_avar, @snd_max, @graf_switch, @graf_diapz, " +
"@graf_min, @graf_max, @contr_par, @res_str, @res_float1, @res_float2, @res_int1, @res_int2)", db_parameter.tblHistory);
if (command.ExecuteNonQuery() != 1)
{
throw new Exception("Не удалось добавить описание параметра в БД.");
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// Добавить параметр в загруженную БД
/// </summary>
/// <param name="Identifier">Идентификатор параметра</param>
/// <param name="Name">Имя параметра</param>
public void InsertParameter(Guid Identifier, String Name)
{
bool blocked = false;
try
{
if (mutex.WaitOne(1000))
{
blocked = true;
if (state == DataBaseState.Loaded || state == DataBaseState.Saving)
{
DataBaseParameter parameter = new DataBaseParameter();
DataBaseDescription description = new DataBaseDescription();
parameter.Identifier = Identifier;
description.NameParameter = Name;
parameter.Descriptions.Add(description);
dataBase.Insert(parameter);
}
else
throw new InvalidOperationException("База данных не загружена.");
}
else
throw new TimeoutException();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (blocked) mutex.ReleaseMutex();
}
}
