Read/Write files from/to an Image field in SQL-Server

In this example I have a table called Attachment having a field of time Image which holds binary value. In this field I am going to put any type of file in a byte array and read it back. I have defined a class called Attachment to read and write the records. This class has a variable called Data which is defined as byte[]

Read Image Field

This example passes a reference to an object called record of type Attachment. The record object has an attribute called Data where the attachment file will be streamed into it.

/// <summary>
/// Read a TestLog record identified by the given parameter
/// </summary>
/// <param name="id">the record key</param>
/// <returns>a Attachment object that maps to the record</returns>
public static void Read(Attachment record)
{


    if (record.Id < 1)
        throw new ApplicationException("Invalid Record ID");

    using (SqlConnection sqlConnection1 = Connection)
    {
        SqlCommand cmd = sqlConnection1.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT TestLogId, Name, Description, CreatedBy, CreateDate, Data FROM Attachment WHERE ID=@Id";

        SqlParameter par = new SqlParameter("@Id", SqlDbType.Int);
        par.Value = record.Id;
        cmd.Parameters.Add(par);


        sqlConnection1.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (!dr.HasRows)
            throw new ApplicationException(String.Format("Could not find TestLog '{0}'.", record.Id));

        dr.Read();

        if (!dr.IsDBNull(0))
            record.TestLogID = dr.GetInt32(0);
        if (!dr.IsDBNull(1))
            record.Name = dr.GetString(1).TrimEnd();
        if (!dr.IsDBNull(2))
            record.Description = dr.GetString(2).TrimEnd();
        if (!dr.IsDBNull(3))
            record.CreatedBy = dr.GetString(3).TrimEnd();
        if (!dr.IsDBNull(4))
            record.CreateDate = dr.GetDateTime(4);

        // Read the bytes into the Data attribute of the record
        int PictureCol = 5; // the column # of the BLOB field                    
        record.Data = new Byte[(dr.GetBytes(PictureCol, 0, null, 0, int.MaxValue))];
        dr.GetBytes(PictureCol, 0, record.Data, 0, record.Data.Length);
     
        dr.Close();
        sqlConnection1.Close();
    }
}

Write File into the record

/// <summary>
/// Save the Attachment record. If Id doesn't exist, it will create a new one, otherwise it will update.
/// </summary>
/// <param name="record">the attachment record to save</param>
public static void Save(Attachment record)
{

    using (SqlConnection sqlConnection1 = Connection)
    {
        SqlCommand cmd = sqlConnection1.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SaveAttachment";

        #region Add the input parameter and set its value

        SqlParameter par = new SqlParameter("@TestLogId", SqlDbType.Int);
        par.Value = record.TestLogID;
        cmd.Parameters.Add(par);

        par = new SqlParameter("@Name", SqlDbType.NVarChar, 128);
        par.Value = record.Name;
        cmd.Parameters.Add(par);

        par = new SqlParameter("@Description", SqlDbType.NVarChar, 320);
        par.Value = record.Description;
        cmd.Parameters.Add(par);

        par = new SqlParameter("@Data", SqlDbType.VarBinary, record.Data.Length);
        par.Value = record.Data;
        cmd.Parameters.Add(par);

        #endregion

        // Add the output parameter 
        par = new SqlParameter("@Id", SqlDbType.Int, 4);
        par.Direction = ParameterDirection.InputOutput;
        par.Value = record.Id;
        cmd.Parameters.Add(par);

        sqlConnection1.Open();
        cmd.ExecuteNonQuery();
        int id = Int32.Parse(cmd.Parameters["@ID"].Value.ToString());
        record.Id = id;
        sqlConnection1.Close();
    }
}

Read the file into the variable

/// <summary>
/// Read the file into the Data field
/// </summary>
/// <param name="path">A valid path to the file</param>
public void ReadFromFile(string path)
{
    System.IO.FileStream fs =
        new System.IO.FileStream(path, System.IO.FileMode.Open, System.IO.FileAccess.Read);

    this.data = new Byte[fs.Length];
    fs.Read(this.data, 0, this.data.Length);
    fs.Close();
}

Write the byte array to a File

This simple method is using the name of the file as is stored in the attachment record to save in the location specified by parameter.

/// <summary>
/// Save the content into a file
/// </summary>
/// <param name="path">the full path to write to</param>
public void SaveToFile(string path)
{
    if (path.EndsWith("\\"))
        path += "\\";

    string DestFilePath = path + this.name;
    System.IO.FileStream fs =
             new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);

    fs.Write(this.data, 0, this.data.Length);
    fs.Close();

}