THE IN-PROCESS DATA PROVIDER cmd.CommandText = select *

THE IN-PROCESS DATA PROVIDER cmd.CommandText = select * from authors where state = @state ; cmd.Parameters.Add( @state , SqlDbType.VarChar); cmd.Parameters[0].Value = state; SqlDataReader rdr = cmd.ExecuteReader(); SqlPipe pipe = SqlContext.GetPipe(); pipe.Send(rdr); } In addition to returning an entire set of results through the pipe, SqlPipe s Send method lets you send an instance of any class that implements IDataRecord. You can also batch the send operations however you d like. An interesting feature of using SqlPipe is that the result is streamed to the caller immediately as fast as you are able to send it. This may improve performance at the client because you can process rows as fast as they are sent out the pipe. Note that you can combine executing a command and sending the results back through SqlPipein a single operation with the Executeconvenience methods, using either a SqlCommandor SqlExecutionContextas a method input parameter. SqlPipe also contains methods for sending scalar values as messages and sending errors. We ll talk about error handling practices next. The entire set of methods exposed by SqlPipeis shown in Table 4-5. Table 4-5: Methods of the SqlPipe Class Method What It Does Execute(SqlCommand) Execute command, return results through SqlPipe. Execute(SqlExecutionContext) Execute command through SqlExecutionContext, return results through SqlPipe. Send(string) Send a message as a string. Send(ISqlReader) Send results through ISqlReader. Send(ISqlRecord) Send results through ISqlRecord. SendResultsStart(ISqlRecord, bool) Start sending results. SendResultsRow(ISqlRecord) Send a single row after calling SendResultsStart. SendResultsEnd() Indicate finished sending rows.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

PIPE SqlServerprovider. A named savepoint provides the ability

PIPE SqlServerprovider. A named savepoint provides the ability for a partial rollback scenario, but as of this writing they don t work, because you can t start another transaction through the API if there is already one in the context. // this should work whether there is a context tx or not SqlTransaction tx = SqlContext.GetConnection().BeginTransaction( named ); SqlCommand cmd = SqlContext.GetCommand(); cmd.Transaction = tx; cmd.CommandText = insert jobs values( row1 , 10, 10) ; cmd.ExecuteNonQuery(); cmd.CommandText = insert jobs values( row2 , 10, 10) ; cmd.ExecuteNonQuery(); tx.Commit(); Distributed transactions that is, transactions that encompass more than one instance of SQL Server are supported implicitly by using the BEGIN TRAN SQL command as Command.Textand then accessing data in another SQL Server using four-part names. You can also start them explicitly by setting the Command.Text to BEGIN DISTRIBUTED TRANSACTION . The SqlServerprovider does not support the method on the SqlConnection to enlist in an existing distributed transaction manually as SqlClientdoes. Pipe In the section on results, we mentioned that you had a choice of processing results in your procedural code as part of its logic or returning the results to the caller. Consuming SqlDataReaders or the stream of XML in procedural code makes them unavailable to the caller; you cannot process a cursorless mode result more than once. The code for in-process consumption of a SqlDataReader is identical to SqlClient; you call Read() until no more rows remain. To pass a SqlDataReader (or any result) back to the client, you need to use a special class, SqlPipe. The SqlPipe class represents a TDS (tabular data stream) output stream. You obtain a SqlPipe by using SqlContext.GetPipe(). Results, errors, and messages can be written to the pipe. A simple example of a stored procedure that returns a SqlDataReaderto the client follows. public static void getAuthorsByState(SqlString state) { SqlCommand cmd = SqlContext.GetCommand();

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

THE IN-PROCESS DATA PROVIDER Here s an example that

