Writing Managed Stored Procedures in SQL Server 2005

Writing Managed Stored Procedures in SQL Server 2005

ith the functionality found in the .NET Framework Base Class Library (BCL), database developers now have access to thousands of pre-built classes and routines which can be easily accessed from stored procedures. The BCL includes classes that provide functionality for improved string functioning, advanced math operations, file access, cryptography, and more. .NET languages such as VB .NET and C# offer object-oriented capabilities such as encapsulation, inheritance and polymorphism.

SQL Server 2005’s (formerly code named Yukon) integration with the .NET CLR makes it possible for developers to author stored procedures, triggers, user defined functions, and create other database objects using a managed language such as VB.NET or C#. This excellent feature provides a number of benefits, including increased productivity, significant performance gains, and the ability to leverage the features of .NET Code Access Security to prevent assemblies from performing certain operations. This article takes a look at this new CLR integration feature so you can understand how to create stored procedures in SQL Server using a managed language. You’ll also see how to leverage.NET code access security to better control the assembly execution environment. Despite the benefits, it isn’t appropriate to use .NET code for every stored procedure you write, so you’ll see how to decide when to use T-SQL and when to use a .NET language for creating SQL Server stored procedures.

.NET CLR and SQL Server Integration
In previous versions of SQL Server, database programmers were limited to using Transact-SQL when creating server side objects such as stored procedures, triggers, user defined functions and so on. But the integration of SQL Server with .NET CLR?opens up an avenue of wholly new opportunities. Before discussing the features of the .NET CLR integration with SQL Server, its important understand the limitations of T-SQL for creating server side objects.

Transact-SQL (T-SQL) is an extension of the Structured Query Language as defined by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). Using T-SQL, database developers can create, modify and delete databases and tables, as well as insert, retrieve, modify and delete data stored in a database. T-SQL is specifically designed for direct data access and manipulation. While T-SQL can be very useful for data access and management, it is not a full-fledged programming language in the way that Visual Basic .NET and C# are. For example, T-SQL does not support arrays, strongly typed objects, collections, for each loops, bit shifting, classes and so on. While it’s possible to simulate some of these constructs in T-SQL, managed code based languages such as VB.NET or C# have first-class support for them.

With CLR integration, things have changed dramatically. The CLR provides the execution environment for all the server side objects created using a .NET language. This means that database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. The new integration allows you to more easily organize and maintain your code investments, especially when working with large amounts of server code. Unlike traditional stored procedures, this approach lets developers create code utilizing object-oriented concepts such as encapsulation. By allowing the code to run under the control of .NET CLR, you can also leverage the code access security features of .NET. Before executing code, the CLR can check to see if the code is safe. This process is known as “verification.” During verification, the CLR performs several checks to ensure that the code is safe to run. For example, it checks the code to ensure that it doesn’t try to read from memory to which it has not written. The CLR will also prevent buffer overflows.

Creating a Managed Stored Procedure
To create a stored procedure using a managed language such as C# or VB.NET, follow these steps.

  • Create a .NET class and implement the functionality of the stored procedure within that class
  • Compile that class to produce a .NET assembly
  • Register that assembly in SQL Server using the Create Assembly statement
  • Create stored procedure definitions. As part of this, you also associate the stored procedure with the actual methods in the assembly.

After completing these steps, the stored procedures are configured and you can execute them just like any other stored procedure.

Implementing a .NET Class-Based Stored Procedure
Start by creating the VB.NET class that implements the stored procedure functionality. For this example, create a VB.NET class named AddressType that contains the following code (the downloadable code provides both VB.NET and C# examples).

   Imports  System.Data   Imports System.Data.Sql   Imports  System.Data.SqlServer      Public Class AddressType         Public Shared Sub _ GetAddressTypeDetailsByID _         (ByVal  _addressTypeID as Integer)         Dim sp as SqlPipe = _            SqlContext.GetPipe()         sp.Send("Address Type ID is " + _            addressTypeID.ToString() + _            "
")         Dim cmd as SqlCommand = _            SqlContext.GetCommand()         cmd.CommandText = "SELECT * " & _            "FROM Person.AddressType " & _            "Where AddressTypeID=" & _            addressTypeID.ToString()         Dim rdr as SqlDataReader = _            cmd.ExecuteReader()         sp.Send(rdr)      End Sub      End Class   

To execute .NET code in SQL server, you need to reference the System.Data.Sql and System.Data.SqlServer namespaces. The AddressType class contains one method, GetAddressTypeDetails, which uses the ID passed as a parameter to retrieve a row from the AddressType table in the AdventureWorks database that ships with SQL Server 2005. The code gets a reference to the SqlPipe object by invoking the GetPipe method of the SqlContext class. You then use that SqlPipe reference to return tabular results and messages to the client using the SqlPipe.Send method. By calling the various overloads of the Send method, you can transmit data through the pipe to the calling application. Some of the overloaded versions of the Send method are:

  • Send(ISqlDataReader)? Allows us to send the tabular results in the form of a SqlDataReader object
  • Send(ISqlDataRecord)?Allows us to send the results in the form of a SqlDataRecord object
  • Send(ISqlError)?Makes it possible for us to send error information in the form of a SqlError object.
  • Send(msg As String)?Using this method, we can send messages to the calling application.

Compile the Class Into a .NET Assembly
Compile the completed class to produce a .NET assembly, which you can then register with SQL Server. Figure 1 shows how to compile the VB.NET class with the VB command line compiler to produce the .NET assembly.

?
Figure 1. Compiling the VB.NET Class: The figure shows a sample command line to compile the VB.NET AddressType class using the VB command line compiler

As shown in Figure 1, you need to reference the sqlaccess.dll because the code uses the classes contained in the System.Data.SqlServer namespaces. The code download for this article also contains the C# version of the complete code.

Register the Assembly with SQL Server
The deployment unit for managed code is called an assembly. An assembly is packaged as a DLL or executable (EXE) file. While an executable can run on its own, a DLL must be hosted in an existing application. SQL Server can load and host managed DLL assemblies. To load an assembly into SQL Server, you need to use the Create Assembly statement.

   CREATE ASSEMBLY AddressType   FROM 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnTestCLRProceduresCSharpAddressType.dll'

When you execute the above statement in the SQL Server Workbench, it will load the assembly into SQL Server. The FROM clause specifies the pathname of the assembly to load. This path can either be a UNC path or a physical file path that is local to the machine. The above statement will register the assembly with the SQL Server (the assembly name should be unique within a database). Running the Create Assembly statement causes SQL Server to load a copy of the assembly. Subsequently, if you want to change the assembly code, you need to first drop the assembly, then change the code, recompile the assembly, and finally, register the assembly with SQL Server again. To drop an assembly from SQL Server, you use the Drop Assembly statement. For example, to drop the assembly created earlier, use the following command.

   DROP ASSEMBLY AddressType

Creating Stored Procedure Definitions
After loading the assembly into SQL Server, you need to associate a stored procedure to a specific method of the class contained in the assembly. You create a stored procedure using the Create Procedure statement. SQL Server 2005 supports a new clause named External Name that lets you reference a method in a registered assembly (an external method). Referencing an external method hooks the stored procedure to that method in the assembly.

   CREATE PROCEDURE [dbo].[GetAddressTypeDetailsByID]      @addressTypeID int    AS   EXTERNAL NAME       [AddressType]:[AddressType]::      [GetAddressTypeDetailsByID]

The preceding code uses the GetAddressTypeDetailsByID method shown earlier in the AddressType class. The External Name clause uses the following syntax.

   [Assembly Name]:[Class Name]::[Method Name]

After creating the stored procedure, you can test it using the following statement, passing an ID of 1 as the @addressTypeID parameter.

   exec GetAddressTypeDetailsByID 1
?
Figure 2. Results of the Stored Procedure: The figure shows representative results from executing the GetAddressTypeDetailsByID stored procedure using SQL Server Workbench

Invoking Managed Stored Procedures from Windows Forms Clients
Here’s a simple Windows Forms application to test the stored procedure created in the previous sections. Create a new project, add a command button and a data grid to the form. Name the command button btnGetByID, and name the data grid grdAddressType. Then modify the Click event of the command button to contain the following code.

   Private Sub btnGetByID_Click( _      ByVal _ sender As System.Object, _      ByVal e As System.EventArgs) _      Handles _ btnGetByID.Click      Dim connString as String = _          "server=localhost;uid=sa;" & _         "pwd=thiru;database=AdventureWorks;"      Dim conn as New SqlConnection _(connString)      Dim procName as String  = _ _         "GetAddressTypeDetailsByID"      Dim adapter as New _ SqlDataAdapter(procName,conn)      adapter.SelectCommand.CommandType = _  __         CommandType.StoredProcedure      Dim paramAddressTypeID as New  _SqlParameter _         ("@addressTypeID", SqlDbType.Int)      paramAddressTypeID.Direction = _          ParameterDirection.Input      paramAddressTypeID.Value = 1      adapter.SelectCommand.Parameters. _Add( _         paramAddressTypeID)      Dim addressTypeDataSet as New  _DataSet()      adapter.Fill(addressTypeDataSet)      grdAddressType.DataSource  _= _         addressTypeDataSet.Tables(0).DefaultView   End Sub
?
Figure 3. Output of the Client Application: Executing the application results in the details of the AddressType being displayed in a DataGrid control

As you can see, the preceding code is straightforward?and identical to the way you invoke any other SQL Server stored procedure. The code declares a variable named connString and assigns the database connection string to it. Then it creates a new SqlConnection object, using the connection string as an argument to its constructor. Next it creates a SqlDataAdapter object and supplies the stored procedure name and the previously created SqlConnection object as its arguments. Because the stored procedure takes an addressTypeID as an argument, the code creates a SqlParameter object and then assigns appropriate values to its properties. Finally, it executes the stored procedure by invoking the Fill method of the SqlDataAdapter object. After getting the results in the form of a DataSet object, you then bind the results of the DataSet to the DataGrid control. If you run the application and click on the command button, you will get the output shown in Figure 3.

Invoking Nested Stored Procedures
You aren’t limited to running managed stored procedures one by one?you can chain or nest them as well. In this section you’ll see how to invoke a CLR-based stored procedure from within another CLR-based stored procedure. For the purposes of this example, you’ll create two stored procedures that, together, let you retrieve address type details based on the name of the address type. The capability is split into two stored procedures. The first procedure, GetAddressTypeIDByName, accepts the name of an address type and returns the corresponding address type ID. The second procedure, GetAddressTypeDetailsByName, accepts the name of an address type and returns the details of that address type. It does this in two steps. In the first step, it invokes the GetAddressTypeIDByName procedure to convert the name of the address type into an address type ID. It then uses ID to retrieve the corresponding address type record.

To start, create a new class named AddressTypeID and add a method named GetAddressTypeIDByName as shown in the following code.

   Imports System.Data.Sql   Imports System.Data.SqlServer      Public class AddressTypeID      Public Shared Function GetAddressTypeIDByName( _         ByVal name _As String) As Integer         Dim sp as SqlPipe =  _SqlContext.GetPipe()         Dim cmd as SqlCommand =  _SqlContext.GetCommand()         cmd.CommandText = "SELECT " & _" AddressTypeID " & _            "FROM " &_" Person.AddressType WHERE " & _             "Name='" + name + "'"         Dim addressTypeID As Integer = _CType( _            cmd.ExecuteScalar(),Integer)         Return addressTypeID      End Function   End Class

You can see that the code?is very similar to the previous stored procedure. It simply takes in the name of an address type and returns the corresponding address type identifier. Next, add a GetAddressDetailsByName method to the existing AddressType class as shown below.

   Public Shared Sub GetAddressTypeDetailsByName _      (ByVal name as String)      Dim sp as SqlPipe = SqlContext.GetPipe()      Dim cmd as SqlCommand = SqlContext.GetCommand()      cmd.CommandType = CommandType.StoredProcedure      cmd.CommandText = "GetAddressTypeIDByName"      Dim paramName as New SqlParameter("@name", _        SqlDbType.NVarChar,50)      paramName.Direction = ParameterDirection.Input      paramName.Value = name      cmd.Parameters.Add(paramName)      Dim paramID as New SqlParameter( _         "@ID",SqlDbType.Int)      paramID.Direction = ParameterDirection.ReturnValue      cmd.Parameters.Add(paramID)      cmd.ExecuteNonQuery()      ' Get the returned value from the stored procedure      Dim id as Integer  = CType( _         cmd.Parameters(1).Value, _Integer)      ' Use this id as an input value for the       ' execution of       '   next stored procedure      cmd.CommandType = CommandType.Text      cmd.CommandText = "SELECT * FROM " & _         "Person.AddressType " & _      "Where AddressTypeID=" + id.ToString()      Dim rdr as SqlDataReader = cmd.ExecuteReader()      sp.Send(rdr)   End Sub

The preceding code consists of two parts. The first part?invokes the GetAddressTypeIDByName stored procedure to convert the address type name into an address type ID. The second part retrieves the details of the address type using that ID. Finally the method returns the results back to the calling application using the Send method. Download the sample code to get this test client application and other code shown in this article.

Enforcing Permissions for Executing Assemblies
A managed assembly’s security is scoped to the user identity of the account that loaded it to SQL Server. Data and code (assemblies) owned by a particular user or role are isolated from data and code owned by another user or user role unless you explicitly grant access.

The owner of an assembly can, in turn, grant the permission to reference that assembly to other database users or roles. An assembly can succeed in invoking the methods of another assembly only if one of the following conditions is true.

  • The referenced assembly is owned by the same user
  • The user owning the referenced assembly has granted the permission to the user owning the referencing assembly

When loading an assembly into SQL Server, you have the ability to specify one of three different levels of security in which your code can run:

  • SAFE?This is the default permission set. With this mode, the assembly can only do computation and data access within the server via the in-process managed provider.
  • EXTERNAL_ACCESS?This permission set is typically useful in scenarios where the code needs to access resources outside the server such as files, network, registry and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.
  • UNSAFE?SQL Server uses this permission set in situations where an assembly is not verifiably safe or requires additional access to restricted resources, such as the Win32 API.

To specify a given security level, you modify the syntax of the Create Assembly statement, adding the WITH PERMISSION_SET clause as shown below.

   CREATE ASSEMBLY    FROM    WITH PERMISSION_SET = 

Transact-SQL vs. Managed Code
The ability to write server side objects using a .NET compliant language gives developers a new way to write stored procedures, but that flexibility also poses some important challenges. Developers now must decide when to use T-SQL and when to use a managed language. There's no straight answer to this question; the appropriate method depends on the particular situation, but here are some guidelines.

T-SQL is best used in situations where the code primarily performs data access with little or no procedural logic. Managed code is best suited for CPU-intensive computations and server side objects where you need to create complex logic. You might want to consider using managed code when you want to leverage the rich features and object model supported by the .NET Framework's Base Class Library (BCL). The location in which the code executes is also an important factor to consider. By using this ability to write stored procedures using a .NET language, you can move some of your middle tier code to stored procedures and other server side objects. Doing that lets you take full advantage of the processing power of the database server. On the other hand, you may wish to avoid placing processor-intensive tasks on your database server. Most client machines today are very powerful, and you may wish to take advantage of this processing power by placing as much code as possible on the client.

devx-admin

devx-admin

Share the Post:
Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India,

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1