ASP.NET Tutorial/ADO.net Database/SqlDataSource — различия между версиями

Версия 15:30, 26 мая 2010

Содержание

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

<%@ 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">
    <div>
        Categories:<br />
        <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>
        <br />
        Products in this category:<br />
        <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>
    </div>
    </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>

Bind asp:SqlDataSource with CheckBoxList and RadioButtonList (VB.net)

<%@ 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">
    <div>
        <asp:SqlDataSource 
             ID="SqlDataSource1" 
             runat="server" 
             ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT * FROM [Books]"></asp:SqlDataSource>
    
    </div>
        CheckBoxList:
        <asp:CheckBoxList ID="CheckBoxList1" 
                          runat="server" 
                          DataSourceID="SqlDataSource1"
                          DataTextField="Title" 
                          DataValueField="BookID">
        </asp:CheckBoxList><br />
        RadioButtonList:<br />
        <asp:RadioButtonList ID="RadioButtonList1" 
                             runat="server" 
                             DataSourceID="SqlDataSource1"
                             DataTextField="Title" 
                             DataValueField="BookID">
        </asp:RadioButtonList><br />
        <asp:Button ID="Button1" runat="server" Text="Click Me!" />
        <br />
        <br />
        <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 & "<br/>"
            End If
        Next
        results.Text &= "<br/><br/><hr/>"

        If RadioButtonList1.SelectedItem IsNot Nothing Then
            results.Text &= RadioButtonList1.SelectedItem.Text
        End If
    End Sub
End Class

Caching Database Data with the SqlDataSource Control

<%@ 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">
    <div>
    <asp:Label
        id="lblMessage"
        EnableViewState="false"
        Runat="server" />
    <br /><br />
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <!-- 3600 is (one hour) -->
    <asp:SqlDataSource
        id="srcProducts"
        EnableCaching="True"
        CacheDuration="3600"
        SelectCommand="SELECT * FROM Products"
        ConnectionString="<%$ ConnectionStrings:Products %>"
        Runat="server" OnSelecting="srcProducts_Selecting" />
    </div>
    </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>

Canceling Command Execution when Deleting, Filtering, Inserting, Selecting, Updating

<%@ 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">
    <div>
    <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" />
    </div>
    </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>

Configure two asp:SqlDataSource with different SelectCommand

<%@ 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">
    <div>
        <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>
        <br />
        <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>
        
    </div>
    </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>

Connecting to Microsoft SQL Server

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">
    <div>
    <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" />

    </div>
    </form>
</body>
</html>

Connection to a mdf file

<%@ 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">
    <div>
        <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>
    
    </div>
    </form>
</body>
</html>

Declare each of the parameters used when executing the update command.

<%@ 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">
    <div>
    <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>
    </div>
    </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>

Executing Stored Procedures

<%@ 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">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        CssClass="gridView"
        Runat="server" />
    <asp:SqlDataSource
        id="srcProducts"
        SelectCommand="CountProductsInCategory"
        SelectCommandType="StoredProcedure"
        ConnectionString="<%$ ConnectionStrings:Products %>"
        Runat="server" />
    </div>
    </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>

Filtering Database Rows

<%@ 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">
    <div>
    <asp:TextBox
        id="txtTitle"
        Runat="server" />
    <asp:Button
        id="btnMatch"
        Text="Match"
        Runat="server" />
    <hr />
    <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>
    </div>
    </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>

Handle the exception at the level of a DataBound control.

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">
    <div>
    <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" />
    </div>
    </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>

Handling SQL Command Execution Errors

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">
    <div>
    <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" />
    </div>
    </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>

Link asp:DropDownList with asp:SqlDataSource

<%@ 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">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" 
                           runat="server" 
                           ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT DISTINCT [Genre] FROM [Books] ORDER BY [Genre]"></asp:SqlDataSource>
        &nbsp;</div>
        Choose a genre:
        <asp:DropDownList ID="genres" 
                          runat="server" 
                          AutoPostBack="True" 
                          DataSourceID="SqlDataSource1"
                          DataTextField="Genre" 
                          DataValueField="Genre">
        </asp:DropDownList><br />
        
        <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>

Link asp:GridView with asp:SqlDataSource

<%@ 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">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" 
                           runat="server" 
                           ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT * FROM [Books]"></asp:SqlDataSource>
    
    </div>
        <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>

Retrieves the records from the database by using a DataReader

<%@ 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">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <asp:SqlDataSource
        id="srcProducts"
        DataSourceMode="DataReader"
        SelectCommand="SELECT * FROM Products"
        ConnectionString="<%$ ConnectionStrings:Products %>"
        Runat="server" />
    </div>
    </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>

Show Page Control Parameter