THE IN-PROCESS DATA PROVIDER Here s an example that illustrates these practices. public static void InsertSomeJobs() { // if we have no context transaction // we can start one // if we have one, we can use it and compose with it bool got_context_tx = false; bool error_occurred = false; SqlTransaction tx = SqlContext.GetTransaction(); if (tx == null) tx = SqlContext.GetConnection().BeginTransaction(); else got_context_tx = true; SqlCommand cmd = SqlContext.GetCommand(); try { cmd.Transaction = tx; cmd.CommandText = insert jobs values( row1 , 10, 10) ; cmd.ExecuteNonQuery(); cmd.CommandText = insert jobs values( row2 , 10, 10) ; cmd.ExecuteNonQuery(); } catch (SqlException ex) { error_occurred = true; if (!got_context_tx) tx.Rollback(); // or rethrow the exception throw ex; } // else commit the transaction // only if we started it if (!error_occurred && !got_context_tx) tx.Commit(); } Transaction Exotica In the following example, we ve stuck strictly to local transactions at the default transaction isolation level. The SqlServer provider also supports other transaction isolation levels through the API. This is accomplished through an override of SqlConnection.BeginTransaction, just as with the SqlClient provider. It s also possible to use named savepoints in the

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

TRANSACTIONS // this one must be enlisted SqlCommand

TRANSACTIONS // this one must be enlisted SqlCommand cmd1 = new SqlCommand(); cmd1.Connection = SqlContext.GetConnection(); cmd1.Transaction = tx; // this one must be enlisted too SqlConnection conn = SqlContext.GetConnection(); SqlCommand cmd2 = conn.CreateCommand(); cmd2.Transaction = tx; } Starting a Transaction with T-SQL Command.Text The last way to start a transaction is to use T-SQL s BEGIN TRANSACTIONas the command s Text property and use Command.ExecuteNonQuery to begin the transaction. This was shown at the beginning of the Transactions section. Interestingly, transactions started in this way behave differently from transactions started through the API. You can start a T-SQL transaction whether or not there is already a transaction in context, as in ordinary T-SQL; this just increases the @@trancountvariable by one. You must commit or roll back the transaction through T-SQL using Command.Execute NonQuerywith the appropriate Command.Textbefore exiting the procedure. In addition, calling ROLLBACK using Command.ExecuteNonQueryresults in a SQL error on exit, just as in nested transactional T-SQL procedures. Best Practice for Using Transactions in SQLCLR Stored Procedures With so many choices and different transactional combinations, which one is best? We d like to write a SQLCLR stored procedure with a few SQL statements that runs correctly standalone, but also works when the caller has already started a transaction. For ease of use, here are a few hints. Check for a context transaction first. If there is already a context transaction, use it. If one of the commands fails, return an error to the caller, rather than trying to call SqlTransaction.Rollback. If the context transaction does not exist, start one. Remember to call either SqlTransaction.Commitor SqlTransaction.Rollback before exiting the procedure. Use only the context command, unless you have a good reason to do otherwise. SqlContext.GetCommandalways returns a new instance of a SqlCommandeach time, enlisted in the current connection and transaction (if there is one).

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

THE IN-PROCESS DATA PROVIDER SqlTransaction instance. Your context

THE IN-PROCESS DATA PROVIDER SqlTransaction instance. Your context SqlCommand is already enlisted in this transaction that is, SqlContext.GetCommand().Transaction will return the context transaction. SQL statements issued through this command become part of the transaction. An example .NET stored procedure body that uses a T-SQL statement follows. The T-SQL insert statement will be part of the transaction. SqlTransaction tx = SqlContext.GetTransaction(); if (tx != null) { // this is already part of the transaction SqlCommand ctxcmd = SqlContext.GetCommand(); ctxcmd.CommandText = insert into t1 values( sometext ) ; ctxcmd.ExecuteNonQuery(); } Note that you cannot roll back the context transaction; this produces an error. You cannot commit the context transaction either; in this case, an error is produced when your stored procedure returns to the caller. This is shown in the following example and is the same error produced when you try to do this inside a called stored procedure using T-SQL code. SqlTransaction tx = SqlContext.GetTransaction(); bool error_occured = false; // … do some SQL operations here // either of these fail if (error_occurred) tx.Rollback(); else tx.Commit(); Only the context command is implicitly enlisted in the transaction. If you instantiate a new SqlCommand either directly or with SqlContext. GetConnection().CreateCommand(), you must set its Transaction property to the context transaction yourself. Failure to set the transaction if one exists will cause an error when you call one of the command s set of Executemethods. SqlTransaction tx = SqlContext.GetTransaction(); if (tx != null) { // this is already part of the transaction SqlCommand ctxcmd = SqlContext().GetCommand(); ctxcmd.CommandText = insert into t1 values( sometext ) ; ctxcmd.ExecuteNonQuery();

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

TRANSACTIONS // now commit the transaction // this

TRANSACTIONS // now commit the transaction // this transaction must be committed // or rolled back before you leave the procedure tx.Commit(); The following example shows that attempting to use multiple transactions within a context will cause an error. SqlTransaction tx = null; // this will fail if there s already a transaction if (SqlContext.GetTransaction() == null) tx = SqlContext.GetConnection().BeginTransaction(); // hook up the context command SqlCommand ctxcmd = SqlContext().GetCommand(); ctxcmd.Transaction = tx; ctxcmd.CommandText = insert into t1 values( sometext ) ; ctxcmd.ExecuteNonQuery(); // this fails ctxconn = SqlContext.GetConnection(); SqlTransaction tx2 = ctxconn.BeginTransaction(); // start a new transaction with this command SqlCommand cmd = new SqlCommand(); cmd.Connection = SqlContext.GetConnection(); cmd.CommandText = BEGIN TRANSACTION ; // this fails too // can t have more than one transaction cmd.ExecuteNonQuery(); When you create an API transaction via BeginTransaction, you may not call BeginTransaction again in the same connection until either Transaction.Commit or Transaction.Rollback is called. Although SQL Server supports nesting transactions with multiple T-SQL BEGINTRAN calls, the SqlServer provider does not. If you use the BeginTransaction method in your .NET procedural code, you must call either Commit or Rollback before you exit the procedure. Failing to do so will result in an error. Using a Transaction in the Context If your .NET procedural code is called from code (either T-SQL or .NET code) that has already begun a transaction, there will be a transaction in the context. That is, SqlContext.GetTransaction will return an active

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

THE IN-PROCESS DATA PROVIDER It s also possible that

THE IN-PROCESS DATA PROVIDER It s also possible that a transaction existed for the current session before the .NET procedural code was invoked. You can get a handle to this transaction by using the SqlContext.GetTransaction method. The semantics of each type of transaction handling and the interaction between the transaction and the command (exposed as the SqlCommand s Transaction property) are subtly different for each case. When There Is No Transaction in the Context This is the case when the .NET procedural code has been called in standalone mode (also called autocommit mode, because although each SQL statement is transactional, the database automatically commits after each statement. You can deduce this because SqlContext.GetTransaction() returns a null SqlTransactioninstance. When you have no transaction in context, you can state one by using the SqlConnection s BeginTransaction method, as shown in the following example. You can use this transaction with multiple SQL statements through a SqlCommand instance, or you can compose multiple SqlCommand instances in the same transaction. Once you have started a transaction, you must enlist each new SqlCommand(that you might create, for example, with SqlConnection.CreateCommand or a new SqlCommand instance) in this transaction. SQL Server 2005 does not support multiple transaction contexts on the same connection or support the behavior in which some SqlCommands within the same connection are transactional and some are autocommit. The following code shows an example of composing multiple SqlCommands within the same API transaction. SqlTransaction tx = SqlContext.GetTransaction(); // this will fail if there s already a transaction if (tx == null) tx = SqlContext.GetConnection().BeginTransaction(); // hook up the context command SqlCommand ctxcmd = SqlContext.GetCommand(); ctxcmd.Transaction = tx; ctxcmd.CommandText = insert into t1 values( sometext ) ; ctxcmd.ExecuteNonQuery(); // hook up another command SqlCommand cmd = new SqlCommand(); cmd.Connection = SqlContext.GetConnection(); cmd.Transaction = tx; cmd.CommandText = insert into t1 values( moretext ) ; cmd.ExecuteNonQuery();

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

TRANSACTIONS // 4. Accessors for SqlTypes SqlString s2

TRANSACTIONS // 4. Accessors for SqlTypes SqlString s2 = GetSqlString(0); } Although you can process results obtained inside .NET procedural code, you can also pass these items back to the client. This is accomplished through the SqlPipe class, which is described later in the chapter. Note that each of the classes returns rows, which must be processed sequentially; these result cannot be updated in place. Transactions Multiple SQL operations within a stored procedure or user-defined function can be executed individually or composed within a single transaction. Composing multistatement procedural code inside a transaction ensures that a set of operations has ACID properties. ACID is an acronym for the following: Atomicity Either all the statements in a transaction will succeed or none of them will. Consistency None of the statements in a transaction are allowed to violate database rules such as constraints or even business rules that reside in a trigger. Isolation Each transaction has its own view of the database state. Durability These behaviors are guaranteed even if the database or host operating system fails for example, because of a power failure. You can use transactions in two general ways within the SqlServer managed provider use T-SQL statements as Command.Text, or start a transaction by using the SqlConnection.BeginTransaction method. An example of each follows. // Example 1: start transaction using T-SQL SqlCommand cmd = SqlContext.GetCommand(); cmd.CommandText = BEGIN TRANSACTION ; cmd.ExecuteNonQuery(); // Example 2: start transaction using the API SqlTransaction tx = null; tx = SqlContext.GetConnection().BeginTransaction();

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

THE IN-PROCESS DATA PROVIDER SqlDataReader rdr = cmd.ExecuteReader();

THE IN-PROCESS DATA PROVIDER SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read() == true) // process rows in SqlDataReader} SqlDataReaderencapsulates multiple rows that can be read in a forward- only manner. You move to the next row in the set by using the SqlDataReader.Read() method, as shown in the preceding example. After you call ExecuteReader, the resultant SqlDataReader is positioned before the first row in the set, and an initial Read positions it at the first row. The Readmethod returns false when there are no more rows in the set. If more than one rowset is available, you move to the next rowset by calling SqlDataReader.NextResult. While you are positioned on a row, the IDataRecordinterface can be used to read data. You can use loosely typed ordinals or names to read the data in single columns. Using ordinals or names is a syntactic shortcut to using IDataRecord.GetValue. This returns the value as a .NET System.Object, which must be cast to the correct type. If you know the data type of the value, you can use more strongly typed column accessors. Both SQL Server providers have two kinds of strongly typed accessors. IDataReader.GetDecimal(0)is an example; this returns the value of the first column of the current row as a .NET System. Decimal data type. It is better to use ISqlRecord s SQL Server specific accessors; these return instances of structures from the System.Data. SqlTypes namespace. These types are isomorphic with SQL Server data types; examples of their use and reasons they are preferable to the .NET data types were covered in Chapter 3. An example of using each type follows. SqlCommand cmd = SqlContext.GetCommand(); cmd.CommandText = select * from authors ; cmd.CommandType = CommandType.Text; cmd.Parameters = null; SqlDataReader rdr = cmd.ExecuteReader() while (rdr.Read() == true) { String s; // 1. Use ordinals or names // must cast from object to desired type s = (string)rdr[0]; s = (string)rdr[ au_id ]; // 2. Use GetValue (must cast) s = (string)GetValue(0); // 3. Strong typed accessors s = GetString(0);

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

OBTAINING RESULTS Table 4-4: How to Obtain Different

OBTAINING RESULTS Table 4-4: How to Obtain Different Result Types Result Desired Mechanism to Obtain It Return code Parameter with ParameterDirectionof ReturnCode Count of rows affected Returned value from SqlCommand.ExecuteNonQuery; or use SqlCommand.ExecuteReaderand SqlDataReader.RecordsAffected Scalar value Use SqlCommand.ExecuteScalar Single row Use SqlCommand.ExecuteRow Cursorless mode results Use SqlCommand.ExecuteReader XML stream Use SqlCommand.ExecuteXmlReader server cursor. An example of when to use each results-returning method follows. SqlCommand cmd = SqlContext.GetCommand(); // 1. this is a user-defined function // returning a single value (authorname) as VARCHAR cmd.CommandText = GetFullAuthorNameById ; // required from procedure or UDF cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue( @id , 111-11-1111 ); SqlString fullname = (SqlString)cmd.ExecuteScalar(); // use fullname // 2. returns one row cmd.CommandText = GetAuthorInfoById ; // required from procedure or UDF cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue( @id , 111-11-1111 ); SqlDataRecord rec = cmd.ExecuteRow(); // use fields in SqlDataRecord // 3. returns multiple rows cmd.CommandText = select * from authors ; cmd.CommandType = CommandType.Text; cmd.Parameters.Clear();

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services