在实际应用中,我们经常需要导出数据到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

exceldata

 

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

Author:Petter Liu   http://wintersun.cnblogs.com

希望对您有帮助!

posted on 2009-12-03 18:02  PetterLiu  阅读(1474)  评论(2编辑  收藏  举报