Wednesday, April 22, 2015

Crude Operation in MVC using ADO.NET

Controller
========================================================================


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcTest.Models;
using MvcTest.Repository;
namespace MvcTest.Controllers
{
    public class UserController : Controller
    {
        //
        // GET: /User/

        public ActionResult userList()
        {
            List<userModel> objList = new userRepository().getUserList();
            return View(objList);
        }
        [HttpGet]
        public ActionResult addUser()
        {
            return View();
        }
        [HttpPost]
        public ActionResult addUser(userModel obj)
        {
            if (ModelState.IsValid)
            {
                userRepository objs = new userRepository();
                objs.Insert(obj);
                Response.Redirect("userList");
            }
            return View();
        }
        [HttpGet]
        public ActionResult delete(int id = 0)
        {

            userRepository objs = new userRepository();
            objs.Delete(id);
            Response.Redirect("~/user/userList");
            return View();
        }
        [HttpGet]
        public ActionResult editUser(int id)
        {
            userModel model = new userRepository().getUserListById(id);
            return View(model);
        }
        [HttpPost]
        public ActionResult editUser(userModel obj)
        {

            userRepository objs = new userRepository();
            objs.Edit(obj);
            Response.Redirect("~/user/userList");
            return View();
        }

        //[HttpGet]
        //public ActionResult deleteUser(int id)
        //{
        //   // userModel model = new userRepository().getUserListById(id);
        //    return View();
        //}
    
    }
}

Model
=====================================================================

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

namespace MvcTest.Models
{
    public class userModel
    {
        public int userId { get; set; }
        [Required(ErrorMessage = "Name is required")]
        [StringLength(15, ErrorMessage = "Name length Should be less than 50")]
        public string userName { get; set; }

        [Required(ErrorMessage = "city is required")]
        [StringLength(50, ErrorMessage = "city Name length Should be less than 50")]
        public string city { get; set; }

        [Required(ErrorMessage = "Gender is required")]
        //[StringLength(,ErrorMessage = "city Name length Should be less than 50")]
        public int gender { get; set; }
       
        //public List<product> products
        //{
        //    get; set;
       
        //}
        //[Required(ErrorMessage = "First Name is required")]
        //[StringLength(15, ErrorMessage = "First Name length Should be less than 50")]
        //public string FirstName { get; set; }

        //[Required(ErrorMessage = "Last Name is required")]
        //[StringLength(50, ErrorMessage = "Last Name length Should be less than 50")]
        //public string LastName { get; set; }

        //[Required(ErrorMessage = "Address is required")]
        //[StringLength(100, ErrorMessage = "Address length Should be less than 100")]
        //public string Address { get; set; }

        //[Required(ErrorMessage = "Phone Number is required")]
        //[StringLength(15, ErrorMessage = "Phone Number length Should be less than 15")]
        //[RegularExpression(@"^[0-9]{0,15}$", ErrorMessage = "PhoneNumber should contain only numbers")]
        //public string PhoneNumber { get; set; }

        //[Required(ErrorMessage = "eMail is required")]
        //[StringLength(35, ErrorMessage = "eMail Length Should be less than 35")]
        //[RegularExpression(@"^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$", ErrorMessage = "eMail is not in proper format")]
        //public string eMail { get; set; }


    }

    public class product
    {
        public int productId { get; set; }
        public string productName { get; set; }
    }
}


=======================================================================
Repository
=======================================================================


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using MvcTest.Models;
using System.Data.Common;
namespace MvcTest.Repository
{
    public class userRepository
    {
        SqlConnection con = new SqlConnection(@"Data Source=WINDOWSSERVER\MSSQLSERVER2008;Initial Catalog=test;User ID=test;Password=test123");
        public List<userModel> getUserList()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from userMaster", con);
         //   SqlDataReader dr = cmd.ExecuteReader();
            List<userModel> objList = new List<userModel>();

