This application lets you perform CRUD operations on a table with an image column along with other text columns. The following is the structure for the table. Here we are using SQL Server 2008 as the BackEnd.
create table Employee
(
EmployeeId int identity(1,1),
Name varchar(30),
Address varchar(50),
Department varchar(50),
Photo varbinary(max)
)
insert into Employee(name,address,department)
values('Vishal Gilbile','Andheri','IT')
insert into Employee(name,address,department)
values('Rahul Bandekar','Worli','System')
insert into Employee(name,address,department)
values('Jack Johnsens','Bandra','Oracle')
insert into Employee(name,address,department)
values('Lolly Dolly','Dahisar','IT')
This will add 4 rows to the table with all photo columns set to null.
Now we'll add the following procedures that we will be using to do the CRUD operations on the table.
--for getting all the employees
create procedure prc_getallEmployees
as
begin
select * from Employee
end
--for adding a new employee
create procedure prc_addemployee
(
@name varchar(30),
@address varchar(50),
@department varchar(50),
@photo varbinary(max)= null
)
as
begin
insert into Employee
values(@name,@address,@department,@photo)
end
--for updating the employee
create procedure prc_updateEmployee
(
@id int,
@name varchar(30),
@address varchar(50),
@department varchar(50),
@image varbinary(max)
)
as
begin
update Employee
set Name=@name,Address=@address,Department=@department,Photo=@image
where EmployeeId=@id
end
--for deleting the Employee
create procedure prc_deleteEmployee
(
@eid int
)
as
begin
delete from Employee
where EmployeeId=@eid
end
We'll now move towards creation of the WCF service. Here we'll be creating a Service that will interact with the database to do the CRUD operations against the database table. For creation of the service we've made use of the WCF Service Library application that will provide us with a builtin hosting feature.
The following is the service description.
ServiceContract
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace CRUDService
{
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.
[ServiceContract]
public interface ICRUDOpr
{
[OperationContract]
List<Employee> GetAllEmployees();
[OperationContract]
int AddEmployee(List<Employee> lstEmployee);
[OperationContract]
int UpdateEmployee(Employee objEmployee);
[OperationContract]
int DeleteEmployee(int eid);
}
}
DataContract
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using System.Threading.Tasks;
using System.Web.Mvc;
using System.ComponentModel.DataAnnotations;
namespace CRUDService
{
[DataContract]
public class Employee
{
[DataMember]
public int EmployeeID { get; set; }
[DataMember]
public string Name { get; set; }
[DataMember]
public string Address { get; set; }
[DataMember]
public string Department { get; set; }
[DataMember]
public byte[] Photo { get; set; }
}
}
Service
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
namespace CRUDService
{
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in both code and config file together.
public class CRUDOpr : ICRUDOpr
{
private string dbCon = ConfigurationManager.ConnectionStrings["myCon"].ConnectionString;
SqlConnection con;
SqlDataAdapter da;
DataSet ds;
SqlCommand cmd;
public List<Employee> GetAllEmployees()
{
if (con == null)
con = new SqlConnection(dbCon);
da = new SqlDataAdapter("prc_getallEmployees", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
ds = new DataSet();
da.Fill(ds);
List<Employee> lstEmployee = new List<Employee>();
if (ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Employee obj = new Employee()
{
EmployeeID = Convert.ToInt32(ds.Tables[0].Rows[i]["EmployeeId"].ToString()),
Name = ds.Tables[0].Rows[i]["Name"].ToString(),
Address = ds.Tables[0].Rows[i]["Address"].ToString(),
Department = ds.Tables[0].Rows[i]["Department"].ToString(),
Photo = ds.Tables[0].Rows[i]["Photo"] == DBNull.Value ? null : (byte[])ds.Tables[0].Rows[i]["Photo"]
};
lstEmployee.Add(obj);
}
}
return lstEmployee;
}
public int AddEmployee(List<Employee> lstEmployee)
{
int rowsAffected = 0;
if (con == null)
con = new SqlConnection(dbCon);
cmd = new SqlCommand("prc_addemployee", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
for (int i = 0; i < lstEmployee.Count; i++)
{
cmd.Parameters.AddWithValue("@name", lstEmployee[i].Name);
cmd.Parameters.AddWithValue("@address", lstEmployee[i].Address);
cmd.Parameters.AddWithValue("@department", lstEmployee[i].Department);
cmd.Parameters.AddWithValue("@photo", lstEmployee[i].Photo);
rowsAffected += cmd.ExecuteNonQuery();
}
con.Close();
return rowsAffected;
}
public int UpdateEmployee(Employee objEmployee)
{
int rowsAffected = 0;
if (con == null)
con = new SqlConnection(dbCon);
cmd = new SqlCommand("prc_updateEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", objEmployee.EmployeeID);
cmd.Parameters.AddWithValue("@name", objEmployee.Name);
cmd.Parameters.AddWithValue("@address", objEmployee.Address);
cmd.Parameters.AddWithValue("@department", objEmployee.Department);
cmd.Parameters.AddWithValue("@image", objEmployee.Photo);
con.Open();
rowsAffected = cmd.ExecuteNonQuery();
con.Close();
return rowsAffected;
}
public int DeleteEmployee(int eid)
{
int rowsAffected = 0;
if (con == null)
con = new SqlConnection(dbCon);
cmd = new SqlCommand("prc_updateEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eid", eid);
con.Open();
rowsAffected = cmd.ExecuteNonQuery();
con.Close();
return rowsAffected;
}
}
}
Note: Kindly check the App.Config of the WCF Service of this example after downloading the article.
Now our service is ready. We can start using it. Let's start creating the application by opening a new Visula Studio 2012 window and select the new project as MVC4 project. Provide the project name as ESoutions.
Run the service by pressing the F5 key. This will open the following dialog box, since we've used the WCF service library application.
![the wcf service library application]()
Once the host is open we can add the service reference to our application. By right-clicking the ESolutions application then selecting the "Add Service Reference" option. Paste the URL that is shown in the preceding WCF Test client of your service, in my case the following is the URL:
http://localhost:8733/Design_Time_Addresses/CRUDService/CRUDOpr/mex
Provide the service reference name as Eservice and before proceeding further kindly check the web.config of your application; ensure that it contains the service reference tags or check the Reference.cs file of your Eservice Service Reference, that has been added to your MVC application. In my case these tags are not shown in the web.config file and my Reference.cs file is empty. The following is the snapshot of that.
![Service Reference]()
This is because when I added the reference the DataContractSerializer class encountered a new type (Newtonsoft.Json.Linq.JToken) that it does not support and it throws an exception and stops creating the references. Now how to remove this error? Just a few simple steps are required. Right-click on your Eservice then click "Configure Service Reference". This will open the ServiceReferenceSettings dialog box as in the following:
![ServiceReferenceSettings dialog box]()
Now select the radio button "Reuse types in referenced assemblies" then select all the checkboxes except Newtonsoft.JSon.
![Newtonsoft.JSon]()
And click OK and now try to update the service reference. This time you'll find that References.cs is not empty and also there is any entry in the application web.config file for your service.
Note: If the Reference.cs in your case is not empty kindly ignore the preceding steps.
Once the service reference is added we can start with developing the application flow.
First we'll start the Model for our application. I create a new class with the name EmployeeViewModel inside my models folder. This class will have the same structure as the one we declared in the CRUDService DataContract class named Employee except for the Photo Property, there we took byte[] here we'll HttpPostedFileBase class; the reason will be explained later.
The following is the code for the EmployeeViewModel class with all the validation added as an attribute to the property.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Web.Mvc;
namespace ESolutions.Models
{
public class EmployeeViewModel
{
[Key]//denotes that it uniquely identifies the employee
public int EmployeeID { get; set; }
[Required(ErrorMessage="Name is Required")]
public string Name { get; set; }
[Required(ErrorMessage="Address is Required")]
public string Address { get; set; }
[Required(ErrorMessage="Department is Required")]
public string Department { get; set; }
public HttpPostedFileBase Photo { get; set; }
}
}
The next thing is to add a controller to our application. The following is the code for that.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ESolutions.EService;
using ESolutions.Models;
namespace ESolutions.Controllers
{
public class EHomeController : Controller
{
CRUDOprClient objClient = new CRUDOprClient();
public ActionResult Index()
{
//calling the service function for retrieving all the employees data
List<Employee> lstEmployees = objClient.GetAllEmployees();
Session["eData"] = lstEmployees;//storing the Employee Data to Session Object
List<EmployeeViewModel> lstEModel = lstEmployees.Select(x => new EmployeeViewModel
{
EmployeeID = x.EmployeeID,
Department = x.Department,
Address = x.Address,
Name = x.Name,
}).ToList();
return View("Index", lstEModel);
}
}
}
Then right-click on the Index() and click on AddView to create a view for our application data display purposes. When creating the view kindly select a Strongly Type view option and also set the default layout to the _Layout.cshtml and select the Scaffold option to List. The following is the snapshot of that
Note: Before creating the view kindly just build the application so that your models are reflected when you select the strongly typed view when adding the view.
![Before creating the view]()
The following code will be added to your applications Index view:
@model IEnumerable<ESolutions.Models.EmployeeViewModel>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table>
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th>
@Html.DisplayNameFor(model => model.Department)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.DisplayFor(modelItem => item.Department)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.EmployeeID }) |
@Html.ActionLink("Details", "Details", new { id=item.EmployeeID }) |
@Html.ActionLink("Delete", "Delete", new { id=item.EmployeeID })
</td>
</tr>
}
</table>
Notice that for your photo column there is no entry in the view, we need to manually add the entry for that. After adding the entry your view will look like the following:
@model IEnumerable<ESolutions.Models.EmployeeViewModel>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table>
<tr>
<th>
@Html.DisplayNameFor(model => model.EmployeeID)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th>
@Html.DisplayNameFor(model => model.Department)
</th>
<th>
@Html.DisplayNameFor(model => model.Photo)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.EmployeeID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.DisplayFor(modelItem => item.Department)
</td>
<td>
<!--Here we added a Url Action to GetImage which will be an action method also for routing we are ing the EmployeeID
inside EHome controller-->
<img width="50" height="50" src="@Url.Action("GetImage", "EHome", new { item.EmployeeID })" />
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.EmployeeID }) |
@Html.ActionLink("Details", "Details", new { id = item.EmployeeID }) |
@Html.ActionLink("Delete", "Delete", new { id = item.EmployeeID })
</td>
</tr>
}
</table>
Now it's time to add the GetImage Action Method since that method is used for displaying the image of the employees. Write the following function inside the EHomeController.cs file:
public FileContentResult GetImage(int employeeID)
{
List<Employee> lstData = null;
if (Session["eData"] != null)
{
lstData = (List<Employee>)Session["eData"];
Employee objEmployee = lstData.FirstOrDefault(x => x.EmployeeID == employeeID);
if (objEmployee != null && objEmployee.Photo != null)
{
return File(objEmployee.Photo, "image");
}
else
{
FileStream fs = new FileStream(Server.MapPath("~/Content/images/blank-image.jpg"), FileMode.Open, FileAccess.Read);
byte[] rawByte = new byte[fs.Length];
fs.Read(rawByte, 0, Convert.ToInt32(fs.Length));
return File(rawByte, "image");
}
}
else
eturn null;
}
You can now test the application by running it. You'll get an error message stating that the resource couldn't be found; this is because of the default Routing for MVC. We need to make a minor change in the RouteConfig.cs file that is inside the App_Start folder of your application.
Here the controller name default is specified as "Home"; we need to make it "EHome", that's it.
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "EHome", action = "Index", id = UrlParameter.Optional }
);
Also if the image stored inside your database is large then when retrieving the image from the database it may throw an error. Make the following changes in your applications web.config file:
<system.serviceModel>
<bindings>
<basicHttpBinding>
<binding name="BasicHttpBinding_ICRUDOpr" closeTimeout="00:10:00" openTimeout="00:10:00" receiveTimeout="00:10:00" sendTimeout="00:10:00"
allowCookies="false" byProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
maxBufferPoolSize="524288" maxBufferSize="2147483647" maxReceivedMessageSize="2147483647" textEncoding="utf-8" transferMode="Buffered"
useDefaultWebProxy="true" messageEncoding="Text">
<readerQuotas maxDepth="32" maxStringContentLength="2147483647" maxArrayLength="2147483647" maxBytesPerRead="2147483647"
maxNameTableCharCount="2147483647"/>
</binding>
</basicHttpBinding>
</bindings>
<client>
<endpoint address="http://localhost:8733/Design_Time_Addresses/CRUDService/CRUDOpr/"
binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_ICRUDOpr" behaviorConfiguration="Bhw"
contract="EService.ICRUDOpr" name="BasicHttpBinding_ICRUDOpr" />
</client>
<behaviors>
<endpointBehaviors>
<behavior name="Bhw">
<dataContractSerializer maxItemsInObjectGraph="2147483647"/>
</behavior>
</endpointBehaviors>
</behaviors>
</system.serviceModel>
Now run the application.
Note: Before running the application throughout this example ensure your service is running because our database level operations are being done by the Service.
![run the application]()
Notice that there is no image in the database table; there the default image is being displayed.
Now we'll try to create a new Employee for which we've already provided the link on the Index View. Add a new ActionMethod in EHomeController like the following:
public ActionResult Create()
{
return View("Create", new EmployeeViewModel());
}
Add the view like we did above. But this time select the scaffold option to create it keeping everything the same.
The following are the changes that we need to do inside the Create.cshtml View:
@model ESolutions.Models.EmployeeViewModel
@{
ViewBag.Title = "Create";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>Create</h2>
<!--Since we are returning data with image we need to set the enctype attribute to "multipart/form-data" -->
@using (Html.BeginForm("Create", "EHome", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<!--If you want to show validation summary then make the below validationsummary value to false default true. Also while displaying the
validation error if you want heading for the validation summary you can provide it by ing the second parameter -->
@Html.ValidationSummary(false, "Errors on the Page")
<fieldset>
<legend>Add Employee</legend>
<div class="editor-label">
@Html.HiddenFor(model => model.EmployeeID)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name, "*")
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Address)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Address)
@Html.ValidationMessageFor(model => model.Address, "*")
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Department)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Department)
@Html.ValidationMessageFor(model => model.Department, "*")
</div>
<div class="editor-label">
Photo
</div>
<div class="editor-field">
@if (Model.Photo == null)
{
@:None<!--This is used for writing on browse same as <%=%> in asp.net-->
}
else
{
<img width="70" height="70" style="border:2px solid black;" src="@Url.Action("GetImage", "EHome", new { Model.EmployeeID })" />
}
<div>
Upload new image:
<input type="file" name="Image" />
</div>
@Html.ValidationMessageFor(model => model.Photo)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}In the CreateNew.cshtml page there is one button on whose click the data that the user enter should be saved. For this I've added a new action method inside EHomecontroller with the name Create() only but decorated with the [HttpPost] attribute. The following is the code for that.
[HttpPost]//This denotes that this action method will be called only for post requests.
public ActionResult Create(EmployeeViewModel objEmployee, HttpPostedFileBase image)
{
if (ModelState.IsValid)
{
byte[] rawBytes = null;
if (image != null)
{
rawBytes = new byte[image.ContentLength];
image.InputStream.Read(rawBytes, 0, image.ContentLength);
}
else
{
//if image is null then set the default image
FileStream fs = new FileStream(Server.MapPath("~/Content/images/blank-image.jpg"), FileMode.Open, FileAccess.Read);
rawBytes = new byte[fs.Length];
fs.Read(rawBytes, 0, Convert.ToInt32(fs.Length));
}
List<Employee> lstEmployee = new List<Employee>();
lstEmployee.Add(new Employee()
{
EmployeeID = objEmployee.EmployeeID,
Address = objEmployee.Address,
Department = objEmployee.Department,
Name = objEmployee.Name,
Photo = rawBytes
});
int rows = objClient.AddEmployee(lstEmployee);
if (rows > 0)
return RedirectToAction("Index");// if the row is added then redirect to action index
else
return View("Create");
}
return View("Create");
}
Now run the solution and click on the "Create New" link. The following is the snapshot for that.
If you don't provide the full details then the validation errors will be fired as in the following:
![validation errors]()
Fill in the details and once you click on the Create Button a new employee will be added to your application.
![new employee added]()
Similarly you can do that for an edit. For the full code download the article.