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

本篇文章是对SQL2005中如何使用CLR函数获取行号的实现代码进行了详细的分析介绍,需要的朋友参考下

SQLServer数据导出到excel有很多种方法,比如dts、ssis、还可以用sql语句调用openrowset。我们这里开拓思路,用CLR来生成Excel文件,并且会考虑一些方便操作的细节。
下面我先演示一下我实现的效果,先看测试语句
——————————————————————————–
<div class=”codetitle”><a style=”CURSOR: pointer” data=”36893″ class=”copybut” id=”copybut36893″ onclick=”doCopy(‘code36893’)”> 代码如下:<div class=”codebody” id=”code36893″>
exec BulkCopyToXls ‘select from testTable’,’d:/test’,’testTable’,- 1
/

开始导出数据
文件 d:/test/testTable.0.xls,共65534条,大小20,450,868 字节
文件 d:/test/testTable.1.xls,大小 20,101,773 字节
文件 d:/test/testTable.2.xls,040,589 字节
文件 d:/test/testTable.3.xls,大小 19,948,925 字节
文件 d:/test/testTable.4.xls,080,974 字节
文件 d:/test/testTable.5.xls,056,737 字节
文件 d:/test/testTable.6.xls,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文件。
——————————————————————————–
<div class=”codetitle”><a style=”CURSOR: pointer” data=”28607″ class=”copybut” id=”copybut28607″ onclick=”doCopy(‘code28607’)”> 代码如下:<div class=”codebody” id=”code28607″>
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server; public partial class StoredProcedures
{
///


/// 导出数据
///


/// <param name=”sql”>
/// <param name=”savePath”>
/// <param name=”tableName”>
/// <param name=”maxRecordCount”>
[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);
} ///


/// 查询数据,生成文件
///


/// <param name=”sql”>
/// <param name=”savePath”>
/// <param name=”tableName”>
/// <param name=”maxRecordCount”>
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));
}
}
}
}
///


/// 写单元格
///


/// <param name=”writer”>
/// <param name=”obj”>
/// <param name=”x”>
/// <param name=”y”>
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,Convert .ToDouble(obj.ToString()));
break ;
default :
writer.WriteString(x,obj.ToString());
break ;
}
}
///


/// 写一批数据到一个excel 文件
///


/// <param name=”reader”>
/// <param name=”count”>
/// <param name=”fileName”>
///
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;
} ///


/// 写excel 的对象
///


public class ExcelWriter
{
System.IO.FileStream _wirter;
public ExcelWriter(string strPath)
{
_wirter = new System.IO.FileStream (strPath,System.IO.FileMode .OpenOrCreate);
}
///


/// 写入short 数组
///


/// <param name=”values”>
private void _writeFile(System.UInt16 [] values)
{
foreach (System.UInt16 v in values)
{
byte [] b = System.BitConverter .GetBytes(v);
_wirter.Write(b,b.Length);
}
}
///


/// 写文件头
///


public void BeginWrite()
{
_writeFile(new System.UInt16 [] { 0x809,8,0×10,0 });
}
///


/// 写文件尾
///


public void EndWrite()
{
_writeFile(new System.UInt16 [] { 0xa,0 });
_wirter.Close();
}
///


/// 写一个数字到单元格x,y
///


/// <param name=”x”>
/// <param name=”y”>
/// <param name=”value”>
public void WriteNumber(System.UInt16 x,System.UInt16 y,double value)
{
_writeFile(new System.UInt16 [] { 0x203,14,x,0 });
byte [] b = System.BitConverter .GetBytes(value);
_wirter.Write(b,b.Length);
}
///


/// 写一个字符到单元格x,y
///


/// <param name=”x”>
/// <param name=”y”>
/// <param name=”value”>
public void WriteString(System.UInt16 x,string value)
{
byte [] b = System.Text.Encoding .Default.GetBytes(value);
_writeFile(new System.UInt16 [] { 0x204,(System.UInt16 )(b.Length + 8),(System.UInt16 )b.Length });
_wirter.Write(b,b.Length);
}
}
};

把上面代码编译为TestExcel.dll,copy到服务器目录。然后通过如下SQL语句部署存储过程。
——————————————————————————–
<div class=”codetitle”><a style=”CURSOR: pointer” data=”6581″ class=”copybut” id=”copybut6581″ onclick=”doCopy(‘code6581’)”> 代码如下:<div class=”codebody” id=”code6581″>
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的循环里面的代码就行了。

作者: dawei

【声明】:永州站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

为您推荐

联系我们

联系我们

0577-28828765

在线咨询: QQ交谈

邮箱: xwei067@foxmail.com

工作时间:周一至周五,9:00-17:30,节假日休息

返回顶部