Wednesday 26 April 2017

SQL Query interview question part - 3

Here I just created Employee table and on the basis of Write Some common query  which generally asking in interview.

CREATE TABLE [dbo].[tblEmployee](
[EmployeeID] [int] IDENTITY(1,1)  NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Country] [nvarchar](15) NULL
)
GO

 SELECT * FROM [tblEmployee]


1. Ques: List of all employee from tblEmployee table whose "FirstName" start with latter 'a'.

Answer: SELECT * FROM [tblEmployee] WHERE FirstName like 'a%'



2. Ques:  List of all employee from tblEmployee table whose "FirstName" start with any single character between 'a-j'.

Answer: SELECT * FROM [tblEmployee] WHERE FirstName like '[a-j]%'



3.Ques: Get only Year part of "HireDate".
Answer:SELECT DATEPART(YEAR, HireDate) FROM [tblEmployee]


Thursday 20 April 2017

ViewModel Example In MVC ASP.Net With bootstrap

What is ViewModel in MVC ?
Answer: ViewModel contain fields that are represented in the view.It allow you to shape multiple entities from one or more data models or sources into a single object, optimized for consumption and rendering by the view.

In this tutorials i am going to showing demo how to use of ViewModel class in MVC ASP.Net.





In this demo I used flowing version of framework
  • Using Visual studio 2012 MVC 4 version.
  • Sql Server 2012.
  • AutoMapper DLL references  
  • For css using bootstrap and jquery
  • Entity framework (4.4.0.0) references  


Step:1
Create a Three table in sql server database. The three table name as

  • y_category_master
  • y_vendor_master
  • y_Product_Master

CREATE TABLE [dbo].[y_category_master] (
    [CategoryID]   INT          IDENTITY (1, 1) NOT NULL,
    [CategoryName] VARCHAR (50) NULL,
    CONSTRAINT [PK_y_category_master] PRIMARY KEY CLUSTERED ([CategoryID] ASC)
);

GO
CREATE TABLE [dbo].[y_vendor_master] (
    [venderId]   INT          IDENTITY (1, 1) NOT NULL,
    [venderName] VARCHAR (50) NULL,
    CONSTRAINT [PK_y_vendor_master] PRIMARY KEY CLUSTERED ([venderId] ASC)
);

GO
CREATE TABLE [dbo].[y_Product_Master] (
    [ProductId]  INT             IDENTITY (1, 1) NOT NULL,
    [Name]       VARCHAR (50)    NULL,
    [Price]      DECIMAL (18, 2) NULL,
    [CategoryID] INT             NULL,
    [VendorID]   INT             NULL,
    CONSTRAINT [PK_y_Product_Master] PRIMARY KEY CLUSTERED ([ProductId] ASC),
    CONSTRAINT [FK_y_Product_Master_y_category_master] FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[y_category_master] ([CategoryID]),
    CONSTRAINT [FK_y_Product_Master_y_vendor_master] FOREIGN KEY ([VendorID]) REFERENCES [dbo].[y_vendor_master] ([venderId])
);

GO
CREATE NONCLUSTERED INDEX [IX_FK_y_Product_Master_y_category_master]
    ON [dbo].[y_Product_Master]([CategoryID] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_FK_y_Product_Master_y_vendor_master]
    ON [dbo].[y_Product_Master]([VendorID] ASC);


Step:2 Create Entity Data Model to connect to database

Right-click on your project in Solution Explorer then click on ADD New ADO.NET Entity Data Model. name as "Product.edmx". Create database connection with the help of Entity framework
And drag & drop all three table in Entity modal design.



Step:3 Reffrence the AutoMapper DLL to your projects

Right Click on your project in Solution Explorer then select Manage NuGet Packages.search AutoMapper and install it.


Step:4 Add Model class

Here I created product modal partial class in Modal Folder with name Product.cs

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

namespace ViewModelProj.Models
{
    public partial class Product
    {
        public string Name { get; set; }
        public decimal Price { get; set; }
        public int CategoryID { get; set; }
        public int VendorID { get; set; }
 
    }

    public partial class Product
    {
        public string CategoryName { get; set; }
        public string VendorName { get; set; }
    }
}

Step:5 Add ViewModel class

Create new folder in project View_Model. In this folder right click and add class with name ProductViewModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ViewModelProj.Models;
using System.Web.Mvc;

namespace ViewModelProj.View_Model
{
    public class ProductViewModel
    {
        public Product product { get; set; }
        public SelectList VendorList { get; set; }
        public SelectList CategoryList { get; set;}
    }
}


Step:6 Add Controller 
In Controller folder open the 'HomeController' and put the code given bellow.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ViewModelProj.Models;
using ViewModelProj.View_Model;
using AutoMapper;

namespace ViewModelProj.Controllers
{
    public class HomeController : Controller
    {  
        TestEntities db = new TestEntities();
        public ActionResult Index()
        {
            List<Product> ProVModal = new List<Product>();
            using (TestEntities dc = new TestEntities())
            {
                var v = (from a in dc.y_Product_Master
                         join b in dc.y_category_master on a.CategoryID equals b.CategoryID
                         join c in dc.y_vendor_master on a.VendorID equals c.venderId
                         select new Product
                         {
                             Name = a.Name,
                             Price= (decimal)a.Price,
                             CategoryName= b.CategoryName,
                             VendorName = c.venderName
                         }).OrderBy(x => x.Name).ToList();
                ProVModal = v;
             
            }
            return View(ProVModal);
        }

