ObjectDataSource 源对象示例
更新：2007 年 11 月
阐释可以和 ObjectDataSource 控件一起使用的自定义中间层业务对象。
示例
说明
下面的代码示例阐释可以和 ObjectDataSource 控件一起使用的自定义中间层业务对象。本主题还阐释了一个将业务对象用作 ObjectDataSource 控件的源的示例 ASP.NET 页。该页包含与 ObjectDataSource 控件绑定的 GridView 控件和 DetailsView 控件。
若要使用该代码，您可以在 Web 应用程序的 App_Code 子目录中创建一个代码文件，然后将代码复制到该文件。然后，业务对象将被动态编译并作为 Web 应用程序的一部分包含在应用程序中。或者，您可以编译业务对象，然后将它放入 ASP.NET 应用程序的 Bin 目录中或全局程序集缓存 (GAC) 中。有关 App_Code 和 Bin 目录的更多信息，请参见 ASP.NET 网站中的共享代码文件夹。
代码
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Web.UI
Imports System.Web.UI.WebControls
Namespace Samples.AspNet.ObjectDataSource
'
' Northwind Employee Data Factory
'
Public Class NorthwindData
Private _connectionString As String
Public Sub New()
Initialize()
End Sub
Public Sub Initialize()
' Initialize data source. Use "Northwind" connection string from configuration.
If ConfigurationManager.ConnectionStrings("Northwind") Is Nothing OrElse _
ConfigurationManager.ConnectionStrings("Northwind").ConnectionString.Trim() = "" Then
Throw New Exception("A connection string named 'Northwind' with a valid connection string " & _
"must exist in the <connectionStrings> configuration section for the application.")
End If
_connectionString = _
ConfigurationManager.ConnectionStrings("Northwind").ConnectionString
End Sub
' Select all employees.
Public Function GetAllEmployees(sortColumns As String, startRecord As Integer, maxRecords As Integer) As DataTable
VerifySortColumns(sortColumns)
Dim sqlCmd As String = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees "
If sortColumns.Trim() = "" Then
sqlCmd &= "ORDER BY EmployeeID"
Else
sqlCmd &= "ORDER BY " & sortColumns
End If
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim da As SqlDataAdapter = New SqlDataAdapter(sqlCmd, conn)
Dim ds As DataSet = New DataSet()
Try
conn.Open()
da.Fill(ds, startRecord, maxRecords, "Employees")
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
Return ds.Tables("Employees")
End Function
Public Function SelectCount() As Integer
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM Employees", conn)
Dim result As Integer = 0
Try
conn.Open()
result = CInt(cmd.ExecuteScalar())
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
Return result
End Function
'''''
' Verify that only valid columns are specified in the sort expression to aSub a SQL Injection attack.
Private Sub VerifySortColumns(sortColumns As String)
If sortColumns.ToLowerInvariant().EndsWith(" desc") Then _
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5)
Dim columnNames() As String = sortColumns.Split(",")
For Each columnName As String In columnNames
Select Case columnName.Trim().ToLowerInvariant()
Case "employeeid"
Case "lastname"
Case "firstname"
Case ""
Case Else
Throw New ArgumentException("SortColumns contains an invalid column name.")
End Select
Next
End Sub
' Select an employee.
Public Function GetEmployee(EmployeeID As Integer) As DataTable
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim da As SqlDataAdapter = _
New SqlDataAdapter("SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode " & _
" FROM Employees WHERE EmployeeID = @EmployeeID", conn)
da.SelectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID
Dim ds As DataSet = New DataSet()
Try
conn.Open()
da.Fill(ds, "Employees")
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
Return ds.Tables("Employees")
End Function
' Delete the Employee by ID.
Public Function DeleteEmployee(EmployeeID As Integer) As Integer
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim cmd As SqlCommand = New SqlCommand("DELETE FROM Employees WHERE EmployeeID = @EmployeeID", conn)
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID
Dim result As Integer = 0
Try
conn.Open()
result = cmd.ExecuteNonQuery()
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
Return result
End Function
' Update the Employee by original ID.
Public Function UpdateEmployee(EmployeeID As Integer, LastName As String, FirstName As String, _
Address As String, City As String, Region As String, _
PostalCode As String) As Integer
If String.IsNullOrEmpty(FirstName) Then _
Throw New ArgumentException("FirstName cannot be null or an empty string.")
If String.IsNullOrEmpty(LastName) Then _
Throw New ArgumentException("LastName cannot be null or an empty string.")
If Address Is Nothing Then Address = String.Empty
If City Is Nothing Then City = String.Empty
If Region Is Nothing Then Region = String.Empty
If PostalCode Is Nothing Then PostalCode = String.Empty
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim cmd As SqlCommand = New SqlCommand("UPDATE Employees " & _
" SET FirstName=@FirstName, LastName=@LastName, " & _
" Address=@Address, City=@City, Region=@Region, " & _
" PostalCode=@PostalCode " & _
" WHERE EmployeeID=@EmployeeID", conn)
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID
Dim result As Integer = 0
Try
conn.Open()
result = cmd.ExecuteNonQuery()
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
Return result
End Function
' Insert an Employee.
Public Function InsertEmployee(LastName As String, FirstName As String, Address As String, _
City As String, Region As String, PostalCode As String) As Integer
If String.IsNullOrEmpty(FirstName) Then _
Throw New ArgumentException("FirstName cannot be null or an empty string.")
If String.IsNullOrEmpty(LastName) Then _
Throw New ArgumentException("LastName cannot be null or an empty string.")
If Address Is Nothing Then Address = String.Empty
If City Is Nothing Then City = String.Empty
If Region Is Nothing Then Region = String.Empty
If PostalCode Is Nothing Then PostalCode = String.Empty
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim cmd As SqlCommand = New SqlCommand("INSERT INTO Employees " & _
" (FirstName, LastName, Address, City, Region, PostalCode) " & _
" Values(@FirstName, @LastName, @Address, @City, @Region, @PostalCode) " & _
"SELECT @EmployeeID = SCOPE_IDENTITY()", conn)
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode
Dim p As SqlParameter = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int)
p.Direction = ParameterDirection.Output
Dim newEmployeeID As Integer = 0
Try
conn.Open()
cmd.ExecuteNonQuery()
newEmployeeID = CInt(p.Value)
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
Return newEmployeeID
End Function
'
' Methods that support Optimistic Concurrency checks.
'
' Delete the Employee by ID.
Public Function DeleteEmployee(original_EmployeeID As Integer, original_LastName As String, _
original_FirstName As String, original_Address As String, _
original_City As String, original_Region As String, _
original_PostalCode As String) As Integer
If String.IsNullOrEmpty(original_FirstName) Then _
Throw New ArgumentException("FirstName cannot be null or an empty string.")
If String.IsNullOrEmpty(original_LastName) Then _
Throw New ArgumentException("LastName cannot be null or an empty string.")
If original_Address Is Nothing Then original_Address = String.Empty
If original_City Is Nothing Then original_City = String.Empty
If original_Region Is Nothing Then original_Region = String.Empty
If original_PostalCode Is Nothing Then original_PostalCode = String.Empty
Dim sqlCmd As String = "DELETE FROM Employees WHERE EmployeeID = @original_EmployeeID " & _
" AND LastName = @original_LastName AND FirstName = @original_FirstName " & _
" AND Address = @original_Address AND City = @original_City " & _
" AND Region = @original_Region AND PostalCode = @original_PostalCode"
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim cmd As SqlCommand = New SqlCommand(sqlCmd, conn)
cmd.Parameters.Add("@original_EmployeeID", SqlDbType.Int).Value = original_EmployeeID
cmd.Parameters.Add("@original_FirstName", SqlDbType.VarChar, 10).Value = original_FirstName
cmd.Parameters.Add("@original_LastName", SqlDbType.VarChar, 20).Value = original_LastName
cmd.Parameters.Add("@original_Address", SqlDbType.VarChar, 60).Value = original_Address
cmd.Parameters.Add("@original_City", SqlDbType.VarChar, 15).Value = original_City
cmd.Parameters.Add("@original_Region", SqlDbType.VarChar, 15).Value = original_Region
cmd.Parameters.Add("@original_PostalCode", SqlDbType.VarChar, 10).Value = original_PostalCode
Dim result As Integer = 0
Try
conn.Open()
result = cmd.ExecuteNonQuery()
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
Return result
End Function
' Update the Employee by original ID.
Public Function UpdateEmployee(EmployeeID As Integer, LastName As String, FirstName As String, _
Address As String, City As String, Region As String, _
PostalCode As String, _
original_EmployeeID As Integer, original_LastName As String, _
original_FirstName As String, original_Address As String, _
original_City As String, original_Region As String, _
original_PostalCode As String) As Integer
If String.IsNullOrEmpty(FirstName) Then _
Throw New ArgumentException("FirstName cannot be null or an empty string.")
If String.IsNullOrEmpty(LastName) Then _
Throw New ArgumentException("LastName cannot be null or an empty string.")
If Address Is Nothing Then Address = String.Empty
If City Is Nothing Then City = String.Empty
If Region Is Nothing Then Region = String.Empty
If PostalCode Is Nothing Then PostalCode = String.Empty
If original_Address Is Nothing Then original_Address = String.Empty
If original_City Is Nothing Then original_City = String.Empty
If original_Region Is Nothing Then original_Region = String.Empty
If original_PostalCode Is Nothing Then original_PostalCode = String.Empty
Dim sqlCmd As String = "UPDATE Employees " & _
" SET FirstName = @FirstName, LastName = @LastName, " & _
" Address = @Address, City = @City, Region = @Region, " & _
" PostalCode = @PostalCode " * _
" WHERE EmployeeID = @original_EmployeeID " & _
" AND LastName = @original_LastName AND FirstName = @original_FirstName " & _
" AND Address = @original_Address AND City = @original_City " & _
" AND Region = @original_Region AND PostalCode = @original_PostalCode"
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim cmd As SqlCommand = New SqlCommand(sqlCmd, conn)
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode
cmd.Parameters.Add("@original_EmployeeID", SqlDbType.Int).Value = original_EmployeeID
cmd.Parameters.Add("@original_FirstName", SqlDbType.VarChar, 10).Value = original_FirstName
cmd.Parameters.Add("@original_LastName", SqlDbType.VarChar, 20).Value = original_LastName
cmd.Parameters.Add("@original_Address", SqlDbType.VarChar, 60).Value = original_Address
cmd.Parameters.Add("@original_City", SqlDbType.VarChar, 15).Value = original_City
cmd.Parameters.Add("@original_Region", SqlDbType.VarChar, 15).Value = original_Region
cmd.Parameters.Add("@original_PostalCode", SqlDbType.VarChar, 10).Value = original_PostalCode
Dim result As Integer = 0
Try
conn.Open()
result = cmd.ExecuteNonQuery()
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
Return result
End Function
End Class
End Namespace
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Samples.AspNet.ObjectDataSource
{
//
// Northwind Employee Data Factory
//
public class NorthwindData
{
private string _connectionString;
public NorthwindData()
{
Initialize();
}
public void Initialize()
{
// Initialize data source. Use "Northwind" connection string from configuration.
if (ConfigurationManager.ConnectionStrings["Northwind"] == null ||
ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString.Trim() == "")
{
throw new Exception("A connection string named 'Northwind' with a valid connection string " +
"must exist in the <connectionStrings> configuration section for the application.");
}
_connectionString =
ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
}
// Select all employees.
public DataTable GetAllEmployees(string sortColumns, int startRecord, int maxRecords)
{
VerifySortColumns(sortColumns);
string sqlCmd = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees ";
if (sortColumns.Trim() == "")
sqlCmd += "ORDER BY EmployeeID";
else
sqlCmd += "ORDER BY " + sortColumns;
SqlConnection conn = new SqlConnection(_connectionString);
SqlDataAdapter da = new SqlDataAdapter(sqlCmd, conn);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds, startRecord, maxRecords, "Employees");
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return ds.Tables["Employees"];
}
public int SelectCount()
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Employees", conn);
int result = 0;
try
{
conn.Open();
result = (int)cmd.ExecuteScalar();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
//////////
// Verify that only valid columns are specified in the sort expression to avoid a SQL Injection attack.
private void VerifySortColumns(string sortColumns)
{
if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);
string[] columnNames = sortColumns.Split(',');
foreach (string columnName in columnNames)
{
switch (columnName.Trim().ToLowerInvariant())
{
case "employeeid":
break;
case "lastname":
break;
case "firstname":
break;
case "":
break;
default:
throw new ArgumentException("SortColumns contains an invalid column name.");
break;
}
}
}
// Select an employee.
public DataTable GetEmployee(int EmployeeID)
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlDataAdapter da =
new SqlDataAdapter("SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode " +
" FROM Employees WHERE EmployeeID = @EmployeeID", conn);
da.SelectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds, "Employees");
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return ds.Tables["Employees"];
}
// Delete the Employee by ID.
public int DeleteEmployee(int EmployeeID)
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM Employees WHERE EmployeeID = @EmployeeID", conn);
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
// Update the Employee by original ID.
public int UpdateEmployee(int EmployeeID, string LastName, string FirstName,
string Address, string City, string Region, string PostalCode)
{
if (String.IsNullOrEmpty(FirstName))
throw new ArgumentException("FirstName cannot be null or an empty string.");
if (String.IsNullOrEmpty(LastName))
throw new ArgumentException("LastName cannot be null or an empty string.");
if (Address == null) { Address = String.Empty; }
if (City == null) { City = String.Empty; }
if (Region == null) { Region = String.Empty; }
if (PostalCode == null) { PostalCode = String.Empty; }
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("UPDATE Employees " +
" SET FirstName=@FirstName, LastName=@LastName, " +
" Address=@Address, City=@City, Region=@Region, " +
" PostalCode=@PostalCode " +
" WHERE EmployeeID=@EmployeeID", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
// Insert an Employee.
public int InsertEmployee(string LastName, string FirstName,
string Address, string City, string Region, string PostalCode)
{
if (String.IsNullOrEmpty(FirstName))
throw new ArgumentException("FirstName cannot be null or an empty string.");
if (String.IsNullOrEmpty(LastName))
throw new ArgumentException("LastName cannot be null or an empty string.");
if (Address == null) { Address = String.Empty; }
if (City == null) { City = String.Empty; }
if (Region == null) { Region = String.Empty; }
if (PostalCode == null) { PostalCode = String.Empty; }
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO Employees " +
" (FirstName, LastName, Address, City, Region, PostalCode) " +
" Values(@FirstName, @LastName, @Address, @City, @Region, @PostalCode); " +
"SELECT @EmployeeID = SCOPE_IDENTITY()", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode;
SqlParameter p = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
int newEmployeeID = 0;
try
{
conn.Open();
cmd.ExecuteNonQuery();
newEmployeeID = (int)p.Value;
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return newEmployeeID;
}
//
// Methods that support Optimistic Concurrency checks.
//
// Delete the Employee by ID.
public int DeleteEmployee(int original_EmployeeID, string original_LastName,
string original_FirstName, string original_Address,
string original_City, string original_Region,
string original_PostalCode)
{
if (String.IsNullOrEmpty(original_FirstName))
throw new ArgumentException("FirstName cannot be null or an empty string.");
if (String.IsNullOrEmpty(original_LastName))
throw new ArgumentException("LastName cannot be null or an empty string.");
if (original_Address == null) { original_Address = String.Empty; }
if (original_City == null) { original_City = String.Empty; }
if (original_Region == null) { original_Region = String.Empty; }
if (original_PostalCode == null) { original_PostalCode = String.Empty; }
string sqlCmd = "DELETE FROM Employees WHERE EmployeeID = @original_EmployeeID " +
" AND LastName = @original_LastName AND FirstName = @original_FirstName " +
" AND Address = @original_Address AND City = @original_City " +
" AND Region = @original_Region AND PostalCode = @original_PostalCode";
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Parameters.Add("@original_EmployeeID", SqlDbType.Int).Value = original_EmployeeID;
cmd.Parameters.Add("@original_FirstName", SqlDbType.VarChar, 10).Value = original_FirstName;
cmd.Parameters.Add("@original_LastName", SqlDbType.VarChar, 20).Value = original_LastName;
cmd.Parameters.Add("@original_Address", SqlDbType.VarChar, 60).Value = original_Address;
cmd.Parameters.Add("@original_City", SqlDbType.VarChar, 15).Value = original_City;
cmd.Parameters.Add("@original_Region", SqlDbType.VarChar, 15).Value = original_Region;
cmd.Parameters.Add("@original_PostalCode", SqlDbType.VarChar, 10).Value = original_PostalCode;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
// Update the Employee by original ID.
public int UpdateEmployee(int EmployeeID, string LastName, string FirstName,
string Address, string City, string Region, string PostalCode,
int original_EmployeeID, string original_LastName,
string original_FirstName, string original_Address,
string original_City, string original_Region,
string original_PostalCode)
{
if (String.IsNullOrEmpty(FirstName))
throw new ArgumentException("FirstName cannot be null or an empty string.");
if (String.IsNullOrEmpty(LastName))
throw new ArgumentException("LastName cannot be null or an empty string.");
if (Address == null) { Address = String.Empty; }
if (City == null) { City = String.Empty; }
if (Region == null) { Region = String.Empty; }
if (PostalCode == null) { PostalCode = String.Empty; }
if (original_Address == null) { original_Address = String.Empty; }
if (original_City == null) { original_City = String.Empty; }
if (original_Region == null) { original_Region = String.Empty; }
if (original_PostalCode == null) { original_PostalCode = String.Empty; }
string sqlCmd = "UPDATE Employees " +
" SET FirstName = @FirstName, LastName = @LastName, " +
" Address = @Address, City = @City, Region = @Region, " +
" PostalCode = @PostalCode " +
" WHERE EmployeeID = @original_EmployeeID " +
" AND LastName = @original_LastName AND FirstName = @original_FirstName " +
" AND Address = @original_Address AND City = @original_City " +
" AND Region = @original_Region AND PostalCode = @original_PostalCode";
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode;
cmd.Parameters.Add("@original_EmployeeID", SqlDbType.Int).Value = original_EmployeeID;
cmd.Parameters.Add("@original_FirstName", SqlDbType.VarChar, 10).Value = original_FirstName;
cmd.Parameters.Add("@original_LastName", SqlDbType.VarChar, 20).Value = original_LastName;
cmd.Parameters.Add("@original_Address", SqlDbType.VarChar, 60).Value = original_Address;
cmd.Parameters.Add("@original_City", SqlDbType.VarChar, 15).Value = original_City;
cmd.Parameters.Add("@original_Region", SqlDbType.VarChar, 15).Value = original_Region;
cmd.Parameters.Add("@original_PostalCode", SqlDbType.VarChar, 10).Value = original_PostalCode;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
}
}
<%@ 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">
Sub EmployeesDetailsView_ItemInserted(sender As Object, e As DetailsViewInsertedEventArgs)
EmployeesGridView.DataBind()
End Sub
Sub EmployeesDetailsView_ItemUpdated(sender As Object, e As DetailsViewUpdatedEventArgs)
EmployeesGridView.DataBind()
End Sub
Sub EmployeesDetailsView_ItemDeleted(sender As Object, e As DetailsViewDeletedEventArgs)
EmployeesGridView.DataBind()
End Sub
Sub EmployeesGridView_OnSelectedIndexChanged(sender As Object, e As EventArgs)
EmployeeDetailsObjectDataSource.SelectParameters("EmployeeID").DefaultValue = _
EmployeesGridView.SelectedDataKey.Value.ToString()
EmployeesDetailsView.DataBind()
End Sub
Sub EmployeeDetailsObjectDataSource_OnInserted(sender As Object, e As ObjectDataSourceStatusEventArgs)
EmployeeDetailsObjectDataSource.SelectParameters("EmployeeID").DefaultValue = _
e.ReturnValue.ToString()
EmployeesDetailsView.DataBind()
End Sub
Sub EmployeeDetailsObjectDataSource_OnUpdated(sender As Object, e As ObjectDataSourceStatusEventArgs)
If CInt(e.ReturnValue) = 0 Then _
Msg.Text = "Employee was not updated. Please try again."
End Sub
Sub EmployeeDetailsObjectDataSource_OnDeleted(sender As Object, e As ObjectDataSourceStatusEventArgs)
If CInt(e.ReturnValue) = 0 Then _
Msg.Text = "Employee was not deleted. Please try again."
End Sub
Sub Page_Load()
Msg.Text = ""
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>ObjectDataSource Example</title>
</head>
<body>
<form id="form1" runat="server">
<h3>ObjectDataSource Example</h3>
<asp:Label id="Msg" runat="server" ForeColor="Red" />
<asp:ObjectDataSource
ID="EmployeesObjectDataSource"
runat="server"
TypeName="Samples.AspNet.ObjectDataSource.NorthwindData"
SortParameterName="SortColumns"
EnablePaging="true"
SelectCountMethod="SelectCount"
StartRowIndexParameterName="StartRecord"
MaximumRowsParameterName="MaxRecords"
SelectMethod="GetAllEmployees" >
</asp:ObjectDataSource>
<asp:ObjectDataSource
ID="EmployeeDetailsObjectDataSource"
runat="server"
TypeName="Samples.AspNet.ObjectDataSource.NorthwindData"
ConflictDetection="CompareAllValues"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetEmployee"
InsertMethod="InsertEmployee"
UpdateMethod="UpdateEmployee"
DeleteMethod="DeleteEmployee"
OnInserted="EmployeeDetailsObjectDataSource_OnInserted"
OnUpdated="EmployeeDetailsObjectDataSource_OnUpdated"
OnDeleted="EmployeeDetailsObjectDataSource_OnDeleted">
<SelectParameters>
<asp:Parameter Name="EmployeeID" Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>
<table cellspacing="10">
<tr>
<td valign="top">
<asp:GridView ID="EmployeesGridView"
DataSourceID="EmployeesObjectDataSource"
AutoGenerateColumns="false"
AllowSorting="true"
AllowPaging="true"
PageSize="5"
DataKeyNames="EmployeeID"
OnSelectedIndexChanged="EmployeesGridView_OnSelectedIndexChanged"
RunAt="server">
<HeaderStyle backcolor="lightblue" forecolor="black"/>
<Columns>
<asp:ButtonField Text="Details..."
HeaderText="Show Details"
CommandName="Select"/>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" SortExpression="EmployeeID" />
<asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName, FirstName" />
</Columns>
</asp:GridView>
</td>
<td valign="top">
<asp:DetailsView ID="EmployeesDetailsView"
DataSourceID="EmployeeDetailsObjectDataSource"
AutoGenerateRows="false"
EmptyDataText="No records."
DataKeyNames="EmployeeID"
Gridlines="Both"
AutoGenerateInsertButton="true"
AutoGenerateEditButton="true"
AutoGenerateDeleteButton="true"
OnItemInserted="EmployeesDetailsView_ItemInserted"
OnItemUpdated="EmployeesDetailsView_ItemUpdated"
OnItemDeleted="EmployeesDetailsView_ItemDeleted"
RunAt="server">
<HeaderStyle backcolor="Navy" forecolor="White"/>
<RowStyle backcolor="White"/>
<AlternatingRowStyle backcolor="LightGray"/>
<EditRowStyle backcolor="LightCyan"/>
<Fields>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>
<asp:BoundField DataField="FirstName" HeaderText="First Name"/>
<asp:BoundField DataField="LastName" HeaderText="Last Name"/>
<asp:BoundField DataField="Address" HeaderText="Address"/>
<asp:BoundField DataField="City" HeaderText="City"/>
<asp:BoundField DataField="Region" HeaderText="Region"/>
<asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>
</Fields>
</asp:DetailsView>
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ 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">
void EmployeesDetailsView_ItemInserted(Object sender, DetailsViewInsertedEventArgs e)
{
EmployeesGridView.DataBind();
}
void EmployeesDetailsView_ItemUpdated(Object sender, DetailsViewUpdatedEventArgs e)
{
EmployeesGridView.DataBind();
}
void EmployeesDetailsView_ItemDeleted(Object sender, DetailsViewDeletedEventArgs e)
{
EmployeesGridView.DataBind();
}
void EmployeesGridView_OnSelectedIndexChanged(object sender, EventArgs e)
{
EmployeeDetailsObjectDataSource.SelectParameters["EmployeeID"].DefaultValue =
EmployeesGridView.SelectedDataKey.Value.ToString();
EmployeesDetailsView.DataBind();
}
void EmployeeDetailsObjectDataSource_OnInserted(object sender, ObjectDataSourceStatusEventArgs e)
{
EmployeeDetailsObjectDataSource.SelectParameters["EmployeeID"].DefaultValue =
e.ReturnValue.ToString();
EmployeesDetailsView.DataBind();
}
void EmployeeDetailsObjectDataSource_OnUpdated(object sender, ObjectDataSourceStatusEventArgs e)
{
if ((int)e.ReturnValue == 0)
Msg.Text = "Employee was not updated. Please try again.";
}
void EmployeeDetailsObjectDataSource_OnDeleted(object sender, ObjectDataSourceStatusEventArgs e)
{
if ((int)e.ReturnValue == 0)
Msg.Text = "Employee was not deleted. Please try again.";
}
void Page_Load()
{
Msg.Text = "";
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>ObjectDataSource Example</title>
</head>
<body>
<form id="form1" runat="server">
<h3>ObjectDataSource Example</h3>
<asp:Label id="Msg" runat="server" ForeColor="Red" />
<asp:ObjectDataSource
ID="EmployeesObjectDataSource"
runat="server"
TypeName="Samples.AspNet.ObjectDataSource.NorthwindData"
SortParameterName="SortColumns"
EnablePaging="true"
SelectCountMethod="SelectCount"
StartRowIndexParameterName="StartRecord"
MaximumRowsParameterName="MaxRecords"
SelectMethod="GetAllEmployees" >
</asp:ObjectDataSource>
<asp:ObjectDataSource
ID="EmployeeDetailsObjectDataSource"
runat="server"
TypeName="Samples.AspNet.ObjectDataSource.NorthwindData"
ConflictDetection="CompareAllValues"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetEmployee"
InsertMethod="InsertEmployee"
UpdateMethod="UpdateEmployee"
DeleteMethod="DeleteEmployee"
OnInserted="EmployeeDetailsObjectDataSource_OnInserted"
OnUpdated="EmployeeDetailsObjectDataSource_OnUpdated"
OnDeleted="EmployeeDetailsObjectDataSource_OnDeleted">
<SelectParameters>
<asp:Parameter Name="EmployeeID" Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>
<table cellspacing="10">
<tr>
<td valign="top">
<asp:GridView ID="EmployeesGridView"
DataSourceID="EmployeesObjectDataSource"
AutoGenerateColumns="false"
AllowSorting="true"
AllowPaging="true"
PageSize="5"
DataKeyNames="EmployeeID"
OnSelectedIndexChanged="EmployeesGridView_OnSelectedIndexChanged"
RunAt="server">
<HeaderStyle backcolor="lightblue" forecolor="black"/>
<Columns>
<asp:ButtonField Text="Details..."
HeaderText="Show Details"
CommandName="Select"/>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" SortExpression="EmployeeID" />
<asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName, FirstName" />
</Columns>
</asp:GridView>
</td>
<td valign="top">
<asp:DetailsView ID="EmployeesDetailsView"
DataSourceID="EmployeeDetailsObjectDataSource"
AutoGenerateRows="false"
EmptyDataText="No records."
DataKeyNames="EmployeeID"
Gridlines="Both"
AutoGenerateInsertButton="true"
AutoGenerateEditButton="true"
AutoGenerateDeleteButton="true"
OnItemInserted="EmployeesDetailsView_ItemInserted"
OnItemUpdated="EmployeesDetailsView_ItemUpdated"
OnItemDeleted="EmployeesDetailsView_ItemDeleted"
RunAt="server">
<HeaderStyle backcolor="Navy" forecolor="White"/>
<RowStyle backcolor="White"/>
<AlternatingRowStyle backcolor="LightGray"/>
<EditRowStyle backcolor="LightCyan"/>
<Fields>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>
<asp:BoundField DataField="FirstName" HeaderText="First Name"/>
<asp:BoundField DataField="LastName" HeaderText="Last Name"/>
<asp:BoundField DataField="Address" HeaderText="Address"/>
<asp:BoundField DataField="City" HeaderText="City"/>
<asp:BoundField DataField="Region" HeaderText="Region"/>
<asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>
</Fields>
</asp:DetailsView>
</td>
</tr>
</table>
</form>
</body>
</html>
注释
该示例需要 SQL Server 上的示例 Northwind 数据库的连接字符串。该连接字符串必须在应用程序的配置文件的 <connectionStrings> 元素中定义。connectionStrings 节可能看起来类似于下面的示例：
<configuration>
<system.web>
<connectionStrings>
<add
name="Northwind"
connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;" />
</connectionStrings>
</system.web>
</configuration>