因工作需要写了个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 } } }