ASP.Net/ADO.net Database/SqlDataSource — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 11:50, 26 мая 2010
Содержание
- 1 Adding a <DeleteParameters> section to the SqlDataSource control
- 2 Adding an InsertCommand to the SqlDataSource control
- 3 Adding an UpdateCommand to a SqlDataSource control
- 4 Adding delete functionality to the SqlDataSource Control
- 5 Adding the AutoGenerateEditButton attribute to a SqlDataSource control
- 6 Adding the ConflictDetection property to a SqlDataSource control
- 7 Adding the DataSourceMode property to a SqlDataSource control
- 8 Adding UpdateParameters to the SqlDataSource control
- 9 Cache Sql data source (C#)
- 10 Change parameters of SqlDataSource in your own code
- 11 Connection to Sql Express (VB.net)
- 12 Deletion using SqlDataSource Control
- 13 Detecting concurrency errors after updating data (C#)
- 14 Detecting concurrency errors after updating data (VB)
- 15 Executing a Stored Procedure using SqlDataSource Control
- 16 Executing Inline SQL Statements: SelectCommand, InsertCommand, UpdateCommand, DeleteCommand
- 17 Executing Insert, Update, and Delete Commands
- 18 Executing Select Commands
- 19 Filtering SqlDataSource data with a FilterExpression
- 20 Handling Null values in the SqlDataSource Control
- 21 Programmatically adding a SqlDataSource control to the Page
- 22 Programmatically Executing SqlDataSource Commands
- 23 Renaming Parameters passed to a Stored Procedure by handling the SqlDataSource Events
- 24 Set sqlCacheDependency
- 25 SqlDataSource for Oracle
- 26 SqlDataSource Simple
- 27 SqlDataSource Update
- 28 SqlDataSource Update Stored Procedure
- 29 SqlDataSource with Parameters
- 30 SqlDataSource with select command
- 31 Static Sql connection data source (C#)
- 32 Typical SqlDataSource control generated by Visual Studio
Adding a <DeleteParameters> section to the SqlDataSource control
<!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:GridView ID="GridView1"
Runat="server"
DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID"
AutoGenerateColumns="False"
AllowSorting="True"
AllowPaging="True"
AutoGenerateEditButton="true"
AutoGenerateDeleteButton="true">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page"
Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True"
HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"
Visible="False"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country"
SortExpression="CompanyName"
DataNavigateUrlFormatString="http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
DeleteCommand="DELETE From Customers WHERE (CustomerID = @CustomerID)"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Adding an InsertCommand to the SqlDataSource control
<!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:GridView ID="GridView1"
runat="server"
DataSourceId="SqlDataSource1"
AllowPaging="True"
BorderColor="#DEBA84"
BorderStyle="None"
BorderWidth="1px"
BackColor="#DEBA84"
CellSpacing="2"
CellPadding="3"
DataKeyNames="CustomerID"
AutoGenerateSelectButton="True"
AutoGenerateColumns="False"
PageSize="5">
<FooterStyle ForeColor="#8C4510"
BackColor="#F7DFB5"></FooterStyle>
<PagerStyle ForeColor="#8C4510"
HorizontalAlign="Center"></PagerStyle>
<HeaderStyle ForeColor="White"
BackColor="#A55129"
Font-Bold="True"></HeaderStyle>
<Columns>
<asp:BoundField ReadOnly="True"
HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID">
</asp:BoundField>
<asp:BoundField HeaderText="CompanyName"
DataField="CompanyName"
SortExpression="CompanyName">
</asp:BoundField>
<asp:BoundField HeaderText="ContactName"
DataField="ContactName"
SortExpression="ContactName">
</asp:BoundField>
<asp:BoundField HeaderText="ContactTitle"
DataField="ContactTitle"
SortExpression="ContactTitle">
</asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" DataField="Region"
SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White" BackColor="#738A9C"
Font-Bold="True"></SelectedRowStyle>
<RowStyle ForeColor="#8C4510" BackColor="#FFF7E7"></RowStyle>
</asp:GridView>
<p><b>Customer Details:</b></p>
<asp:DetailsView ID="DetailsView1"
runat="server"
DataSourceId="SqlDataSource2"
BorderColor="#DEBA84"
BorderStyle="None"
BorderWidth="1px"
BackColor="#DEBA84"
CellSpacing="2"
CellPadding="3"
AutoGenerateRows="False"
AutoGenerateInsertButton="true"
DataKeyNames="CustomerID">
<FooterStyle ForeColor="#8C4510" BackColor="#F7DFB5"></FooterStyle>
<RowStyle ForeColor="#8C4510" BackColor="#FFF7E7"></RowStyle>
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center"></PagerStyle>
<Fields>
<asp:BoundField ReadOnly="True" HeaderText="CustomerID"
DataField="CustomerID" SortExpression="CustomerID">
</asp:BoundField>
<asp:BoundField HeaderText="CompanyName" DataField="CompanyName"
SortExpression="CompanyName"></asp:BoundField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" DataField="Region"
SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Fields>
<HeaderStyle ForeColor="White" BackColor="#A55129"
Font-Bold="True"></HeaderStyle>
<EditRowStyle ForeColor="White" BackColor="#738A9C"
Font-Bold="True"></EditRowStyle>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
SelectCommand="SELECT * FROM [Customers]"
InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName],
[ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode],
[Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName,
@ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode,
@Country, @Phone, @Fax)" DeleteCommand="DELETE FROM [Customers] WHERE
[CustomerID] = @original_CustomerID"
FilterExpression="CustomerID="{0}""
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>">
<FilterParameters>
<asp:ControlParameter Name="CustomerID" ControlId="GridView1"
PropertyName="SelectedValue"></asp:ControlParameter>
</FilterParameters>
<InsertParameters>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
</InsertParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Adding an UpdateCommand to a SqlDataSource control
<!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:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID" AutoGenerateColumns="False"
AllowSorting="True" AllowPaging="True" PageSize="10">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page" Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True" HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country" SortExpression="CompanyName"
DataNavigateUrlFormatString=
"http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Adding delete functionality to the SqlDataSource Control
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID" AutoGenerateColumns="False"
AllowSorting="True" AllowPaging="True"
AutoGenerateEditButton="true" AutoGenerateDeleteButton="true">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page" Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True"
HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"
Visible="False"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country"
SortExpression="CompanyName"
DataNavigateUrlFormatString = "http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName"
DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle"
DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address"
DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
<DeleteParameters>
<asp:Parameter Name="CustomerID" Type="String">
</asp:Parameter>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Adding the AutoGenerateEditButton attribute to a SqlDataSource control
<!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:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID" AutoGenerateColumns="False"
AllowSorting="True" AllowPaging="True"
AutoGenerateEditButton="true">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page" Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True" HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country" SortExpression="CompanyName"
DataNavigateUrlFormatString=
"http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Adding the ConflictDetection property to a SqlDataSource control
<!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"
SelectCommand="SELECT * FROM [Customers] WHERE ([CustomerID] = @CustomerID)"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
ConflictDetection="CompareAllValues">
<SelectParameters>
<asp:QueryStringParameter Name="CustomerID"
QueryStringField="id" Type="String">
</asp:QueryStringParameter>
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Adding the DataSourceMode property to a SqlDataSource control
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<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"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Adding UpdateParameters to the SqlDataSource control
<!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:GridView ID="GridView1"
Runat="server"
DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID"
AutoGenerateColumns="False"
AllowSorting="True"
AllowPaging="True"
PageSize="10">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page"
Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True"
HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country"
SortExpression="CompanyName"
DataNavigateUrlFormatString="http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Cache Sql data source (C#)
<%@ Page Language="C#" %>
<script runat="server">
protected void dataSelect(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="grdEmployee"
DataSourceID="srcEmployee"
Runat="server" />
<asp:SqlDataSource
id="srcEmployee"
EnableCaching="True"
CacheDuration="3600"
SelectCommand="SELECT * FROM Employee"
ConnectionString="Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";"
Runat="server"
OnSelecting="dataSelect" />
</div>
</form>
</body>
</html>
<%-- server=localhost;Initial Catalog=TT;User Id=sa;Password=yourpassword; --%>
Change parameters of SqlDataSource in your own code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="SqlDataSourceLimits" %>
<!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="sourceEmployees"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees WHERE City=@City"
OnSelecting="sourceEmployees_Selecting">
<SelectParameters>
<asp:ControlParameter ControlID="lstCities"
Name="City"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="sourceEmployeeCities"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT DISTINCT City FROM Employees">
</asp:SqlDataSource>
<asp:DropDownList ID="lstCities"
runat="server"
DataTextField="City"
Width="205px"
AutoPostBack="True" >
</asp:DropDownList><br />
<br />
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="sourceEmployees"
CellPadding="4"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Navy"
GridLines="None"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="Navy" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False"
ReadOnly="True" SortExpression="EmployeeID" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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;
using System.Data.SqlClient;
public partial class SqlDataSourceLimits : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lstCities.DataSource = sourceEmployeeCities.Select(DataSourceSelectArguments.Empty);
lstCities.DataBind();
lstCities.Items.Insert(0, "(Choose a City)");
lstCities.Items.Insert(1, "(All Cities)");
lstCities.SelectedIndex = 0;
}
}
protected void sourceEmployees_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
if ((string)e.rumand.Parameters["@City"].Value == "(Choose a City)")
{
e.Cancel = true;
}
else if ((string)e.rumand.Parameters["@City"].Value == "(All Cities)")
{
e.rumand.rumandText = "SELECT * FROM Employees";
}
}
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
Connection to Sql Express (VB.net)
<%@ Page Language="C#" %>
<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="grdEmployee"
DataSourceID="srcEmployee"
Runat="server" />
<asp:SqlDataSource
id="srcEmployee"
SelectCommand="SELECT * FROM Employee"
ConnectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;
Integrated Security=True;User Instance=True"
Runat="server" />
</div>
</form>
</body>
</html>
Deletion using SqlDataSource Control
<%@ Page Language="C#" %>
<script runat="server">
void deptSource_Deleted(object sender,SqlDataSourceStatusEventArgs e)
{
if (e.Exception == null)
{
if (e.AffectedRows == 1)
{
lblResult.Text = "Record deleted successfully.";
}
else
{
lblResult.Text = "An error occurred during the delete operation.";
}
}
else
{
lblResult.Text = "An error occurred while attempting to delete the row." + e.Exception.Message;
e.ExceptionHandled = true;
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Deletion using SqlDataSource Control</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="deptSource" runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="Select DepartmentID, Name, GroupName, ModifiedDate from HumanResources.Department"
DeleteCommand="Delete from HumanResources.Department Where DepartmentID=@original_DepartmentID"
OldValuesParameterFormatString="original_{0}" OnDeleted="deptSource_Deleted">
<DeleteParameters>
<asp:Parameter Type="Int32" Name="DepartmentID"></asp:Parameter>
</DeleteParameters>
</asp:SqlDataSource>
<asp:GridView ID="deptView"
AutoGenerateColumns="False"
runat="server"
DataSourceID="deptSource"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True"
HeaderStyle-BackColor="blue"
HeaderStyle-ForeColor="White"
DataKeyNames="DepartmentID">
<Columns>
<asp:TemplateField HeaderText="Delete">
<ItemTemplate>
<asp:Button ID="btnDelete" Text="Delete" runat="server"
OnClientClick="return confirm("Are you sure you want to delete this record?");"
CommandName="Delete" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="DepartmentID" DataField="DepartmentID" />
<asp:BoundField HeaderText="Department Name" DataField="Name" />
<asp:BoundField HeaderText="Group Name" DataField="GroupName" />
<asp:BoundField HeaderText="Last Modified Date" DataField="ModifiedDate" />
</Columns>
</asp:GridView>
<asp:Label ID="lblResult" runat="server" ForeColor="DarkRed"/>
</div>
</form>
</body>
</html>
Detecting concurrency errors after updating data (C#)
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.AffectedRows > 0)
Message.Text = "The record has been updated";
else
Message.Text = "Possible concurrency violation";
}
</script>
<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"
SelectCommand="SELECT * FROM [Customers] WHERE ([CustomerID] = @CustomerID)"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
ConflictDetection="CompareAllValues"
OnUpdated="SqlDataSource1_Updated">
<SelectParameters>
<asp:QueryStringParameter Name="CustomerID"
QueryStringField="id"
Type="String">
</asp:QueryStringParameter>
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="Message" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
Detecting concurrency errors after updating data (VB)
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub SqlDataSource1_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
If (e.AffectedRows > 0) Then
Message.Text = "The record has been updated"
Else
Message.Text = "Possible concurrency violation"
End If
End Sub
</script>
<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"
SelectCommand="SELECT * FROM [Customers] WHERE ([CustomerID] = @CustomerID)"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
ConflictDetection="CompareAllValues"
OnUpdated="SqlDataSource1_Updated">
<SelectParameters>
<asp:QueryStringParameter Name="CustomerID"
QueryStringField="id"
Type="String">
</asp:QueryStringParameter>
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="Message" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
Executing a Stored Procedure using SqlDataSource Control
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Executing a Stored Procedure using SqlDataSource Control</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="categoriesSource" runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="GetProductSubCategories" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>
Categories:
<asp:DropDownList runat="server" DataSourceID="categoriesSource"
DataValueField="ProductSubcategoryID" DataTextField="Name" AutoPostBack="true"
ID="lstCategories" />
<asp:SqlDataSource ID="productsSource" runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="GetProductsByCategoryID" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lstCategories" Name="ProductSubcategoryID"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView runat="server" DataSourceID="productsSource"
HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True"
HeaderStyle-BackColor="blue" HeaderStyle-ForeColor="White" />
</div>
</form>
</body>
</html>
Executing Inline SQL Statements: SelectCommand, InsertCommand, UpdateCommand, DeleteCommand
<%@ 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:DetailsView
id="dtlProducts"
DataSourceID="srcProducts"
DataKeyNames="Id"
AllowPaging="true"
AutoGenerateEditButton="true"
AutoGenerateInsertButton="true"
AutoGenerateDeleteButton="true"
AutoGenerateRows="false"
CssClass="detailsView"
PagerSettings-Mode="NumericFirstLast"
Runat="server">
<Fields>
<asp:BoundField DataField="Id"
HeaderText="Product Id:" ReadOnly="true" InsertVisible="false" />
<asp:BoundField DataField="Title" HeaderText="Product Title:" />
<asp:BoundField DataField="Director" HeaderText="Product Director:" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource
id="srcProducts"
SelectCommand="SELECT Id,Title,Director FROM Products"
InsertCommand="INSERT Products (Title,Director,CategoryId,DateReleased)VALUES (@Title, @Director,0,"12/15/1966")"
UpdateCommand="UPDATE Products SET Title=@Title,Director=@Director WHERE Id=@Id"
DeleteCommand="DELETE Products 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>
Executing Insert, Update, and Delete Commands
<%@ 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 btnAddEntry_Click(object sender, EventArgs e)
{
srcGuestBook.InsertParameters["Name"].DefaultValue = txtName.Text;
srcGuestBook.InsertParameters["Comments"].DefaultValue = txtComments.Text;
srcGuestBook.Insert();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Execute Insert</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label
id="lblName"
Text="Name:"
AssociatedControlId="txtName"
Runat="server" />
<asp:TextBox
id="txtName"
Runat="server" />
<asp:Label
id="lblComments"
Text="Comments:"
AssociatedControlId="txtComments"
Runat="server" />
<asp:TextBox
id="txtComments"
TextMode="MultiLine"
Columns="50"
Rows="2"
Runat="server" />
<asp:Button
id="btnAddEntry"
Text="Add Entry"
Runat="server" OnClick="btnAddEntry_Click" />
<asp:GridView
id="grdGuestBook"
DataSourceId="srcGuestBook"
Runat="server" />
<asp:SqlDataSource
id="srcGuestBook"
ConnectionString="<%$ ConnectionStrings:GuestBook %>"
SelectCommand="SELECT Name,Comments FROM GuestBook
ORDER BY Id DESC"
InsertCommand="INSERT GuestBook (Name,Comments)
VALUES (@Name,@Comments)"
Runat="server">
<InsertParameters>
<asp:Parameter Name="Name" />
<asp:Parameter Name="Comments" />
</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>
Executing Select Commands
<%@ 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>Photo Gallery</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataList
id="dlstImages"
DataSourceID="srcImages"
RepeatColumns="3"
Runat="server">
<ItemTemplate>
<asp:Image ID="Image1"
ImageUrl="<%# String.Format("DynamicImage.ashx?id={0}", Eval("Id")) %>"
Width="250"
Runat="server" />
<%# Eval("Description") %>
</ItemTemplate>
</asp:DataList>
<asp:FormView
id="frmImage"
DataSourceID="srcImages"
DefaultMode="Insert"
Runat="server">
<InsertItemTemplate>
<asp:Label
id="lblImage"
Text="Upload Image:"
AssociatedControlId="upImage"
Runat="server" />
<asp:FileUpload
id="upImage"
FileBytes="<%# Bind("Image") %>"
Runat="server" />
<asp:Label
id="lblDescription"
Text="Description:"
AssociatedControlID="txtDescription"
Runat="server" />
<asp:TextBox
id="txtDescription"
Text="<%# Bind("Description") %>"
TextMode="MultiLine"
Columns="50"
Rows="2"
Runat="server" />
<asp:Button
id="btnInsert"
Text="Add Image"
CommandName="Insert"
Runat="server" />
</InsertItemTemplate>
</asp:FormView>
<asp:SqlDataSource
id="srcImages"
SelectCommand="SELECT ID,Description FROM Images"
InsertCommand="INSERT Images (Image,Description) VALUES (@Image,@Description)"
ConnectionString="<%$ ConnectionStrings:Images %>"
Runat="server" />
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<connectionStrings>
<add name="Images"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
</connectionStrings>
</configuration>
File: DynamicImage.ashx
<%@ WebHandler Language="C#" Class="DynamicImage" %>
using System.Data;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
public class DynamicImage : IHttpHandler
{
public void ProcessRequest (HttpContext context)
{
string imageId = context.Request.QueryString["Id"];
SqlDataSource src = new SqlDataSource();
src.ConnectionString = WebConfigurationManager.ConnectionStrings ["Images"].ConnectionString;
src.SelectCommand = "SELECT Image FROM Images WHERE Id=" + imageId;
DataView view = (DataView)src.Select(DataSourceSelectArguments.Empty);
context.Response.BinaryWrite( (byte[])view[0]["Image"]);
}
public bool IsReusable
{
get {
return false;
}
}
}
Filtering SqlDataSource data with a FilterExpression
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
SelectCommand="SELECT * FROM [Customers]"
FilterExpression="CustomerID="@CustomerID""
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>">
<FilterParameters>
<asp:ControlParameter Name="CustomerID" ControlId="GridView1"
PropertyName="SelectedValue"></asp:ControlParameter>
</FilterParameters>
</asp:SqlDataSource>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Handling Null values in the SqlDataSource Control
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Handling Null values in the SqlDataSource Control</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="categoriesSource"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="Select ProductSubcategoryID, Name from Production.ProductSubcategory">
</asp:SqlDataSource>
Categories:
<asp:DropDownList AppendDataBoundItems="true"
runat="server"
DataSourceID="categoriesSource"
DataValueField="ProductSubcategoryID"
DataTextField="Name"
AutoPostBack="true"
ID="lstCategories">
<asp:ListItem Value="">ALL</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="productsSource"
runat="server"
ProviderName="System.Data.SqlClient"
CancelSelectOnNullParameter="false"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="Select ProductID, Name, ProductNumber, StandardCost from Production.Product WHERE ProductSubcategoryID = IsNull(@ProductSubcategoryID, ProductSubcategoryID)">
<SelectParameters>
<asp:ControlParameter ControlID="lstCategories" Name="ProductSubcategoryID"
ConvertEmptyStringToNull="true" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView runat="server" DataSourceID="productsSource"
HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True"
HeaderStyle-BackColor="blue" HeaderStyle-ForeColor="White" />
</div>
</form>
</body>
</html>
Programmatically adding a SqlDataSource control to the Page
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">
void lstCategories_SelectedIndexChanged(Object sender, EventArgs e)
{
SqlDataSource productsSource = new SqlDataSource();
productsSource.ID = "productsSource";
Page.Controls.Add(productsSource);
productsSource.ConnectionString = WebConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;
productsSource.SelectCommand = "GetProductsByCategoryID";
productsSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
ControlParameter categoryIdParam = new ControlParameter();
categoryIdParam.ControlID = "lstCategories";
categoryIdParam.Name = "ProductSubcategoryID";
categoryIdParam.PropertyName = "SelectedValue";
productsSource.SelectParameters.Clear();
productsSource.SelectParameters.Add(categoryIdParam);
productsSource.Select(DataSourceSelectArguments.Empty);
GridView1.DataSource = productsSource;
GridView1.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Programmatically adding a SqlDataSource control to the Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="categoriesSource" runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="GetProductSubCategories"
SelectCommandType="StoredProcedure">
</asp:SqlDataSource>
Categories:
<asp:DropDownList runat="server"
DataSourceID="categoriesSource"
DataValueField="ProductSubcategoryID"
OnSelectedIndexChanged="lstCategories_SelectedIndexChanged"
DataTextField="Name"
AutoPostBack="true"
ID="lstCategories" />
<asp:GridView ID="GridView1" runat="server"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True"
HeaderStyle-BackColor="blue"
HeaderStyle-ForeColor="White" />
</div>
</form>
</body>
</html>
Programmatically Executing SqlDataSource Commands
<%@ 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 srcGuestBook_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
e.rumand.Parameters.Add(new SqlParameter("@Name", User.Identity.Name));
}
</script>
<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" />
<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" OnInserting="srcGuestBook_Inserting" />
</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>
Renaming Parameters passed to a Stored Procedure by handling the SqlDataSource Events
<%@ Page Language="C#" AutoEventWireup="true"%>
<script runat="server">
void deptSource_Updating(Object sender, System.Web.UI.WebControls.SqlDataSourceCommandEventArgs e)
{
e.rumand.Parameters["@DeptName"].Value = e.rumand.Parameters["@Name"].Value;
e.rumand.Parameters["@DeptGroupName"].Value = e.rumand.Parameters["@GroupName"].Value;
e.rumand.Parameters.Remove(e.rumand.Parameters["@Name"]);
e.rumand.Parameters.Remove(e.rumand.Parameters["@GroupName"]);
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Renaming Parameters passed to a Stored Procedure by handling the SqlDataSource Events</title>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="deptView" AllowSorting="true" AllowPaging="true" Runat="server"
DataSourceID="deptSource" AutoGenerateEditButton="true" DataKeyNames="DepartmentID"
AutoGenerateColumns="False" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True"
HeaderStyle-BackColor="blue" HeaderStyle-ForeColor="White">
<Columns>
<asp:BoundField ReadOnly="true" HeaderText="Department ID" DataField="DepartmentID" SortExpression="DepartmentID" />
<asp:BoundField HeaderText="Name" DataField="Name" SortExpression="Name" />
<asp:BoundField HeaderText="Group Name" DataField="GroupName" SortExpression="GroupName" />
<asp:BoundField HeaderText="ModifiedDate" DataField="ModifiedDate" SortExpression="ModifiedDate" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="deptSource" runat="server"
ConnectionString="<%$ ConnectionStrings:AdventureWorks%>"
SelectCommand= "SELECT DepartmentID, Name, GroupName, ModifiedDate from HumanResources.Department"
UpdateCommand="UpdateDepartment" UpdateCommandType="StoredProcedure" OnUpdating="deptSource_Updating" >
<UpdateParameters>
<asp:Parameter Name="DeptName" Type="String" />
<asp:Parameter Name="DeptGroupName" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
Set sqlCacheDependency
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="Default2" %>
<!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:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="au_id"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="au_id" HeaderText="au_id" ReadOnly="True" SortExpression="au_id" />
<asp:BoundField DataField="au_lname" HeaderText="au_lname" SortExpression="au_lname" />
<asp:BoundField DataField="au_fname" HeaderText="au_fname" SortExpression="au_fname" />
<asp:BoundField DataField="phone" HeaderText="phone" SortExpression="phone" />
<asp:BoundField DataField="address" HeaderText="address" SortExpression="address" />
<asp:BoundField DataField="city" HeaderText="city" SortExpression="city" />
<asp:BoundField DataField="state" HeaderText="state" SortExpression="state" />
<asp:BoundField DataField="zip" HeaderText="zip" SortExpression="zip" />
<asp:CheckBoxField DataField="contract" HeaderText="contract" SortExpression="contract" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:pubsConnectionString2 %>"
SelectCommand="SELECT * FROM [authors]"
EnableCaching="True"
SqlCacheDependency="CommandNotification">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Default.aspx.vb
Partial Class Default2
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim settings As ConnectionStringSettings
settings = _
ConfigurationManager.ConnectionStrings("PubsDatabase")
System.Data.SqlClient.SqlDependency.Start( _
settings.ConnectionString)
End Sub
End Class
File: Web.Config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="PubsDatabase" connectionString="Server=.\SQLEXPRESS;Integrated Security=True;Database=pubs;Persist Security Info=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true"/>
<authentication mode="Windows"/>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add name="Pubs" connectionStringName="PubsDatabase" pollTime="10000"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>
SqlDataSource for Oracle
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Connect Oracle</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView
id="grdOrders"
DataSourceID="srcOrders"
Runat="server" />
<asp:SqlDataSource
id="srcOrders"
ProviderName="System.Data.OracleClient"
SelectCommand="SELECT * FROM Orders"
ConnectionString="Data Source=OracleDB;Integrated Security=yes"
Runat="server" />
</div>
</form>
</body>
</html>
SqlDataSource Simple
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="SqlDataSourceSimple" %>
<!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="sourceEmployees"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees"
OnSelected="sourceEmployees_Selected"/>
<asp:ListBox ID="ListBox1"
runat="server"
DataSourceID="sourceEmployees"
DataTextField="EmployeeID"
Width="145px"/><br />
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="sourceEmployees"
CellPadding="4"
GridLines="None"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID"
EnableSortingAndPagingCallbacks="True"
PageSize="5">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="Navy" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="EmployeeID"
HeaderText="EmployeeID"
InsertVisible="False"
ReadOnly="True"
SortExpression="EmployeeID" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Columns>
</asp:GridView>
<asp:Label ID="lblError"
runat="server"
EnableViewState="False"
Font-Bold="True"
Font-Names="Verdana"
Font-Size="Small" ForeColor="Red"/>
</div>
</form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 SqlDataSourceSimple : System.Web.UI.Page
{
protected void sourceEmployees_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.Exception != null)
{
lblError.Text = "An exception occured performing the query.";
e.ExceptionHandled = true;
}
}
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
SqlDataSource Update
<%@ 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="sourceEmployees"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees"
UpdateCommand="UPDATE Employees SET FirstName=@FirstName, LastName=@LastName, Title=@Title, City=@City FROM Employees WHERE EmployeeID=@EmployeeID"
UpdateCommandType="StoredProcedure"/>
<br />
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="sourceEmployees"
CellPadding="4"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Navy"
GridLines="None"
DataKeyNames="EmployeeID"
EnableSortingAndPagingCallbacks="True"
PageSize="5">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="Navy" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
SqlDataSource Update Stored Procedure
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Default" %>
<!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="sourceEmployees"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees"
UpdateCommand="UpdateEmployee"
UpdateCommandType="StoredProcedure"
OldValuesParameterFormatString="{0}"
OnUpdating="sourceEmployees_Updating">
<UpdateParameters>
<asp:Parameter Name="First" Type="String" />
<asp:Parameter Name="Last" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="sourceEmployees"
CellPadding="4"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Navy"
GridLines="None"
DataKeyNames="EmployeeID"
EnableSortingAndPagingCallbacks="True"
PageSize="5">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="Navy" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 Default : System.Web.UI.Page
{
protected void sourceEmployees_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
e.rumand.Parameters["@First"].Value = e.rumand.Parameters["@FirstName"].Value;
e.rumand.Parameters["@Last"].Value = e.rumand.Parameters["@LastName"].Value;
e.rumand.Parameters.Remove(e.rumand.Parameters["@FirstName"]);
e.rumand.Parameters.Remove(e.rumand.Parameters["@LastName"]);
}
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
SqlDataSource 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>
<asp:SqlDataSource ID="sourceEmployees"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees WHERE City=@City">
<SelectParameters>
<asp:ControlParameter ControlID="lstCities"
Name="City"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="sourceEmployeeCities"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT DISTINCT City FROM Employees">
</asp:SqlDataSource>
<asp:DropDownList ID="lstCities"
runat="server"
DataSourceID="sourceEmployeeCities"
DataTextField="City"
Width="205px"
AutoPostBack="True">
</asp:DropDownList><br />
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="sourceEmployees"
CellPadding="4"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Navy"
GridLines="None"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="Navy" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False"
ReadOnly="True" SortExpression="EmployeeID" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
SqlDataSource with select command
<%@ 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="sourceEmployees"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees" >
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="sourceEmployees"
CellPadding="4"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Navy"
GridLines="None"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID"
EnableSortingAndPagingCallbacks="True"
PageSize="5">
<FooterStyle BackColor="#990000"
Font-Bold="True"
ForeColor="White" />
<RowStyle BackColor="#FFFBD6"
ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66"
ForeColor="Navy"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66"
Font-Bold="True"
ForeColor="Navy" />
<HeaderStyle BackColor="#990000"
Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="ID" />
<asp:BoundField DataField="FirstName" HeaderText="First Name" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" />
<asp:BoundField DataField="Title" HeaderText="Title" />
<asp:BoundField DataField="City" HeaderText="City" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
Static Sql connection data source (C#)
<%@ Page Language="C#" %>
<script runat="server">
protected void dataSelect(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="grdEmployee"
DataSourceID="srcEmployee"
Runat="server" />
<asp:SqlDataSource
id="srcEmployee"
EnableCaching="True"
CacheDuration="3600"
SelectCommand="SELECT * FROM Employee"
ConnectionString="Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";"
Runat="server"
OnSelecting="dataSelect" />
</div>
</form>
</body>
</html>
<%-- server=localhost;Initial Catalog=TT;User Id=sa;Password=yourpassword; --%>
Typical SqlDataSource control generated by Visual Studio
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<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"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>