public DataTransferObject.Organization.WithholdingView GetWithholding(WithholdingAccountType accountType, Guid company)
{
DataTransferObject.Organization.WithholdingView withholding = null;
//[AccountNo] = @AccountNo AND
string sql = "SELECT [AccountNo],[AccountType],[Time],[Status],[Amount],[Company] FROM [T_Withholding] WHERE [AccountType]=@AccountType AND [Company] = @Company ";
using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString)) {
//dbOperator.AddParameter("AccountNo", withholdingView.AccountNo);
dbOperator.AddParameter("AccountType", accountType);
dbOperator.AddParameter("Company", company);
using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader(sql))
{
if (reader.Read())
{
withholding = new DataTransferObject.Organization.WithholdingView();
withholding.AccountNo = reader.GetString(0);
withholding.AccountType = (WithholdingAccountType)reader.GetByte(1);
withholding.Time = reader.GetDateTime(2);
withholding.Status = (WithholdingProtocolStatus)reader.GetByte(3);
withholding.Amount = reader.GetDecimal(4);
withholding.Company = reader.GetGuid(5);
}
}
}
return(withholding);
}
/*缓存代码
* public int Delete(Account value)
* {
* throw new NotImplementedException();
* }
*
* public int Insert(Account value)
* {
* return Save(value.Company, value);
* }
*
* public IEnumerable<KeyValuePair<string, Account>> Query()
* {
* string sql = "SELECT [Company],[Account],[Valid],[Type],[Time] FROM T_Account WHERE Valid=1";
* using (DbOperator dbOperator = new DbOperator(Provider,ConnectionString))
* {
* using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader(sql))
* {
* while (reader.Read())
* {
* Guid companyId = reader.GetGuid(0);
* string accountNo = reader.GetString(1);
* bool valid = reader.GetBoolean(2);
* AccountType type= (AccountType)reader.GetInt32(3);
* DateTime time = reader.GetDateTime(4);
* Account account = new Account(companyId,accountNo,type,valid,time);
* yield return new KeyValuePair<string, Account>(companyId.ToString() + type.GetHashCode(),account);
* }
* }
* }
* }
*
* public int Update(Account value)
* {
* return Save(value.Company, value);
* }*/
public void SetWithholdingInfo(DataTransferObject.Organization.WithholdingView withholdingView)
{
string sql = "IF EXISTS(SELECT NULL FROM T_Withholding WHERE AccountType=@AccountType AND Company =@Company ) UPDATE T_Withholding SET [Time] = @Time,[Status]=@Status,Amount =@Amount,AccountNo=@AccountNo WHERE AccountType=@AccountType AND Company =@Company ELSE INSERT INTO T_Withholding(AccountNo,AccountType,[Time],[Status],Amount,Company) Values(@AccountNo,@AccountType,@Time,@Status,@Amount,@Company) ";
using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString))
{
dbOperator.AddParameter("AccountNo", withholdingView.AccountNo);
dbOperator.AddParameter("AccountType", withholdingView.AccountType);
dbOperator.AddParameter("Time", withholdingView.Time);
dbOperator.AddParameter("Status", withholdingView.Status);
dbOperator.AddParameter("Amount", withholdingView.Amount);
dbOperator.AddParameter("Company", withholdingView.Company);
dbOperator.ExecuteNonQuery(sql);
}
}