Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

Downloading varbinary data into a variable or file

I am looking to download varbinary data into a sqlbinary variable in C# but I am not sure how to get this to work.  I have a class method that is running the transaction from the database and then another small chunk of code that is actully implementing the method.  How am I supposed to get this to work?  I have attached some code below.  Thanks!


 public System.Data.SqlTypes.SqlBinary GetProfilePicture(Guid profileid)
        {
            Profile profile = new Profile();

            SqlBinary profilepic;

            SqlConnection objConnection = new SqlConnection();
            DataSet objDataset = new DataSet();
            SqlDataAdapter objAdapter = new SqlDataAdapter();

            SqlCommand objCommand = new SqlCommand("spGetProfilePictureByProfileId");
            objCommand.CommandType = CommandType.StoredProcedure;

            profile.ProfileId = profileid;


            objConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            objConnection.Open();
            objCommand.Connection = objConnection;
            objCommand.Parameters.Add(new SqlParameter("@ProfileId", profile.ProfileId));

            SqlParameter sqlparam = new SqlParameter();

            sqlparam.ParameterName = "@ProfilePictureUrl";
            sqlparam.Direction = ParameterDirection.Output;
            sqlparam.Size = -1;

            objCommand.Parameters.Add(sqlparam.ParameterName, SqlDbType.VarBinary);
            //objCommand.Parameters.Add(sqlparam);



            //SqlDataReader reader;
            //objCommand.ExecuteNonQuery();

           // reader = objCommand.ExecuteReader();

            System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();

            using (var reader = objCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    byte[] raw = (byte[])reader.GetSqlBytes(0).Buffer;
                    profilepic = raw;

                    profile.ProfilePictureUrl = profilepic;
                }

                
            }

          return profile.ProfilePictureUrl;
            
        }      

Open in new window


and below is how I am attempting to retrieve the data from the database:

profileid = Guid.Parse(Session["ProfileId"].ToString());

SqlBinary ProfileImage = profile.GetProfilePicture(profileid);

Open in new window



unfortunately my retrieval process is not working.  How do I do this correctly?  The varbinary data that I am trying to retrieve from the database is an image.  Thanks!
0
VBBRett
Asked:
VBBRett
  • 4
  • 3
3 Solutions
 
PeterInStingbertCommented:
Hi
read a Byte[] within a datareader:

int fieldIndex = 0

if (reader.IsDBNull(fieldIndex ))
    return new byte[0];
else
{
    Byte[] b = new Byte[(reader.GetBytes(fieldIndex , 0, null, 0, int.MaxValue))];
     if (b.Length <= 0)
          return new byte[0];
     else
      {
           this.reader.GetBytes(fieldIndex , 0, b, 0, b.Length);
            return b;
       }
}

Open in new window


Create the Image from Byte[]

Byte[] imageStream = ...

System.Drawing.ImageConverter ic = new System.Drawing.ImageConverter();
image = (System.Drawing.Image)(ic.ConvertFrom(this._imageStream));

Open in new window

0
 
PeterInStingbertCommented:
Sorry typing error in Create Image from...

Byte[] imageStream = ...

System.Drawing.ImageConverter ic = new System.Drawing.ImageConverter();
image = (System.Drawing.Image)(ic.ConvertFrom(imageStream)); 

Open in new window

0
 
VBBRettAuthor Commented:
So how would I change my code?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PeterInStingbertCommented:
Hi,
It is a Problem to test w/o Information about Profil
but try this:

public System.Data.SqlTypes.SqlBinary GetProfilePicture(Guid profileid)
        {
            Profile profile = new Profile();

            SqlBinary profilepic;

            SqlConnection objConnection = new SqlConnection();
            DataSet objDataset = new DataSet();
            SqlDataAdapter objAdapter = new SqlDataAdapter();

            SqlCommand objCommand = new SqlCommand("spGetProfilePictureByProfileId");
            objCommand.CommandType = CommandType.StoredProcedure;

            profile.ProfileId = profileid;


            objConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            objConnection.Open();
            objCommand.Connection = objConnection;
            objCommand.Parameters.Add(new SqlParameter("@ProfileId", profile.ProfileId));

            SqlParameter sqlparam = new SqlParameter();

            sqlparam.ParameterName = "@ProfilePictureUrl";
            sqlparam.Direction = ParameterDirection.Output;
            sqlparam.Size = -1;

            objCommand.Parameters.Add(sqlparam.ParameterName, SqlDbType.VarBinary);
            //objCommand.Parameters.Add(sqlparam);



            //SqlDataReader reader;
            //objCommand.ExecuteNonQuery();

           // reader = objCommand.ExecuteReader();

            System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();

            using (var reader = objCommand.ExecuteReader())
            {
               
                while (reader.Read())
                {

                    int fieldIndex = 0;
                    Byte[] raw;

                    if (reader.IsDBNull(fieldIndex))
                        raw = new byte[0];
                    else
                    {
                        raw = new Byte[(reader.GetBytes(fieldIndex, 0, null, 0, int.MaxValue))];
                        if (raw.Length > 0)
                            reader.GetBytes(fieldIndex, 0, raw, 0, raw.Length);
                    }

                    profilepic = raw;
                    profile.ProfilePictureUrl = profilepic;
                }                
            }

          return profile.ProfilePictureUrl;
            
        }       

Open in new window

0
 
VBBRettAuthor Commented:
There seems to be a problem with the code not reading the reader.  It appears to drop out of the loop when it gets to that point.  I don't understand, it completely skipped the using (var reader) or the while (reader) loop.  Why would it do that?
0
 
PeterInStingbertCommented:
Your original code is:

 using (var reader = objCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    byte[] raw = (byte[])reader.GetSqlBytes(0).Buffer;
                    profilepic = raw;

                    profile.ProfilePictureUrl = profilepic;
                }
            }

          return profile.ProfilePictureUrl;

Open in new window


Reader.Read()  works strictly Forward
--> The default position of a data reader is before the first record. Therefore, you must call Read to begin accessing data.

If there is no result (no records) in your "select Statement"

  while (reader.Read())
{
}

never Loop.
0
 
VBBRettAuthor Commented:
So is it better to throw into a file in a file directory or stream it to a file in a website?
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now