.NET Data Access Architecture Guide (2)

This article provides guidelines for implementing ADO.NET-based data access layers in multi-layer .NET applications ... Get a single line of data in this scenario, which will acquire a set of specified columns from the data source. Single line of data. For example, you get a customer ID and want to find details related to the customer; or get a product ID, and hope to get product information. Method Compare If the binding operation is to be performed on a row of data obtained from the data source, you can populate the DataSet or DataTable object with the SqlDataAdapter object, which is the same as those described in the previously discussed multi-line data and repeating scenes. However, this object should be avoided unless otherwise requiring a DataSet or DataTable object. If you need to get a single line of data, use one of the following methods:

Use stored procedure output parameters. Use the SqlDataReader object. Both methods avoid universal additional overhead of the server-side creation result set and create a DataSet object in the client. The relative performance of each method relies on whether the intensity level and whether the database link is enabled. When the database link is enabled, the performance test indicates that the stored procedure method is similar to the SqlDataReader method in the high-intensity environment (more than 200 links at the same time). Use stored procedure output parameters to use stored procedure output parameters:

To get a row of data from a linked poolization enabled multi-layer web application. Use the SqlDataReader object below, you need to use the SqlDataReader object:

In addition to data values, metadata is required. The column metadata can be obtained using the GetSchematable method of the data reader. Did not use a link poolization. When link pool is invalid, the SqlDataReader object is a good way in all intensity environments; performance tests indicate that this method is approximately 20% higher than storage process methods when the 200 browser link is linked. More information

If you know that the query results need to return a line, use the commandbehavior.singlerow enumeration value when calling the SQLCOMMAND object method. Some suppliers such as OLE DB .NET data supplies, use this skill to optimize performance. For example, the supplier uses an IROW interface (if this interface exists) instead of a higher IROWSET interface. This parameter has no effect on the SQL Server .NET data supplier. When using the SqlDataReader object, the output parameters should always be used through the type of Accessor of the SqlDataReader object, such as getString and GetDecimal. This avoids unnecessary type conversion. Get a single data in this scenario to get a single data. For example, after the product ID is provided, you want to query a single product name; or give the customer name, you want to query the customer's credit rating. In this scenario, in order to obtain single data, it is usually not desirable to raise additional overhead created a DataSet object or even a DataTable object. Maybe just want to check if there is a specific row in the database. For example, when the new user registers on the website, you need to check if the selected username already exists. This is a very special example in a single data query, but in this example, it is enough to return a simple Boolean return value. Methods Compare the following methods when obtaining single data from the data source:

The ExecuteScalar method for the SQLCommand object with the memory process. Use stored procedures to output or return to parameters. Use the SqlDataReader object. The ExecuteScalar method returns the data item directly because it is designed to return only a single value, which requires less code compared to the stored procedure output parameters and the SqlDataReader method. In terms of performance, the stored procedure output or return parameters should be used because the test results show that the stored procedure method provides consistently from low-intensity to high-intensity environments (links to 200 browser links at the same time). Performance. MORE INFORMATION If the query executed by the ExecuteQuery method returns multiple columns and / or rows, this method only returns the first column of the first row. Configure Internet applications frequently through firewalls to configure Internet applications to enable it to connect to SQL Server through the firewall chain. For example, many web applications and main structural components of the firewall are peripheral networks (also known as DMZ or non-military zones), which are used to isolate high-end web servers and internal networks. When connecting to SQL Server through a firewall, you need to configure the firewall, customer, and server. SQL Server provides customer web applications and server web applications to help configure it. Select a network library When you establish a link through a firewall, use the SQL Server TCP / IP network library to simplify configuration, which is the default option for SQL Server2000 installation. If you use the previous version of SQL Server, you can check if the TCP / IP has been configured as the default network library in the client and server-side network applications. In addition to configuration advantages, use TCP / IP libraries also means:

Benefiting from the improved performance and increased extensibility of bulk data. Avoid additional security information related to the specified pipeline. TCP / IP must be configured on the customer and server computer, because most firewall limits the port passing port, so you must carefully consider the port number used by SQL Server. Configure the default instance of the server SQL Server to monitor the 1433 port. However, the specified instance of SQL Server 2000 dynamically assigns the port number when they are turned on. Network administrators have hopes to open a range of ports in the firewall; therefore, when using the firewall to specify instances of SQL Server, the instance is configured using the service network application to make it listening to a specific port. The administrator then configures the firewall so that the firewall allows traffic to reach the port listening to a particular IP address and server instance. Note that the source port number used by the client network library is dynamically allocated between 1024-5000. This is a standard practice for TCP / IP client applications, but this means that the firewall must allow any port traffic to this range to pass. For more information on ports used by SQL Server, see INF: P on the Microsoft Product Support Services website, see the TCP port required to communicate SQL Server via the firewall. Dynamic Lookup Specify instance If you change the default port listens to SQL Server, you must configure the client to link it to this port. For more details, see the configuration client section in this article. If the port number of the SQL Server 2000 default instance is changed, the client will cause a link error. If there are multiple SQL Server instances, the latest version of the MDAC data access stack (2.6) will perform dynamic lookup, and the specified instance is positioned using the User Data Bad Protocol (UDP) negotiation (via UDP port 1434). Although this approach may be effective in the development environment, it is not much working in the current environment, because the firewall will prevent UDP negotiation traffic under the typical question. In order to avoid this, always configure the client to link to the configured destination port number. Configuring the client should configure the client to use the TCP / IP network library to SQL Server, and should ensure that the client library uses the correct destination port number. Use the TCP / IP network library to use the SQL Server client network library to configure the client. In some installation versions, this application may not be installed to the client (such as a web server). In this case, you can solve it as follows: The "NetWork Library = DBMSSOCN" name-value supplied by the link string is used to specify the network library. String DBMSSOCN is used to identify TCP / IP (socket) libraries. Note that when using the SQL Server .NET data supply, the default settings of the network library use "dbmsoCN".

Modify the registry on the client machine and set TCP / IP to the default library. Specify port If the instance of SQL Server is configured to listen to other ports other than the default 1433, then the link to the port number can be specified:

Use the client network application to specify the port number using the "Server" or "Data Source" name-value pair provided to the link string. To use a string in the format: "Data Source = ServerName, PortNumber" Note that ServerName can be an IP address, or a Domain Name System (DNS) name, in order to optimize performance, you can use IP addresses to avoid DNS queries. Distributed Transaction Process If developed service components using COM Distributed Transaction Processing and Microsoft Distributed Transaction Coordinator (DTC) services, you need to configure firewalls to allow DTC streams to be in different DTC instances and DTCs and resources. The manager (such as SQL Server) flows. Processing BLOBS Currently, many applications have external data formats, such as video formats in addition to processing many traditional strings and digital data. Data format types of graphics, sound and video are different. However, from a storage perspective, they can be considered as binary data blocks, typically referred to as BLOBS (binary large objects). SQL Server provides binary, varbinary, and image data formats to store BLOBS. Without the name, the BLOB data can also be referred to as a file-based data. For example, you might want to store binary annotation fields related to a particular row. SQL Server provides NTEXT and TEXT data types for this purpose. Typically, for binary data less than 8 kB, use Varbinary data types. For binary data exceeding this size, use Image. Table 2 brings together the main features of each data type. Table 2 Data Type Characteristic Data Type Size Description Binary range from 1-8KB. The storage size is a specified size plus 4 bytes. The fixed length binary data Varbinary range ranges from 1-8 kB. The storage size is an actual size of the data plus 4 bytes. Variable Length Binary Data Image From 0-2GB size variable length binary data large-capacity variable length binary data TEXT from 0-2GB size variable length data character type data NTEXT from 0-2GB size variable length data Wide Biode Character Data Stores the BLOB Data SQL Server 7.0 and its later versions have improved the use of BLOB data stored in the database. One reason for this is that the database page size has increased to 8KB. As a result, less than 8kb text or image data does not have to store a separate binary tree structure in the page, but can be stored in a single line. This means reading and writing text, ntext, or image data can be as fast as reading or writing characters or binary strings. After 8KB, a pointer will be created in the row, the data itself is stored in the binary structure of the standalone data page, which inevitably generates an impact on performance. For more information to force Text, NText, and Image data to store in a single line, see the use of Text and Image data topics in SQL Server online books. An optional method for processing BLOB data frequently is that the BLOB data is stored in the file system and stores a pointer in the database column (usually a uniform resource locator -ur link) to reference the correct file. For SQL Server 7.0, you can improve performance in file systems that BLOB data stored outside of the database. However, SQL Server 2000 improves BLOB support, and ADO.NET supports support for reading and writing BLOB data, making BLOB data in the database into a feasible method. The advantages of the BLOB data in the database are stored in the database, bringing a lot of advantages:

