Use automation transactions
Automated transactions simplify programming models 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:
Delicate new categories from the ServicComponent 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. For more details, see section for the result of the transaction processing later.
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 processing separation level
The transaction separation level used for COM 1.0 - i.e., the COM --AM --water running 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 (TransactionOption.supported, Isolation = TransactioniSolationledLevel.Readcommitted)]
Public Class Account: ServicedComponent
{
.
}
More information
For more information on configuring transactional separation levels and other Windows .NET COM enhancements, see MSDN Magazine 2001 "Windows XP: Use Com 1.5 Enhancement Characteristics to make your components stronger".
Determine transaction results
In all transaction processing components of a single transaction stream, automated transaction results are determined by the status of transaction cancellation flags and consistency logos. 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 Transaction flow context
When the root object (in this example is an object), it is determined when the customer's method call returns, and 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 from the .NET object in one of the following methods:
You can use the AutoComplete property to comment on the method and let the .NET automatically stores to 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.
AutoComplete method
For a method marked attribute, do 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 Details
Throw; // Rethrow the Exception, Causeing the consistent
// flag to be set to false.
}
Finally
{
// Close The Database Connection
.
}
}
Non-autocomlete method
For methods for the properties without AutoComplete, must:
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 someharmthod ()
{
Try
{
// Open the connection, and perform Database Operation
.
Contextutil.setComplete (); //manually vote to commit the transport
}
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 If there are multiple Catch blocks, call ContextVtil.Setabort when the method starts, and call ContextUtIl.setComplete at the end of the TRY block will become easy. 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
Paging in distributed applications is a universal requirement. 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.
Option comparison
The options for data paging are:
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 Network Bandwidth Database Server Memory and Power Intermediate Server Memory and Power By Size of Row Data Numbers Returned by Paging Queries
Performance tests show that the manual method of the stored procedure provides optimal performance in a large stress level. 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.
Use 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 Fill
DataSet DataSet,
Int StartRecord,
Int MaxRecords,
String Srctable
);
The 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.
Use ADO
Another option to implement paging is to use COM-based ADO to page. 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.
Provide manual implementation
The last option of the data paging discussed 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.
Paging with a single keyword
If the table contains a unique keyword, you can use the keywords in the WHERE Terms to create the result settings from a special 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 code stored below:
Create Procedure GetProductSpaged
@LastProductId Int,
@PageSize Int
AS
Set rowcount @PageSize
SELECT *
From product
Where [Standard Search Criteria]
And Productid> @LastProductID
Order by [criteria That Leaves Productid Monotonically Increasing]
Go
The calling program of this stored procedure only maintains the value of the LastProductID and increases or decreases the value by the size between the selected continuous calls.
Paging in forms with unique keywords
If you need a paging table without a unique keyword, 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 Int
AS
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 ASC
Go
The 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 use Enterprise (COM ) Services to enable .NET management classes to the object structure, you need to perform the following steps:
Guide the required class from the Servic Component located in the SYSTEM. Enterprise Services namespace. Using system.enterprises;
Public Class DataAccessComponent: ServicesDComponent Adds a construction enabled attribute for this class and specifies the default structure string. The default value 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 DataAccessComponent: ServicesDComponent provides a replacement 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 ConstructionString)
{
// Construct Method Is Called Next After Constructionor.
// the configured dsn is supplished as the single argument
} Provide a strong name for the assembly via the Assembly key file or the Assembly key name property. 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. More information about assembly registration, reference: http://msdn.microsoft.com/library/en-us/cpguide/html/cpconregisteringserviced components.asp. // the applicationname attribute specifies the name of the name
// 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
[assmbly: ApplicationActivation (ActivationOption.Library)]
The following code segment is a service component called DataAccessComponent that uses the COM structure string to get a database connection string.
Using system;
Using system.enterprises;
// the applicationname attribute specifies the name of the name
// 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
[assmbly: ApplicationActivation (ActivationOption.Library)]
// sign the assembly. The snk key file is created Using the THE
// sn.exe utility
[Assembly: askEMBLYKEYFILE ("Dataservices.snk")]]
[Constructionenabled (default = "default dsn")] public class dataAccessComponent: ServicesDComponent
{
PRIVATE STRING CONNECTIONSTRING;
Public DataAccessComponent ()
{
// Constructor is Called On Instance Creation
}
Public override void construct (String ConstructionString)
{
// Construct Method Is Called Next After Constructionor.
// the configured dsn is supplished as the single argument
THIS.CONNECTIONSTRING = ConstructString;
}
}
How to use SqlDataAdapter to retrieve multiple lines
The following code shows how to use the SqlDataAdapter object to issue a command to generate a Data SET or DATATABLE. 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 DataBLE ("Products");
Da.fill (DT);
Return DT;
}
}
Press the following steps to use SQLADAPTER to generate DataSet or DataTable:
Create a SQLCommand object Enable the stored procedure and connect it with 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 lines
The following code illustrates how to retrieve multi-line using the SqlDataReader method:
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
{
Cn.open ();
// generate the reader. ComMMandbehavior.closeConnection Causes the
// the connection to be closed when the reader object is closed
Return (cmd.executeReader (CMMANDBEHAVIOR.CLOSECONNECONNECONNECONNECONNECONNECONNECONNECTION);
}
Catch
{
CONN.CLOSE ();
Throw;
}
}
// Display the Product List sale the console
Private 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 Connection Due To THE
// Commandbehavior Enum Used Whenrating The Reader
}
Press the following steps to use SqlDataReader to search multi-line:
Create a SQLCommand object for executing the stored procedure and link 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 search multiple lines
You can generate an XMLReader object using a SQLCommand object that provides a stream-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
{
Cn.open ();
XmlTextReader XReader = (XMLTextReader) cmd.executexmlreader ();
While (XReader.Read ())
{
IF (XReader.name == "Products")
{
String stroutput = xreader.getattribute ("productID");
Stroutput = ""
Stroutput = XReader.getaTRibute ("ProductName"); console.writeline (stroutPut);
}
}
XReader.close ();
}
Catch
{
Throw;
}
Finally
{
CONN.CLOSE ();
}
}
The above code uses the following stored procedures:
Create Procedure DatretrieveProductsXML
AS
Select * from products
For XML Auto
Go
Retrieve XML data by following these 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 stored procedure output parameters to retrieve single line
A stored procedure can be called, which can return to retrieval data items in a single row in a single row by means of an output parameter. 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 products)
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 ("DatgetProductDetailspoutput", 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
{
Cn.open ();
// Use ExecutenonQuery to run the commit.
// Alth No Rows Are Returned Any Mapped Output Parameters
// (and potentially returnes) Are Populate
cmd.executenonquery ();
// Return Output Parameters from stored Proc
ProductName = paramprodname.value.toString ();
Unitprice = (decimal) paramunitprice.value;
}
Catch
{
Throw;
}
Finally
{
CONN.CLOSE ();
}
}
Use the stored procedure output parameter to retrieve a single line:
Create a SQLCOMMAND object and link it to the SQLConnection object. 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 Output
AS
SELECT @ProductName = ProductName,
@Unitprice = Unitprice
From product
Where productid = @ProductID
Go
How to use SqlDataReader to retrieve a single line
You can search the single row using the SqlDataReader object, and the value from the required column that returns the data stream. This is described by the following code:
Void getProductDetailsusingReader (int products)
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
{
Cn.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 ();
}
}
Follow these steps to return to with the SqlDataReader object:
Establish a SQLCommand object. Open the connection. Call the ExecuteReader object of the SQLDReader object. Retrieve the output parameters using the classification of the SqlDataReader object - here is getString and getDecimal.
The following code segments are enabled by the following stored procedures:
CREATE Procedure DatgetProductDetailsReader
@ProductID Int
AS
Select ProductName, Unitprice from Products
Where productid = @ProductID
Go
How to use ExecuteScalar single item
The ExecuteScalar method is to design access to a single value. 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 the product name of a product ID:
Void getProductNameexecutescalar (int Productid, Out String ProductName)
{
SqlConnection conn = new SQLCONNECTION
"Server = (local); Integrated Security = SSPI; Database = Northwind");
Sqlcommand cmd = new SQLCOMMAND ("LookuppproductNamescalar", Conn;
cmd.commandtype = commandtype.storedProcedure;
Cmd.Parameters.Add ("@ ProductID", ProductId;
Try
{
Cn.open ();
ProductName = (string) cmd.executescalar ();
}
Catch
{
Throw;
}
Finally
{
CONN.CLOSE ();
}
}
Use the following steps 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:
Create Procedure LookuppproductNamescalar
@ProductID Int
AS
SELECT TOP 1 ProductName
From product
Where productid = @ProductIDGO
How to use the stored procedure output or returned parameters to retrieve a single item
Using the parameters output or returned by the stored procedure can query a single value, the following code illustrates the use of output parameters:
Void getProductNameusingsingsPoutPut (int ProductId, Out String ProductName)
{
SqlConnection conn = new SQLCONNECTION
"Server = (local); Integrated Security = SSPI; Database = Northwind");
Sqlcommand cmd = new SQLCommand ("LookuppproductNamesPoutput", 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
{
Cn.open ();
cmd.executenonquery ();
ProductName = parampn.value.tostring ();
}
Catch
{
Throw;
}
Finally
{
CONN.CLOSE ();
}
}
Use the following 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) Output
AS
SELECT @ProductName = ProductName
From product
Where productid = @ProductID
Go
The following code shows how to use the return value to determine if there is a special 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 products)
{
SqlConnection conn = new SQLCONNECTION
"Server = (local); Integrated Security = SSPI; Database = Northwind");
Sqlcommand cmd = new sqlcommand ("CheckProductSP", CONN);
cmd.commandtype = commandtype.storedProcedure;
cmd.parameters.add ("@ ProductidID", ProductID; SQLParameter Paramret =
Cmd.Parameters.Add ("@ productuctexists", sqldbtype.int;
Paramret.direction = parameterDirection.ReturnValue;
Try
{
Cn.open ();
cmd.executenonquery ();
}
Catch
{
Throw;
}
Finally
{
CONN.CLOSE ();
}
Return (int) paramret.value == 1;
}
According to the following steps, you can use the stored procedure return value 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 Int
AS
IF EXISTS (SELECT ProductID)
From product
Where produter = @ProductID)
Return 1
Else
Return 0
Go
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 products)
{
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
{
Cn.open ();
SqldataReader Reader = cmd.executeReader
Commandbehavior.sing Leresult);
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 Int
AS
Select Count (*) from Products
Where productid = @ProductID
Go
How to encode ADO.NET manual affairs
The following codes explain how to use the SQL Server. NET data supplies to protect the bold transfer operation of the transaction. 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);
Sqlcommand cmddebit = new sqlcommand ("debit", conn);
CMDDebit.commandtype = commandtype.storedProcedure;
Cmddebit.Parameters.add (New Sqlparameter ("@ Accountno", fromaccount);
Cmddebit.Parameters.Add (New Sqlparameter);
Cn.open ();
// start a new transaction
Using (SqlTransaction Trans = Conn.BegintransAction ())
{
// Associate the two of the sand of the Same Transaction
Cmdcredit.transaction = trans;
Cmddebit.Transaction = Trans;
Try
{
cmdcredit.executenonquery ();
Cmddebit.executenonquery ();
// Both Commands (Credit and Debit) WERE SUCCESSFUL
TRANS.COMMIT ();
}
Catch (Exception EX)
{
// Transaction Failed
TRANS. ROLLBACK ();
// log exception details.
Throw EX;
}
}
}
}
How to use Transact-SQL to perform transactions
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 Money
AS
Begin 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
End
Declare @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
This stored procedure uses Begin Transaction, Commit Transaction, and Rollback Transaction status manual controlled transactions.
How to encode a transactional .NET class
The following examples are three service NET classes, they 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.enterprises;
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 TransferaXception ("Transfer Failure", SQLEX);
}
}
}
[Transaction (TransactionOption.Required)] Public Class Credit: ServicesDComponent
{
[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 (New Sqlparameter ("@ Accountno", Account);
cmd.Parameters.Add (New Sqlparameter);
Try
{
Cn.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 = commandtype.storedProcedure;
Cmd.Parameters.Add (New Sqlparameter ("@ Accountno", Account);
cmd.Parameters.Add (New Sqlparameter);
Try
{
Cn.open ();
cmd.executenonquery ();
}
Catch (SQLEXCEPTION SQLEX)
{
// log exception details here
Throw; // Propagate Exception Back to Caller
}
}
}
Partner
Thank you very much for the following writers and reviewers:
Bill Vaughn, Mike Pizzo, Doug Rothaus, Kevin White, Blaine Dokter, David Schleifer, Graeme Malcolm, Bernard Chen, Matt Drucke and Steve Kirk.
What kinds of questions, comments and suggestions are readers? Regarding feedback from this article, please send e-mail to devfdbck®Microsoft.com.
