Javascript debugger
Javascript examples
|
|
Datasets are one of the two main ways of working with data in ADO.NET. (The other way is with DataReaders, which you’ve been using already and will learn about in more detail later in this chapter.) Unlike most other objects we have looked at, in which there is one version in the SqlClient namespace and a similar object in the OleDb namespace, there is a single DataSet class, found in the System.Data namespace. Dataset objects provide a database-independent in-memory representation of data. This data can be used for display or to update a back-end database through the appropriate DataAdapter object. A dataset can also read data from and write data to an XML file or a Stream object.
Dataset objects can be constructed and manipulated programmatically by adding, modifying, or deleting DataTable, DataColumn, and DataRow objects in the dataset. You can also use such datasets to update a back-end database (assuming that the schema of the tables you’ve constructed is compatible) by calling the Update method of the dataset object.
You can work with typed datasets to make your code easier to read and your ADO.NET code less error prone. Also, you can use a DataView object to filter the contents of a dataset for use in display (such as for data binding) or calculations.
DataAdapter objects allow you to get data from a back-end database into a dataset and to update a back-end database from a dataset. Several DataAdapters are installed with the .NET Framework—the SqlDataAdapter and the OleDbDataAdapter are the ones you’ll use most frequently, as well as the OdbcDataAdapter and OracleDataAdapter, which are new in version 1.1 of the Framework. Each of the DataAdapter classes uses the appropriate Connection and Command classes to either retrieve or update data.
You can use the Fill method of the SqlDataAdapter and OleDbAdapter classes to populate a dataset with results from a back-end database, using the Command object specified by the SelectCommand property of the DataAdapter. The following code creates and opens a connection to the database specified by the ConnStr argument. Then it creates a new SqlDataAdapter and sets its SelectCommand property to a newly created SqlCommand object that uses the query specified by the SQL argument. Finally the code creates a new dataset, populates it using the Fill method of the SqlDataAdapter, and closes the connection.
SqlConnection mySqlConn = new SqlConnection(ConnStr) mySqlConn.Open() SqlDataAdapter mySqlAdapter = new SqlDataAdapter() mySqlAdapter.SelectCommand = new SqlCommand(SQL, mySqlConn) DataSet myDS = new DataSet() mySqlAdapter.Fill(myDS) mySqlConn.Close();
At this point, the data in the dataset can be updated or deleted, new rows can be added, or the entire dataset can be passed to another component because it no longer has a connection to the back-end database. It’s important to understand that updating the dataset does not automatically update the back-end database (or other data source) from which the dataset was filled. To update the back-end database, you need to call the Update method of the data adapter used to fill the dataset. You’ll learn how to do this later in this chapter.
In addition to populating a dataset from a database using a DataAdapter, you can also read the data for a dataset from an XML file or stream using the ReadXml method of the dataset. Since you can also save a dataset as XML using the WriteXml method, the two techniques together can be a convenient way of serializing and deserializing the data in a dataset. The ExecuteXmlReader.aspx example earlier in the chapter shows an example of using the ReadXml method to populate a dataset with XML data retrieved from SQL Server using the FOR XML AUTO query syntax.
Another way of populating a dataset object is to create its table, column, and row objects programmatically. This allows you to create a dataset without connecting to a back-end database. Assuming that the schema of the created table(s) matches your database (which is handled in the following code by using an XSD schema file, shown in the listing AddTitle.xsd later in the chapter, created from the Pubs Titles table), you can then connect to the database and update it based on the row(s) you’ve added to the dataset programmatically.
The following example shows how to create a program to add a new row to the Titles table of the Pubs sample SQL Server database. Note that the SelectCommand property of the SqlDataAdapter is set by passing the SQL string variable to the constructor of the SqlDataAdapter. This is necessary to allow the UpdateCommand (the command that is called when the Update method of the SqlDataAdapter is called) to be built automatically, using the SqlCommandBuilder object. The call to SqlAdapter.Update updates the database, and then the call to SqlAdapter.Fill refreshes the data in the dataset with the current data from the database table.
Open Visual Studio, and then open the Chapter_09 project you created earlier in the chapter.
Add a new Web Form to the project. Name the form AddTitle.aspx.
Copy AddTitle.xsd from the practice files to the folder the project resides in.
Unlike previous examples, this example will use an HTML table to format the output. To make this easier, use the Properties window to change the pageLayout property from GridLayout to FlowLayout. (Click an empty area of the page to make sure the properties for the page are displayed.)
Change to HTML mode by clicking on the HTML tab at the bottom of the design window. Insert the following HTML between the beginning and ending form tags. (If you’ve downloaded and installed the practice files, you can copy the code from the AddTitle.aspx sample to save typing.) The HTML code sets up a table to be used to display captions and textboxes.
<h3>Inserting a Title</h3> <table width="300"> <tr> <td colspan="2" bgcolor="silver">Add a New Title:</td> </tr> <tr> <td nowrap>Title ID: </td> <td> <asp:textbox id="title_id" text="XX0000" runat="server"/> </td> </tr> <tr> <td nowrap>Title: </td> <td> <asp:textbox id="title" text="The Tao of ASP.NET runat="server"/> </td> </tr> <tr> <td nowrap >Type: </td> <td> <asp:textbox id="type" text="popular_comp runat="server"/> </td> </tr> <tr> <td>Publisher ID: </td> <td> <asp:textbox id="pub_id" text="1389" runat="server"/> </td> </tr> <tr> <td>Price: </td> <td> <asp:textbox id="price" text="39.99" runat="server"/> </td> </tr> <tr> <td>Advance: </td> <td> <asp:textbox id="advance" text="2000" runat="server"/> </td> </tr> <tr> <td>Royalty: </td> <td> <asp:textbox id="royalty" text="5" runat="server"/> </td> </tr> <tr> <td nowrap>Year-to-Date Sales: </td> <td> <asp:textbox id="ytd_sales" text="0" runat="server"/> </td> </tr> <tr> <td>Notes: </td> <td> <asp:textbox id="notes" textmode="multiline text="Philosophy and Code...a perfect mix. " rows="3" columns="30" runat="server"/> </td> </tr> <tr> <td nowrap>Publication Date: </td> <td> <asp:textbox id="pubdate" text="12/01/01" runat="server"/> </td> </tr> <tr> <td></td> <td style="padding-top:15"> <asp:button text="Add Title" runat="server"/> </td> </tr> <tr> <td colspan="2"> <asp:datagrid id="titlegrid" runat="server" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="3" GridLines="Vertical"> <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#008A8C"> </SelectedItemStyle> <AlternatingItemStyle BackColor="#DCDCDC"> </AlternatingItemStyle> <ItemStyle ForeColor="Black" BackColor="#EEEEEE"> </ItemStyle> <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084"> </HeaderStyle> <FooterStyle ForeColor="Black" BackColor="#CCCCCC"> </FooterStyle> <PagerStyle HorizontalAlign="Center" ForeColor="Black" BackColor="#999999" Mode="NumericPages"> </PagerStyle> </asp:datagrid> </td> </tr> </table>
After inserting this code, switch back to design mode by clicking in the Design tab at the bottom of the editor window. In the designer, the screen should look similar to the following illustration.
Switch to the code window by pressing F7, or by selecting View, and then Code. At the top of the file, add the following using clause:
using System.Data.SqlClient;
Scroll down to the Page_Load event handler and add the following code:
DataSet MyDS = new DataSet() DataTable TitleTable DataRow TitleRow string ConnStr ConnStr = @"server=(local)\VSdotNET;database=pubs; " "Trusted_Connection=yes" string SQL SQL = "SELECT * FROM Titles" SqlConnection MySqlConn = new SqlConnection(ConnStr) SqlDataAdapter MySqlAdapter = new SqlDataAdapter(SQL,ConnStr) SqlCommandBuilder MySqlCB = new SqlCommandBuilder(MySqlAdapter) MyDS.ReadXmlSchema(Server.MapPath("AddTitle.xsd")) if ( this.IsPostBack TitleTable = MyDS.Tables[0] TitleRow = TitleTable.NewRow() TitleRow["title_id"] = title_id.Text TitleRow["title"] = title.Text TitleRow["type"] = type.Text TitleRow["pub_id"] = pub_id.Text TitleRow["price"] = double.Parse(price.Text) TitleRow["advance"] = double.Parse(advance.Text) TitleRow["royalty"] = int.Parse(royalty.Text) TitleRow["ytd_sales"] = int.Parse(ytd_sales.Text) TitleRow["notes"] = notes.Text TitleRow["pubdate"] = DateTime.Parse(pubdate.Text) TitleTable.Rows.Add(TitleRow) //Update back-end table based on new ro MySqlAdapter.Update(MyDS) //Reset dataset before filling with data from D MyDS.Reset() //Fill dataset with data from the Titles tabl MySqlAdapter.Fill(MyDS) titlegrid.DataSource = MyDS.Tables[0].DefaultView titlegrid.DataBind() els //To prevent conflicts on multiple inserts, we ca //generate a random value for title_i Random RandomNum = new Random() title_id.Text = "XX" + String.Format("{0:000#}", RandomNum.Next(9999)) }
Save the page and code-behind module.
Build the project.
Test the page by right-clicking AddTitle.aspx, selecting Browse With, and then selecting Microsoft Internet Explorer. The resulting screen should look like the illustration on the following page. After you click the Add Title button, a DataGrid will be displayed below the form fields with the current data in the Titles table, including the added row.
Since datasets can contain multiple tables of data, it only makes sense that you might want the dataset object to contain information about the relationships between these tables. This would allow you to treat them as true relational data and to maintain any hierarchy and foreign key relationships inherent in the data. The DataSet class fulfills this need with its Relations collection, which contains a collection of DataRelation objects, each of which describes a parent/child relationship between two matching columns in different tables in the dataset.
You can add DataRelation objects to a dataset programmatically, or they can be inferred from a supplied XSD schema.
Values in a dataset are accessed by referring to the DataTable and DataRow objects containing the data. The DataTables are available via the Tables collection of the dataset, and you can reference a particular table either by index (zero-based) or by table name (key) as follows:
DataTable MyTable = myDS.Tables[0] DataTable MyTable = myDS.Tables["tablename"];
You can also iterate over the DataTableCollection (returned by the Tables property of the dataset), DataRowCollection (returned by the Rows property of a DataTable), or the DataColumnCollection (returned by the Columns property of a DataTable). The code in the following example iterates over all of the tables in a dataset and all of the rows in each table. Then it displays the value of each column in the row, using an ASP.NET Literal control as a placeholder for the text.
Open Visual Studio, and then open the Chapter_09 project you created earlier in the chapter.
Add a new Web Form to the project. Name the form DisplayDataSetItems.aspx.
Add an ASP.NET Literal server control to the form. Change the ID of the Literal control to Value.
Switch to the code window by pressing F7, or by selecting View, then Code. At the top of the file, add the following using clauses:
using System.Data.SqlClient;
Scroll down to the Page_Load event handler and add the following code:
DataSet MyDS = new DataSet() string ConnStr string SQL ConnStr = @"server=(local)\VSdotNET;database=pubs; " "Trusted_Connection=yes" SQL = "SELECT * FROM Titles " + "SELECT * FROM Publishers " SqlConnection MySqlConn = new SqlConnection(ConnStr) SqlDataAdapter MySqlAdapter = new SqlDataAdapter(SQL,ConnStr) MySqlAdapter.Fill(MyDS);
Most of this should look somewhat familiar by now. One difference is that the SQL command contains two distinct SELECT statements. This is to cause two tables to be retrieved into the DataSet.
Add code to iterate over the DataTables, DataRows and DataColumns. Just below the code added in the previous step, add the following code:
// Get each DataTable in the DataTable Collectio // and display each row value by appending it t // the Text property of the Literal control foreach ( DataTable CurrentTable in MyDS.Tables Value.Text += "Table: " + CurrentTable.TableName.ToString() + "<br/>" Value.Text += "------------------<br/>" foreach ( DataRow CurrentRow in CurrentTable.Rows Value.Text += "<br/> " foreach ( DataColumn CurrentColumn in CurrentTable.Columns if ( CurrentRow[CurrentColumn] != null if ( CurrentRow[CurrentColumn] != DBNull.Value Value.Text += CurrentRow[CurrentColumn].ToString() els Value.Text += "NULL" Value.Text += "<br/> " }
Save the page and code-behind module.
Build the project.
Test the page by right-clicking DisplayDataSetItems.aspx, selecting Browse With, and then selecting Microsoft Internet Explorer. The resulting screen should look like the following illustration.
Scroll toward the bottom of the screen. You should see the transition between the titles and publishers tables, as the following illustration shows:
Once you’ve populated a dataset with data—either by using a DataAdapter or by calling the ReadXml method of the dataset—you might want to update that data. You can edit an individual value by specifying the table, row, and column of the item you want to edit and then supplying a new value.
myDS.Tables[0].Rows[0].Columns[2] = " popular_comp";
But what if you edit several values, and then you realize that you want to roll back one or more of the changes? Fortunately, the dataset provides robust support for accepting and rejecting changes in its data by maintaining multiple versions of the data for each row. Each row contains the following versions:
Original Contains the data originally loaded into the row.
Default Contains the default values for the row (specified by the DefaultValue property of the row’s columns). If no defaults have been specified, this contains the same data as Original.
Current Contains updated data for any columns that have been updated, and the same data as Original for columns that have not been updated.
Proposed Valid after calling BeginEdit on a row and before calling the EndEdit or CancelEdit methods of the DataRow, the Proposed version contains changes to data that have not yet been applied to the Current version. If CancelEdit is called, the Proposed version is deleted. If EndEdit is called, the changes in the Proposed version are applied to the Current version.
You can determine whether changes have been made to an item in a row by comparing the item’s Original or Default version with the Current version:
if ( CurrentRow[1, DataRowVersion.Current] == CurrentRow[1, DataRowversion.Proposed] // Take appropriate actio }
You can accept or reject changes to data on a row-by-row basis:
if ( CurrentRow[0, DataRowVersion.Current] == "" // Blank field not allowe CurrentRow.RejectChanges() els CurrentRow.AcceptChanges() }
Finally, you can check the current state of a row by checking the row’s RowState property:
string CurrentState CurrentState = CurrentRow.RowState if ( CurrentState == DataRowState.Modified // Take appropriate actio }
There are five possible row states.
Added Represents a row that has been added to the table prior to AcceptChanges being called. Once AcceptChanges is called, this row’s RowState will be set to Unchanged.
Deleted Represents a row whose Delete method has been called.
Detached Represents a newly created row that has not yet been added to a DataRowCollection, or a row that has been removed from a DataRowCollection but not destroyed.
Modified Represents a row whose data has changed, but whose AcceptChanges method has not been called.
Unchanged Represents a row whose data has not changed since the last call to AcceptChanges.
Once you’ve called AcceptChanges on the dataset, you would call the Update method of the DataAdapter used to load data into the dataset to update the underlying database with the new values.
One of the coolest new features of ADO.NET is the ability to create strongly typed datasets. Typed datasets are special classes, generated by the xsd.exe command-line utility, that inherit from the DataSet class. They use an XSD schema to create additional public properties and methods that allow you to access the columns of the dataset’s tables directly by name, rather than having to use either an index or a late-bound key. This can improve run time performance and reduce the likelihood of errors in coding against the dataset.
You can create typed datasets two ways: you can create them using Visual Studio .NET, or, if you find the auto-generated code more than you need or you want finer control, you can create them manually. Let’s look at the manual procedure first.
Creating a typed dataset manually for the AddTitles example shown earlier requires just a few steps. The following example assumes that you’ve already created an XSD schema for the table or tables in the dataset (you can use the AddTitle.xsd schema included with the practice files), which you can do with the WriteXmlSchema method of a dataset that’s been loaded with data from the table or tables.
Open a command prompt at the location of the XSD schema file.
Create the class for the typed dataset using the xsd.exe utility, located by default in the folder C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin\:
xsd.exe /d /l:cs AddTitle.xsd /n:AddTitle
The /d option specifies that we want to create a dataset, the /l option sets the language as Visual C#, and the /n option specifies that the class should use the namespace AddTitle. AddTitle.xsd is the name of your XSD schema file, which is shown in the AddTitle.xsd listing that follows this section. The output of xsd.exe with these arguments is a .cs class file (in this case, AddTitle.cs).
Compile the class using the csc.exe command-line compiler, located by default in the folder %windir%\Microsoft.NET\Framework\%version%, where windir is the Windows directory, and version is the version of the framework that is installed). Note that the three lines that follow should be entered as a single command:
csc.exe /t:library AddTitle.cs /r:System.dl /r:System.Data.dll /r:System.XML.dl /out:bin/AddTitle.dll
The /t option specifies that you want to compile as a library component (DLL), the /r options specify assemblies that you need to reference, and the /out option directs the compiler to save the compiled assembly in the bin subdirectory of the current directory.
| Tip |
Note that to use xsd.exe and csc.exe without specifying the entire path to the executable, the paths to these programs must be registered in the PATH environment variable for the machine you're working on. The steps necessary to do this are detailed in Appendix C. |
 |
<?xml version="1.0" standalone="yes"?> <xsd:schema id="TitleDataSet" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xsd:element name="TitleDataSet" msdata:IsDataSet="true"> <xsd:complexType> <xsd:choice maxOccurs="unbounded"> <xsd:element name="Titles"> <xsd:complexType> <xsd:sequence> <xsd:element name="title_id" type="xsd:string" minOccurs="0" /> <xsd:element name="title" type="xsd:string" minOccurs="0" /> <xsd:element name="type" type="xsd:string" minOccurs="0" /> <xsd:element name="pub_id" type="xsd:string" minOccurs="0" /> <xsd:element name="price" type="xsd:decimal" minOccurs="0" /> <xsd:element name="advance" type="xsd:decimal" minOccurs="0" /> <xsd:element name="royalty" type="xsd:int" minOccurs="0" /> <xsd:element name="ytd_sales" type="xsd:int" minOccurs="0" /> <xsd:element name="notes" type="xsd:string" minOccurs="0" /> <xsd:element name="pubdate" type="xsd:dateTime" minOccurs="0" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:choice> </xsd:complexType> </xsd:element> </xsd:schema>
|
You can also create a typed dataset using Visual Studio .NET. The following steps walk you through doing just that on a new form modeled after AddTitle.aspx, the form you created in a previous example.
Open Visual Studio, and then open the Chapter_09 project created earlier in the chapter.
Add a new Web Form to the project. Name the form AddTitle_TypedDS.aspx.
Copy the HTML code used in step 5 of the AddTitle.aspx example (see page 303) and paste it into the HTML view of AddTitle_TypedDS.aspx. Change the heading (between the <h3> tags) from Inserting a Title to Inserting a Title with a Typed DataSet.
Switch back to Design view and add a DataAdapter to the page. In the past examples, these have been created programmatically. Visual Studio .NET allows you to create them interactively. Open the Toolbox, click the Data tab, and then drag a SqlDataAdapter from the Toolbox onto the form.
The Data Adapter Configuration Wizard, shown in the following illustration, will appear:
Click Next. You will be asked to select or create a connection.
Create a connection by clicking the New Connection button and the Data Link Properties dialog box will appear. Select the (local)\VSdotNET instance of SQL Server, set the login information to use NT Integrated security and the database to pubs.
The Data Link Properties dialog box, when filled in, should look like the following illustration (assuming you are logged into a Windows account that has rights to connect to the VSdotNET MSDE instance). You can click the Test Connection button to ensure that the connection settings are correct:
Click OK in the Data Link Properties dialog box, and then click Next in the Data Adapter Configuration Wizard. The Choose A Query Type page will appear, as shown in the following illustration.
You will proceed to the Generate The SQL Statements page allowing you to generate the Select statement to use. While you can just type in commands (for instance, SELECT * FROM Titles), the Query Builder provides the means to create a query visually.
Click the Query Builder button. The Add Table dialog box will appear.
Select the Titles table from the list and click Add, and then click Close to close the Add Tables dialog box, which will return you to the Query Builder. You can click the (All Columns) check box to select all columns, or you can select the columns individually. Selecting explicitly the columns you want can often be more efficient. In this example, check each column individually, as shown in the following illustration. Click OK to close the Query Builder dialog box.
Click next on the Generate The SQL Statements page.
The final screen of the Data Adapter Configuration Wizard should appear, as shown in the following illustration.
Click Finish.
A new window should appear at the bottom of the main editing window in Visual Studio .NET. It will have two components, sqlDataAdapter1 and sqlConnection1. Use the Properties windows to change the names to daTitles and cnPubs, respectively. When you’ve renamed the components, the screen should look like the following illustration.
Click on the daTitles component. From the Data menu, select Generate DataSet. The Generate DataSet dialog box will appear, as shown in the following illustration. The default name for the typed dataset will be DataSet1. Change the dataset name to TitleDataSet and click OK.
Switch to the code window by pressing F7, or by selecting View, and then Code. At the top of the file, add the following using clause:
using System.Data.SqlClient;
Add the following code to the Page_Load event handler:
if ( this.IsPostBack cnPubs.Open() tr TitleDataSet.titlesRow MyTitleRow MyTitleRow = titleDataSet1.titles.NewtitlesRow() MyTitleRow.title_id = title_id.Text MyTitleRow.title = title.Text MyTitleRow.type = type.Text MyTitleRow.pub_id = pub_id.Text MyTitleRow.price = decimal.Parse(price.Text) MyTitleRow.advance = decimal.Parse(advance.Text) MyTitleRow.royalty = int.Parse(royalty.Text) MyTitleRow.ytd_sales = int.Parse(ytd_sales.Text) MyTitleRow.notes = notes.Text MyTitleRow.pubdate = DateTime.Parse(pubdate.Text) titleDataSet1.titles.AddtitlesRow(MyTitleRow) this.daTitles.Update(titleDataSet1) titlegrid.DataSource = titleDataSet1.Tables[0] titlegrid.DataBind() finall cnPubs.Close() els //To prevent conflicts on multiple inserts, we ca //generate a random value for title_i Random RandomNum = new Random() title_id.Text = "XX" + String.Format("{0:000#}", RandomNum.Next(9999)) }
Save the page and code-behind module.
Build the project.
Test the page by right-clicking AddTitle_TypedDS.aspx, selecting Browse With, and then selecting Microsoft Internet Explorer. The resulting screen should look almost exactly like the previous AddTitles.aspx example, with a different heading.
Now when you create the dataset, you create an instance of TitleDataSet—the class that implements the typed dataset—instead of DataSet. When you look at the code Visual Studio .NET adds to the code-behind module, you will see the following declaration (note that in Visual Studio .NET 2003, this declaration is hidden in the region marked Web Form Designer Generated Code):
protected Chapter_09.TitleDataSet titleDataSet1;
Similarly, you’ll see strong types for the table and row. The code that you added to the Page_Load event handler, for example, includes a strongly typed declaration for a row in the Titles table:
TitleDataSet.titlesRow MyTitleRow;
Another useful feature of typed datasets is that when you use them with Visual Studio .NET, you automatically get IntelliSense code completion for all the members of the TitleDataSet. This makes coding against a typed dataset much easier and considerably less error prone. The following illustration shows how easy it is to choose the correct field from a typed dataset using IntelliSense.
It’s important to note that when you’re programming against a typed dataset (rather than a standard dataset), the fields of the typed dataset are exposed as strongly typed properties. This means that if you try to assign data of an incompatible type to a property, you’ll get an error at compile time (allowing you to fix the problem before your users find it). Therefore, typed datasets make it easier to create error-free data access code.
The DataView class provides a means for sorting, searching, editing, filtering, and navigating DataTable objects. DataViews can also be data-bound, as you’ll see in later examples. Data binding allows ASP.NET server controls to display bound data automatically, even when multiple rows or columns of data are contained in the datasource. This greatly simplifies the display of data in ASP.NET. You can access an unfiltered view of the data in a table in a dataset as follows:
DataView MyDV MyDV = MyDS.Tables[0].DefaultView;
Once you have a reference to the DataView, filter it by adding an expression to the RowFilter property:
myDV.RowFilter = "type='business'";
Search the DataView using the Find method:
Dim RowNum As Integer = myDV.Find("BU1032");
Sort the DataView by adding an expression to the Sort property:
myDV.Sort = "type, title_id DESC";
You’ve already seen some basic examples of binding to a DataView. You’ll look at data binding in more detail later in this chapter.
|
|
Javascript debugger
Javascript examples