Jeffrey's coding note

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;
}