How to Upload Varbinary Data into SQL Server Using C# and SQL

I have a varbinary field which is going to hold images that I need to upload varbinary data and the file name into a table that has both a nvarchar for the file name and a varbinary field for the file data.  How can I do this using C#, asp.net and a SQL Stored Procedure?  Thanks!
VBBRettAsked:
Who is Participating?
 
Imran Javed ZiaConnect With a Mentor Consultant Software Engineer - .NET ArchitectCommented:
Hi,

You may convert you image file into byte array and then pass this byte array to sql server insert query or procedure as parameter.

for more information please review following links:

http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/cea770c9-b0e7-455b-8739-8659e5744ddd

http://www.codeproject.com/Articles/76206/Image-in-C-Save-Resize-and-Convert-to-Binary

Thanks
0
 
jorge_torizConnect With a Mentor Research & Development ManagerCommented:
With a table like this in a database named ExpertsExchange:
CREATE TABLE Pics(
	Id SMALLINT IDENTITY(1, 1),
	FileName VARCHAR(50) NOT NULL,
	Content VARBINARY(MAX) NOT NULL,
	CONSTRAINT pkPics PRIMARY KEY (Id)
)

Open in new window


You could upload a picture with the following code:

string filePath = @"D:\MyPics\SamplePic.jpg";

SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ExpertsExchange;Integrated Security=true;");
SqlCommand comm = new SqlCommand("INSERT INTO Pics (FileName, Content) VALUES (@FileName, @Content)", conn);
comm.Parameters.AddWithValue("@FileName", Path.GetFileName(filePath));
comm.Parameters.AddWithValue("@Content", File.ReadAllBytes(filePath));

conn.Open();
comm.ExecuteNonQuery();
conn.Close();
conn.Dispose();

Console.WriteLine("The pic have been uploaded");

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
VBBRettAuthor Commented:
Would this work in reverse for downloading a varbinary into a variable?
0
 
jorge_torizResearch & Development ManagerCommented:
Yes, you can download it in a byte array and then make anything you want with it.
0
 
Kalpesh ChhatralaConnect With a Mentor Software ConsultantCommented:
0
 
VBBRettAuthor Commented:
Can I share my code?  For some reason, I am getting a null value when I am trying to retrieve the data.
0
 
jorge_torizResearch & Development ManagerCommented:
Using the same table of my previous sample I would do this:

string filePath = @"D:\Personal\Pics\Misc\MyPic.JPG";

SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ExpertsExchange;Integrated Security=true;");
SqlCommand comm = new SqlCommand("SELECT Content FROM Pics WHERE Id = 1", conn);

conn.Open();
File.WriteAllBytes(filePath, (byte[])comm.ExecuteScalar());
conn.Close();
conn.Dispose();

Console.WriteLine("The pic has been downloaded");

Open in new window

0
 
VBBRettAuthor Commented:
I attempted to use the followin to download an image varbinary from the database, but for some reason, I am getting no reader to work.  Why is that?  Here is the code:


  public System.Data.SqlTypes.SqlBinary GetProfilePicture(Guid profileid)
        {
            Profile mcsprofile = 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();



                while (reader.Read())
                {

                    int fieldIndex = 1;
                    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
 
jorge_torizResearch & Development ManagerCommented:
I think the Size property is the problem, set it to a big size enough to load the picture
0
 
VBBRettAuthor Commented:
What size?  I thougth -1 was setting it to max?
0
 
jorge_torizResearch & Development ManagerCommented:
When I get an output parameter that has a size on SQL Server I always set the Size property to a right value
0
 
VBBRettAuthor Commented:
Right value meaning what for varbinary?
0
 
jorge_torizResearch & Development ManagerCommented:
Enough size to get the image
0
 
jorge_torizResearch & Development ManagerCommented:
Put a break point in line 46 (int fieldIndex = 1;) and review in the watcher what do you get in reader[1], I want to know what do you get
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.