        public ActionResult AddProduct()
        {
            ProductViewModel objProductViewModel = new ProductViewModel();
            var category = db.y_category_master;
            var vendor = db.y_vendor_master;

            objProductViewModel.CategoryList = new SelectList(category, "CategoryID", "CategoryName");
            objProductViewModel.VendorList = new SelectList(vendor, "venderId", "venderName");

            return View(objProductViewModel);
        }

        public ActionResult SaveProduct(ProductViewModel objProductViewModel)
        {        
            Mapper.CreateMap<Product,y_Product_Master>();
            y_Product_Master obj1 = Mapper.Map<Product, y_Product_Master>(objProductViewModel.product);
            db.y_Product_Master.Add(obj1);
            db.SaveChanges();

            return View();
        }

    }
}

Step:7 Adding View
Add three View against the controler. I added three view which given bellow as

Index.cshtml : This View showing all the product list in grid.
AddProduct.cshtml : This View use for add new product in system.
SaveProduct.cshtml : This View use shwoing Message to successfully product added.

@model IEnumerable<ViewModelProj.Models.Product>
@{
    ViewBag.Title = "Index";
}

<link href="~/Content/Bootstrap/bootstrap.css" rel="stylesheet" />

<h2 style="margin-left: 20px">All Product List</h2>
<p style="margin-left: 20px">
    @Html.ActionLink("Create New", "AddProduct")
</p>

 <div class="row" style="margin-left: 20px">
     <div class="form-group">
            <div class="col-md-6">
<table  class="col-md-12 table-bordered table-striped table-condensed cf" style="margin-left: 20px">
    <thead class="cf" style="border: 1px solid #ccc;">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Price)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.CategoryName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.VendorName)
        </th>
        <th></th>
    </tr>
        </thead>
    <tbody style="border: 1px thin black">
@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Price)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.CategoryName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.VendorName)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
        </td>
    </tr>
}
         </tbody>
</table>
</div>
         </div>
     </div>


AddProduct.cshtml
@model ViewModelProj.View_Model.ProductViewModel

@{
    ViewBag.Title = "AddProduct";
}

<h2 style="margin-left: 20px">AddProduct</h2>

@using (Html.BeginForm("SaveProduct","Home",FormMethod.Post)) {
    @Html.ValidationSummary(true)

    <div class="row" >
         <div class="col-xs-12 col-sm-12 col-md-12 ">
                    &nbsp;
                </div>
         <div>    
    </div>

         <div class="col-md-12">
        <table style="width:40%; margin-left: 20px" class="table-condensed" border="1">
        <tr>
            <td>
                <div class="row" >
                <div class="col-xs-12 col-sm-6 col-md-6">
                    <div class="editor-label">
                        Product Name:                    
                    </div>
                </div>
                <div class="col-xs-12 col-sm-6 col-md-6">
                    <div class="editor-field">
                        @Html.TextBoxFor(model => model.product.Name, new { @class="form-control", placeholder="Please Enter Product Name"})
                        @Html.ValidationMessageFor(model => model.product.Name, "Please Enter ProductName", new { @class = "text-danger" })
                    </div>
                </div>
              </div>

                <div class="row" >
               <div class="col-xs-12 col-sm-6 col-md-6">
                    <div class="editor-label">
                        Price:
                    </div>
                </div>
                <div class="col-xs-12 col-sm-6 col-md-6">
                    <div class="editor-field">

                        @Html.TextBoxFor(model => model.product.Price, new { @class="form-control", placeholder="Please Enter product Price"})
                        @Html.ValidationMessageFor(model => model.product.Price, "Please Enter product Price", new { @class = "text-danger" })
                     </div>
                </div>
                    </div>

                <div class="row" >
                <div class="col-xs-12 col-sm-6 col-md-6">
                    <div class="editor-label">
                     Select Category:
                    </div>
                </div>
                <div class="col-xs-12 col-sm-6 col-md-6">
                    <div class="editor-field">
                        @Html.DropDownListFor(model => model.product.CategoryID, Model.CategoryList, "-- Select One --" , new { @style = "width:100%", @class="dropdown-header" })
                        @Html.ValidationMessageFor(model => model.CategoryList, "Please Select Department Location", new { @class = "text-danger" })
                     </div>
                </div>
                    </div>

                <div class="row" >
                 <div class="col-xs-12 col-sm-6 col-md-6">
                    <div class="editor-label">
                      Select Vendor:
                    </div>
                </div>
                <div class="col-xs-12 col-sm-6 col-md-6">
                    <div class="editor-field">
                        @Html.DropDownListFor(model => model.product.VendorID, Model.VendorList, "-- Select One --" , new { @style = "width:100%", @class="dropdown-header" })
                        @Html.ValidationMessageFor(model => model.VendorList, "Please Select Department Location", new { @class = "text-danger" })
                     </div>
                </div>
                     </div>

                <div class="row" >
                <div class="col-xs-12 col-sm-12 col-md-12 " style="align-items:center">
                    <br />
                    <br />
                 
                     <button type="submit"  value="SAVE"  class="btn btn-success btn-group-lg"  >
                            <span class="glyphicon glyphicon-plus"></span> SAVE
                      </button>      
                </div>
              </div>
            </td>
        </tr>
    </table>
 </div>
</div>
}
 <br />
<div>
    <div class="row" >
                <div class="col-xs-12 col-sm-12 col-md-12 " style="margin-left: 20px" >
                           @Html.ActionLink("Back to List", "Index")
                    </div>
        </div>
    </div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}


SaveProduct.cshtml
@{
    ViewBag.Title = "SaveProduct";
}

<h2>Product Saved Successfully</h2>

<p>
    @Html.ActionLink("List of Prduct", "Index")
</p>


Conclusion : 
                     In this way you can use viewmodal class in mvc. Hope this article will helpful for you.