لطفا منتظر باشید ...
Using ADO.NET
All the examples of data binding that you've seen so far have used .NET Framework collection classes, such as ArrayList. However, if your data is held in a database, you'll use ADO.NET classes such as DataSet and DataView.As mentioned at the beginning of this chapter, ADO—ADO.NET's predecessor—will be familiar to developers with Visual Studio 6.0 experience. However, ADO.NET presents a new model for working with data that's well suited for distributed applications. In the past, developers based the design of data-driven applications on the fact that these applications were permanently connected to the database and that the database managed record locking, updates, and deletions.Data access from server-side code in an ASP.NET application presents special challenges because a Web page is essentially stateless. The data you access during the application might require updating at a later point, by which time the program will have instantiated the Web page class a number of times. ADO.NET provides a model that's ideally suited for this type of access. In ADO.NET, the application works with a DataSet or DataReader (any class that implements the System.Data.IDataReader interface), which is a representation of the data that's disconnected from the database and works independently of the data source.
Understanding the ADO.NET Objects
DataSet objects represent the actual data that an application works with. Because a DataSet object is always disconnected from its source data, you can modify it independently. However, you can easily reconcile changes to a DataSet object with the original data. The internal structure of a DataSet object is similar to that of a relational database; it contains tables, columns, relationships, constraints, views, and so on. DataSet objects can result from a database query. You can also construct DataSet objects in code and serialize them to, and deserialize them from, an XML file. Because a DataSet object remains independent from its underlying data, you can work with a consistent programming model, regardless of the data source.DataAdapters are classes that implement the System.Data.IDataAdapter interface, and are responsible for populating a DataSet object. DataAdapters also reconcile changes in the database with changes applied to the DataSet. Connections implement System.Data.IDbConnection and represent a physical connection to a data store, such as Microsoft SQL Server or an XML file. Commands are classes that implement the System.Data.IDbCommand interface, and contain the SQL commands used to actually access the data source.As an alternative to the DataSet, you'll often use a DataReader, which is a set of classes that implement the System.Data.IDataReader interface and provide efficient, read-only data access to a data source. A DataReader doesn't contain the full functionality of a DataSet object, such as the ability to make changes or identify changed data rows, and only allows you to read forward through the contained data.
Choosing a Data Provider
The IDataAdapter, IDataReader, IDbConnection and IDbCommand interfaces together define how a managed application accesses a database. A Data Provider is a set of classes that implements these interfaces for a specific database. In .NET Framework 1.1, you have a choice of four data providers:
SQL Server .NET Data Provider (in the System.Data.SqlClient namespace). The SQL data provider talks directly to Microsoft SQL Server.
OLE DB .NET Data Provider (in the System.Data.OleDb namespace). You can use the OLE DB data provider to talk to any data source that offers an OLE DB interface
Microsoft .NET Data Provider for Oracle (in the System.Data.OracleClient namespace). You can use the Oracle data provider to access Oracle databases.
ODBC .NET Data Provider (in the System.Data.Odbc namespace). You can use the ODBC data provider to connect to any data source that implements an ODBC interface.
Each data provider implements its own Connection, Command, DataAdapter, and DataReader classes. For example, the SQL data provider implements the SQLConnection, SQLCommand, SQLDataAdapter, and SQLDataReader classes, and the OLE DB data provider implements the OLEDbConnection, OLEDbCommand, OLEDbDataAdapter, and OLEDbDataReader classes. The examples we'll look at next in this section will use the SQL data provider.To use the ADO.NET objects, you must import the relevant namespaces:
using System.Data;
using System.Data.SqlClient;
If you're using the OLE DB data provider, the syntax will look like this:
using System.Data;
using System.Data.OleDb;
Using a DataReader Object for Read-Only Data Access
If you don't need to update the data you're fetching from a database, the DataReader offers a more efficient alternative to using a DataSet. To use a DataReader, you must first open a connection to the database, define the SQL command to fetch the data in a Command object, and then call the ExecuteReader method of the Command object. This returns a DataReader object containing the data that you can use as the data source for the control. Be aware that this transaction doesn't involve the use of a DataAdapter.Listing 11-3 shows a simple code sample using a List control. In Listing 11-4, the Page_Load method accesses the database and builds the SqlDataReader, which then provides data to the control.Listing 11-3: Source file DataReaderExample.aspx
<%@ Register TagPrefix="mobile" Namespace="System.Web.UI.MobileControls"
Assembly="System.Web.Mobile" %>
<%@ Page language="c#" Codebehind="DataReaderExample.aspx.cs"
Inherits="MSPress.MobWeb.DataRdrEx.DataReaderMobileWebForm" %>
<mobile:Form id="Form1" runat="server" Paginate="True">
<mobile:List id="List1" runat="server"></mobile:List>
</mobile:Form>
Listing 11-4: Code-behind file DataReaderExample.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.MobileControls;
namespace MSPress.MobWeb.DataRdrEx
{
/// <summary>
/// Use the DataReader for efficient read-only access to data.
/// </summary>
public class DataReaderMobileWebForm
: System.Web.UI.MobileControls.MobilePage
{
protected System.Web.UI.MobileControls.List List1;
protected System.Web.UI.MobileControls.Form Form1;
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
private void Page_Load(object sender, System.EventArgs e)
{
// Use the DataReader to fetch a read-only dataset.
String strConnectionString = "server=(local)\\NetSDK;" +
"database=pubs;Trusted_Connection=yes";
SqlConnection myConnection =
new SqlConnection(strConnectionString);
SqlCommand myCommand =
new SqlCommand("select * from Authors", myConnection);
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader();
List1.DataSource = dr;
List1.DataTextField="au_lname";
List1.DataBind();
myConnection.Close();
}
}
}
The output is a list of all the last names of entries in the authors table, as Figure 11-1 shows.
Figure 11-1: A List control bound to a database table
Using a DataSet Object for Data Binding
In many applications, the DataReader provides all the required functionality. However, for applications that involve long transactions or require you to update the database, the DataSet offers a number of advantages. A DataSet also has the benefit of containing information about the constraints defined in the underlying database. Therefore, you can make changes to the DataSet data and trap any constraint violations in your application (such as data field lengths or valid ranges) when you apply updates to the DataSet, rather than when you attempt the database update. When you update the database from the DataSet, the data remains consistent with any constraints defined in the database.
Accessing a database to populate a DataSet is very similar to fetching data using a DataReader. However, you define the SQL command to retrieve the data in a DataAdapter, rather than in a Command. Listing 11-5 shows the syntax for creating such a DataSet. See the C# and Visual Basic examples DatasetExample in the companion material on this book's Web site for applications that use a DataSet object.
Listing 11-5: Creating a DataSet object, which is bound to the ObjectList control named ObjectList1
// Use the DataAdapter to fill a dataset.
String strConnectionString =
"server=(local)\\NetSDK;database=pubs;Trusted_Connection=yes";
SqlConnection myConnection =
new SqlConnection(strConnectionString);
SqlDataAdapter myCommand =
new SqlDataAdapter("select * from Authors", myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "Authors");
ObjectList1.DataSource = ds.Tables["Authors"].DefaultView;
ObjectList1.LabelField = "au_lname";
ObjectList1.AutoGenerateFields = true;
ObjectList1.DataBind();
A DataSet object contains DataTable objects, which in turn contain DataRow and DataColumn objects. Together, these classes offer a rich set of functionality for manipulating data. However, this functionality is too extensive to detail here. For more information, consult the .NET Framework SDK documentation.
Creating a Mobile Web Application to Update a Database
If your application requires the user to make a series of changes that must be applied to the database in a single transaction, the ideal approach is to programmatically alter a DataSet object, storing changes as they're made. You then apply these changes to the database using a DataAdapter.
If the user makes changes to a single record, which is more likely in a mobile application, you can adopt a simpler approach. In Listings 11-6 and 11-7 shown later in this section, the application uses an ObjectList control to display data from the authors table in the pubs database installed with the .NET Framework samples. The code defines an item command using the <Command…> syntax in the .aspx file, which allows the user to edit the details. When selected, Form2 appears, displaying the current field values using editable controls, such as TextBox. To keep the example short, this application allows editing of only the First Name and Last Name fields. Figures 11-2 and 11-3 show how the user interface of this example will look.
Figure 11-2: The ObjectList control on the first form lists the last names of entries in the authors table. When the user selects an entry, an additional Edit Details command appears.
Figure 11-3: When the user makes changes and clicks Save, the database updates.
The Edit form, shown in Listing 11-6, employs a data binding syntax that inserts the values for the currently selected record into the TextBox controls ready for editing. This form also presents two command buttons: one to save any changes, and one to cancel without saving. The same OnItemCommand event handler, named CancelConfirmEdit, handles both these buttons.If the user clicks the Save button, the runtime calls the SaveChanges event handler method (shown in listing 11-7). This method uses a parameterized SQL query string to update the required record. The parameters are set to the values the user enters into the text boxes on the form. Be aware that the user isn't allowed to edit the au_id field on the Edit form because this field is the primary key of the database item and isn't user assignable.
Listing 11-6: Source file DataUpdateExample.aspx
<%@ Page language="c#" Codebehind="DataUpdateExample.aspx.cs"
Inherits="MSPress.MobWeb.DataUpdateEx.DataUpdateMobileWebForm" %>
<%@ Register TagPrefix="mobile"
Namespace="System.Web.UI.MobileControls"
Assembly="System.Web.Mobile" %>
<mobile:Form id="Form1" runat="server" Paginate="True">
<mobile:ObjectList id="ObjectList1" runat="server">
<Command Name="EditCommand" Text="Edit Details"/>
</mobile:ObjectList>
</mobile:Form>
<mobile:Form id="Form2" runat="server">
<mobile:Label id="Label1" runat="server"
text="Edit Author Details" StyleReference="title"/>
<mobile:Label runat="server">
Author ID: <%# ObjectList1.Selection["au_id"] %>
</mobile:Label>
First Name:
<mobile:TextBox id="TextBox1" runat="server" MaxLength="20"
Text='<%# ObjectList1.Selection["au_fname"]%>' />
Last Name:
<mobile:TextBox id="TextBox2" runat="server" MaxLength="40"
Text='<%# ObjectList1.Selection["au_lname"]%>' />
<mobile:Label id=Label3 runat="server"
StyleReference="error" Visible="false"/>
<mobile:Command id="Command1" runat="server" Text="Save"
CommandName="Save" />
<mobile:Command id="Command2" runat="server" Text="Cancel"
CommandName="Cancel" />
</mobile:Form>
Listing 11-7: Code-behind file DataUpdateExample.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.MobileControls;
using System.Web.UI.WebControls;
namespace MSPress.MobWeb.DataUpdateEx
{
/// <summary>
/// Use the DataReader to fetch the data.
/// </summary>
public class DataUpdateMobileWebForm
: System.Web.UI.MobileControls.MobilePage
{
SqlConnection myConnection;
protected System.Web.UI.MobileControls.ObjectList ObjectList1;
protected System.Web.UI.MobileControls.Form Form1;
protected System.Web.UI.MobileControls.Form Form2;
protected System.Web.UI.MobileControls.Label Label3;
protected System.Web.UI.MobileControls.Command Command1;
protected System.Web.UI.MobileControls.Command Command2;
protected System.Web.UI.MobileControls.TextBox TextBox1;
protected System.Web.UI.MobileControls.TextBox TextBox2;
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
this.ObjectList1.ItemCommand += new
ObjectListCommandEventHandler(this.OnEditCommand);
this.Command1.ItemCommand += new
CommandEventHandler(this.CancelConfirmEdit);
this.Command2.ItemCommand += new
CommandEventHandler(this.CancelConfirmEdit);
}
private void Page_Load(object sender, System.EventArgs e)
{
// Use the DataReader to fetch a read-only data set.
String strConnectionString = "server=(local)\\NetSDK;" +
"database=pubs;Trusted_Connection=yes";
myConnection = new SqlConnection(strConnectionString);
if (!IsPostBack) BindList();
}
private void BindList()
{
SqlCommand myCommand =
new SqlCommand("select * from Authors", myConnection);
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader();
ObjectList1.DataSource = dr;
ObjectList1.LabelField = "au_lname";
ObjectList1.AutoGenerateFields = true;
ObjectList1.DataBind();
// The field names of au_id, au_lname, and au_fname
// do not provide good titles, so change them in the
// AllFields collection.
ObjectList1.AllFields[ObjectList1.AllFields.IndexOf("au_id")]
.Title = "Author ID";
ObjectList1.AllFields[ObjectList1.AllFields.IndexOf("au_fname")]
.Title = "First Name";
ObjectList1.AllFields[ObjectList1.AllFields.IndexOf("au_lname")]
.Title = "Last Name";
}
/// <summary>
/// Called when the user clicks the 'Edit Details' link
/// </summary>
protected void OnEditCommand(
Object source,
ObjectListCommandEventArgs args)
{
// DataBind the form to insert the selected item details.
Form2.DataBind();
this.ActiveForm = Form2;
Label3.Visible = false;
Command1.Visible = true;
Command2.Visible = true;
Command2.Text = "Cancel";
}
/// <summary>
/// Called when a user clicks on either 'Save' or 'Cancel' button
/// on Edit screen
/// </summary>
private void CancelConfirmEdit(Object sender, CommandEventArgs e)
{
if (e.CommandName == "Save") {
SaveChanges();
}
else
{
// Go back to the List View.
this.ActiveForm = Form1;
ObjectList1.ViewMode = ObjectListViewMode.List;
}
BindList();
}
private void SaveChanges()
{
String updateCmd = "UPDATE Authors SET au_lname = @LName, " +
"au_fname = @FName where au_id = @Id";
SqlCommand myCommand = new SqlCommand(updateCmd, myConnection);
myCommand.Parameters.Add(
new SqlParameter("@Id", SqlDbType.NVarChar, 11));
myCommand.Parameters.Add(
new SqlParameter("@LName", SqlDbType.NVarChar, 40));
myCommand.Parameters.Add(
new SqlParameter("@FName", SqlDbType.NVarChar, 20));
myCommand.Parameters["@Id"].Value =
ObjectList1.Selection["au_id"];
myCommand.Parameters["@LName"].Value = TextBox2.Text;
myCommand.Parameters["@FName"].Value = TextBox1.Text;
myCommand.Connection.Open();
try
{
myCommand.ExecuteNonQuery();
Label3.Text = "Record Updated";
}
catch (SqlException)
{
Label3.Text = "ERROR: Could not update record";
}
myCommand.Connection.Close();
Label3.Visible = true;
Command1.Visible = false;
Command2.Visible = true;
Command2.Text = "Back";
}
}
}
The code that performs the actual database update resides in the SaveChanges method. ADO.NET denotes parameters in a SQL command by using a leading at sign (@), as illustrated here:
String updateCmd = "UPDATE Authors SET au_lname = @LName, " +
"au_fname = @FName where au_id = @Id";
SqlCommand myCommand = new SqlCommand(updateCmd, myConnection);
The code adds a SQLParameter object to the SQLCommand object for each variable quantity within the SQL command, configured with the appropriate data format, as shown here:
myCommand.Parameters.Add(new SqlParameter("@Id", SqlDbType.NVarChar, 11));Then the code sets the actual value that you want to substitute into the SQL command for that parameter:
myCommand.Parameters["@LName"].Value = txtLName.Text;
After you've defined the SQL command, you can execute it in one of two ways. If you want the code to return a new DataSet object, you can call the Execute method of the SQLCommand object. If you don't need a DataSet object returned, call ExecuteNonQuery like this:
myCommand.ExecuteNonQuery();
We gave this particular example a final polish by presenting a confirmation message, using the label Label3. This label has its Visible property set to false when Form2 is first displayed. However, it's set to a result string and appears after the database update occurs (or fails). Similar program logic hides the Save button after the user saves the record, and it changes the legend on the Cancel button to Back.You can achieve database inserts and deletions using similar logic. To do so, just set the SQL Command string to the appropriate SQL INSERT or DELETE command. Needless to say, a real application would require more extensive error checking to handle complex error situations, such as a duplicate key error when attempting an INSERT.
