Paging in SQL Server

CREATE proc P_GET_PAGE

(

@p_page int,

@p_page_size int

)

as

begin

set nocount on

declare @total_rows int

declare @first_row int

declare @first_row_id int

select @total_rows = count(Num) from Users

select @first_row = (@p_page - 1) * @p_page_size + 1

if (@first_row <= @total_rows)

begin

set rowcount @first_row

select @first_row_id = Num

from Users

order by 1

set rowcount @p_page_size

select * from Users

where Num >= @first_row_id

order by 1

end

set nocount off

end

exec P_GET_PAGE 10, 10



DECLARE @PageNum AS INT;

DECLARE @PageSize AS INT;

SET @PageNum = 1;

SET @PageSize = 10;

WITH OrdersRN AS

(

SELECT ROW_NUMBER() OVER(ORDER BY Num) AS RowNum,

 UserID,

 UserName

FROM Users

)

SELECT UserID, UserName

FROM OrdersRN

WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1

AND @PageNum * @PageSize

ORDER BY Num

Getting Public Key Token of Assembly Within Visual Studio

In Visual Studio, go to the Tools menu and choose External Tools.




That brings up a new dialog window. Click the Add button and add the following:

Title: Get &PublicKeyToken

Command: C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\sn.exe

Arguments: -Tp $(TargetPath)

Check the Use Output Window checkbox. The final looks like this:



Create a project and sign it using a strong name key. Then choose Tools / Get PublicKeyToken, and in the output window you will see something like the following:

Microsoft (R) .NET Framework Strong Name Utility  Version 3.5.21022.8
Copyright (c) Microsoft Corporation.  All rights reserved.

Public key is
0024000004800000940000000602000000240000525341310004000001000100d59451ff2ed310
447372c4d689f24dcece5aaaef6dddaffc3e43c36a9235586b33ec9e3121ad844ee521bd76fbc0
9a9a357bfeec32d87d8cd1278cd7697667263724e6ff1712e5ee3054542cfbb11b9241da118fbd
c1df7439ba13db77b63f8bf557c7f081946c02e32884c82806e0e95667e879d15b9a2912012398
76e0efa7

Public key token is 9589fa1be527eb6c

 9589fa1be527eb6c is the Public Key Token

Referencing assemblies from folder other than /bin

Add the below tag in web.config file of your web application

 <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="CoreFeature"
                          publicKeyToken="5e3a2279d3663dea"
                          culture="neutral" />
        <codeBase  version="1.0.0.0" href="file:///F:/Test/References/CoreFeature.dll"/>
            </dependentAssembly>
    </assemblyBinding>
  </runtime>

While Statement in T-SQL

DECLARE @intFlag INT

DECLARE @endFlag INT

SET @intFlag = 1

SET @endFlag = 10

WHILE (@intFlag < = @endFlag)

BEGIN

PRINT @intFlag

SET @intFlag = @intFlag + 1

END

GO

Restoring Database

USE [master]

ALTER DATABASE [Employee]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


RESTORE DATABASE [Employee] FROM

DISK = N'F:\Employee_Aug_08_2011.bak'

WITH FILE = 1, MOVE N 'Employee_Prod'

TO N'F:\Microsoft SQL Server Database Files\MSSQL10_50.TSTIM\MSSQL\DATA\Employee.mdf',

MOVE N'eSubmission_Prod_log' TO N'F:\Microsoft SQL Server Database Files\MSSQL10_50.TSTIM\MSSQL\DATA\Employee.ldf',

NOUNLOAD, REPLACE, STATS = 10

GO

USE [Employee]

ALTER DATABASE [Employee]

SET MULTI_USER;

Creating C# Properties with SQL Server Table Schema

declare @TableName sysname = 'WAccessRequests'

declare @result varchar(max) = ''



select @result = @result + '

public ' + ColumnType + ' ' + ColumnName + ' { get; set; }

'

from

(

select replace(col.name, ' ', '_') ColumnName,

case typ.name

when 'bigint' then 'long'

when 'binary' then 'byte[]'

when 'bit' then 'bool'

when 'char' then 'char'

when 'date' then 'DateTime'

when 'datetime' then 'DateTime'

when 'datetime2' then 'DateTime'

when 'datetimeoffset' then 'DateTimeOffset'

when 'decimal' then 'decimal'

when 'float' then 'float'

when 'image' then 'byte[]'

when 'int' then 'int'

when 'money' then 'decimal'

when 'nchar' then 'char'

when 'ntext' then 'string'

when 'numeric' then 'decimal'

when 'nvarchar' then 'string'

when 'real' then 'double'

when 'smalldatetime' then 'DateTime'

when 'smallint' then 'short'

when 'smallmoney' then 'decimal'

when 'text' then 'string'

when 'time' then 'TimeSpan'

when 'timestamp' then 'DateTime'

when 'tinyint' then 'byte'

when 'uniqueidentifier' then 'Guid'

when 'varbinary' then 'byte[]'

when 'varchar' then 'string'

end ColumnType

from sys.columns col

join sys.types typ on

col.system_type_id = typ.system_type_id

where object_id = object_id(@TableName)

)
t



print @result




Output



(17 row(s) affected)



public short AccessRequestID { get; set; }

public short UserCreated { get; set; }

public short UserModified { get; set; }

public int SourceContactID { get; set; }

public DateTime IndividualDateOfBirth { get; set; }

public DateTime RegistrationDate { get; set; }

public DateTime DateCreated { get; set; }

public DateTime DateModified { get; set; }

public bool isRegistered { get; set; }

public bool ValidRequest { get; set; }

public string Num { get; set; }

public string IndividualName { get; set; }

public string ValidNumber { get; set; }

public string IndividualJobTitle { get; set; }

public string IndividualNationality { get; set; }

public string IndividualEmailAddress { get; set; }

public string RegistrationPassword { get; set; }

Using COALESCE to Build Comma-Delimited String

declare @CategoryList varchar(1000)
select @CategoryList = coalesce(@CategoryList + ', ', '') + IndividualEmailAddress from dbo.WAccessRequests
select Results = + @CategoryList

Paging in SQL Server

CREATE proc P_GET_PAGE

(

   @p_page int,

   @p_page_size int

)

as

begin

   set nocount on

   declare @total_rows int

   declare @first_row int

   declare @first_row_id int

   select @total_rows = count(Num) from WFirms

   select @first_row = (@p_page - 1) * @p_page_size + 1

   if (@first_row <= @total_rows)

   begin

      set rowcount @first_row

      select @first_row_id = Num

      from WFirms

      order by 1

      set rowcount @p_page_size

      select * from WFirms

      where Num >= @first_row_id 

      order by 1    

   end

   set nocount off

end

exec P_GET_PAGE 10, 10

DECLARE @PageNum AS INT;

DECLARE @PageSize AS INT;

SET @PageNum = 1;

SET @PageSize = 10;

WITH OrdersRN AS

(

    SELECT ROW_NUMBER() OVER(ORDER BY Num) AS RowNum,

         Firmname,

         Num

      FROM Wfirms

)

SELECT Num, FirmName

  FROM OrdersRN

 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1

                  AND @PageNum * @PageSize

 ORDER BY Num

Invalid object name = 'tempdb.dbo.ASPStateTempApplications'.

Execute the below script to solve the error : Invalid object name = 'tempdb.dbo.ASPStateTempApplications'.


USE [ASPState]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[CreateTempTables]

SELECT 'Return Value' = @return_value

GO