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();
}
