This is based on Dapper 1.50.5
requestModel:
public class TopupHistory_WebRequestModel
{
public DateTime BeginDate { get; set; }
public DateTime EndDate { get; set; }
public string CustID { get; set; }
}
SELECT:
public ResponseModel<TopupHistory_APResponseModel> GetTopupHistory(TopupHistory_WebRequestModel requestModel)
{
#region Contract
if (requestModel == null) { throw new ArgumentNullException($"{nameof(requestModel)} can't be null."); }
#endregion
string connectionString = ConfigurationManager.ConnectionStrings["xxx"].ToString();
ResponseModel<TopupHistory_APResponseModel> result = new ResponseModel<TopupHistory_APResponseModel>();
TopupHistory_APResponseModel content = new TopupHistory_APResponseModel();
using (SqlConnection conn = new SqlConnection(connectionString))
{
string sql = @" SELECT TxnSeq, CustID, ChkCustomerNo, IsKeyIn, BillingAccount, CompanyName, ReferenceNo, FromAccount,
Currency, Amount, Message, FuseReturnCode, FuseReturnDescription, CreateDT
FROM Payment_Result WITH (NOLOCK)
WHERE CreateDT >= @BeginDate
AND CreateDT < @EndDate
AND CustID = @CustID
AND FuseReturnCode = '0000' ";
content.DBPaymentResultList = conn.Query<DBPaymentResult>(sql, requestModel).ToList();
}
if (content != null)
{
result.IsSuccess = true;
result.ReturnCode = "200";
result.Content = content;
}
else
{
result.IsSuccess = false;
result.ErrorMsg = "Cannot get data from DB";
}
return result;
}
dataModel:
public class DBPaymentResult
{
public Guid TxnSeq { get; set; }
public string CustID { get; set; }
public string ChkCustomerNo { get; set; }
public bool IsKeyIn { get; set; }
public string BillingAccount { get; set; }
public string CompanyName { get; set; }
public string ReferenceNo { get; set; }
public string FromAccount { get; set; }
public string Currency { get; set; }
public decimal Amount { get; set; }
public string Message { get; set; }
public string FuseReturnCode { get; set; }
public string FuseReturnDescription { get; set; }
public DateTime CreateDT { get; set; }
}
INSERT:
public void SavePaymentResult(BasicData basicData, ImmediatePayment_WebRequestModel requestModel, ImmediatePayment_APResponseModel responseModel)
{
#region Contract
if (requestModel == null) { throw new ArgumentNullException($"{nameof(requestModel)} can't be null."); }
#endregion
string connectionString = ConfigurationManager.ConnectionStrings["xxx"].ToString();
DBPaymentResult dataModel = new DBPaymentResult();
dataModel.TxnSeq = basicData.TxnSeq;
dataModel.CustID = requestModel.CustID;
dataModel.ChkCustomerNo = requestModel.CHK_CUSTOMER_NO;
dataModel.IsKeyIn = requestModel.IsKeyIn;
dataModel.BillingAccount = requestModel.L_MT_COMP;
dataModel.CompanyName = requestModel.CompanyName;
dataModel.ReferenceNo = requestModel.ReferenceNo;
dataModel.FromAccount = requestModel.DEBIT_ACCT_NO;
dataModel.Currency = requestModel.DEBIT_CURRENCY;
dataModel.Amount = decimal.Parse(requestModel.DEBIT_AMOUNT);
dataModel.Message = requestModel.Message;
dataModel.FuseReturnCode = responseModel.returnCode;
dataModel.FuseReturnDescription = responseModel.returnDesc;
dataModel.CreateDT = DateTime.Now;
string sql = @" INSERT INTO Payment_Result (TxnSeq, CustID, ChkCustomerNo, IsKeyIn, BillingAccount, CompanyName, ReferenceNo, FromAccount, Currency, Amount, Message, FuseReturnCode, FuseReturnDescription, CreateDT)
VALUES (@TxnSeq, @CustID, @ChkCustomerNo, @IsKeyIn, @BillingAccount, @CompanyName, @ReferenceNo, @FromAccount, @Currency, @Amount, @Message, @FuseReturnCode, @FuseReturnDescription, @CreateDT) ";
using (SqlConnection conn = new SqlConnection(connectionString))
{
var affectedRows = conn.Execute(sql, dataModel);
//return affectedRows > 0;
}
}
UPDATE multi rows and TRANSACTION:
public ResponseModel<bool> UpdateSystemMaintain(SystemMaintainEdit requestData, AuthorizationData userProfile)
{
#region Contract
if (requestData == null) { throw new ArgumentNullException($"{nameof(requestData)} can't be null."); }
#endregion
string connectionString = ConfigurationManager.ConnectionStrings["DD6CIAS02"].ToString();
int count = 0;
ResponseModel<bool> result = new ResponseModel<bool>();
DateTime currentTime = DateTime.Now;
using (SqlConnection conn = new SqlConnection(connectionString))
{
string sqlUpdataAdminSystemMaintainTempStorage = @"
UPDATE AdminSystemMaintainTempStorage
SET ApplicationStatus = @ApplicationStatus, ApproverId = @ApproverId, ApprovedDateTime = @ApprovedDateTime
WHERE TxnSeq = @TxnSeq";
DBAdminSystemMaintainTempStorage tempData = new DBAdminSystemMaintainTempStorage()
{
TxnSeq = requestData.TxnSeq,
ApplicationStatus = requestData.ApplicationStatus,
ApproverId = userProfile.UserId,
ApprovedDateTime = DateTime.Now
};
string sqlUpdataSystemParameter = @"
UPDATE System_Parameter
SET ParamValue = @ParamValue, ModifyDT = @ModifyDT
WHERE ParamKey = @ParamKey";
List<DBSystemParameter> paramDatas = new List<DBSystemParameter>()
{
new DBSystemParameter() { ParamKey = nameof(requestData.IsSystemMaintain), ParamValue = requestData.IsSystemMaintain, ModifyDT = currentTime },
new DBSystemParameter() { ParamKey = nameof(requestData.SystemMaintainEndTime), ParamValue = requestData.SystemMaintainEndTime.ToString("yyyy/MM/dd HH:mm"), ModifyDT = currentTime },
new DBSystemParameter() { ParamKey = nameof(requestData.SystemMaintainStartTime), ParamValue = requestData.SystemMaintainStartTime.ToString("yyyy/MM/dd HH:mm"), ModifyDT = currentTime }
};
//Transaction
conn.Open();
using (var tran = conn.BeginTransaction())
{
if (requestData.ApplicationStatus == "Agree")
count = conn.Execute(sqlUpdataSystemParameter, paramDatas, transaction: tran);
count = conn.Execute(sqlUpdataAdminSystemMaintainTempStorage, tempData, transaction: tran);
tran.Commit();
result.IsSuccess = true;
result.Content = true;
}
}
return result;
}