.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ASP.NET Controls >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Paging Concept with SQLDataSource in SQL:

Posted By:Athira Sreejith       Posted Date: March 05, 2009    Points: 10    Category: ASP.NET Controls    URL: http://www.dotnetspark.com  
 

Paging Concept with SQLDataSource in SQL:

 


                       
                       


                                        BorderWidth="0px" CellPadding="3" EnableTheming="true" DataKeyNames="Id" Width="100%"
                                        SkinID="gridviewSkin" >


...


                                            No Datas

                                   
                                                                            SelectCommand="sp_getAdminInformation" SelectCommandType="StoredProcedure"
                                          ProviderName="System.Data.SqlClient">
                                       
                                           
                                       

                                   

                       
                       

 

.cs:

    private void BindGrid()
        {
            //gvAdminDetails.DataSource = null;
            //gvAdminDetails.DataBind();
            RememberOldValues();
            RePopulateValues();

            if (null != System.Web.HttpContext.Current.Cache[Admincount])
            {
                max = Convert.ToInt32(System.Web.HttpContext.Current.Cache[Admincount]);
            }
            else
            {
                System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
                conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["VetCareWeb"].ConnectionString;
                conn.Open();

                //DataSet ds = new DataSet();
                //SqlDataAdapter myCommand = new SqlDataAdapter("sp_getAdminInformation", conn);
                //myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
                //myCommand.Fill(ds);
                //gvAdminDetails.DataSource = ds;
                //gvAdminDetails.DataBind();
                //=========================================

                SqlDataReader rdr = null;
                SqlCommand cmd = new SqlCommand("sp_getAdminInfo_count", conn);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    tblInformation.Visible = true;
                    max = rdr.GetInt32(0);
                    System.Web.HttpContext.Current.Cache.Insert("Admincount", max.ToString(), null, DateTime.Now.AddMinutes(2), TimeSpan.Zero);
                }
            }
            if ((Request.QueryString["start"] == null) | (Request.QueryString["start"] == "0"))
            {
                status.Text = "
We found about " + max.ToString()
                              + " records
, now viewing 1 through 8.
";
                paging.Text = "<< prev | next >>";
            }
            else
            {
                int start = Convert.ToInt32(Request.QueryString["start"]) + 1;
                int next = Convert.ToInt32(Request.QueryString["start"]) + results;
                int prev = Convert.ToInt32(Request.QueryString["start"]) - results;

                if (next > max)
                {
                    status.Text = "
We found about " + max.ToString() + " records, now viewing " + start
                                    + "
through " + max.ToString() + ".
";
                    paging.Text = @"<< prev | next >>";
                }
                else
                {
                    status.Text = "
We found about " + max.ToString() + " records, now viewing "
                                  + start + "
through " + next + ".
";
                    paging.Text = @"<< prev | next >>";
                }
            }
            pagingtop.Text = paging.Text;
            statustop.Text = status.Text;

        }


SP:

ALTER PROCEDURE [dbo].[sp_getAdminInformation]
 -- Add the parameters for the stored procedure here
 @start int = 0
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 Set NOCOUNT ON

 SELECT TOP 8 * FROM
 (
 
  SELECT Id,InformationTo_Id,Information,InformedBy_Id,InformationDate,
  ROW_NUMBER() OVER (ORDER BY Information) as num
  from dbo.SFA_AdminInfoToEmp
 
 
 

 ) AS a
 WHERE num > @start

END

 

ALTER PROCEDURE [dbo].[sp_getAdminInfo_count]
 -- Add the parameters for the stored procedure here
 @start int = 0
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 Set NOCOUNT ON


SELECT count(*) AS count
    FROM dbo.SFA_AdminInfoToEmp


END



     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend