因工作需要写了个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([email protected])
//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 == )
{
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 == ? 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,),
new SqlMetaData("FilFullName", SqlDbType.NVarChar,),
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(, file.Name);
rec.SetSqlString(, file.FullName);
rec.SetDateTime(, file.CreationTimeUtc);
rec.SetDateTime(, file.LastWriteTimeUtc);
rec.SetInt64(, 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 == )
{
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 == ? 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,),
new SqlMetaData("FilFullName", SqlDbType.NVarChar,),
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(, file.Name);
rec.SetSqlString(, file.FullName);
rec.SetDateTime(, file.CreationTimeUtc);
rec.SetDateTime(, file.LastWriteTimeUtc);
rec.SetInt64(, 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 == )
{
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 == ? 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,),
new SqlMetaData("FilFullName", SqlDbType.NVarChar,),
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(, file.Name);
rec.SetSqlString(, file.FullName);
rec.SetDateTime(, file.CreationTimeUtc);
rec.SetDateTime(, file.LastWriteTimeUtc);
rec.SetInt64(, 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 == )
{
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 == ? 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,),
new SqlMetaData("FilFullName", SqlDbType.NVarChar,),
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(, file.Name);
rec.SetSqlString(, file.FullName);
rec.SetDateTime(, file.CreationTimeUtc);
rec.SetDateTime(, file.LastWriteTimeUtc);
rec.SetInt64(, file.Length); // send new record/row
SqlContext.Pipe.SendResultsRow(rec);
}
}
SqlContext.Pipe.SendResultsEnd(); // finish sending
}
}
}