解析SQL2005中如何使用CLR函数获取行号

半个和尚 發表於 2013-6-27 12:33:42

解析SQL2005中如何使用CLR函数获取行号

SQLServer数据导出到excel有很多种方法，比如dts、ssis、还可以用sql语句调用openrowset。我们这里开拓思路，用CLR来生成Excel文件，并且会考虑一些方便操作的细节。 下面我先演示一下我实现的效果，先看测试语句 -------------------------------------------------------------------------------- <div class="codetitle"><a data="65427" class="copybut" id="copybut65427">复制代码</a> 代码如下:</div><div class="codebody" id="code65427"> exec BulkCopyToXls 'select * from testTable' , 'd:/test' , 'testTable' ,- 1 /* 开始导出数据 文件 d:/test/testTable.0.xls, 共65534条 , 大小20 ,450,868 字节 文件 d:/test/testTable.1.xls, 共65534条 , 大小 20 ,101,773 字节 文件 d:/test/testTable.2.xls, 共65534条 , 大小 20 ,040,589 字节 文件 d:/test/testTable.3.xls, 共65534条 , 大小 19 ,948,925 字节 文件 d:/test/testTable.4.xls, 共65534条 , 大小 20 ,080,974 字节 文件 d:/test/testTable.5.xls, 共65534条 , 大小 20 ,056,737 字节 文件 d:/test/testTable.6.xls, 共65534条 , 大小 20 ,590,933 字节 文件 d:/test/testTable.7.xls, 共26002条 , 大小 8,419,533 字节 导出数据完成 ------- 共484740条数据，耗时 23812ms */ </div> -------------------------------------------------------------------------------- 上面的BulkCopyToXls存储过程是自定的CLR存储过程。他有四个参数： 第一个是sql语句用来获取数据集 第二个是文件保存的路径 第三个是结果集的名字，我们用它来给文件命名 第四个是限制单个文件可以保存多少条记录,小于等于0表示最多65534条。 前三个参数没有什么特别，最后一个参数的设置可以让一个数据集分多个excel文件保存。比如传统excel的最大容量是65535条数据。我们这里参数设置为-1就表示导出达到这个数字之后自动写下一个文件。如果你设置了比如100，那么每导出100条就会自动写下一个文件。 另外每个文件都可以输出字段名作为表头，所以单个文件最多容纳65534条数据。 用微软公开的biff8格式通过二进制流生成excel，服务器无需安装excel组件，而且性能上不会比sql自带的功能差，48万多条数据，150M，用了24秒完成。 下面我们来看下CLR代码。通过sql语句获取DataReader，然后分批用biff格式来写xls文件。 -------------------------------------------------------------------------------- <div class="codetitle"><a data="57141" class="copybut" id="copybut57141">复制代码</a> 代码如下:</div><div class="codebody" id="code57141"> using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {     /// <summary>     /// 导出数据     /// </summary>     /// <param name="sql"></param>     /// <param name="savePath"></param>     /// <param name="tableName"></param>     /// <param name="maxRecordCount"></param>         public static void BulkCopyToXls(SqlString sql, SqlString savePath, SqlString tableName, SqlInt32 maxRecordCount)     {          if (sql.IsNull || savePath.IsNull || tableName.IsNull)         {             SqlContext .Pipe.Send(" 输入信息不完整！" );         }         ushort _maxRecordCount = ushort .MaxValue-1;         if (maxRecordCount.IsNull == false && maxRecordCount.Value < ushort .MaxValue&&maxRecordCount.Value>0)             _maxRecordCount = (ushort )maxRecordCount.Value;         ExportXls(sql.Value, savePath.Value, tableName.Value, _maxRecordCount);     }     /// <summary>     /// 查询数据，生成文件     /// </summary>     /// <param name="sql"></param>     /// <param name="savePath"></param>     /// <param name="tableName"></param>     /// <param name="maxRecordCount"></param>     private static void ExportXls(string sql, string savePath, string tableName, System.UInt16 maxRecordCount)     {         if (System.IO.Directory .Exists(savePath) == false )         {             System.IO.Directory .CreateDirectory(savePath);         }         using (SqlConnection conn = new SqlConnection ("context connection=true" ))         {             conn.Open();             using (SqlCommand command = conn.CreateCommand())             {                 command.CommandText = sql;                 using (SqlDataReader reader = command.ExecuteReader())                 {                     int i = 0;                     int totalCount = 0;                     int tick = System.Environment .TickCount;                     SqlContext .Pipe.Send(" 开始导出数据" );                     while (true )                     {                         string fileName = string .Format(@"{0}/{1}.{2}.xls" , savePath, tableName, i++);                         int iExp = Write(reader, maxRecordCount, fileName);                         long size = new System.IO.FileInfo (fileName).Length;                         totalCount += iExp;                         SqlContext .Pipe.Send(string .Format(" 文件{0}, 共{1} 条, 大小{2} 字节" , fileName, iExp, size.ToString("###,###" )));                         if (iExp < maxRecordCount) break ;                     }                     tick = System.Environment .TickCount - tick;                      SqlContext .Pipe.Send(" 导出数据完成" );                      SqlContext .Pipe.Send("-------" );                      SqlContext .Pipe.Send(string .Format(" 共{0} 条数据，耗时{1}ms" ,totalCount,tick));                 }             }         }       }     /// <summary>     /// 写单元格     /// </summary>     /// <param name="writer"></param>     /// <param name="obj"></param>     /// <param name="x"></param>     /// <param name="y"></param>     private static void WriteObject(ExcelWriter writer, object obj, System.UInt16 x, System.UInt16 y)     {         string type = obj.GetType().Name.ToString();         switch (type)         {             case "SqlBoolean" :             case "SqlByte" :             case "SqlDecimal" :             case "SqlDouble" :             case "SqlInt16" :             case "SqlInt32" :             case "SqlInt64" :             case "SqlMoney" :             case "SqlSingle" :                 if (obj.ToString().ToLower() == "null" )                     writer.WriteString(x, y, obj.ToString());                 else                     writer.WriteNumber(x, y, Convert .ToDouble(obj.ToString()));                 break ;             default :                 writer.WriteString(x, y, obj.ToString());                 break ;         }     }     /// <summary>     /// 写一批数据到一个excel 文件     /// </summary>     /// <param name="reader"></param>     /// <param name="count"></param>     /// <param name="fileName"></param>     /// <returns></returns>     private static int Write(SqlDataReader reader, System.UInt16 count, string fileName)     {         int iExp = count;         ExcelWriter writer = new ExcelWriter (fileName);         writer.BeginWrite();         for (System.UInt16 j = 0; j < reader.FieldCount; j++)         {             writer.WriteString(0, j, reader.GetName(j));         }         for (System.UInt16 i = 1; i <= count; i++)         {             if (reader.Read() == false )             {                 iExp = i-1;                 break ;             }             for (System.UInt16 j = 0; j < reader.FieldCount; j++)             {                 WriteObject(writer, reader.GetSqlValue(j), i, j);             }         }         writer.EndWrite();         return iExp;     }     /// <summary>     /// 写excel 的对象     /// </summary>     public class ExcelWriter     {         System.IO.FileStream _wirter;         public ExcelWriter(string strPath)         {             _wirter = new System.IO.FileStream (strPath, System.IO.FileMode .OpenOrCreate);         }         /// <summary>         /// 写入short 数组         /// </summary>         /// <param name="values"></param>         private void _writeFile(System.UInt16 [] values)         {             foreach (System.UInt16 v in values)             {                 byte [] b = System.BitConverter .GetBytes(v);                 _wirter.Write(b, 0, b.Length);             }         }         /// <summary>         /// 写文件头         /// </summary>         public void BeginWrite()         {             _writeFile(new System.UInt16 [] { 0x809, 8, 0, 0x10, 0, 0 });         }         /// <summary>         /// 写文件尾         /// </summary>         public void EndWrite()         {             _writeFile(new System.UInt16 [] { 0xa, 0 });             _wirter.Close();         }         /// <summary>         /// 写一个数字到单元格x,y         /// </summary>         /// <param name="x"></param>         /// <param name="y"></param>         /// <param name="value"></param>         public void WriteNumber(System.UInt16 x, System.UInt16 y, double value)         {             _writeFile(new System.UInt16 [] { 0x203, 14, x, y, 0 });             byte [] b = System.BitConverter .GetBytes(value);             _wirter.Write(b, 0, b.Length);          }         /// <summary>         /// 写一个字符到单元格x,y         /// </summary>         /// <param name="x"></param>         /// <param name="y"></param>         /// <param name="value"></param>         public void WriteString(System.UInt16 x, System.UInt16 y, string value)         {             byte [] b = System.Text.Encoding .Default.GetBytes(value);             _writeFile(new System.UInt16 [] { 0x204, (System.UInt16 )(b.Length + 8), x, y, 0, (System.UInt16 )b.Length });             _wirter.Write(b, 0, b.Length);         }     } }; </div> 把上面代码编译为TestExcel.dll，copy到服务器目录。然后通过如下SQL语句部署存储过程。 -------------------------------------------------------------------------------- <div class="codetitle"><a data="35115" class="copybut" id="copybut35115">复制代码</a> 代码如下:</div><div class="codebody" id="code35115"> CREATE ASSEMBLY TestExcelForSQLCLR FROM 'd:/sqlclr/TestExcel.dll' WITH PERMISSION_SET = UnSAFE; -- go CREATE proc dbo. BulkCopyToXls  (       @sql nvarchar ( max ),     @savePath nvarchar ( 1000),     @tableName nvarchar ( 1000),     @bathCount int )     AS EXTERNAL NAME TestExcelForSQLCLR. StoredProcedures. BulkCopyToXls go </div> -------------------------------------------------------------------------------- 当这项技术掌握在我们自己手中的时候，就可以随心所欲的来根据自己的需求定制。比如，我可以不要根据序号来分批写入excel，而是根据某个字段的值（比如一个表有200个城市的8万条记录）来划分为n个文件，而这个修改只要调整一下DataReader的循环里面的代码就行了。

頁: [1]

琼殿技术论坛's Archiver

解析SQL2005中如何使用CLR函数获取行号