Api Convert Currency (edit)

• DbUp: Chạy script để đồng bộ database
• Oracle Server Side Paging: Paging phía Server truyền lên PageSize, PageIndex dùng câu Raw SQL
• Dapper: dùng với Raw SQL
• cUrl: Chuyển đổi tiền tệ giữa VND-USD-JPY (Việt-Nhật-Mỹ) dùng API online public
• Oracle DbDataReader: Pagination in Oracle - @manhng
• Oracle Command Timeout: Pagination in Oracle - @manhng

Pagination in Oracle - @manhng

Api Convert Currency Online

1 VND to USD - Vietnamese Dongs to US Dollars Exchange Rate (xe.com)

1 USD to VND - US Dollars to Vietnamese Dongs Exchange Rate (xe.com)

1 JPY to VND - Japanese Yen to Vietnamese Dongs Exchange Rate (xe.com)

1 VND to JPY - Vietnamese Dongs to Japanese Yen Exchange Rate (xe.com)

cURL - @manhng

curl -X GET "https://www.xe.com/currencyconverter/convert/?Amount=1&From=VND&To=USD"

Dapper

Working With Parameters When Using Dapper | Learn Dapper

Paging

Paging in ASP.NET Core Web API - Code Maze (code-maze.com)

Paging Oracle

SELECT A.*, CEIL(TOTAL_NUM_ROWS / 10) TOTAL_NUM_PAGES
FROM ( SELECT B.ID, B.FIRST_NAME, B.LAST_NAME, B.UPLOAD_DATE, row_number() OVER(ORDER BY UPLOAD_DATE DESC) rn, COUNT(*) OVER() TOTAL_NUM_ROWS
 FROM PERSON B 
 ) A 
WHERE rn BETWEEN (1 - 1) * 10 + 1 AND 1 * 10


SELECT A.*, CEIL(TOTAL_NUM_ROWS / 10) TOTAL_NUM_PAGES
FROM ( SELECT B.ID, B.FIRST_NAME, B.LAST_NAME, B.UPLOAD_DATE, row_number() OVER(ORDER BY UPLOAD_DATE DESC) rn, COUNT(*) OVER() TOTAL_NUM_ROWS
 FROM PERSON B 
 ) A 
WHERE rn BETWEEN (2 - 1) * 10 + 1 AND 2 * 10


SELECT A.*, CEIL(TOTAL_NUM_ROWS / :PAGESIZE) TOTAL_NUM_PAGES
FROM ( SELECT B.ID, B.FIRST_NAME, B.LAST_NAME, B.UPLOAD_DATE, row_number() OVER(ORDER BY UPLOAD_DATE DESC) rn, COUNT(*) OVER() TOTAL_NUM_ROWS
 FROM PERSON B 
 ) A 
WHERE rn BETWEEN (:PAGEINDEX - 1) * :PAGESIZE + 1 AND :PAGEINDEX * :PAGESIZE

DbDataReader Oracle

Retrieving Data Using a DataReader - ADO.NET | Microsoft Docs

DbUp Oracle

Releases · DbUp/DbUp (github.com)

DbUp/DbUp: DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date. (github.com)

Using Database Project and DbUp for database management - Kamil Grzybek

Simplifying database development with docker and DbUp | Blog (mcode.it) (HAY HAY HAY)

Snippet

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="dbup-core" version="4.5.0" targetFramework="net472" />
  <package id="dbup-oracle" version="4.5.0" targetFramework="net472" />
  <package id="Oracle.ManagedDataAccess" version="18.3.0" targetFramework="net472" />
</packages>

Snippet

using DbUp;
using DbUp.Oracle;
using System;
using System.Linq;
using System.Reflection;
 
namespace Oracle.DbUp
{
    class Program
    {
        [Obsolete]
        static void Main(string[] args)
        {
            var connectionString =
                args.FirstOrDefault()
                ?? "DATA SOURCE=localhost/ORCL;USER ID=sa;PASSWORD=123456;Min Pool Size=5;Decr Pool Size=10;PERSIST SECURITY INFO=True";
            var upgrader = DeployChanges.To
                .OracleDatabase(connectionString)
                .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                .LogToConsole()
                .Build();
            var result = upgrader.PerformUpgrade();
            if (!result.Successful)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(result.Error);
                Console.ResetColor();
                Environment.Exit(-1);
            }
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine(value: "Success!");
            Console.ResetColor();
            Environment.Exit(0);
        }
    }
}

Mapping Generator (edit)

MappingGenerator was initially created as a design-time alternative to AutoMapper. Now it is evolving into a coding assistant to whom you can delegate the most mundane coding tasks.

Alternative to AutoMapper

MappingGenerator - Visual Studio Marketplace

A Simple and Fast Object Mapper (HAY HAY HAY)

Nuget: Boxed.Mapping

A Simple and Fast Object Mapper - Muhammad Rehan Saeed

AutoMapper Usage Guidelines

Nuget: AutoMapper

AutoMapper Usage Guidelines (jimmybogard.com)

Adding errors to model state and returning bad request within asp.net core 3.1

Adding errors to model state and returning bad request within asp.net core 3.1 - Developer Ramblings of Kevin Smith (kevsoft.net)

Paging data in MongoDB with C#

Paging data in MongoDB with C# - Developer Ramblings of Kevin Smith (kevsoft.net)

Storing GUIDs as strings in MongoDB with C#

Storing GUIDs as strings in MongoDB with C# - Developer Ramblings of Kevin Smith (kevsoft.net)

Managing Relationships With Dapper

Managing Relationships With Dapper | Learn Dapper

Script Create Table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SysExternalUsers](
[Id] [uniqueidentifier] NOT NULL,
[Email] [nvarchar](255) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Address] [nvarchar](255) NULL,
[Tel] [nvarchar](255) NULL,
[Title] [nvarchar](50) NULL,
[Birthday] [datetime] NULL,
CONSTRAINT [PK_SysExternalUsers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

STORED PROCEDURE: Unit Test

USE [AcpiNetdb]
GO

DECLARE @RC int
DECLARE @SqlSearch nvarchar(max)
DECLARE @PageIndex int
DECLARE @PageSize int
DECLARE @RecordCount int

SET @SqlSearch='SELECT * FROM SysExternalUsers'

--SET @SqlSearch='SELECT * FROM SysExternalUsers WHERE Name LIKE N''%Manh%'''

--SET @SqlSearch='SELECT * FROM SysExternalUsers WHERE CONVERT(VARCHAR, Birthday, 120) LIKE N''%1983%'''

SET @PageIndex=1
SET @PageSize=10

EXECUTE @RC = [dbo].[GetUsersPagedList]
@SqlSearch
,@PageIndex
,@PageSize
,@RecordCount OUTPUT
GO

STORED PROCEDURE: Searching + Paging

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE GetUsersPagedList
@SqlSearch NVARCHAR(MAX)
,@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Temp TABLE
(
[Id] [uniqueidentifier] NOT NULL,
[Email] [nvarchar](255) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Address] [nvarchar](255) NULL,
[Tel] [nvarchar](255) NULL,
[Title] [nvarchar](50) NULL,
[Birthday] [datetime] NULL
)

INSERT @Temp EXECUTE sp_executesql @SqlSearch;

SELECT ROW_NUMBER() OVER
(
ORDER BY [Id] ASC
)AS RowNumber
,[Id]
,[Email]
,[Name]
,[Address]
,[Tel]
,[Title]
,[Birthday]
INTO #Results
FROM @Temp

SELECT @RecordCount = COUNT(*)
FROM #Results

SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results
END
GO

Return Data From a Stored Procedure

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure

Building Dynamic SQL In a Stored Procedure

https://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

Execute Dynamic Sql Commands in SQL Server

https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Implement Paging DataGridView in Windows Forms (WinForms) Application

https://www.aspsnippets.com/Articles/Implement-Paging-DataGridView-in-Windows-Forms-WinForms-Application-using-C-and-VBNet.aspx

Custom Paging in ASP.Net GridView using SQL Server Stored Procedure

https://www.aspsnippets.com/Articles/Custom-Paging-in-ASPNet-GridView-using-SQL-Server-Stored-Procedure.aspx

Implement Paging in DataGridView

https://www.mindstick.com/Articles/1334/implement-paging-in-datagridview-c-sharp

Other

https://www.codeproject.com/Tips/377207/Custom-paging-like-Google-paging

https://www.codeproject.com/Articles/19058/A-Neat-Solution-to-GridView-Custom-Paging

https://www.codeproject.com/Articles/25835/GridView-Custom-Paging-with-PageSize-Change-Dropdo

https://www.codeproject.com/Articles/410733/Custom-paging-with-ASP-NET-GridView

Paging (edit)

Linq

https://stackoverflow.com/questions/17047192/entityframework-do-paging-on-a-query-with-a-join

var data = (from v in context.Vehicles
         join va in context.VehicleAttributes on v.VehicleId equals va.VehicleId into vAttributes
         from vehicleAttributes in vAttributes.DefaultIfEmpty()
         where v.FleetId == fleetId
         select new { v, vehicleAttributes })
         .OrderBy(p => p.v.FleetId)
         .Skip(10 * (page - 1))
         .Take(10)
         .ToList();

Paging a Query with SQL Server

https://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx

Paging of Large Resultsets in ASP.NET

https://www.codeproject.com/Articles/6936/Paging-of-Large-Resultsets-in-ASP-NET

What is the best way to paginate results in SQL Server

Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you would determine the total number of results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.

https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server

SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY CreatedOn ) AS RowNum ,
*
FROM dbo.Project
WHERE CreatedOn >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum;

Tutorial 25: Efficiently Paging Through Large Amounts of Data

https://msdn.microsoft.com/en-us/library/bb445504.aspx