Stored Procedure Using c# CLR Project

Step 1: Create a SQL Server Procedure like:



Step 2: Add the Stored Procedure Class:



Step 3: Create a Data Reader function and query like:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedure()
    {
        try
        {
            // Put your code here
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = "Context Connection=true";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection;
            cmd.CommandText = @" SELECT
                                 [timestamp]
                              ,[No_]
                              ,[Name]
                              ,[Search Name]
                              ,[Name 2]
                              ,[Address]
                              ,[Address 2]
                              ,[City]
                              ,[Contact]
                              ,[Phone No_]
                              ,[Telex No_]
                              ,[Our Account No_]
                              ,[Territory Code]
                              ,[Global Dimension 1 Code]
                              ,[Global Dimension 2 Code]
                              ,[Chain Name]
                              ,[Budgeted Amount]
                              ,[Credit Limit (LCY)]
                              ,[Customer Posting Group]
                              ,[Currency Code]
                              ,[Customer Price Group]
                              ,[Language Code]
                              ,[Statistics Group]
                              ,[Payment Terms Code]
                              ,[Fin_ Charge Terms Code]
                              ,[Salesperson Code]
                              ,[Shipment Method Code]
                              ,[Shipping Agent Code]
                              ,[Place of Export]
                              ,[Invoice Disc_ Code]
                              ,[Customer Disc_ Group]
                              ,[Country_Region Code]
                              ,[Collection Method]
                              ,[Amount]
                              ,[Blocked]
                              ,[Invoice Copies]
                              ,[Last Statement No_]
                              ,[Print Statements]
                              ,[Bill-to Customer No_]
                              ,[Priority]
                              ,[Payment Method Code]
                              ,[Last Date Modified]
                              ,[Application Method]
                              ,[Prices Including VAT]
                              ,[Location Code]
                              ,[Fax No_]
                              ,[Telex Answer Back]
                              ,[VAT Registration No_]
                              ,[Combine Shipments]
                              ,[Gen_ Bus_ Posting Group]
                              ,[Picture]
                              ,[Post Code]
                              ,[County]
                              ,[E-Mail]
                              ,[Home Page]
                              ,[Reminder Terms Code]
                              ,[No_ Series]
                              ,[Tax Area Code]
                              ,[Tax Liable]
                              ,[VAT Bus_ Posting Group]
                              ,[Reserve]
                              ,[Block Payment Tolerance]
                              ,[IC Partner Code]
                              ,[Prepayment %]
                              ,[Primary Contact No_]
                              ,[Responsibility Center]
                              ,[Shipping Advice]
                              ,[Shipping Time]
                              ,[Shipping Agent Service Code]
                              ,[Service Zone Code]
                              ,[Allow Line Disc_]
                              ,[Base Calendar Code]
                              ,[Copy Sell-to Addr_ to Qte From]
                              ,[UPS Zone]
                              ,[Tax Exemption No_]
                              ,[Bank Communication]
                              ,[Check Date Format]
                              ,[Check Date Separator]
                              ,[Tax Identification Type]
                              ,[Components on Sales Orders]
                              ,[Components on Shipments]
                              ,[Components on Invoices]
                          FROM [dbo].[ Customer] ";

            connection.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            SqlContext.Pipe.Send(dr);
            dr.Close();
            connection.Close();
        }
        catch (Exception)
        {

        }

    }
};

Now build the application.

Step 4: Now create / register the Assembly in your SQL Server using this query:

CREATE ASSEMBLY SQLCLRTest
FROM 'D:\Jayendra-pc\JayendrasinhGohil-Demo\SqlServerProjectCLR\SqlServerProjectCLR\bin\Debug\SqlClassLibrary.dll';

Step 5: Now enable the .NET framework for SQL Server and configure the CLR assembly using:

sp_configure system store procedure like

EXEC sp_configure 'show advanced options' , '1'; reconfigure;
EXEC sp_configure 'clr enabled' , '1' ;reconfigure;
EXEC sp_configure 'show advanced options' , '0';
reconfigure;

Step 6: Now create the Stored Procedure like:

CREATE PROCEDURE USP_Customer
AS
EXTERNAL NAME SQLCLRTest.StoredProcedures.StoredProcedure
/*(Your class name. Method Name)*/