THE IN-PROCESS DATA PROVIDER Client Connection SqlServer.exe Plnvoke

THE IN-PROCESS DATA PROVIDER Client Connection SqlServer.exe Plnvoke SqlCommand cmd= SqlContext.GetCommand(); cmd.ExecuteNonQuery(); Managed Code Unmanaged Code Figure 4-3: Interop to Retrieve the SQL Context whole of SQL Server in managed code. And yes, interoperability calls happen. The managed classes are making PInvoke calls against the executable of SQL Server, sqlservr.exe, as shown in Figure 4-3, which exposes a couple of dozen methods for the CLR to call into. When you read about interop, you may become concerned about performance. Theoretically, a performance hit is possible, but because SQL Server hosts the CLR (as discussed in Chapter 2) and the SqlServer provider runs in process with SQL Server, the hit is minimal. In the last sentence, notice that we said theoretically: Remember that when you execute CLR code, you will run machine-compiled code, which is not the case when you run T-SQL. Therefore, for some code executing in the CLR, the end result may be a performance improvement, compared with pure T-SQL code. Now, that we have discussed the SqlContextclass, let s see how we go about using it. Connections As already mentioned, when you are at server side and a client executes, you are part of that client s connection context, which in the SQL Server 2005 release is exposed through the static GetConnection method in the SqlContext class. So rather than creating a new connection (as is necessary with a connection from within extended stored procedures), you call SqlContext.GetConnection, as shown in the following code snippet.

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

CONTEXT THE SQLCONTEXT CLASS In SQL Server 2005 when

CONTEXT THE SQLCONTEXT CLASS In SQL Server 2005 when you use .NET to write procedures, functions, and triggers, you no longer need to explicitly create the connections, as was necessary when writing extended stored procedures. SQL Server 2005 and the CLR do this by introducing a class called SqlContext. So how does this work? Remember that we mentioned contexts in the beginning of this section namely, that we have contexts when connecting, executing, and doing other things in the database? You can see the SqlContextas a helper class first retrieve the particular context the caller runs under and from there the managed representations of SQL Server objects: connections, commands, and so on. The managed representations of the different contexts are exposed as classes, and they are based on the caller s context, which has been abstracted into SqlContext. To get the different classes, you use static factory methods in SqlContext. Table 4-1 lists these methods and the respective classes. Some of the classes are probably familiar to you, whereas others are new. You can instantiate some of the classes in Table 4-1 SqlCommand, for example by using a constructor (New in VB.NET), but normally you would use the methods listed in the table, and we will cover those later in this chapter. You can create the other classes that are part of the SqlServer provider in the same way that you normally would create them if used from ADO.NET. By now you may be wondering: If some of the managed classes are calling into SQL Server, does that mean that the internals of SQL Server are managed as well, and if not, are interoperability calls between managed and native code space happening? The answers are no and yes. No, the internals of SQL Server are not managed Microsoft did not rewrite the Table 4-1: SqlContext Methods Method Component Class GetConnection() Connection SqlConnection GetCommand() Command SqlCommand GetTransaction() Transaction SqlTransaction GetPipe() Pipe SqlPipe GetTriggerContext() TriggerContext SqlTriggerContext

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

THE IN-PROCESS DATA PROVIDER create and open a

