Read Image stored in Oracle using Long DataType

Question

I want to read the image stored in Oracle Long datatype. Number of images are stored in a remote Oracle database in a column with datatype long. I just need to retrieve those images and show them on my aspx page. I could retrieve the image from database but when tried to caste it to byte array, it threw error that, string can not be converted to byte[]'. Anybody have any suggestions on how to retrieve these images stored in long column in database.

byte[] signatureBlobReceived = cls_TBL_BROKER_BL.GetInstance().GetSignatureBlobFromAccountNumber_BL(strCRNnumber);
 return File(signatureBlobReceived, "image/jpeg");


public byte[] GetSignatureBlobFromAccountNumber_BL()
{
object SignatureBlob = null;
Database db = DatabaseFactory.CreateDatabase("imageConnectionString");
DbCommand dbc = db.GetSqlStringCommand(ConfigurationSettings.AppSettings["signqry"].ToString());
dbc.CommandType = CommandType.Text;
SignatureBlob = db.ExecuteScalar(dbc);
byte[] array = Encoding.ASCII.GetBytes(Convert.ToString(SignatureBlob));
 string aa = string.Empty;
return array;
}

Query used is:
<add key="signqry" value="SELECT image FROM table1"/> `

Answer 1

Try this (odp.net)

            string connStr = "User Id=user;Password=pwd;Data Source=mySID;";
            OracleConnection _conn = new OracleConnection(connStr);
            _conn.Open();

            string sel = @"select long_raw_col from long_raw_test";
            OracleCommand cmd = new OracleCommand(sel, _conn);
            cmd.InitialLONGFetchSize = 5000;
            OracleDataReader reader = cmd.ExecuteReader();

            int rows = 0;
            // loop through rows from table
            while (reader.Read())
            {
                rows++;
                byte[] buf = new byte[5000];
                long bytesRead = reader.GetBytes(reader.GetOrdinal("long_raw_col"), 0, buf, 0, 5000);
                FileStream fs = new FileStream("C:\\test\\test_long" + rows + ".dat", FileMode.Create);
                fs.Write(buf, 0, (int)bytesRead);
                fs.Close();

                Console.WriteLine("Row " + rows + ": Read " + bytesRead + " bytes from table, see test_long" + rows + ".dat");
            }

This example just reads the long raw data from Oracle into a byte array, then outputs to a file. Note the InitalLONGFetchSize > 0.

Answer 2

I use this class :my database is informix and the images are stored in Byte type .Hope this can help you.

---

 public class MyPhoto
    {
        public static Stream RetrievePhoto()
        {
            DBConnection DAL_Helper = new DBConnection(ConfigurationSettings.AppSettings["connection"].ToString());
            Byte[] myByteBuff;
            Stream myImgStream;
            string qry = "----------";
            DataTable dt = DAL_Helper.Return_DataTable(qry);
            try
            {
                if (dt.Rows.Count > 0)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
                    {
                        myByteBuff = (Byte[])((object)(dt.Rows[0][0]));
                        myImgStream = new MemoryStream(myByteBuff);
                    }
                    else
                    {
                        myImgStream = RetrievePhotoNoProfile();
                    }
                }
                else
                {
                    myImgStream = RetrievePhotoNoProfile();
                }
            }
            catch (Exception ex)
            {
                myImgStream = RetrievePhotoNoProfile();
            }
            return myImgStream;
        }

        public static byte[] StreamToByteArray(Stream stream)
        {
            if (stream is MemoryStream)
            {
                return ((MemoryStream)stream).ToArray();
            }
            else
            {
                return ReadFully(stream);
            }
        }
        public static byte[] ReadFully(Stream input)
        {
            byte[] buffer = new byte[input.Length];
            using (MemoryStream ms = new MemoryStream())
            {
                int read;
                while ((read = input.Read(buffer, 0, buffer.Length)) > 0)
                {
                    ms.Write(buffer, 0, read);
                }
                return ms.ToArray();
            }
        }

        private static Stream RetrievePhotoNoProfile()
        {
            string noprofileimgPath = HttpContext.Current.Server.MapPath("~/images/noprofile.png");
            System.IO.FileStream fs = new System.IO.FileStream(noprofileimgPath, System.IO.FileMode.Open, FileAccess.Read);
            byte[] ba = new byte[fs.Length];
            fs.Read(ba, 0, (int)fs.Length);
            Stream myImgStream = new MemoryStream(ba);
            fs.Close();
            return myImgStream;
        }

        public static Image byteArrayToImage(byte[] byteArrayIn)
        {
            MemoryStream ms = new MemoryStream(byteArrayIn);
            Image returnImage = Image.FromStream(ms);
            return returnImage;
        }
    }