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

SQLServer数据导出到excel有很多种方法比如dtsssis还可以用sql语句调用openrowset我们这里开拓思路用CLR来生成Excel文件并且会考虑一些方便操作的细节
下面我先演示一下我实现的效果先看测试语句

复制代码 代码如下:
exec BulkCopyToXls select * from testTable d:/test testTable
/*
开始导出数据
文件 d:/test/testTablexls 共条 大小 字节
文件 d:/test/testTablexls 共条 大小 字节
文件 d:/test/testTablexls 共条 大小 字节
文件 d:/test/testTablexls 共条 大小 字节
文件 d:/test/testTablexls 共条 大小 字节
文件 d:/test/testTablexls 共条 大小 字节
文件 d:/test/testTablexls 共条 大小 字节
文件 d:/test/testTablexls 共条 大小 字节
导出数据完成

共条数据耗时 ms
*/

上面的BulkCopyToXls存储过程是自定的CLR存储过程他有四个参数
第一个是sql语句用来获取数据集
第二个是文件保存的路径
第三个是结果集的名字我们用它来给文件命名
第四个是限制单个文件可以保存多少条记录小于等于表示最多条
前 三个参数没有什么特别最后一个参数的设置可以让一个数据集分多个excel文件保存比如传统excel的最大容量是条数据我们这里参数设 置为就表示导出达到这个数字之后自动写下一个文件如果你设置了比如那么每导出条就会自动写下一个文件
另外每个文件都可以输出字段名作为表头所以单个文件最多容纳条数据
用微软公开的biff格式通过二进制流生成excel服务器无需安装excel组件而且性能上不会比sql自带的功能差万多条数据M用了秒完成

下面我们来看下CLR代码通过sql语句获取DataReader然后分批用biff格式来写xls文件

复制代码 代码如下:
using System;
using SystemData;
using SystemDataSqlClient;
using SystemDataSqlTypes;
using MicrosoftSqlServerServer;

