How to save a blob file returned from a stored procedure to D: drive

[Elmo_loves_you]

Hi

I was hoping that somebody could help me.

I have a stored procedure that returns blob file and other data. The blob files are encrypted =) in byte form.

what I want to do now is decrypt them for the user to see but in order to do that I need to pass in a string path of where the files are stored. Has anybody any idea how I could programatically force the files returned via the Stored Procedure to save to disk...From here I could use the decryption code I have to pass in the string file paths.

Please help me - - many thanks Michelle

[Elmo_loves_you]

There is probably a very simple way to do this.....please help me.

while (reader.Read())
{
//Get the file in bytes from the reader
byte[] blobFile = reader["FileName"] as byte[];

}

What I want to do is force the blobFile to save somewhere now,

[JerryShaw]

See the attachment, there was a similar recent request for this, so I build a demo project.
You should be able to figure it out from this.

// Jerry

[Elmo_loves_you]

thanks Jerry, I found that after I created this thread....It is a very helpful little application. I have tried to implement your code into my asp.net...

but im having a little trouble getting my program to go into my while loop in order to read through the data in the reader returned by the stored procedure...the stored procedure does return the binary blob data that I want however in the code behind its telling me that the reader is null

[JerryShaw]

Show me your code including the SqlCommand

[Elmo_loves_you]

Here is the stored procedure code, the parameter for this I want to pass in via the form (hidden textbox) The correct ID is being displayed in this textbox so the correct data should definately be passed into the Sproc. For the decryption process I am trying to create two temporary files on disk which I can then use the filepath (string value) to pass into the decryption code. Its bypassing the while loop completely

ALTER PROCEDURE dbo.getEncryptedAssignments 
--Returns the encrypted assignment and pKey blob files
--based on the ID entered
	(
	@studentID int
	)

AS
--DECLARE @studentID int

SELECT     Assignment, pKey, Filename
FROM         AssignmentSubmissions
WHERE		StudentID = @studentID

	RETURN

public static byte[] ToByteArray(string s)
        {
            byte[] byteArray = new Byte[s.Length];
            for (int index = 0; index < s.Length; index++)
            {
                byteArray[index] = (byte)s[index];
            }
            return byteArray;
        }

