public int SetMStorage(string SID, MStorageItem item, DbActionType type)
{
using (new DebugLog())
{
return(sv.SetMStorage(SID, item, type));
}
}
public int SetMUser(string SID, MUserItem item, DbActionType type)
{
using (new DebugLog())
{
return(sv.SetMUser(SID, item, type));
}
}
private SqlCommand CommandBuilder(DbActionType type)
{
SqlCommand command = new SqlCommand(type.ToString(), GetSqlConnection())
{
CommandType = CommandType.StoredProcedure
};
if (Entity == null && (type == DbActionType.EditarProductos || type == DbActionType.InsertarProductos || type == DbActionType.EliminarProducto))
{
throw new ArgumentNullException();
}
if (type == DbActionType.EditarProductos || type == DbActionType.InsertarProductos)
{
command.Parameters.AddWithValue("@nombre", Entity.Name);
command.Parameters.AddWithValue("@descrip", Entity.Descripcion);
command.Parameters.AddWithValue("@marca", Entity.Marca);
command.Parameters.AddWithValue("@precio", Entity.Precio);
command.Parameters.AddWithValue("@stock", Entity.Stock);
}
if (type == DbActionType.EditarProductos || type == DbActionType.EliminarProducto)
{
command.Parameters.AddWithValue("@stock", Entity.Id);
}
return(command);
}
/// <summary>
/// Appends Account-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of Budget class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, Budget budget, DbActionType action)
{
command.AddCommonParameters(budget.Id, action);
command.Parameters.Add("@id_category", SqlDbType.Int).Value = budget.CategoryId;
command.Parameters.Add("@amount", SqlDbType.Money).Value = budget.Amount;
command.Parameters.Add("@budget_date", SqlDbType.DateTime).Value = budget.BudgetDate.ToDbValue();
command.Parameters.Add("@id_company", SqlDbType.Int).Value = budget.CompanyId.ToDbValue();
command.Parameters.Add("@period_type", SqlDbType.SmallInt).Value = budget.PeriodType.ToDbValue();
}
/// <summary>
///
/// </summary>
/// <param name = "actionType"></param>
/// <param name="ontology"></param>
/// <param name="isDumb"></param>
/// <param name="client"></param>
/// <param name="commandId"></param>
/// <param name="localEntityId"></param>
/// <param name="infoValue"></param>
internal DbCmd(DbActionType actionType,
OntologyDescriptor ontology, bool isDumb, NodeDescriptor client,
string commandId, string localEntityId, InfoItem[] infoValue)
{
this.ActionType = actionType;
this.Ontology = ontology;
this.IsDumb = isDumb;
this.Client = client;
this.CommandId = commandId;
this.LocalEntityId = localEntityId;
this.InfoValue = infoValue;
}
/// <summary>
///
/// </summary>
/// <param name = "actionType"></param>
/// <param name="ontology"></param>
/// <param name="isDumb"></param>
/// <param name="client"></param>
/// <param name="commandId"></param>
/// <param name="localEntityId"></param>
/// <param name="infoValue"></param>
internal DbCmd(DbActionType actionType,
OntologyDescriptor ontology, bool isDumb, NodeDescriptor client,
string commandId, string localEntityId, InfoItem[] infoValue)
{
this.ActionType = actionType;
this.Ontology = ontology;
this.IsDumb = isDumb;
this.Client = client;
this.CommandId = commandId;
this.LocalEntityId = localEntityId;
this.InfoValue = infoValue;
}
public int ExecuteCommand(Entity entity, DbActionType type)
{
Entity = entity;
try
{
return(CommandBuilder(type).ExecuteNonQuery());
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Appends UserProfile-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of UserProfile class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, UserProfile userProfile, DbActionType action)
{
command.AddCommonParameters(userProfile.Id, action);
command.Parameters.Add("@email", SqlDbType.NVarChar).Value = userProfile.Name.ToDbValue();
command.Parameters.Add("@password", SqlDbType.NVarChar).Value = userProfile.Password.ToDbValue();
command.Parameters.Add("@password_question", SqlDbType.NVarChar).Value = userProfile.PasswordQuestion.ToDbValue();
command.Parameters.Add("@password_answer", SqlDbType.NVarChar).Value = userProfile.PasswordAnswer.ToDbValue();
command.Parameters.Add("@first_name", SqlDbType.NVarChar).Value = userProfile.FirstName.ToDbValue();
command.Parameters.Add("@last_name", SqlDbType.NVarChar).Value = userProfile.LastName.ToDbValue();
command.Parameters.Add("@comment", SqlDbType.NVarChar).Value = userProfile.Comment.ToDbValue();
command.Parameters.Add("@is_approved", SqlDbType.Bit).Value = userProfile.IsApproved;
command.Parameters.Add("@current_time_utc", SqlDbType.DateTime).Value = DateTime.UtcNow;
}
/// <summary>
/// Appends Transaction-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of Transaction class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, Transaction transaction, DbActionType action)
{
command.AddCommonParameters(transaction.Id, action);
command.Parameters.Add("@date", SqlDbType.DateTime).Value = transaction.Date;
command.Parameters.Add("@amount", SqlDbType.Decimal).Value = transaction.Amount;
command.Parameters.Add("@merchant", SqlDbType.NVarChar).Value = transaction.Merchant.ToDbValue();
command.Parameters.Add("@id_account", SqlDbType.Int).Value = transaction.AccountId.ToDbValue();
command.Parameters.Add("@id_category", SqlDbType.Int).Value = transaction.CategoryId.ToDbValue();
command.Parameters.Add("@description", SqlDbType.NVarChar).Value = transaction.Description.ToDbValue();
command.Parameters.Add("@notes", SqlDbType.NVarChar).Value = transaction.Notes.ToDbValue();
command.Parameters.Add("@type", SqlDbType.Int).Value = transaction.TransactionType;
command.Parameters.Add("@direction", SqlDbType.Int).Value = transaction.Direction;
command.Parameters.Add("@include_in_tax", SqlDbType.Bit).Value = transaction.IncludeInTax;
command.Parameters.Add("@tags", SqlDbType.NVarChar).Value = string.Join(Transaction.TAG_SEPARATOR, transaction.TagIds);
}
private void AddAudit(string message, string objectType = "", int? objectKey = null, int recordCount = 0, DbActionType action = DbActionType.NullAction)
{
Audit audit = new Audit();
audit.DateStamp = DateTime.Now;
audit.Message = message;
audit.ObjectKey = objectKey;
audit.ObjectType = objectType;
audit.UserName = UserName;
audit.RecordCount = recordCount;
audit.Action = action.ToString();
Debug.WriteLine("[AUDIT] " + message);
DB.Audits.Add(audit);
}
private int InsertUpdate(Tag entity, int? userId, DbActionType action, bool intoCache)
{
var cache = new CacheWrapper();
var res = TagsDataAdapter.InsertUpdate(entity, userId, action);
if (res == 0)
{
//if ok - update cache
if (intoCache)
{
if (action == DbActionType.Insert)
cache.AddToList(CacheKey, entity, userId);
if (action == DbActionType.Update)
cache.UpdateList(CacheKey, entity, userId);
}
}
return res;
}
public static void PlanToWriteToDb <T>(T item, DbActionType type)
{
string sql = null;
switch (type)
{
case DbActionType.Insert:
sql = BuildInsertSql(item);
break;
case DbActionType.Delete:
sql = BuildDeleteSql(item);
break;
}
if (sql != null)
{
SqlQueue.Enqueue(sql);
}
}
public static int BatchInsert(List<Transaction> list, int? companyId, DbActionType action)
{
var res = -1;
var commName = action == DbActionType.Insert ? "transaction_create" : "web.transaction_update";
using (var scope = new System.Transactions.TransactionScope())
{
using (var holder = SqlConnectionHelper.GetConnection())
{
var cmd = holder.Connection.CreateSPCommand(commName);
//SqlTransaction transaction = holder.Connection.BeginTransaction("BatchInsertTransactions");
//cmd.Transaction = transaction;
try
{
foreach (var entity in list)
{
cmd.Parameters.Clear();
cmd.AddEntityParameters(entity, action);
if (action == DbActionType.Update)
cmd.Parameters.AddWithValue("@id_company", companyId);
cmd.ExecuteNonQuery();
if (action == DbActionType.Insert)
entity.Id = cmd.GetRowIdParameter();
int l_res = cmd.GetReturnParameter();
if (l_res != 0)
return l_res;
}
//transaction.Commit();
scope.Complete();
res = 0;
}
catch (SqlException e)
{
//transaction.Rollback();
cmd.AddDetailsToException(e);
throw;
}
}
}
return res;
}
public static int InsertUpdate(Tag entity, int? userId, DbActionType action)
{
var res = -1;
using (var holder = SqlConnectionHelper.GetConnection())
{
var commName = action == DbActionType.Insert ? "tag_create" : "web.tag_update";
var cmd = holder.Connection.CreateSPCommand(commName);
try
{
cmd.AddEntityParameters(entity, action);
cmd.ExecuteNonQuery();
if (action == DbActionType.Insert)
entity.Id = cmd.GetRowIdParameter();
res = cmd.GetReturnParameter();
}
catch (SqlException e)
{
cmd.AddDetailsToException(e);
throw;
}
}
return res;
}
/// <summary>
///
/// </summary>
public Sql(OntologyDescriptor ontology, string clientId, string commandId, DbActionType actionType, string localEntityId, InfoItem[] infoValue, Func <DbParameter> createParameter)
{
this._ontology = ontology;
this._localEntityId = localEntityId;
this._infoValue = infoValue;
this._clientId = clientId;
this._commandId = commandId;
this._createParameter = createParameter;
if (ontology == null ||
string.IsNullOrEmpty(localEntityId))
{
this.IsValid = false;
this.Description = "命令信息标识或信息值为空或本体为空或本地标识为空";
throw new ArgumentNullException("command");
}
// 无需把switch流程选择逻辑重构掉，因为actionType枚举不存在变化
switch (actionType)
{
case DbActionType.Insert:
if (infoValue == null || infoValue.Length == 0)
{
this.Description = "命令信息值为空";
break;
}
BuildInsertSql();
break;
case DbActionType.Update:
if (infoValue == null || infoValue.Length == 0)
{
this.Description = "命令信息值为空";
break;
}
BuildUpdateSql();
break;
case DbActionType.Delete:
BuildDeleteSql();
break;
default:
this.IsValid = false;
this.Description = "意外的不能执行的动作码" + actionType.ToString();
break;
}
}
private static int InsertUpdate(Account entity, int? companyId, DbActionType action, bool intoCache)
{
var cache = new CacheWrapper();
var res = AccountsDataAdapter.InsertUpdate(entity, companyId, action);
if (res == 0)
{
//if ok - update cache
if (intoCache)
{
if (action == DbActionType.Insert)
cache.AddToList(CacheKey, entity, companyId);
if (action == DbActionType.Update)
cache.UpdateList(CacheKey, entity, companyId);
}
}
return res;
}
public static void SetMGroup(MGroupItem item, DbActionType type)
{
LCL.SetMGroup(item, type);
}
/// <summary>
/// Appends Tag-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of Tag class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, Tag entity, DbActionType action)
{
command.AddCommonParameters(entity.Id, action);
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = entity.Name.ToDbValue();
command.Parameters.Add("@id_user", SqlDbType.Int).Value = entity.UserId.ToDbValue();
}
public static void SetMStorage(MStorageItem item, DbActionType type)
{
LCL.SetMStorage(item, type);
}
public static void SetMUser(MUserItem item, DbActionType type)
{
LCL.SetMUser(item, type);
}
private void WriteToDb(T item, DbActionType type)
{
SmartDbBus.PlanToWriteToDb(item, type);
}
/// <summary>
/// Appends Account-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of Account class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, Account account, DbActionType action)
{
command.AddCommonParameters(account.Id, action);
command.Parameters.Add("@curr_date_utc", SqlDbType.DateTime).Value = DateTime.UtcNow;
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = account.Name.ToDbValue();
command.Parameters.Add("@id_company", SqlDbType.Int).Value = account.CompanyId.ToDbValue();
command.Parameters.Add("@id_currency", SqlDbType.Int).Value = account.CurrencyId.ToDbValue();
command.Parameters.Add("@initial_balance", SqlDbType.Decimal).Value = account.InitialBalance;
command.Parameters.Add("@id_type", SqlDbType.Int).Value = account.TypeId.ToDbValue();
command.Parameters.Add("@description", SqlDbType.NVarChar).Value = account.Description.ToDbValue();
command.Parameters.Add("@balance", SqlDbType.Decimal).Value = account.Balance;
command.Parameters.Add("@id_financial_institution", SqlDbType.Int).Value = account.FinInstitutionId.ToDbValue();
command.Parameters.Add("@card_number", SqlDbType.NVarChar).Value = account.CardNumber.ToDbValue();
}
/// <summary>
///
/// </summary>
public Sql(OntologyDescriptor ontology, string clientId, string commandId, DbActionType actionType, string localEntityId, InfoItem[] infoValue, Func<DbParameter> createParameter)
{
this._ontology = ontology;
this._localEntityId = localEntityId;
this._infoValue = infoValue;
this._clientId = clientId;
this._commandId = commandId;
this._createParameter = createParameter;
if (ontology == null
|| string.IsNullOrEmpty(localEntityId))
{
this.IsValid = false;
this.Description = "命令信息标识或信息值为空或本体为空或本地标识为空";
throw new ArgumentNullException("command");
}
// 无需把switch流程选择逻辑重构掉，因为actionType枚举不存在变化
switch (actionType)
{
case DbActionType.Insert:
if (infoValue == null || infoValue.Length == 0)
{
this.Description = "命令信息值为空";
break;
}
BuildInsertSql();
break;
case DbActionType.Update:
if (infoValue == null || infoValue.Length == 0)
{
this.Description = "命令信息值为空";
break;
}
BuildUpdateSql();
break;
case DbActionType.Delete:
BuildDeleteSql();
break;
default:
this.IsValid = false;
this.Description = "意外的不能执行的动作码" + actionType.ToString();
break;
}
}
/// <summary>
/// Appends Rule-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of FieldRule class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, FieldRule rule, DbActionType action)
{
command.AddCommonParameters(rule.Id, action);
command.Parameters.Add("@id_user", SqlDbType.Int).Value = rule.UserId.ToDbValue();
command.Parameters.Add("@field_target", SqlDbType.NVarChar).Value = rule.FieldTarget.ToDbValue();
command.Parameters.Add("@field_target_value", SqlDbType.NVarChar).Value = rule.FieldTargetValue.ToDbValue();
command.Parameters.Add("@field", SqlDbType.NVarChar).Value = rule.Field.ToDbValue();
command.Parameters.Add("@field_value", SqlDbType.NVarChar).Value = rule.FieldValue.ToDbValue();
command.Parameters.Add("@priority", SqlDbType.Int).Value = rule.Priority.ToDbValue();
command.Parameters.Add("@type_rule", SqlDbType.SmallInt).Value = (short)rule.RuleType;
}
/// <summary>
/// Appends Account-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of Modem class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, Modem account, DbActionType action)
{
command.AddCommonParameters(account.Id, action);
command.Parameters.Add("@description", SqlDbType.NVarChar).Value = account.Description.ToDbValue();
command.Parameters.Add("@gateway", SqlDbType.NVarChar).Value = account.Description.ToDbValue();
}