public partial class StoredProcedures
{
/// <summary>
/// 导出数据
/// </summary>
/// <param name="sql"></param>
/// <param name="savePath"></param>
/// <param name="tableName"></param>
/// <param name="maxRecordCount"></param>
[MicrosoftSqlServerServerSqlProcedure ]
public static void BulkCopyToXls(SqlString sql SqlString savePath SqlString tableName SqlInt maxRecordCount)
{
if (sqlIsNull || savePathIsNull || tableNameIsNull)
{
SqlContext PipeSend(" 输入信息不完整！" );
}
ushort _maxRecordCount = ushort MaxValue;

if (maxRecordCountIsNull == false &&  maxRecordCountValue < ushort  MaxValue&&maxRecordCountValue>)
_maxRecordCount = (ushort )maxRecordCountValue;

ExportXls(sqlValue savePathValue tableNameValue _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 SystemUInt maxRecordCount)
{

if (SystemIODirectory Exists(savePath) == false )
{
SystemIODirectory CreateDirectory(savePath);
}

using (SqlConnection conn = new SqlConnection ("context connection=true" ))
{
connOpen();
using (SqlCommand command = connCreateCommand())
{
commandCommandText = sql;
using (SqlDataReader reader = commandExecuteReader())
{
int i = ;
int totalCount = ;
int tick = SystemEnvironment TickCount;
SqlContext PipeSend(" 开始导出数据" );
while (true )
{
string fileName = string Format(@"{}/{}{}xls" savePath tableName i++);
int iExp = Write(reader maxRecordCount fileName);
long size = new SystemIOFileInfo (fileName)Length;
totalCount += iExp;
SqlContext PipeSend(string Format(" 文件{}  共{} 条 大小{} 字节" fileName iExp sizeToString("######" )));
if (iExp < maxRecordCount) break ;
}
tick = SystemEnvironment TickCount tick;
SqlContext PipeSend(" 导出数据完成" );

SqlContext PipeSend("" );
SqlContext PipeSend(string Format(" 共{} 条数据耗时{}ms" totalCounttick));
}
}
}


}
/// <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 SystemUInt x SystemUInt y)
{
string type = objGetType()NameToString();
switch (type)
{
case "SqlBoolean" :
case "SqlByte" :
case "SqlDecimal" :
case "SqlDouble" :
case "SqlInt" :
case "SqlInt" :
case "SqlInt" :
case "SqlMoney" :
case "SqlSingle" :
if (objToString()ToLower() == "null" )
writerWriteString(x y objToString());
else
writerWriteNumber(x y Convert ToDouble(objToString()));
break ;
default :
writerWriteString(x y objToString());
break ;
}
}
/// <summary>
/// 写一批数据到一个excel 文件
/// </summary>
/// <param name="reader"></param>
/// <param name="count"></param>
/// <param name="fileName"></param>
/// <returns></returns>
private static int Write(SqlDataReader reader SystemUInt count string fileName)
{
int iExp = count;
ExcelWriter writer = new ExcelWriter (fileName);
writerBeginWrite();
for (SystemUInt j = ; j < readerFieldCount; j++)
{
writerWriteString( j readerGetName(j));
}
for (SystemUInt i = ; i <= count; i++)
{
if (readerRead() == false )
{
iExp = i;
break ;
}
for (SystemUInt j = ; j < readerFieldCount; j++)
{
WriteObject(writer readerGetSqlValue(j) i j);
}
}
writerEndWrite();
return iExp;
}

/// <summary>
/// 写excel 的对象
/// </summary>
public class ExcelWriter
{
SystemIOFileStream _wirter;
public ExcelWriter(string strPath)
{
_wirter = new SystemIOFileStream (strPath SystemIOFileMode OpenOrCreate);
}
/// <summary>
/// 写入short 数组
/// </summary>
/// <param name="values"></param>
private void _writeFile(SystemUInt [] values)
{
foreach (SystemUInt v in values)
{
byte [] b = SystemBitConverter GetBytes(v);
_wirterWrite(b bLength);
}
}
/// <summary>
/// 写文件头
/// </summary>
public void BeginWrite()
{
_writeFile(new SystemUInt [] { x x });
}
/// <summary>
/// 写文件尾
/// </summary>
public void EndWrite()
{
_writeFile(new SystemUInt [] { xa });
_wirterClose();
}
/// <summary>
/// 写一个数字到单元格xy
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void WriteNumber(SystemUInt x SystemUInt y double value)
{
_writeFile(new SystemUInt [] { x x y });
byte [] b = SystemBitConverter GetBytes(value);
_wirterWrite(b bLength);
}
/// <summary>
/// 写一个字符到单元格xy
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void WriteString(SystemUInt x SystemUInt y string value)
{
byte [] b = SystemTextEncoding DefaultGetBytes(value);
_writeFile(new SystemUInt [] { x (SystemUInt )(bLength + ) x y (SystemUInt )bLength });
_wirterWrite(b bLength);
}
}
};

把上面代码编译为TestExceldllcopy到服务器目录然后通过如下SQL语句部署存储过程

复制代码 代码如下:
CREATE ASSEMBLY TestExcelForSQLCLR FROM d:/sqlclr/TestExceldll WITH PERMISSION_SET = UnSAFE;

go
CREATE proc dbo BulkCopyToXls  
(   
@sql nvarchar ( max )
@savePath nvarchar ( )
@tableName nvarchar ( )
@bathCount int
)     
AS EXTERNAL NAME TestExcelForSQLCLR StoredProcedures BulkCopyToXls

go

当 这项技术掌握在我们自己手中的时候就可以随心所欲的来根据自己的需求定制比如我可以不要根据序号来分批写入excel而是根据某个字段的值（比如 一个表有个城市的万条记录）来划分为n个文件而这个修改只要调整一下DataReader的循环里面的代码就行了