Visual Studio 2008 SP1: SqlFileStream – Managed API for SQL Server 2008 FILESTREAM

12 במאי 2008

no comments

Visual Studio 2008 SP1: SqlFileStream – Managed API for SQL Server 2008 FILESTREAM


logo2_SQL_2 In the previous post about SQL 2008 FILESTREAM I created a sample database and added a text file using sql commands. In this post I will build a simple .net client application that adds a file to the database and than reads it. To do that, I will go over a code block that I've written and explain it. You can download the full source code project for this post.


Some related posts about SQL Server 2008:


  • SQL Server 2008 FILESTREAM – Part 1

  • SqlFileStream – Managed API for SQL Server 2008 FILESTREAM

  • LINQ to SQL with SQL Server 2008 Date Time Types

  • LINQ to SQL and FILESTREAM

  • SQL Server 2008 IntelliSense

  • SQL Server 2008 T-SQL: DECLARE and SET in the Same Statement

  • SQL Server 2008 T-SQL: Insert Multiple Rows

  • SQL Server 2008 T-SQL: MERGE Statement

  • Insert a New File


    1. Create and open a new connection to the database:



    string connectionString = @"Data Source=.;Initial Catalog=FileManagement;Integrated Security=True";


    SqlConnection conn = new SqlConnection(connectionString);


    conn.Open();


    2. Begin a new Transaction. Since writing the row to the table and saving the file to the file system should be a single atomic operation, all the following operations should be inside a transaction.



    SqlTransaction tx = conn.BeginTransaction();


    3. Add a new record to the Files table.



    SqlCommand insertFileCommand = conn.CreateCommand();


    insertFileCommand.Transaction = tx;


    insertFileCommand.CommandText = "INSERT INTO Files (FileID) VALUES (@FileID)";


    Guid newFileID = Guid.NewGuid();


    insertFileCommand.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = newFileID;


    insertFileCommand.ExecuteNonQuery();


    4. After the row has been added to the table, we can select the path name of the newly added file and later write to it. Another thing we have to get is the FileStream Transaction Context (we do that by using the function get_filestream_transaction_context()).



    SqlCommand getPathAndTokenCommand = conn.CreateCommand();


    getPathAndTokenCommand.Transaction = tx;


    getPathAndTokenCommand.CommandText =
        "SELECT FileContents.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() " +
        "FROM   Files " +
        "WHERE  FileID = @FileID"
    ;


    getPathAndTokenCommand.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = newFileID;



    SqlDataReader tokenReader = getPathAndTokenCommand.ExecuteReader(CommandBehavior.SingleRow);


    tokenReader.Read();


    SqlString filePathName = tokenReader.GetSqlString(0);


    SqlBinary fileToken = tokenReader.GetSqlBinary(1);


    tokenReader.Close();


    5. Copy the contents of a file in the file system to the sql file using SqlFileStream class which is new in Service Pack 1 of Visual Studio 2008 and .Net Framework 3.5. The SqlFileStream is found in the System.Data.SqlTypes namespace, and inherits from the Stream class which means that using it is very similar to any other stream.


    When constructing a new instance of SqlFileStream, we pass the pass name and FileStream Transaction Context that we got after inserting the new row into the table.



    FileStream inputFile = File.OpenRead("TextFile1.txt");


    SqlFileStream sqlFile = new SqlFileStream(filePathName.Value, fileToken.Value, System.IO.FileAccess.Write);


    byte[] buffer = new byte[512 * 1024]; // 512Kb


    int bytesRead = inputFile.Read(buffer, 0, buffer.Length);


    while (bytesRead > 0)


    {


        sqlFile.Write(buffer, 0, bytesRead);


        bytesRead = inputFile.Read(buffer, 0, buffer.Length);


    }


    Writing into an SqlFile is very similar to the way we would use any other stream class, and we do that by using a buffer that we read into it and write from it.


    6. Just close all open connections and resources.



    sqlFile.Close();


    inputFile.Close();


    tx.Commit();


    conn.Close();


    Read File Contents


    Reading file's contents is very similar to adding a new file, except from using the Read method instead of the Write method. Just to make things more clear – here's how you do it.


    1. Create and open a new connection to the database:



    string connectionString = @"Data Source=.;Initial Catalog=FileManagement;Integrated Security=True";


    SqlConnection conn = new SqlConnection(connectionString);


    conn.Open();


    2. Begin a new Transaction.



    SqlTransaction tx = conn.BeginTransaction();


    3. Get the path name and token of the file.



    SqlCommand getPathAndTokenCommand = conn.CreateCommand();


    getPathAndTokenCommand.Transaction = tx;


    getPathAndTokenCommand.CommandText =
        "SELECT FileContents.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() " +
        "FROM   Files " +
        "WHERE  FileID = @FileID"
    ;


    getPathAndTokenCommand.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = newFileID;



    SqlDataReader tokenReader = getPathAndTokenCommand.ExecuteReader(CommandBehavior.SingleRow);


    tokenReader.Read();


    SqlString filePathName = tokenReader.GetSqlString(0);


    SqlBinary fileToken = tokenReader.GetSqlBinary(1);


    tokenReader.Close();


    4. Copy the contents of the SqlFile to a new file in the file system.




    FileStream outputFile = File.OpenWrite("TextFile2.txt");


    SqlFileStream sqlFile = new SqlFileStream(filePathName.Value, fileToken.Value, System.IO.FileAccess.Read);


    byte[] buffer = new byte[512 * 1024]; // 512Kb


    int bytesRead = sqlFile.Read(buffer, 0, buffer.Length);


    while (bytesRead > 0)


    {


        outputFile.Write(buffer, 0, bytesRead);


        bytesRead = sqlFile.Read(buffer, 0, buffer.Length);


    }


    5. Cleanup.



    sqlFile.Close();


    outputFile.Close();


    tx.Commit();


    conn.Close();


    Summary


    Writing and Reading from a file stored in SQL Server 2008 make easy using the SqlFileStream class in Visual Studio 2008 and .Net Framework 3.5 SP1. After the file row exists in the database, we query for the path name and token, and then use the SqlFileStream just as we would use any other stream.


    Enjoy!

    Add comment
    facebook linkedin twitter email