I used it to replace xp_getfiledetails. This allowed several developers relying on the stored procedure to just do a find and replace on the stored procedure name in their code to be able to move from 2000 to 2005.

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

namespace Enterprise.SqlServer.Server

{

public partial class GetFileDetails

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void csp_getfiledetails(string filePath)

{

try

{

FileInfo fileProperties = new FileInfo(filePath);

SqlMetaData colAlternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000);

SqlMetaData colSize = new SqlMetaData("Size", SqlDbType.BigInt);

SqlMetaData colCreationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8);

SqlMetaData colCreationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6);

SqlMetaData colLastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8);

SqlMetaData colLastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6);

SqlMetaData colLastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8);

SqlMetaData colLastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6);

SqlMetaData colAttributes = new SqlMetaData("Attributes", SqlDbType.Int);

SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {

colAlternateName,

colSize,

colCreationDate,

colCreationTime,

colLastWrittenDate,

colLastWrittenTime,

colLastAccessedDate,

colLastAccessedTime,

colAttributes});

record.SetInt64(1, fileProperties.Length);

record.SetString(2, fileProperties.CreationTime.ToString("yyyyMMdd"));

record.SetString(3, fileProperties.CreationTime.ToString("HHmmss"));

record.SetString(4, fileProperties.LastWriteTime.ToString("yyyyMMdd"));

record.SetString(5, fileProperties.LastWriteTime.ToString("HHmmss"));

record.SetString(6, fileProperties.LastAccessTime.ToString("yyyyMMdd"));

record.SetString(7, fileProperties.LastAccessTime.ToString("HHmmss"));

char[] splitter = { ',' };

string[] attributes = fileProperties.Attributes.ToString().Split(splitter);

int attributesInt = 0;

foreach (string attributesString in attributes)

{

FileAttributes fileAttributes = (FileAttributes)Enum.Parse(typeof(FileAttributes), attributesString);

attributesint += (int)fileAttributes;

}

record.SetInt32(8, attributesInt);

record.SetInt32(8, (int)fileProperties.Attributes);

SqlContext.Pipe.Send(record);

}

catch (Exception myexception)

{

throw (myexception);

}

}

};

}