THE IN-PROCESS DATA PROVIDER create and open a connection, precisely as in the preceding code listings. However, as seen from the server side, when a procedure or function is executed, it is executed as part of the user s connection. Whether that connection comes from ODBC, ADO.NET, or T-SQL doesn t really matter. You are in a connection, which has specific properties, environment variables, and so on, and you are executing within that connection; you are in the context of the user s connection. A command is executed within the context of the connection, but it also has an execution context, which consists of data related to the command. The same goes for triggers, which are executed within a trigger context. Prior to SQL Server 2005, the closest we came to being able to write code in another language that executed within the process space of SQL Server was to write extended stored procedures. An extended stored procedure is a C or C++ DLL that has been cataloged in SQL Server and therefore can be executed in the same manner as a normal SQL Server stored procedure. The extended stored procedure is executed in process with SQL Server and on the same Win32 thread as the user s connection. Note, however, that if you need to do any kind of database access, even within the database to which the user is connected, from the extended stored procedure you still need to connect to the database explicitly either through DBLib, ODBC, or OLE DB, exactly as you would do from a client, as Figure 4-2 illustrates. Furthermore, once you have created the connection from the procedure, you may want to share a common transaction lock space with the client. Because you now have a separate connection, you need to explicitly ensure that you share the transaction lock space by using the srv_getbindtokencall and the stored procedure sp_bindsession. Extended Stored Procedure Second Thread Select * … Select * … SqlServr.exe Client Second Connection Connection Connection Figure 4-2: Connections from Extended Stored Procedures

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

CONTEXT THE SQLCONTEXT CLASS SqlConnec tion ISqlConnec tion SqlContext

CONTEXT THE SQLCONTEXT CLASS SqlConnec tion ISqlConnec tion SqlContext SqlPipe IDbConnec tion IDbCommand SqlDataReader IDataRecord ISqlReader ISqlRecord IDataReader ISqlCommand SqlTriggerContext IDbTransaction SqlTransaction DbTransaction SqlCommand SqlPParameterCollection ISqlParameterCollection SqlParameter IDataParameter IDataParameterCollection ISqlParameter SqlException SqlError SqlErrorCollection Figure 4-1: The SqlServer Provider Object Model SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = insert into yukontest values ( inproc1 ) ; cmd.ExecuteNonQuery(); Figure 4-1 shows the object model for the SqlServerprovider. Some of the classes and interfaces will be familiar to you if you have coded ADO.NET; others are part of the SPM (and therefore shared between the SqlClientand SqlServerproviders); and yet others are extensions to the SPM and available only in the SqlServerprovider. It is worth noticing that Microsoft has changed the interface hierarchy and, in the COM spirit, added new interfaces with new functionality specific for SQL Server: ISqlXXX and friends, which implement the IDataXXX and IDbXXX interfaces from the GPM. Most of the classes in the diagram are likely to be familiar to you, but there are also a couple of new classes that didn t exist in the previous versions of ADO.NET. Context The SqlContext Class The SqlContextclass is one of the new classes, one of the extensions of the SPM; it is only part of the SqlServer provider, not the SqlClient provider. When you write data access code from the mid-tier, you always

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

THE IN-PROCESS DATA PROVIDER model for data access

THE IN-PROCESS DATA PROVIDER model for data access in .NET is called the General Programming Model for Managed Providers (GPM). As already mentioned, until now the managed providers have lived in the managed code space and have existed outside the database process space. The SQL Server 2005 release needs a provider that runs in the same process space, because SQL Server 2005 hosts the .NET Common Language Runtime (CLR) inside SQL Server, allowing stored procedures, functions, and triggers to be written in any of the CLR-compliant languages. This provider is exposed in the System.Data.SqlServer name- space, although the exact namespace may change before the release of SQL Server 2005. The SqlServer provider contains a common set of classes and interfaces that implement and extend those that the GPM defines, but the SqlServer provider is not the only provider that uses these new classes and interfaces. The SqlClient provider also uses some of the new classes and interfaces (refer to Chapter 12 for changes in the SqlClientprovider). Microsoft adds a new, extended programming model, called the SQL Server Programming Model, to complement the GPM. These are the results. Consistent programming model Regardless of what extensions or enhancements a specific provider implements, you obtain consistency by coding to the GPM. In other words, you have consistent APIs. Consistency between mid-tier and in-process providers You can transparently migrate your code (and also optimize performance) between the tiers because both the SqlClientand the SqlServer providers implement the SPM. You can move code between the mid- tier and in-process providers just by changing the code you use to obtain a connection, as the following code shows. //code to insert data from mid-tier string connStr = GetConnectionStringFromConfigFile(); SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = insert into yukontest values ( midtier1 ) ; cmd.ExecuteNonQuery(); //code to insert data in-proc SqlConnection conn = SqlContext.GetConnection();

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 SQL SERVER PROGRAMMING MODEL connection string

THE SQL SERVER PROGRAMMING MODEL connection string for SQL Server strConn = Provider=SQLOLEDB;Data Source=myServer; + _ Initial Catalog=pubs;User Id=uid;Password=pwd; connection string for Oracle strConn = Provider=MSDAORA;Data Source=ora;User Id=scott; + _ Password=tiger; open the connection Provider is specified in connection string oConn.Open strConn oCmd.Connection = oConn Listing 4-2: Connections in ADO.NET //connection to SQL Server //data provider is not specified in connection string string sqlConn = server=localhost;database=pubs;uid=sa;pwd=6#B5p; ; //create an instance of SqlClient IDbConnection conn = new SqlConnection(sqlConn); conn.Open(); IDbCommand cmd = conn.CreateCommand(); //connection to Oracle using managed OLE DB provider string oraConn = Provider=MSDAORA;Data Source=ora; User Id=scott; Password=tiger; IDbConnection conn = new OleDbConnection(oraConn); IDbCommand oCmd = conn.CreateCommand(); conn.Open(); In .NET, then, how do we achieve generic data access, given that we have different providers? The actual data provider portion is factored into common interfaces IDbConnection, IDbCommand, IDataReader, and so on which are being implemented by the providers and exposed by a series of provider-specific classes: SqlConnection, OleDbConnection, SqlCommand, OleDbReader, and so on. You code against the interfaces rather than the data-source-specific classes, and the only time you need to specify a database-specific class is when you open the connection. Factory classes and common base classes are added in .NET 2.0. This programming

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 features of SQL Server

THE IN-PROCESS DATA PROVIDER features of SQL Server 2005 mentioned in previous chapters. This code is as secure, reliable and scalable as T-SQL itself, as we have seen in previous chapters, so it makes no sense to require it to build a connection to the database as required of extended stored procedures. A connection requires significant resources in SQL Server 2005 itself and, probably more importantly, serializes all commands and results into a bit stream. When T-SQL accesses the database, it just uses the context in which it is running to access the database directly. .NET code running inside the database can use the same interfaces as SqLCient code; this is called the SQL Server Programming Model (SPM). Code inside the database does not depend on making connections to the database; it accesses SQL Server 2005 directly through the context in which it runs in a way very similar to T-SQL itself. This capability is exposed using the ADO.NET data provider model. It is accessed through classes in the namespace System.Data.SqlServerand a few others. With the first release of .NET, two different providers were available, located in the System.Data.OleDband the System.Data.SqlClientnamespaces, respectively. Because the SqlClientprovider is optimized for SQL Server, you can use it to access only SQL Server, whereas you can use the OleDbprovider to access data from any data source with a compliant OLE DB provider. Note that this architecture differs somewhat from the previous provider architectures ODBC, OLE DB, ADO, and so on in that with .NET you have different providers, depending on the data source. This gives provider writers the ability to add data store specific functionality to the provider on the class level without breaking the model. In OLE DB, provider writers had to add functionality through interfaces that in most cases ADO wouldn t see. This led to a trend where most providers implemented the same functionality and the ADO developer merely changed a property (or the connection string) in order to determine the data source to which to connect. The positive implication of this was that, as an ADO developer, you could code very generically, as Listing 4-1 shows. Notice that the equivalent code in ADO.NET, shown in Listing 4-2, is not as generic as in ADO. Listing 4-1: Connections in ADO Connections in ADO – Visual Basic.NET Dim oConn as ADODB.Connection Dim strConn As String Dim oCmd As New ADODB.Command

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