????? 在實際應用中，我們經常需要導出數據到Excel文件，你可以使用DTS或SSIS來做。但有時，我們并不需要這么重量級的工具，直接用CLR SP實現就可以了。
假設你已經寫好了導出Excel的C# code:
1: /// <summary> 2: /// This is the method registered with SQL Server as a 3: /// CLR stored procedure. The attribute, Microsoft.SqlServer.Server.SqlProcedure, is 4: /// required for the method to be a CLR stored procedure. 5: /// </summary> 6: [Microsoft.SqlServer.Server.SqlProcedure]
7: public static void ExportToExcel(SqlString procName, SqlString filePath, SqlString fileName, SqlXml xmlParams)
8: {
9: DataSet exportData =
new DataSet();
10: ?
11: //check for empty parameters 12: ?
13: if (procName.Value ==
string.Empty)
14: throw new Exception(
"Procedure name value is missing.");
15: ?
16: if (filePath.Value ==
string.Empty)
17: throw new Exception(
"Missing file path location.");
18: ?
19: if (fileName.Value ==
string.Empty)
20: throw new Exception(
"Missing name of file.");
21: ?
22: using (SqlConnection conn =
new SqlConnection(
"context connection=true"))
23: {
24: SqlCommand getOutput =
new SqlCommand();
25: ?
26: getOutput.CommandText = procName.ToString(); ;
27: getOutput.CommandType = CommandType.StoredProcedure;
28: getOutput.CommandTimeout = 120;
29: ?
30: //To allow for multiple parameters, xml is used 31: //and must then be parsed to set up the paramaters 32: //for the command object. 33: using (XmlReader parms = xmlParams.CreateReader())
34: {
35: while(parms.Read())
36: {
37: if (parms.Name ==
"param")
38: {
39: string paramName;
40: paramName = parms.GetAttribute(
"name");
41: ?
42: string paramValue;
43: paramValue = parms.GetAttribute(
"value");
44: ?
45: getOutput.Parameters.AddWithValue(paramName, paramValue);
46: }
47: }
48: }
49: ?
50: getOutput.Connection = conn;
51: ?
52: conn.Open();
53: SqlDataAdapter da =
new SqlDataAdapter(getOutput);
54: da.Fill(exportData);
55: conn.Close();
56: }
57: ?
58: ExcelExportUtility exportUtil =
new ExcelExportUtility(fileName.ToString(),filePath.ToString());
59: //This allows for flexible naming of the tabs in the workbook 60: exportUtil.SheetNameColumnOrdinal = 0;
61: exportUtil.Export(exportData);
62: }
C#導出Excel細節代碼在這兒就不貼了。編譯成Assembly,這里我們叫ExcelExport.dll。
首先，啟用SQL CLR
1: sp_configure
'clr',1
2: reconfigure
設置Database可依賴性:
ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY ON
接下來，我們在AdventureWorks 數據庫中注冊這個Assembly.
1: CREATE ASSEMBLY ExportToExcel
2: FROM 'D:\ExcelExport.dll' 3: WITH PERMISSION_SET = EXTERNAL_ACCESS
創建一個存儲過程關聯它：
1: CREATE PROCEDURE [dbo].[prc_ExportToExcel]
2: @
proc [nvarchar](100),
3: @
path [nvarchar](200),
4: @filename [nvarchar](100),
5: @params xml
6: AS 7: EXTERNAL NAME [ExportToExcel].[StoredProcedures].[ExportToExcel]
好的。讓我們創建一個查詢數據的SP，加一個'MyContactList'列，導出時將取這個名稱做為Sheet的名稱，另返回多個數據集將生成多個Sheet頁。：
1: CREATE PROC GetContactByFirstName
2: @FirstName nvarchar(50)
3: AS 4: BEGIN 5: SELECT 'MyContactList' 6: ,[ContactID]
7: ,[FirstName]
8: ,[LastName]
9: ,[EmailAddress]
10: ,[Phone]
11: FROM [AdventureWorks].[Person].[Contact]
Where FirstName=@FirstName
12: END
現在，我們可以使用了,
Declare @params xml
Set @params =
'<params><param name="FirstName" value="Peter" /></params>'--
Set @params =
'<params />'exec [AdventureWorks].[dbo].[prc_ExportToExcel]
'[AdventureWorks].[dbo].[GetContactByFirstName]',
'D:\',
'MyContact', @params
注意，傳遞參數是xml，如果無參數剛傳’<params />’，FirstName是之前那個SP的參數。最后，運行，將生成文件 D:\MyContact.xls
?
完了，由此我們可以看到SQL CLR的強大。
Author:Petter Liu?? http://wintersun.cnblogs.com
希望對您有幫助！
總結
以上是生活随笔為你收集整理的SQLSERVER使用CLR Stored Procedure导出数据到Excel的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
