CODEWITHSUNDEEP
c#.netsqlsql-serverfilestream
Sam
Sam

Reputation: 586

SqlFileStream - insert without producing a null file

I am using SqlFileStream to store files in a database, however I have a small issue when inserting new records that I can't seem to resolve! The problem lies with the fact that I need to create a temporary file when inserting a record to provide a file path to stream the actual filedata to. I was expecting the temporary file to be overwritten when the actual data is streamed to the file path but it actually adds another file - leaving the null file in the file system.

Here is the code (please ignore the query string format and the way I am inserting the parameters, I have been hacking away trying different things and have lost the will to code nicely ;) ):

using (TransactionScope transactionScope = new TransactionScope())
        {
            string InsertTSql = "Insert Into Documents(file_name, file_type, category, uploaded_by, file_data) values('" + request.fileInfo.fileName  + "', '" + request.fileInfo.fileType + "', '" + request.fileInfo.category  + "', '" + request.fileInfo.uploadedBy  + "',Cast('' As varbinary(Max))); Select file_data.PathName() As Path From Documents Where document_id = @@Identity";
            SqlConnection dbConnection = new SqlConnection(@"Data Source=SAM\SQLEXPRESS;Initial Catalog=PIRS;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework");//ConfigurationManager.ConnectionStrings["PIRSDBCon"].ToString()) )
            var cmd = new SqlCommand(InsertTSql, dbConnection);
            dbConnection.Open();
            string filePath = (string)cmd.ExecuteScalar();

            string GetTcTSql = "Select GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext";
            cmd = new SqlCommand(GetTcTSql, dbConnection);
            byte[] transactionContext =(byte[]) cmd.ExecuteScalar();

            SqlFileStream sqlFileStream = new SqlFileStream(filePath, transactionContext, FileAccess.Write);
            request.fileData.CopyTo(sqlFileStream);
            sqlFileStream.Close();
            transactionScope.Complete();
        }

Please just let me know if I can add further information or more clarity regarding the issue.

Upvotes: 3

Views: 1212

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

The filestream system uses a garbage collector - every change always generates a new file, and then eventually the old file is deleted. But that doesn't happen instantly.

See e.g. How It Works: FileStream Garbage Collection or How It Works: File Stream the Before and After Image of a File for some discussion of the mechanics behind this.

Upvotes: 3

Related Questions