用c#创建一个SQL CLR UDF

本文关键字：一个 SQL CLR UDF 创建 | 更新日期: 2023-09-27 18:05:35

我被赋予了能够将SQL查询结果集推送到Excel文件的任务。目前，我有一个c#控制台应用程序(如下)，它接受查询，并使用EPPlus库将结果转储到指定的xlsx文件中。

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();
        }
    }
}

这工作得很漂亮，但下一步是将其转换为可以在SQL Server中作为程序集引用的包。我希望在这里实现的两件主要事情是能够将SQL查询和文件名(作为变量)传递给SQL UDF，后者将此信息传递给此包，并运行查询并创建文件。

我想知道，1。如果可能的话。2. 如果你能给我一些简单的例子，告诉我该怎么做。

请原谅我对c#的无知，我昨天才开始使用它:/

致以亲切的问候和感谢，

约翰尼

用c#创建一个SQL CLR UDF

如你所说-

我想知道，1。如果可能的话。2. 如果有的话你可以给我一些例子，告诉我该怎么做。

是的，有可能。

好的，首先要在visual studio中创建SQL Server Project

  • 在Visual Studio中>>点击新建项目>>选择Visual c#>>数据库>> SQL Server项目
    • 然后提供您现有的数据库连接作为参考。
  • 然后右键单击解决方案资源管理器>>单击添加>>存储过程
    • 现在把你的代码放到文件中。

这是你的CLR存储过程代码。

[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();
        }
    }

，现在按照这个代码项目文章

进行部署

好的，所以在尝试将其部署到我的数据库之后，我发现EPPlus库使用了SQL Server不支持的附加程序集。

我没有尝试将其创建为CLR函数，而是坚持使用控制台应用程序方法: 
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();
        }
    }
}

显然可以用命令行调用。其思想是将查询和文件路径作为命令行参数传递到main()中。一旦你构建了这个解决方案，你就可以在SQL Server中使用XP_CMDSHELL调用控制台应用程序。我甚至添加了额外的参数来捕获服务器名称和当前数据库。

此外，我还添加了一些错误处理和控制台写行，以返回应用程序的每个阶段以进行调试。这允许最终用户正确地确定他们需要做什么才能使它工作。

最后一点。如果你想用参数测试控制台应用程序，你可以通过在解决方案资源管理器中右键单击项目，选择属性，然后选择Debug选项卡，然后在那里，应该有一个输入一些参数的选项。确保它们之间用空格隔开。如果没有这个，你只会得到错误，说参数没有在args[]数组中指定。

希望这对将来寻找类似解决方案的人有所帮助。

谢谢!

相关文章：