SQL Server ->> CLR存储过程枚举目录文件并返回结果集

因工作需要写了个CLR存储过程枚举目录文件并返回结果集

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

//Author:   Jerry Chen(v-jerrch@microsoft.com)
//Date:     10/7/2015
//Comment:  This is a CLR class that provides methods to be called to do something beyond the ability of SQL Server
//
//

namespace StoredProcedures
{
    public partial class EnumerateSourceFileDirectory
    {
        [SqlProcedure()]
        public static void GetFileListByBeginEndAndPattern(
            SqlString SourceFolder, SqlDateTime BeginModDate, SqlDateTime EndModDate, SqlString FileNamePattern, SqlInt16 IsSubfolderScanned)
        {
            if (SourceFolder.ToString().Length == 0)
            {
                throw new System.ArgumentException("SourceFolder cannot be null or empty.", "");
            }

            if ((!BeginModDate.IsNull && !EndModDate.IsNull && BeginModDate > EndModDate))
            {
                throw new System.ArgumentException("'Begin Modify Date' shouldn't be later than 'End Modify Date'.", "");
            }

            //Comment out because Directory.Exists doesn't work for network path
            //if (!Directory.Exists(SourceFolder.ToString()))
            //{
            //    throw new System.ArgumentException("Source folder doesn't exist.", "");
            //}

            DirectoryInfo DirInfo = new DirectoryInfo(SourceFolder.ToString());
            DateTime dt1 = (DateTime)BeginModDate;
            DateTime dt2 = (DateTime)EndModDate;

            var files = from file in DirInfo.EnumerateFiles(FileNamePattern.ToString(), IsSubfolderScanned == 1 ? SearchOption.AllDirectories: SearchOption.TopDirectoryOnly)
            where file.CreationTimeUtc > dt1 & file.CreationTimeUtc < dt2 
            select file;

            //create a SqlDataRecord to store file info
            SqlDataRecord rec = new SqlDataRecord(new SqlMetaData[] {
                                new SqlMetaData("FileName", SqlDbType.NVarChar,2000),
                                new SqlMetaData("FilFullName", SqlDbType.NVarChar,4000),
                                new SqlMetaData("CreateDateUTC", SqlDbType.DateTime),
                                new SqlMetaData("ModifyDateUTC", SqlDbType.DateTime),
                                new SqlMetaData("Size_in_bytes", SqlDbType.BigInt),
                            });

            // start sending and tell the pipe to use the created record
            SqlContext.Pipe.SendResultsStart(rec);
            {
                foreach (var file in files)
                {
                    rec.SetSqlString(0, file.Name);
                    rec.SetSqlString(1, file.FullName);
                    rec.SetDateTime(2, file.CreationTimeUtc);
                    rec.SetDateTime(3, file.LastWriteTimeUtc);
                    rec.SetInt64(4, file.Length);

                    // send new record/row
                    SqlContext.Pipe.SendResultsRow(rec);
                }
            }
            SqlContext.Pipe.SendResultsEnd();    // finish sending
        }

