Home    Articles    Managed Stored Procedures and User Defined Functions - Part I

Managed Stored Procedures and User Defined Functions - Part I Monday, December 06, 2004

Introduction

Prior to SQL Server 2005, Transact-SQL (T-SQL) and Extended Stored Procedures were the two options to write server-side code. Some DBAs/developers used sp_OA* extended stored procedures to invoke COM automation components from within the T-SQL code. Writing extended stored procedures (XP) and, understanding and using Open Data Service (ODS) API is not a trivial task. In addition, reliability is the other challenge with XP. A poorly written XP can potentially cause a number of unpredictable problems with SQL Server. As the extended stored procedure code resides in an external DLL, if you backed up a database, the extended stored procedure will not be part of it. You have to separately preserve a copy of the XP DLL. Also, if you were to move the database to a different server, you have to move the XP DLL file and register on that server.

SQL Server 2005 introduces a much better alternative - using any of the .NET managed languages, such as C# and Visual Basic .NET, to extend the functionality not provided by T-SQL or something that would perform better as a compiled code, such as computational tasks. Programming in .NET languages and using in-process data provider is much easier, efficient, and reliable compared to programming extended stored procedure using C++ and ODS.

The .NET Framework integration enables access to thousands of classes in the .NET Base Class Library (BCL) in your managed procedures, triggers, and functions. You can write managed objects in your choice of managed language, and not just be limited to C++. The Visual Studio .NET 2005 IDE contains excellent support for debugging managed objects.

When I started learning SQLCLR integration in SQL Server 2005, I converted few existing documented and un-documented extended stored procedures into managed procedures and functions. In addition I wrote some new utility managed stored procedures and functions, such as to load XML from a file, save XML into a file, read and write INI files, get the user environment settings, and so on. This article has more code and less talk. I will not go into SQLCLR integration details. If you want to learn about SQLCLR integration in SQL Server 2005, see the article "Using CLR Integration in SQL Server 2005" on MSDN.

Getting Ready

Visual Studio 2005 "Whidbey" contains excellent support for building, debugging, and deploying SQLCLR managed code. Click on File | New | Project, under Visual C# and Visual C# | Database, you will see a SQL Server Project type. Once you create a project of this type, in Solution Explorer, you can right click and add a new managed Stored Procedure, Function, Aggregate, Trigger, or a user-defined type. If you click on Project | Properties for such a project, you will see Database and Deploy tabs that you can use to directly deploy your managed code into SQL Server 2005. In this article, I will not use the Deploy functionality of Visual Studio 2005, but we'll manually run the DDL statements to register the assembly and create the managed objects mapping. Feel free to try out the Deploy functionality provided by Visual Studio .NET 2005 IDE.

To create and use a managed database object, the steps that I followed are:

• Create a new C# SQL Server project using Visual Studio .NET 2005 IDE. Add a new stored procedure or function. Fill in the generated template with the procedure logic.
• Build the assembly.
• Upload the assembly into AdventureWorks SQL Server 2005 sample database using CREATE ASSEMBLY DDL statement.
• Map the managed stored procedure or function to a T-SQL call using CREATE PROCEDURE or CREATE FUNCTION using "AS EXTERNAL NAME" clause.
• Once this mapping is created, calling managed procedure or function is no different that calling a built-in stored procedure or a function.

My First Managed Stored Procedure: Create a Directory

Let's begin with a managed stored procedure that allows creating a folder. There is no direct equivalent extended stored procedure available in SQL Server.

Create a new SQL Server C# project & add a new stored procedure. You'll notice that the template created by the IDE contains a public class with a public, static method having the SqlProcedure attribute. We'll use the System.IO.Directory.Exists method to find out if the specified folder exists. Here is how the code looks like:

using System; 
using System.Data; 
using System.Data.Sql; 
using System.Data.SqlServer; 
using System.Data.SqlTypes; 

using System.IO; 

public partial class StoredProcedures 
{ 
   [SqlProcedure] 
   public static void DirCreate(String NewDirFullPath) 
   { 
      try 
      { 
         if (Directory.Exists(NewDirFullPath)) 
            SqlContext.GetPipe().Send(String.Format("Directory '{0}' already exists.", 
                  NewDirFullPath)); 
         else 
         { 
            Directory.CreateDirectory(NewDirFullPath); 
            SqlContext.GetPipe().Send(String.Format("Directory '{0}' created.", 
                  NewDirFullPath)); 
         } 
      } 
      catch (Exception exp) 
      { 
         SqlContext.GetPipe().Send(String.Format("Error while creating '{0}'. {1}", 
               NewDirFullPath, exp.ToString())); 
      } 
   } 
};

