Entity framework code first SQL:

Most of viewers and visitors of Yogeshdotnet.com, requested to upload some blogs based on entity framework code first with SQL. First of all I want to say thanks to all my visitors and regular viewers who shows their keen interest in my blogs.

Most of native programmers who have experience in traditional ado.net and SQL , they love to work in SQL ,they feel  it pretty easy .Writing Lambda expression for all database operations sometimes become very bigger task.  Entity framework API provides SQL Query function which is used to perform all SQL select queries.

In this example I am going to create a product table on where I will perform crud operations.

Product Table

SQL Query for product table

CREATE TABLE [dbo].[Products](
	[ProductId] [int] IDENTITY(1,1) NOT NULL,
	[ProductName] [varchar](50) NULL,
	[SerialNumber] [int] NULL,
	[Company] [varchar](50) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 Note: Please insert some dummy data into Product table  to view results .now come to the Visual Studio.

Let’s start

1. Create a new Project. Open Visual Studio 2013.
2. Go to “File” => “New” => “Project…”.
3. Select “Web” in installed templates.
4. Select “ASP.NET MVC Web Application”.
5. Enter the Name and choose the location.
6. Click “OK”.

Step 1:  first we create model class for product table.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace EF_BY_SQL.Models
{
    [Table("Products")]
    public class Products
    {
        [Key]
        public int ProductId{ get; set; }
        public string ProductName { get; set; }
        public int SerialNumber { get; set; }
        public string Company { get; set; }
    }
}

Step 2:   create data context class for all database operations.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace EF_BY_SQL.Models
{
    public class DataContext  : DbContext
    {
        public DataContext() : base("conn") { }
        public DbSet<Products> Products { get; set; }
    }
}

Step 3 create product controller

Step 4: Here I am writing action for searching product by product ID.

  //Object of DataContext
        DataContext db = new DataContext();

        // GET: /Product/
        public ActionResult Index()
        {

            return View();
        }
        [HttpPost]
        public ActionResult Index(string productid)
        {
            var productlist = db.Products.SqlQuery("select *from products where ProductId=@p0", productid).ToList();

            return View(productlist);
        }

 Note : Add Empty View with Index Action:

Index.cshtml

@model IEnumerable<EF_BY_SQL.Models.Products>
@{
    ViewBag.Title = "Index";
}

<h2>Search Product By ID</h2>
<form action="/" method="post" role="form">
    <div class="form-group">
        <label for="productid">ProductId</label>
        <input type="text" name="productid" value="" class="form-control"/>
        <input type="submit" name="btn_search" value="Search Item" class="btn btn-primary"  />
    </div>
</form>

<hr />
<!-- Following    code will execute after click of search button and item found as per productid -->
@{
    
    if(Model!=null)
    {
        <table class="table table-striped"  width="500px" border="1">
            <tr>
                <th>ProductId</th>
                <th>ProductName</th>
                <th>SRNumber</th>
                <th>Company</th>
            </tr>
            @foreach(var p in Model)
            {
                <tr>
                    <td>@p.ProductId</td>
                    <td>@p.ProductName</td>
                    <td>@p.SerialNumber</td>
                    <td>@p.Company</td>

                </tr>
            }

        </table>
    }
}

press F5 in see output

Step 5: now we will write code to insert products in database table
Right action to add product

  public ActionResult AddProduct()
        {
            //Open View with textboxes to insert product data
            //Add View with Create templete(select Product as Model class and DataContext as Context Class)
            return View();
        }
        [HttpPost]
        public ActionResult AddProduct(Products obj)
        {
            //Create Parameters which are used for product table fields. 
            List<object> parameters = new List<object>();
            parameters.Add(obj.ProductName);
            parameters.Add(obj.SerialNumber);
            parameters.Add(obj.Company);
            object[] objectarray = parameters.ToArray();
            //SQL Query function is used only for retrieve sql tables. 
            //To Perform Insert, Update , Delete we use Database.ExecuteSqlCommand which will return affected rows in database table
            int output = db.Database.ExecuteSqlCommand("insert into Products(ProductName,SerialNumber,Company) values(@p0,@p1,@p2)", objectarray);
            if (output > 0)
            {
                ViewBag.Itemmsg = "Your Product " + obj.ProductName + "  is added Successfully";
            }
            return View();
        }

AddProduct.cshtml

@model EF_BY_SQL.Models.Products

@{
    ViewBag.Title = "AddProduct";
}

<h2>AddProduct</h2>

@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Products</h4>
        <hr />
        @Html.ValidationSummary(true)

        <div class="form-group">
            @Html.LabelFor(model => model.ProductName, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ProductName)
                @Html.ValidationMessageFor(model => model.ProductName)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.SerialNumber, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.SerialNumber)
                @Html.ValidationMessageFor(model => model.SerialNumber)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Company, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Company)
                @Html.ValidationMessageFor(model => model.Company)
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}
<!-- To show message that Product is inserted successfully-->
<div>
    @{
        
        if(ViewBag.Itemmsg!=null)
        {
            <span style="color:green; font-weight:bold">@ViewBag.Itemmsg</span>
        }
    }
