public bool RegistrarUsuario(Usuarios model) { System.Data.Common.DbTransaction transaccion = null; AdministraAccesoDatos acceso = null; List <IDataParameter> listaParametros = null; try { acceso = new AdministraAccesoDatos(Configuracion.BASEDATOSLOCAL, TipoProveedor.SqlServer); acceso.ObtenerConexion().Open(); transaccion = acceso.ObtenerConexion().BeginTransaction(); listaParametros = new List <IDataParameter>(); listaParametros.Add(new SqlParameter("@NOMBRE", model.Nombre)); listaParametros.Add(new SqlParameter("@APELLIDO", model.Apellidos)); listaParametros.Add(new SqlParameter("@USUARIO", model.Usuario)); listaParametros.Add(new SqlParameter("@CONTRASENA", model.Contrasena)); acceso.EjecutarStoredProcedureTransaccional("[dbo].[REGISTRA_USUARIO]", listaParametros.ToArray <IDataParameter>(), transaccion); transaccion.Commit(); return(true); } catch (Exception ex) { if (transaccion != null) { transaccion.Rollback(); transaccion.Dispose(); } throw ex; } finally { if (acceso != null) { acceso.CerrarConexion(); } if (transaccion != null) { transaccion.Dispose(); } } }
public bool EliminarUsuario(int IdUsuario) { System.Data.Common.DbTransaction transaccion = null; AdministraAccesoDatos acceso = null; List <IDataParameter> listaParametros = null; try { acceso = new AdministraAccesoDatos(Configuracion.BASEDATOSLOCAL, TipoProveedor.SqlServer); acceso.ObtenerConexion().Open(); transaccion = acceso.ObtenerConexion().BeginTransaction(); listaParametros = new List <IDataParameter>(); listaParametros.Add(new SqlParameter("@IDUSUARIO", IdUsuario)); acceso.EjecutarStoredProcedureTransaccional("[dbo].[ELIMINA_USUARIO]", listaParametros.ToArray <IDataParameter>(), transaccion); transaccion.Commit(); return(true); } catch (Exception ex) { if (transaccion != null) { transaccion.Rollback(); transaccion.Dispose(); } throw ex; } finally { if (acceso != null) { acceso.CerrarConexion(); } if (transaccion != null) { transaccion.Dispose(); } } }
//判断是否给父用户返现 //本过程应该用事务处理 void ParentUserOrderCash(Entities.UserInfo user, Entities.OrderInfo order) { if (order.couponid <= 0) { return; } var dbh = Common.CommonService.Resolve <Common.DB.IDBHelper>(); var coupon = dbh.GetData("select pid from [user.coupon] where id=@0", order.couponid); //判断该代金券是不是别人送的 int pid = ConvertHelper.ToInt32(coupon["pid"]); if (pid <= 0) { return; } //用户不可以自己给自己返现 if (pid == user.id) { return; } //判断该笔订单是否已经返现 object exists_cash_obj = dbh.ExecuteScalar <object>("select top 1 1 from [user.cash] where orderno=@0", order.orderno); bool exists_cash = ConvertHelper.ToInt32(exists_cash_obj) > 0; if (exists_cash) { return; } var config = dbh.GetData("select top 1 [userpercentLimitAmount],[userpercentAmount] from [sys.config] where [enabled]=1"); int userpercentLimitAmount = Convert.ToInt32(config["userpercentLimitAmount"]); int userpercentAmount = Convert.ToInt32(config["userpercentAmount"]); //订单金额小于最小返现金额 if (order.amount < userpercentLimitAmount) { throw new Exception("订单金额小于最小返现金额"); return; } //达到返现最低消费 int percentamt = Convert.ToInt32((decimal)order.amount * ((decimal)userpercentAmount / 100m)); if (percentamt <= 0) { throw new Exception("达到返现最低消费"); return; } var now = DateTime.Now; var conn = dbh.GetConnection(); System.Data.Common.DbTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); var amountCmd = dbh.CreateCommand(); amountCmd.Connection = conn; amountCmd.Transaction = tran; amountCmd.CommandText = "select top 1 amountNow,amountTotal from [user.cashnow] where userid=@userid"; amountCmd.Parameters.Add(dbh.CreateParameter("@userid", pid)); //查询流水 int amountNow = 0; int amountTotal = 0; using (var reader = amountCmd.ExecuteReader()) { if (reader.Read()) { amountNow = reader.GetInt32(0); amountTotal = reader.GetInt32(1); } } int amountNext = amountNow + percentamt; var cmdExe = dbh.CreateCommand(); cmdExe.Transaction = tran; cmdExe.Connection = conn; cmdExe.CommandText = "insert into [user.cash] ([userid],[orderno],[amount],[date],[datets],[amountNow],[amountPrev],[amountTotal],[type],[info]) values (@userid,@orderno,@amount,@date,@datets,@amountNow,@amountPrev,@amountTotal,@type,@info) select @@identity"; cmdExe.Parameters.Add(dbh.CreateParameter("@userid", pid)); cmdExe.Parameters.Add(dbh.CreateParameter("@orderno", order.orderno)); cmdExe.Parameters.Add(dbh.CreateParameter("@amount", percentamt)); cmdExe.Parameters.Add(dbh.CreateParameter("@date", now)); cmdExe.Parameters.Add(dbh.CreateParameter("@datets", Common.Helpers.TimeHelper.GetTimeStamp(now, 10))); //amountNext, amountNow, amountTotal + percentamt cmdExe.Parameters.Add(dbh.CreateParameter("@amountNow", amountNext)); cmdExe.Parameters.Add(dbh.CreateParameter("@amountPrev", amountNow)); cmdExe.Parameters.Add(dbh.CreateParameter("@amountTotal", amountTotal + percentamt)); cmdExe.Parameters.Add(dbh.CreateParameter("@type", "give")); cmdExe.Parameters.Add(dbh.CreateParameter("@info", "好友" + user.name + "到店消费")); object idobj = cmdExe.ExecuteScalar(); int cashid = 0; if (idobj != null && idobj != DBNull.Value) { cashid = Convert.ToInt32(idobj); } var cmdUpdate = dbh.CreateCommand(); cmdUpdate.Transaction = tran; cmdUpdate.Connection = conn; cmdUpdate.CommandText = "update [user.cashnow] set amountNow=@amountNow,amountPrev=@amountPrev,amountTotal=@amountTotal,[date]=@date where userid=@userid"; cmdUpdate.Parameters.Add(dbh.CreateParameter("@userid", pid)); cmdUpdate.Parameters.Add(dbh.CreateParameter("@amountNow", amountNext)); cmdUpdate.Parameters.Add(dbh.CreateParameter("@amountPrev", amountNow)); cmdUpdate.Parameters.Add(dbh.CreateParameter("@amountTotal", amountTotal + percentamt)); cmdUpdate.Parameters.Add(dbh.CreateParameter("@date", now)); int cmdUN = cmdUpdate.ExecuteNonQuery(); if (cmdUN == 0) { var cmdInsert = dbh.CreateCommand(); cmdInsert.Transaction = tran; cmdInsert.Connection = conn; cmdInsert.CommandText = "insert into [user.cashnow] (userid,amountNow,amountPrev,amountTotal,[date]) values (@userid,@amountNow,@amountPrev,@amountTotal,@date)"; cmdInsert.Parameters.Add(dbh.CreateParameter("@userid", pid)); cmdInsert.Parameters.Add(dbh.CreateParameter("@amountNow", amountNext)); cmdInsert.Parameters.Add(dbh.CreateParameter("@amountPrev", amountNow)); cmdInsert.Parameters.Add(dbh.CreateParameter("@amountTotal", amountTotal + percentamt)); cmdInsert.Parameters.Add(dbh.CreateParameter("@date", now)); cmdInsert.ExecuteNonQuery(); } tran.Commit(); new Thread(() => { SendCashMessage(pid, cashid, percentamt, amountNow); }).Start(); } catch (Exception e) { tran.Rollback(); throw e; } finally { if (tran != null) { tran.Dispose(); } if (conn != null) { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } conn.Dispose(); } } //var cash = dbh.GetData("select top 1 amountNow,amountTotal from [user.cashnow] where userid=@0", pid); //if (cash != null) //{ // amountNow = Convert.ToInt32(cash["amountNow"]); // amountTotal = Convert.ToInt32(cash["amountTotal"]); //} //var cashid = dbh.ExecuteScalar<int>("insert into [user.cash] ([userid],[orderno],[amount],[date],[datets],[amountNow],[amountPrev],[amountTotal],[type],[info]) values (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9) select @@identity", pid, order.orderno, percentamt, DateTime.Now, Common.Helpers.TimeHelper.GetTimeStamp(DateTime.Now, 10), amountNext, amountNow, amountTotal + percentamt, "give", "好友" + user.name + "到店消费"); //if (dbh.ExecuteNoneQuery("update [user.cashnow] set amountNow=@0,amountPrev=@1,amountTotal=@2,[date]=@3 where userid=@4", amountnext, amountNow, amountTotal + percentamt, DateTime.Now, pid) == 0) //{ // dbh.ExecuteNoneQuery("insert into [user.cashnow] (userid,amountNow,amountPrev,amountTotal,[date]) values (@0,@1,@2,@3,@4)", pid, amountnext, amountNow, amountTotal + percentamt, DateTime.Now); //} }
//判断是否给用户赠送代金券 //本过程应该用事务处理 void GiveCoupon(Entities.OrderInfo order) { var dbh = Common.CommonService.Resolve <Common.DB.IDBHelper>(); //判断是否达到消费金额 var couponRule = dbh.GetData("select top 1 id,amount,daysBegin,daysEnd from [sys.couponRule] where amountMin<=@0 and amountMax>=@0 and [type]='normal' and [status]=1", order.amount); if (couponRule == null) { return; } int ruleid = ConvertHelper.ToInt32(couponRule["id"]); int ruleAmount = ConvertHelper.ToInt32(couponRule["amount"]); int daysBegin = ConvertHelper.ToInt32(couponRule["daysBegin"]); int daysEnd = ConvertHelper.ToInt32(couponRule["daysEnd"]); DateTime now = DateTime.Now; var conn = dbh.GetConnection(); System.Data.Common.DbTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); var cmdExists = dbh.CreateCommand(); cmdExists.Connection = conn; cmdExists.Transaction = tran; cmdExists.CommandText = "select top 1 1 from [user.coupon] where orderno=@orderno"; cmdExists.Parameters.Add(dbh.CreateParameter("@orderno", order.orderno)); object exo = cmdExists.ExecuteScalar(); if (exo != null && exo != DBNull.Value) { tran.Commit(); } else { var cmdInsert = dbh.CreateCommand(); cmdInsert.Connection = conn; cmdInsert.Transaction = tran; cmdInsert.CommandText = "insert into [user.coupon] (userid,ruleid,amount,type,date,dateBegin,dateEnd,status,statusdate,pid,orderno) values (@userid,@ruleid,@amount,@type,@date,@dateBegin,@dateEnd,@status,@statusdate,@pid,@orderno);"; cmdInsert.Parameters.Add(dbh.CreateParameter("@userid", order.userid)); cmdInsert.Parameters.Add(dbh.CreateParameter("@ruleid", ruleid)); cmdInsert.Parameters.Add(dbh.CreateParameter("@amount", ruleAmount)); cmdInsert.Parameters.Add(dbh.CreateParameter("@type", Constant.CouponType.Order.GetHashCode())); cmdInsert.Parameters.Add(dbh.CreateParameter("@date", now)); cmdInsert.Parameters.Add(dbh.CreateParameter("@dateBegin", now.AddDays(daysBegin))); cmdInsert.Parameters.Add(dbh.CreateParameter("@dateEnd", now.AddDays(daysEnd))); cmdInsert.Parameters.Add(dbh.CreateParameter("@status", Constant.CouponStatus.Enabled.GetHashCode())); cmdInsert.Parameters.Add(dbh.CreateParameter("@statusdate", now)); cmdInsert.Parameters.Add(dbh.CreateParameter("@pid", 0)); cmdInsert.Parameters.Add(dbh.CreateParameter("@orderno", order.orderno)); int rnum = cmdInsert.ExecuteNonQuery(); tran.Commit(); } } catch (Exception ex) { tran.Rollback(); throw ex; } finally { if (tran != null) { tran.Dispose(); } if (conn != null) { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } conn.Dispose(); } } //判断该订单是否赠送过代金券 //if (Convert.ToInt32(dbh.ExecuteScalar<object>("select top 1 1 from [user.coupon] where orderno=@0", order.orderno)) != 1) //{ // dbh.ExecuteNoneQuery("insert into [user.coupon] (userid,ruleid,amount,type,date,dateBegin,dateEnd,status,statusdate,pid,orderno) values (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10);", order.userid, couponRule["id"], couponRule["amount"], Constant.CouponType.Order.GetHashCode(), DateTime.Now, DateTime.Now, DateTime.Now.AddDays(30), Constant.CouponStatus.Enabled.GetHashCode(), DateTime.Now, 0, order.orderno); //} }
/// <summary> /// Updates an object; returns an updated object or null if the object does not exist /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="TAccount"></typeparam> /// <param name="dbCtx"></param> /// <param name="userAccountService"></param> /// <param name="uuid"></param> /// <returns></returns> protected internal virtual async Task <T> UpdateAsync <T, TAccount>(DbContext dbCtx, UserAccountService <TAccount> userAccountService, Guid uuid) where T : MapHiveUserBase where TAccount : RelationalUserAccount { T output; //reassign guid - it usually comes through rest api, so not always present on the object itself //this is usually done at the base lvl, but need proper id for the user validation! Uuid = uuid; //need to validate the model first await ValidateAsync(dbCtx); //make sure the email is ALWAYS lower case Email = Email.ToLower(); //Note: user account resides in two places - MembershipReboot and the MapHive metadata database. //therefore need t manage it in tow places and obviously make sure the ops are properly wrapped into transactions //first get the user as saved in the db var mbrUser = userAccountService.GetByID(uuid); if (mbrUser == null) { throw new BadRequestException(string.Empty); } //in order to check if some mbr ops are needed need to compare the incoming data with the db equivalent var currentStateOfUser = await ReadAsync <T>(dbCtx, uuid); //work out if email is being updated and make sure to throw if it is not possible! var updateEmail = false; if (currentStateOfUser.Email != Email) { //looks like email is about to be changed, so need to check if it is possible to proceed var mbrUserWithSameEmail = userAccountService.GetByEmail(Email); if (mbrUserWithSameEmail != null && mbrUserWithSameEmail.ID != uuid) { throw Validation.Utils.GenerateValidationFailedException(nameof(Email), ValidationErrors.EmailInUse); } //looks like we're good to go. updateEmail = true; } DbContext mbrDbCtx = GetMembershipRebootDbCtx(userAccountService); System.Data.Common.DbTransaction mbrTrans = null; System.Data.Common.DbTransaction mhTransaction = null; //since this method wraps the op on 2 dbs into transactions, it must handle connections manually and take care of closing it aftwerwards //it is therefore required to clone contexts with independent conns so the base contexts can be reused var clonedMhDbCtx = dbCtx.Clone(contextOwnsConnection: false); var clonedMbrDbCtx = mbrDbCtx.Clone(false); try { //open the connections as otherwise will not be able to begin transaction await clonedMbrDbCtx.Database.Connection.OpenAsync(); await clonedMhDbCtx.Database.Connection.OpenAsync(); //begin the transaction and set the transaction object back on the db context so it uses it //do so for both contexts - mbr and mh mbrTrans = clonedMbrDbCtx.Database.Connection.BeginTransaction(); clonedMbrDbCtx.Database.UseTransaction(mbrTrans); mhTransaction = clonedMhDbCtx.Database.Connection.BeginTransaction(); clonedMhDbCtx.Database.UseTransaction(mhTransaction); //check if mbr email related work is needed at all... if (updateEmail) { //Note: //since the change comes from the user edit, can assume this is an authorised operation... userAccountService.SetConfirmedEmail(uuid, Email); } //also check the IsAccountClosed, as this may be modified via update too, not only via Destroy //btw. destroy just adjust the model's property and delegates the work to update if (currentStateOfUser.IsAccountClosed != IsAccountClosed) { if (IsAccountClosed) { userAccountService.CloseAccount(uuid); } else { userAccountService.ReopenAccount(uuid); } } //check the account verification status if (!mbrUser.IsAccountVerified) { if (IsAccountVerified) { userAccountService.SetConfirmedEmail(uuid, Email); } } else { //force one way changes only IsAccountVerified = true; } //mbr work done, so can update the user within the mh metadata db output = await base.UpdateAsync <T>(clonedMhDbCtx, uuid); //looks like we're good to go, so can commit mbrTrans.Commit(); mhTransaction.Commit(); } catch (Exception ex) { mbrTrans?.Rollback(); mhTransaction?.Rollback(); throw Validation.Utils.GenerateValidationFailedException(ex); } finally { //try to close the connections as they were opened manually and therefore may not have been closed! clonedMhDbCtx.Database.Connection.CloseConnection(dispose: true); clonedMbrDbCtx.Database.Connection.CloseConnection(dispose: true); mbrTrans?.Dispose(); mhTransaction?.Dispose(); } return(output); }
public void Dispose() { _innerTransaction.Dispose(); _innerTransaction = null; }