<%@ 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">
    <div>
    <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" />
        <br /><br />
        <asp:Label
            id="Label1"
            Text="Your Comments:"
            AssociatedControlID="txtComments"
            Runat="server" />
        <br />
        <asp:TextBox
            id="txtComments"
            Text="<%# Bind("Comments") %>"
            TextMode="MultiLine"
            Columns="60"
            Rows="4"
            Runat="server" />
        <br /><br />
        <asp:Button
            id="btnSubmit"
            Text="Submit"
            CommandName="Insert"
            Runat="server" />
        </InsertItemTemplate>
    </asp:FormView>
    <hr />
    <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>
    </div>
    </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>

Specify column from asp:SqlDataSource

<%@ 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">
    <div>
    <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>
        <br />
        <br />
        <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>
                <b>In Stock:</b>
                    <%# Eval("UnitsInStock") %>
                    <br />
                    <b>On Order:</b>
                    <%# Eval("UnitsOnOrder") %>
                    <br />
                    <b>Reorder:</b>
                    <%# Eval("ReorderLevel") %>
                    <br />
                    <br />
                
            </ItemTemplate>
        </asp:FormView>
    </div>
    </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>

SqlDataSource with dynamic parameter

<%@ 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">
    <div>
       <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>
    </div>
    </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>

Use connection string in asp:SqlDataSource and set the DeleteCommand and SelectCommand

<%@ 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">
    <div>
        <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>
    
    </div>
        <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>

Using ASP.NET Parameters with the SqlDataSource Control

<%@ 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">
    <div>
    <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" />
    </div>
    </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>

Using the ASP.NET ControlParameter Object to represent the value of a control 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 Control Parameter</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:DropDownList
        id="ddlProducts"
        DataSourceID="srcProducts"
        DataTextField="Title"
        DataValueField="Id"
        Runat="server" />
    <asp:Button
        id="btnSelect"
        Text="Select"
        Runat="server" />
    <hr />
    <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>
    </div>
    </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>

Using the ASP.NET CookieParameter Object

<%@ 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">
    <div>
    <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>
        <br />
        <asp:Button
           id="btnSubmit"
           Text="Submit"
           CommandName="Insert"
           Runat="server" />
        </InsertItemTemplate>
    </asp:FormView>
    <hr />
    <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>
    </div>
    </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>

Using the ASP.NET FormParameter Object

<%@ 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">
    <div>
    <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>
    </div>
    </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>

Using the ASP.NET ProfileParameter Object

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">
    <div>
    <asp:FormView
        id="frmGuestBook"
        DataSourceID="srcGuestBook"
        DefaultMode="Insert"
        Runat="server">
        <InsertItemTemplate>
        <asp:Label
            id="lblComments"
            Text="Enter Your Comments:"
            Runat="server" />
        <br />
        <asp:TextBox
            id="txtComments"
            Text="<%# Bind("Comments") %>"
            TextMode="MultiLine"
            Columns="50"
            Rows="4"
            Runat="server" />
        <br />
        <asp:Button
            id="btnInsert"
            Text="Add Comments"
            CommandName="Insert"
            Runat="server" />
        </InsertItemTemplate>
    </asp:FormView>
    <hr />
    <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>
    </div>
    </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>

Using the QueryStringParameter Object

<%@ 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">
    <div>
    <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" />
    </div>
    </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">
    <div>
    <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>
    </div>
    </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>

Using the SessionParameter Object

<%@ 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">
    <div>
    <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>
    </div>
    </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>

ASP.NET Tutorial/ADO.net Database/SqlDataSource — различия между версиями

Версия 15:30, 26 мая 2010

Содержание

asp:SqlDataSource select command with parameters

Bind asp:SqlDataSource with CheckBoxList and RadioButtonList (VB.net)

Caching Database Data with the SqlDataSource Control

Canceling Command Execution when Deleting, Filtering, Inserting, Selecting, Updating

Configure two asp:SqlDataSource with different SelectCommand

Connecting to Microsoft SQL Server

Connection to a mdf file

Declare each of the parameters used when executing the update command.

Executing Stored Procedures

Filtering Database Rows

Handle the exception at the level of a DataBound control.

Handling SQL Command Execution Errors

Link asp:DropDownList with asp:SqlDataSource

Link asp:GridView with asp:SqlDataSource

Retrieves the records from the database by using a DataReader

Show Page Control Parameter

Specify column from asp:SqlDataSource

SqlDataSource with dynamic parameter

Use connection string in asp:SqlDataSource and set the DeleteCommand and SelectCommand

Using ASP.NET Parameters with the SqlDataSource Control

Using the ASP.NET ControlParameter Object to represent the value of a control property.

Using the ASP.NET CookieParameter Object

Using the ASP.NET FormParameter Object

Using the ASP.NET ProfileParameter Object

Using the QueryStringParameter Object

Using the SessionParameter Object

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация

Текущая версия на 11:56, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Версия 15:30, 26 мая 2010 (просмотреть исходный код) (обсуждение) Следующая правка →
(нет различий)