The above code is a managed stored procedure written in C#. We'll eventually import this into SQL Server 2005, map a stored procedure to this method, and call a stored procedure. The stored procedure, and hence the above method code, will be called from a client that already has a connection established with SQL Server. In other words, when the above code is executed, it is already within a connection scope. You can use the System.Data.SqlServer.SqlContext class to interact with the database using the current connection in context. This class has methods like GetConnection(), GetCommand(), GetTransaction() and so on to get the connection under which this code is running, the currently executing command, currently executing transaction context, and so on. One such method is GetPipe(). This method returns an instance of SqlPipe class. The SqlContext.GetPipe() static method returns an object representing the channel or TDS stream between the client and the server. If you write something on this SqlPipe object, it will be sent to the client. That's what the above code does. It calls SqlPipe.Send method to send a message string to a client. The SqlContext class represents current execution context and it eliminates the need to open another connection to the database, as you have to do if you are writing an extended stored procedure.

Build the above project. Let's assume that it builds into an assembly named YukonXML.com.Samples.DiskIO.dll. The next step is to import this assembly into SQL Server 2005 database and map a T-SQL stored procedure to the above method.

Start SQL Server Management Studio, connect to a SQL Server 2005 instance, and run the following commands:

USE AdventureWorks;
GO

IF OBJECT_ID('sqlclr_DirCreate') IS NOT NULL
   DROP PROCEDURE sqlclr_DirCreate;
GO

IF EXISTS(SELECT name FROM sys.assemblies WHERE name = 'YukonXML.com.Samples.DiskIO')
   DROP ASSEMBLY [YukonXML.com.Samples.DiskIO];
GO
CREATE ASSEMBLY [YukonXML.com.Samples.DiskIO]
FROM 'C:\SQLCLR\XP2SQLCLR\DiskIO\bin\Release\YukonXML.com.Samples.DiskIO.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE PROCEDURE dbo.sqlclr_DirCreate
(
   @NewDirFullPath nvarchar(1024)
)
AS EXTERNAL NAME [YukonXML.com.Samples.DiskIO].[StoredProcedures].[DirCreate]
GO

The above T-SQL statements first checks if an object named sqlclr_DirCreate exists. If it does, we assume it is a stored procedure, and we drop it. Next it uses sys.assemblies catalog view to check if an assembly named 'YukonXML.com.Samples.DiskIO' is already imported into the database. If it is, then we drop the assembly using DROP ASSEMBLY command and then we import the assembly into SQL Server database using CREATE ASSEMBLY DDL statement. With this DLL statement, SQL Server 2005 allows you to determine what an assembly can do or cannot do. The three permission levels defined by SQL Server 2005 are SAFE, EXTERNAL_ACCESS, and UNSAFE.

The assemblies imported with SAFE permission set can work with data and use some of the CLR utility classes, but they cannot access external resources (such as file system, network, etc.), the code must be verifiable type safe by the CLR, and things like COM interop, PInvoke, multithreading, etc. are not allowed. This is the default permission set.

The assemblies imported with EXTERNAL_ACCESS are same as SAFE, but also allows access to external sources such as file system using the .NET Framework class library. Since the above code needs to access the file system, EXTERNAL_ACCESS permission set is required for the assembly.

The assemblies imported with the UNSAFE can do virtually anything. COM interop, PInvoke, etc. is allowed. It is recommended that UNSAFE permissions be granted only to highly trusted assemblies by experienced developers or administrators. Only members of the sysadmin fixed server role can create UNSAFE assemblies.

The final DDL statement in the above T-SQL code creates a stored procedure named sqlclr_DirCreate and maps it to the DirCreate method in the assembly just imported.

Once the above steps are followed, calling managed stored procedure is no different than calling a traditional stored procedure. This was one of the goals of the SQLCLR integration team that the caller should not know if the stored procedure being called is a managed or a traditional T-SQL code.

EXEC dbo.sqlclr_DirCreate 'c:\temp\SalesData';

Run the above command and you should see the following output:

Directory 'c:\temp\SalesData' created.

Verify that the SalesData folder is created. If you run the sqlclr_DirCreate again, you should see the following output:

Directory 'c:\temp\SalesData' already exists.

