Upload images to SQL Server 2005 using ASP.Net MVC?

Question

Hi there,

I know there is a way to upload images to the database as image type or varbinary type, however, I searched around the entire week, I am unable to find anything that can help me, so this is really my last resort, if anybody know how to upload images to the database, I am using SQL Server 2005 Express.

Thanks

Answer 1

If you're okay storing the image as a VARCHAR, here is some code to do so.

    String b64;
    using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
    {
        this.pic.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
        Byte[] bytes = ms.ToArray();
        b64 = Convert.ToBase64String(bytes);
    }
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE [settings] SET [value] = @val WHERE [id] = 2", conn))
        {
            conn.Open();
            cmd.Parameters.Add(new SqlParameter("@val", b64));
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }

Answer 2

You should be able to access the Request's File collection and obtain an HttpPostedFile instance for each uploaded file. Grab the InputStream from the file and read it into the byte array for the column property. I'm assuming this is how your DAL maps the varbinary to your business class -- if not, say it's a native Image, then you'll need to do the conversion before saving. The example below uses LINQ2SQL.

MyClass obj = new MyClass();
obj.Name = Request["name"];   // other properties
obj.Alt = Request["altText"];

HttpPostedFile file = Request.Files[0];
if (file != null)
{
     obj.Image image = new byte[file.ContentLength];
     file.Read(obj.Image,0,file.ContentLength];
}

using (DataContext context = new DataContext())
{
    context.InsertOnSubmit( obj );
    context.SubmitChanges();
}

Answer 3

Assuming you have a stored procedure called TestProc which takes a single argument called @data of type IMAGE, your C# code could be as follows:

SqlConnection conn = new SqlConnection("<your connection string>");
conn.Open();

SqlCommand cmd = new SqlCommand("TestProc", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter("@data", SqlDbType.Image);
param.Value = System.IO.File.ReadAllBytes("any_file.jpg");
cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

Let me know if you want the stored procedure code as well.