Solved

Downloading varbinary data into a variable or file

Posted on 2013-06-18
7
284 Views
Last Modified: 2013-10-04
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
Comment
Question by:VBBRett
  • 4
  • 3
7 Comments
 
LVL 1

Accepted Solution

by:
PeterInStingbert earned 500 total points
ID: 39257486
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
 
LVL 1

Assisted Solution

by:PeterInStingbert
PeterInStingbert earned 500 total points
ID: 39257575
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
 

Author Comment

by:VBBRett
ID: 39257590
So how would I change my code?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Assisted Solution

by:PeterInStingbert
PeterInStingbert earned 500 total points
ID: 39257660
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
 

Author Comment

by:VBBRett
ID: 39257839
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
 
LVL 1

Expert Comment

by:PeterInStingbert
ID: 39265015
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
 

Author Comment

by:VBBRett
ID: 39302835
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now