To summarize, we used Visual Studio .NET 2005 to create a C# project of type SQL Server, added a stored procedure, it created a public class with a public static method having SqlProcedure attribute, we filled in our logic in this method, we used SqlContext to get the current executing environment, GetPipe method to get the client TDS stream pointer and used Send method to send the string message to the client. Once the assembly was ready, we imported it into SQL Server 2005 AdventureWorks sample database using CREATE ASSEMBLY DDL and mapped a T-SQL stored procedure to the C# method. After this, calling this managed method is no different than calling a stored procedure.

Deleting a Directory

Let's write a managed stored procedure to remove a folder. Right click on the project in the solution explorer, and add a new stored procedure and add the code as shown below:

using System; 
using System.Data; 
using System.Data.Sql; 
using System.Data.SqlServer; 
using System.Data.SqlTypes; 

using System.IO; 

public partial class StoredProcedures 
{ 
   [SqlProcedure] 
   public static void DirRemove(String directory, SqlBoolean deleteContents) 
   { 
      try 
      { 
         if (Directory.Exists(directory)) 
         { 
            Directory.Delete(directory, deleteContents.Value); 

            SqlContext.GetPipe().Send(String.Format("Directory '{0}' successfully deleted.",
                     directory)); 
         } 
         else 
            SqlContext.GetPipe().Send(String.Format("Directory '{0}' does not exist.", 
                   directory)); 
      } 
      catch (Exception exp) 
      { 
         SqlContext.GetPipe().Send(String.Format("Error while deleting '{0}'. {1}",
                 directory, exp.ToString())); 
      } 
   } 
}; 

The above code is very similar to the earlier method to create the directory. Build the solution and import the assembly into SQL Server database and map the stored procedure:

IF OBJECT_ID('sqlclr_DirRemove') IS NOT NULL
   DROP PROCEDURE sqlclr_DirRemove;
GO

IF EXISTS(SELECT name FROM sys.assemblies WHERE name = 'YukonXML.com.Samples.DiskIO')
   ALTER ASSEMBLY [YukonXML.com.Samples.DiskIO];
GO

CREATE ASSEMBLY [YukonXML.com.Samples.DiskIO]
FROM 'C:\SQLCLR\XP2SQLCLR\DiskIO\bin\Release\YukonXML.com.Samples.DiskIO.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE PROCEDURE sqlclr_DirRemove
(
	@Directory nvarchar(1024),
	@DeleteContents bit = 0
)
AS EXTERNAL NAME [YukonXML.com.Samples.DiskIO].[StoredProcedures].[DirRemove]
GO

While creating the T-SQL procedure mapping for the C# method, we pass the default value for the second parameter. By default the second parameter @DeleteContents is assigned the bit value 0, indicating if the folder contains something, don't delete it. If you pass 1 as the second parameter while calling the sqlclr_DirRemove store procedure, it will recursively delete the folder contents and the folder itself.

Be very careful while trying out this method. This stored procedure actually deletes the physical folder. Use this method with some test folder. The deleted folder and its contents will not be in recycle bin either. Use extreme caution when trying out this method.

Create some more sub-folders within the c:\temp\SalesData folder created using the earlier example, and then run the following command:

EXEC dbo.sqlclr_DirRemove 'c:\temp\SalesData';

The output you should see:

Error while deleting 'c:\temp\SalesData'. System.IO.IOException: 
   The directory is not empty.

   at System.IO.__Error.WinIOError(Int32 errorCode, String str)
   at System.IO.Directory.DeleteHelper(String fullPath, String userPath, Boolean recursive)
   at System.IO.Directory.Delete(String fullPath, String userPath, Boolean recursive)
   at StoredProcedures.DirRemove(String directory, SqlBoolean deleteContents)

Now try:

EXEC dbo.sqlclr_DirRemove 'c:\temp\SalesData', 1;

The output you should see:

Directory 'c:\temp\SalesData' successfully deleted.

Run the above command again:

EXEC dbo.sqlclr_DirRemove 'c:\temp\SalesData', 1;

The output you should see:

Directory 'c:\temp\SalesData' does not exist.

Moving a Directory

Let's write a managed stored procedure to move a folder. Right click on the project in the solution explorer, and add a new stored procedure and add the code as shown below:

using System; 
using System.Data; 
using System.Data.Sql; 
using System.Data.SqlServer; 
using System.Data.SqlTypes; 