            using (DbDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    userModel obj_pro_User = new userModel();
                    obj_pro_User.userId = dr.GetInt32(0);
                    obj_pro_User.userName = dr.GetString(1);
                    obj_pro_User.city = dr.GetString(2);
                    obj_pro_User.gender = dr.GetInt32(3);
                 //   obj_pro_User.products = getproductlist(obj_pro_User.userId);
                    objList.Add(obj_pro_User);
                }
                //dr.Close();
                cmd.Connection.Close();
                con.Close();

             
            }
            return objList;
        }


        public List<product> getproductlist(int pId)
        {
         //   con.Open();
         
            SqlCommand cmds = new SqlCommand("SP_product_select", con);
            cmds.CommandType = CommandType.StoredProcedure;
            cmds.Parameters.AddWithValue("@productId", pId);
           // dr.Close();
           // cmd.Connection.Close();
            List<product> objLists = new List<product>();
            using (DbDataReader drs = cmds.ExecuteReader())
            {
               
                while (drs.Read())
                {
                    product obj_pro_User = new product();
                    obj_pro_User.productId = drs.GetInt32(0);
                    obj_pro_User.productName = drs.GetString(1);

                    objLists.Add(obj_pro_User);
                }
            }
            con.Close();
            return objLists;
        }
        public void Insert(userModel obj)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("SP_user_insert", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@userName", obj.userName);
            cmd.Parameters.AddWithValue("@city", obj.city);
            cmd.Parameters.AddWithValue("@gender", obj.gender);
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public void Edit(userModel obj)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("SP_user_update", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@userId", obj.userId);
            cmd.Parameters.AddWithValue("@userName", obj.userName);
            cmd.Parameters.AddWithValue("@city", obj.city);
            cmd.Parameters.AddWithValue("@gender", obj.gender);
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public void Delete(int id)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("SP_user_delete", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@userId", id);
            cmd.ExecuteNonQuery();
            con.Close();
        }
        public userModel getUserListById(int id)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("SP_userByID", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@userId", id);
            SqlDataReader dr = cmd.ExecuteReader();
            //List<userModel> objList = new List<userModel>();
            userModel obj_pro_User = new userModel();
            if (dr.Read())
            {
              
                obj_pro_User.userId = dr.GetInt32(0);
                obj_pro_User.userName = dr.GetString(1);
                obj_pro_User.city = dr.GetString(2);
                obj_pro_User.gender = dr.GetInt32(3);
               // objList.Add(obj_pro_User);
            }
            con.Close();
            return obj_pro_User;
        }
    }
}


