Pagination in Oracle (edit)

• row_number()
• rank()
• dense_rank()
• count(*)

Oracle with DbDataReader

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

c# - What is the fastest way to read data from a DbDataReader? - Stack Overflow

.net - Timeout for OracleDataReader.Read Method - Stack Overflow

c# - Changing the where clause to use Oracle Command Parameter - Stack Overflow

Example

  1  select ename,sal,
  2   row_number()
  3     over (order by sal desc)rn,
  4   rank()
  5     over (order by sal desc)rnk,
  6   dense_rank()
  7     over (order by sal desc)drnk
  8   from emp
  9  order by sal desc

Oracle sql pagination with total pages or total entries - Stack Overflow (HAY HAY HAY)

Best Practice (HAY HAY HAY)

SELECT res.*,
 CEIL(total_num_rows/10) total_num_pages
FROM (SELECT o.*,
 row_number() OVER (ORDER BY CREATION_TS DESC, ID DESC) rn,
 COUNT(*) OVER () total_num_rows
 FROM ORCL.TODOITEM o
 WHERE DESCRIPTION LIKE '%ask%') res
WHERE rn BETWEEN (1 - 1) * 10 + 1 AND 1 * 10;

SELECT res.*,
 CEIL(total_num_rows/pageSize) total_num_pages
FROM (SELECT o.*,
 row_number() OVER (ORDER BY CREATION_TS DESC, ID DESC) rn,
 COUNT(*) OVER () total_num_rows
 FROM ORCL.TODOITEM o
 WHERE DESCRIPTION LIKE '%ask%') res
WHERE rn BETWEEN (pageNumber - 1) * pageSize + 1 AND pageNumber * pageSize;

Pagination And Total Number Of Rows From One SELECT – Andrey Zavadskiy – SQL and .NET developer, trainer, speaker

SELECT res.*,
       CEIL(total_num_rows/pagesize) total_num_pages
FROM   (SELECT o.*,
               row_number() OVER (ORDER BY orderdate DESC, shippingdate DESC) rn,
               COUNT(*) OVER () total_num_rows
        FROM   orders o
        WHERE  customerid LIKE 'A%') res
WHERE  rn BETWEEN (pagenumber - 1) * pagesize + 1 AND pagenumber * pagesize;

Sql - (Oracle) How get total number of results when using a pagination query? - Stack Overflow

How To Do Paging With ASP.NET Web API (c-sharpcorner.com)

Simple Solution

SELECT ID, DESCRIPTION, CREATION_TS, DONE, 
 (SELECT COUNT(*) FROM (SELECT ID, DESCRIPTION, CREATION_TS, DONE
 FROM ORCL.TODOITEM
 ORDER BY CREATION_TS DESC)) TOTAL
 FROM (SELECT A.ID, A.DESCRIPTION, A.CREATION_TS, A.DONE, rownum rn
 FROM (SELECT ID, DESCRIPTION, CREATION_TS, DONE
 FROM ORCL.TODOITEM
 ORDER BY CREATION_TS DESC) A
 WHERE rownum <= 20)
 WHERE rn >= 11;

Oracle Data Provider for .NET

Oracle Data Provider for .NET (ODP.NET) features optimized ADO.NET data access to the Oracle database. ODP.NET allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, self-tuning statement cache, Application Continuity, and Fast Connection Failover. Developers can also use popular .NET features, such as Entity Framework Core.

There are three driver types: ODP.NET Core; ODP.NET, Managed Driver; and ODP.NET, Unmanaged Driver. ODP.NET Core is designed for multi-platform .NET (Core) applications. ODP.NET, Managed Driver is 100% managed code .NET Framework provider. Developers deploy a single assembly in a deployment package smaller than 10 MB. ODP.NET, Unmanaged Driver is the traditional Oracle ADO.NET provider that uses the Oracle Database Client.

ODP.NET 21c add support for user-defined types, .NET 6, Entity Framework Core 6, binary JSON data type, Client Initiated Continuous Query Notification, and more.

Oracle Data Provider for .NET (ODP.NET)

Microsoft PowerPoint - Oracle Perf .NET BP.pptx

Best practice for pagination in Oracle? - Stack Overflow

C# - What are the best practices working with Oracle.DataAccess.Client? - Stack Overflow

C# - OracleCommand SQL Parameters Binding - Stack Overflow

C# OracleCommand SQL Call Procedure with Params - Stack Overflow

Using StringBuilder To Build Query

SELECT ID, DESCRIPTION, CREATION_TS, DONE
FROM ORCL.TODOITEM;

SELECT ID, DESCRIPTION, CREATION_TS, DONE
FROM (SELECT A.ID, A.DESCRIPTION, A.CREATION_TS, A.DONE, rownum rn
FROM (SELECT ID, DESCRIPTION, CREATION_TS, DONE
FROM ORCL.TODOITEM
ORDER BY CREATION_TS DESC) A
WHERE rownum <= 10)
WHERE rn >= 1;

 SELECT * 
 FROM (SELECT A.*, rownum rn
 FROM (SELECT *
 FROM your_table
 ORDER BY your_column) A
 WHERE rownum <= :Y)
 WHERE rn >= :X

Transaction Scope (edit)

Implementing an Implicit Transaction using Transaction Scope | Microsoft Docs

Implementing an Implicit Transaction using Transaction Scope | Microsoft Docs

TransactionScope: A simple way to handle transactions in .NET | Code Wala (2018)

TransactionScope Considered Annoying | Josh the Coder (2020)

• Transaction Management
• Transaction or TransactionScope
• TransactionScope
• TransactionScope Timeout
• Multiple databases
• Dapper
• Dapper CommandTimeout
• Logging
• Autofac DI using in Console Application

