using System;

using System.Data;  

using System.Data.SqlClient;

using System.Text;

public class Program

{

    public static void Main()

    {

        Console.WriteLine("Hello World");

    }

    public static string ConnectionString;

    public static bool FetchEnabled{

        get{

            try

            {

                Select("SELECT GETDATE() ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY");

                return true;

            }

            catch (SqlException ex)

            {

                return  !(ex.Message.Contains("OFFSET") && ex.Message.Contains("NEXT") && ex.Message.Contains("FETCH"));

            }

        }

    }

    private static void Select(string q){

    }

    private static DataTable Select(SqlCommand cmd, int RowsPerPage, int Page, string OrderBy)

    {

        SqlConnection connection = new SqlConnection();

        try

        {

            using (connection = new SqlConnection { ConnectionString = ConnectionString })

            {

                connection.Open();

                if (RowsPerPage > 0 && Page >= 0)

                {

                    string _orderby = "(SELECT NULL)";

                    if (!string.IsNullOrEmpty(OrderBy))

                    {

                        _orderby = OrderBy;

                    }

                    if (FetchEnabled)

                    {

​

                        cmd.CommandText += $" \n ORDER BY {_orderby} \nOFFSET {RowsPerPage * Page} ROWS FETCH NEXT {RowsPerPage} ROWS ONLY";

                    }

                    else

                    {

                        if (cmd.CommandText.Substring(0, 20).ToLower().Contains("with "))

                            throw new Exception("With içeren sorgularda otomatik sayfalama yapamazsınız kodunuzu rownumber ve parametre ekleyerek güncelleyiniz");

                        string rn = "rn_" + Guid.NewGuid().ToString("N");

​

                        StringBuilder sb = new StringBuilder($"SELECT * FROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY {_orderby})[{rn}] FROM(");

                        sb.AppendLine(cmd.CommandText);

                        sb.AppendLine($")Q)Q WHERE [{rn}]>{RowsPerPage * Page} AND [{rn}]<={RowsPerPage * (Page + 1)}");

​

                        sb.AppendLine($"ORDER BY {_orderby}");

                        cmd.CommandText = sb.ToString();

                    }

                }

                var adapter = new SqlDataAdapter(cmd);

                var dataTable = new DataSet();

                adapter.Fill(dataTable);

                connection.Close();

                switch (dataTable.Tables.Count)

                {

                    case 0: return null;

                    case 1: return dataTable.Tables[0];

                    default:

                        throw new Exception("Yapılan sorgu, birden fazla tablo dönderiyor. Bu sorgu için, Multi-- Methodunu kullanmanız gerekmektedir.");

                }

            }

        }

        catch (Exception ex)

        {

            throw ex;

        }

        finally

        {

            if (connection.State != ConnectionState.Closed)

                connection.Close();

        }

    }

}