[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 917
  • Last Modified:

Gridview Custom Paging with SQL Server 2000

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
devo00
Asked:
devo00
  • 3
  • 2
1 Solution
 
nmarunCommented:
This is a duplicate question from:
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_24299766.html

Please close this ticket.
0
 
devo00Author Commented:
No, this is not a duplicate ticket. One is SQL Server 2000 and one is 2005.
0
 
nmarunCommented:
Have you looked at using a SqlDataSource instead?
http://msdn.microsoft.com/en-us/library/dz12d98w.aspx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Anurag ThakurCommented:
here are a few recommendation for sql server 2000
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33472&SearchTerms=paging
0
 
devo00Author Commented:
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
 
devo00Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now