T. Combined with XML
ADO.NET provides a wide range of support to XML in DataSet, while the XML functionality extension in SQL Server2000 or later can be fully applied in ADO.NET. You can use SQLXML to access the XML functionality extension provided in SQL Server2000 and later versions. Below is some tips information using XML and ADO.NET.
I. Dataset and XML
DataSet and XML's perfect integration allow you to do the following:
1 Enter a DataSet plan or related structure from the XSD plan;
The following example illustrates the structure of an XSD file, where myDataSet is our DataSet element, which contains a Customers composite type element. We can map to create a table: Customers (Customerid, CompanyName, Phone), It also defines our DataSet plan or structure:
XMLns = "" XMLns: XS = "http://www.w3.org/2001/xmlschema" XMLns: MSData = "URN: Schemas-Microsoft-COM: XML-MSDATA"> Minoccurs = "0" /> Minoccurs = "0" /> 2 load a DataSet from the XML file; To populate DataSet from an XML file, use the READXML method of the DataSet object. The following example shows how to read data from an XML file to a DataSet: 'Visual Basic Dim myds as dataset = new dataset MyDS.Readxml ("Input.xml", XmlreadMode.Readschema) 'C # DataSet myds = new dataset (); MyDS.Readxml ("Input.xml", XmlreadMode.Readschema); 3 Increase a DataSet program from an XML file when there is no plan to provide a plan; to load the DataSet program information from an XML file, you can use the ReadXmlschema method of the DataSet object. If you do not provide a plan, you can also use InferXmlschema from the XML file to the DataSet plan. The following example shows how to infer the DataSet from an XML file from an XML file from an XML file: 'Visual Basic Dim myds as dataset = new dataset Myds.inferxmlschema ("Input_od.xml", New String [] {"URN: Schemas-Microsoft-com: OfficeData"}) 'C # DataSet myds = new dataset (); Myds.inferxmlschema ("Input_od.xml", New String [] "URN: Schemas-Microsoft-Com: OfficeData"); 4 Writing a Dataset in the XSD format plan; The following example shows how to load a DataSet from an XSD file from an XSD file from an XSD file: 'Visual Basic Dim myds as dataset = new dataset MyDS.Readxmlschema ("Schema.xsd") 'C # DataSet myds = new dataset (); Myds.readxmlschema ("Schema.xsd"); 5 read and write a DataSet in the XML format file. Using DiffGrams to read the content from the DataSet, the following example shows the result of updating the line data in the table before the change is submitted, where the CustomerId is modified for the ALFKI but has not been updated: Note: You can use XPath queries and XSLT conversions in your DataSet to simultaneously use XML functionality, or provide a related view, or create a copy of XML document data. II. Planned interface When you load a DataSet from an XML file, you can load a DataSet from the XSD plan, or you can predetermine the tables and columns before loading data. If there is no XSD plan or you don't know that the table is determined by the XML file content, then you can use an XML document structure inference plan. The planned interface is a very useful porting tool, but it should limit the application in the design phase, just because of the following points: 1 Inference Plan will propose additional processing to affect the improvement of application performance; 2 All columns will be a data type: String; 3 The inferior process is uncertain. That is to say, it is based on XML files, not based on intentional plans. III. SQL Server for XML query If you want to return to the FOR XML query results such as SQL Server, you can create an XMLReader with SQL Server.NET Data Provider. IV. SQLXML management class In the .NET framework, the SQLXML management class uses Microsoft.Data.sqlxml namespace. It allows you to perform XPath query and XML template files, just like using XSLT conversion data. The latest version is SQLXML3.0. U. More useful skills I. Avoid automatic incremental conflicts Like most databases, DataSet allows you to identify the columns automatically fill the incremental increment when adding new data. When using an automated increment, the incremental value of the local DataSet should be avoided to conflict with the incremental value of the database. To avoid this, it is recommended to use automatic increment in your database and DataSet, create autoincrementStep to -1 and autoincrementseed to 0 automatic increment columns in your DataSet, and ensure that columns in your database start The direction is incremented. This ensures that the increment in a negative direction will not conflict with an incremental increment in a positive direction. Another method is to use GUID instead of automatic increment. The GUID generated in the DataSet will never be like the GUID generated in the database. If your automatic incrementum is just simply used as a unique value, and does not represent any meaning, it is recommended that you use the Guids instead of automatic increment. They are unique and avoiding additional work generated using automatic incrementation. II. Handling optimism concurrency errors Because DataSet is separated from the database, you should avoid conflicts when multiple clients update database data. There are several solutions to handle optimistic errors. First, add a timestamp column in your table. The second is whether the data of all columns in the check line is static match with you using the WHERE clause in the SQL declaration. The following example shows how to use WHERE condition to handle optimism and errors: 'Visual Basic DIM NWINDCONN As SqlConnection = New SqlConnection ("Data Source = localhost; integrated security = sspi; initial catalog = northwind") Dim Custda As SqldataAdapter = New SqldataAdapter ("Select Customerid, CompanyName from Customers Order By Customerid", NWINDCONN) 'The Update Command Checks for Optimistic Concurrency Violations in The Where Clause. Custda.UpdateCommand = New SqlCommand ("Update Customers (Customerid, CompanyName) Values (@customerid, @companyname)" & _ "Where customerid = @OLDCUSTOMERID and companyNAME = @OLDCompanyName", NWINDCONN) Custda.UpdateCommand.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid") Custda.UpdateCommand.Parameters.Add ("@ companyName", Sqldbtype.nvarchar, 30, "CompanyName") 'Pass the original value.......... Dim myparm as sqlparameter myparm = Custda.UpdateCommand.Parameters.Add ("@ OldcuStomerid", SqldbType.Nchar, 5, "CustomerID") myparm.sourceversion = DATAROWVERSION.ORIGINAL MyPARM = Custda.UpdateCommand.Parameters.add ("@ OldcompanyName", Sqldbtype.NVARCHAR, 30, "CompanyName") MyParm.SourceVersion = DataRowVersion.original 'Add the Rowupdated Event Handler. AddHandler Custda.Rowupdated, New SqlrowUpdatedEventHandler (AddressOf OnrowuPdated) DIM Custds as dataset = new dataset () Custda.Fill (Custds, "Customers") 'Modify the dataset contents. Custda.Update (Custds, "Customers") Dim Myrow As DataRow For Each Myrow In Custds.Tables ("Customers"). Rows Ifmow.haserrors the console.writeline (Myrow (0) & vbcrlf & myrow.rowerror) NEXT Private Shared Sub Onrowdated (Sender As Object, Args As SqlrowUpdatedEventArgs) IF args.recordsaffected = 0 args.row.rowerror = "Optimistic Concurrency Violation Encounted" args.status = updatestatus.skipcurrentrow END IF End Sub 'C # SqlConnection nwindconn = new SqlConnection ("data source = localhost; integrated security = sspi; initial catalog = northwind"); Sqldataadapter Custda = New Sqldataadapter ("Select Customerid, CompanyName from Customers Order By Customerid", NWINDCONN // The Update Command Checks for Optimistic Concurrency Violations in The Where Clause. Custda.UpdateCommand = New SQLCOMMAND ("Update Customers (Customerid, CompanyName) Values (@customerid, @companyname)" "Where customerid = @OLDCUSTOMERID AND CompanyNAME = @OLDCompanyName", NWINDCONN); Custda.UpdateCommand.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid"); Custda.UpdateCommand.Parameters.Add ("@ companies", sqldbtype.nvarchar, 30, "companyName"); // pass the original value to the where clause parameters. Sqlparameter myparm; myparm = Custda.UpdateCommand.Parameters.Add ("@ OldCustomerid", SqldbType.Nchar, 5, "Customerid"); myparm.sourceversion = DATAROWVERSION.ORIGINAL; myparm = Custda.UpdateCommand.Parameters.Add ("@ OldcompanyName", SqldbType.nvarchar, 30, "CompanyName"); myparm.sourceversion = DATAROWVERSION.ORIGINAL; // Add the Rowupdated Event Handler. Custda.rowupdated = new sqlrowupdateDeventhandler (onrowupdated); DataSet Custds = New DataSet (); Custda.Fill (Custds, "Customers"); // modify the dataset contents. Custda.Update (Custds, "Customers"); Foreach (DataRow Myrow In Custds.tables ["Customers"]. Rows) { IF (Myrow.haserRRORS) Console.writeline (Myrow [0] "/ n" myrow.rowerror); } Protected Static Void OnrowuPdated (Object Sender, SqlrowUpdatedEventArgs Args) { IF (args.recordsaffected == 0) { args.row.rowerror = "Optimistic Concurrence Violation Encounted"; Args.status = updatestatus.skipcurrentrow; } } III. Collaborative design You should lock DataSet during you. IV. Only use COM objects only when needed to access ADO ADO.NET is designed to have the best solution for a large number of applications. However, some applications require only ADO objects to provide features, such as Adomd. In this case, you can use the COM object to access the ADO, note that access ADO data using COM objects will affect the execution efficiency of the application. So when designing an application, you should first consider whether ADO.NET meets your design requirements before using the COM object to access the ADO data. V. Comparison of ADO.NET and ADO I. ADO.NET evolves and developed on the basis of the ADO design model, it does not replace the COM programmer's ADO, more, it is to access relevant data sources, XML and application data for .NET programmers. ADO.NET supports diverse development requirements, including creating database clients and intermediate business objects used in suppliers, tools, languages, and web browsers. ADO.NET has many similarities with ADO. II. ADO provides a variety of interfaces that have a powerful, powerful and database dealing with COM programmers. The ADO can be widely used because it supports any call to automated control language (such as VC, VB, and scripting languages). ADO.NET enlarged by ADO basis provides a better interactive platform and upgradeable data access. Creating a new data accessed in ADO.NET provides a few superior places than those in the ADO interface, as described below: 1 Improved combination with XML As XML plays an increasingly important role in the application, it is born with ADO.NET combined with XML. In order to continuous and loading data and the xml format of the data, ADO.NET relies on XML between multiple layers or remote delivery between the client. The special XML expression in ADO.NET provides a method of transmitting data conveniently in any network, including data security boundaries. At the same time, ADO.NET uses the XML tool to perform confirmation, hierarchical query and data between data and data. 2 integrated .NET framework The ADO structure such as Recordset does not use a common design structure, which simulates a data orientation. For example, the cursor used to navigate and get data in ADO, which is different from other such as arrays and set data structures. However, in ADO.NET, because stored data can expose, include arrays and collections through common .NET framework, you can use some public methods to deal with your related data. 3 Improvement of support for discrete business models ADO uses RecordSet to provide limited support for discrete access. ADO.NET introduces a new object Dataset, which is a public, stored expression form of related data, which is designed to discrete at any time, which does not maintain a lasting connection with external data, which is packaged, store , Good way to exchange, continuation, and loading data. That is to say, any operation of the data is carried out locally, and does not directly deal with the real database. 4 The control of data access behavior is clear The ADO included in the application does not always require and specify hidden behavior to limit the performance of the application. It provides good definitions and pre-behaviors, execution, and semantic elements components in ADO.NET, allow you to locate a normal plot in a highly optimized way. 5 improve the support of the design phase The ADO originates from implicit data information, and this information is metadata based on expensive cost. The metadata in ADO.NET is only a leverage in the design phase, providing better performance and better stability in the execution phase. III. ADO design In order to better understand the ADO.NET model and design ideas, review the concept of ADO is useful. ADO uses a single object Recordset to deal with all data types. The Recordset is used to process only the only-flow data returned from the database, and the data or a batch of storage result sets of the flip server. The change in data will be applied to the database or applied to a batch of data using an optimal query and update. When you create a Recordset, you clarify the task you make, the change of the Recordset results depends primarily on the Recordset parameters you requested. Because ADO uses a single RECORDSET object that can be used in many occasions, this makes the object model in your application is simple. However, it is also difficult to write a public, propheminated and optimized code, because behavior, execution, and a single object described semantic to get a change to a large extent on how objects are created and object access. What data. IV. ADO.NET design ADO.NET is designed in consideration of developers to face tasks and issues faced when using data. I would rather use a single object to perform a large number of tasks, and it is not as specified in ADO.NET to specify the functional factors of each object to complete each task. Recordset functionality in ADO is broken down into a few clear objects in ADO.NET: DataReader, providing fast, only entering and read-only access to query results; DataSet, store data; DataAPter, in DataSet and data sources There is a bridge between the bridge; ExecutenonQuery, does not return the line; ExecuteScalar, returns a single value rather than a rowset. Here are some detailed description: 1 only, read-only data flow Applications, especially intermediate applications, often handle a series of results, requiring no user interaction and no update or rollback results when they read. In ADO, use the RECORDSET to enter only the cursor and only the lock when performing such data. In ADO.NET, DataReader optimizes the performance performance of this data, which provides data from the database by providing a non-buffered, only-to-read-only data stream. 2 return a single value To get a single value in the ADO, you need to create a RECORDSET-> read result -> Get a single value -> Close Recordset. In ADO.NET you can use the ExecuteScalar method of the Command object without additional operations to get a single value. 3 Discrete data access ADO uses client cursors to locate access to discrete data, and Dataset in ADO.NET can clearly achieve access to discrete data. DataSet provides a public, completely discrete data representation from a variety of different data sources because DataSet is completely independent of the data source. It doesn't matter that your data is from the database, or from the XML file, or from the application. A simple DataSet can load data from multiple different databases or non-database sources. Then use the DataRelation to create a connection between multiple tables, although the RECORDSET's MSDataShape provider can implement a hierarchical query, but DataSet provides higher stability to process discrete data. At the same time, DataSet provides data between remote clients and servers in XML file format. 4 get data and update data from the database ADO.NET provides better implementation phase performance and visibility. For example, when using an ADO's RecordSet object, you must use Update, Insert or delete declaration for each row that needs to change the result. ADO generates these statements, in the execution phase, is the need to pay an expensive price to obtain metadata. In ADO.NET, specify Update, Insert or delete commands like custom business logics (such as a stored procedure), you can use DataAdapter to implement it all. DataAdapter racks a bridge between DataSet and data sources. Let you collect metadata information in the data source as needed in the execution phase. Thereby improving the performance performance of the application. V. type of data In ADO, all results returns a Variant data type, in ADO.NET, you can get the data type of column itself. The data type can be defined in the system.data.sqltypes namespace. W. For more information on ADO and ADO.NET, please refer to Microsoft information: ADO.NET for the ADO Programmer to sum up: Through this article, I hope to communicate and learn together with you, please refer to you, thank you! ADO.NET Best Practices (on)
