SQL-CLR For Beginners: Part-4: Consume C# Class in Stored Procedure

Welcome to the SQL-CLR For Beginners article series. In our previous three articles we have seen how to execute basic Stored Procedures, Triggers and Functions in SQL-CLR Applications. If you are not familiar with them then please visit the following links.

• SQL CLR For Beginner: Part 1: Create Stored Procedure in SQL-CLR Project
• SQL CLR For Beginner: Part 2: Create Trigger in SQL-CLR Project
• SQL CLR For Beginner :Part-3: Create function in SQL-CLR Project

In this article we will learn how to use a traditional C# class within a SQL-CLR Application. To do that, at first we need to create one SQL-CLR application. Use the following screens and see how to do it.

Select "File" -> "New" -> "Project...".



In the templates on the left select "Installed Templates" -> "Database" -> "SQL Server". From the project types in the middle select the "Visual C# SQL CLR Database Project" template, provide a nice name and provide a path to save it to.



Once you click "OK", it will ask you to select a database server. Choose the SQL Server of where the application is to be deployed. It might be a remote server or your local server. Once you have successfully established a connection, it will show you the following connection success message.



Once you press "OK", it will create an empty template for the SQL-CLR project. Go to the Test Script folder. Right-click then select "Add" -> "Class".



Choose "Class" and give it a nice name. In my case it's "ClsPerson".



Once you press "OK" it will create an empty class for you. And a point to be noted is that it's nothing but our very familiar C# class. In this example we will create a simple person class called "ClsPerson". It has three properties.

The following is the structure of the class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace StoreProcTest.Test_Scripts
{
    public class ClsPerson
    {
        public String Name { get; set; }
        public String Surname { get; set; }
        public Int32 Age { get; set; }
    }
}

We will now add a Stored Procedure to this project. Right-click on "Test Scripts" then select "Add" -> "Stored Procedure".



Select the Stored Procedure and provide a name. Here we used "Classconsumer".

This is the default body of the Stored Procedure. Yes, it is nothing but one more C# class. Here we are not doing any useful operation within the Stored Procedure. Just for example we are creating an object of the person class, then after assigning a few properties, we are supplying them to the Stored Procedure to display a pipe. Basically this pipe is for sending data from C# CLR code to the SQL Server object.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using StoreProcTest.Test_Scripts;

 

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void ClassConsumer()

    {

        // Put your code here

        ClsPerson objPerson = new ClsPerson();

        objPerson.Name = "Sourav";

        objPerson.Surname = "Kayal";

        objPerson.Age = 24;

       

        //We will Send String as paramiter

        SqlContext.Pipe.Send("Name:-" + objPerson.Name + " Surname:-" + objPerson.Surname + " Age:-" + objPerson.Age);

   }

};

Now we need to do one useful task, we will change the target .NET Framework version from 4.0 to 3.5.

 

Then, right-click on Solution Explorer and click on "Deploy". Once it is deployed successfully, you will get the successful message.



Now go to SQL Server Management Studio, select your chosen database. For the first time what you had chosen. Type "EXEC ClassConsumer" and run it.



I hope that the screen you see is very similar to the preceding screen.