Sql Server Filestream using C#

1) WRITE

            using (TransactionScope transactionScope = new TransactionScope())
            {
             
                //1. create Record with Empty Blob (File)
                SqlConnection sqlConnection1 = new SqlConnection(conStr);
                sqlConnection1.Open();

                SqlCommand sqlCommand1 = sqlConnection1.CreateCommand();
                sqlCommand1.CommandText = @"DECLARE @Out TABLE (ID uniqueidentifier)
                                            Insert Into Blob2(Blob) output inserted.ID into @Out values(Cast('' As varbinary(Max)))
                                            Select BLOB.PathName() As Path From BLOB2 Where Id =(SELECT id FROM  @Out)";
                string filePath1 = (string) sqlCommand1.ExecuteScalar();

                //2. Get SqlFileStream to empty File createt in Step 1
                SqlConnection sqlConnection2 = new SqlConnection(conStr);
                sqlConnection2.Open();

                SqlCommand sqlCommand2 = sqlConnection2.CreateCommand();
                sqlCommand2.CommandText = "Select GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext ";
                byte[] transactionContext1 = (byte[]) sqlCommand2.ExecuteScalar();

                //3. Write Data into Stream and Close
                SqlFileStream sqlFileStream1 = new SqlFileStream(filePath1, transactionContext1, FileAccess.Write);
                byte[] fileData = Encoding.ASCII.GetBytes(nameof(AdoFileStreamTest)+" "+DateTime.Now);
                sqlFileStream1.Write(fileData, 0, fileData.Length);
                sqlFileStream1.Close();
                transactionScope.Complete();
            }

2) READ

            using (TransactionScope transactionScope2 = new TransactionScope())

            {

                SqlConnection sqlConnection3 = new SqlConnection(conStr);

                sqlConnection3.Open();

                //1. Get Path & Transaction Scope

                SqlCommand sqlCommand3 = sqlConnection3.CreateCommand();

                sqlCommand3.CommandText = @"Select Top 1 Blob.PathName() As Path,

                                            GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext

                                            From Blob2 Order by Created desc";

                

                SqlDataReader reader = sqlCommand3.ExecuteReader();

                reader.Read();

                string filePath = (string)reader["Path"];

                byte[] transactionContext2 = (byte[])reader["TransactionContext"];

                //2. read file

                SqlFileStream sqlFileStream2 = new SqlFileStream(filePath, transactionContext2, FileAccess.Read);

                byte[] data = new byte[sqlFileStream2.Length];

                sqlFileStream2.Read(data, 0, Convert.ToInt16(sqlFileStream2.Length));

                res = Encoding.ASCII.GetString(data);

                sqlFileStream2.Close();

            }

3) maybe need to config Distributet Transaction Manager:

https://lennilobel.wordpress.com/2011/08/22/using-sqlfilestream-in-c-to-access-sql-server-filestream-data/

down voteaccepted

To enable MSDTC on the business management server that is running on Windows Server 2008 click Start, Run, type dcomcnfg and then click OK to open Component Services. In the console tree, click to expand Component Services, click to expand Computers, click to expand My Computer, and click to expand Distributed Transaction Coordinator. Right click Local DTC, and click Properties to display the Local DTC Properties dialog box. Switch to the Security tab. In the Security Settings section, click Network DTC Access. In the Client and Administration section, select Allow Remote Clients and Allow Remote Administration. In the Transaction Manager Communication section, select Allow Inbound and Allow Outbound. In the Transaction Manager Communication section, select Mutual Authentication Required (if all remote machines are running Windows Server 2003 SP1 or Windows XP SP2 or higher), select Incoming Caller Authentication Required (if running MSDTC in a cluster), or select No Authentication Required if some of the remote machines are pre-Windows Server 2003 SP1 or pre-Windows XP SP2. No Authentication Required is the recommended selection. Select Enable XA Transactions, and then click OK.