using System.IO; 
public partial class StoredProcedures 
{ 
   [SqlProcedure] 
   public static void DirMove(String SourceDir, String DestinationDir) 
   { 
      try 
      { 
         if (Directory.Exists(SourceDir)) 
         { 
            Directory.Move(SourceDir, DestinationDir); 
            SqlContext.GetPipe().Send(String.Format("Successfully moved '{0}' to '{1}'.", 
            SourceDir, DestinationDir)); 
         } 
         else 
            SqlContext.GetPipe().Send(String.Format("Source folder '{0}' does not exist.", 
            SourceDir)); 
      } 
      catch (Exception exp) 
      { 
         SqlContext.GetPipe().Send(String.Format("Error while moving '{0}' to '{1}'. {2}", 
         SourceDir, DestinationDir, exp.ToString())); 
      } 

   } 
}; 

Use the T-SQL statements similar to earlier two examples to import the managed stored procedure. Download the zip file for this article to see the complete T-SQL script. Here is the partial T-SQL text:

....
....
CREATE PROCEDURE dbo.sqlclr_DirMove
(
   @SourceDir nvarchar(1024),
   @DestinationDir nvarchar(1024)
)
AS EXTERNAL NAME [YukonXML.com.Samples.DiskIO].[StoredProcedures].[DirMove]
GO

Copying a File

Let's write a managed stored procedure to copy a disk file. Right click on the project in the solution explorer, and add a new stored procedure and add the code as shown below:

using System; 
using System.Data; 
using System.Data.Sql; 
using System.Data.SqlServer; 
using System.Data.SqlTypes; 

using System.IO; 

public partial class StoredProcedures 
{ 
   [SqlProcedure] 
   public static void FileCopy(String SourceFile, String DestinationFile, SqlBoolean Overwrite) 
   { 
      try 
      { 
         if (File.Exists(SourceFile)) 
         { 
            File.Copy(SourceFile, DestinationFile, Overwrite.Value); 
            SqlContext.GetPipe().Send(String.Format("Successfully copied '{0}' to '{1}'.", 
                   SourceFile, DestinationFile)); 
         } 
         else 
            SqlContext.GetPipe().Send(String.Format("Source file '{0}' does not exist.", 
                   SourceFile)); 
      } 
      catch (Exception exp) 
      { 
         SqlContext.GetPipe().Send(String.Format("Error while copying '{0}' to '{1}'. {2}", 
            SourceFile, DestinationFile, exp.ToString())); 
      } 
   } 
}; 

Use the T-SQL statements similar to earlier two examples to import the managed stored procedure. Download the zip file for this article to see the complete T-SQL script. Here is the partial T-SQL text:

....
....
CREATE PROCEDURE dbo.sqlclr_FileCopy
(
   @SourceFile nvarchar(1024),
   @DestinationFile nvarchar(1024),
   @Overwrite bit = 0
)
AS EXTERNAL NAME [YukonXML.com.Samples.DiskIO].[StoredProcedures].[FileCopy]
GO

The code download for this article includes a method to move a file. See FileMove.cs in the DiskIO project for more details.

Sending the Resultset

So far we were just sending the string message text to the client. Let's now see how to send the actual resultset to the client. Let's write a managed stored procedure equivalent to master..xp_subdirs undocumented extended stored procedure. Given the folder name, this procedure returns all the sub-folders directly under the specified folder name. Right click on the project in the solution explorer, and add a new stored procedure and add the code as shown below:

using System; 
using System.Data; 
using System.Data.Sql; 
using System.Data.SqlServer; 
using System.Data.SqlTypes; 

using System.IO; 

