SQLSERVER使用CLR Stored Procedure导出数据到Excel

最新推荐文章于 2023-02-16 22:56:36 发布

cuanji3287

最新推荐文章于 2023-02-16 22:56:36 发布

阅读量99

点赞数

在实际应用中，我们经常需要导出数据到Excel文件，你可以使用DTS或SSIS来做。但有时，我们并不需要这么重量级的工具，直接用CLR SP实现就可以了。
假设你已经写好了导出Excel的C# code:

   1:    ///

   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:      ///

   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:      }

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

C#导出Excel细节代码在这儿就不贴了。编译成Assembly,这里我们叫ExcelExport.dll。

首先，启用SQL CLR

   1:  sp_configure'clr',1

   2:  reconfigure

设置Database可依赖性:

ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY ON

   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 = ''

--Set @params = ''

exec [AdventureWorks].[dbo].[prc_ExportToExcel]

'[AdventureWorks].[dbo].[GetContactByFirstName]', 'D:\', 'MyContact', @params

注意，传递参数是xml，如果无参数刚传’’，FirstName是之前那个SP的参数。最后，运行，将生成文件 D:\MyContact.xls

完了，由此我们可以看到SQL CLR的强大。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-621640/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-621640/

cuanji3287

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