Sunday, 20 October 2013

MVC4 Razor:How to call Stored Procedure using C#

Hi friends,in this article I will explain about How to call Stored Procedure in MVC4 Razor.
I already explained in the previous articles about MVC4 Razor:How to retrieve view values on POST method in Controller using FormCollection,Mvc 4 Razor CRUD Operation using C# and How to create a dropdown list field for MVC4 Razor view?
Open SQL Server and write the below stored procedure and Execute.
Stored Procedure:
CREATE PROCEDURE AddStudent
                @firstname varchar(20),
                @lastname varchar(20),
                @address varchar(100),
                @retval int output
AS
BEGIN
                SET NOCOUNT ON;
                IF NOT EXISTS(SELECT * FROM student_data WHERE firstname =@firstname and lastname=@lastname )
                BEGIN
    INSERT INTO student_data (FIRSTNAME ,LASTNAME ,ADDRESS ) VALUES (@firstname ,@lastname ,@address )
    SET @retval=1
                END
                ELSE
                BEGIN
                SET @retval =0
                END     
END
GO


Open Visual Studio and take one Model Class Account.cs and write the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MvcCRUD.Models
{
    public class Account
    {
        public string firstName { get; set; }
        public string lastname { get; set; }
        public string address { get; set; }
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        public int Register()
        {
            SqlCommand cmd = new SqlCommand("AddStudent", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@firstname", firstName);
            cmd.Parameters.AddWithValue("@lastname", lastname);
            cmd.Parameters.AddWithValue("@address", address);
            SqlParameter ret = new SqlParameter("@retval", SqlDbType.Int);
            ret.Direction =ParameterDirection .Output;
            cmd.Parameters.Add(ret);
            con.Open();
            int retval = Convert.ToInt32(cmd.ExecuteNonQuery());
            con.Close();
            return retval;
        }
    }
}

Take one Controller AccountController.cs and call the Stored procedure in Controller as shown below.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcCRUD.Models;

namespace MvcCRUD.Controllers
{
    public class AccountController : Controller
    {    
        public ActionResult Register()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Register(FormCollection frm)
        {
            Account obj = new Account();
            obj.firstName = frm[0].ToString();
            obj.lastname = frm[1].ToString();
            obj.address = frm[2].ToString();
            int retval = Convert.ToInt32 (obj.Register());
            if (retval == 1)
            {
                return View("~/Home/index");
            }
            else
            {
                return View("Register");
            }
        }
    }
}

Right click on the Register() function in the AccountController.cs  and create one view as Register.cshtml and it will create the view as shown below.
@model MvcCRUD.Models.Account

@{
    ViewBag.Title = "Register";
}

<h2>Register</h2>

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>Account</legend>

        <div class="editor-label">
            @Html.LabelFor(model => model.firstName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.firstName)
            @Html.ValidationMessageFor(model => model.firstName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.lastname)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.lastname)
            @Html.ValidationMessageFor(model => model.lastname)
        </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>

        <p>
            <input type="submit" value="Create" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

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

And run the application,the output as shown in the below figure when you click on create button It will automatically save the record.
"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook pageAspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter  on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on  RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog  for more articles." 
"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook page Aspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog for more articles."

7 comments:

  1. hi, please help me with this question related to the above scenario.
    i have a controller that should check for controllername adn windows login name, if both satisfies a condition then user is redirected to a particular action on the same controller.

    The condition above spoken is acheived by a public class file in a businesslayer.
    please see the code and tell em what could be wrong:
    Controller:
    public ActionResult Index()
    {
    FileUploadCheckPoint checkpointsdata = new FileUploadCheckPoint();

    checkpointsdata.CDSID = System.Environment.UserName;
    checkpointsdata.ControllerName = System.Web.HttpContext.Current.Request.RequestContext.RouteData.Values["controller"].ToString();
    int retval = Convert.ToInt32(checkpointsdata.CheckAccess());
    //checkpointsdata.username= "someuser1";
    //checkpointsdata.ControllerName = "CorporateHRBenefitsDocumentsAdmin";
    //var retval = checkpointsdata.CheckAccess();
    ViewBag.Message = retval;
    if (retval == 1)
    {
    return View("Upload");
    }
    else if (retval == 0)
    {
    TempData["notice"] = "You do not have permissions to upload files, please contact the admin for more information.";
    return View("Index");
    }
    else
    {
    TempData["notice"] = "No valid permissions to acces the page.";
    return View("Index");
    }

    }
    ==================
    fileuploadcheckpoint.cs:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;

    namespace solutionname.Models
    {
    public class FileUploadCheckPoint
    {
    public string ControllerName { get; set; }
    public string CDSID { get; set; }
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
    public int CheckAccess()
    {
    FileUploadCheckPoint fileuploadcheckpointdata = new FileUploadCheckPoint();
    SqlCommand cmd = new SqlCommand("spGetStatusforUserRoleControllersAccess", con);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@username", username);
    cmd.Parameters.AddWithValue("@ControllerName", ControllerName);

    SqlParameter ret = new SqlParameter("@retval", SqlDbType.Int);
    ret.Size = 50;
    ret.Direction =ParameterDirection.Output;
    cmd.Parameters.Add(ret);
    con.Open();
    int retval = Convert.ToInt32(cmd.ExecuteNonQuery());
    con.Close();
    return retval;

    }


    }
    }
    =============
    index.cshtml
    @model solutionname.Models.FileUploadCheckPoint

    just has viewbag.message
    ===========sql stored procedure==========
    ALTER PROCEDURE [dbo].[spGetStatusforUserRoleControllersAccess]
    (@ControllerName nvarchar(50), @username nvarchar(50), @retval int OUTPUT)
    AS
    Begin
    --SET NOCOUNT ON;
    IF(SELECT urc.IsActive
    FROM [TDMIntranet_DEV].[dbo].[tbl_UserRoleControllers] urc,
    dbo.tbl_Users tu,
    dbo.tbl_Roles tr,
    dbo.tbl_Controllers tc
    where urc.userid=tu.userid
    and urc.roleid=tr.roleid
    and urc.ControllerID=tc.ControllerID
    and tu.username= @username
    and tc.ControllerName = @ControllerName) ='Y'
    Set @retval = 1
    Else
    set @retval = 0

    End

    ==========

    and thanks for so many articles that are really useful for beginners like me.

    Appreciate all your efforts and help.

    ReplyDelete
  2. Finally, I found the fix for my problem above posted:
    In the fileuploadcheckpoint.cs in the Models folder:
    Block of code that needs a change:
    con.Open();
    //earlier below line was :int retval = Convert.ToInt32(cmd.ExecuteNonQuery()); and instead it should be like below
    Int16 retval = Convert.ToInt16(cmd.Parameters["@retval"].Value);
    con.Close();

    ------------Thanks a lot to the admin of the blog, this article is really helpful to people like me and many others.

    Appreciate all your efforts!

    ReplyDelete
    Replies
    1. Hi Thank you for your appreciation.

      Delete
  3. whoah this weblog is great i like reading your posts. Stay up the good work!
    You recognize, a lot of persons are hunting around for this information, you could aid them greatly.


    Here is my web blog ... twitter followers (www.Buygoodfollowers.Co.uk)

    ReplyDelete

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.