Joins in LINQ to SQL

The following post shows how to write different types of joins in LINQ to SQL. I am using the My Sample database and LINQ to SQL for these examples


SampleDbDataContext dataContext = new SampleDbDataContext();

Inner Join :-

var q1 = from b in dataContext.Blogs
join c in dataContext.Comments on b.BlogId equals c.BlogId
select new
{
b.BlogId,
b.BlogTitle,
b.BlogDescription,
c.CommentsId,
c.CommentDescription
};

Above LINQ In SQL

SELECT [b].[BlogId], [b].[BlogTitle], [b].[BlogDescription], [c].[CommentsId],[c].[CommentsDescription]
FROM [dbo].[Blogs] AS [b]
INNER JOIN [dbo].[Comments] AS [c] ON [b].[BlogId] = [c].[BlogId]

Left Join :-

var q2 = from b in dataContext.Blogs
join c in dataContext.Comments on b.BlogId equals c.BlogId into g
from s in g.DefaultIfEmpty()
select new
{
b.BlogId,
b.BlogTitle,
b.BlogDescription,
s.CommentsId,
s.CommentDescription
};

Above LINQ In SQL

SELECT [b].[BlogId], [b].[BlogTitle], [b].[BlogDescription], [c].[CommentsId],[c].[CommentsDescription]
FROM [dbo].[Blogs] AS [b]
LEFT OUTER JOIN [dbo].[Comments] AS [c] ON [b].[BlogId] = [c].[BlogId]

LinkedIn button on profile page

