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

Sourav Kayal
Jan 31, 2023

28.2k
0
0
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

Introduction

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 unfamiliar with them, please visit the following links.

This article will teach us how to use a traditional C# class within a SQL-CLR Application. To do that, 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, and provide a nice name and path to save it to.

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

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

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

In this example, we will create a simple Person class called "ClsPerson." Once you press "OK," it will create an empty class for you. And a note is that it's nothing but our very familiar C# class. 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 helpful operation within the Stored Procedure. For example, we create an object of the person class; then, after assigning a few properties, we supply them to the Stored Procedure to display a pipe. This pipe is for sending data from the 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);

   }
};

We need to do one helpful 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, and select your chosen database. Type "EXEC ClassConsumer" and run it. For the first time, what you had chosen.