|
To understand the topic, create
a sample project that displays User information from a sql express database in GridView control
using SqlDataSource control. I have created a table called CD_Users to demonstrate the SqlDataSource control usages in this article.
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
advance features that we can implement with the SqlDataSource
control.
Cancel SqlDataSource DataBind on Initial
Page Load
By default, when we configure the
SqlDataSource control with a Databound control it will populate the data on the
initial page load. At times, we need to restrict the databind on initial page
load and populate it in subsequent postbacks. For example, in a button click.
SqlDataSource expose various events during the process of data binding to have a
control on databinding. With these events, it is possible to do customizations
and even implementing business rules dynamically during the databind operation.
To cancel a databind, we can utilize the “onselecting” event which will be
raised by SqlDataSource control during every database select operation. The
SqlDataSourceSelectingEventArgs object is packed with a property called “Cancel”
which can be used to cancel the databind.
To cancel the databind on initial page
load,
ASPX
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$
ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM
[CD_Users]"
onselecting="SqlDataSource1_Selecting"></asp:SqlDataSource>
CodeBehind
protected void
SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs
e)
{
if (!IsPostBack)
e.Cancel = true;
}
Get Total Number of Rows returned by
SqlDataSource control
There will be requirements where we
need to fetch the number of records or rows returned by SqlDataSource control.
The previous section explained about an event called “onselecting” which gets
raised during select operations. This means that the selected data will be
available only after this event. There is an event called “onselected” event
which gets raised after every database select operations. This event exposes a
property called AffectedRows that returns number of rows selected.
To fetch the total number of records or
rows returned by SqlDataSource control,
ASPX
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$
ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM
[CD_Users]"
onselecting="SqlDataSource1_Selecting"></asp:SqlDataSource>
CodeBehind
protected void
SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs
e)
{
Response.Write(e.AffectedRows.ToString());
}
Get Output Parameter Value from
SqlDataSource control
Sometimes, we will use stored
procedures to bind the data to a databound control using SqlDatasource control.
To use strored procedure we need to set the “SelectCommand” property to the
stored procedure name and “SelectCommandType” proeprty to “StoredProcedure”.
There are also chances where we will have output parameters associated with the
Store procedures whose value should be assigned to a page control like Label,
etc.
To access the output parameter returned
from SP,
ASPX
<asp:SqlDataSource
ID="SqlDataSource2" runat="server"
ConnectionString="<%$
ConnectionStrings:ConnectionString %>"
onselected="SqlDataSource2_Selected"
SelectCommand="SP_GetUsersForModeration"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter
Direction="Output" Name="NoOfRoles" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
CodeBehind
protected void
SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs
e)
{
Response.Write(e.Command.Parameters["@NoOfRoles"].Value);
}
SP
ALTER PROC
[dbo].[SP_GetUsersForModeration]
(
@NoOfRoles INT OUTPUT
)
AS
Begin
SELECT
users.Email,
users.FirstName,
users.LastName,
roles.Role,
roles.RoleID
FROM
CD_Users users INNER JOIN CD_Roles
roles
ON users.RoleID =
roles.RoleID
SELECT @NoOfRoles = COUNT(*) from
CD_Roles
End
|