Create an SQL CLR UDF with C#

Question

I have been given the task of being able to push SQL query result sets to Excel files. Currently I have a C# console application in place (below) that takes a query, and dumps the results into a specified xlsx file using the EPPlus library.

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace ExcelExport
{
    class Program
    {
        static void Main(string[] args)
        {

            //Create a connection to the database
            SqlConnection conn = new SqlConnection("user id=username;" +
                                                   "password=password;server=SQL-04;" +
                                                   "Trusted_Connection=yes;" +
                                                   "database=JOHN; " +
                                                   "connection timeout=30");
            conn.Open();
            //Set up the SQL query
            string query = "SELECT TOP 10 FORENAME, SURNAME, POSTCODE FROM JOHN.DBO.TEST ORDER BY POSTCODE ASC";
            SqlCommand cmd = new SqlCommand(query, conn);

            //Fill a C# dataset with query results
            DataTable t1 = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                a.Fill(t1);
            }

            //Assign filename to a variable
            string filePath = @"C:\Misc\test.xlsx";

            var file = new FileInfo(filePath);

            //Load the data table into the excel file and save
            using (ExcelPackage pck = new ExcelPackage(file))
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Data");
                ws.Cells["A1"].LoadFromDataTable(t1, true);
                pck.Save();
            }

            //Close the existing connection to clean up
            conn.Close();
        }
    }
}

This works beautifully, but the next step is to convert this into a package that can be referenced as an assembly in SQL Server. The two main things I am looking to achieve here is the ability to pass an SQL query and a filename (as variables) to an SQL UDF, which passes this information to this package and runs the query and creates the file.

I would like to know, 1. if this is possible. 2. if there are any quick examples you can give me of how I would go about this.

Please forgive me for my ignorance on the subject of C#, I only started using it yesterday :/

Kind Regards and thanks in advance,

Johnny

Answer 1

As you said -

I would like to know, 1. if this is possible. 2. if there are any quick examples you can give me of how I would go about this.

Yes, it is possible.

Okay, for that you have to first create SQL Server Project in visual studio.

• In Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project.
• Then give your existing database connection as reference.
• Then Right click on Solution Explorer >> Click on Add >> Stored Procedure
• Now put your code in the file.

Here is your CLR Stored Proc code.

[SqlProcedure()]
    public static void GetMyTestData(
        SqlString sqlQuery, SqlString fileName)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand cmd = new SqlCommand(sqlQuery, conn);

            //Fill a C# dataset with query results
            DataTable t1 = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                a.Fill(t1);
            }

            //Assign filename to a variable

            var file = new FileInfo(fileName);

            //Load the data table into the excel file and save
            using (ExcelPackage pck = new ExcelPackage(file))
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Data");
                ws.Cells["A1"].LoadFromDataTable(t1, true);
                pck.Save();
            }

            conn.Close();
        }
    }

and now to for deployment of it follow this code project article

Hope it may help you...

Answer 2

Ok, so after a lot of deliberating over trying to deploy this to my database, I found out that the EPPlus library was using additional assemblies that could not be supported by SQL Server.

Rather than try to create this as a CLR function, I persisted with the console application method:

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace ExcelExport
{
    class Program
    {
        static void Main(string[] args)
        {

            //Create a connection to the database
            SqlConnection conn = new SqlConnection("user id=username;" +
                                                   "password=password;server=SQL-04;" +
                                                   "Trusted_Connection=yes;" +
                                                   "database=JOHN; " +
                                                   "connection timeout=30");
            conn.Open();
            //Set up the SQL query
            string query = args[0];
            SqlCommand cmd = new SqlCommand(query, conn);

            //Fill a C# dataset with query results
            DataTable t1 = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                a.Fill(t1);
            }

            //Assign filename to a variable
            string filePath = args[1];

            var file = new FileInfo(filePath);

            //Load the data table into the excel file and save
            using (ExcelPackage pck = new ExcelPackage(file))
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Data");
                ws.Cells["A1"].LoadFromDataTable(t1, true);
                pck.Save();
            }

            //Close the existing connection to clean up
            conn.Close();
        }
    }
}

Obviously this can be called with the command line. The idea is to pass the query and filepath as command line arguments which are read into main(). Once you have built this solution you can call the console app with XP_CMDSHELL in SQL Server. I've even added additional arguments to capture the server name and current database.

Additionally I also added some error handling and console writelines to return each stage of the application for debugging. This allows the end user to correctly identify what they need to do to get it to work.

Just one last note. If you want to test out the console app with arguments, you can pass it some arguments by right clicking on the project in the solution explorer, goto properties and then the Debug tab, then in there, there should be an option to enter some arguments. Make sure they are separated by a space. Without this you'll just get errors saying the arguments aren't specified in the args[] array.

Hope this helps anyone who's looking for a similar solution in the future.

Thanks!