Writing File to Database Feild.

[cutamacious]

I want write a binary format file onto a feild in SQL server. I'm unable to accomplish this. Any help / Ideas????

However, I am able to write the file to remote Fileshare on the Network. I mapped the Share as NetworkDrive. Now I am writing the file as usual using

FileStream streamWriter = File.Create(filepath);
streamWriter.Write(data, 0, size);

and it writes fine and retrieves fine. But I want this in the Database instead of as a file in the network share.

[umilmi81]

I personally have never stored a file in a database. But I have heard of the practice. I disagree with it for various reasons, but it does seem to be a popular option in some circumstances.

Here is how you do it. First you have to define a field in your database as "blob". If it's not blob you wont be able to store a file in it.

Following is the code to put the file into the database and then the code to remove it from the database. Both code sets were lifted from this site. http://builder.com.com/5100-6371_14-5766889.html

//put your file in database
SqlConnection conn =null;
SqlCommand cmd = null;
SqlParameter param = null;
FileStream fs = null;
const string sConn = "server=(local);Initial  Catalog=Northwind;UID=ctester;PWD=password";
try {
  conn = new SqlConnection(sConn);
  cmd = new SqlCommand("UPDATE Categories SET Picture = @Picture WHERE
                                    CategoryName = 'Seafood'", conn);
  fs = new FileStream("c:\\Builder.doc", FileMode.Open, FileAccess.Read);
  Byte[] blob = new Byte[fs.Length];
  fs.Read(blob, 0, blob.Length);
  fs.Close();
  param = new SqlParameter("@Picture", SqlDbType.VarBinary, blob.Length,
  ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, blob);
  cmd.Parameters.Add(param);
  conn.Open();
  cmd.ExecuteNonQuery();
} catch (SqlException e){
  Console.Write("SQL Exception: " + e.Message());
} catch (Exception e) {
  Console.Write("Exception: " e.Message());
} 




//Get your file back out

Byte[] blob = null;
FileStream fs = null;
const string sConn = "server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password";
try {
  SqlConnection conn = new SqlConnection(sConn);
  SqlCommand cmd = new SqlCommand("SELECT Picture FROM Categories WHERE
                                                      CategoryName='Builder'", conn);
  cn.Open();
  SqlDataReader sdr = cmd.ExecuteReader();
  sdr.Read(); 

  blob = new Byte[(sdr.GetBytes(0, 0, null, 0, int.MaxValue))];
  sdr.GetBytes[0, 0, blob, 0, blob.Length);
  sdr.Close();
  conn.Close();
  fs = new FileStream("c:\\Builder.doc", FileMode.Create, FileAccess.Write); 

  fs.Write(blob, 0, blob.Length);
  fs.Close();
} catch (SqlException e){
  Console.WriteLine("SQL Exception: " + e.Message);
} catch (Exception e) {
  Console.WriteLine("Exception: "+ e.Message);
}