How to insert a file into a filestream data type

Question

I am creating an application to store Excel files into a database using the filestream data type provided by SQL Server 2008, and now I'm stuck searching the internet for the best practice way to insert it using a stored procedure from C#.

So far, I've created the database structure and the classes, what I need to do now is to actually use the stored procedure and I'm stuck, here's the snippet of code

OpenFileDialog ofd = new OpenFileDialog();
ofd.ShowDialog();

if (ofd.CheckFileExists)
{
    ....            
}

using (SqlConnection conn = new SqlConnection(Murel.Util.DBUtil.CONSTRING))
{
    try
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand("items_insert", conn))
        {
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(new SqlParameter("@name", "test"));
           cmd.Parameters.Add(new SqlParameter("@template", HELP));

           Guid id = (Guid)cmd.ExecuteScalar();

           return true;
        }
     }
     catch (Exception ex)
     {
        throw ex;
     }
     finally
     {
         conn.Close();
     }
  }

The data table consist of an id which is an unique identifier, name and template which is varbinary(max), I already have anything else set up, I just need to know what to put in

cmd.Parameters.Add(new SqlParameter("@template", HELP));

thx,

darius

Read [here](http://stackoverflow.com/questions/766926/filestream-in-sql-server-and-c-sharp-for-aspx), it's a similar question. — Matteo Migliore, Jun 10 '12 at 20:27
What does the stored procedure look like? What's template's definition in SQL? — zmbq, Jun 10 '12 at 20:28
Totally unrelated, but just fyi, you should almost always use throw instead of throw ex. See http://stackoverflow.com/questions/730250/is-there-a-difference-between-throw-and-throw-ex/730255#730255 — Kenneth Ito, Jun 10 '12 at 20:32
template is the varbinary(max)/ the filestream, the stored procedure is just a plain old insert stored procedure — Darius Suwardi, Jun 10 '12 at 20:36
@matteo i did open that question, but the answer is a link to another web, which cant be opened now... — Darius Suwardi, Jun 10 '12 at 20:39
Just to be nitpicky: `FILESTREAM` is not really a new data type that SQL Server 2008 introduced - it's merely an additional attribute on the `VARBINARY(MAX)` datatype. — marc_s, Jun 10 '12 at 20:52
i understand that it is the same type, but from what i read, if the data is "large" using the easy way to insert the data will result in poor performance on the database... — Darius Suwardi, Jun 10 '12 at 20:54

score 1 · Answer 1 · answered Jun 11 '12 at 09:53

1

Try this, this should work

byte[] fileContent = new byte[ofd.PostedFile.ContentLength];
ofd.PostedFile.InputStream.Read(fileContent, 0,ofd.PostedFile.ContentLength);
command.Parameters.Add("@FileContent", SqlDbType.VarBinary, -1).Value = fileContent;

ofd is your FileUpload Control

answered Jun 11 '12 at 09:53

Jayant Bramhankar

368
3
12

i dont exactly have fileupload coz this is a win apps – Darius Suwardi Jun 24 '12 at 10:12

How to insert a file into a filestream data type

1 Answers1