SQL QUERY
______________________________________________________________________________
USE [test]
GO
/****** Object:  Table [dbo].[userMaster]    Script Date: 04/22/2015 09:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[userMaster](
      [userId] [int] IDENTITY(1,1) NOT NULL,
      [userName] [varchar](500) NULL,
      [city] [varchar](500) NULL,
      [Gender] [int] NULL,
 CONSTRAINT [PK_userMaster] PRIMARY KEY CLUSTERED
(
      [userId] 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
/****** Object:  Table [dbo].[tbl_Product]    Script Date: 04/22/2015 09:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Product](
      [Product_Id] [int] IDENTITY(1,1) NOT NULL,
      [ProductName] [varchar](500) NULL,
      [CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_tbl_Product] PRIMARY KEY CLUSTERED
(
      [Product_Id] 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
/****** Object:  Table [dbo].[tbl_Cat]    Script Date: 04/22/2015 09:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Cat](
      [Cat_Id] [int] IDENTITY(1,1) NOT NULL,
      [Product_Id] [int] NULL,
      [Cat_Name] [varchar](500) NULL,
      [Cat_Description] [varchar](5000) NULL,
      [Created_on] [datetime] NULL,
 CONSTRAINT [PK_tbl_Cat] PRIMARY KEY CLUSTERED
(
      [Cat_Id] 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
/****** Object:  StoredProcedure [dbo].[SP_userByID]    Script Date: 04/22/2015 09:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_userByID]

      @userId     int =0
     
AS
BEGIN
      select * from userMaster where userId=@userId
END
GO
/****** Object:  StoredProcedure [dbo].[SP_user_update]    Script Date: 04/22/2015 09:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_user_update]
      @userId     int =0,
      @userName varchar(500)='',
      @city varchar(500)='',
      @gender     int
     
AS
BEGIN
      update userMaster set
      userName=@userName,
      city=@city,
      Gender=@gender
      where userId=@userId
END
GO
/****** Object:  StoredProcedure [dbo].[SP_user_insert]    Script Date: 04/22/2015 09:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_user_insert]
     
      @userName varchar(500)='',
      @city varchar(500)='',
      @gender     int
     
AS
BEGIN
      insert into userMaster(userName,city,Gender) values (@userName,@city,@gender)
END
GO
/****** Object:  StoredProcedure [dbo].[SP_user_delete]    Script Date: 04/22/2015 09:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_user_delete]
      @userId     int =0
     
     
AS
BEGIN
      delete from userMaster
      where userId=@userId
END
GO
/****** Object:  StoredProcedure [dbo].[sp_Product]    Script Date: 04/22/2015 09:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_Product]

AS
BEGIN

      SET NOCOUNT ON;
      select * from ProductDemo
END
GO

 
VIEW
LIST USER
@model  IEnumerable<MvcTest.Models.userModel>
@{
    ViewBag.Title = "userList";
}

<h2>userList</h2>


<a href="@Url.Action("addUser", "User")">Add User</a>
<table>

    <tr>
        <th>UserName</th>
        <th>City</th>
        <th>Gender</th>
        <th>Edit</th>
        <th>Delete</th>

    </tr>
    @foreach (var model in Model)
    {
        <tr>
            <td>@Html.DisplayFor(m => model.userName)</td>
            <td>@Html.DisplayFor(m => model.city)</td>
            <td>@Html.DisplayFor(m => model.gender)</td>

            <td><a href="@Url.Action("editUser", "User", new { id = model.userId })">Edit</a></td>
            <td>

                <a href="@Url.Action("delete", "User", new { id = model.userId })" onclick="alert('Are you sure you want to delete?');">Delete</a></td>
        </tr>
    }
</table>

Edit User

@model MvcTest.Models.userModel

@{
    ViewBag.Title = "editUser";
}

<h2>editUser</h2>

@using (Html.BeginForm("editUser", "User", FormMethod.Post))
{
    <table>
        <tr>
            <td>UserName</td>
            <td>@Html.TextBoxFor(model => model.userName)</td>
             @Html.HiddenFor(model => model.userId)
        </tr>
        <tr>
            <td>city</td>
            <td>@Html.TextBoxFor(model => model.city)</td>
        </tr>
        <tr>
            <td>Gender</td>
            <td>@Html.TextBoxFor(model => model.gender)</td>

        </tr>


    




    </table>
  
        <button type="submit" name="Save">Save</button>








}

Add user

@model MvcTest.Models.userModel

@{
    ViewBag.Title = "addUser";
}


<h2>addUser</h2>
<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>
@using (Html.BeginForm("addUser", "User", FormMethod.Post))
{
    @Html.ValidationSummary(true)
    <table>
        <tr>
            <td>UserName</td>
            <td>@Html.TextBoxFor(model => model.userName)    @Html.ValidationMessageFor(m => m.userName)</td>

        </tr>
        <tr>
            <td>city</td>
            <td>@Html.TextBoxFor(model => model.city)    @Html.ValidationMessageFor(model => model.city)</td>

        </tr>
        <tr>
            <td>Gender</td>
            <td>     MALE @Html.RadioButtonFor(model => model.gender,0)
                     FEMALE @Html.RadioButtonFor(model => model.gender,1)
            </td>

                        
           
        </tr>
    </table>
    <button type="submit" name="Save">Save</button>








}


BIND DROPDOWN
=================

DYNAMIC
View
<div class="form_div">
                        <div class="lable_text">
                            CountryName <span>*</span>
                        </div>
                        <div style="float: left;">
                            &nbsp;:&nbsp;
                        </div>
                        <div class="input2">
                              @Html.DropDownList("CountryDropDownList",ViewBag.CountryDropDownList as SelectList, string.Empty,
                           new { @class = "form-control" })
                        </div>
                    </div>

                    <div class="form_div">
                        <div class="lable_text">
                            Gender<span>*</span>
                        </div>
                        <div style="float: left;">
                            &nbsp;:&nbsp;
                        </div>
                        <div class="input2" style="background:none">
                            @if (Model.gender == 1)
                            {
                                <input type="radio" name="gender" value="1" style="float:left;" checked="checked"/><span>Male</span>
                                <input type="radio" name="gender" value="0" /><span>Female</span>
                            }
                            else
                            {
                                <input type="radio" name="gender" value="1" style="float:left;" /><span>Male</span>
                                <input type="radio" name="gender" value="0" checked="checked"/><span>Female</span>
                            }
                        </div>
                    </div>
===================================================================
[HttpGet]
        public ActionResult EditUser(int id = 0)
        {
            Pro_UserMaster model = new UserRepository().GetUserByIdAP(Convert.ToString(id));
            ViewBag.CountryDropDownList = BindCountryNameList(model);
            return View(model);
        }
        public List<SelectListItem> BindCountryNameList(Pro_UserMaster country)
        {
            List<SelectListItem> CountryDropDownList = new List<SelectListItem>();
            IEnumerable<Pro_CountryMaster_New> objUserName = new List<Pro_CountryMaster_New>();
            objUserName = new UserRepository().GetCountriesForAPI();
            foreach (var item in objUserName)
            {
                CountryDropDownList.Add(new SelectListItem { Text = item.countryName, Value = Convert.ToString(item.countryId), Selected = false });
            }
            foreach (SelectListItem sli in CountryDropDownList)
            {
                if (sli.Text.ToString() == country.countryName.ToString())
                {
                    sli.Selected = true;
                    break;
                }
            }
            return CountryDropDownList;
        }

STATIC
public ActionResult AddQuestion()
        {
            Pro_Questions model = new Pro_Questions();
            List<SelectListItem> CategoryDropDownList = new List<SelectListItem>();
            CategoryDropDownList.Add(new SelectListItem { Text = "Option1", Value = "1" });
            CategoryDropDownList.Add(new SelectListItem { Text = "Option2", Value = "2" });
            CategoryDropDownList.Add(new SelectListItem { Text = "Option3", Value = "3" });
            CategoryDropDownList.Add(new SelectListItem { Text = "Option4", Value = "4" });
            ViewBag.QuestionOptionsDropdownList = CategoryDropDownList;

            List<SelectListItem> UserNameDropDownList = new List<SelectListItem>();
            IEnumerable<Pro_UserMaster> objUserName = new List<Pro_UserMaster>();
            objUserName = new UserRepository().GetUserNameAndIdList();
            foreach (var item in objUserName)
            {
                UserNameDropDownList.Add(new SelectListItem { Text = item.userName, Value = Convert.ToString(item.userId), Selected = true });
            }
            ViewBag.UserNameDropDownList = UserNameDropDownList;

            return View(model);
        }
 

getvalue from dropdown
======================

on post method


strring str=Request.Form["CountryDropDownList"];


      if (Request.Form["CountryDropDownList"] != "")
            {
                List<Pro_CountryMaster_New> objUserName = new List<Pro_CountryMaster_New>();
                objUserName = new UserRepository().GetCountriesForAPI().FindAll(Pro_CountryMaster_New => Pro_CountryMaster_New.countryId == Convert.ToInt32(Request.Form["CountryDropDownList"]));
                argPro_UserMaster.countryName = Convert.ToString(objUserName[0].countryName);
            }


Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)