Before moving into the topic, create a sample project by including a Sql Express database in App_Data folder. In this article, i have used a table called CD_Users created in App_Data folder. Also, drag a GridView control to display the data from the database using SqlDataSource control.

Binding a SqlDataSource Control

Drag a SqlDataSource control and a GridView control from the “Data” tab of Visual Studio toolbar. Configure the SqlDataSource control through “Configure Data Source...” wizard to fetch the data from the Sql express database added to your solution. Assign the GridView’s DataSourceID property to the SqlDataSource control’s ID.

These steps will generate the code below,

ASPX

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

        SelectCommand="SELECT * FROM [CD_Users]"></asp:SqlDataSource>

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

        DataKeyNames="Email" DataSourceID="SqlDataSource1">

        <Columns>

            <asp:BoundField DataField="UserID" HeaderText="UserID" InsertVisible="False"

                ReadOnly="True" SortExpression="UserID" />

            <asp:BoundField DataField="FirstName" HeaderText="FirstName"

                SortExpression="FirstName" />

            <asp:BoundField DataField="LastName" HeaderText="LastName"

                SortExpression="LastName" />

            <asp:BoundField DataField="Email" HeaderText="Email" ReadOnly="True"

                SortExpression="Email" />

            <asp:BoundField DataField="RoleID" HeaderText="RoleID"

                SortExpression="RoleID" />

            <asp:BoundField DataField="CreatedOn" HeaderText="CreatedOn"

                SortExpression="CreatedOn" />

            <asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated"

                SortExpression="LastUpdated" />

        </Columns>

    </asp:GridView>

Execute the application and you can see the data populated in the GridView control. Moving forward, we will see some of the advance features that we can implement with the SqlDataSource control.

Using DataSourceMode property

Using this property, we can control the SqlDataSource control to use either DataSet or DataReader object for the database operations. If your database operation is only select then you can set this property to DataReader to have better performance. If your database operation involves sorting, filtering, paging then it can be set to DataSet. Next section discusses more on this.

Filtering or Searching Data Using SqlDataSource control

Filtering or searching data is one of most frequent operation that is done on any data driven website. SqlDataSource control offers us the flexibility to do this operation very easily.

This operation can be done in 2 ways using SqlDataSource control,

1.      Filtering or Searching at database level

2.      Filtering or Searching at application level

To understand this, we will create a sample page that can search for the employees using the firstname and lastname that is typed in textbox.

Filtering or Searching at Database level

1.      Drag 2 TextBox control and a Button control from the Visual Studio toolbox.

2.      Drag a SqlDataSource control and a GridView control from Data tab of Visual studio toolbox.

3.      Configure the SqlDataSource control’s SelectParameter collection to read the search parameter from the textbox control.  Refer the below code. Also, configure the GridView control’s DataSourceID to SqlDataSource control’s ID.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true"

     DataSourceID="SqlDataSource2">   

 <asp:SqlDataSource ID="SqlDataSource2" runat="server"

        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

        SelectCommand="SELECT * FROM [CD_Users] WHERE (([FirstName] LIKE '%' + @FirstName + '%') AND ([LastName] LIKE '%' + @LastName + '%'))">

        <SelectParameters>

         <asp:ControlParameter ControlID="txtFirstName" Name="FirstName"

                 PropertyName="Text" Type="String" ConvertEmptyStringToNull="false"  />

             <asp:ControlParameter ControlID="txtLastName" Name="LastName"

                 PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />

        </SelectParameters>       

    </asp:SqlDataSource>

Execute the page. You can now search or filter data based on the user’s firstname and lastname.

The above code will fetch the data from database every time you click search button.

Filtering or Searching at Application level

1.      Drag 2 TextBox control and a Button control from the Visual Studio toolbox.

2.      Drag a SqlDataSource control and a GridView control from Data tab of Visual studio toolbox.

3.      Configure the SqlDataSource control’s FilterExpression property to filter the data based on the firstname and lastname. Next, configure the FilterParameters collection to read the search parameter values from the textbox control.  Enable the property EnableCaching and set DataSourceMode to DataSet to filter data at the application level. The SqlDataSource control will in turn use DataView to filter the data. Also, configure the GridView control’s DataSourceID to SqlDataSource control’s ID.

Refer the below code,

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

        SelectCommand="SELECT * FROM [CD_Users]"      

       FilterExpression="FirstName LIKE '%{0}%' AND LastName LIKE '%{1}%'"

        EnableCaching="true" DataSourceMode="DataSet">

        <FilterParameters>

         <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" ConvertEmptyStringToNull="false"  />

         <asp:ControlParameter ControlID="txtLastName" Name="LastName" ConvertEmptyStringToNull="false"/>

        </FilterParameters>        

    </asp:SqlDataSource>

Execute the page. You can now search or filter data based on the user’s firstname and lastname. 

The above code will fetch the data from database and will apply the filter at the application level i.e. the data will be filled to a dataset object on the initial page load and henceforth the filtration will be done on this without hitting the database as opposed to the previous section.

Accessing the Database Result from SqlDataSource control in CodeBehind

Sometimes, it is required to fetch the result of a select operation from SqlDataSource control in the codebehind file. To do this, SqlDataSource control exposes a method called Select() which will return the data. This method takes DataSourceSelectArguments object as a parameter. The DataSourceSelectArguments object can be used to make the SqlDataSource control to perform additional data-related operations on the result set, such as sorting the data or returning a specific subset of data(Filtering).  In case if you need not do any sorting or filtering this object has property called Empty which can be used.

Refer the below code,

protected void Page_Load(object sender, EventArgs e)

    {

        GridView2.DataSource = SqlDataSource2.Select(DataSourceSelectArguments.Empty);

        GridView2.DataBind();

    }

The above code will get the result of the select operation from SqlDataSource control and will bind the GridView control. SqlDataSource control will in turn use the sql query and parameter configured on it to fetch the result.

Note

SqlDataSource control includes Update() and Delete() method to do their respective actions from codebehind or programmatically.