All About TransactionScope - CodeProject

Handling Transactions in .NET Using TransactionScope (codeguru.com)

1. using (var transactionScope = new TransactionScope())
2. using (var transactionScope = new TransactionScope(TransactionScopeOption.Required)) // default
3. using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))

c# - Advanced System.Transactions debugging - Stack Overflow

<configuration>
 <system.diagnostics>
  <sources>
   <source name="System.Transactions" switchValue="Information">
   <listeners>
    <add name="tx" type="System.Diagnostics.XmlWriterTraceListener" initializeData= "tx.log" />
   </listeners>
  </source>
 </sources>
</system.diagnostics>

[SOLVED] => Transaction scope timeout on 10 minutes (entityframework.net)

Transaction Scope uses the Machine config setting as the maximum timeout. The default machine timeout is 10 minutes.

TransactionScope Has a Default Timeout (stephencleary.com)

Passing TimeSpan.Zero into the TransactionScope constructor will instruct it to use the maximum timeout (TransactionManager.MaximumTimeout, which has a default value of 10 minutes) instead of the default. Unfortunately, the maximum timeout can only be increased by editing the machine.config file.

using new TransactionScope() Considered Harmful | Microsoft Docs

public class TransactionUtils {
    public static TransactionScope CreateTransactionScope() {
        var transactionOptions = new TransactionOptions();
        transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
        transactionOptions.Timeout = TransactionManager.MaximumTimeout;
        return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
    }
}

Show me the code

    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() {
        IsolationLevel = System.Transactions.IsolationLevel.Serializable,
        Timeout = TimeSpan.FromMinutes(10)
    }))

    private void SomeMethod()
    {
        try
        {
            using (var _transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
            {
                using (SqlConnection _connection = new SqlConnection(connectionstring))
                {
                    _connection.Open();

                    DoSomething()...
                }

                _transactionScope.Complete();
            }
        }
        catch (TransactionAbortedException e)
        {
            nlog.Error(string.Format("The transaction has been aborted: {0}", e.Message));
            throw;
        }
        catch (Exception e)
        {
            throw;
        }
    }

Autofac DI container in Console App

dependency injection - Correct use of Autofac in C# console application - Stack Overflow

c# - Autofac DI container in console app - Code Review Stack Exchange

DI in .NET Core Console Application

Dependency injection in .NET Core console applications (gunnarpeipman.com)

TransactionScope & Transaction in EF6

Entity Framework (EF) TransactionScope vs Database.BeginTransaction (vunvulearadu.blogspot.com)

Unit Testing

c# - Full integration test for a Console application - Code Review Stack Exchange

How to unit test C# Dynamics CRM interface code (alexanderdevelopment.net)

jordimontana82/fake-xrm-easy: The testing framework for Dynamics CRM and Dynamics 365 which runs on an In-Memory context and deals with mocks or fakes for you (github.com)

Timeout (edit)

Session-State Modes

https://docs.microsoft.com/en-us/previous-versions/ms178586(v=vs.140)?redirectedfrom=MSDN

HttpSessionState.SessionID Property

https://docs.microsoft.com/en-us/dotnet/api/system.web.sessionstate.httpsessionstate.sessionid?view=netframework-4.5.2

Configuring Step 2: Configure ASP.NET Settings

https://docs.microsoft.com/en-us/iis/application-frameworks/scenario-build-an-aspnet-website-on-iis/configuring-step-2-configure-asp-net-settings

Tiếng Anh

ExpireTimeSpan & SlidingExpiration

CookieAuthenticationOptions.ExpireTimespan is the option that allows you to set how long the issued cookie is valid for. In the example above, the cookie is valid for 30 minutes from the time of creation. Once those 30 minutes are up the user will have to sign back in becuase the SlidingExpiration is set to false.

If SlidingExpiration is set to true then the cookie would be re-issued on any request half way through the ExpireTimeSpan. For example, if the user logged in and then made a second request 16 minutes later the cookie would be re-issued for another 30 minutes. If the user logged in and then made a second request 31 minutes later then the user would be prompted to log in.

Tiếng Việt

Thuộc tính cookieless: khi thuộc tình này có giá trị là true thì asp.net session state sẽ không sử dụng cookie để xử lý session.
Lúc đó thông tin sẽ được lưu trữ ngay trên url và asp.net sẽ tự thêm vào url bình thường của bạn những chuỗi ký tự mã hóa.
Ví dụ: http://localhost/(lit3py55t21z5v55vlm25s55)/Appliction/SessionTest.aspx

Thuộc tính timeout: chứa giá trị quy định thời gian time out của session (tính bằng phút).
Thuộc tính mode với giá trị "InProc": khi mode được set giá trị này thì chỉ có thuộc tính timeout có tác dụng, với thiết lập này session của asp.net sẽ làm việc giống asp cổ điển.
Ví dụ: <sessionState mode="InProc" timeout="20" />

Sự kiện window.onload

if (window.attachEvent) {window.attachEvent('onload', your_function);}
else if (window.addEventListener) {window.addEventListener('load', your_function, false);}
else {document.addEventListener('load', your_function, false);}

Learning

https://www.c-sharpcorner.com/article/state-management-in-asp-net-mvc/

https://www.c-sharpcorner.com/article/learn-about-state-management-in-asp-net-mvc/

Samples

https://www.dotnetspider.com/forum/326114-How-to-show-a-popup-warning-before-session-timeout-in-aspnet-mvc-3.aspx

https://stackoverflow.com/questions/1740230/how-do-i-warn-the-user-that-their-web-session-is-about-to-time-out