// Contains the delegate used for the "read file" event that creates
// a stream to the external file and the delegate that registers 
// with the "read complete" event to close the stream.  
public class ExternalFileHandler
{
    // the stream that will be used to read the file that contains 
    // the stored procedure
    FileStream _internalStream;
            
    // Delegate that is invoked when the TdConnection.OpenFile 
    // event gets invoked by the provider
    public void OnFile(Object sender, TdSourceFileEventArgs eventArgs)
    {
        String FileName = eventArgs.FileName;
            
        // creating a stream to the file.
        _internalStream = new FileStream(FileName, FileMode.Open);
            
        // sending stream back to provider
        eventArgs.ExternalFileObject = _internalStream;
    }
             
    // Delegate that is invoked when the TdConnection.CloseFile
    // event gets invoked by the provider.
    public void OnCloseFile(Object sender, TdSourceFileEventArgs eventArgs)
    {
        _internalStream.Close();
    }
 }
            
            
public void CreateExternalProcedure(TdConnection cn)
{
    TdCommand cmd = cn.CreateCommand();
            
    // Creating the type that contains the callbacks that will be used by the 
    // ExternalFile and ExternalFileReadCompleted events
    ExternalFileHandler fh = new ExternalFileHandler();
            
   cmd.CommandText = "CREATE PROCEDURE provider_sp(INOUT region VARCHAR(64)) " +
                      "LANGUAGE C NO SQL EXTERNAL NAME 'CS!provider_sp!c:\xsp.c!F!provider_sp' " +
                      "PARAMETER STYLE SQL";
            
   // creating delegates for the OpenFile and CloseFile events
   TdOpenFileEventHandler extFile = new TdOpenFileEventHandler(fh.OnFile);
   TdCloseFileEventHandler extReadCompleted = 
            new TdCloseFileEventHandler(fh.OnCloseFile);
            
    // this event will get raised when Teradata requests the source code of the 
    // stored procedure.  Registration to this event is required if a DDL is 
    // executed that specifies data is to be read from a file that resides
    // on the client machine.
    cn.OpenFile += extFile;
            
    // this event will get raised after Teradata has received all the source code 
    // for the stored procedure.  Registering to this event is optional.
    cn.CloseFile += extReadCompleted;
            
    try
    {
        // going to read through the compiler messages sent from Teradata
        using (TdDataReader dr = cmd.ExecuteReader())
        {
            String result;
            
            while (dr.Read() == true)
            {
                result = dr.GetString(0);
            
                Console.WriteLine(result);
            }
        }
    }
    finally
    {
        // need to remove delegates from the events.
        cn.OpenFile -= extFile;
        cn.CloseFile -= extReadCompleted;
    }
}