程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 解析SQL2005中如何使用CLR函數獲取行號

解析SQL2005中如何使用CLR函數獲取行號

編輯:關於SqlServer

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

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved