I spent a bunch of time today just trying to play with the FILESTREAM option on varbinary(max) in SQL Server 2008.

There's a good intro to doing this up here but it stops short of writing code against it which is where I wanted to go.

So, I've created my database with a FILESTREAM filegroup, I've got a table with a FILESTREAM entry in it a la;

create table Photos
(
  rowid uniqueidentifier rowguidcol not null primary key default(NEWID()),
  photo varbinary(max) filestream
)

and I've read about how I'm supposed to use PathName() as a function and then use GET_FILESTREAM_TRANSACTION_CONTEXT() as a function and then feed all that into a new API (in the SQL native client library) called OpenSqlFilestream but I look in the docs and I see that the declaration in the docs;

HANDLE OpenSqlFilestream (
  LPCWSTR FilestreamPath,
  SQL_FILESTREAM_DESIRED_ACCESS DesiredAccess,
  ULONG OpenOptions,
  LPBYTE FilestreamTransactionContext,
  ULONG FilestreamTransactionContextLength
);

doesn't match the definition in the header file which is;

HANDLE __stdcall OpenSqlFilestream (
           LPCWSTR                        FilestreamPath,
           SQL_FILESTREAM_DESIRED_ACCESS  DesiredAccess,
           ULONG                          OpenOptions,
           LPBYTE                         FilestreamTransactionContext,
           SSIZE_T                        FilestreamTransactionContextLength,
           PLARGE_INTEGER                 AllocationSize);

which leads into that whole discussion of why (for me) .NET is a more productive model than C++ because in .NET I just have the assembly whereas in C++ I have the header, the .lib file and the DLL itself and if they are out of sync with each other then you can nicely waste a few hours.

I kept trying to get an interop signature to work in .NET for this function but I was getting nowhere and so I ended up writing a call to the function from C++/CLI and when that worked I tried to translate this back to the .NET world and ended up with the old fashioned (i.e. IntPtr based) version below;

        [DllImport("sqlncli10.dll", SetLastError=true, CharSet=CharSet.Unicode)]
        private extern static IntPtr OpenSqlFilestream(
            string path,
            int access,
            int openOptions,
            byte[] txnContext,
            int contextLength,
            ref int allocationSize);

which seems to work for me. Currently calling it from a fairly dodgy looking piece of code which is this (purely trying to get it to not give me back INVALID_HANDLE which I believe I've now got to);

   class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection con = new SqlConnection(
                "server=.;database=photodb;integrated security=sspi"))
            {
                con.Open();

                SqlTransaction tran = con.BeginTransaction();

                using (SqlCommand com = new SqlCommand("select photo.PathName(), " +
                    "GET_FILESTREAM_TRANSACTION_CONTEXT() from photos", con))
                {
                    com.Transaction = tran;

                    SqlDataReader reader = com.ExecuteReader();

                    while (reader.Read())
                    {
                        byte[] bits = ((byte[])(reader[1]));
                        int  len = bits.Length;
                        int allocSize = 0;

                        IntPtr h = OpenSqlFilestream(
                            (string)reader[0],
                            0,
                            0,
                            bits,
                            len,
                            ref allocSize);

                        int errno = Marshal.GetLastWin32Error();

                        Console.WriteLine(errno);

                        CloseHandle(h);
                    }
                }
                tran.Rollback();

                con.Close();

            }
        }
        [DllImport("sqlncli10.dll", CharSet=CharSet.Unicode)]
        private extern static IntPtr OpenSqlFilestream(
            string path,
            int access,
            int openOptions,
            byte[] txnContext,
            int contextLength,
            ref int allocationSize);

        [DllImport("kernel32.dll")]
        private extern static void CloseHandle(IntPtr handle);
    }

What a nice way to spend a couple of hours - at least there's a happy ending.