        [SqlProcedure()]
        public static void GetFileListByBeginEndAndExtension(
            SqlString SourceFolder, SqlDateTime BeginModDate, SqlDateTime EndModDate, SqlString FileExtension, SqlInt16 IsSubfolderScanned)
        {
            if (SourceFolder.ToString().Length == 0)
            {
                throw new System.ArgumentException("SourceFolder cannot be null or empty.", "");
            }

            if ((!BeginModDate.IsNull && !EndModDate.IsNull && BeginModDate > EndModDate))
            {
                throw new System.ArgumentException("'Begin Modify Date' shouldn't be later than 'End Modify Date'.", "");
            }

            //Comment out because Directory.Exists doesn't work for network path
            //if (!Directory.Exists(SourceFolder.ToString()))
            //{
            //    throw new System.ArgumentException("Source folder doesn't exist.", "");
            //}

            DirectoryInfo DirInfo = new DirectoryInfo(SourceFolder.ToString());
            DateTime dt1 = (DateTime)BeginModDate;
            DateTime dt2 = (DateTime)EndModDate;

            var files = from file in DirInfo.EnumerateFiles("*", IsSubfolderScanned == 1 ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
                        where file.CreationTimeUtc > dt1 & file.CreationTimeUtc < dt2 & file.Extension == FileExtension.ToString()
                        select file;

            //create a SqlDataRecord to store file info
            SqlDataRecord rec = new SqlDataRecord(new SqlMetaData[] {
                                new SqlMetaData("FileName", SqlDbType.NVarChar,2000),
                                new SqlMetaData("FilFullName", SqlDbType.NVarChar,4000),
                                new SqlMetaData("CreateDateUTC", SqlDbType.DateTime),
                                new SqlMetaData("ModifyDateUTC", SqlDbType.DateTime),
                                new SqlMetaData("Size_in_bytes", SqlDbType.BigInt),
                            });

            // start sending and tell the pipe to use the created record
            SqlContext.Pipe.SendResultsStart(rec);
            {
                foreach (var file in files)
                {
                    rec.SetSqlString(0, file.Name);
                    rec.SetSqlString(1, file.FullName);
                    rec.SetDateTime(2, file.CreationTimeUtc);
                    rec.SetDateTime(3, file.LastWriteTimeUtc);
                    rec.SetInt64(4, file.Length);

                    // send new record/row
                    SqlContext.Pipe.SendResultsRow(rec);
                }
            }
            SqlContext.Pipe.SendResultsEnd();    // finish sending
        }

        [SqlProcedure()]
        public static void GetFileListByExtension(
            SqlString SourceFolder, SqlString FileExtension, SqlInt16 IsSubfolderScanned)
        {
            //validation
            if (SourceFolder.ToString().Length == 0)
            {
                throw new System.ArgumentException("SourceFolder cannot be null or empty.", "");
            }


            //Comment out because Directory.Exists doesn't work for network path
            //if (!Directory.Exists(SourceFolder.ToString()))
            //{
            //    throw new System.ArgumentException("Source folder doesn't exist.", "");
            //}

            //set directory
            DirectoryInfo DirInfo = new DirectoryInfo(SourceFolder.ToString());

            //enumerate files
            var files = from file in DirInfo.EnumerateFiles("*", IsSubfolderScanned == 1 ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
                        where file.Extension == FileExtension.ToString()
                        select file;

            //create a SqlDataRecord to store file info
            SqlDataRecord rec = new SqlDataRecord(new SqlMetaData[] {
                                new SqlMetaData("FileName", SqlDbType.NVarChar,2000),
                                new SqlMetaData("FilFullName", SqlDbType.NVarChar,4000),
                                new SqlMetaData("CreateDateUTC", SqlDbType.DateTime),
                                new SqlMetaData("ModifyDateUTC", SqlDbType.DateTime),
                                new SqlMetaData("Size_in_bytes", SqlDbType.BigInt),
                            });

            // start sending and tell the pipe to use the created record
            SqlContext.Pipe.SendResultsStart(rec);
            {
                foreach (var file in files)
                {
                    rec.SetSqlString(0, file.Name);
                    rec.SetSqlString(1, file.FullName);
                    rec.SetDateTime(2, file.CreationTimeUtc);
                    rec.SetDateTime(3, file.LastWriteTimeUtc);
                    rec.SetInt64(4, file.Length);

                    // send new record/row
                    SqlContext.Pipe.SendResultsRow(rec);
                }
            }
            SqlContext.Pipe.SendResultsEnd();    // finish sending
        }

        [SqlProcedure()]
        public static void GetFileListByExtensionAndPattern(
            SqlString SourceFolder, SqlString FileExtension, SqlString FileNamePattern, SqlInt16 IsSubfolderScanned)
        {
            //validation
            if (SourceFolder.ToString().Length == 0)
            {
                throw new System.ArgumentException("SourceFolder cannot be null or empty.", "");
            }


            //Comment out because Directory.Exists doesn't work for network path
            //if (!Directory.Exists(SourceFolder.ToString()))
            //{
            //    throw new System.ArgumentException("Source folder doesn't exist.", "");
            //}

            //set directory
            DirectoryInfo DirInfo = new DirectoryInfo(SourceFolder.ToString());

            //enumerate files
            var files = from file in DirInfo.EnumerateFiles(FileNamePattern.ToString(), IsSubfolderScanned == 1 ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
                        where file.Extension == FileExtension.ToString()
                        select file;

            //create a SqlDataRecord to store file info
            SqlDataRecord rec = new SqlDataRecord(new SqlMetaData[] {
                                new SqlMetaData("FileName", SqlDbType.NVarChar,2000),
                                new SqlMetaData("FilFullName", SqlDbType.NVarChar,4000),
                                new SqlMetaData("CreateDateUTC", SqlDbType.DateTime),
                                new SqlMetaData("ModifyDateUTC", SqlDbType.DateTime),
                                new SqlMetaData("Size_in_bytes", SqlDbType.BigInt),
                            });

            // start sending and tell the pipe to use the created record
            SqlContext.Pipe.SendResultsStart(rec);
            {
                foreach (var file in files)
                {
                    rec.SetSqlString(0, file.Name);
                    rec.SetSqlString(1, file.FullName);
                    rec.SetDateTime(2, file.CreationTimeUtc);
                    rec.SetDateTime(3, file.LastWriteTimeUtc);
                    rec.SetInt64(4, file.Length);

                    // send new record/row
                    SqlContext.Pipe.SendResultsRow(rec);
                }
            }
            SqlContext.Pipe.SendResultsEnd();    // finish sending
        }
    }
}