none
c# UI - SQL Server Filestream SQLDataReader.ExecuteReader() exception from transaction not initialized? RRS feed

  • Question

  • I am working with SQL Server FileStream. I can insert files into my table no problem but opening them is another story. At the moment the best solution I have come across is to write the queried file into a temp table then open it from there. However I encounter an exception I can't seem to figure out. Code below, exception below, thanks in advance for taking the time to look at this issue.

    string conString = ************ SqlConnection sqlCon = new SqlConnection(conString); sqlCon.Open(); if (sqlCon.State == ConnectionState.Open) { SqlTransaction txn = sqlCon.BeginTransaction(); string queryFileStream = "Select file_stream.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(), name from dbo.ImagesTable"; SqlCommand cmdProjectDatabase = new SqlCommand(queryFileStream, sqlCon); SqlDataReader projectReader; projectReader = cmdProjectDatabase.ExecuteReader(); while (projectReader.Read()) { string filePath = projectReader[0].ToString(); byte[] objContext = (byte[])projectReader[1]; string fName = projectReader[2].ToString(); SqlFileStream sfs = new SqlFileStream(filePath, objContext, System.IO.FileAccess.Read); byte[] buffer = new byte[(int)sfs.Length]; sfs.Read(buffer, 0, buffer.Length); sfs.Close(); string filename = "C:\Temp\" + fName; System.IO.FileStream fs = new System.IO.FileStream(filename, FileMode.Create, FileAccess.Write, FileShare.Write); fs.Write(buffer, 0, buffer.Length); fs.Flush(); fs.Close(); } projectReader.Close(); txn.Commit(); }

    sqlCon.Close();

    Line: projectReader = cmdProjectDatabase.ExecuteReader();

    $exception {"ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."} System.InvalidOperationException


    • Edited by CWhyte_gov Tuesday, December 19, 2017 10:20 PM Formating
    Tuesday, December 19, 2017 10:16 PM

Answers

  • Unless I'm missing something, shouldn't you set the Transaction property of the command ?

    See also


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by CWhyte_gov Wednesday, December 20, 2017 12:02 AM
    Tuesday, December 19, 2017 11:52 PM
    Moderator

All replies

  • Unless I'm missing something, shouldn't you set the Transaction property of the command ?

    See also


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by CWhyte_gov Wednesday, December 20, 2017 12:02 AM
    Tuesday, December 19, 2017 11:52 PM
    Moderator
  • Yep, thanks a ton.
    Wednesday, December 20, 2017 12:02 AM
  • Your welcome :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, December 20, 2017 12:16 AM
    Moderator