Writing UDFs for Firebird Embedded SQL Server

using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Data;

using FirebirdSql.Data.FirebirdClient;

namespace MainApp
{
    public class SampleProvider : IDisposable
    {
        private FbConnection connection;

        private delegate void MessageCallbackDelegate([MarshalAs(UnmanagedType.LPStr)] string message);
        private static MessageCallbackDelegate messageCallback;
        private static MessageCallbackDelegate errorCallback;
        [DllImport("udf/SampleUdf")]
        private static extern void RegisterCallbacks(MessageCallbackDelegate messageCallback, MessageCallbackDelegate errorCallback);

        static SampleProvider()
        {
            messageCallback = MessageCallback;
            errorCallback = ErrorCallback;
            RegisterCallbacks(messageCallback, errorCallback);
        }

        public static void MessageCallback(string message)
        {
            Trace.WriteLine(message, "Message");
        }
        public static void ErrorCallback(string message)
        {
            Trace.WriteLine(message, "Error");
        }

        public SampleProvider(string dbPath)
        {
            string conString = "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=" + dbPath;
            FbConnection.CreateDatabase(conString); //creating blank database
            File.Move(dbPath, dbPath); //small fix - FbConnection always create uppercase filename
            this.connection = new FbConnection(conString);
            this.connection.Open();
        }

        public void Run()
        {
            this.CreateDatabaseStructure();
            this.FillRawData();
            this.TransferData();
            this.GetParsedData();
        }

        private void CreateDatabaseStructure()
        {
            using (Stream resourceStream = this.GetType().Assembly.GetManifestResourceStream("MainApp.batch.sql"))
            {
                using (StreamReader reader = new StreamReader(resourceStream))
                {
                    string content = reader.ReadToEnd();
                    foreach (string block in content.Split('#'))
                    {
                        if (block != string.Empty)
                        {
                            using (FbCommand command = this.connection.CreateCommand())
                            {
                                command.CommandText = block;
                                command.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }

        private void FillRawData()
        {
            int count = 0;
            DirectoryInfo directoryInfo = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
            foreach (FileInfo fi in directoryInfo.GetFiles())
            {
                using (MemoryStream memStream = new MemoryStream())
                {
                    using (BinaryWriter writer = new BinaryWriter(memStream))
                    {

                        writer.Write(Encoding.ASCII.GetBytes(fi.FullName));
                        writer.Write((byte)0); //for 0-based string
                        writer.Write(fi.CreationTime.ToFileTime());
                        writer.Write((int)fi.Attributes);
                        writer.Write(fi.Length);
                        //MemoryStream contains buffer larger than writen
                        byte[] buffer = new byte[memStream.Position];
                        Array.Copy(memStream.GetBuffer(), buffer, memStream.Position);

                        using (FbCommand command = this.connection.CreateCommand())
                        {
                            command.CommandText = "AddRawData";
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@Data", FbDbType.Binary).Value = buffer;
                            command.ExecuteNonQuery();
                        }
                        count += 1;
                    }
                }
            }
            Console.WriteLine("Added {0} raw records", count);
        }

        private void TransferData()
        {
            int count = 0;
            using (FbCommand command = this.connection.CreateCommand())
            {
                command.CommandText = "TransferData";
                command.CommandType = CommandType.StoredProcedure;
                count = (int)command.ExecuteScalar();
            }
            Console.WriteLine("Transferred {0} records", count);
        }

        private void GetParsedData()
        {
            Console.WriteLine("Parsed data:");
            using (FbCommand command = this.connection.CreateCommand())
            {
                command.CommandText = "select * from FSTable";
                using (FbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}"
                            , reader["FileName"]
                            , reader["FullPath"]
                            , reader["CreationTime"]
                            , reader["Attributes"]
                            , reader["FileSize"]
                            );
                    }
                }
            }

        }

        #region IDisposable Members

        public void Dispose()
        {
            this.connection.Dispose();
        }

        #endregion
    }
}