Sharing Knowledge is always advisable
We all are familiar how to declare function in C# and SQL
and call it. Today we are going to learn how to call C# function from SQL.
 |
| Figure 1 Select Project |
Open Visual Studio and select Add New Project à
and window will popup as Figure1 and from them select the Template Database and under this template select
SQLServer. Select the Project shown in figure for starting this feature.
After you have selected the template when clicking on OK the
window will popup asking for to add database reference as shown in figure below
Figure 2 Database Connection
You can select the connection from existing list or can also
create the new by clicking on Add New
References.
After selecting the database reference when you click OK the
window will popup asking you to allow debugging or not.
Figure 3 SQL/CLR Debugging
It is upto you as what you want, I select no and proceed
further. The project gets loaded as shown in figure.
Right click the project and
select add new and select Stored
Procedure from the window.
Figure 4 Stored Procedure
If you want you select any template from the required one
here I am selecting stored procedure. After selecting the stored procedure the
visual studio will prepare the class for you, I have created one method which
will print the current time to sql message screen when executed as shown in the
figure.
After this you need to build the project and deploy your
code to SQL server.
Figure 5 Build & Deploy
Deploy will directly registered the .dll of your project to
the SQL server so that you can execute your stored procedure. If your
deployment failed then you can use the alternate method to register the .dll
into the Database. Connect the database and open query window and write the
following command
Create ASSEMBLY CLRTest
FROM ‘yourprojectdllpath’
Or you can also register the .dll by right clicking the
assembly icon in your database and select New Assembly as shown in the figure.
Figure 6 Register Assembly
After registering the .dll we need to enable the clr to able
execute the C# code for this write the following command
EXEC sp_configure
'clr enabled', 1; RECONFIGURE WITH
OVERRIDE;
To execute our stored procedure create stored procedure and
write following query as shown below.
create procedure sp_CLRTest3
as
external name
[CLRTest].[StoredProcedures].sp_CLRTest1
Go
# Defnition Format
– EXTERNAL NAME [AssemblyName].[ClassName].[MemberName]
After doing this you can easily see the procedure listed in
the procedure tab as shown in the figure
Now simply execute your procedure by writing
exec sp_CLRTest1
when you excute this the message window will show the result
Current Time : 2017-09-08 9:18:21 AM
