Retrieve a saved image in byte array using stored procedure in C#

Question

I have saved an image as byte array in a SQL Server database table successfully. Now I need to retrieve it using a stored procedure.

Model:

public class PlateNumberEntity
{
    public DateTime EntryDateTime { get; set; }
    public string PlateNumber { get; set; } = string.Empty;
    public string Plaza { get; set; } = string.Empty;
    public string Direction { get; set; } = string.Empty;
    public byte[]? OverViewImage { get; set; } = null;
}

Repository:

public List<PlateNumberEntity> GetPlateNumberEntities(string plateNumber)
{
    var list = new List<PlateNumberEntity>();

    using(connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using(var cmd = connection.CreateCommand())
        {
            cmd.CommandText = "[dbo].[GetAlprEntryInfo]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 10;

            var pPlateNumber = new SqlParameter
            {
                ParameterName = "@PlateNumber",
                DbType = DbType.String,
                Direction = ParameterDirection.Input,
                Value = plateNumber
            };

            cmd.Parameters.Add(pPlateNumber);

            using(var reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var entryDateTime = reader.GetDateTime(0);
                        var plate = reader.GetString(1);
                        var plaza = reader.GetString(2);
                        var direction = reader.GetString(3);
                        var overViewImage = reader.??? ====> HOW TO RETRIEVE THE BYTES
                        
                        var xList = new PlateNumberEntity
                        {
                            ....
                        }
                        list.Add(xList)
                    }
                }
            }
        }
    }

    return list;
}

I think I can use reader.GetBytes(<parameters>), but the size of the image may vary.

Does this answer your question? [What 'length' parameter should I pass to SqlDataReader.GetBytes()](https://stackoverflow.com/questions/11135245/what-length-parameter-should-i-pass-to-sqldatareader-getbytes) — Ilian, Jun 09 '22 at 05:24
One of the many reasons it might make a lot of sense to **store** the length of the image byte array along with the file contents itself, in your database table! — marc_s, Jun 09 '22 at 05:26
If you have not seen it, you might want to read [store images in db or file system](https://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay) — JonasH, Jun 09 '22 at 06:58

score 2 · Answer 1 · answered Jun 09 '22 at 05:48

Get raw values first

object[] values = new object[5];
reader.GetValues(values);

Then, individual values

var entryDateTime = (DateTime)values[0];
var plate = (string)values[1];
var plaza = (string)values[2];
var direction = (string)values[3];
var overViewImage = (byte[])values[4];

Retrieve a saved image in byte array using stored procedure in C#

1 Answers1