/*
*************************************************************************************
*
* Written By: Gregory A. Larsen
* Date: May 23, 2006
*
* Name: xp_getfiledetails.cs
*
* Description:
*    The undocumented xp_getfiledetails extended stored procedure was available in SQL Server 2000 
but was not provided with SQL Server 2005.  There for this C# program was written to replace 
*    the functionality of the old xp_getfiledetails extended stored procedure. 
*
*    This code is to be defined as a CLR in SQL Server 2005 and placed in a database.  The old xp_getfiledetails
*    was originally provided in the master database.
*    
*    The following DOS command can be used to compile this C# program.  To use this command place this source code in a file 
*    named "c:\temp\xp_getfiledetails.cs" on your SQL Server 2005 machine.  Then run the following command from the 
*    DOS prompt.
*  
*    c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc /target:library /out:c:\temp\xp_getfiledetails.dll c:\temp\xp_getfiledetails.cs
* 
*    Note: You might need to change the "V2.0.50727" to the appropriate .NET 2.0 version your running on your
*          machine.  Also you might want to store this vb program and generated dll in a different location then
*          "c:\temp".
*
*    The following commands will assemble and create this CLR as a callable stored procedure in SQL Server 2005.  Run
*    these commands in the database you want to assemble and create your CLR stored procedure:
*
*    CREATE ASSEMBLY xp_getfiledetails from 'c:\temp\xp_getfiledetails.dll' WITH PERMISSION_SET = UNSAFE
*    go
*    CREATE PROCEDURE xp_getfiledetails 
*    @FileName nvarchar(128)
*    AS
*    EXTERNAL NAME xp_getfiledetails.SQLCLR.xp_getfiledetails
*    go
*
*    Here is an example of how to call this CLR using T-SQL:
*
*    declare @i int
*    exec @i=xp_getfiledetails 'C:\temp\xp_getfiledetails.cs'
*    print @i
*
**************************************************************************************
*/

// Define Namespaces needed
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Globalization;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;

// S Q L C L R ***************************************************************************   
public class SQLCLR
{ // Begin SQLCLR Class
		
	// Identify that this is a SQL Stored Procedure
	[Microsoft.SqlServer.Server.SqlProcedure] 

// G e t S h o r t P a t h N a m e ***************************************************************************   
	// include GetShortPathName API to get short name of file
	[DllImport("kernel32.dll")]
	static extern int GetShortPathName(string lpszLongPath, 
                                           StringBuilder lpszShortPath, 
                                           int cchBuffer);

// L e f t ***********************************************************************
	// code to simulate the Visual Basic Left Function
	public static string Left(string param, int length)
        	{ // Begin Left

            	// we start at 0 since we want to get the characters starting from the
            	// left and with the specified length and assign it to a variable
            	string result = param.Substring(0, length);
            	//return the result of the operation
            	return result;

        	} // End Left

// G e t S h o r t N a m e ***************************************************************************   
	// Get the short name of a DOS file 
	public static string GetShortName(string longName)
		{ // Begin GetShortName

	    	StringBuilder shortNameBuffer = new StringBuilder(256);
    	    	int bufferSize = shortNameBuffer.Capacity;

    	    	int result = GetShortPathName(longName, shortNameBuffer, bufferSize);

    	    	return shortNameBuffer.ToString();

		} // End GetShortName

// x p _ g e t f i l e d e t a i l s ***************************************************************************   
 	public static int xp_getfiledetails(String FileName) 
 	{ // Begin xp_getfiledetails

        // Setup TRY/CATCH block to handle error trapping   
        try
        { //Begin Try

                // Declare the FileInfo object f that will contain the file information for filename
            	FileInfo f = new FileInfo(FileName);
        
	    	// Variables used to define the columns in the final result set
   	    	SqlMetaData AlternateName;
   	    	SqlMetaData Size;
   	    	SqlMetaData CreationDate;
   		SqlMetaData CreationTime;
   		SqlMetaData LastWrittenDate;
   		SqlMetaData LastWrittenTime;
   		SqlMetaData LastAccessDate;
   		SqlMetaData LastAccessTime;
   		SqlMetaData Attributes;
   		SqlDataRecord record;

   		// Create each column in the final result set
   		AlternateName = new SqlMetaData("ALternate Name", SqlDbType.NVarChar, 128);
   		Size = new SqlMetaData("Size", SqlDbType.BigInt);
   		CreationDate = new SqlMetaData("Creation Date", SqlDbType.Char, 8);
   		CreationTime = new SqlMetaData("Creation Time", SqlDbType.Char, 6);
   		LastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.Char, 8);
   		LastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.Char, 6);
   		LastAccessDate = new SqlMetaData("Last Accessed Date", SqlDbType.Char, 8);
   		LastAccessTime = new SqlMetaData("Last Accessed Time", SqlDbType.Char, 6);
   		Attributes = new SqlMetaData("Attributes", SqlDbType.Int);
      
   		// Create a new record with column information    
   		record = new SqlDataRecord(new SqlMetaData[] { AlternateName, 
                                                               Size, 
                                                               CreationDate,
                                                               CreationTime,
                                                               LastWrittenDate,
                                                               LastWrittenTime, 
                                                               LastAccessDate,
                                                               LastAccessTime, 
                                                               Attributes });


                // Set each column to the appropriate value and format

		// Set the Alternative Name column for the file
      		record.SetSqlString(0,System.IO.Path.GetFileName(GetShortName(FileName)));

                // Set the size column for the file
   		record.SetInt64(1, f.Length);

		// Set the Creation Date column in YYYYMMDD format
   		record.SetSqlString(2,f.CreationTime.ToString("yyyyMMdd"));

		// Set the Creation Time column in HHMMSS
   		record.SetSqlString(3,f.CreationTime.ToString("HHmmss"));

		// Set the Last Written Date column in YYYYMMDD format
   		record.SetSqlString(4,f.LastWriteTime.ToString("yyyyMMdd"));

		// Set the Last Written Time column in HHMMSS
   		record.SetSqlString(5,f.LastWriteTime.ToString("HHmmss"));

		// Set the Last Access Date column in YYYYMMDD format
   		record.SetSqlString(6,f.LastAccessTime.ToString("yyyyMMdd"));
	
		// Set the Last Access Time column in HHMMSS format
   		record.SetSqlString(7,f.LastAccessTime.ToString("HHmmss"));

		// Set the Attribute column value
   		record.SetSqlInt32(8, (int) f.Attributes);

                // Return record to client
		SqlContext.Pipe.Send(record);
              
                // If no errors set return code to zero
      		return 0;

	} // End Try

        // Catch All Exceptions
        catch (Exception e)
        { // Begin Catch

		// Send the error information back to the client
		SqlContext.Pipe.Send(e.ToString());
	
		// Set the return code to 1 indicating an error has occurred
		return 1;

	} //End Catch
	
	} //End xp_getfiledetails

} //End SQLCLR class