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

SQLServer数据导出到excel有很多种方法，比如dts、ssis、还可以用sql语句调用openrowset。我们这里开拓思路，用CLR来生成Excel文件，并且会考虑一些方便操作的细节。下面我先演示一下我实现的效果，先看测试语句——————————————————————————–复制代码代码如下: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 */ ——————————————————————————–上面的BulkCopyToXls存储过程是自定的CLR存储过程。他有四个参数：第一个是sql语句用来获取数据集第二个是文件保存的路径第三个是结果集的名字，我们用它来给文件命名第四个是限制单个文件可以保存多少条记录,小于等于0表示最多65534条。前三个参数没有什么特别，最后一个参数的设置可以让一个数据集分多个excel文件保存。比如传统excel的最大容量是65535条数据。我们这里参数设置为-1就表示导出达到这个数字之后自动写下一个文件。如果你设置了比如100，那么每导出100条就会自动写下一个文件。另外每个文件都可以输出字段名作为表头，所以单个文件最多容纳65534条数据。用微软公开的biff8格式通过二进制流生成excel，服务器无需安装excel组件，而且性能上不会比sql自带的功能差，48万多条数据，150M，用了24秒完成。下面我们来看下CLR代码。通过sql语句获取DataReader，然后分批用biff格式来写xls文件。——————————————————————————–复制代码代码如下: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> [Microsoft.SqlServer.Server.SqlProcedure ] 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); } } }; 把上面代码编译为TestExcel.dll，copy到服务器目录。然后通过如下SQL语句部署存储过程。——————————————————————————–复制代码代码如下: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 ——————————————————————————–当这项技术掌握在我们自己手中的时候，就可以随心所欲的来根据自己的需求定制。比如，我可以不要根据序号来分批写入excel，而是根据某个字段的值（比如一个表有200个城市的8万条记录）来划分为n个文件，而这个修改只要调整一下DataReader的循环里面的代码就行了。