用C＃创建一个SQL CLR UDF [英] Create an SQL CLR UDF with C#
问题描述
using System;
使用System.IO;
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Data;
使用System.Data.SqlClient;
使用OfficeOpenXml;
使用OfficeOpenXml.Style;
命名空间ExcelExport
{
类程序
{
static void Main（string [] args）
{
//创建与数据库的连接
SqlConnection conn = new SqlConnection（user id = username;+
password = password; server = SQL-04;+
Trusted_Connection = yes;+
database = JOHN;+
连接超时= 30）;
conn.Open（）;
//设置SQL查询
string query =SELECT TOP 10 FORENAME，SURNAME，POSTCODE FROM JOHN.DBO.TEST ORDER BY POSTCODE ASC;
SqlCommand cmd = new SqlCommand（query，conn）;
//用查询结果填写C＃数据集
DataTable t1 = new DataTable（）;
using（SqlDataAdapter a = new SqlDataAdapter（cmd））
{
a.Fill（t1）;
}
//将变量赋给变量
string filePath = @C：\Misc\test.xlsx;
var file = new FileInfo（filePath）;
//将数据表加载到excel文件中，并使用（ExcelPackage pck = new ExcelPackage（file））
{
ExcelWorksheet ws = pck.Workbook .Worksheets.Add（ 数据）;
ws.Cells [A1]。LoadFromDataTable（t1，true）;
pck.Save（）;
}
//关闭现有连接以清理
conn.Close（）;
}
}
}
这个工作很好，但是下一步是将其转换为可在SQL Server中作为程序集引用的包。我正在寻找的两个主要的功能是将SQL查询和文件名（作为变量）传递给SQL UDF，该文件将此信息传递到此程序包并运行查询并创建文件。
我想知道，1.如果可以的话2.如果有任何快速的例子，你可以给我我将如何处理这个。
请原谅我对C＃的无知，我只是开始使用它昨天：/
亲切并提前感谢，
Johnny
好的，所以经过很多细节试图将其部署到我的数据库之后，我发现EPPlus库正在使用不可能的附加程序集支持SQL Server。
而不是尝试将其创建为CLR功能，我坚持使用控制台应用程序方法：
使用系统;
使用System.IO;
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Data;
使用System.Data.SqlClient;
使用OfficeOpenXml;
使用OfficeOpenXml.Style;
命名空间ExcelExport
{
类程序
{
static void Main（string [] args）
{
//创建与数据库的连接
SqlConnection conn = new SqlConnection（user id = username;+
password = password; server = SQL-04;+
Trusted_Connection = yes;+
database = JOHN;+
连接超时= 30）;
conn.Open（）;
//设置SQL查询
string query = args [0];
SqlCommand cmd = new SqlCommand（query，conn）;
//用查询结果填写C＃数据集
DataTable t1 = new DataTable（）;
using（SqlDataAdapter a = new SqlDataAdapter（cmd））
{
a.Fill（t1）;
}
//将变量赋给变量
string filePath = args [1];
var file = new FileInfo（filePath）;
//将数据表加载到excel文件中，并使用（ExcelPackage pck = new ExcelPackage（file））
{
ExcelWorksheet ws = pck.Workbook .Worksheets.Add（ 数据）;
ws.Cells [A1]。LoadFromDataTable（t1，true）;
pck.Save（）;
}
//关闭现有连接以清理
conn.Close（）;
}
}
}
显然这可以用命令行。这个想法是将查询和filepath作为命令行参数传递给main（）。一旦你构建了这个解决方案，你可以使用XP_CMDSHELL在SQL Server中调用控制台应用程序。我甚至添加了其他参数来捕获服务器名称和当前数据库。
此外，我还添加了一些错误处理和控制台编写，以返回应用程序的每个阶段进行调试。这允许最终用户正确地识别他们需要做什么来使其工作。
只是最后一个注释。如果要使用参数测试控制台应用程序，可以通过右键单击解决方案资源管理器中的项目，goto属性，然后在调试选项卡上传递一些参数，然后在其中输入一些参数。确保它们被空格隔开。没有这个，你会得到错误，表示参数没有在args []数组中指定。
希望这可以帮助任何在未来寻找类似解决方案的人。
谢谢！
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
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!
这篇关于用C＃创建一个SQL CLR UDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