public void getEncryptedAssignments()
        {
            //Declare an SQl Command that handles the stored procedure getEncryptedAssignments
            command = new SqlCommand("getEncryptedAssignments", myConnection);
            command.CommandType = CommandType.StoredProcedure;
            myConnection.Open();

            //Declare stored procedure parameters associated with database Assignment Table
            command.Parameters.Add(new SqlParameter("@studentID", SqlDbType.Int, 4, "StudentID"));
            command.Parameters["@studentID"].Value = txt_StudentID_RetrieveAss.Text;

            reader = command.ExecuteReader();
            byte[] blobFile;
            byte[] blobKey;
            string fName;

            string OutFile = @"d:\_DecryptedFile";
            string OutKey = @"d:\_PublicKey";

            //Create a new instance of the FileUpload class to save the encrypted files
            //to the D: drive
            //FileUpload outEncFIle = new FileUpload();
            //outEncFIle.SaveAs(OutFile);

            //Pass the file location stored in OutFile to a byte array.
            //This will be used to get the file from the database.  
            //Likewise for the key file
            //byte[] outFile = ToByteArray(OutFile);
            //byte[] blobOFile = outFile;

            //byte[] outkey = ToByteArray(OutKey);
            //byte[] blobOkey = outkey;

            //Create a filestream that creates the file at the OutFile location
            //FileStream fsFileOut = File.Create(OutFile);

            //Loop through the contents of the reader
            while (reader.Read())
            {
                Label1.Text = "GRRRRR";
                //Get the byte count by passing in null as the buffer
                int byteCount = (int)reader.GetBytes(0, 0, null, 0, 0);
                byte[] blobData = new byte[byteCount];

                //Read entire block for this row at once
                reader.GetBytes(0, 0, blobData, 0, byteCount);
                //lbl_byteCount.Text = byteCount.ToString();

                fName = reader["Filename"] as string;
                blobFile = reader["Assignment"] as byte[];

                //Get new filename and file path
                LaunchedFile = (string)reader["Filename"];
                FileInfo fi = new FileInfo(LaunchedFile); 
                //Create the new file
                FileStream fs = fi.Create();

                //Instantiate a buffer to hold the data
                byte[] DataFile = new byte[0];
                //Load the buffer
                DataFile = (byte[])reader["Assignment"];
                
                //Find the size of the buffer
                int ArraySize = new int();
                ArraySize = DataFile.GetUpperBound(0);
                fs.Write(DataFile, 0, ArraySize);
                fs.Flush();
                fs.Close();

                Launcher = new Process();                                        // Fire up a process
                Launcher.StartInfo.FileName = fi.FullName;                       // Give it the name of our file we just created
                Launcher.StartInfo.WorkingDirectory = OutFile;                   // Tell it to work in the temp directory
                 //Close and Dispose objects
            reader.Close();
            reader.Dispose();
            myConnection.Close();
}

Thank you so much Jerry for your help, its greatly appreciated (",)

[Elmo_loves_you]

have you any ideas?

[JerryShaw]

I modified your method as below.
In my File manager example, I added a button to call this method, after adding you Sql proc to use my FileRepository table.

Even with your previous code, I my reader was not null.
I suggest that you send an integer instead of text to the proc. Make sure it is a studentid that exists. The only reason the reader will be null is if there are no records returned.

public void getEncryptedAssignments()
        {
            myConnection = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True"
                    , ServerName
                    , DatabaseName)
                    );
            command = new SqlCommand("getEncryptedAssignments", myConnection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@studentID", 3);
            myConnection.Open();

            //Declare stored procedure parameters associated with database Assignment Table
            //command.Parameters.Add(new SqlParameter("@studentID", SqlDbType.Int, 4, "StudentID"));
            //command.Parameters["@studentID"].Value = 1; // txt_StudentID_RetrieveAss.Text;

            reader = command.ExecuteReader();
            //byte[] blobFile;
            //byte[] blobKey;
            //string fName;

            string OutFile = @"C:\"; //_DecryptedFile";
            //string OutKey = @"d:\_PublicKey";
            while (reader.Read())
            {
//                Label1.Text = "GRRRRR";
                //int byteCount = (int)reader.GetBytes(0, 0, null, 0, 0);
                //byte[] blobData = new byte[byteCount];
                //reader.GetBytes(0, 0, blobData, 0, byteCount);
                //fName = reader["Filename"] as string;
                //blobFile = reader["Assignment"] as byte[];
                LaunchedFile = (string)reader["Filename"];
                FileInfo fi = new FileInfo(LaunchedFile);
                FileStream fs = fi.Create();
                byte[] DataFile = new byte[0];
                DataFile = (byte[])reader["Assignment"];
                int ArraySize = new int();
                ArraySize = DataFile.GetUpperBound(0);
                fs.Write(DataFile, 0, ArraySize);
                fs.Flush();
                fs.Close();
                Launcher = new Process();                                        // Fire up a process
                Launcher.StartInfo.FileName = fi.FullName;                       // Give it the name of our file we just created
                Launcher.StartInfo.WorkingDirectory = OutFile;                   // Tell it to work in the temp directory
                Launcher.Start();
            }
            reader.Close();
            reader.Dispose();
            myConnection.Close();
        }

[Elmo_loves_you]

Jerry thank you very much :)

It opens files based on the ID but the original data is not in them. Would this be because they are not decrypted yet? What they show in the file is

d:\enc-outfile-4027820

I must also do some validation that only returns the most recent file making use of the date and time submitted!

Another wee thing too...how can I tell the file to save to the disk only and not open in a document? Because I need to be able get the string value of the path where the file is saved so that it can pass into the decryption method.

Thank you so very much for your help

you're a wee star :)

[JerryShaw]

First things first,

You need to verify that you actually have byte data in the correct columns in the table.
I have the feeling that you do not really have the file data in the table. Looks like you only have the name of the encrypted file, but not the actual file byte array. Verify you have File Data in the table.

To "not" launch the file... remove all lines containing "Launcher" , example: Launcher = new Process // should be removed since you are not going to be running this file.

On the otherhand, if you are using an external decrypter for the file, then you need to pass the complete path of the file as a startinfo parameter, so you would retain Launcher, and setup the parameters correctly.

// Jerry