public static void UpdateCardAmount(float amount, float updateAmount, int id, int type, out int uSuccess) { StringBuilder sb = new StringBuilder(); MySqlParameter[] pars = new MySqlParameter[3]; pars[0] = new MySqlParameter("@Id", MySqlDbType.Int32); pars[1] = new MySqlParameter("@Amount", MySqlDbType.Float); if (type == 1) { sb.AppendFormat(" {0} ", "update card set Amount=@Amount,IncomeAmount=@IncomeAmount where Id=@Id"); pars[2] = new MySqlParameter("@IncomeAmount", MySqlDbType.Float); } else if (type == 2) { sb.AppendFormat(" {0} ", "update card set Amount=@Amount,ExpenditureAmount=@ExpenditureAmount where Id=@Id"); pars[2] = new MySqlParameter("@ExpenditureAmount", MySqlDbType.Float); } else if (type == 3) { sb.AppendFormat(" {0} ", "update card set Amount=@Amount,BorrowAmount=@BorrowAmount where Id=@Id"); pars[2] = new MySqlParameter("@BorrowAmount", MySqlDbType.Float); } else if (type == 4) { sb.AppendFormat(" {0} ", "update card set Amount=@Amount,LoanAmount=@LoanAmount where Id=@Id"); pars[2] = new MySqlParameter("@LoanAmount", MySqlDbType.Float); } pars[0].Value = id; pars[1].Value = amount; pars[2].Value = updateAmount; uSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), pars); }
public static void InsertOrUpdatetoExpenses(ExpensesInfo info, out int iSuccess) { StringBuilder sb = new StringBuilder(); ExpensesInfo expenses = GetExpensesById(info.Id); if (expenses.Id > 0) { sb.Append(" update Expenses set OwnerId=@OwnerId,OwnerName=@OwnerName,SpendType=@SpendType,HowToUse=@HowToUse, "); sb.Append(" Price = @Price,Number = @Number,Amount=@Amount,SpendDate=@SpendDate,SpendMode=@SpendMode,ConsumerId=@ConsumerId,ConsumerName=@ConsumerName,Content=@Content"); sb.Append(" where Id=@Id"); } else { sb.Append(" insert into Expenses (OwnerId,OwnerName,CardId,CardNumber,BankCardNumber,SpendType,HowToUse,Price,Number,Amount,SpendDate,SpendMode,ConsumerId,ConsumerName,Content) "); sb.Append(" Values(@OwnerId,@OwnerName,@CardId,@CardNumber,@BankCardNumber, @SpendType,@HowToUse,@Price,@Number,@Amount,@SpendDate,@SpendMode,@ConsumerId,@ConsumerName,@Content)"); } MySqlParameter[] pars = new MySqlParameter[] { new MySqlParameter("@Id", MySqlDbType.Int32), new MySqlParameter("@OwnerId", MySqlDbType.Int32), new MySqlParameter("@OwnerName", MySqlDbType.String), new MySqlParameter("@CardId", MySqlDbType.Int32), new MySqlParameter("@CardNumber", MySqlDbType.String), new MySqlParameter("@BankCardNumber", MySqlDbType.String), new MySqlParameter("@SpendType", MySqlDbType.Int32), new MySqlParameter("@HowToUse", MySqlDbType.String), new MySqlParameter("@Price", MySqlDbType.Float), new MySqlParameter("@Number", MySqlDbType.Int32), new MySqlParameter("@Amount", MySqlDbType.Float), new MySqlParameter("@SpendDate", MySqlDbType.DateTime), new MySqlParameter("@SpendMode", MySqlDbType.Int32), new MySqlParameter("@ConsumerId", MySqlDbType.Int32), new MySqlParameter("@ConsumerName", MySqlDbType.String), new MySqlParameter("@Content", MySqlDbType.String) }; pars[0].Value = info.Id; pars[1].Value = info.OwnerId; pars[2].Value = info.OwnerName; pars[3].Value = info.CardId; pars[4].Value = info.CardNumber; pars[5].Value = info.BankCardNumber; pars[6].Value = info.SpendType; pars[7].Value = info.HowToUse; pars[8].Value = info.Price; pars[9].Value = info.Number; pars[10].Value = info.Amount; pars[11].Value = info.SpendDate; pars[12].Value = info.SpendMode; pars[13].Value = info.ConsumerId; pars[14].Value = info.ConsumerName; pars[15].Value = info.ConsumerName; iSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), pars); if (expenses.Id > 0) { iSuccess = -1; } }
/// <summary> /// 删除一条记录 /// </summary> /// <param name="mySqlTransaction"></param> /// <param name="id"></param> /// <param name="iSuccess"></param> public static void DeleteUser(MySqlTransaction mySqlTransaction, int id, out int iSuccess) { StringBuilder sb = new StringBuilder(); sb.Append(" delete from user where id = @id "); MySqlParameter par = new MySqlParameter("@id", MySqlDbType.Int32); par.Value = id; iSuccess = MySqlDBHelper.ExecuteCommand(mySqlTransaction, sb.ToString(), par); }
public static void DeleteCashIncome(int id, out int iSuccess) { StringBuilder sb = new StringBuilder(); sb.Append(" delete from cashincome where Id = @Id "); MySqlParameter par = new MySqlParameter("@Id", MySqlDbType.Int32); par.Value = id; iSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), par); }
/// <summary> /// 新增或修改 /// </summary> /// <param name="mySqlTransaction"></param> /// <param name="info"></param> /// <param name="iSuccess"></param> public static void InsertOrUpdatetoBorrowed(BorrowORLoanInfo info, out int iSuccess) { StringBuilder sb = new StringBuilder(); if (info.Id > 0) { sb.Append(" update borrowing set BorrowORLoanType = @BorrowORLoanType,BorrowORLoanAccountId=@BorrowORLoanAccountId,BorrowedAccount = @BorrowedAccount,LoanAccount=@LoanAccount,"); sb.Append("Lender = @Lender,Amount = @Amount,HappenedDate = @HappenedDate,ReturnDate = @ReturnDate,Status=@Status,Content = @Content "); sb.Append(" where Id = @Id "); } else { sb.Append(" insert into borrowing (borrowORLoan,BorrowORLoanType,BorrowORLoanAccountId,BorrowedAccount,Borrower,LoanAccount,Lender,Amount,HappenedDate,ReturnDate,Status,Content) "); sb.Append(" Values (@borrowORLoan,@BorrowORLoanType,@BorrowORLoanAccountId,@BorrowedAccount,@Borrower,@LoanAccount,@Lender,@Amount,@HappenedDate,@ReturnDate,@Status,@Content) "); } MySqlParameter[] pars = new MySqlParameter[] { new MySqlParameter("@Id", MySqlDbType.Int32), new MySqlParameter("@borrowORLoan", MySqlDbType.Int32), new MySqlParameter("@BorrowORLoanType", MySqlDbType.Int32), new MySqlParameter("@BorrowORLoanAccountId", MySqlDbType.Int32), new MySqlParameter("@BorrowedAccount", MySqlDbType.String), new MySqlParameter("@Borrower", MySqlDbType.String), new MySqlParameter("@LoanAccount", MySqlDbType.String), new MySqlParameter("@Lender", MySqlDbType.String), new MySqlParameter("@Amount", MySqlDbType.Float), new MySqlParameter("@HappenedDate", MySqlDbType.DateTime), new MySqlParameter("@ReturnDate", MySqlDbType.DateTime), new MySqlParameter("@Status", MySqlDbType.Int32), new MySqlParameter("@Content", MySqlDbType.String) }; pars[0].Value = info.Id; pars[1].Value = 1; pars[2].Value = info.BorrowORLoanType; pars[3].Value = info.BorrowORLoanAccountId; pars[4].Value = info.BorrowedAccount; pars[5].Value = info.Borrower; //pars[4].Value = info.LoanType; pars[6].Value = info.LoanAccount; pars[7].Value = info.Lender; pars[8].Value = info.Amount; pars[9].Value = info.HappenedDate; pars[10].Value = info.ReturnDate; pars[11].Value = info.Status; pars[12].Value = info.Content; iSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), pars); }
//public static UserCollection GetUser(string[] strFields, string[] strValues) //{ //UserCollection coll = new UserCollection(); //StringBuilder sb = new StringBuilder(); //sb.Append("select * from user "); //if (strFields != null && strFields.Length > 0) //{ // MySqlParameter[] pars = new MySqlParameter[strFields.Length]; // for (int i = 0; i < strFields.Length; i++) // { // if (i == 0) // { // sb.Append("where"); // } // else // { // sb.Append("AND "); // } // sb.AppendFormat(" {0}=@{1}", strFields[i], strFields[i]); // pars[i] = GetParType("@" + strFields[i]); // int backvalue = SetValue(strFields[i]); // if (backvalue == 1) // { // pars[i].Value = strValues[i]; // } // else if (backvalue == 2) // { // pars[i].Value = Convert.ToInt32(strValues[i]); // } // } // using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), pars)) // { // while (reader.Read()) // { // coll.Add(new UserInfo(reader)); // } // } //} //else //{ // using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString())) // { // while (reader.Read()) // { // coll.Add(new UserInfo(reader)); // } // } //} //return coll; //} #endregion /*INSERT INTO table_name VALUES (value1,value2,value3,...); * INSERT INTO table_name (column1,column2,column3,...) * VALUES (value1,value2,value3,...); */ /// <summary> /// 新增或修改 /// </summary> /// <param name="mySqlTransaction"></param> /// <param name="info"></param> /// <param name="iSuccess"></param> public static void InsertOrUpdatetoUser(MySqlTransaction mySqlTransaction, UserInfo info, out int iSuccess) { StringBuilder sb = new StringBuilder(); UserInfo userInfo = GetUserByCode(info.Code); if (userInfo.Id > 0) { sb.Append(" update user set Code = @Code,Name = @Name,Password=@Password,Role = @Role,Content = @Content where id = @id "); info.Id = userInfo.Id; } else { sb.Append(" insert into user (Code,Name,Password,EMail,Role,Content) Values (@Code,@Name,@Password,@EMail,@Role,@Content ) "); } MySqlParameter[] pars = new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.Int32), new MySqlParameter("@Code", MySqlDbType.String), new MySqlParameter("@Name", MySqlDbType.String), new MySqlParameter("@Password", MySqlDbType.String), new MySqlParameter("@EMail", MySqlDbType.String), new MySqlParameter("@Role", MySqlDbType.Int32), new MySqlParameter("@Content", MySqlDbType.String) }; pars[0].Value = info.Id; pars[1].Value = info.Code; pars[2].Value = info.Name; pars[3].Value = info.Password; pars[4].Value = info.EMail; pars[5].Value = info.Role; pars[6].Value = info.content; iSuccess = MySqlDBHelper.ExecuteCommand(mySqlTransaction, sb.ToString(), pars); if (info.Id > 0) { iSuccess = -1; } }
public static void InsertOrUpdatetocashincome(CashIncomeInfo info, out int iSuccess) { StringBuilder sb = new StringBuilder(); CashIncomeInfo cashIncomeInfo = GetCashIncomeById(info.Id); if (cashIncomeInfo.Id > 0) { sb.Append(" update cashincome set OwnerId=@OwnerId,OwnerName=@OwnerName,IncomeAmount = @IncomeAmount,PreMode = @PreMode,Mode = @Mode,PreRate=@PreRate,Rate=@Rate, "); sb.Append(" DepositDate = @DepositDate,BDate = @BDate,EDate=@EDate,AutoSave=@AutoSave,DepositorId=@DepositorId,DepositorName=@DepositorName,DepositMode=@DepositMode,"); sb.Append(" Status=@Status,IncomeType=@IncomeType,TAmount=@TAmount,Content=@Content where Id=@Id"); } else { sb.Append(" insert into cashincome (OwnerId,OwnerName,CardId,CardNumber,BankCardNumber,IncomeAmount,PreMode,Mode,PreRate,Rate,DepositDate,BDate,EDate,AutoSave,DepositorId, "); sb.Append(" DepositorName,DepositMode,Status,IncomeType,TAmount,Content) "); sb.Append(" Values(@OwnerId,@OwnerName,@CardId,@CardNumber,@BankCardNumber, @IncomeAmount,@PreMode,@Mode,@PreRate,@Rate,@DepositDate,@BDate,@EDate,@AutoSave,@DepositorId,"); sb.Append(" @DepositorName,@DepositMode,@Status,@IncomeType,@TAmount,@Content)"); } MySqlParameter[] pars = new MySqlParameter[] { new MySqlParameter("@Id", MySqlDbType.Int32), new MySqlParameter("@OwnerId", MySqlDbType.Int32), new MySqlParameter("@OwnerName", MySqlDbType.String), new MySqlParameter("@CardId", MySqlDbType.Int32), new MySqlParameter("@CardNumber", MySqlDbType.String), new MySqlParameter("@BankCardNumber", MySqlDbType.String), new MySqlParameter("@IncomeAmount", MySqlDbType.Float), new MySqlParameter("@PreMode", MySqlDbType.Int32), new MySqlParameter("@Mode", MySqlDbType.Int32), new MySqlParameter("@PreRate", MySqlDbType.Int32), new MySqlParameter("@Rate", MySqlDbType.Int32), new MySqlParameter("@DepositDate", MySqlDbType.DateTime), new MySqlParameter("@BDate", MySqlDbType.DateTime), new MySqlParameter("@EDate", MySqlDbType.DateTime), new MySqlParameter("@AutoSave", MySqlDbType.Int32), new MySqlParameter("@DepositorId", MySqlDbType.Int32), new MySqlParameter("@DepositorName", MySqlDbType.String), new MySqlParameter("@DepositMode", MySqlDbType.Int32), new MySqlParameter("@Status", MySqlDbType.Int32), new MySqlParameter("@IncomeType", MySqlDbType.Int32), new MySqlParameter("@TAmount", MySqlDbType.Float), new MySqlParameter("@Content", MySqlDbType.String) }; pars[0].Value = info.Id; pars[1].Value = info.OwnerId; pars[2].Value = info.OwnerName; pars[3].Value = info.CardId; pars[4].Value = info.CardNumber; pars[5].Value = info.BankCardNumber; pars[6].Value = info.IncomeAmount; pars[7].Value = info.PreMode; pars[8].Value = info.Mode; pars[9].Value = info.PreRate; pars[10].Value = info.Rate; pars[11].Value = info.DepositDate; pars[12].Value = info.BDate; pars[13].Value = info.EDate; pars[14].Value = info.AutoSave; pars[15].Value = info.DepositorId; pars[16].Value = info.DepositorName; pars[17].Value = info.DepositMode; pars[18].Value = info.Status; pars[19].Value = info.IncomeType; pars[20].Value = info.TAmount; pars[21].Value = info.Content; iSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), pars); if (cashIncomeInfo.Id > 0) { iSuccess = -1; } }
/// <summary> /// 新增或修改 /// </summary> /// <param name="mySqlTransaction"></param> /// <param name="info"></param> /// <param name="iSuccess"></param> public static void InsertOrUpdatetoCard(MySqlTransaction mySqlTransaction, CardInfo info, out int iSuccess) { StringBuilder sb = new StringBuilder(); CardInfo cardInfo = GetCardByCardNumber(info.CardNumber, info.UserId); if (cardInfo.Id > 0) { sb.Append(" update card set BankId = @BankId,CardNumber = @CardNumber,AccountType = @AccountType,Amount=@Amount,"); sb.Append("ExpenditureAmount = @ExpenditureAmount,BorrowAmount = @BorrowAmount,LoanAmount = @LoanAmount,IncomeAmount = @IncomeAmount,"); sb.Append("OwnerId = @OwnerId,OwnerCode = @OwnerCode,UserId = @UserId,UserCode = @UserCode,BankName = @BankName,OpenDate = @OpenDate,Content=@Content "); sb.Append(" where Id = @Id"); info.Id = cardInfo.Id; info.Amount = cardInfo.Amount; info.ExpenditureAmount = cardInfo.ExpenditureAmount; info.BorrowAmount = cardInfo.BorrowAmount; info.IncomeAmount = cardInfo.IncomeAmount; } else { sb.Append(" insert into card (BankId,CardNumber,AccountType,Amount,ExpenditureAmount,BorrowAmount,LoanAmount,IncomeAmount,OwnerId,OwnerCode,UserId,UserCode,BankName,OpenDate,Content) "); sb.Append(" Values (@BankId,@CardNumber,@AccountType,@Amount,@ExpenditureAmount,@BorrowAmount,@LoanAmount,@IncomeAmount,@OwnerId,@OwnerCode,@UserId,@UserCode,@BankName,@OpenDate,@Content)"); } MySqlParameter[] pars = new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.Int32), new MySqlParameter("@BankId", MySqlDbType.Int32), new MySqlParameter("@CardNumber", MySqlDbType.String), new MySqlParameter("@AccountType", MySqlDbType.Int32), new MySqlParameter("@Amount", MySqlDbType.Float), new MySqlParameter("@ExpenditureAmount", MySqlDbType.Float), new MySqlParameter("@BorrowAmount", MySqlDbType.Float), new MySqlParameter("@LoanAmount", MySqlDbType.Float), new MySqlParameter("@IncomeAmount", MySqlDbType.Float), new MySqlParameter("@OwnerId", MySqlDbType.Int32), new MySqlParameter("@OwnerCode", MySqlDbType.String), new MySqlParameter("@UserId", MySqlDbType.Int32), new MySqlParameter("@UserCode", MySqlDbType.String), new MySqlParameter("@BankName", MySqlDbType.String), new MySqlParameter("@OpenDate", MySqlDbType.DateTime), new MySqlParameter("@Content", MySqlDbType.String), }; pars[0].Value = info.Id; pars[1].Value = info.BankId; pars[2].Value = info.CardNumber; pars[3].Value = info.AccountType; pars[4].Value = info.Amount; pars[5].Value = info.ExpenditureAmount; pars[6].Value = info.BorrowAmount; pars[7].Value = info.LoanAmount; pars[8].Value = info.IncomeAmount; pars[9].Value = info.OwnerId; pars[10].Value = info.OwnerCode; pars[11].Value = info.UserId; pars[12].Value = info.UserCode; pars[13].Value = info.BankName; pars[14].Value = info.OpenDate; pars[15].Value = info.Content; iSuccess = MySqlDBHelper.ExecuteCommand(mySqlTransaction, sb.ToString(), pars); if (cardInfo.Id > 0) { iSuccess = -1; } }