Solved

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

Posted on 2013-06-01
15
2,283 Views
Last Modified: 2013-07-05
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!
0
Comment
Question by:VBBRett
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 16

Accepted Solution

by:
Imran Javed Zia earned 125 total points
ID: 39213570
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
 
LVL 16

Assisted Solution

by:Kalpesh Chhatrala
Kalpesh Chhatrala earned 250 total points
ID: 39213913
0
 
LVL 15

Assisted Solution

by:jorge_toriz
jorge_toriz earned 125 total points
ID: 39224008
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
 

Author Comment

by:VBBRett
ID: 39256975
Would this work in reverse for downloading a varbinary into a variable?
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39257110
Yes, you can download it in a byte array and then make anything you want with it.
0
 
LVL 16

Assisted Solution

by:Kalpesh Chhatrala
Kalpesh Chhatrala earned 250 total points
ID: 39257135
0
 

Author Comment

by:VBBRett
ID: 39257446
Can I share my code?  For some reason, I am getting a null value when I am trying to retrieve the data.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39257669
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
 

Author Comment

by:VBBRett
ID: 39257883
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
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39257984
I think the Size property is the problem, set it to a big size enough to load the picture
0
 

Author Comment

by:VBBRett
ID: 39257997
What size?  I thougth -1 was setting it to max?
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39258034
When I get an output parameter that has a size on SQL Server I always set the Size property to a right value
0
 

Author Comment

by:VBBRett
ID: 39258038
Right value meaning what for varbinary?
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39258048
Enough size to get the image
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39258052
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 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

17 Experts available now in Live!

Get 1:1 Help Now