SQL datasource for gridview

Question

Hi

I want to use a gridview with sorting and paging to display data from an SQL server, the query uses 3 joins and the full text search containstable. The from part of the query uses all 3 tables in the join.

What is the best way to do this? I can think of a stored procedure, SQL directly in the SQLDataSource and creating a view in the database. I want good performance and would like to leverage the automatic sorting and paging features of the gridview as much as possible.

Edit: About size, I suspect very few records, total about 1000 and a query would typical result in no more than 100 records and most times much less.

Answer 1

My suggestion would be to use a stored procedure. You can setup the stored procedure parameter input from the SQLDataSource attached to the Gridview. I would discourage use of direct SQL select statement in your datasource, as IMHO calling a stored procedure with parameter specification is more secure.

Hope this helps, Sid

Answer 2

It all depends on the volume of data which you are talking about. If it has millions of records then I would not suggest automatic sorting and paging. It is better to use stored procedure and use Row_Number() feature (if you are using SQL 2005).

HTH

Answer 3

I would create a search procedure with parameters for all of your inputs, and attach those input fields to your Sql Data Source. That way, when users enter filter criteria all you have to do is call grid.DataBind() to apply the filter.

Answer 4

Here is what I ended up doing

.aspx
<asp:GridView ID="gridAnnoncer" runat="server" AutoGenerateColumns="false" DataSourceID="dsAnnonceSearch" DataKeyNames="Id" AllowPaging="true" AllowSorting="true" PageSize="1">
    <Columns>
        ..
    </Columns>
</asp:GridView>

<asp:LinqDataSource ID="dsAnnonceSearch" runat="server" AutoPage="false" OnSelecting="AnnonceSearchOnSelecting">
</asp:LinqDataSource>


.aspx.cs
protected void AnnonceSearchOnSelecting(object sender, LinqDataSourceSelectEventArgs e)
{
    using (TheContext context = new TheContext())
    {
        int? totalRows;
        string orderby = e.Arguments.SortExpression.ToLower().Replace(" desc", "").Replace(" asc", "").Trim();
        string sortDirection = e.Arguments.SortExpression.ToLower().Contains("desc") ? "desc" : "asc";
        e.Result = context.AnnonceSearch("test", orderby, sortDirection, e.Arguments.StartRowIndex, e.Arguments.MaximumRows, out totalRows);
        e.Arguments.TotalRowCount = (int) totalRows;
    }
}


Stored procedure
ALTER PROCEDURE [dbo].[AnnonceSearch]
    @keywords nvarchar(4000),
    @orderby varchar(100),
    @orderDirection varchar(100),
    @startRowIndex int,
    @maximumRows int,
    @totalRows int output
AS
BEGIN
    SET NOCOUNT ON;

    if @keywords is null or @keywords = '' set @keywords = '""'
    if @startRowIndex < 0 RAISERROR('startRowIndex parameter is invalid', 0, 1)
    if @maximumRows < 1 RAISERROR('getRows parameter is invalid', 0, 1)

    select TOP (@maximumRows) Id, Productname, description, Zipcode from
        (select row_number() over (order by 
            case when lower(@orderDirection) = 'desc' then
                    case lower(@orderby)
                        when 'description' then Annoncer.description
                        when 'Productname' then Annoncer.Productname
                    end
            end desc,
            case when lower(@orderDirection) = 'asc' then
                    case lower(@orderby)
                        when 'description' then Annoncer.description
                        when 'Productname' then Annoncer.Productname
                    end
            end
            ) as RowNumber, 
        Annoncer.Id, Annoncer.Productname, Annoncer.description from Annoncer

        where @keywords = '""' or (contains(Annoncer.Productname, @keywords) or 
                                    contains(Annoncer.description, @keywords))) searchResult
    where RowNumber > @startRowIndex

    SELECT @totalRows = COUNT(*) FROM Annoncer
END