Monday, January 30, 2017

Pagination with OFFSET / FETCH : A better way

CREATE TABLE [dbo].[Customers]
(
  CustomerID bigint IDENTITY(1,1) NOT NULL,
  FirstName varchar(64) NOT NULL,
  LastName varchar(64) NOT NULL,
  EMail varchar(320) NOT NULL,
  Active bit NOT NULL DEFAULT ((1)),
  Created datetime NOT NULL DEFAULT (sysdatetime()),
  Updated datetime NULL,
  CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
insert into [dbo].[Customers]
(
  FirstName,
  LastName,
  EMail
)
Values('subrat','samal','ss@ss.com')
CREATE PROCEDURE dbo.usp_Test_WithCount
  @PageNumber INT = 1,
  @PageSize   INT = 100
AS
BEGIN
  SET NOCOUNT ON;
 
  ;WITH pg AS
  (
      SELECT CustomerID, 
             t = COUNT(*) OVER()
      FROM dbo.Customers
      ORDER BY CustomerID
      OFFSET @PageSize * (@PageNumber - 1) ROWS
      FETCH NEXT @PageSize ROWS ONLY
  )
  SELECT c.CustomerID, 
         c.FirstName,
         c.LastName,
         c.EMail, 
         c.Active, 
         c.Created, 
         c.Updated, 
         TotalRowCount = t
  FROM dbo.Customers AS c
  INNER JOIN pg ON pg.CustomerID = c.CustomerID
  ORDER BY c.CustomerID
  OPTION (RECOMPILE);
END
CREATE PROCEDURE dbo.usp_Test_WithCount
         @pageSize int,
  @page int,
  @sortBy int,
  @isAsc bit,
  @search varchar(50)
AS
;WITH CTEPG AS
   (
    SELECT [ProductId]
       ,[TotalRows] = COUNT(*) OVER()
    FROM [SubProjSchema].[Products] 
    WHERE ISNULL([IsDeleted], -1) = 0
       AND
       (
       (@Search IS NULL OR CAST(ProductId AS VARCHAR(50)) LIKE '%' + @Search + '%')
      OR (@Search IS NULL OR CAST(ProductName AS VARCHAR(50)) LIKE '%' + @Search + '%')
      OR (@Search IS NULL OR CAST(ISNULL(UnitPrice, 0) AS VARCHAR(50)) LIKE '%' + @Search + '%')
       )
    ORDER BY CASE WHEN @SortBy = 1 AND @IsAsc = 0 THEN [ProductId] END DESC,
       CASE WHEN @SortBy = 1 AND @IsAsc = 1 THEN [ProductId] END ASC,
       CASE WHEN @SortBy = 2 AND @IsAsc = 0 THEN [ProductName] END DESC,
       CASE WHEN @SortBy = 2 AND @IsAsc = 1 THEN [ProductName] END ASC,
       CASE WHEN @SortBy = 3 AND @IsAsc = 0 THEN [UnitPrice] END DESC,
       CASE WHEN @SortBy = 3 AND @IsAsc = 1 THEN [UnitPrice] END ASC
    OFFSET ((@Page - 1)  * @PageSize) ROWS
    FETCH NEXT @PageSize  ROWS ONLY
   )
   SELECT p.[ProductId]
      ,p.[ProductName]
      ,p.[UnitPrice]
      ,CTEPG.[TotalRows]
   FROM [SubProjSchema].[Products] AS p
   INNER JOIN CTEPG ON CTEPG.[ProductId] = p.[ProductId]
   ORDER BY CASE WHEN @SortBy = 1 AND @IsAsc = 0 THEN p.[ProductId] END DESC,
      CASE WHEN @SortBy = 1 AND @IsAsc = 1 THEN p.[ProductId] END ASC,
      CASE WHEN @SortBy = 2 AND @IsAsc = 0 THEN p.[ProductName] END DESC,
      CASE WHEN @SortBy = 2 AND @IsAsc = 1 THEN p.[ProductName] END ASC,
      CASE WHEN @SortBy = 3 AND @IsAsc = 0 THEN p.[UnitPrice] END DESC,
      CASE WHEN @SortBy = 3 AND @IsAsc = 1 THEN p.[UnitPrice] END ASC
   OPTION (RECOMPILE);
http://sqlfidd
le.com/#!6/fd831/1

Thursday, January 26, 2017

Sql Server - Split single row in multiple rows based on range

CREATE TABLE Table1 (id int, name varchar(10), start int, [end] int);
GO

INSERT INTO Table1 Values
(13,'sub1', 11, 15),
(15,'sub2', 17, 22),
(19,'sub3', 44, 44);



;WITH CTE 
AS 
(
  select id, name, start, [end] from Table1
  union all
  select id, name, start+1, [end]
  from CTE where [end] > start
)
select id, name, start as 'serial' From CTE order by start option (maxrecursion 0)



idnameserial
13sub111
13sub112
13sub113
13sub114
13sub115
15sub217
15sub218
15sub219
15sub220
15sub221
15sub222
19sub344

Wednesday, January 25, 2017

Tuples,Coalescing, Func - generic delegate in C#

Tuples in C#


 A tuple is a data structure that has a specific number and sequence of elements


Tuples are commonly used in four ways:
  1. To represent a single set of data. For example, a tuple can represent a database record, and its components can represent individual fields of the record.
  2. To provide easy access to, and manipulation of, a data set.
  3. To return multiple values from a method without using out parameters (in C#) or ByRefparameters (in Visual Basic).
  4. To pass multiple values to a method through a single parameter. For example, the Thread.Start(Object) method has a single parameter that lets you supply one value to the method that the thread executes at startup time. If you supply a Tuple<T1, T2, T3>object as the method argument, you can supply the thread’s startup routine with three items of data.

Coalescing operator ?? in C#


One of the subtle (but cool) language features of C# is the ?? "null coalescing" operator.  This provides a nice, terse way to check whether a value is null, and if so return an alternate value.

Simple Example Usages

Simply put, the ?? operator checks whether the value provided on the left side of the expression is null, and if so it returns an alternate value indicated by the right side of the expression.  If the value provided on the left side of the expression isn't null, then it returns the original value. 
For example, let's assume we have a string variable "message".  We could check whether message was null, and return an alternate value using the code below:
Because the "message" variable above wasn't null, the "result" variable is assigned the original "hello world" message value. 
In the code snippet below, however, message is a null value, and so the ?? operator will return the alternate value we've provided:
The ?? operator works for both reference types and value types.  For example, below we are checking whether the nullable integer "number" variable is null.  Because it isn't, the result will be the original value (55):
If "number" is null, then result is assigned the value 0:

Using the ?? operator with LINQ

Last month I wrote a blog post that covered using the new LINQ to XML support in .NET 3.5.  One of the "gotchas" you often need to deal with when handling raw XML are cases where XML shapes are irregular and/or missing elements/attributes.  The ?? operator can be very useful in these scenarios.
For example, let's consider a scenario where we have an XML file or feed with the following contact data:
We could write the below LINQ to XML code in C# to open the XML file and retrieve back a sequence of anonymous type objects with "Name", "Title", "Email" and "YearsAtCompany" properties, and then databind the results to an <asp:gridview> control on a page:
Notice above how I'm using the explicit conversion operator support on the XElement class to retrieve strongly typed values from the XML.  This enables me to just cast the c.Element("YearsAtCompany") value to an int - and it will then automatically convert the string value to an integer for me, and type the "YearsAtCompany" property on my anonymous type to be an int.
When we run the above code snippet, we'll then get a nice Gridview listing of our contacts:
 
This explicit conversion support works great when the <YearsAtCompany> element is always defined.  But it will throw a runtime error in the case where we have a <Contact> that is missing a <YearsAtCompany> sub-element:
One way to fix this is to modify our LINQ to XML query so that we indicate that YearsAtCompany is a nullable integer.  We can do this by changing the explicit cast to be (int?) instead of (int):
This enables our query to execute cleanly and not raise any errors.  Instead a null value will be assigned to the YearsAtCompany property if no <YearsAtCompany> element is present in the XML. 
When we run the application and databind the results to the Gridview, you can see the YearsAtCompany column for our third contact is empty as a result (since the value is null):
But what if we didn't want a missing XML value to result in a null integer value - but instead just indicate a value of 0? 
Well.... that is where we can use the new ?? operator support.  We can just modify the LINQ to XML query like below to indicate a default value of 0 if no element is present:
This indicates that if the <YearsAtCompany> element is missing in the XML, and the result would otherwise be null, instead assign a value of 0.  Notice in the intellisense above how C# automatically detects that this means that the YearsAtCompany property on the new anonymous type will never be null - and so it marks the property to be of type (int) instead of (int?). 
And now when we run the page we'll see a value of 0 show up in our third row instead of a blank value:

Func - generic delegate in C#


In this article, we will discuss about the concept of Func generic delegate. In C# 3.5, a generic delegate was provided with the keyword Func, which acts like a template for the user, to create and delegates. The general signatures of this delegate are  Func<T, TResult>.
Here, T is the input parameter for the method and TResult is the return parameter of the method that will be called using this delegate. This is just a single declaration. You can have the signatures like Func<Int32, Int32, Boolean> or Func<String, Int32, Int32, Boolean>. Here, the last parameter is always the result type and rest of them are the input parameter types of the methods, that can be encapsulated by these delegates.
So let's discuss this with a simple example. We will be creating a delegate which will take two input parameters of type integer, and result a boolean value, which specifies whether the first number is greater then second or not. So our generic delegate signatures will be Func<Int32, Int32, Boolean>. Then we will be creating a function which will be encapsulated by this delegate.
See the code below :

Next, to use this delegate, we will be assigning the method with the same signatures, to this delegate and call the delegate object and pass the required parameters to it. Check out the code below :

So this will return the true or false, based on your input parameters. Similarly, you can declare the delegate with more parameters, of the required type. If you try to go to the definition of this declared delegate, it will display the generic signatures of it, like below. By generic signature, here we mean that depending on the signature of the delegate we declared, its definition is displayed dynamically.

One more interesting point is the use of the anonymous methods or the lambda expression with this delegate. You could have avoided writing the method CheckNumbers, by using the following anonymous or lambda expression declarations.



Encrypt/Decrypt the App.Config

Program.cs using System; using System.Diagnostics; using System.IO; namespace EncryptAppConfig {     internal class Program     {         pr...