How to retrieve Blob (VarBinary) data as string from SQL Server 2012 using a stored procedure?

Question

I have been trying to get a blob data from database through a stored procedure I am able to get the data but the problem is the data is for an ASP.NET MVC application where it converts using ConvertToList:

public static List<T> ConvertToList<T>(this DataTable dt)
{
    var columnNames = dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();
    var properties = typeof(T).GetProperties();

    return dt.AsEnumerable().Select(row =>
        {
            var objT = Activator.CreateInstance<T>();

            foreach (var pro in properties)
            {
                if (columnNames.Contains(pro.Name))
                {
                    if (pro.PropertyType == Type.GetType("System.Int32"))
                        pro.SetValue(objT, row[pro.Name].ToString().ToInt32(), null);
                    else if (pro.PropertyType == Type.GetType("System.Double"))
                        pro.SetValue(objT, row[pro.Name].ToString().ToDouble(), null);
                    else if (pro.PropertyType == Type.GetType("System.String"))
                        pro.SetValue(objT, row[pro.Name].ToString(), null);
                    else if (pro.PropertyType == Type.GetType("System.Int64"))
                        pro.SetValue(objT, row[pro.Name].ToString().ToInt32(), null);
                    else if (pro.PropertyType == Type.GetType("System.Boolean"))
                        pro.SetValue(objT, Convert.ToBoolean(row[pro.Name].ToString()), null);
                    //else if (pro.PropertyType == Type.GetType("System.Byte[]"))
                    //    pro.SetValue(objT, Convert.ToByte(row[pro.Name]), null); 
                }
            }
            return objT;
        }).ToList();
}

When a System.Byte[] data is fetched, it always is set to null; I have tried to convert it using usual Byte to string conversion such as

string result = System.Text.Encoding.UTF8.GetString(byteArray);

and

string s3 = Convert.ToBase64String(bytes);

This cannot be done since my return type is a var so I tried to convert using stored procedure while it's selecting like this

CONVERT(VARCHAR(MAX), LME.CoverPageImage, 2)

but it's still returning null value after the ConvertToList is called!

Is there any way to get it as a string from the database through a stored procedure or to convert it using C# ASP.NET MVC?

Have you checked the value in the table? Maybe it is null in the database. — Alex, Nov 02 '19 at 06:57
@Alex No I have checked it for the Id am calling it does have value in that column — marceloo1223, Nov 02 '19 at 07:29
I can't see a problem. Please try debugging tell us at what stage a non-NULL value becomes NULL. — Alex, Nov 02 '19 at 08:07
The problem is my ```ConvertToList``` function somehow sets the value to null if the datatype is ```System. Byte``` So here is how I overcome the situation Using this method link:https://stackoverflow.com/questions/45664937/retrieve-varbinary-value-as-base64-in-mssql retrived the data as a string and later the image src attribute dynamically set using a javascript function — marceloo1223, Nov 02 '19 at 10:54

David Browne - Microsoft · Accepted Answer · 2019-11-02T23:41:36.527

1

This cannot be done since my return type is a var

No. var is not at type.

run

var data = row[pro.Name];
var typeName = data.GetType().Name;

to see the runtime type of the DataTable column. I think you'll find that it's already byte[], if not you can convert it.

And

 Convert.ToByte(row[pro.Name])

is definitely not what you want. It converts a value to a single byte not a byte[].

edited Nov 02 '19 at 23:41

answered Nov 02 '19 at 13:54

David Browne - Microsoft

80,331
6
39
67

How to retrieve Blob (VarBinary) data as string from SQL Server 2012 using a stored procedure?

1 Answers1