[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3247
  • Last Modified:

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!
0
VBBRett
Asked:
VBBRett
  • 7
  • 5
  • 2
  • +1
4 Solutions
 
Imran Javed ZiaCommented:
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
 
Kalpesh ChhatralaSoftware ConsultantCommented:
0
 
jorge_torizCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
VBBRettAuthor Commented:
Would this work in reverse for downloading a varbinary into a variable?
0
 
jorge_torizCommented:
Yes, you can download it in a byte array and then make anything you want with it.
0
 
Kalpesh ChhatralaSoftware 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_torizCommented:
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_torizCommented:
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_torizCommented:
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_torizCommented:
Enough size to get the image
0
 
jorge_torizCommented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now