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

}

Author: Pouya Panahy

Microsoft certified DevOps engineer with passion in analysing, designing and implementing solutions for Azure Cloud with hands-on experience in security and quality assurence.

Leave a Reply