解析SQL 2005でCLR関数を使用して行番号を取得する方法
11260 ワード
SQLServerデータをexcelにエクスポートするには、dts、ssisなど、sql文でopenrowsetを呼び出す方法がたくさんあります.ここではCLRを使ってExcelファイルを作成し、操作しやすい詳細を考慮します.
次に、私が実現した効果を説明します.まず、テスト文を見てみましょう.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
上記のBulkCopyToXlsストレージ・プロシージャは、独自のCLRストレージ・プロシージャです.彼には4つのパラメータがあります.
1つ目はsql文がデータセットを取得するために使用されます
2つ目はファイル保存のパスです
3つ目は結果セットの名前で、ファイルに名前を付けます.
4つ目は、1つのファイルにどれだけのレコードを保存できるかを制限し、0以下は最大65534件を表します.
最初の3つのパラメータは特にありません.最後のパラメータの設定では、1つのデータセットを複数のexcelファイルに分けて保存できます.例えば、従来のexcelの最大容量は65535データである.ここでパラメータを-1に設定すると、この数値にエクスポートされた後に自動的に次のファイルが書き込まれることを示します.たとえば100を設定すると、100をエクスポートするたびに自動的に次のファイルが書き込まれます.
また、各ファイルはフィールド名をヘッダとして出力できるため、単一ファイルには最大65534個のデータが格納されます.
マイクロソフトが公開したbiff 8フォーマットでバイナリストリームを通じてexcelを生成し、サーバはexcelコンポーネントをインストールする必要がなく、sqlが持つ機能よりも性能が悪くなく、48万件以上のデータ、150 M、24秒で完成した.
次にCLRコードを見てみましょう.sql文でDataReaderを取得し、バッチでbiff形式でxlsファイルを書きます.
--------------------------------------------------------------------------------
上のコードをTestExcelにコンパイルします.dll、copyからサーバディレクトリへ.次に、次のSQL文を使用してストレージ・プロシージャを配置します.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
この技術が私たちの手に握られているとき、自分のニーズに合わせてカスタマイズすることができます.たとえば、シーケンス番号に基づいてexcelにバッチで書き込むのではなく、あるフィールドの値(例えば、テーブルに200都市の8万件の記録がある)に基づいてn個のファイルに分割することができますが、この修正はDataReaderのループ内のコードを調整すればいいだけです.
次に、私が実現した効果を説明します.まず、テスト文を見てみましょう.
--------------------------------------------------------------------------------
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ストレージ・プロシージャです.彼には4つのパラメータがあります.
1つ目はsql文がデータセットを取得するために使用されます
2つ目はファイル保存のパスです
3つ目は結果セットの名前で、ファイルに名前を付けます.
4つ目は、1つのファイルにどれだけのレコードを保存できるかを制限し、0以下は最大65534件を表します.
最初の3つのパラメータは特にありません.最後のパラメータの設定では、1つのデータセットを複数のexcelファイルに分けて保存できます.例えば、従来のexcelの最大容量は65535データである.ここでパラメータを-1に設定すると、この数値にエクスポートされた後に自動的に次のファイルが書き込まれることを示します.たとえば100を設定すると、100をエクスポートするたびに自動的に次のファイルが書き込まれます.
また、各ファイルはフィールド名をヘッダとして出力できるため、単一ファイルには最大65534個のデータが格納されます.
マイクロソフトが公開したbiff 8フォーマットでバイナリストリームを通じてexcelを生成し、サーバはexcelコンポーネントをインストールする必要がなく、sqlが持つ機能よりも性能が悪くなく、48万件以上のデータ、150 M、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
{
///
///
///
///
///
///
///
[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);
}
///
/// ，
///
///
///
///
///
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));
}
}
}
}
///
///
///
///
///
///
///
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 ;
}
}
///
/// excel
///
///
///
///
///
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
///
///
private void _writeFile(System.UInt16 [] values)
{
foreach (System.UInt16 v in values)
{
byte [] b = System.BitConverter .GetBytes(v);
_wirter.Write(b, 0, b.Length);
}
}
///
///
///
public void BeginWrite()
{
_writeFile(new System.UInt16 [] { 0x809, 8, 0, 0x10, 0, 0 });
}
///
///
///
public void EndWrite()
{
_writeFile(new System.UInt16 [] { 0xa, 0 });
_wirter.Close();
}
///
/// x,y
///
///
///
///
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);
}
///
/// x,y
///
///
///
///
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のループ内のコードを調整すればいいだけです.