retrieve varbinary datafield from SQLdatabase

Question

i am storing files in database and this is the code i am using and it works in adding the data, but now i want to retrieve it back. how can i do that?

string filename = FileUploader.PostedFile.FileName;
    string filecontent = FileUploader.PostedFile.ContentType;
    int filesize = FileUploader.PostedFile.ContentLength;

    string filepath = System.IO.Path.GetFileName(filename);


    FileUploader.PostedFile.SaveAs("c:\\try\\" + filepath);

    byte[] fileData = new byte[FileUploader.PostedFile.ContentLength];
    FileUploader.PostedFile.InputStream.Read(fileData, 0, fileData.Length);
    string originalName = Path.GetFileName(FileUploader.PostedFile.FileName);


    con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["FAQ"].ToString();

    con.Open();

    using (SqlCommand cmd = new SqlCommand("INSERT INTO Files(FileData) VALUES (@binaryValue)", con))
    {
        // Replace 8000, below, with the correct size of the field
        cmd.Parameters.Add("@binaryValue", SqlDbType.VarBinary, fileData.Length).Value = fileData;
        cmd.ExecuteNonQuery();
        con.Close();
    }

Eric · Accepted Answer · 2015-04-14 13:40:01Z

9

You can use a SqlDataReader to obtain the value, and BinaryWriter to create the file, like so:

//Database connection code...
cmd.CommandText = "SELECT FileData FROM Files WHERE ID = @ID";
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1234;
SqlDataReader sqlRead = cmd.ExecuteReader();

string fileName = "file.txt";
string fileDir = "C:\\Test\\";
string fileUrl = "/";

if (sqlRead.HasRows)
{
    while(sqlRead.Read())
    {
        byte[] fileData = (byte[]) sqlRead[0].Value;
        BinaryWriter fileCreate = 
            new BinaryWriter(File.Open(fileDir + fileName, FileMode.Create));
        fileCreate.Write(fileData);
        fileCreate.Close();
        HttpResponse.Redirect(fileUrl + fileName);
    }
}

sqlRead.Close();

edited Apr 14, 2015 at 13:40

answered Jul 8, 2009 at 13:36

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ahmad Farid Over a year ago

thanks man. but do u mind if i added something to the question. how can i put this date in a file and download this file? thanks in advance.

Tri Nguyen Dung Over a year ago

There is an error at line byte[] fileData = (byte[]) sqlRead[0].Value; SqlDataReader doesn't have "Value" method to be called!

DrLazer Over a year ago

fileCreate.Write(fileDate) should be fileCreate.Write(fileData)

Collectives™ on Stack Overflow

retrieve varbinary datafield from SQLdatabase

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related