locked
SqlDataSource as DataSet RRS feed

  • Question

  • User1221384465 posted

    Not finding any "clear" explanations on how this actually works. My assumption here is that a dataset is returned from a single database access. Then, through the markup, you canb sort, page, and filter the dataset without having to go to and from the database.

    All I'm attempting is to use a SqlDataSource to call a stored procedure that returns a DataSet, then link the control's select and sort commands to run strictly off the returned dataset. I'm not adding or deleting to the dataset, just simply want to sort, page, amd filter from one set of data returned from my query. Where it starts getting murky is wiring up the select and sort commands for the control. The only examples I keep finding access a SqlServer database throught the connectionstring, then write in the select commands. If I write say "select * table1" am I accessing the DataSet or accessing db through the connection string provided?

    Can anyone give me a brief overview on how this works? Explanations, code samples, links, or whatever work for me. Just can't seem to find anything straightforward without 20 different short explanations of a very general topic.

    Much appreciated!

    Tuesday, January 25, 2011 11:22 AM

All replies

  • User1009619486 posted

    Wrong Assumtpyion

    When  u use SqlDataSource it always make a hit to database for your sorting and paging commands

    Advantages :

    1) No piece of code behind for applying sorting and paging

     

    DisAdvantages:

    1) Every time a sort or paging button is clicked database hit has to be made

    2) it always return whole data and then page or sort on this full result set. so if data is more it will utilize

        more time and bandwidth

    here is very simple and basic implementation of sqldatasource without a single piece of code behind with paging and sorting

    it uses storedprocedure

    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AllowPaging="true" AllowSorting="true"
                AutoGenerateColumns="False">
                <Columns>
                    <asp:BoundField DataField="RecursionLevel" HeaderText="RecursionLevel" 
                        ReadOnly="True" SortExpression="RecursionLevel" />
                    <asp:BoundField DataField="ManagerID" HeaderText="ManagerID" ReadOnly="True" 
                        SortExpression="ManagerID" />
                    <asp:BoundField DataField="ManagerFirstName" HeaderText="ManagerFirstName" 
                        SortExpression="ManagerFirstName" />
                    <asp:BoundField DataField="ManagerLastName" HeaderText="ManagerLastName" 
                        SortExpression="ManagerLastName" />
                    <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="True" 
                        SortExpression="EmployeeID" />
                    <asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True" 
                        SortExpression="FirstName" />
                    <asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True" 
                        SortExpression="LastName" />
                </Columns>
            </asp:GridView>
        </div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>" 
            SelectCommand="uspGetManagerEmployees" SelectCommandType="StoredProcedure" >
            <SelectParameters>
                <asp:Parameter DefaultValue="109" Name="ManagerID" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>


     

     

     

    Tuesday, January 25, 2011 12:18 PM
  • User1221384465 posted

    I see now, I'm taking the wrong approach entirely and possibly using the wrong datasource control. The hits on the db are returning thousands of rows of information. My intention was to have access to the common dataset at the front as a datasource control so functions like filtering, sorting, and paging are automatically wired up without having to write my own event handlers (which by the way is giving me a huge headache). What controls and structures are recommended for this approach?

    Tuesday, January 25, 2011 12:45 PM
  • User1009619486 posted

    just the code which is pasted here and nothing else

    Wednesday, January 26, 2011 10:56 AM