none
Optimize code to insert data in FileStream RRS feed

  • Question

  • Hello,

    I have written the following code to insert files into a database FILESTREAM column

    What I don't like about this code is that the entire contents of the file is loaded into a byte array before being passed to the SQL Parameter. I have a feeling that if my files are large, then this will caluse an out-of-memory exception in my code.


    	DirectoryInfo dinfo = new DirectoryInfo(@"c:\temp");
    	FileInfo[] finfo = dinfo.GetFiles("*.exe");
    	foreach(FileInfo fi in finfo)
    	{
    		FileStream fs = fi.OpenRead();	
    		byte[] data = new byte[fs.Length];
    		fs.Read(data, 0, (int)fs.Length);
    		SqlConnection con = new SqlConnection(@"server=.\SQLEXPRESS;Integrated Security=true;database=Archive");					
    		con.Open();
    		SqlCommand cmd = new SqlCommand("insert into Records (id, filedata) values (newid(), @filedata)", con);
    		SqlParameter p1 = new SqlParameter("@filedata", SqlDbType.VarBinary);
    		p1.Value = data;
    		cmd.Parameters.Add(p1);
    		cmd.ExecuteNonQuery();
    		con.Close();
    	}
    


    But since I have to pass the entire value as SQLParameter , I cannot think of another way.
    Friday, March 12, 2010 10:53 AM

Answers

All replies

  • I believe that's the only way how you could pass parameter value to the provider. It does not allow to pass only the file name or reference to the stream itself. If you know expected size of the files, I would recommend to do sort of stress test to see how application performs under certain load level.
    Val Mazur (MVP) http://www.xporttools.net
    Friday, March 12, 2010 11:21 AM
    Moderator
  • This is funny because when reading filestream always require you to start a transaction and then use the SqlFileReader API... I wonder there can't be a SqlFileWriter API which allows me to write the file byte by byte and then commit the transaction.


    Saturday, March 13, 2010 7:19 PM
  • Actually the old method of updating BLOB data involved using the UPDATETEXT and READTEXT SQL commands to process data in chunks. You should be able to use this method if you're concerned about excessive memory usage. I wasn't able to find a C# example, but there is a VB example available that you shouldn't have any trouble following:

    HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual Basic .NET



    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by MSDN Student Monday, March 15, 2010 7:20 AM
    Sunday, March 14, 2010 12:53 AM