- These tables are accessed through a Tables property that refers to a collection of DataTable objects in the DataSet. - Let’s look at the DataTable object and the collections that hold information on the data in the table and the cache. - .NET Data Providers The ADO.NET classes contain .NET data providers that encapsulate a connection to a data source and the functionality to read, change, and update data in the data source. - DataRowCollection DataRow DataRow encapsulates a row of data in the table. - TABLE 6-2 Collections in the DataTable Object. - The different providers and the products they service will be tested in the exam.. - In the case of SQL Server, the SqlCommand is used to execute a stored procedure on the server.. - The Objects of the .NET Data Provider. - The columns to be returned are listed in the select_list parameter. - to select all columns in the table. - The DISTINCT argument removes all duplicates in the returned data. - The SELECT statement returned all the rows in the table. - This query results in the following output:. - in the SELECT statement;. - The INNER keyword is optional, as it is the default join in the ANSI92 SQL standard.. - Inner join The inner join combines tables on the basis of the equality comparison of data values in common columns in the two tables. - Only rows that match the comparison are returned in the result set.. - Outer join The outer join combines rows from two tables on the basis of the equality comparison of data values in common columns in the tables.. - In the FROM clause, you added the JOIN clause to specify that you want the tables on either side of the JOIN clause to be connected. - This results in the preceding output. - If the column list is not used, the values must be listed in the column order of the table.. - For example, to insert a new employee in the Employees table, you could use the following statement:. - If it is not used, the order of the values in the VALUE clause must match the column order of the table.. - The data types of the columns in the result set should match the data types of the columns in the table.. - The result set must have data for all required columns in the destination table.. - In the following example, you will retrieve the price list from the Products table and save the product and the price in a new table. - if you don’t use a WHERE condition, every row in the table is updated.. - Use the SET keyword to specify the new value for a column in the row.. - Always include a WHERE clause in the UPDATE statement. - If you issue the DELETE statement without a WHERE clause, the statement will remove all the rows in the table.. - Always include a WHERE clause in the DELETE statement. - otherwise, all rows in the table will be deleted.. - Stored procedures are stored in the procedure cache of the database server, resulting in faster execution if the stored procedure is called repeatedly.. - In the preceding example, the parameter @C_NAME was added to the stored procedure. - A transaction is a group of related operations that either succeed or fail as one unit—the transaction will either commit or roll back, respectively. - all processing will be persisted in the database.. - The schema ensures that the data in the DataSet is presented in a normalized fashion. - Constraints are added to the Constraints collection of the DataTable object, as you can see in the following code segment:. - Figure 6-4 shows the data model for the DataSet you will work with in the next section. - You will need to add some code to the code module that will create the DataSet as is detailed in the next steps.. - Create the DataTable objects for the DataSet by using the Add() method of the Tables collection in the DataSet. - The data type that is used for the dtOrderID column must be a valid data type in the .NET Framework. - Adding the remaining columns to the DataTable objects results in the following code listing. - The DataTable objects in the DataSet are related to each other, and you can implement that relationship by using a couple of objects available in the DataSet object.. - One common candidate for a primary key is a column that represents the ID of the data stored in the table, such as an OrderID. - The primary key is used to ensure the uniqueness of the data stored in the rows of that table.. - The reference is such that no entry of data is allowed in the table if there is no corresponding entry in the primary key column of the other table. - Figure 6-5 shows the relationships in the DataSet you have been building.. - SetDefault Sets the value in the foreign key to the default value for the column(s).. - The DataRelation object is the final object for us to look at in the DataSet.. - To create a DataRelation object, you add it to the Relations collection of the DataSet object as in the following code segment:. - Process the data in the row. - In this exercise, you will add constraints and work with the data in the DataSet.. - Now, go back to the exercise you started earlier in the chapter. - Add constraints to the end of the makeDataSet() method to relate the four DataTable objects in the DataSet . - which is part of the DataTable:. - The index of the DataRow object can also be used with the DataColumn name, as in the following line of code:. - it is initialized by passing a comma-delimited list of data values, as shown in the following code segment:. - check out that topic in the section “Access and Manipulate XML Data” later in this chapter.. - Now that you know how to add data, you will populate the DataSet in the DataSetTest project.. - Create a new method in the Service1 class with the following signature:. - This code also goes in the populateDataSet() method.. - Define the order that will be inserted in the Orders DataTable . - The data in Table 6-10 should be inserted, again using 0 (zero) for numeric values and empty strings for string values for columns that are not in the table.. - The code that inserts the data in the Orders DataTable should look like this:. - Enter the following code in the Service1 class:. - DOM uses an object model to operate on the data in the XML document, while SAX uses an event-driven API to work with the data. - In the true sense of the word, everything in DOM is a node. - The following objects are in the DOM:. - Element objects This node represents the elements in the XML document.. - Every object in the DOM is a node that defines some properties that are available when you work with the document. - This example uses an XML file that is provided in the Chapter 6 folder of the CD that accompanies this book. - We’ll also use the System.Xml namespace, because that is where all support for XML in the .NET Framework comes from.. - go through and display all the nodes in the document PrintNodes(Console.Out, node). - Text placed in the public domain by Moby Lexical Tools, 1992.. - With the XmlReader class, you can process the XML document using techniques similar to the ones available in the Simple API for XML (SAX) packages.. - Create a Visual Basic .NET source file in the same directory that contains the Macbeth.xml file, and name it XmlReader.vb.. - The bold line in the preceding code specifies the handling of whitespace in the XML document. - The XML file that is hard-coded in the program is Employees.xml,. - The whitespace in the XML document is now returned, as are the nodes.. - Once you have an XML document loaded, you can process the data in the nodes by using Visual Basic .NET, or you can take advantage of the transformations that can be performed. - XSL support in the .NET Framework is found in the System.Xml.Xsl namespace. - The transformation will be performed by the following XSL style sheet that is also found in the Chapter 6 folder on the CD, Empl.xsl:. - Then create a Visual Basic .NET source file in the same directory, and name the file Trans.vb.. - The output is an HTML file (Empl.htm) located in the same directory as the program. - One issue that remains after you have transformed a document is validation—the process of guaranteeing that a document meets the rules. - The support for validation is provided through the XmlValidatingReader class that is created from an XmlReader —the XmlValidatingReader has a ValidationType property that controls how an XML document is to be validated.. - Create a Visual Basic .NET source file in the same folder, and name it Valid.vb.. - Using your editor, enter the following code in the Valid.vb source file:. - However, Microsoft included rudimentary XML support for SQL Server in the .NET Framework once the Framework was released.. - To use the XxxDataAdapter, you will create XxxCommands that will be used to populate the DataSet, update the data source from the DataSet, insert data in the data source, and delete data from the data source.. - All the rows in the table will be deleted.. - This chapter looked at a number of special areas of the ADO.NET environment, especially the DataSet object and the use of XML, as well as the core concepts of one of the largest topic areas to be covered in the exam.. - You were exposed to the ADO.NET concepts and the object models that make it possible to treat data in the same way irrespective of where it originated.. - The exam questions may be on totally different topics from the ADO.NET DataSet code that was presented in the question.. - Syntax error in the INSERT statement.. - The columns in the Employees table are not in the indicated order ( int , char , char, int).. - Replace the reference to RowSet in the Add() method with Object().. - You find that the resulting node tree is very large compared to the number of elements and attributes in the XML document.. - The relation object is the equivalent of the INNER JOIN in the database
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt