Solved

Gridview Custom Paging with SQL Server 2000

Posted on 2009-04-06
6
908 Views
Last Modified: 2012-05-06
I need to know how to setup custom sorting / paging with gridviews for large datasets with .NET 2.0, C# and SQL Server 2000. My current method for general SQL SELECT queries is below and does not have anything I need for custom paging, but I'd like to adapt what I have to work.

I am new to n-tier (presentation / BLL / DAL) and am not using that in this example unless it'd be easier to do.


<asp:ObjectDataSource ID="ObjDataSource1" runat="server" TypeName="SomeClass" SelectMethod="rtnDataSet">

        <SelectParameters>

            <asp:Parameter DefaultValue="Select * from employees" Name="_sqlStr" />

        </SelectParameters>

    </asp:ObjectDataSource> 
 
 

    public DataSet rtnDataSet(string _sqlStr)

    {

        DataSet _rtnDS = new DataSet();

        dataRetrieval.fillDataSet(ref _rtnDS, ref _sqlStr);

	return _rtnDS;

    }
 
 

    public static void fillDataSet(ref DataSet _dataSet, ref string _sqlStr)

    {

            SqlConnection _sqlCon = connectToSomeDB();

            SqlCommand _sqlCmd = new SqlCommand();
 

            _sqlCmd.Connection = _sqlCon;

            _sqlCmd.CommandType = CommandType.Text;

            _sqlCmd.CommandTimeout = 30;

            _sqlCmd.CommandText = _sqlStr;
 

            SqlDataAdapter _sqlAdptr = new SqlDataAdapter(_sqlCmd);

            _sqlAdptr.Fill(_dataSet);
 

            _sqlAdptr.Dispose();

            _sqlCmd.Dispose();

            _sqlCon.Close();

    }

Open in new window

0
Comment
Question by:devo00
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:nmarun
ID: 24081661
This is a duplicate question from:
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_24299766.html

Please close this ticket.
0
 

Author Comment

by:devo00
ID: 24083440
No, this is not a duplicate ticket. One is SQL Server 2000 and one is 2005.
0
 
LVL 27

Expert Comment

by:nmarun
ID: 24083937
Have you looked at using a SqlDataSource instead?
http://msdn.microsoft.com/en-us/library/dz12d98w.aspx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24084177
here are a few recommendation for sql server 2000
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33472&SearchTerms=paging
0
 

Author Comment

by:devo00
ID: 24086967
nmarun: When I say custom sorting / paging, I'm speaking of large datasets being paged and sorted. With the SQLDataSource, every time you sort or page, the entire resultset is returned and a portion is displayed or re-ordered. For this you need the ObjectDataSource. This is definitely not a help to get the built-in .NET sorting and paging functions inherent to gridviews and SqlDataSources.
-I need to only  a specific 10-15 record 'section' of the resultset returned for paging.
I've been through all the online versions and mostlyfind things that don't apply using the method I have above, only a way to start from scratch.
I need a simple step by step to change what I have to get custom sorting / paging working. For anyone that has done this before, I would think this would not be too difficult.
0
 

Accepted Solution

by:
devo00 earned 0 total points
ID: 24101560
This answered my question for Custom Paging and I have it working now (procedure at bottom):  http://www.4guysfromrolla.com/webtech/042606-1.shtml

---------------------------

Gridview and ObjectDataSource:

    <asp:ObjectDataSource ID="o1" runat="server" 

    TypeName="TestTier" 

    SelectMethod="BindUsers" 

    SelectCountMethod="GetOrderCount"

    StartRowIndexParameterName="startRowIndex" 

    MaximumRowsParameterName="maximumRows"

    EnablePaging="true" 

    ></asp:ObjectDataSource>
 

<asp:GridView runat="server" ID="gv3" DataSourceID="o1" AllowPaging="true" PageSize="15" AllowSorting="true"></asp:GridView>
 

---------------------------

Stored Procedures:

CREATE  PROCEDURE [dbo].[GetTestData] 

(

    @startRowIndex int,

    @maximumRows int

)

AS
 

DECLARE @first_id int, @startRow int

	

SET ROWCOUNT @startRowIndex

SELECT @first_id = OrderID FROM [Order Details] ORDER BY OrderId
 

SET ROWCOUNT @maximumRows

Select * from [Order Details] where OrderId >= @first_id

 

SET ROWCOUNT 0

GO
 

CREATE PROCEDURE dbo.GetOrderCount

AS

    SELECT  count(*)  FROM [Order Details]

GO
 

---------------------------

Data Access Class:

public class TestTier

{
 

    public DataSet BindUsers(int startRowIndex, int maximumRows, string sortBy)

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["testcon_north"].ConnectionString);        

        SqlCommand com = new SqlCommand("GetTestData", con);

        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@startRowIndex", SqlDbType.Int, 4).Value = startRowIndex;

        com.Parameters.Add("@maximumRows", SqlDbType.Int, 4).Value = maximumRows;

        com.Parameters.Add("@sortBy",SqlDbType.VarChar).Value = sortBy;

        SqlDataAdapter ada = new SqlDataAdapter(com);

        DataSet ds = new DataSet();

        ada.Fill(ds);

        return ds;   

    }
 

    public int GetOrderCount()

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["testcon_north"].ConnectionString);

        con.Open();

        SqlCommand com = new SqlCommand("GetOrderCount", con);

        SqlDataReader dr = com.ExecuteReader();

        int count = 0;

        while (dr.Read())

        {

            if (dr[0] != null)

                int.TryParse(dr[0].ToString(), out count);           

        }

        return count;

    }
 
 

}

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Change in PHP Behavior with Session Write Short Circuit (http://php.net/manual/en/book.session.php#116217) (Winter 2014)** With the release of PHP 5.6 the session handler changed in a way that many think should be considered a bug.  See the note …
If I have to fix slow responding website my first thoughts are server side optimizations: the database may not be optimized or caching is not enabled, or things like that. We often overlook another major part of our web application: the client. We o…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now