Data Source Controls
by Microsoft
The DataGrid control in ASP.NET 1.x marked a great improvement in data access in Web applications. However, it wasn’t as user-friendly as it could have been. It still required a considerable amount of code to obtain much useful functionality from it. Such is the model in all data access endeavors in 1.x.
The DataGrid control in ASP.NET 1.x marked a great improvement in data access in Web applications. However, it wasn’t as user-friendly as it could have been. It still required a considerable amount of code to obtain much useful functionality from it. Such is the model in all data access endeavors in 1.x.
ASP.NET 2.0 addresses this with in part with data source controls. The data source controls in ASP.NET 2.0 provide developers with a declarative model for retrieving data, displaying data, and editing data. The purpose of data source controls is to provide a consistent representation of data to data-bound controls regardless of the source of those data. At the heart of the data source controls in ASP.NET 2.0 is the DataSourceControl abstract class. The DataSourceControl class provides a base implementation of the IDataSource interface and the IListSource interface, the latter of which allows you to assign the data source control as the DataSource of a data-bound control (via the new DataSourceId property discussed later) and expose the data therein as a list. Each list of data from a data source control is exposed as a DataSourceView object. Access to the DataSourceView instances is provided by the IDataSource interface. For example, the GetViewNames method returns an ICollection that allows you to enumerate the DataSourceViews associated with a particular data source control, and the GetView method allows you to access a particular DataSourceView instance by name.
Data source controls have no user-interface. They are implemented as server controls so that they can support declarative syntax and so that they have access to page state if desired. Data source controls do not render any HTML markup to the client.
[!NOTE] As you’ll see later, there are also caching benefits obtained by using data source controls.
Storing Connection Strings
Before we get into looking at how to configure data source controls, we should cover a new capability in ASP.NET 2.0 concerning connection strings. ASP.NET 2.0 introduces a new section in the configuration file that allows you to easily store connection strings that can be read dynamically at runtime. The <connectionStrings> section makes it easy to store connection strings.
The snippet below adds a new connection string.
[!code-xmlMain]
1: <connectionStrings> <add name="Northwind" connectionString="Data Source=localhost; Integrated Security=SSPI;Initial Catalog=Northwind;" providerName="System.Data.SqlClient" /> </connectionStrings>
[!NOTE] Just as with the <appSettings> section, the <connectionStrings> section appears outside of the <system.web> section in the configuration file.
To use this connection string, you can use the following syntax when setting the ConnectionString attribute of a server control.
[!code-aspxMain]
1: ConnectionString="<%$ ConnectionStrings:Northwind%>"
The <connectionStrings> section can also be encrypted so that sensitive information is not exposed. That ability will be covered in a later module.
Caching Data Sources
Each DataSourceControl provides four properties for configuring caching; EnableCaching, CacheDuration, CacheExpirationPolicy, and CacheKeyDependency.
EnableCaching
EnableCaching is a Boolean property that determines whether or not caching is enabled for the data source control.
CacheDuration Property
The CacheDuration property sets the number of seconds that the cache remains valid. Setting this property to 0 causes the cache to remain valid until explicitly invalidated.
CacheExpirationPolicy Property
The CacheExpirationPolicy property can be set to either Absolute or Sliding. Setting it to Absolute means that the maximum amount of time that the data will be cached is the number of seconds specified by the CacheDuration property. By setting it to Sliding, the expiration time is reset when each operation is performed.
CacheKeyDependency Property
If a string value is specified for the CacheKeyDependency property, ASP.NET will set up a new cache dependency based on that string. This allows you to explicitly invalidate the cache by simply changing or removing the CacheKeyDependency.
Important: If impersonation is enabled and access to the data source and/or content of data are based upon client identity, it is recommended that caching be disabled by setting EnableCaching to False. If caching is enabled in this scenario and a user other than the user who originally requested the data issues a request, authorization to the data source is not enforced. The data will simply be served from cache.
The SqlDataSource Control
The SqlDataSource control allows a developer to access data stored in any relational database that supports ADO.NET. It can use the System.Data.SqlClient provider to access a SQL Server database, the System.Data.OleDb provider, the System.Data.Odbc provider, or the System.Data.OracleClient provider to access Oracle. Therefore, the SqlDataSource is certainly not only used for accessing data in a SQL Server database.
In order to use the SqlDataSource, you simply provide a value for the ConnectionString property and specify a SQL command or stored procedure. The SqlDataSource control takes care of working with the underlying ADO.NET architecture. It opens the connection, queries the data source or executes the stored procedure, returns the data, and then closes the connection for you.
[!NOTE] Because the DataSourceControl class automatically closes the connection for you, it should reduce the number of customer calls generated by leaking database connections.
The code snippet below binds a DropDownList control to a SqlDataSource control using the connection string that is stored in the configuration file as shown above.
[!code-aspxMain]
1: <asp:SqlDataSource id="SqlDataSource1" runat="server" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings:Northwind%>" SelectCommand="SELECT EmployeeID, LastName FROM Employees"> </asp:SqlDataSource><asp:DropDownList id="ListBox1" runat="server" DataTextField="LastName" DataValueField="EmployeeID" DataSourceID="SqlDataSource1"> </asp:DropDownList>
As illustrated above, the DataSourceMode property of the SqlDataSource specifies the mode for the data source. In the example above, the DataSourceMode is set to DataReader. In that case, the SqlDataSource will return an IDataReader object using a forward-only and read-only cursor. The specified type of object that is returned is controlled by the provider that is used. In this case, I’m using the System.Data.SqlClient provider as specified in the <connectionStrings> section of the web.config file. Therefore, the object that is returned will be of type SqlDataReader. By specifying a DataSourceMode value of DataSet, the data can be stored in a DataSet on the server. This mode allows you to add features such as sorting, paging, etc. If I had been data-binding the SqlDataSource to a GridView control, I would have chosen the DataSet mode. However, in the case of a DropDownList, the DataReader mode is the correct choice.
[!NOTE] When caching a SqlDataSource or an AccessDataSource, the DataSourceMode property must be set to DataSet. An exception will occur if you enable caching with a DataSourceMode of DataReader.
SqlDataSource Properties
The following are some of the properties of the SqlDataSource control.
CancelSelectOnNullParameter
A Boolean value that specifies whether a select command is canceled if one of the parameters is null. True by default.
ConflictDetection
In a situation where multiple users may be updating a data source at the same time, the ConflictDetection property determines the behavior of the SqlDataSource control. This property evaluates to one of the values of the ConflictOptions enumeration. Those values are CompareAllValues and OverwriteChanges. If set to OverwriteChanges, the last person to write data to the data source overwrites any previous changes. However, if the ConflictDetection property is set to CompareAllValues, parameters get created for the columns returned by the SelectCommand and parameters are also created to hold the original values in each of those columns allowing the SqlDataSource to determine whether or not the values have changed since the SelectCommand was executed.
DeleteCommand
Sets or gets the SQL string used when deleting rows from the database. This can either be a SQL query or a stored procedure name.
DeleteCommandType
Sets or gets the type of delete command, either a SQL query (Text) or a stored procedure (StoredProcedure).
DeleteParameters
Returns the parameters that are used by the DeleteCommand of the SqlDataSourceView object associated with the SqlDataSource control.
OldValuesParameterFormatString
This property is used to specify the format of the original value parameters in cases where the ConflictDetection property is set to CompareAllValues. The default is {0} which means that original value parameters will take the same name as the original parameter. In other words, if the field name is EmployeeID, the original value parameter would be @EmployeeID.
SelectCommand
Sets or gets the SQL string that is used to retrieve data from the database. This can either be a SQL query or a stored procedure name.
SelectCommandType
Sets or gets the type of select command, either a SQL query (Text) or a stored procedure (StoredProcedure).
SelectParameters
Returns the parameters that are used by the SelectCommand of the SqlDataSourceView object associated with the SqlDataSource control.
SortParameterName
Gets or sets the name of a stored procedure parameter that is used when sorting data retrieved by the data source control. Valid only when SelectCommandType is set to StoredProcedure.
SqlCacheDependency
A semi-colon delimited string specifying the databases and tables used in a SQL Server cache dependency. (SQL cache dependencies will be discussed in a later module.)
UpdateCommand
Sets or gets the SQL string that is used when updating data in the database. This can either be a SQL query or a stored procedure name.
UpdateCommandType
Sets or gets the type of update command, either a SQL query (Text) or a stored procedure (StoredProcedure).
UpdateParameters
Returns the parameters that are used by the UpdateCommand of the SqlDataSourceView object associated with the SqlDataSource control.
The AccessDataSource Control
The AccessDataSource control derives from the SqlDataSource class and is used to data-bind to a Microsoft Access database. The ConnectionString property for the AccessDataSource control is a read-only property. Instead of using the ConnectionString property, the DataFile property is used to point to the Access Database as shown below.
[!code-aspxMain]
1: <asp:AccessDataSource id="AccessDataSource1" runat="server" DataFile="~/App_Data/Northwind.mdb"> </asp:AccessDataSource>
The AccessDataSource will always set the ProviderName of the base SqlDataSource to System.Data.OleDb and connects to the database using the Microsoft.Jet.OLEDB.4.0 OLE DB provider. You cannot use the AccessDataSource control to connect to a password-protected Access database. If you have to connect to a password protected database, you should use the SqlDataSource control.
[!NOTE] Access databases stored within the Web site should be placed in the App_Data directory. ASP.NET does not allow files in this directory to be browsed. You will need to grant the process account Read and Write permissions to the App_Data directory when using Access databases.
The XmlDataSource Control
The XmlDataSource is used to data-bind XML data to data-bound controls. You can bind to an XML file using the DataFile property or you can bind to an XML string using the Data property. The XmlDataSource exposes XML attributes as bindable fields. In cases where you need to bind to values that are not represented as attributes, you will need to use an XSL transform. You can also use XPath expressions to filter XML data.
Consider the following XML file:
[!code-xmlMain]
1: <?xml version="1.0" encoding="utf-8" ?> <People> <Person FirstName="Jake" LastName="Stone"> <Address> <Street>345 Maple St.</Street> <City>Redmond</City> <Region>WA</Region> <ZipCode>01434</ZipCode> </Address> <Job> <Title>CEO</Title> <Description>Develops company strategies.</Description> </Job> </Person> <Person FirstName="Jacob" LastName="Ladder"> <Address> <Street>123 Elm St.</Street> <City>Seattle</City> <Region>WA</Region> <ZipCode>11223</ZipCode> </Address> <Job> <Title>Attorney</Title> <Description>Reviews legal issues.</Description> </Job> </Person> <Person FirstName="Angela" LastName="Hound"> <Address> <Street>34 Palm Avenue</Street> <City>Renton</City> <Region>WA</Region> <ZipCode>63910</ZipCode> </Address> <Job> <Title>IT Director</Title> <Description>In charge of corporate network.</Description> </Job> </Person> </People>
Notice that the XmlDataSource uses an XPath property of People/Person in order to filter on just the <Person> nodes. The DropDownList then data-binds to the LastName attribute using the DataTextField property.
While the XmlDataSource control is primarily used to data-bind to read-only XML data, it is possible to edit the XML data file. Note that in such cases, automatic insertion, updating, and deletion of information in the XML file does not happen automatically as it does with other data source controls. Instead, you will have to write code to manually edit the data using the following methods of the XmlDataSource control.
GetXmlDocument
Retrieves an XmlDocument object containing the XML code retrieved by the XmlDataSource.
Save
Saves the in-memory XmlDocument back to the data source.
It’s important to realize that the Save method will only work when the following two conditions are met:
- The XmlDataSource is using the DataFile property to bind to an XML file instead of the Data property to bind to in-memory XML data.
- No transformation is specified via the Transform or TransformFile property.
Note also that the Save method can yield unexpected results when called by multiple users concurrently.
The ObjectDataSource Control
The data source controls that we have covered up to this point are excellent choices for two-tier applications where the data source control communicates directly to the data store. However, many real-world applications are multi-tier applications where a data source control might need to communicate to a business object which, in turn, communicates with the data layer. In these situations, the ObjectDataSource fills the bill nicely. The ObjectDataSource works in conjunction with a source object. The ObjectDataSource control will create an instance of the source object, call the specified method, and dispose of the object instance all within the scope of a single request, if your object has instance methods instead of static methods (Shared in Visual Basic). Therefore, your object must be stateless. That is, your object should acquire and release all required resources within the span of a single request. You can control how the source object is created by handling the ObjectCreating event of the ObjectDataSource control. You can create an instance of the source object, and then set the ObjectInstance property of the ObjectDataSourceEventArgs class to that instance. The ObjectDataSource control will use the instance that is created in the ObjectCreating event instead of creating an instance on its own.
If the source object for an ObjectDataSource control exposes public static methods (Shared in Visual Basic) that can be called to retrieve and modify data, an ObjectDataSource control will call those methods directly. If an ObjectDataSource control must create an instance of the source object in order to make method calls, the object must include a public constructor that takes no parameters. The ObjectDataSource control will call this constructor when it creates a new instance of the source object.
If the source object does not contain a public constructor without parameters, you can create an instance of the source object that will be used by the ObjectDataSource control in the ObjectCreating event.
Specifying Object Methods
The source object for an ObjectDataSource control can contain any number of methods that are used to select, insert, update, or delete data. These methods are called by the ObjectDataSource control based on the name of the method, as identified by using either the SelectMethod, InsertMethod, UpdateMethod, or DeleteMethod property of the ObjectDataSource control. The source object can also include an optional SelectCount method, which is identified by the ObjectDataSource control using the SelectCountMethod property, that returns the count of the total number of objects at the data source. The ObjectDataSource control will call the SelectCount method after a Select method has been called to retrieve the total number of records at the data source for use when paging.
Lab Using Data Source Controls
Exercise 1 - Displaying Data with the SqlDataSource Control
The following exercise uses the SqlDataSource control to connect to the Northwind database. It assumes that you have access to the Northwind database on a SQL Server 2000 instance.
- Create a new ASP.NET Web site.
Add a new web.config file.
- Right-click on the project in Solution Explorer and click Add New Item.
- Choose Web Configuration File from the list of templates and click Add.
Edit the <connectionStrings> section as follows:
[!code-aspxMain]1: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
2: ConnectionString="<%$ConnectionStrings:Northwind%>"
3: SelectCommand="SELECT * FROM Products">
4: </asp:SqlDataSource>
Switch to Code view and add a ConnectionString attribute and a SelectCommand attribute to the <asp:SqlDataSource> control as follows:
[!code-aspxMain]1: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
2: ConnectionString="<%$ConnectionStrings:Northwind%>"
3: SelectCommand="SELECT * FROM Products">
4: </asp:SqlDataSource>
- From Design view, add a new GridView control.
- From the Choose Data Source dropdown in the GridView Tasks menu, choose SqlDataSource1.
- Right-click on Default.aspx and choose View in Browser from the menu. Click Yes when prompted to save.
The GridView displays the data from the Products table.
Exercise 2 - Editing Data with the SqlDataSource Control
The following exercise demonstrates how to data bind a DropDownList control using the declarative syntax and allows you to edit the data presented in the DropDownList control.
In Design view, delete the GridView control from Default.aspx.
Important: Leave the SqlDataSource control on the page.- Add a DropDownList control to Default.aspx.
- Switch to Source view.
Add a DataSourceId, DataTextField, and DataValueField attribute to the <asp:DropDownList> control as follows:
[!code-aspxMain]1: <asp:DropDownList ID="ddlProducts" runat="server"
2: DataSourceId="SqlDataSource1" DataTextField="ProductName"
3: DataValueField="ProductID">
4: </asp:DropDownList>
- Save Default.aspx and view it in the browser. Note that the DropDownList contains all of the products from the Northwind database.
- Close the browser.
- In Source view of Default.aspx, add a new TextBox control below the DropDownList control. Change the ID property of the TextBox to txtProductName.
- Under the TextBox control, add a new Button control. Change the ID property of the Button to btnUpdate and the Text property to Update Product Name.
In Source view of Default.aspx, add an UpdateCommand property and two new UpdateParameters to the SqlDataSource tag as follows:
[!code-aspxMain]
1: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
2: ConnectionString="<%$ConnectionStrings:Northwind%>"
3: SelectCommand="SELECT * FROM Products"
4: UpdateCommand="UPDATE Products SET ProductName=@ProductName WHERE ProductID=@ProductID">
5: <UpdateParameters>
6: <asp:ControlParameter Name="ProductName"
7: ControlID="txtProductName" PropertyName="Text" />
8: <asp:ControlParameter Name="ProductID"
9: ControlID="ddlProducts" PropertyName="SelectedValue" />
10: </asp:SqlDataSource>
[!NOTE] Note that there are two update parameters (ProductName and ProductID) added in this code. These parameters are mapped to the Text property of the txtProductName TextBox and the SelectedValue property of the ddlProducts DropDownList.
- Switch to Design view and double-click on the Button control to add an event handler.
Add the following code to the btnUpdate_Click code:
[!code-csharpMain]1: SqlDataSource1.Update();- Right-click on Default.aspx and choose to view it in the browser. Click Yes when prompted to save all changes.
- ASP.NET 2.0 partial classes allow for compilation at runtime. It is not necessary to build an application in order to see code changes take effect.
- Select a product from the DropDownList.
- Enter a new name for the selected product in the TextBox and then click the Update button.
The product name is updated in the database.
Exercise 3 Using the ObjectDataSource Control
This exercise will demonstrate how to use the ObjectDataSource control and a source object to interact with the Northwind database.
- Right-click on the project in Solution Explorer and click on Add New Item.
- Select Web Form in the templates list. Change the name to object.aspx and click Add.
- Right-click on the project in Solution Explorer and click on Add New Item.
- Select Class in the templates list. Change the name of the class to NorthwindData.cs and click Add.
- Click Yes when prompted to add the class to the App_Code folder.
Add the following code to the NorthwindData.cs file:
[!code-csharpMain]1: using System;
2: using System.Data;
3: using System.Configuration;
4: using System.Web;
5: using System.Web.Security;
6: using System.Web.UI;
7: using System.Web.UI.WebControls;
8: using System.Web.UI.WebControls.WebParts;
9: using System.Web.UI.HtmlControls;
10: using System.Data.SqlClient;
11: public class NorthwindData {
12: private string _connectionString;
13: public NorthwindData() {
14: Initialize();15: }16:17: private void Initialize() {
18: if (ConfigurationManager.ConnectionStrings["Northwind"] == null ||
19: ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString.Trim() == "") {
20: throw new Exception("A connection string named 'Northwind' with " +
21: "a valid connection string must exist in the <connectionStrings> " +
22: "configuration section for the application.");
23: }24: _connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
25: }26:27: public DataTable GetAllEmployees(string sortColumns, int startRecord, int maxRecords) {
28: VerifySortColumns(sortColumns);29: string sqlCmd = "SELECT EmployeeID, LastName, FirstName, Address, " +
30: "City, Region, PostalCode FROM Employees ";
31: if (sortColumns.Trim() == "")
32: sqlCmd += "ORDER BY EmployeeID";
33: else
34: sqlCmd += "ORDER BY " + sortColumns;
35:36: SqlConnection conn = new SqlConnection(_connectionString);
37: SqlDataAdapter da = new SqlDataAdapter(sqlCmd, conn);
38: DataSet ds = new DataSet();
39: try {
40: conn.Open();41: da.Fill(ds, startRecord, maxRecords, "Employees");
42: } catch (SqlException e) {
43: // Handle exception.
44: } finally {
45: conn.Close();46: }47: return ds.Tables["Employees"];
48: }49:50: public int SelectCount() {
51: SqlConnection conn = new SqlConnection(_connectionString);
52: SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Employees", conn);
53: int result = 0;
54:55: try {
56: conn.Open();57: result = (int)cmd.ExecuteScalar();
58: } catch (SqlException e) {
59: // Handle exception.
60: } finally {
61: conn.Close();62: }63: return result;
64: }65:66: //////////
67: // Verify that only valid columns are specified in the sort expression to
68: // avoid a SQL Injection attack.
69: private void VerifySortColumns(string sortColumns) {
70: if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
71: sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);72: string[] columnNames = sortColumns.Split(',');
73: foreach (string columnName in columnNames) {
74: switch (columnName.Trim().ToLowerInvariant()) {
75: case "employeeid":
76: break;
77: case "lastname":
78: break;
79: case "firstname":
80: break;
81: case "":
82: break;
83: default:
84: throw new ArgumentException("SortColumns contains an " +
85: "invalid column name.");
86: break;
87: }88: }89: }90:91: // Select an employee.
92: public DataTable GetEmployee(int EmployeeID) {
93: SqlConnection conn = new SqlConnection(_connectionString);
94: SqlDataAdapter da =95: new SqlDataAdapter("SELECT EmployeeID, LastName, FirstName, " +
96: "Address, City, Region, PostalCode " +
97: " FROM Employees WHERE EmployeeID = @EmployeeID", conn);
98: da.SelectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
99: DataSet ds = new DataSet();
100: try {
101: conn.Open();102: da.Fill(ds, "Employees");
103: } catch (SqlException e) {
104: // Handle exception.
105: } finally {
106: conn.Close();107: }108:109: return ds.Tables["Employees"];
110: }111:112: // Delete the Employee by ID.
113: public int DeleteEmployee(int EmployeeID) {
114: SqlConnection conn = new SqlConnection(_connectionString);
115: SqlCommand cmd = new SqlCommand("DELETE FROM Employees WHERE " +
116: "EmployeeID = @EmployeeID", conn);
117: cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
118: int result = 0;
119: try {
120: conn.Open();121: result = cmd.ExecuteNonQuery();122: } catch (SqlException e) {
123: // Handle exception.
124: } finally {
125: conn.Close();126: }127:128: return result;
129: }130:131: // Update the Employee by original ID.
132: public int UpdateEmployee(int EmployeeID, string LastName, string FirstName,
133: string Address, string City, string Region,
134: string PostalCode) {
135: if (String.IsNullOrEmpty(FirstName))
136: throw new ArgumentException("FirstName cannot be null or an empty string.");
137: if (String.IsNullOrEmpty(LastName))
138: throw new ArgumentException("LastName cannot be null or an empty string.");
139: if (Address == null) { Address = String.Empty; }
140: if (City == null) { City = String.Empty; }
141: if (Region == null) { Region = String.Empty; }
142: if (PostalCode == null) { PostalCode = String.Empty; }
143:144: SqlConnection conn = new SqlConnection(_connectionString);
145: SqlCommand cmd = new SqlCommand("UPDATE Employees " +
146: " SET FirstName=@FirstName, " +
147: "LastName=@LastName, " +
148: "Address=@Address, City=@City, " +
149: "Region=@Region, " +
150: "PostalCode=@PostalCode " +
151: "WHERE EmployeeID=@EmployeeID", conn);
152: cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
153: cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;
154: cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address;
155: cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
156: cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region;
157: cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode;
158: cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
159:160: int result = 0;
161: try {
162: conn.Open();163: result = cmd.ExecuteNonQuery();164: } catch (SqlException e) {
165: // Handle exception.
166: } finally {
167: conn.Close();168: }169:170: return result;
171: }172:173: // Insert an Employee.
174: public int InsertEmployee(string LastName, string FirstName,
175: string Address, string City, string Region,
176: string PostalCode) {
177: if (String.IsNullOrEmpty(FirstName))
178: throw new ArgumentException("FirstName cannot be null or an empty string.");
179: if (String.IsNullOrEmpty(LastName))
180: throw new ArgumentException("LastName cannot be null or an empty string.");
181: if (Address == null) { Address = String.Empty; }
182: if (City == null) { City = String.Empty; }
183: if (Region == null) { Region = String.Empty; }
184: if (PostalCode == null) { PostalCode = String.Empty; }
185:186: SqlConnection conn = new SqlConnection(_connectionString);
187: SqlCommand cmd = new SqlCommand("INSERT INTO Employees " +
188: " (FirstName, LastName, Address, " +
189: " City, Region, PostalCode) " +
190: " Values(@FirstName, @LastName, " +
191: "@Address, @City, @Region, @PostalCode); " +
192: "SELECT @EmployeeID = SCOPE_IDENTITY()", conn);
193:194: cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
195: cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;
196: cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address;
197: cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
198: cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region;
199: cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode;
200: SqlParameter p = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
201: p.Direction = ParameterDirection.Output;202: int newEmployeeID = 0;
203: try {
204: conn.Open();205: cmd.ExecuteNonQuery();206: newEmployeeID = (int)p.Value;
207: } catch (SqlException e) {
208: // Handle exception.
209: } finally {
210: conn.Close();211: }212:213: return newEmployeeID;
214: }215:216: //
217: // Methods that support Optimistic Concurrency checks.
218: //
219: // Delete the Employee by ID.
220: public int DeleteEmployee(int original_EmployeeID, string original_LastName,
221: string original_FirstName, string original_Address,
222: string original_City, string original_Region,
223: string original_PostalCode) {
224:225: if (String.IsNullOrEmpty(original_FirstName))
226: throw new ArgumentException("FirstName cannot be null or an empty string.");
227: if (String.IsNullOrEmpty(original_LastName))
228: throw new ArgumentException("LastName cannot be null or an empty string.");
229: if (original_Address == null) { original_Address = String.Empty; }
230: if (original_City == null) { original_City = String.Empty; }
231: if (original_Region == null) { original_Region = String.Empty; }
232: if (original_PostalCode == null) { original_PostalCode = String.Empty; }
233: string sqlCmd = "DELETE FROM Employees WHERE EmployeeID = " + @original_EmployeeID
234:235: SqlConnection conn = new SqlConnection(_connectionString);
236: SqlCommand cmd = new SqlCommand(sqlCmd, conn);
237: cmd.Parameters.Add("@original_EmployeeID",
238: SqlDbType.Int).Value = original_EmployeeID;239: cmd.Parameters.Add("@original_FirstName",
240: SqlDbType.VarChar, 10).Value = original_FirstName;241: cmd.Parameters.Add("@original_LastName",
242: SqlDbType.VarChar, 20).Value = original_LastName;243: cmd.Parameters.Add("@original_Address",
244: SqlDbType.VarChar, 60).Value = original_Address;245: cmd.Parameters.Add("@original_City",
246: SqlDbType.VarChar, 15).Value = original_City;247: cmd.Parameters.Add("@original_Region",
248: SqlDbType.VarChar, 15).Value = original_Region;249: cmd.Parameters.Add("@original_PostalCode",
250: SqlDbType.VarChar, 10).Value = original_PostalCode;251:252: int result = 0;
253: try {
254: conn.Open();255: result = cmd.ExecuteNonQuery();256: } catch (SqlException e) {
257: // Handle exception.
258: } finally {
259: conn.Close();260: }261:262: return result;
263: }264:265: // Update the Employee by original ID.
266: public int UpdateEmployee(string LastName, string FirstName,
267: string Address, string City, string Region,
268: string PostalCode, int original_EmployeeID,
269: string original_LastName, string original_FirstName,
270: string original_Address, string original_City,
271: string original_Region, string original_PostalCode) {
272:273: if (String.IsNullOrEmpty(FirstName))
274: throw new ArgumentException("FirstName cannot be null or an empty string.");
275: if (String.IsNullOrEmpty(LastName))
276: throw new ArgumentException("LastName cannot be null or an empty string.");
277: if (Address == null) { Address = String.Empty; }
278: if (City == null) { City = String.Empty; }
279: if (Region == null) { Region = String.Empty; }
280: if (PostalCode == null) { PostalCode = String.Empty; }
281: if (original_Address == null) { original_Address = String.Empty; }
282: if (original_City == null) { original_City = String.Empty; }
283: if (original_Region == null) { original_Region = String.Empty; }
284: if (original_PostalCode == null) { original_PostalCode = String.Empty; }
285:286: string sqlCmd = "UPDATE Employees " +
287: " SET FirstName = @FirstName, LastName = @LastName, " +
288: " Address = @Address, City = @City, Region = @Region, " +
289: " PostalCode = @PostalCode " +
290: " WHERE EmployeeID = @original_EmployeeID";
291:292: SqlConnection conn = new SqlConnection(_connectionString);
293: SqlCommand cmd = new SqlCommand(sqlCmd, conn);
294: cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
295: cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;
296: cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = Address;
297: cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
298: cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = Region;
299: cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode;
300: cmd.Parameters.Add("@original_EmployeeID",
301: SqlDbType.Int).Value = original_EmployeeID;302: cmd.Parameters.Add("@original_FirstName",
303: SqlDbType.VarChar, 10).Value = original_FirstName;304: cmd.Parameters.Add("@original_LastName",
305: SqlDbType.VarChar, 20).Value = original_LastName;306: cmd.Parameters.Add("@original_Address",
307: SqlDbType.VarChar, 60).Value = original_Address;308: cmd.Parameters.Add("@original_City",
309: SqlDbType.VarChar, 15).Value = original_City;310: cmd.Parameters.Add("@original_Region",
311: SqlDbType.VarChar, 15).Value = original_Region;312: cmd.Parameters.Add("@original_PostalCode",
313: SqlDbType.VarChar, 10).Value = original_PostalCode;314:315: int result = 0;
316:317: try {
318: conn.Open();319: result = cmd.ExecuteNonQuery();320: } catch (SqlException e) {
321: // Handle exception.
322: } finally {
323: conn.Close();324: }325: return result;
326: }327: }Add the following code to the Source view of object.aspx:
[!code-aspxMain]1: <%@ Page language="C#" %>
2: <script RunAt="server">
3: void EmployeesDetailsView_ItemInserted(Object sender, DetailsViewInsertedEventArgs e) {
4: EmployeesGridView.DataBind();5: }6:7: void EmployeesDetailsView_ItemUpdated(Object sender, DetailsViewUpdatedEventArgs e) {
8: EmployeesGridView.DataBind();9: }10:11: void EmployeesDetailsView_ItemDeleted(Object sender, DetailsViewDeletedEventArgs e) {
12: EmployeesGridView.DataBind();13: }14: void EmployeesGridView_OnSelectedIndexChanged(object sender, EventArgs e) {
15: EmployeeDetailsObjectDataSource.SelectParameters["EmployeeID"].DefaultValue =
16: EmployeesGridView.SelectedDataKey.Value.ToString();17: EmployeesDetailsView.DataBind();18: }19: void EmployeeDetailsObjectDataSource_OnInserted(object sender,
20: ObjectDataSourceStatusEventArgs e) {21:22: EmployeeDetailsObjectDataSource.SelectParameters["EmployeeID"].DefaultValue =
23: e.ReturnValue.ToString();24: EmployeesDetailsView.DataBind();25: }26: void EmployeeDetailsObjectDataSource_OnUpdated(object sender,
27: ObjectDataSourceStatusEventArgs e) {28:29: if ((int)e.ReturnValue == 0)
30: Msg.Text = "Employee was not updated. Please try again.";
31: }32: void EmployeeDetailsObjectDataSource_OnDeleted(object sender,
33: ObjectDataSourceStatusEventArgs e) {34:35: if ((int)e.ReturnValue == 0)
36: Msg.Text = "Employee was not deleted. Please try again.";
37: }38: void Page_Load() {
39: Msg.Text = "";
40: }41: </script>
42: <html>
43: <body>
44: <form id="Form1" runat="server">
45: <h3>ObjectDataSource Example</h3>
46: <asp:Label id="Msg" runat="server" ForeColor="Red" />
47: <asp:ObjectDataSource
48: ID="EmployeesObjectDataSource"
49: runat="server"
50: TypeName="NorthwindData"
51: SortParameterName="SortColumns"
52: EnablePaging="true"
53: SelectCountMethod="SelectCount"
54: StartRowIndexParameterName="StartRecord"
55: MaximumRowsParameterName="MaxRecords"
56: SelectMethod="GetAllEmployees" >
57: </asp:ObjectDataSource>
58: <asp:ObjectDataSource
59: ID="EmployeeDetailsObjectDataSource"
60: runat="server"
61: TypeName="NorthwindData"
62: ConflictDetection="CompareAllValues"
63: OldValuesParameterFormatString="{0}"
64: SelectMethod="GetEmployee"
65: InsertMethod="InsertEmployee"
66: UpdateMethod="UpdateEmployee"
67: DeleteMethod="DeleteEmployee"
68: OnInserted="EmployeeDetailsObjectDataSource_OnInserted"
69: OnUpdated="EmployeeDetailsObjectDataSource_OnUpdated"
70: OnDeleted="EmployeeDetailsObjectDataSource_OnDeleted">
71: <SelectParameters>
72: <asp:Parameter Name="EmployeeID" Type="Int32" />
73: </SelectParameters>
74: </asp:ObjectDataSource>
75: <table cellspacing="10">
76: <tr>
77: <td valign="top">
78: <asp:GridView ID="EmployeesGridView"
79: DataSourceID="EmployeesObjectDataSource"
80: AutoGenerateColumns="false"
81: AllowSorting="true"
82: AllowPaging="true"
83: PageSize="5"
84: DataKeyNames="EmployeeID"
85: OnSelectedIndexChanged="EmployeesGridView_OnSelectedIndexChanged"
86: RunAt="server">
87: <HeaderStyle backcolor="lightblue" forecolor="black"/>
88: <Columns>
89: <asp:ButtonField Text="Details..."
90: HeaderText="Show Details"
91: CommandName="Select"/>
92:93: <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID"
94: SortExpression="EmployeeID" />
95: <asp:BoundField DataField="FirstName" HeaderText="First Name"
96: SortExpression="FirstName" />
97: <asp:BoundField DataField="LastName" HeaderText="Last Name"
98: SortExpression="LastName, FirstName" />
99: </Columns>
100: </asp:GridView>
101: </td>
102: <td valign="top">
103: <asp:DetailsView ID="EmployeesDetailsView"
104: DataSourceID="EmployeeDetailsObjectDataSource"
105: AutoGenerateRows="false"
106: EmptyDataText="No records."
107: DataKeyNames="EmployeeID"
108: Gridlines="Both"
109: AutoGenerateInsertButton="true"
110: AutoGenerateEditButton="true"
111: AutoGenerateDeleteButton="true"
112: OnItemInserted="EmployeesDetailsView_ItemInserted"
113: OnItemUpdated="EmployeesDetailsView_ItemUpdated"
114: OnItemDeleted="EmployeesDetailsView_ItemDeleted"
115: RunAt="server">
116: <HeaderStyle backcolor="Navy" forecolor="White"/>
117: <RowStyle backcolor="White"/>
118: <AlternatingRowStyle backcolor="LightGray"/>
119: <EditRowStyle backcolor="LightCyan"/>
120: <Fields>
121: <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID"
122: InsertVisible="False" ReadOnly="true"/>
123: <asp:BoundField DataField="FirstName" HeaderText="First Name"/>
124: <asp:BoundField DataField="LastName" HeaderText="Last Name"/>
125: <asp:BoundField DataField="Address" HeaderText="Address"/>
126: <asp:BoundField DataField="City" HeaderText="City"/>
127: <asp:BoundField DataField="Region" HeaderText="Region"/>
128: <asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>
129: </Fields>
130: </asp:DetailsView>
131: </td>
132: </tr>
133: </table>
134: </form>
135: </body>
136: </html>
- Save all files and browse object.aspx.
Interact with the interface by viewing details, editing employees, adding employees, and deleting employees.
)
|
|