public partial class StoredProcedures 
{ 
   [SqlProcedure] 
   public static void SubDirs(String directory) 
   { 
      if (directory == null || directory.Length <= 0 || !Directory.Exists(directory)) 
      { 
         throw new Exception("Error executing sqlclr_SubDirs. Invalid parameter."); 
      } 

      try 
      { 
         DirectoryInfo dirInfo = new DirectoryInfo(directory + @"\"); 

         SqlMetaData[] subdirs = new SqlMetaData[1]; 
         subdirs[0] = new SqlMetaData("subdirectory", SqlDbType.NVarChar, 1000); 

         SqlDataRecord record = new SqlDataRecord(subdirs); 

         SqlPipe sqlpipe = SqlContext.GetPipe(); 

         sqlpipe.SendResultsStart(record, false); 

         foreach (DirectoryInfo subdir in dirInfo.GetDirectories()) 
         { 
            record.SetString(0, subdir.Name); 
            sqlpipe.SendResultsRow(record); 
         } 

         sqlpipe.SendResultsEnd(); 
      } 
      catch (Exception exp) 
      { 
         throw new Exception("Error executing sqlclr_SubDirs.", exp); 
      } 
   } 
}; 

The above managed stored procedure accepts a String parameter. The method performs some validation to make sure that input parameter is not null or empty string and that the specified folder exists.

Then we create a single element array of class System.Data.Sql.SqlMetaData. This class is used to hold the metadata about a column.

Since we would like to return just a single column, we have declared an array with just one element. We fill this single array element with an instance of SqlMetaData class containing definition for a NVARCHAR(1000) type column named "subdirectory". We then create an instance of System.Data.SqlDataRecord class and pass the column metadata to it.

Next, we call the SendResultsStart method on the SqlPipe class. This method is used to indicate beginning of a new result set being returned to the client. We are passing the second parameter (sendRow) as false, telling ADO.NET to just send the metadata, and not the record values to the client.

Then for each subdirectory, we update the record column value, and use SendResultsRow to send the record to the client. Finally, when done, we call SendResultsEnd to indicate the end of result set.

Use the T-SQL statements similar to earlier examples to import the managed stored procedure. Download the zip file for this article to see the complete T-SQL script. Here is the partial T-SQL text:

....
....
CREATE PROCEDURE dbo.sqlclr_SubDirs
(
    @Directory nvarchar(1024)
)
AS EXTERNAL NAME [YukonXML.com.Samples.DiskIO].[StoredProcedures].[SubDirs]
GO

Try the above managed stored procedure:

EXEC dbo.sqlclr_SubDirs 'c:\Temp';

Tip:

Here are some of the catalog views related to .NET CLR integration:

SELECT * FROM sys.assemblies;
GO
SELECT * FROM sys.assembly_files;
GO
SELECT * FROM sys.assembly_modules;
GO
SELECT * FROM sys.assembly_references;
GO
SELECT * FROM sys.assembly_types;
GO

Equivalent to Undocumented XP 'xp_dirtree'

The following method accepts a folder name and returns the entire folder hierarchy under the specified directory. Try out this method with a folder that does not have too many sub-folders in the hierarchy.

From now on, I will not present the T-SQL code to import the assembly and create a T-SQL procedure for the managed procedure. Refer to the .sql script file included with the code download for this article contains for complete details.

using System; 
using System.Data; 
using System.Data.Sql; 
using System.Data.SqlServer; 
using System.Data.SqlTypes; 

using System.IO; 

public partial class StoredProcedures 
{ 
   [SqlProcedure] 
   public static void DirTree(String directory) 
   { 
      try 
      { 
         SqlPipe sqlpipe = SqlContext.GetPipe(); 

         SqlMetaData[] subdirs = new SqlMetaData[2]; 
         subdirs[0] = new SqlMetaData("subdirectory", SqlDbType.NVarChar, 1000); 
         subdirs[1] = new SqlMetaData("depth", SqlDbType.Int); 

         SqlDataRecord record = new SqlDataRecord(subdirs); 

         sqlpipe.SendResultsStart(record, false); 

         if (!(directory == null || directory.Length <= 0 ||  
             !Directory.Exists(directory))) 
            RecurseFolder(directory, 0, sqlpipe, record); 

         sqlpipe.SendResultsEnd(); 
      } 
      catch (Exception exp) 
      { 
         throw new Exception("Error executing sqlclr_DirTree.", exp); 
      } 
   } 

