In this article I am going to explain how to store images in SQL Server database and retrieve images from SQL Server database object.
Open studio 2010 and create a new ASP.NET MVC 3 Web Application project as seen below:
Choose ASPX as the view engine and click OK.
Add a controller and give the name as HomeController.
Create a table in your database as structured below:
CREATE TABLE FileStore
( ID int IDENTITY NOT NULL,
FileContent image NOT NULL,
MimeType nvarchar(50) NOT NULL,
FileName nvarchar(50) NOT NULL
)
Then create view (Index) in your application that contains multiple FileUpload box in order to upload the images.
<html>
<head runat="server">
<title>Index</title>
</head>
<body>
<div>
<%
using (Html.BeginForm("", "home", FormMethod.Post, new { enctype =
"multipart/form-data" }))
{%>
<input type="file" name="FileUpload1" /><br />
<input type="file" name="FileUpload2" /><br />
<input type="file" name="FileUpload3" /><br />
<input type="file" name="FileUpload4" /><br />
<input type="file" name="FileUpload5" /><br />
<input type="submit" name="Submit" id="Submit" value="Upload" /><br />
<% }%>
<h4>
<a href="/Home/Download">Go for the Download</a></h4>
</div>
</body>
</html>
After creating a View, add the following methods in your controller:
using System.Web;
using System.Web.Mvc;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace UploadDownloadDbDemo.Controllers
{
public class HomeController : Controller
{
//check whether the FileUpload control contain file or not
public bool HasFile(HttpPostedFileBase file)
{
return (file != null && file.ContentLength > 0) ? true : false;
}
//upload images in database one by one one if FileUpload contain file
public ActionResult Index()
{ foreach (string upload in Request.Files)
{
if (!HasFile(Request.Files[upload])) continue;
string mimeType = Request.Files[upload].ContentType;
Stream fileStream = Request.Files[upload].InputStream;
string fileName = Path.GetFileName(Request.Files[upload].FileName);
int fileLength = Request.Files[upload].ContentLength;
byte[] fileData = new byte[fileLength];
fileStream.Read(fileData, 0, fileLength);
const string connect = @"Server=your_servername;Database=your_database
;User Id=user_id;password=password;";
using (var conn = new SqlConnection(connect))
{
var qry = "INSERT INTO FileStore (FileContent, MimeType, FileName)
VALUES (@FileContent, @MimeType, @FileName)";
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.AddWithValue("@FileContent", fileData);
cmd.Parameters.AddWithValue("@MimeType", mimeType);
cmd.Parameters.AddWithValue("@FileName", fileName);
conn.Open();
cmd.ExecuteNonQuery();
}
}
return View();
}
//store file name in the list from the database
//and store that list object in a ViewBag property
public ActionResult Download()
{
const string connect = @"Server=your_servername;Database=your_database;User Id=user_id;password=user_password;";
List<string> imgList = new List<string>();
using (var conn = new SqlConnection(connect))
{
var qry = "SELECT FileContent, MimeType, FileName FROM FileStore";
var cmd = new SqlCommand(qry, conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
imgList.Add(rdr["FileName"].ToString());
}
}
ViewBag.Images = imgList;
return View();
}
//get the file id and return a file to the browser
public FileContentResult GetFile(int id)
{ SqlDataReader rdr;
byte[] fileContent = null;
string mimeType = "";
string fileName = "";
const string connect = @"Server=your_servername;Database=your_database;User
Id=user_id;password=user_password;";
using (var conn = new SqlConnection(connect))
{
var qry = "SELECT FileContent, MimeType, FileName FROM FileStore WHERE ID
= @ID";
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.AddWithValue("@ID", id);
conn.Open();
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{ rdr.Read();
fileContent = (byte[])rdr["FileContent"];
mimeType = rdr["MimeType"].ToString();
fileName = rdr["FileName"].ToString();
}
}
return File(fileContent, mimeType, fileName);
}
}
}
Create a Download View that displays the images file name return by the ViewBag
properties and if the property does not contain any files it will display a message
No files are in the database!.
<html>
<head runat="server">
<title>Download</title>
</head>
<body>
<div>
<%int data = 1; %>
<% foreach (string img in ViewBag.Images)
{ %>
<h4>
<%: Html.ActionLink(img,"GetFile/"+data++) %></h4>
<%} %>
<%if (data == 1)
{ %>
<h2>
No files are in the database!</h2>
<%} %>
</div>
</body>
</html>
When you run your application it displays the page as follows:
You can upload image file in the database. When you click on the Go for the Download link it will display all the uploaded files from the database into the pages:
Now, you can the download the images.
Thanks for reading this article and I think after reading this article you can easily store and retrieve images from database in MVC.
DigiOz Multimedia
13-Aug-2012Great Article, thank you for sharing it Rohit. A few small suggestions: