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:
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."
hi, please help me with this question related to the above scenario.
ReplyDeletei 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.
Finally, I found the fix for my problem above posted:
ReplyDeleteIn 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!
Hi Thank you for your appreciation.
Deletewhoah this weblog is great i like reading your posts. Stay up the good work!
ReplyDeleteYou 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)
Thank You
Delete