   private static void RecurseFolder(String directory, Int32 currentLevel,  
       SqlPipe sqlpipe, SqlDataRecord record) 
   { 
      try 
      { 
         record = new SqlDataRecord(record.MetaData); 

         DirectoryInfo dirInfo = new DirectoryInfo(directory + @"\"); 

         currentLevel++; 

         foreach (DirectoryInfo subdir in dirInfo.GetDirectories()) 
         { 
            record.SetString(0, subdir.Name); 
            record.SetInt32(1, currentLevel); 
            sqlpipe.SendResultsRow(record); 

            RecurseFolder(subdir.FullName, currentLevel, sqlpipe, record); 
         } 
      } 
      catch (Exception exp) 
      { 
          throw new Exception("RecurseFolder :: Error executing sqlclr_SubDirs.", 
              exp); 
      } 
   } 
}; 

Directory Information

using System; 
using System.Data; 
using System.Data.Sql; 
using System.Data.SqlServer; 
using System.Data.SqlTypes; 

using System.IO; 

public partial class StoredProcedures 
{ 
   [SqlProcedure] 
   public static void DirInfo(String directory) 
   { 
      try 
      { 
         SqlPipe sqlpipe = SqlContext.GetPipe(); 

         SqlMetaData[] metadatarec = new SqlMetaData[11]; 
         metadatarec[0] = new SqlMetaData("Attributes", SqlDbType.VarChar, 100); 
         metadatarec[1] = new SqlMetaData("Files Count", SqlDbType.BigInt); 
         metadatarec[2] = new SqlMetaData("SubDir Count", SqlDbType.BigInt); 
         metadatarec[3] = new SqlMetaData("CreationTime", SqlDbType.VarChar, 100); 
         metadatarec[4] = new SqlMetaData("CreationTimeUtc", SqlDbType.VarChar, 100); 
         metadatarec[5] = new SqlMetaData("Parent", SqlDbType.VarChar, 255); 
         metadatarec[6] = new SqlMetaData("Root", SqlDbType.VarChar, 100); 
         metadatarec[7] = new SqlMetaData("LastAccessTime", SqlDbType.VarChar, 100); 
         metadatarec[8] = new SqlMetaData("LastAccessTimeUtc", SqlDbType.VarChar, 100); 
         metadatarec[9] = new SqlMetaData("LastWriteTime", SqlDbType.VarChar, 100); 
         metadatarec[10] = new SqlMetaData("LastWriteTimeUtc", SqlDbType.VarChar, 100); 

         SqlDataRecord record = new SqlDataRecord(metadatarec); 

         sqlpipe.SendResultsStart(record, false); 

         Boolean DirFound = false; 
         if (!(directory == null || directory.Trim().Length <= 0)) 
         { 
            directory += @"\"; 

            if ((DirFound = Directory.Exists(directory)) == true) 
            { 
               DirectoryInfo di = new DirectoryInfo(directory); 
               record.SetString(0, di.Attributes.ToString()); 
               record.SetInt64(1, di.GetFiles().Length); 
               record.SetInt64(2, di.GetDirectories().Length); 
               record.SetString(3, di.CreationTime.ToString()); 
               record.SetString(4, di.CreationTimeUtc.ToString()); 
               record.SetString(5, (di.Parent != null) ? di.Parent.FullName : ""); 
               record.SetString(6, (di.Root != null) ? di.Root.FullName : ""); 
               record.SetString(7, di.LastAccessTime.ToString()); 
               record.SetString(8, di.LastAccessTimeUtc.ToString()); 
               record.SetString(9, di.LastWriteTime.ToString()); 
               record.SetString(10, di.LastWriteTimeUtc.ToString()); 
               sqlpipe.SendResultsRow(record); 
            } 
         } 

         sqlpipe.SendResultsEnd(); 
         if (!DirFound) sqlpipe.Send(String.Format("Directory '{0}' not found.",  
             directory)); 
      } 
      catch (Exception exp) 
      { 
         throw new Exception("Error executing sqlclr_DirInfo.", exp); 
      } 
   } 
}; 

Import and then try out this method:

EXEC dbo.sqlclr_DirInfo 'c:\temp'

Equivalent to Undocumented XP 'xp_fileexist'

using System; 
using System.Data; 
using System.Data.Sql; 
using System.Data.SqlServer; 
using System.Data.SqlTypes; 

using System.IO; 

public partial class StoredProcedures 
{ 
   [SqlProcedure] 
   public static void FileExist(String filename, ref Int32 file_exists) 
   { 
      file_exists = 0; 
      try 
      { 
         SqlPipe sqlpipe = SqlContext.GetPipe(); 

         SqlMetaData[] fileexistrec = new SqlMetaData[3]; 
         fileexistrec[0] = new SqlMetaData("File Exists", SqlDbType.Int); 
         fileexistrec[1] = new SqlMetaData("File is a Directory", SqlDbType.Int); 
         fileexistrec[2] = new SqlMetaData("Parent Directory Exists", SqlDbType.Int); 

         SqlDataRecord record = new SqlDataRecord(fileexistrec); 

         sqlpipe.SendResultsStart(record, false); 

         record.SetInt32(0, 0); 
         record.SetInt32(1, 0); 
         record.SetInt32(2, 0); 
         if (!(filename == null || filename.Trim().Length <= 0)) 
         { 
            if (filename.EndsWith(@"\")) 
               filename = filename.Remove(filename.Length - 1); 

            if (File.Exists(filename)) 
            { 
               record.SetInt32(0, 1); 
               file_exists = 1; 
            } 
            else 
               record.SetInt32(0, 0); 

            if(Directory.Exists(filename)) 
               record.SetInt32(1, 1)<