locked
How to compatible datagridview on demand data loading with stored procedure instead of in-line sql RRS feed

  • Question

  • User264732274 posted

    I like to refer a code which i have taken from this url

    https://msdn.microsoft.com/en-us/library/ms171624%28v=vs.110%29.aspx

    The above url shown how we can load data by

    datagridview

    just on demand means when user scroll and reach a limit then data will load from db. My code is working but now i am in situation that i have to use a stored procedure from SQL Server which is very big and it return data after so many calculation. So I have to customize my code as a result it should be working with stored procedure instead of in-line SQL. From the above link anyone can see full working sample code. I just here highlight 3 routine from where in-line SQL fired to fetch data from db and this routine I need to change and want to call stored procedure from there instead of in-line SQL.

    From this one route use in-line sql:

    public int RowCount
    {
                get
                {
                    // Return the existing value if it has already been determined. 
                    if (rowCountValue != -1)
                    {
                        return rowCountValue;
                    }
    
                    if (filters.Trim().ToUpper().IndexOf("WHERE") > -1)
                    {
                        filters = filters.ToUpper().Replace("WHERE", string.Empty);
                    }
    
                    // Retrieve the row count from the database.
                    command.CommandText = "SELECT COUNT(*) FROM " + tableName + " WHERE 1=1 " + (filters.Trim().Length > 0 ? " AND " : string.Empty) + filters;
                    rowCountValue = (int)command.ExecuteScalar();
                    return rowCountValue;
                }
    }

    This routine too uses in-line SQL:

    public DataColumnCollection Columns
    {
                get
                {
                    // Return the existing value if it has already been determined. 
                    if (columnsValue != null)
                    {
                        return columnsValue;
                    }
    
                    // Retrieve the column information from the database.
                    command.CommandText = "SELECT * FROM " + tableName;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    DataTable table = new DataTable();
                    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                    adapter.FillSchema(table, SchemaType.Source);
                    columnsValue = table.Columns;
                    return columnsValue;
                }
    }

    This routine too

    public DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage)
    {
                // Store the name of the ID column. This column must contain unique  
                // values so the SQL below will work properly. 
                if (columnToSortBy == null)
                {
                    columnToSortBy = this.Columns[0].ColumnName;
                }
    
                if (!this.Columns[columnToSortBy].Unique)
                {
                    throw new InvalidOperationException(String.Format(
                        "Column {0} must contain unique values.", columnToSortBy));
                }
    
                // Retrieve the specified number of rows from the database, starting 
                // with the row specified by the lowerPageBoundary parameter.
                if (filters.Trim().ToUpper().IndexOf("WHERE") > -1)
                {
                    filters = filters.ToUpper().Replace("WHERE", string.Empty);
                }
    
                command.CommandText = "Select Top " + rowsPerPage + " " +
                    CommaSeparatedListOfColumnNames + " From " + tableName +
                    " WHERE 1=1 AND " + filters + " " + (filters.Trim().Length > 0 ? " AND " : string.Empty) + columnToSortBy + " NOT IN (SELECT TOP " +
                    lowerPageBoundary + " " + columnToSortBy + " From " +
                    tableName + "  WHERE 1=1 " + (filters.Trim().Length > 0 ? " AND " : string.Empty) + filters + " Order By " + sortColumn +
                    ") Order By " + sortColumn;
                adapter.SelectCommand = command;
    
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                adapter.Fill(table);
                return table;
    }

    Now I want to use a stored procedure in one routine which replace other two routine.

    I will develop the stored procedure with a 1st result will return no of rows and second result will return actual data.

    This way I am paging in the stored procedure:

    WHERE [rn] BETWEEN ((@StartIndex-1) * @EndIndex ) + 1 AND (@StartIndex * @EndIndex)

    Now see the above routine called

    SupplyPageOfData

    how it is doing paging and tell me how to use

    lowerPageBoundary

    and

    rowsPerPage

    as a result I could send those two value in my sp and sp can do the successfully paging.

    rowsPerPage

    is fixed that is 16 but

    lowerPageBoundary

    jump by 16. so calculation is not coming to my mind like how to change code as a result i can send

    lowerPageBoundary

    &

    rowsPerPage

    to store proc and with in stored procedure I can form paging line like

    WHERE [rn] BETWEEN ((@StartIndex-1) * @EndIndex ) + 1 AND (@StartIndex * @EndIndex)

    Please help me with code and sample. thanks

    Wednesday, September 23, 2015 2:24 PM

Answers

  • User-219423983 posted

    Hi sudip_inn,

    The following is a stored procedure about paging you could refer to and make some changes according your own needs. It would return two results, the first one is the total count with on line data and the second one with the expected actual data. Here, you could just use the “Top N*” to get the data without using the “between…and…”.

    CREATE PROCEDURE [usp_GetProducts] 
    @startRowIndex int,
    @maximumRows int, 
    @totalRows int OUTPUT
    
    AS
    
    DECLARE @first_id int, @startRow int
    
    SET @startRowIndex =  (@startRowIndex - 1)  * @maximumRows
    
    IF @startRowIndex = 0 
    SET @startRowIndex = 1
    
    SET ROWCOUNT @startRowIndex
    
    SELECT @first_id = ProductID FROM Products ORDER BY ProductID
    
    PRINT @first_id
    
    SET ROWCOUNT @maximumRows
    
    SELECT ProductID, ProductName FROM Products WHERE 
    ProductID >= @first_id 
    ORDER BY ProductID
    
    SET ROWCOUNT 0
    
    -- GEt the total rows 
    
    SELECT @totalRows = COUNT(ProductID) FROM Products
    GO
    

    http://www.codeproject.com/Articles/16238/GridView-Custom-Paging

    I hope it’s useful to you.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 24, 2015 1:14 AM