解析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的循環裡面的代碼就行了

From:http://tw.wingwit.com/Article/program/MySQL/201311/29542.html