{"id":113,"date":"2007-08-21T21:34:00","date_gmt":"2007-08-21T21:34:00","guid":{"rendered":"https:\/\/wdev-blog.azurewebsites.net\/index.php\/2007\/08\/21\/read-write-files-from-to-an-image-field-in-sql-server\/"},"modified":"2007-08-21T21:34:00","modified_gmt":"2007-08-21T21:34:00","slug":"read-write-files-from-to-an-image-field-in-sql-server","status":"publish","type":"post","link":"http:\/\/panahy.nl\/index.php\/2007\/08\/21\/read-write-files-from-to-an-image-field-in-sql-server\/","title":{"rendered":"Read\/Write files from\/to an Image field in SQL-Server"},"content":{"rendered":"

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[]<\/p>\n

<\/p>\n

Read Image Field<\/h3>\n

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.<\/span><\/p>\n

<\/p>\n

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


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

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

        SqlParameter par <\/span>=<\/span> <\/span>new<\/span> SqlParameter(<\/span>\"<\/span>@Id<\/span>\"<\/span>, SqlDbType.Int);
        par.Value <\/span>=<\/span> record.Id;
        cmd.Parameters.Add(par);


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

        dr.Read();

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

        <\/span>\/\/<\/span> Read the bytes into the Data attribute of the record<\/span>
<\/span>        <\/span>int<\/span> PictureCol <\/span>=<\/span> <\/span>5<\/span>; <\/span>\/\/<\/span> the column # of the BLOB field                    <\/span>
<\/span>        record.Data <\/span>=<\/span> <\/span>new<\/span> Byte[(dr.GetBytes(PictureCol, <\/span>0<\/span>, <\/span>null<\/span>, <\/span>0<\/span>, <\/span>int<\/span>.MaxValue))];
        dr.GetBytes(PictureCol, <\/span>0<\/span>, record.Data, <\/span>0<\/span>, record.Data.Length);
     
        dr.Close();
        sqlConnection1.Close();
    }
}<\/span><\/pre>\n
<\/p>\n
Write File into the record<\/h3>\n
\/\/\/<\/span> <\/span><summary><\/span>
<\/span>\/\/\/<\/span> Save the Attachment record. If Id doesn't exist, it will create a new one, otherwise it will update.
<\/span>\/\/\/<\/span> <\/span><\/summary><\/span>
<\/span>\/\/\/<\/span> <\/span><param name=\"record\"><\/span>the attachment record to save<\/span><\/param><\/span>
<\/span>public<\/span> <\/span>static<\/span> <\/span>void<\/span> Save(Attachment record)
{

    <\/span>using<\/span> (SqlConnection sqlConnection1 <\/span>=<\/span> Connection)
    {
        SqlCommand cmd <\/span>=<\/span> sqlConnection1.CreateCommand();
        cmd.CommandType <\/span>=<\/span> CommandType.StoredProcedure;
        cmd.CommandText <\/span>=<\/span> <\/span>\"<\/span>SaveAttachment<\/span>\"<\/span>;

        <\/span>#region<\/span> Add the input parameter and set its value<\/span>

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

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

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

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

        <\/span>#endregion<\/span>

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

        sqlConnection1.Open();
        cmd.ExecuteNonQuery();
        <\/span>int<\/span> id <\/span>=<\/span> Int32.Parse(cmd.Parameters[<\/span>\"<\/span>@ID<\/span>\"<\/span>].Value.ToString());
        record.Id <\/span>=<\/span> id;
        sqlConnection1.Close();
    }
}<\/span><\/pre>\n
<\/p>\n
Read the file into the variable<\/h3>\n
<\/p>\n
\/\/\/<\/span> <\/span><summary><\/span>
<\/span>\/\/\/<\/span> Read the file into the Data field
<\/span>\/\/\/<\/span> <\/span><\/summary><\/span>
<\/span>\/\/\/<\/span> <\/span><param name=\"path\"><\/span>A valid path to the file<\/span><\/param><\/span>
<\/span>public<\/span> <\/span>void<\/span> ReadFromFile(<\/span>string<\/span> path)
{
    System.IO.FileStream fs <\/span>=<\/span>
        <\/span>new<\/span> System.IO.FileStream(path, System.IO.FileMode.Open, System.IO.FileAccess.Read);

    <\/span>this<\/span>.data <\/span>=<\/span> <\/span>new<\/span> Byte[fs.Length];
    fs.Read(<\/span>this<\/span>.data, <\/span>0<\/span>, <\/span>this<\/span>.data.Length);
    fs.Close();
}<\/span><\/pre>\n
Write the byte array to a File<\/h3>\n
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.<\/p>\n
<\/p>\n
\/\/\/<\/span> <\/span><summary><\/span>
<\/span>\/\/\/<\/span> Save the content into a file
<\/span>\/\/\/<\/span> <\/span><\/summary><\/span>
<\/span>\/\/\/<\/span> <\/span><param name=\"path\"><\/span>the full path to write to<\/span><\/param><\/span>
<\/span>public<\/span> <\/span>void<\/span> SaveToFile(<\/span>string<\/span> path)
{
    <\/span>if<\/span> (path.EndsWith(<\/span>\"<\/span>\\<\/span>\"<\/span>))
        path <\/span>+=<\/span> <\/span>\"<\/span>\\<\/span>\"<\/span>;

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

    fs.Write(<\/span>this<\/span>.data, <\/span>0<\/span>, <\/span>this<\/span>.data.Length);
    fs.Close();

}<\/span><\/pre>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading “Read\/Write files from\/to an Image field in SQL-Server”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"post-thumbnail":false},"uagb_author_info":{"display_name":"Pouya Panahy","author_link":"http:\/\/panahy.nl\/index.php\/author\/pouya\/"},"uagb_comment_info":0,"uagb_excerpt":"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…","_links":{"self":[{"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/posts\/113"}],"collection":[{"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/comments?post=113"}],"version-history":[{"count":0,"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/posts\/113\/revisions"}],"wp:attachment":[{"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/media?parent=113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/categories?post=113"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/panahy.nl\/index.php\/wp-json\/wp\/v2\/tags?post=113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}