Solved

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

Posted on 2013-06-01
15
2,592 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
C# Windows app updating JSON file error. 9 39
SQL profiler 3 18
Fastest way to get file count in MS Server 2012 3 11
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.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 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