@manhnguyenv

Welcome to my blog!

Searching Paging in WinForms with Stored Procedure

November 14, 2017 00:57

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

Implement Paging DataGridView in Windows Forms (WinForms) Application using C# and VB.Net

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

Custom Paging in ASP.Net DataGrid using SQL server Stored procedure

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

November 10, 2017 19:18

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

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

Categories

Recent posts