</div>

Step 6: Now we write action to update product table.

   public ActionResult UpdateProduct()
        {
            return View();
        }
        [HttpPost]
        public ActionResult UpdateProduct(Products obj)
        {
            List<object> parameters = new List<object>();
            parameters.Add(obj.ProductName);
            parameters.Add(obj.SerialNumber);
            parameters.Add(obj.Company);
            parameters.Add(obj.ProductId);
            object[] objectarray = parameters.ToArray();

            int output = db.Database.ExecuteSqlCommand("update Products set ProductName=@p0,SerialNumber=@p1,Company=@p2 where ProductId=@p3", objectarray);
            if (output > 0)
            {
                ViewBag.Itemmsg = "Your Product id " + obj.ProductId + "  is updated seccussfully";
            }
            return View();
        }

Create view with template create and select product as model class and data context as context class

UpdateProduct.cshtml

@model EF_BY_SQL.Models.Products

@{
    ViewBag.Title = "UpdateProduct";
}

<h2>UpdateProduct</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Products</h4>
        <hr />
        @Html.ValidationSummary(true)

        <div class="form-group">
            @Html.LabelFor(model => model.ProductId, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ProductId)
                @Html.ValidationMessageFor(model => model.ProductId)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.ProductName, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ProductName)
                @Html.ValidationMessageFor(model => model.ProductName)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.SerialNumber, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.SerialNumber)
                @Html.ValidationMessageFor(model => model.SerialNumber)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Company, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Company)
                @Html.ValidationMessageFor(model => model.Company)
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @{

        if (ViewBag.Itemmsg != null)
        {
            <span style="color:green; font-weight:bold">@ViewBag.Itemmsg</span>
        }
    }
</div>

Press F5 and see results.

Step 7: Now I will write Code to delete product.

  public ActionResult ProductDelete()
        {

            //First We write table to show all data including product id textbox. where user will see all data with Delete button 
            //with each row.
            var productlist = db.Products.SqlQuery("select *from products").ToList();

            return View(productlist);
        }
        //Add action with List Action ,Product as Model class and DataContext as Context
        //This action will execute when user will click on delete
        public ActionResult Delete(int? ProductId)
        {
            //As per id we will delete product
            var productlist = db.Database.ExecuteSqlCommand("delete from Products where ProductId=@p0", ProductId);

            if(productlist!=0)
            {
                //We will go back to action ProductDelete to show updated records
                return RedirectToAction("ProductDelete");
            }
            return View();
        }

ProductDelete.cshtml

@model IEnumerable<EF_BY_SQL.Models.Products>

@{
    ViewBag.Title = "ProductDelete";
}

<h2>ProductDelete</h2>
@using (Html.BeginForm())
{
    <table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.ProductName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.SerialNumber)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Company)
        </th>
        <th>Delete</th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.ProductName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.SerialNumber)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Company)
        </td>
        <td>
     
            @Html.ActionLink("Delete", "Delete", new { ProductId = item.ProductId })
        </td>
    </tr>
}

</table>
}

Download Complete Source Code