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

????? 在实际应用中，我们经常需要导出数据到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的强大。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!