VBBRett
asked on
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, you can download it in a byte array and then make anything you want with it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can I share my code? For some reason, I am getting a null value when I am trying to retrieve the data.
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");ASKER
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;
}
I think the Size property is the problem, set it to a big size enough to load the picture
ASKER
What size? I thougth -1 was setting it to max?
When I get an output parameter that has a size on SQL Server I always set the Size property to a right value
ASKER
Right value meaning what for varbinary?
Enough size to get the image
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
ASKER