0

First of all I'm using a FileStream with the server. The server is SQL Server express 2014. I configured the database and table correctly (I hope) and I was able to upload an image as a varbinary, but when I try to download that image I get an error

An invalid parameter was passed to the function.

Here is the database structure

Records(
    [id] [uniqueidentifier] ROWGUIDCOL  NOT NULL UNIQUE,
    [Name] [varchar](64) NULL,
    [Clip] [varbinary](max) FILESTREAM  NULL,
)

And the code for downloading the image

private object GetTransactionContext()
{
   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
   cmd.CommandType = CommandType.Text;
   cmd.Connection = sql_Connection;

   return cmd.ExecuteScalar();
}

private void BeginTransaction()
{
   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "BEGIN TRANSACTION";
   cmd.CommandType = CommandType.Text;
   cmd.Connection = sql_Connection;

   cmd.ExecuteScalar();
}

private void CommitTransaction()
{
   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "COMMIT TRANSACTION";
   cmd.CommandType = CommandType.Text;
   cmd.Connection = sql_Connection;

   cmd.ExecuteScalar();
}

//Command for reading the data
public async void ReadFromDatabase(string Name)
{
   //enter the command parameter
   sql_Command_Read.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;

   //open a connection to the server
   sql_Connection.Open();

   this.BeginTransaction();

   //get the path to the BLOB object
   string filePath = null;

   Object pathObj = sql_Command_Read.ExecuteScalar();

   if (DBNull.Value != pathObj) 
   { 
      filePath = (string)pathObj; 
   }
   else
   {
      throw new NotImplementedException();
   }

   Object obj = this.GetTransactionContext();
   byte[] sql_TransactionToken = (byte[])obj;

   sql_FileStream = new SqlFileStream(filePath, sql_TransactionToken, FileAccess.ReadWrite, FileOptions.SequentialScan, 0);

   byte[] buffer = new byte[(int)sql_FileStream.Length];

   sql_FileStream.Seek(0L, SeekOrigin.Begin);
   sql_FileStream.Read(buffer, 0, buffer.Length);

   File.WriteAllBytes("C:\test.mp4", buffer);

   this.CommitTransaction();
 }

The content of the command is 

sql_Command_Read.CommandText = "SELECT Picture.PathName() FROM Archive.dbo.Records WHERE Name = @Name";

Again I am new to database and sql client programming. Now about the code. The error that I get is on this line:

sql_FileStream = new SqlFileStream(filePath, sql_TransactionToken, FileAccess.ReadWrite, FileOptions.SequentialScan, 0);

and about the parameters here are the values they get:

filePath = "\\\\LAPTOP-PC\\VIDEOPRESENTERDB\\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\\Archive\\dbo\\Records\\Picture\\C94D4189-9ECF-448B-B05A-ABF9331BF6CE\\VolumeHint-HarddiskVolume2"

obj has 16 numbers ranging from 0 to 255.

I am obviously making a mistake somewhere but I don't know where exactly

Uğur Aldanmaz
  • 1,018
  • 1
  • 11
  • 16
EnvelopedDevil
  • 658
  • 1
  • 13
  • 29
  • I think it's because of the \ in your path string. either use \\ or precede with @ (verbatim string literal) – Codeek Jan 01 '15 at 14:17
  • @Codeek I will try that in a moment. Note that I don't do anything to the path string I use it as I get it from the database – EnvelopedDevil Jan 01 '15 at 14:19
  • 1
    Your code is really strange, why you are executing scalar in BeginTransaction, Commit. You are giving new sqlCommand in this methods. You are executing Execute Scallar again in other place. Check how it is working with transaction -> http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope%28v=vs.110%29.aspx – mybirthname Jan 01 '15 at 14:20
  • @Codeek I don't know but the code visualization on the site removes transforms '\\' into '\'. I looked at it again and there is not a single slash they are always two or four – EnvelopedDevil Jan 01 '15 at 14:24
  • @mybirthname I do not know which is the proper way to do it actually. I tried reading some MSDN articles but there is no clear example with only reading and saving the data – EnvelopedDevil Jan 01 '15 at 14:25
  • Your BeginTransaction and CommitTransaction functions don't do what you think/hope they do. – Sam Axe Jan 01 '15 at 14:26
  • @Dan-o then what are they doing exactly and what is the proper way to do it – EnvelopedDevil Jan 01 '15 at 14:26
  • @mybirthname I cannot find the TrasactionScope class. VS asks me if I want it to generate the class or type for me. – EnvelopedDevil Jan 01 '15 at 14:31
  • http://stackoverflow.com/questions/26583432/does-sqlbulkcopy-automatically-start-a-transaction/26583508#26583508 here in this question I show other approach with transcation for SqlBulkCopy, I think you can learn somehing of it, but better check other links. Also this link http://msdn.microsoft.com/en-us/library/86773566(v=vs.110).aspx. Also the link of @Dan-o is good. I advice you to read them and try to rebuild your code, after that if you have another problem ask another question. First try to build easy query with Transactions, just for the test. – mybirthname Jan 01 '15 at 15:07

0 Answers0