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() — Ilian
– Ilian, Commented Jun 9, 2022 at 5: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
– marc_s, Commented Jun 9, 2022 at 5:26
If you have not seen it, you might want to read store images in db or file system — JonasH
– JonasH, Commented Jun 9, 2022 at 6:58

DotNet Developer · Accepted Answer · 2022-06-09 05:48:02Z

2

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];

answered Jun 9, 2022 at 5:48

DotNet Developer

3,0381 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related