Solved

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

Posted on 2013-06-01
15
2,744 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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
 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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