Something you may want to do, if you have a LinkedIn account (it's free!) is to add a button in your webpage or blog to make it easier for people to see your LinkedIn profile.

Here's how to find the button and code to paste into your site.

1. Go to your Profile page in LinkedIn
2. Click "My Profile"
3. Click on "Edit Public Profile Settings" (on the top right of the page)
4. Click "Promote your profile with customized buttons"
5. Pick a button, copy the code

or easier yet: http://www.linkedin.com/profile?promoteProfile=

Also, make sure your profile is not set to Private. Make it viewable to the world.

For users of Blogger, where to place the button.

In Blogger, you have to go to the Layout tab, click on "Add a Gadget" and add an "HTML/JavaScript" section where you paste the code. Title section is not mandatory.

How to find a url of Active tab from Active Internet Explore Instance

First Add new class name as IEInstance.cs

The code for this class



using System;
using System.Drawing;
using System.ComponentModel;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Text;
using System.Collections;
public delegate bool IECallBack(int hwnd, int lParam);
namespace IEToolbarEngine
{
    public class IEInstance//Form is a class in System.Windows.Forms namespace
    {
        [DllImport("user32.Dll")]
        public static extern int EnumWindows(IECallBack x, int y);
        [DllImport("User32.Dll")]
        public static extern void GetWindowText(int h, StringBuilder s, int nMaxCount);
        [DllImport("User32.Dll")]
        public static extern void GetClassName(int h, StringBuilder s, int nMaxCount);
        [DllImport("User32.Dll")]
        public static extern IntPtr PostMessage(IntPtr hWnd, int msg, int wParam, int lParam);

        public static ListBox lbPublic;
     
     
        private ListBox listBox1; //ListBox is a class in System.Windows.Forms namespace
        static IntPtr listBoxHandle;// IntPtr is a class in System namespace
        static IntPtr windowHandle;
        static StringBuilder sb, sbc;
        static int i = 0;
        static ArrayList myAl;

        public IEInstance() //Constructor
        {
            InitializeComponent();
        }

        #region Windows Form Designer generated code
        private void InitializeComponent()
        {
           // this.GetIE = new System.Windows.Forms.Button();
            this.listBox1 = new System.Windows.Forms.ListBox();
            myAl = new ArrayList();
        }
        #endregion

        public void GetIEButton()
        {
            listBoxHandle = listBox1.Handle;
            EnumWindows(new IECallBack(IEInstance.EnumWindowCallBack), (int)listBoxHandle);
       
        }

        private  static bool EnumWindowCallBack(int hwnd, int lParam)
        {
            windowHandle = (IntPtr)hwnd;
            listBoxHandle = (IntPtr)lParam;
            lbPublic = (ListBox)ListBox.FromHandle(listBoxHandle);
            sb = new StringBuilder(1024);
            sbc = new StringBuilder(256);
            GetClassName(hwnd, sbc, sbc.Capacity);
            GetWindowText((int)windowHandle, sb, sb.Capacity);
            int ind = sb.ToString().IndexOf("- Microsoft");
                if (ind ==-1)
                    ind = sb.ToString().IndexOf("- Windows");
            String xMsg2 = string.Empty;
            if (ind != -1)
                xMsg2 = sb.ToString().Substring(0,ind - 0) + " " + windowHandle;
            else
                xMsg2 = sb + " " + windowHandle;
            String xMsg = windowHandle.ToString();
            if (sbc.Length > 0)
            {
                if (sbc.ToString().Equals("IEFrame"))
                {
                    myAl.Add(windowHandle);
                    i++;
                    if (lbPublic.Items.Count < 1)
                        lbPublic.Items.Add(xMsg2);
                }
            }
            return true;
        }

   
    }
}

------
I have inherited this class with form class .In "lbPublic"  .This is a ListBox type . After executing this class your lbPublic will fill the active tab title + handler of the tab. You will just add this whole code in your class file ,no need to extent more.

Now , for the sample Add a form in which you will call the above class method GetIEButton();
In a form include a COM component reference MICROSOFT HTML OBJECT LIBRARY.

After adding reference include Add the below line the using area
using mshtml;

Now in the form Load method



private void Form1_Load(object sender, EventArgs e)
        {
       
        //// Below is the IEInstance class object
           IEInstance ieButton = new IEInstance();
            ieButton.GetIEButton();


            SHDocVw.ShellWindows shellWindows = new SHDocVw.ShellWindowsClass();
            string filename = string.Empty; ;

            foreach (SHDocVw.InternetExplorer ie in shellWindows)
            {
                filename = Path.GetFileNameWithoutExtension(ie.FullName).ToLower();
                mshtml.IHTMLDocument2 htmlDoc = ie.Document as mshtml.IHTMLDocument2;
                if (htmlDoc != null)
                {
                    string strIe = htmlDoc.title + "  " + ie.HWND.ToString();
                    //htmlDoc = (HTMLDocument)axWebBrowser1.Document;
                    if (filename.Equals("iexplore"))
                    {
                     
                            if (IEInstance.lbPublic.Items[0].ToString() == strIe)
                            {
                                filename = ie.LocationURL;

                                //  break;
                                goto NewLine;
                            }

                     
                    }
                }

                //Console.WriteLine( &quot;Web Site   : {0}", ie.LocationURL );

                if (filename.Equals("explorer&quot;"))
                    Console.WriteLine("Hard Drive : {0}", ie.LocationURL);
            }

        NewLine:
            int i2 = 0;
            MessageBox.Show(filename);

       }


This is in working state .now you will get thr url of active tab of active Internet Explorer .

START WITH and CONNECT BY in Oracle SQL

One way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is:

create table tblActivity (

  id         number primary key, 

 parent_id  references tblActivity, -- references itself

  descr      varchar2(20),

 

);

This table is now filled with some values:

-- 1st level:

insert into tblActivity values ( 1, null, 'Yahoo'         , 1);

-- 2nd level:

insert into tblActivity values ( 2,    1, 'Entertainment' , 1);

insert into tblActivity values ( 3,    1, 'Science'       , 1);

insert into tblActivity values ( 4,    1, 'Social Science', 1);

-- 3rd level (below Entertainment)

insert into tblActivity values ( 5,    2, 'Awards'        , 1);

insert into tblActivity values ( 6,    2, 'Comedy'        , 1);

insert into tblActivity values ( 7,    2, 'Humor'         , 0);

insert into tblActivity values ( 8,    2, 'Magic'         , 1);

-- 4th level (below Humor)

insert into tblActivity values (19,    7, 'Jokes'         , 1);

insert into tblActivity values (20,    7, 'Advice'        , 1);

insert into tblActivity values (21,    7, 'Parody'        , 1);

-- 4th level (below Magic)

insert into tblActivity values (16,    8, 'Card Tricks'   , 1);

insert into tblActivity values (17,    8, 'Magazines'     , 1);

insert into tblActivity values (18,    8, 'Organizations' , 1);

-- 3rd level (below Science)

insert into tblActivity values ( 9,    3, 'Astronomy'     , 1);

insert into tblActivity values (10,    3, 'Biology'       , 1);

insert into tblActivity values (11,    3, 'Geography'     , 1);

insert into tblActivity values (12,    3, 'Physics'       , 1);

insert into tblActivity values (13,    3, 'Research'      , 1);

-- 4th level (below Astronomy)

insert into tblActivity values (22,    9, 'Galaxies'     , 1);

insert into tblActivity values (23,    9, 'Pictures'     , 1);

insert into tblActivity values (24,    9, 'Stars'        , 1);

-- 4th level (below Physics)

insert into tblActivity values (25,   12, 'Electricity'  , 1);

insert into tblActivity values (26,   12, 'Mechanics'    , 1);

-- 3rd level (below Social Science)

insert into tblActivity values (14,    4, 'Psychology'   , 1);

insert into tblActivity values (15,    4, 'Sociology'    , 1);

-- 4th level (below Psychology)

insert into tblActivity values (27,   14, 'Conferences'  , 1);

insert into tblActivity values (28,   14, 'Intelligence' , 1);

--Now Select 

select * from   tblActivity 

start with id = 3
connect by prior id = parent_id; 

It return the below mention record

Science
    Astronomy
      Galaxies
      Pictures
      Stars
    Biology
    Geography
    Physics
      Electricity
      Mechanics
    Research

Asp.net Datalist item fetches using javascript‏

string scr = @"<script>
function Done()
{

var Hdnval;
var HdnControl;
var foo = document.getElementById('dtlImages');
var inps = foo.getElementsByTagName('input');
var inpsmage = foo.getElementsByTagName('img');
for(var i=0;i<inps.length;i++){
if(inps[i].type=='checkbox')
{
if (inps[i].checked== true)
{
HdnControl = inpsmage[i];
Hdnval = inpsmage[i].src;
}
}}
var fName=Hdnval;
var ret= new Array(fName,HdnControl);
//SetPreviewElements(HdnControl,HdnControl)
window.opener.update(ret);
window.close();
}
</script>;";
ClientScript.RegisterClientScriptBlock(this.GetType(), "done", scr);

Dynamic SQL Query with Sql Paging‏

Create PROC DBO.Article_SelectByCriteria_BySqlPaging

-- Parameters of Article_SelectByCriteria-------------- 
  @P_GUID                     VARCHAR(40)='' , -- OPTIONAL  
  @P_parentArticleId          INT = 0 ,  --OPTIONAL  
  @P_postingURL               nVARCHAR(200) = '' , --OPTIONAL 
  @P_authorId                 INT  = 0, --OPTIONAL 
  @P_elaphObjectId            INT =0,  --OPTIONAL 
  @P_assignedBy     BIGINT=0, --OPTIONAL 
  @P_assignedTo               BIGINT= 0 , --OPTIONAL 
  @P_assignedDate             VARCHAR(50) = '12:00:00 AM' , --OPTIONAL      
  @P_publishedBy              BIGINT = 0, -- OPTIONAL  
  @P_publishedDate            VARCHAR(50) = '12:00:00 AM' ,-- OPTIONAL  
  @P_lastModifiedBY           BIGINT = 0 , -- OPTIONAL 
  @P_lastModifiedDate        VARCHAR(50) = '12:00:00 AM' , --OPTIONAL 
  @P_archivedBy              BIGINT = 0, --OPTIONAL 
  @P_archivedDate            VARCHAR(50) = '12:00:00 AM' , --OPTIONAL 
  @P_expirationDate          VARCHAR(50) = '12:00:00 AM' , --OPTIONAL 
  @P_CountryId               TINYINT  = 0 , --OPTIONAL 
  @P_IsSectionPageNewsTicker BIT = 0 , --OPTIONAL 
  @P_isHomePageNewsTicker    BIT = 0 , --OPTIONAL 
  @P_isPrinterFriendly       BIT = 0 , --OPTIONAL 
  @P_isSendToFriend          BIT = 0 , --OPTIONAL 
  @P_isAddToFavorite         BIT = 0, --OPTIONAL 
  @P_isWriteToAuthor         BIT = 0 , --OPTIONAL 
  @P_isSubscribedArticle     BIT = 0 , --OPTIONAL 
  @P_isDisablePrintCutPaste  BIT = 0, --OPTIONAL 
  @P_IsCopyRight             BIT = 0 , --OPTIONAL  
  @P_IsArticleRating         BIT = 0 , --OPTIONAL 
  @P_IsComments              BIT = 0 , --OPTIONAL 
  @P_articleStatusId         INT = 0, --OPTIONAL 
  @P_articleTypeId           TINYINT = 0 ,--OPTIONAL 
  @P_createdBy               BIGINT  = 0 , --OPTIONAL 


@PageSize int, @PageNumber int

WITH RECOMPILE 

AS
SET NOCOUNT ON
  DECLARE
@STMT nvarchar(max),    -- SQL statement to execute
@ubound int

Declare @RowStart int
Declare @RowEnd int


if @PageNumber > 0
Begin

SET @PageNumber = @PageNumber -1

SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
 --IF @start < 1 SET @start = 1
  --IF @maxct < 1 SET @maxct = 1
 -- SET @ubound = @start + @maxct


DECLARE @L_WhereClause VARCHAR(8000) 
DECLARE @L_SQL Varchar(8000) 
SET @L_WhereClause = '' 
SET @L_SQL = '' 


 
 If @P_parentArticleId <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ParentArticleId =' +  CAST(@P_parentArticleId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ParentArticleId =' +   CAST(@P_parentArticleId AS VARCHAR(20))   
            END 
    
    IF @P_GUID  <> '' 
             BEGIN  
    IF  @L_WhereClause <> ''  
                     SET  @L_WhereClause = @L_WhereClause + ' AND Article.GUID =' + ''''+  @P_GUID + '''' 
                  ELSE 
                     SET  @L_WhereClause = @L_WhereClause + ' Article.GUID =' + ''''+  @P_GUID + '''' 
      END 
    
    
    IF @P_postingURL  <> '' 
             BEGIN  
    IF  @L_WhereClause <> ''  
                     SET  @L_WhereClause = @L_WhereClause + ' AND Article.PostingURL =' + ''''+  @P_postingURL + '''' 
                  ELSE 
                     SET  @L_WhereClause = @L_WhereClause + ' Article.PostingURL =' + ''''+  @P_postingURL + '''' 
      END 
    
    If @P_authorId <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.AuthorId =' +  CAST(@P_authorId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.AuthorId =' +   CAST(@P_authorId AS VARCHAR(20))   
            END 
      If @P_elaphObjectId <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ElaphObjectId =' +  CAST(@P_elaphObjectId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ElaphObjectId =' +   CAST(@P_elaphObjectId AS VARCHAR(20))   
            END 
  If @P_assignedBy <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.AssignedBy =' +  CAST(@P_assignedBy AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.AssignedBy =' +   CAST(@P_assignedBy AS VARCHAR(20))   
            END 
  If @P_assignedTo <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.AssignedTo =' +  CAST(@P_assignedTo AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.AssignedTo =' +   CAST(@P_assignedTo AS VARCHAR(20))   
            END 
 
       If @P_assignedDate <> '12:00:00 AM'   
              
      BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.AssignedDate =' + '''' + @P_assignedDate  + '''' 
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.AssignedDate =' +   '''' + @P_assignedDate + '''' 
            END 
 
        If @P_publishedBy <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.PublishedBy =' +  CAST(@P_publishedBy AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.PublishedBy =' +   CAST(@P_publishedBy AS VARCHAR(20))   
            END 
     -- If @P_publishedDate <> '12:00:00 AM'   
              
  --    BEGIN  
           --      IF  @L_WhereClause <> ''  
 
  --      SET  @L_WhereClause = @L_WhereClause + ' AND Article.PublishedDate =' +  '''' + @P_publishedDate + '''' 
               --  ELSE 
        --       SET  @L_WhereClause = @L_WhereClause + ' Article.PublishedDate =' +   '''' + @P_publishedDate + '''' 
          --  END 
              
  If @P_lastModifiedBY <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedBY =' +  CAST(@P_lastModifiedBY AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.LastModifiedBY =' +   CAST(@P_lastModifiedBY AS VARCHAR(20))   
            END   
         If @P_lastModifiedDate <> '12:00:00 AM'   
              
      BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedDate =' + '''' + @P_lastModifiedDate + '''' 
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.LastModifiedDate =' +   '''' + @P_lastModifiedDate + '''' 
            END      
      If @P_archivedBy <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedBy =' +  CAST(@P_archivedBy AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ArchivedBy =' +   CAST(@P_archivedBy AS VARCHAR(20))   
            END      
  
          If @P_archivedDate <> '12:00:00 AM'   
              
      BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedDate =' +  '''' + @P_archivedDate + '''' 
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ArchivedDate =' +   '''' + @P_archivedDate + '''' 
            END  
  
         If @P_expirationDate <> '12:00:00 AM'   
          
      BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ExpirationDate =' +  '''' + @P_expirationDate + '''' 
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ExpirationDate =' +   '''' + @P_expirationDate + '''' 
            END  
  
        If @P_CountryId <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.CountryId =' +  CAST(@P_CountryId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.CountryId =' +   CAST(@P_CountryId AS VARCHAR(20))   
            END  
   
        If @P_IsSectionPageNewsTicker <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsSectionPageNewsTicker =' +  CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsSectionPageNewsTicker =' +   CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))   
            END  
 
        If @P_isHomePageNewsTicker <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsHomePageNewsTicker =' +  CAST(@P_isHomePageNewsTicker AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsHomePageNewsTicker =' +   CAST(@P_isHomePageNewsTicker AS VARCHAR(20))   
            END 
 
      IF @P_isPrinterFriendly <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.isPrinterFriendly =' +  CAST(@P_isPrinterFriendly AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.isPrinterFriendly =' +   CAST(@P_isPrinterFriendly AS VARCHAR(20))   
            END 
   
  IF @P_isSendToFriend <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.isSendToFriend =' +  CAST(@P_isSendToFriend AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.isSendToFriend =' +   CAST(@P_isSendToFriend AS VARCHAR(20))   
            END 
   
         IF @P_isAddToFavorite <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsAddToFavorite =' +  CAST(@P_isAddToFavorite AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsAddToFavorite =' +   CAST(@P_isAddToFavorite AS VARCHAR(20))   
            END 
   IF @P_isWriteToAuthor <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsWriteToAuthor =' +  CAST(@P_isWriteToAuthor AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsWriteToAuthor =' +   CAST(@P_isWriteToAuthor AS VARCHAR(20))   
            END 
   IF @P_isSubscribedArticle <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsSubscribedArticle =' +  CAST(@P_isSubscribedArticle AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsSubscribedArticle =' +   CAST(@P_isSubscribedArticle AS VARCHAR(20))   
            END 
   
  IF @P_isDisablePrintCutPaste <> 0 
              
     BEGIN   
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsDisablePrintCutPaste =' +  CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsDisablePrintCutPaste =' +   CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))   
            END 
  IF @P_IsCopyRight <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsCopyRight =' +  CAST(@P_IsCopyRight AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsCopyRight =' +   CAST(@P_IsCopyRight AS VARCHAR(20))   
            END 
  IF @P_IsArticleRating <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsArticleRating =' +  CAST(@P_IsArticleRating AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsArticleRating =' +   CAST(@P_IsArticleRating AS VARCHAR(20))   
            END 
  IF @P_IsComments <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsComments =' +  CAST(@P_IsComments AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsComments =' +   CAST(@P_IsComments AS VARCHAR(20))   
            END 
       
  
        
 
          IF @P_articleStatusId <> 0 
              
     BEGIN  
                 IF @P_articleStatusId = 1 
                    BEGIN 
               IF  @L_WhereClause <> ''  
    
          SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' +  CAST(@P_articleStatusId AS VARCHAR(20)) + '  AND Article.PublishedDate <= ' +  '''' + CAST(getdate() AS VARCHAR(50)) + '''' +  '  AND Article.expirationDate > ' + '''' +
CAST(getdate() AS VARCHAR(50)) + ''''  
                    ELSE 
                 SET  @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' +   CAST(@P_articleStatusId AS VARCHAR(20))  + '  AND Article.PublishedDate <= ' +  '''' + CAST(getdate() AS VARCHAR(50)) + '''' +  '  AND Article.expirationDate > ' + '''' + CAST(getdate() AS VARCHAR(50)) + ''''
               
                    END 
     ELSE 
     BEGIN 
    IF  @L_WhereClause <> ''  
  
         SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' +  CAST(@P_articleStatusId AS VARCHAR(20))  
                  ELSE 
                SET  @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' +   CAST(@P_articleStatusId AS VARCHAR(20))   
             END  
    END 
                 
  
 
 IF @P_articleTypeId <> 0 
              
     BEGIN   
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArticleTypeId =' +  CAST(@P_articleTypeId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ArticleTypeId =' +   CAST(@P_articleTypeId AS VARCHAR(20))   
            END  
 
      IF @P_createdBy <> 0 
              
     BEGIN   
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.CreatedBy =' +  CAST(@P_createdBy AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.CreatedBy =' +   CAST(@P_createdBy AS VARCHAR(20))   
            END  
   
      IF  @L_WhereClause = ''  
 
        SET  @L_WhereClause = 'where  row >= ' + CONVERT(varchar(9), @RowStart ) + '  AND
                row <=  ' + CONVERT(varchar(9),@RowEnd)
  
 




SET @STMT = ' SELECT TOP 500 *
                FROM (
                      SELECT  ROW_NUMBER() OVER(ORDER BY PublishedDate Desc) AS row, ArticleId, PublishedDate
                      FROM    Article
                     ) AS tbl
                '+@L_WhereClause+' '
               
  EXEC (@STMT)
 
 
 end

 
GO

//// Now You can execute the SP
Article_SelectByCriteria_BySqlPaging
 @PageSize = 15,
 @PageNumber = 11