Easy to maintain the synchronization of BLOB data and other items in the row. BLOB data is supported by the database, with a single storage stream, easy to manage. The XML supported by SQL Server 2000 can access BLOB data, which will return 64-bit encoded data in the XML stream. A column that contains a filling or variable length character (including wide character) data can perform SQL Server full text search (FTS) operations. It is also possible to perform FTS operations on the formatted text-Word or Excel document containing formatted text in the Image field. Write the BLOB data to the code below to demonstrate how to use ADO.NET to write binary data obtained from a file to the SQL Server Image field. public void StorePicture (string filename) {// Read the file into a byte array FileStream fs = new FileStream (filename, FileMode.Open, FileAccess.Read); byte [] imageData = new Byte [fs.Length]; fs.Read (imageData, 0, (int) fs.Length); fs.Close (); SqlConnection conn = new SqlConnection ( ""); SqlCommand cmd = new SqlCommand ( "StorePicture", conn); cmd.CommandType = CommandType.StoredProcedure; CMD.Parameters.Add ("@ filename"; cmd.parameters ["@ filename"]. Direction = parameterDirection.input; cmd.parameters.add ("@ blobdata", sqldbtype.image); cmd.parameters [ "@blobdata"]. Direction = parameterDirection.input; // store the byte array with the image field cmd.parameters ["@ blobdata"]. value = imagedata; try {conn.open (); cmd.executenon query (); } Catch {coven;} finally {conn.close ();}} Read BLOB Data from the database You must use the Commandbehavior.SequentialAracse to read the COMMANDATAREADER object with the ExecuteReader method to read the line containing the BLOB data. If this enumerate value is not available, the reader only sends a line of data from the client from the server. If the row contains BOLB data, this indicates that a large amount of memory is to be taken. By utilizing an enumerated value, a better control is obtained because the BLOB data is only issued when it is referenced (for example, using the GetBytes method, you can control the number of bytes). This is shown in the code snippet below.

// Assume previously established command and connection // The command SELECTs the IMAGE column from the tableconn.Open (); SqlDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess); reader.Read (); // Get size of image data - pass null as the byte array parameterlong bytesize = reader.GetBytes (0, 0, null, 0, 0); // Allocate byte array to hold image databyte [] imageData = new byte [bytesize]; long bytesread = 0; int curpos = 0; while (bytesread

Handmade transaction. You can write code that uses ADO.NET or Transact-SQL transaction support feature directly during component code or stored procedures. Automation (COM ) transaction. You can add a declaration to the .NET class to specify the properties required for the object transaction processing at runtime. This model allows you to easily configure multiple components to run them within the same transaction. Although the automated transaction model greatly simplifies the distributed transaction process, both models are used to perform local transaction processing (ie, transaction processing executed for single resource manager such as SQL Server 2000) or distributed transaction (ie, Transaction processing performed on multiple resource management on a remote computer). You may try to benefit from an easy programming model with automation (COM ) transaction. This advantage is more pronounced in a system with multiple components to perform a database update. However, in many cases, additional overhead and performance loss caused by this transactional model should be avoided. This section will guide you to select the most appropriate model based on a specific application environment. Selecting a transaction model Before selecting a transaction model, you should first consider whether you really need transaction. Transaction processing is the most expensive resource used by server applications, where they do not use, they reduce scalability. Consider the guidelines for managing transaction processing below: transaction processing only when you need to get a lock and need to enhance the ACID rule. Maintain transaction processing as short as possible to minimize the time to maintain the database lock. Never put our customers in the control of the transaction life cycle. Do not use transaction processing for a single SQL statement. SQL Server automatically performs each statement as a single transaction. Automated transaction processing and manual transaction processing Although the programming model has simplified automated transaction processing, especially when performing database updates in multiple components, local transaction processing is always quite fast because they do not need to interact with Microsoft DTCs. This is also the case even if you use automated transaction to a single local resource manager (such as SQL Server) (although performance loss is reduced), because the hand-local transaction process avoids all unnecessary communication between the DTC process. For the following cases, manual transactions are required:

Perform transaction processing for a single database. For the following conditions, automatic transaction processing should be used:

You need to extend a single transaction to multiple remote databases. A single transaction is required to have multiple resource managers (such as databases and Windows 2000 message queues (called MSMQ) resource managers). Pay attention to avoid mixing transaction processing models. It is best to use only one. In an application environment in which performance is good, (even for a single database) Select Automated Transaction to simplify the programming model, this practice is reasonable. Automated transaction processing allows multiple components to easily perform multiple operations in the current transaction processing. Using manual transaction processing For manual transaction processing, you can write code using the ADO.NET or Transact-SQL transaction support feature during component code or stored procedures. In most cases, you should choose to control transaction during storage, as this method provides higher encapsulation, and in terms of performance, this method is compatible with transaction with ADO.NET code. Use the ADO.NET to perform manual transaction processing ADO.NET support transaction processing objects, using this object to start a new transaction process, and explicitly control whether transaction processing is executed or rollback. Transaction objects are related to a single database link, which can be obtained by a BEGINTRANSACTION method of the link object. Calling this method is not implying that the next command is issued in the transaction context. Each command must be associated with transaction by setting the transaction property of the command. Multiple command objects can be associated with transaction objects, so multiple operations are packet in a single transaction in a single transaction. More information ADO.NET manual transaction processing default separation level is read, which means that the database controls shared lock when reading data, but data can be modified before the end of transaction processing. This situation potentially produces non-repeatable reading or virtual data. The separation level can be changed by setting the ISOLALATIONLEVEL attribute of the transaction object to the ISOLATIONLEVEL enumeration type. The appropriate separation level must be selected carefully for transaction. It is compromised to the proportion of data consistency and performance. The highest separation level (serialized) provides absolute data consistency, but at the expense of the overall throughput of the system. The lower separation level will make the application easier to expand, but at the same time increase the possibility of errors due to inconsistencies of data. The lower separation grade is suitable for systems that read data in most time, with a minimum write data. Regarding the election of the appropriate transaction level, it is very valuable, see Microsoft Publishing House, known as INSIDE SQL Server 2000, Author Kalen delaney. The manual transaction processing can also be directly controlled by the Transact-SQL statement in the stored procedure. For example, transaction processing can be performed using a stored procedure containing Transact-SQL transaction statements such as Begin Transaction, End Transaction, and Rollback Transaction. More information

If necessary, use the SET TRANSACTION ISOLATION LEVEL statement to control the separation level of the transaction processing during the stored procedure. Reading is the default setting for SQL Server. For more information on SQL Server Separation Level, see Separation Levels in SQL Server Online Book "Access and Modify Data" section. The programming model is simplified using an automated transaction automation transaction because they do not need to explicitly begin a new transaction process, or clearly execute or cancel transactions. However, the biggest advantage of automated transactions is that they can be combined with DTC, which makes a single transaction to be extended to multiple distributed data sources. This advantage is important in large distributed applications. Although it is possible to control distributed transactions by manual DTC, it is possible to control distributed transactions, but automated transaction has greatly simplified workload, and it is designed for components-based systems. For example, a plurality of components can be conveniently configured to perform tasks that contain a single transaction process. Automated transactions depends on distributed transaction processing support features provided by COM . As a result, only the service component (ie, the components derived from the ServicesDComponent class) can use automated transactions. To process the configuration class for an automated transaction, the operation is as follows: Dys in the servicedComponent class located in the EnterpriseServices namespace. Define the transaction requirements of the class through the Transaction property. Enumeration values ​​from TransactionOption determine how to configure classes in the COM class. Other attributes that can be set together with this property include transactional separation levels and timeout caps. In order to avoid that the transaction results must be explicitly selected, the method can be annotated with the AutoComplete property. If these methods are released, the transaction will be canceled. Note that if needed, you can directly select the transaction results. More information

For more information on COM automation transactions, search for "Automation Transaction through COM " can be searched in platform SDK documents. For examples of the .ne T transaction processing class, see How to encode .NET transaction in the appendix. Configure transaction separation levels for COM 1.0 - That is, the transaction separation level running in Windows 2000 is serialized. This provides the highest separation level, which is based on performance. The overall throughput of the system is reduced. Because the resource manager (typically the database is typically) must keep reading and writing during transaction processing. During this time, all other transactions have been blocked, which will greatly impact the application's extension capabilities. With the COM version 1.5 version issued with Microsoft Windows .NET Allows the Component Configuration Transaction Separation Level in the COM directory. Settings related to the root component of the transaction determines the separation level of transaction processing. In addition, the transaction level owned by the internal subcomponents in the same transaction stream must not be higher than the level defined by the component. If this is not the case, when the child component is instantiated, it will cause an error. For the .net management class, the Transaction property supports all public isolation properties. You can use this attribute to specify a special separation level, as shown in the following code:

[Transaction (Transaction = Transaction.Supported, Isolation = TransactioniSolationledLight)] Public class account: servicedComponent {........................................ The status of sexual label is determined. When the root assembly in the transaction stream becomes a non-active state (and control returns the caller), the transaction results are determined. This situation has been demonstrated in Figure 5, which shows a typical bank fund transferred transaction. Figure 5 The transaction flow context is the root object (in this example) changes to an inactive state, and when the customer's method calls return, the transaction result is determined. Any consistency logo in any context is set to false, or if the transaction cancel flag is set to true, then the underlying physical DTC transaction will be canceled. You can control the transaction results from the .NET object in one of the following two ways: You can comment on the method with the AutoComplete property and let the .NET automatically store will decide the transaction results vote. If the method releases exception, this property is utilized, the consistency flag is automatically set (this value finally cancels the transaction). If the method returns without release an abnormality, then the consistency flag will be true. This value indicates that the component is happy to perform transactions. This is not guaranteed because it relies on voting of other objects in the same transaction. You can call the static method setComplete or setabort of the ContextUtIL class, which makes the consistency flags true or false separately. SQL Server errors in severeity greater than 10 will cause the management data supplier to release the exception of the SQLEXCeption type. If the method caches and processes an exception, be sure to cancel the transaction manually, or the method is marked with [AutoComplete] to ensure that the exception can pass the caller. The AutoComplete method performs the following:

Pass the SQLException to call the stack. Pack the SQLEXCEPTION in an external exception and pass back the caller. It is also possible to encapsulate an exception type that is more meaningful to the call. If you can't pass, it will cause the object to not put forward the transaction, so that the database error is ignored. This means that the successful operation of the other objects of sharing the same transaction will be submitted. The following code caches SQLException and then passes it directly back to the caller. Transaction processing will eventually be canceled because the consistency flag of the object is automatically set to false when the object changes inactive.

[AutoComplete] void SomeMethod () {try {// Open the connection, and perform database operation} catch (SqlException sqlex) {LogException (sqlex);... // Log the exception detailsthrow; // Rethrow the exception, causing the Finally {// Close The Database Connection...

Call ContextUtil.Setabort in the CatCH block to terminate transaction. This sets the compatible flag to false. If no exception event occurs, call ContextUtil.setComplete to submit a transaction, which sets the compatible flag to true (default). The code illustrates this method. void SomeOtherMethod () {try {// Open the connection, and perform database operation ContextUtil.SetComplete ();... // Manually vote to commit the transaction} catch (SqlException sqlex) {LogException (sqlex); // Log the exception details ContextUtil.SetAbort (); // Manually vote to abort the transaction // Exception is handled at this point and is not propagated to the caller} finally {// Close the database connection}} Note that if there are multiple... Catch block, call ContextVtil.Setabort when the method starts, and calls ContextUtComplete at the end of the TRY block. With this method, you don't need to repeat the conteutil.setabort in each CatCH block. The setting of the compatible marker determined by this method is only valid when the method is returned. For exception events (or loop exception), it must be passed to the calling stack because it makes the calling code that transaction failed. It allows calling code to make optimized selections. For example, in bank fund transfers, if the debt operation fails, the transfer branch can determine that the debt operation is not executed. If the compatible flag is set to a false and there is no exception event when returning, there is no way to call the code if the transaction will certainly fail. Although the Boolean value can be returned or the Boolean output parameter is set, it should still be consistent, and the abnormal event should be displayed to indicate that there is an error. This code has a standard error handling method, so it is more concise, more compatibility. Data Page is a general requirement to use data to make paging in distributed applications. For example, the user may get a list of books and the list is not fully displayed. Users need to perform some familiar operations on the data, such as browsing the next page or the previous page, or jumps to the first page of the list. Or the last page. This part of the content will discuss options for implementing this feature, as well as each option in performance and scaling. Options Compare Data Patement options:

With SQLDataAdapter's Fill method, the results from the query are filled into the DataSet. Use ADO to use ADO through COM, and utilize server cursors. Manually implement data paging by using the stored process. The optimal option for paging data depends on the following factors:

Scalability Requirements Requirements Requirements The memory and power of the memory and power intermediate server of the network bandwidth database server indicate the size performance test of the number of lines returned by the paging query indicates that the manual method of the stored procedure is on a large stress level. Both provide optimum performance. However, because the manual method is executed on the server, if most of the site features rely on the data paging function, the server performance will become a key element. To ensure that this method is suitable for special environments, you should test all kinds of special requirements. A variety of different options will be discussed below. Using SqlDataAdapter as discussed earlier, SqlDataAdapter is used to populate data from the database into the DataSet, and any of the overloaded Fill methods require two integer index values ​​(as shown in the following code): Public int fix (Dataset DataSet, int StartRecord, int maxRecords, String Srctable; StartRecord value indicates the starting index value from zero. The maxRecord value represents the number of records starting from StartRecord and copies them into the new DataSet. SqlDataAdapter uses SqlDataReader to perform queries and returns the result. SqlDataAdapter reads the results and creates a DataSet based on data from SalDataReader. SqlDataAdapter copies all results through StartRecord and MaxRecords to the new generation of DataSet and discard unwanted data. This means that many unnecessary data will have potentially access to data through the network - this is the main defect of this method. For example, if there is 1000 records, it is required to be 900 to 950 records, then 899 records in front will still cross the network and then dropped. For small number of records, this overhead may be relatively small, but if you target a large amount of data, this overhead will be very huge. Another option to use the ADO implementation paging is to use COM-based ADO. The goal of this approach is to access the server cursor. The server cursor is displayed via the ADO Recordset object. You can set the location of the Recordset cursor to the AduseServer. If your OLE DB supplier supports this setting (such as SQLOLEDB), you can use the server cursor. This allows the cursor to navigate directly to the starting record without the need to pass all data to the user code of access data. This method has the following two shortcomings:

In most cases, it may be necessary to translate records returned to the Recordset object into content in the DataSet to use in the customer-managed code. Although OLEDTAADAPTER is indeed acquiring the ADO Recordset object and translating it into DataSet, there is no function of starting and ending using special records. The only reality option is to move the start record to the Recordset object, loop each record, and then copy data to the new DataSet that manually generated. This kind of operation, especially using COM Interop, which advantage may not only do not need to transmit excess data on the network, especially for small Dataset more obvious. The connection and server cursor are open when the required data is output from the server. On the database server, open and maintenance of cursors require expensive resources. Although this option increases performance, it is possible to reduce scalability due to extended time two-consuming server resources. The last option for providing the data paging discussed by manual implementation in this section is to use the stored procedure to manually implement the page features of the application. For tables that contain unique keywords, the stored procedure is relatively easy. And for tables without a unique key (there should not be many keywords), which is relatively complex. Page with a single keyword table If the table contains a unique keyword, you can use the keywords in the WHERE Terms to create results settings from a particular row. This method is to match the SET ROWCOUNT state used to limit the size setting size, which provides a valid patch principle. This method will be described in the following code stored: CREATE PROCEDURE GetProductsPaged @lastProductID int, @pageSize intAS SET ROWCOUNT @pageSize SELECT * FROM Products WHERE [standard search criteria] AND ProductID> @lastProductID ORDER BY [Criteria that leaves ProductID monotonically increasing ] GO This memory process is only maintained for the value of the LastProductID and increases or decreases the value by the size between the selected continuous calls. Subsidies without a single keyword If you need a paging table, you can consider adding one - such as using the identity column. This allows the paging scheme discussed above. As long as you can create uniqueness by combining two or more areas in the results record, it is still possible to implement a valid paging scheme without a unique keyword table. For example, investigate the following form:

Col1col2col3other column ... a1w ... a1x .a1y .a1z .a2w .a2x .b1w ... b1x. For this table, combined with col, col2, and col3 may have a uniqueness. In this way, the distribution principle can be implemented using methods in the following stored procedures:

CREATE PROCEDURE RetrieveDataPaged @lastKey char (40), @pageSize intAS SET ROWCOUNT @pageSize SELECT Col1, Col2, Col3, Col4, Col1 Col2 Col3 As KeyField FROM SampleTable WHERE [Standard search criteria] AND Col1 Col2 Col3> @lastKey ORDER BY Col1 ASC, Col2 ASC, Col3 ASCGO Customer keeps the final value of the Keyfield column returned by the stored procedure, and then inserts back to the stored procedure to control the paging of the table. Although manual implementation increases the strain on the database server, it avoids unnecessary data on the network. Performance tests indicate that this method is working well throughout the strain level. However, based on the data paging function involved in the site, manually paging on the server may affect the scalability of the application. Performance tests should be run in the environment, find the most appropriate way for the application. Appendix how to enable object structure for a .NET class To enable .NET management class to use Enterprise (COM ) Services to enable .NET management class, you need to perform the following steps: Guide the required class from the SYSTEM. Enterprise Services namespace.

Using System.EnterpriseServices; PUBLIC CLASS DATAACCESSCOMPONENT: Service Class DataAccessComponent: ServicesDComponent adds a construction enabled attribute to this class and specifies the default structure string. The default is saved in the COM directory, and the administrator can use the component service Microsoft Management Console (MNC) Snap-in to maintain the default.

[Constructionenabled (default = "default dsn")] Public class dataComponent: ServicesDComponent provides an alternative implementation of a virtual construct method. This method is called after the object language constructor. The structure string saved in the COM directory is the unique string of the method.

public override void Construct (string constructString) {// Construct method is called next after constructor.// The configured DSN is supplied as the single argument} by Assembly key file or compilation Assembly key Name property that provides a strong name. Any compilation that uses COM service registration must have a strong name. About more information on stronger name assembly, reference: http://msdn.microsoft.com/library/en-us/cpguide/html/cpconworkingwithstrongly- namedassemblies.asp. [Assembly: AssemblyKeyFile ("DataServices.snk")] To support dynamic registration, you can specify the name of the COM application for maintaining assembly elements and application action types, respectively, using attributes ApplicationName and Application Action, respectively.

// the ApplicationName attribute specifies the name of the // COM Application which will hold assembly components [assembly: ApplicationName ( "DataServices")] // the ApplicationActivation.ActivationOption attribute specifies // where assembly components are loaded on activation // Library: components run in the creator's process // Server: components run in a system process, dllhost.exe [assembly: ApplicationActivation (ActivationOption.Library)] the following code fragment is called DataAccessComponent service component that uses the string to get the COM database structure Connect strings.

using System; using System.EnterpriseServices; // the ApplicationName attribute specifies the name of the // COM Application which will hold assembly components [assembly: ApplicationName ( "DataServices")] // the ApplicationActivation.ActivationOption attribute specifies // where assembly components are loaded on activation // Library: components run in the creator's process // Server: components run in a system process, dllhost.exe [assembly: ApplicationActivation (ActivationOption.Library)]. // Sign the assembly The snk key file is created using the // sn.exe utility [assembly: AssemblyKeyFile ( "DataServices.snk")] [ConstructionEnabled (Default = "Default DSN")] public class DataAccessComponent: ServicedComponent {private string connectionString; public DataAccessComponent () {// constructor is called on instance creation} public override void Construct (string constructString) {// Construct method is called next after constructor.// The configured DSN is supplied as the single argumentthis.connectio How to use SqlDataAdapter to retrieve the code below the number of rows to explain how to use the SqlDataAdapter object to generate a Data Set or DataTable command. It retrieves a series of catalogs from the SQL Server Northwind database.

using System.Data; using System.Data.SqlClient; public DataTable RetrieveRowsWithDataTable () {using (SqlConnection conn = new SqlConnection (connectionString)) {SqlCommand cmd = new SqlCommand ( "DATRetrieveProducts", conn); cmd.CommandType = CommandType.StoredProcedure SqlDataAdApter Da = New SqlDataAdapter (CMD); DataTable DT = New DataTable ("Products"); DA.FILL (DT); Return DT;}} Press SQLADAPTER to generate DataSet or DataTable: Create a SQLCommand object to enable storage procedures, And connect it to the SQLConnection object (display) or connection string (not shown). Create a new SqlDataAdapter object and link it to your SQLCommand object. Create a DataTable (or DataSet) object. Use the constructor to name DataTable from the variable. Call the Fill method of the SQLData Adapter object, transfer the retrieval line to DataSet or DataTable. How to use SqlDataReader to retrieve multiple rows The following code shows how to use the SqlDataReader method to retrieve multi-line:

using System.IO; using System.Data; using System.Data.SqlClient; public SqlDataReader RetrieveRowsWithDataReader () {SqlConnection conn = new SqlConnection ( "server = (local); Integrated Security = SSPI; database = northwind"); SqlCommand cmd = new SqlCommand ( "DATRetrieveProducts", conn); cmd.CommandType = CommandType.StoredProcedure; try {conn.Open ();. // Generate the reader CommandBehavior.CloseConnection causes the // the connection to be closed when the reader object is closed return (cmd.ExecuteReader (CommandBehavior.CloseConnection));} catch {conn.Close (); throw;}} // Display the product list using the consoleprivate void DisplayProducts () {SqlDataReader reader = RetrieveRowsWithDataReader (); while (reader. Read ()) {Console.writeline ("{0} {1} {2}", Reader.GetInt32 (0) .tostring (), Reader.getstring (1));} Reader.close (); // ALSO Closes The Connection Due To The // Commandbehavior Enum Used Whenrating The Reader} Press the following steps to use SqlDataReader to retrieve multi-line: Create a SQLCommand object for performing the stored procedure And connect it to the SQLConnection object. Open the link. Generate a SQLDataReader object by calling the Excute Reader method of the SQLCommand object. Read data from the stream, call the READ method of the SQLDataReader object to retrieve the row, and use the classified access program method (such as the Getiut 32 and Get String method) to search the column. When reading, call the Close method. How to use XmlReader to retrieve multiple rows to generate an XMLReader object using SQLCommand objects, which provides flow-based forward access to XML data. This command (usually a stored process) must generate an XML-based result setting, which is usually composed of SQL Server2000 consisting of a SELECT state with valid Terms for XML. The following code segment illustrates this method:

public void RetrieveAndDisplayRowsWithXmlReader () {SqlConnection conn = new SqlConnection (connectionString); SqlCommand cmd = new SqlCommand ( "DATRetrieveProductsXML", conn); cmd.CommandType = CommandType.StoredProcedure; try {conn.Open (); XmlTextReader xreader = (XmlTextReader) cmd.ExecuteXmlReader (); while (xreader.Read ()) {if (xreader.Name == "PRODUCTS") {string strOutput = xreader.GetAttribute ( "ProductID"); strOutput = ""; strOutput = xreader. GetAttribute ( "ProductName"); Console.WriteLine (strOutput);}} xreader.Close ();} catch {throw;} finally {conn.Close ();}} the following code uses the above stored procedure: CREATE pROCEDURE DATRetrieveProductsXMLAS SELECT * From products for xml autogo Retrieve XML data by following steps:

Creating a SQLCommand object Enables the generation of the XML result settings. (For example, using the For XML terms in the SELECT state). Contact the SQLCOMMAND object with a link. Call the EXECUTEXMLREADER method for the SQLCommand object and assign the result to the forward object XMLTextReader. This is the fastest type of XMLReader object that should be used if any of the XML-based authentication that returns data is not required. Read the data using the read method of the XMLTextReader object. How to use the stored procedure output parameter retrieval single row can call a stored procedure, which can return to retrieval data items in a single row in a single row by a way to do output parameters. The following code segments use the stored procedure to retrieve the name and unit price of the product, which is included in the Northwind database.

void GetProductDetails (int ProductID, out string ProductName, out decimal UnitPrice) {SqlConnection conn = new SqlConnection ( "server = (local); Integrated Security = SSPI; database = Northwind"); // Set up the command object used to execute the stored proc SqlCommand cmd = new SqlCommand ( "DATGetProductDetailsSPOutput", conn); cmd.CommandType = CommandType.StoredProcedure; // Establish stored proc parameters // @ProductID int INPUT // @ProductName nvarchar (40) OUTPUT // @UnitPrice money. OUTPUT // Must explicitly set the direction of output parameters SqlParameter paramProdID = cmd.Parameters.Add ( "@ProductID", ProductID); paramProdID.Direction = ParameterDirection.Input; SqlParameter paramProdName = cmd.Parameters.Add ( "@ProductName", SqlDbType.VarChar, 40); paramProdName.Direction = ParameterDirection.Output; SqlParameter paramUnitPrice = cmd.Parameters.Add ( "@UnitPrice", SqlDbType.Money); paramUnitPrice.Direction = ParameterDirection.Output; try {conn.Open (); // USE EXE . CuteNonQuery to run the command // Although no rows are returned any mapped output parameters // (and potentially return values) are populated cmd.ExecuteNonQuery (); // Return output parameters from stored proc ProductName = paramProdName.Value.ToString () Unitprice = (decimal) paramunitprice.value;} catch {throw;} finally {conn.close ();}} Follow the steps: Create a SQLCOMMAND object, and put it with SqlConnection object related. Set the stored procedure parameters by calling the Add method of the SQLCommand's Parameters collection. By default, the parameters are assumed to be output, so the direction of any output parameters must be explicitly set. Note that the direction of all parameters is clear is a good exercise, including input parameters.

Open the connection. Call the EXECUTENONQUERY method for the SQLCommand object. It is in output parameters (and potentially with a return value). Use the Value property to retrieve the output parameters from the appropriate SQLParameter object. Close the connection. The above code segment enables the following stored procedures. CREATE PROCEDURE DATGetProductDetailsSPOutput @ProductID int, @ProductName nvarchar (40) OUTPUT, @UnitPrice money OUTPUTAS SELECT @ProductName = ProductName, @UnitPrice = UnitPrice FROM Products WHERE ProductID = @ProductIDGO how to retrieve a single row SqlDataReader SqlDataReader object can be used to retrieve a single row, And the value from the required column returns the data stream. This is described by the following code:

void GetProductDetailsUsingReader (int ProductID, out string ProductName, out decimal UnitPrice) {SqlConnection conn = new SqlConnection ( "server = (local); Integrated Security = SSPI; database = Northwind"); // Set up the command object used to execute the stored proc SqlCommand cmd = new SqlCommand ( "DATGetProductDetailsReader", conn); cmd.CommandType = CommandType.StoredProcedure; // Establish stored proc parameters // @ProductID int INPUT SqlParameter paramProdID = cmd.Parameters.Add ( "@ProductID",. ProductID); paramProdID.Direction = ParameterDirection.Input; try {conn.Open (); SqlDataReader reader = cmd.ExecuteReader (); reader.Read (); // Advance to the one and only row // Return output parameters from returned Data stream productname = reader.getstring (0); unitprice = reader.getDecimal (1); reader.close ();} catch {throw;} finally {conn.close ();}} Back to Return to SqlDataReader objects :

Establish a SQLCommand object. Open the connection. Call the ExecuteReader object of the SQLDReader object. Retrieving output parameters using the classification of the SqlDataReader object - here is getString and getDecimal. The above code segment enables the following stored procedures:

CREATE PROCEDURE DATGetProductDetailsReader @ProductID intAS SELECT ProductName, UnitPrice FROM Products WHERE ProductID = @ProductIDGO how is designed to return a single value for a single item using ExecuteScalar ExecuteScalar access method. In returns multiple columns or multi-line access events, ExecuteScalar only returns the first example of the first line. The following code shows how to query a product ID, product name: void GetProductNameExecuteScalar (int ProductID, out string ProductName) {SqlConnection conn = new SqlConnection ( "server = (local); Integrated Security = SSPI; database = northwind"); SqlCommand cmd = new SqlCommand ( "LookupProductNameScalar", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add ( "@ ProductID", ProductID); try {conn.Open (); ProductName = (string) cmd.ExecuteScalar ( );} Catch {throw;} finally {conn.close ();}} Follow the steps below to use Execute Scalar to retrieve a single item:

Establish a SQLCommand object that calls the stored procedure. Open the link. Call the ExecuteScalar method, pay attention to the method to return to the object type. It contains values ​​of the first column of retrieved and must be designed to be appropriate. Close the link. The above code enables the following stored procedures:

Parameters CREATE PROCEDURE LookupProductNameScalar @ProductID intAS SELECT TOP 1 ProductName FROM Products WHERE ProductID = @ProductIDGO stored procedure how the output or return parameters to retrieve a single item using the stored procedure can be returned query output or a single value, the following code illustrates the use of the output parameters :

void GetProductNameUsingSPOutput (int ProductID, out string ProductName) {SqlConnection conn = new SqlConnection ( "server = (local); Integrated Security = SSPI; database = northwind"); SqlCommand cmd = new SqlCommand ( "LookupProductNameSPOutput", conn); cmd. CommandType = CommandType.StoredProcedure; SqlParameter paramProdID = cmd.Parameters.Add ( "@ ProductID", ProductID); ParamProdID.Direction = ParameterDirection.Input; SqlParameter paramPN = cmd.Parameters.Add ( "@ ProductName", SqlDbType.VarChar, 40 ); parampn.direction = parameterDirection.output; try {conn.open (); cmd.executenonquery (); productName = parampn.value.to t;} finally {conn.close ();}} Follow these steps to use the output parameter of the stored procedure to retrieve the single value: Create a SQLCommand object that calls the stored procedure. Set any input parameters and a single output parameter by adding SQLPARMeters to the SQLCommand's parameters collection. Open the link. Call the EXECUTE NONQUERY method for the SQLCommand object. Close the link. Retrieve the output value with the Value property of the output SQLParameter. The above code uses the following stored procedures:

CREATE PROCEDURE LookupProductNameSPOutput @ProductID int, @ProductName nvarchar (40) OUTPUTAS SELECT @ProductName = ProductName FROM Products WHERE ProductID = @ProductIDGO The following code shows how to return values ​​to determine whether particular line. From an encoding point of view, this is similar to the use of stored procedure output parameters, in addition to the SQLParameter direction that needs to be explicitly set to ParameterDirection.ReturnValue.

bool CheckProduct (int ProductID) {SqlConnection conn = new SqlConnection ( "server = (local); Integrated Security = SSPI; database = northwind"); SqlCommand cmd = new SqlCommand ( "CheckProductSP", conn); cmd.CommandType = CommandType. StoredProcedure; cmd.Parameters.Add ( "@ ProductID", ProductID); SqlParameter paramRet = cmd.Parameters.Add ( "@ ProductExists", SqlDbType.Int); paramRet.Direction = ParameterDirection.ReturnValue; try {conn.Open () Cmd.executenonQuery ();} catch {throw;} finally {conn.close ();} Return (int) paramret.value == 1;} Press the following steps to check if there is special line: Establish a SQLCommand object that calls the stored procedure. Set the input parameters of the main keywords that contain the rows that need to be accessed. Set a single return value parameter. Add the SQLParameter object to the SQLCommand's Parameter collection and set it to ParameterDireetion.ReturnValue. Open the link. Call the method of ExecuteNonQuery of the SQLCommand object. Close the link. Retrieve the return value by using the Value property of the return value SQLParameter. The above code uses the following stored procedures:

Create Procedure CheckProductSp @ProductId Intas if exists (Select ProductID from Products Where ProductId = @ProductID) Return 1 Else Return 0go how to use SqlDataReader to retrieve a single item. By calling the ExecuteReader method of the command object, you can use the SqlDataReader object to obtain a single output value. This requires a little more code, because the SqlDataReader Read method must be called, then the required value is retrieved by the reader access program method. The use of the SqlDataReader object is described in the following code:

bool CheckProductWithReader (int ProductID) {SqlConnection conn = new SqlConnection ( "server = (local); Integrated Security = SSPI; database = northwind"); SqlCommand cmd = new SqlCommand ( "CheckProductExistsWithCount", conn); cmd.CommandType = CommandType. StoredProcedure; cmd.Parameters.Add ( "@ ProductID", ProductID);. cmd.Parameters [ "@ ProductID"] Direction = ParameterDirection.Input; try {conn.Open (); SqlDataReader reader = cmd.ExecuteReader (CommandBehavior.SingleResult Reader.read (); Bool Brecordexists = Reader.GetInt32 (0)> 0; Reader.close (); Return BRecordexists;} catch {throw;} finally {conn.close ();}} The above code uses the following stored procedures:. CREATE pROCEDURE CheckProductExistsWithCount @ProductID intAS SELECT COUNT (*) FROM Products WHERE ProductID = how @ProductIDGO coded ADO.NET manual transactions the following code shows how to use transaction support SQL Server NET data provider to provide protection affairs branch gold Transfer operation. This operation is transferred between two accounts located in the same database.

public void TransferMoney (string toAccount, string fromAccount, decimal amount) {using (SqlConnection conn = new SqlConnection ( "server = (local); Integrated Security = SSPI; database = SimpleBank")) {SqlCommand cmdCredit = new SqlCommand ( "Credit" , conn); cmdCredit.CommandType = CommandType.StoredProcedure; cmdCredit.Parameters.Add (new SqlParameter ( "@ AccountNo", toAccount)); cmdCredit.Parameters.Add (new SqlParameter ( "@ Amount", amount)); SqlCommand cmdDebit = new SqlCommand ( "Debit", conn); cmdDebit.CommandType = CommandType.StoredProcedure; cmdDebit.Parameters.Add (new SqlParameter ( "@ AccountNo", fromAccount)); cmdDebit.Parameters.Add (new SqlParameter ( "@ Amount" , amount)); conn.Open (); // Start a new transaction using (SqlTransaction trans = conn.BeginTransaction ()) {// Associate the two command objects with the same transaction cmdCredit.Transaction = trans; cmdDebit.Transaction = Try {cmdcredit.executenonquery (); cmddebit.executenonQuery () // Both Commands (CREDIT AND DEBIT) WERE SUCCESSFUL TRANS.COMMIT ();} catCH (Exception EX) {// Transaction Faled Trans. Rollback (); // log exception details.......... How to use the Transact-SQL to perform the following stored procedures describe how to perform a bin transfer operation of transactions within the Transact-SQL process.

CREATE PROCEDURE MoneyTransfer @FromAccount char (20), @ToAccount char (20), @Amount moneyASBEGIN TRANSACTION - PERFORM DEBIT OPERATION UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountNumber = @FromAccount IF @@ RowCount = 0 BEGIN RAISERROR ( ' Invalid From Account Number ', 11, 1) GOTO ABORT ENDDECLARE @Balance money SELECT @Balance = Balance FROM ACCOUNTS WHERE AccountNumber = @FromAccount IF @BALANCE <0 BEGIN RAISERROR (' Insufficient funds', 11, 1) GOTO ABORT END - PERFORM CREDIT OPERATION UPDATE Accounts SET Balance = Balance @Amount WHERE AccountNumber = @ToAccount IF @@ RowCount = 0 BEGIN RAISERROR ( 'Invalid To Account Number', 11, 1) GOTO ABORT END COMMIT TRANSACTION RETURN 0 ABORT: ROLLBACK TRANSACTION GO that The stored procedure uses Begin Transaction, Commit Transaction, and Rollback Transaction status manual controlled transactions. How to encode a transactional .NET class The following example is three service NET classes, which are configured or used for automatic transactions. Each class comes with a Transaction property, which will determine if a new transaction stream or a data stream is shared if the object is shared. These elements work together to perform bank billet transfer. The Transfer class is configured with the RequiresNew transaction property, while the debits and CREDIT classes are configured with the request attribute. This way, share the same transaction three objects when running.

using System; using System.EnterpriseServices; [Transaction (TransactionOption.RequiresNew)] public class Transfer: ServicedComponent {[AutoComplete] public void Transfer (string toAccount, string fromAccount, decimal amount) {try {// Perform the debit operation Debit debit = new Debit (); debit.DebitAccount (fromAccount, amount); // Perform the credit operation Credit credit = new Credit (); credit.CreditAccount (toAccount, amount);} catch (SqlException sqlex) {// Handle and log exception details // Wrap and propagate the exception throw new TransferException ( "Transfer Failure", sqlex);}}} [Transaction (TransactionOption.Required)] public class Credit: ServicedComponent {[AutoComplete] public void CreditAccount (string account, decimal amount) {SqlConnection conn = new SqlConnection ( "Server = (local); Integrated Security = SSPI"; database = "SimpleBank"); SqlCommand cmd = new SqlCommand ( "Credit", conn); cmd.CommandType = CommandType.StoredProcedure; CMD.Parameters.Add ("@ Accountno", Account); cmd.parameters.add (New Sqlparameter); try {conn.open (); cmd.executenonquery (); } catch (SqlException sqlex) {// Log exception details here throw; // Propagate exception}}} [Transaction (TransactionOption.Required)] public class Debit: ServicedComponent {public void DebitAccount (string account, decimal amount) {SqlConnection conn = New SqlConnection ("Server = (local); Integrated Security = SSPI"; Database = "SimpleBank"); SQLCommand CMD = New SQLCommand ("Debit", Conn); cmd.commandType =