ASP.NET Tutorial/ADO.net Database/SqlDataSource
Содержание
- 1 asp:SqlDataSource select command with parameters
- 2 Bind asp:SqlDataSource with CheckBoxList and RadioButtonList (VB.net)
- 3 Caching Database Data with the SqlDataSource Control
- 4 Canceling Command Execution when Deleting, Filtering, Inserting, Selecting, Updating
- 5 Configure two asp:SqlDataSource with different SelectCommand
- 6 Connecting to Microsoft SQL Server
- 7 Connection to a mdf file
- 8 Declare each of the parameters used when executing the update command.
- 9 Executing Stored Procedures
- 10 Filtering Database Rows
- 11 Handle the exception at the level of a DataBound control.
- 12 Handling SQL Command Execution Errors
- 13 Link asp:DropDownList with asp:SqlDataSource
- 14 Link asp:GridView with asp:SqlDataSource
- 15 Retrieves the records from the database by using a DataReader
- 16 Show Page Control Parameter
- 17 Specify column from asp:SqlDataSource
- 18 SqlDataSource with dynamic parameter
- 19 Use connection string in asp:SqlDataSource and set the DeleteCommand and SelectCommand
- 20 Using ASP.NET Parameters with the SqlDataSource Control
- 21 Using the ASP.NET ControlParameter Object to represent the value of a control property.
- 22 Using the ASP.NET CookieParameter Object
- 23 Using the ASP.NET FormParameter Object
- 24 Using the ASP.NET ProfileParameter Object
- 25 Using the QueryStringParameter Object
- 26 Using the SessionParameter Object
asp:SqlDataSource select command with parameters
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true"%> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
Categories:
<asp:SqlDataSource ID="sourceCategories" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>" SelectCommand="SELECT * FROM Categories"> </asp:SqlDataSource> <asp:GridView ID="gridCategories" runat="server" DataSourceID="sourceCategories" DataKeyNames="CategoryID"> <Columns> <asp:CommandField ShowSelectButton="True" /> </Columns> <RowStyle BackColor="White" ForeColor="#330099" /> <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" /> </asp:GridView>
Products in this category:
<asp:SqlDataSource ID="sourceProducts" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>" SelectCommand="SELECT ProductID, ProductName, UnitPrice FROM Products WHERE CategoryID=@CategoryID"> <SelectParameters> <asp:ControlParameter Name="CategoryID" ControlID="gridCategories" PropertyName="SelectedDataKey.Value" /> </SelectParameters> </asp:SqlDataSource> <asp:GridView ID="gridProducts" runat="server" DataSourceID="sourceProducts" BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" > <RowStyle BackColor="White" ForeColor="#330099" /> <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" /> </asp:GridView>
</form>
</body> </html> File: Web.config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/> </connectionStrings>
</configuration></source>
Bind asp:SqlDataSource with CheckBoxList and RadioButtonList (VB.net)
<source lang="csharp">
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CheckAndRadio.aspx.vb" Inherits="CheckAndRadio" %> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Books]"></asp:SqlDataSource>
CheckBoxList: <asp:CheckBoxList ID="CheckBoxList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Title" DataValueField="BookID"> </asp:CheckBoxList>
RadioButtonList:
<asp:RadioButtonList ID="RadioButtonList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Title" DataValueField="BookID"> </asp:RadioButtonList>
<asp:Button ID="Button1" runat="server" Text="Click Me!" />
<asp:Label ID="results" runat="server"></asp:Label> </form>
</body> </html>
File: CheckAndRadio.aspx.vb Partial Class CheckAndRadio
Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click results.Text = String.Empty For Each li As ListItem In CheckBoxList1.Items If li.Selected Then results.Text &= li.Text & "results.Text &= "
" End If Next
"
If RadioButtonList1.SelectedItem IsNot Nothing Then results.Text &= RadioButtonList1.SelectedItem.Text End If End Sub
End Class</source>
Caching Database Data with the SqlDataSource Control
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void srcProducts_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { lblMessage.Text = "Retrieving data from database"; }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Cache SqlDataSource</title>
</head> <body>
<form id="form1" runat="server">
<asp:Label id="lblMessage" EnableViewState="false" Runat="server" />
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:SqlDataSource id="srcProducts" EnableCaching="True" CacheDuration="3600" SelectCommand="SELECT * FROM Products" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server" OnSelecting="srcProducts_Selecting" />
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Canceling Command Execution when Deleting, Filtering, Inserting, Selecting, Updating
<source lang="csharp">
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data.SqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void srcProducts_Updating(object sender, SqlDataSourceCommandEventArgs e) { foreach (SqlParameter param in e.rumand.Parameters) if (param.Value == null) { e.Cancel = true; lblError.Text = "All fields are required!"; } }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <body>
<form id="form1" runat="server">
<asp:Label id="lblError" EnableViewState="false" CssClass="error" Runat="server" /> <asp:DetailsView id="dtlProduct" DataSourceID="srcProducts" DataKeyNames="Id" AllowPaging="true" AutoGenerateEditButton="true" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT * FROM Products" UpdateCommand="UPDATE Products SET Title=@Title,Director=@Director,DateReleased=@DateReleased WHERE Id=@id" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server" OnUpdating="srcProducts_Updating" />
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Configure two asp:SqlDataSource with different SelectCommand
<source lang="csharp">
<%@ Page Language="VB" AutoEventWireup="false" %> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Books]"></asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Books] WHERE (([BookID] <= @BookID) AND ([YearPublished] = @YearPublished)) ORDER BY [Price] DESC, [Title]"> <SelectParameters> <asp:Parameter DefaultValue="3" Name="BookID" Type="Int32" /> <asp:Parameter DefaultValue="2005" Name="YearPublished" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="BookID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="BookID" HeaderText="BookID" InsertVisible="False" ReadOnly="True" SortExpression="BookID" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" /> <asp:BoundField DataField="YearPublished" HeaderText="YearPublished" SortExpression="YearPublished" /> <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" /> <asp:BoundField DataField="LastReadOn" HeaderText="LastReadOn" SortExpression="LastReadOn" /> <asp:BoundField DataField="PageCount" HeaderText="PageCount" SortExpression="PageCount" /> </Columns> </asp:GridView>
</form>
</body> </html> File: Web.Config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
Connecting to Microsoft SQL Server
<source lang="csharp">
You represent a database connection string with the SqlDataSource control"s ConnectionString property. <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Local Connection</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT * FROM Products" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" Runat="server" />
</form>
</body> </html></source>
Connection to a mdf file
<source lang="csharp">
<%@ Page Language="VB"%> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True" SelectCommand="SELECT * FROM [Books]"></asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True" SelectCommand="SELECT * FROM [Books] WHERE (([BookID] <= @BookID) AND ([YearPublished] = @YearPublished)) ORDER BY [Price] DESC, [Title]"> <SelectParameters> <asp:Parameter DefaultValue="3" Name="BookID" Type="Int32" /> <asp:Parameter DefaultValue="2005" Name="YearPublished" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
</form>
</body> </html></source>
Declare each of the parameters used when executing the update command.
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show DetailsView Explicit</title>
</head> <body>
<form id="form1" runat="server">
<asp:DetailsView id="dtlProduct" DataKeyNames="Id" DataSourceID="srcProducts" AutoGenerateEditButton="true" DefaultMode="Edit" AllowPaging="true" runat="server" /> <asp:SqlDataSource id="srcProducts" ConnectionString="<%$ ConnectionStrings:Products %>" SelectCommand="Select * FROM Products" UpdateCommand="UPDATE Products SET Title=@Title,Director=@Director, DateReleased=@DateReleased WHERE Id=@id" Runat="server"> <UpdateParameters> <asp:Parameter Name="Title" Type="String" Size="100" DefaultValue="Untitled" /> <asp:Parameter Name="Director" Type="String" Size="100" DefaultValue="Alan Smithee" /> <asp:Parameter Name="DateReleased" Type="DateTime" /> <asp:Parameter Name="id" Type="int32" /> </UpdateParameters> </asp:SqlDataSource>
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Executing Stored Procedures
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" CssClass="gridView" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="CountProductsInCategory" SelectCommandType="StoredProcedure" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server" />
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Filtering Database Rows
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <body>
<form id="form1" runat="server">
<asp:TextBox id="txtTitle" Runat="server" /> <asp:Button id="btnMatch" Text="Match" Runat="server" />
<asp:GridView id="grdProducts" DataSourceId="srcProducts" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT Id,Title,Director,DateReleased FROM Products" FilterExpression="Title LIKE "{0}%"" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server"> <FilterParameters> <asp:ControlParameter Name="Title" ControlID="txtTitle" /> </FilterParameters> </asp:SqlDataSource>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Handle the exception at the level of a DataBound control.
<source lang="csharp">
The GridView, DetailsView, and FormView controls all include events that expose the Exception and ExceptionHandled properties. <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void grdProducts_RowUpdated(object sender, GridViewUpdatedEventArgs e) { if (e.Exception != null) { lblError.Text = e.Exception.Message; e.ExceptionHandled = true; } }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <body>
<form id="form1" runat="server">
<asp:Label id="lblError" EnableViewState="false" CssClass="error" Runat="server" /> <asp:GridView id="grdProducts" DataKeyNames="Id" AutoGenerateEditButton="true" DataSourceID="srcProducts" OnRowUpdated="grdProducts_RowUpdated" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT Id,Title FROM Products" UpdateCommand="UPDATE DontExist SET Title=@Title WHERE Id=@ID" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server" />
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Handling SQL Command Execution Errors
<source lang="csharp">
You can handle errors thrown by the SqlDataSource control by handling any or all of the following four events: Deleted, Inserted, Selected, Updated Each of these events is passed an EventArgs parameter that includes any exceptions raised when the command was executed. <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void srcProducts_Selected(object sender, SqlDataSourceStatusEventArgs e) { if (e.Exception != null) { lblError.Text = e.Exception.Message; e.ExceptionHandled = true; } }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <body>
<form id="form1" runat="server">
<asp:Label id="lblError" EnableViewState="false" CssClass="error" Runat="server" /> <asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT * FROM DontExist" ConnectionString="<%$ ConnectionStrings:Products %>" OnSelected="srcProducts_Selected" Runat="server" />
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Link asp:DropDownList with asp:SqlDataSource
<source lang="csharp">
<%@ Page Language="VB" AutoEventWireup="false"%> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT [Genre] FROM [Books] ORDER BY [Genre]"></asp:SqlDataSource>
Choose a genre: <asp:DropDownList ID="genres" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="Genre" DataValueField="Genre"> </asp:DropDownList>
<asp:SqlDataSource ID="booksDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Books] WHERE ([Genre] = @Genre)"> <SelectParameters> <asp:ControlParameter ControlID="genres" Name="Genre" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> <asp:GridView ID="books" runat="server" AutoGenerateColumns="False" BackColor="White" DataKeyNames="BookID" DataSourceID="booksDataSource" ForeColor="Black" GridLines="Vertical"> <FooterStyle BackColor="#CCCC99" /> <Columns> <asp:BoundField DataField="BookID" HeaderText="BookID" InsertVisible="False" ReadOnly="True" SortExpression="BookID" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" /> <asp:BoundField DataField="YearPublished" HeaderText="YearPublished" SortExpression="YearPublished" /> <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" /> <asp:BoundField DataField="LastReadOn" HeaderText="LastReadOn" SortExpression="LastReadOn" /> <asp:BoundField DataField="PageCount" HeaderText="PageCount" SortExpression="PageCount" /> <asp:BoundField DataField="Genre" HeaderText="Genre" SortExpression="Genre" /> </Columns> <RowStyle BackColor="#F7F7DE" /> <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> </form>
</body> </html> File: Web.config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
Link asp:GridView with asp:SqlDataSource
<source lang="csharp">
<%@ Page Language="VB" AutoEventWireup="false"%> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Books]"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="#FFE0C0" BorderColor="Purple" BorderStyle="Solid" BorderWidth="5px" DataKeyNames="BookID" DataSourceID="SqlDataSource1" EmptyDataText="There are no books!" Font-Names="Verdana" Font-Size="X-Small" GridLines="None"> <Columns> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" > <ItemStyle Font-Italic="True" /> </asp:BoundField> <asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" /> <asp:BoundField DataField="PageCount" HeaderText="Pages" SortExpression="PageCount" /> <asp:BoundField DataField="YearPublished" HeaderText="Published" SortExpression="YearPublished" /> <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" DataFormatString="{0:c}" HtmlEncode="False" /> <asp:BoundField DataField="LastReadOn" HeaderText="Last Read" SortExpression="LastReadOn" DataFormatString="{0:d}" HtmlEncode="False" /> </Columns> <HeaderStyle BackColor="Green" Font-Size="Medium" Font-Underline="True" ForeColor="White" /> <AlternatingRowStyle BackColor="#E0E0E0" /> </asp:GridView> </form>
</body> </html> File: Web.config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
Retrieves the records from the database by using a DataReader
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Data Source Mode</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:SqlDataSource id="srcProducts" DataSourceMode="DataReader" SelectCommand="SELECT * FROM Products" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server" />
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Show Page Control Parameter
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
public string IPAddress { get { return Request.UserHostAddress; } }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Page Control Parameter</title>
</head> <body>
<form id="form1" runat="server">
<asp:FormView id="frmGuestBook" DataSourceID="srcGuestBook" DefaultMode="Insert" runat="server"> <InsertItemTemplate> <asp:Label id="lblName" Text="Your Name:" AssociatedControlID="txtName" Runat="server" /> <asp:TextBox id="txtName" Text="<%# Bind("Name") %>" Runat="server" />
<asp:Label id="Label1" Text="Your Comments:" AssociatedControlID="txtComments" Runat="server" />
<asp:TextBox id="txtComments" Text="<%# Bind("Comments") %>" TextMode="MultiLine" Columns="60" Rows="4" Runat="server" />
<asp:Button id="btnSubmit" Text="Submit" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView>
<asp:GridView id="grdGuestBook" DataSourceID="srcGuestBook" Runat="server" /> <asp:SqlDataSource id="srcGuestBook" SelectCommand="SELECT * FROM GuestBook ORDER BY Id DESC" InsertCommand="INSERT GuestBook (IPAddress,Name,Comments) VALUES (@IPAddress,@Name, ConnectionString="<%$ ConnectionStrings:GuestBook %>" Runat="server"> <InsertParameters> <asp:ControlParameter Name="IPAddress" ControlID="__page" PropertyName="IPAddress" /> </InsertParameters> </asp:SqlDataSource>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="GuestBook" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Specify column from asp:SqlDataSource
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="sourceProducts" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>" SelectCommand="SELECT * FROM Products"></asp:SqlDataSource> <asp:DropDownList ID="lstProducts" runat="server" AutoPostBack="True" DataSourceID="sourceProducts" DataTextField="ProductName" DataValueField="ProductID" Width="184px"> </asp:DropDownList>
<asp:SqlDataSource ID="sourceProductFull" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>" SelectCommand="SELECT * FROM Products WHERE ProductID=@ProductID"> <SelectParameters> <asp:ControlParameter Name="ProductID" ControlID="lstProducts" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" DataSourceID="sourceProductFull" Width="184px" CellPadding="5"> <ItemTemplate> In Stock: <%# Eval("UnitsInStock") %>
On Order: <%# Eval("UnitsOnOrder") %>
Reorder: <%# Eval("ReorderLevel") %>
</ItemTemplate> </asp:FormView>
</form>
</body> </html> File: Web.config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/> </connectionStrings>
</configuration></source>
SqlDataSource with dynamic parameter
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CustomParameters.aspx.cs" Inherits="CustomParameters" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">
<title>Custom Parameters</title>
</head> <body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="sourceCustomers" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ ConnectionStrings:Northwind %>" SelectCommand="SELECT CustomerID, ContactName FROM Customers"/> <asp:DropDownList ID="lstCustomers" runat="server" DataSourceID="sourceCustomers" DataTextField="ContactName" DataValueField="CustomerID" AutoPostBack="True"> </asp:DropDownList> <asp:SqlDataSource ID="sourceOrders" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ ConnectionStrings:Northwind %>" SelectCommand="SELECT OrderID,OrderDate,ShippedDate FROM Orders WHERE CustomerID=@CustomerID AND OrderDate>=@EarliestOrderDate" OnSelecting="sourceOrders_Selecting"> <SelectParameters> <asp:ControlParameter Name="CustomerID" ControlID="lstCustomers" PropertyName="SelectedValue" /> <asp:Parameter Name="EarliestOrderDate" DefaultValue="1900/01/01" /> </SelectParameters> </asp:SqlDataSource> <asp:GridView ID="gridOrders" runat="server" DataSourceID="sourceOrders" AutoGenerateColumns="False" DataKeyNames="OrderID" > <Columns> <asp:boundfield DataField="OrderID" HeaderText="OrderID" InsertVisible="False" ReadOnly="True" SortExpression="OrderID"></asp:boundfield> <asp:boundfield DataField="OrderDate" HeaderText="OrderDate" DataFormatString="{0:d}" SortExpression="OrderDate"></asp:boundfield> <asp:boundfield DataField="ShippedDate" DataFormatString="{0:d}" HeaderText="ShippedDate" SortExpression="ShippedDate"></asp:boundfield> </Columns> </asp:GridView>
</form>
</body> </html>
File: CustomParameters.aspx.cs using System; using System.Data; using System.Configuration; using System.Collections; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class CustomParameters : System.Web.UI.Page {
protected void sourceOrders_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.rumand.Parameters["@EarliestOrderDate"].Value = DateTime.Today.AddYears(-10); }
}
File: Web.config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/> </connectionStrings>
</configuration></source>
Use connection string in asp:SqlDataSource and set the DeleteCommand and SelectCommand
<source lang="csharp">
<%@ Page Language="VB" AutoEventWireup="false" %> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [Books] WHERE [BookID] = @BookID" InsertCommand="INSERT INTO [Books] ([Title], [Author], [YearPublished], [Price], [LastReadOn], [PageCount]) VALUES (@Title, @Author, @YearPublished, @Price, @LastReadOn, @PageCount)" SelectCommand="SELECT * FROM [Books]" UpdateCommand="UPDATE [Books] SET [Title] = @Title, [Author] = @Author, [YearPublished] = @YearPublished, [Price] = @Price, [LastReadOn] = @LastReadOn, [PageCount] = @PageCount WHERE [BookID] = @BookID"> <DeleteParameters> <asp:Parameter Name="BookID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Title" Type="String" /> <asp:Parameter Name="Author" Type="String" /> <asp:Parameter Name="YearPublished" Type="Int32" /> <asp:Parameter Name="Price" Type="Decimal" /> <asp:Parameter Name="LastReadOn" Type="DateTime" /> <asp:Parameter Name="PageCount" Type="Int32" /> <asp:Parameter Name="BookID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="Title" Type="String" /> <asp:Parameter Name="Author" Type="String" /> <asp:Parameter Name="YearPublished" Type="Int32" /> <asp:Parameter Name="Price" Type="Decimal" /> <asp:Parameter Name="LastReadOn" Type="DateTime" /> <asp:Parameter Name="PageCount" Type="Int32" /> </InsertParameters> </asp:SqlDataSource>
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False" DataKeyNames="BookID" DataSourceID="SqlDataSource1" ForeColor="Black" GridLines="Vertical" Height="50px" Width="125px"> <FooterStyle BackColor="#CCCC99" /> <EditRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#F7F7DE" /> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <Fields> <asp:BoundField DataField="BookID" HeaderText="BookID" InsertVisible="False" ReadOnly="True" SortExpression="BookID" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" /> <asp:BoundField DataField="YearPublished" HeaderText="YearPublished" SortExpression="YearPublished" /> <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" /> <asp:BoundField DataField="LastReadOn" HeaderText="LastReadOn" SortExpression="LastReadOn" /> <asp:BoundField DataField="PageCount" HeaderText="PageCount" SortExpression="PageCount" /> <asp:CommandField ShowInsertButton="True" /> </Fields> <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:DetailsView> </form>
</body> </html>
File: Web.Config <?xml version="1.0"?> <configuration>
<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
Using ASP.NET Parameters with the SqlDataSource Control
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show DetailsView</title>
</head> <body>
<form id="form1" runat="server">
<asp:DetailsView id="dtlProduct" DataKeyNames="Id" DataSourceID="srcProducts" AutoGenerateEditButton="true" DefaultMode="Edit" AllowPaging="true" runat="server" /> <asp:SqlDataSource id="srcProducts" ConnectionString="<%$ ConnectionStrings:Products %>" SelectCommand="Select * FROM Products" UpdateCommand="UPDATE Products SET Title=@Title,Director=@Director, DateReleased=@DateReleased WHERE Id=@id" Runat="server" />
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Using the ASP.NET ControlParameter Object to represent the value of a control property.
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Control Parameter</title>
</head> <body>
<form id="form1" runat="server">
<asp:DropDownList id="ddlProducts" DataSourceID="srcProducts" DataTextField="Title" DataValueField="Id" Runat="server" /> <asp:Button id="btnSelect" Text="Select" Runat="server" />
<asp:DetailsView id="dtlProduct" DataSourceID="srcProductDetails" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT Id,Title FROM Products" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server" /> <asp:SqlDataSource id="srcProductDetails" SelectCommand="SELECT * FROM Products WHERE Id=@Id" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server"> <SelectParameters> <asp:ControlParameter Name="Id" ControlID="ddlProducts" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Using the ASP.NET CookieParameter Object
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
void Page_Load() { if (Request.Cookies["VoterId"] == null) { string identifier = Guid.NewGuid().ToString(); HttpCookie voteCookie = new HttpCookie("VoterId", identifier); voteCookie.Expires = DateTime.MaxValue; Response.AppendCookie(voteCookie); } }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Vote</title>
</head> <body>
<form id="form1" runat="server">
<asp:FormView id="frmVote" DataSourceID="srcVote" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label id="lblFavoriteColor" AssociatedControlID="rdlFavoriteColor" Runat="server" /> <asp:RadioButtonList id="rdlFavoriteColor" SelectedValue="<%#Bind("Color")%>" Runat="server"> <asp:ListItem Value="Red" Text="Red" Selected="True" /> <asp:ListItem Value="Blue" Text="Blue" /> <asp:ListItem Value="Green" Text="Green" /> </asp:RadioButtonList>
<asp:Button id="btnSubmit" Text="Submit" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView>
<asp:GridView id="grdVote" DataSourceID="srcVote" Runat="server" /> <asp:SqlDataSource id="srcVote" SelectCommand="SELECT * FROM Vote ORDER BY Id DESC" InsertCommand="INSERT Vote (VoterId,Color) VALUES (@VoterId,@Color)" ConnectionString="<%$ ConnectionStrings:Vote %>" Runat="server"> <InsertParameters> <asp:CookieParameter Name="VoterId" CookieName="VoterId" /> </InsertParameters> </asp:SqlDataSource>
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Vote" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Using the ASP.NET FormParameter Object
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
void Page_Load() { if (Request.Form["AddProduct"] != null) srcProducts.Insert(); }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show FormParameter</title>
</head> <body>
<form action="ShowFormParameter.aspx" method="post"> <label for="txtTitle">Product Title:</label> <input name="txtTitle" /> <label for="txtDirector">Product Director:</label> <input name="txtDirector" /> <input name="AddProduct" type="submit" value="Add Product" /> </form> <form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT * FROM Products" InsertCommand="INSERT Products (Title,Director,CategoryId,DateReleased) VALUES (@Title,@Director,0,"12/25/1966")" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server"> <InsertParameters> <asp:FormParameter Name="Title" FormField="txtTitle" DefaultValue="Untitled" /> <asp:FormParameter Name="Director" FormField="txtDirector" DefaultValue="Allen Smithee" /> </InsertParameters> </asp:SqlDataSource>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Using the ASP.NET ProfileParameter Object
<source lang="csharp">
The ProfileParameter object enables you to represent any of the properties of the Profile object. The ProfileParameter includes all the properties of the Parameter class and the following property: File: Web.config <configuration>
<connectionStrings> <add name="GuestBook" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|GuestBookDB.mdf; Integrated Security=True;User Instance=True" /> </connectionStrings> <system.web> <profile enabled="true"> <properties> <add name="DisplayName" defaultValue="Anonymous" /> </properties> </profile> </system.web>
</configuration> File: index.aspx <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show ProfileParameter</title>
</head> <body>
<form id="form1" runat="server">
<asp:FormView id="frmGuestBook" DataSourceID="srcGuestBook" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label id="lblComments" Text="Enter Your Comments:" Runat="server" />
<asp:TextBox id="txtComments" Text="<%# Bind("Comments") %>" TextMode="MultiLine" Columns="50" Rows="4" Runat="server" />
<asp:Button id="btnInsert" Text="Add Comments" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView>
<asp:GridView id="grdGuestBook" DataSourceID="srcGuestBook" Runat="server" /> <asp:SqlDataSource id="srcGuestBook" SelectCommand="SELECT Name,Comments,EntryDate FROM GuestBook ORDER BY Id DESC" InsertCommand="INSERT GuestBook (Name,Comments) VALUES (@Name,@Comments)" ConnectionString="<%$ ConnectionStrings:GuestBook %>" Runat="server"> <InsertParameters> <asp:ProfileParameter Name="Name" PropertyName="DisplayName" /> </InsertParameters> </asp:SqlDataSource>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Using the QueryStringParameter Object
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show QueryStringParameter Master</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceId="srcProducts" AutoGenerateColumns="false" ShowHeader="false" Runat="server"> <Columns> <asp:HyperLinkField DataTextField="Title" DataNavigateUrlFields="Id" DataNavigateUrlFormatString="ShowQueryStringParameterDetails.aspx?id={0}" /> </Columns> </asp:GridView> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT * FROM Products" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server" />
</form>
</body> </html> File: ShowQueryStringParameterDetails.aspx <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show QueryStringParameter Details</title>
</head> <body>
<form id="form1" runat="server">
<asp:DetailsView id="dtlProduct" DataSourceID="srcProduct" Runat="server" /> <asp:SqlDataSource id="srcProduct" SelectCommand="SELECT * FROM Products WHERE Id=@Id" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server"> <SelectParameters> <asp:QueryStringParameter Name="Id" QueryStringField="Id" /> </SelectParameters> </asp:SqlDataSource>
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
Using the SessionParameter Object
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
void Page_Load() { Session["ProductCategoryName"] = "Animation"; }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show SessionParameter</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:SqlDataSource id="srcProducts" SelectCommand="SELECT Name As Category,Title,Director FROM Products INNER JOIN ProductCategories ON CategoryId = ProductCategories.id WHERE Name=@Name" ConnectionString="<%$ ConnectionStrings:Products %>" Runat="server"> <SelectParameters> <asp:SessionParameter Name="Name" SessionField="ProductCategoryName" /> </SelectParameters> </asp:SqlDataSource>
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>