Today, In this article I am creating an application in ASP.NET MVC4 which is inserting, update and Delete value via Stored Procedure.
We can use all Stored Procedures in this article
- Insert Stored Procedure use when we create an account in this application
- Update Stored Procedure and select Stored Procedure used when we change the password.
- Delete Stored Procedure use when we delete the account in this application.
- And here is also use select Stored Procedure in isUserAndPasswordExist() the method this method is also used in Update Stored Procedure.
After staring this article we will create a table and Stored Procedure in your SQL Server.
Create table:
CREATE TABLE [dbo].[LoginInfo]
(
vName varchar(50),
vID varchar(20)PrimaryKey,
vPassword varchar(20)
)
Create a Stored Procedure:
For more information about creating stored procedure read below articles
https://www.mindstick.com/Articles/429/implementing-stored-procedure
https://www.mindstick.com/blog/200/sql-stored-procedure
https://www.mindstick.com/blog/65/creating-and-using-stored-procedure
Insert:
CREATE PROCEDURE sp_AddLoginDetails
@UserName varchar(50),
@LoginId varchar(20),
@Password varchar(20)
AS
begin
SET NOCOUNT ON;
insert into LoginInfo (vName,vID,vPassword)values (@UserName,@LoginId,@Password)
End
Update:
CREATE PROCEDURE sp_PasswordChange
@Password varchar(20),
@ID varchar(20)
AS
begin
SET NOCOUNT ON;
UPDATE LoginInfo SET vPassword = @Password WHERE vID=@ID
End
Delete:
CREATE PROCEDURE sp_DeleteAccount
@ID varchar(20)
AS
begin
SET NOCOUNT ON;
DELETE FROM LoginInfo WHERE vID = @ID
End
Select:
CREATE PROCEDURE sp_CheckUseAandPassword
@ID varchar(20),
@Password varchar(20)
AS
begin
SET NOCOUNT ON;
select vID,vPassword from LoginInfo where vID=@ID and vPassword=@Password
End
Before creating Stored Procedures and table we create an MVC4 application and write the code below:
Now create a model class for properties:
- Right-click on Models folder in Solution Explorer.
- Click on Add.
- Give the Class name.
- Press OK.
Below is my Model Class Code:
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace Zytron.Models
{
public class LoginDetails
{
[Required(ErrorMessage = "*")]
public string UserName
{
get;
set;
}
[Required(ErrorMessage = "*")]
public string LoginID
{
get;
set;
}
[DataType(DataType.Password)]
public string Password
{
get;
set;
}
[DataType(DataType.Password)]
public string OldPassword
{
get;
set;
}
}
}
My Solution Explorer:
After that, we will add the Connection String in the Web.Config file:
<connectionStrings>
<add name="ConnectionName"connectionString="Data Source=Your Server Name; Initial Catalog= Database; User Id= Your userID; Password= Your Password;"providerName="System.Data.SqlClient"/>
</connectionStrings>
Insert:
This index Action method called when my MVC application load the first time.
[HttpGet]
public ActionResult Index()
{
ViewBag.Message = "";
return View();
}
Output:
After clicking submit button control go on parameterizes Index Action method in Home Controller which will create an account that means insert value in the table via Stored Procedure.
Parameterizes Index Action method:
[HttpPost]
public ActionResult Index(Zytron.Models.LoginDetails model)
{
using (var con = new SqlConnection
(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))// This is give the connectionStrings on SqlConnection class’ Construrctor
{
var cmd = new SqlCommand("sp_AddLoginDetails", con);// Give The command to SqlCommand
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar)).Value = model.UserName.Trim();//Pass the parameter
cmd.Parameters.Add(new SqlParameter("@LoginId", SqlDbType.VarChar)).Value = model.LoginID.Trim();//Pass the parameter
cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = model.Password; //Pass the parameter
try
{
if (con.State != ConnectionState.Open)
con.Open();
cmd.ExecuteNonQuery();
ViewBag.message = "Account Created";
return View();
}
finally
{
if (con.State != ConnectionState.Closed)
con.Close();
}
}
return View();
}
View:
@model Zytron.Models.LoginDetails
@{
ViewBag.Title = "::Cretae Account::";
}
<h1>Cretae Account</h1>
<form id="frmLogin" method="post">
<div class="container">
<fieldset>
<legend class="legend"></legend>
<table cellpadding="10" cellspacing="0" width="100%">
<tr>
<td style="width: 20%;">
<span class="LabelFormat">UserName</span>
</td>
<td>
@Html.TextBoxFor(m => m.UserName, null, new { @class = "TextboxFormat", @style = " tabindex :0;" })
</td>
<td style="width: 5px;">
@Html.ValidationMessageFor(m => m.UserName, null, new { @class = "ErrorMessage", @id = "valMsgForFName" })
</td>
</tr>
<tr>
<td>
<span class="LabelFormat">LoginID</span>
</td>
<td>
@Html.TextBoxFor(m => m.LoginID, null, new { @class = "TextboxFormat", @style = " tabindex :1;" })
</td>
<td style="width: 5px;">
@Html.ValidationMessageFor(m => m.LoginID, null, new { @class = "ErrorMessage", @id = "valMsgForUName" })
</td>
</tr>
<tr>
<td>
<span class="LabelFormat">Password</span>
</td>
<td>
@Html.PasswordFor(m => m.Password, new { @class = "TextboxFormat", @style = " tabindex :1;" })
</td>
<td style="width: 5px;">
@Html.ValidationMessageFor(m => m.Password, null, new { @class = "ErrorMessage", @id = "valMsgForPwd" })
</td>
</tr>
<tr>
<td><p style="color:Red;">@ViewBag.message</p>
</td>
<td>
<input type="submit" value="Submit" name="Submit" />
</td>
</tr>
</table>
</fieldset>
</div>
</form>
Update:
View:
@model Zytron.Models.LoginDetails
@{
ViewBag.Title = "::Change Password::";
}
<h2>
Change Password</h2>
<form id="frmLogin" method="post">
<div class="container">
<fieldset>
<legend class="legend"></legend>
<table cellpadding="10" cellspacing="0" width="100%">
<tr>
<td>
<span class="LabelFormat">Enter LoginID</span>
</td>
<td>
@Html.TextBoxFor(m => m.LoginID, null, new { @class = "TextboxFormat", @style = " tabindex :1;" })
</td>
<td style="width: 5px;">
@Html.ValidationMessageFor(m => m.LoginID, null, new { @class = "ErrorMessage", @id = "valMsgForUName" })
</td>
</tr>
<tr>
<td>
<span class="LabelFormat">Enter Old Password</span>
</td>
<td>
@Html.PasswordFor(m => m.OldPassword, new { @class = "TextboxFormat", @style = " tabindex :1;" })
</td>
<td style="width: 5px;">
@Html.ValidationMessageFor(m => m.OldPassword, null, new { @class = "ErrorMessage", @id = "valMsgForPwd" })
</td>
</tr>
<tr>
<td>
<span class="LabelFormat">New Password</span>
</td>
<td>
@Html.PasswordFor(m => m.Password, new { @class = "TextboxFormat", @style = " tabindex :1;" })
</td>
<td style="width: 5px;">
@Html.ValidationMessageFor(m => m.Password, null, new { @class = "ErrorMessage", @id = "valMsgForPwd" })
</td>
</tr>
<tr>
<td><p style="color:Red;">@ViewBag.message</p>
</td>
<td>
<input type="submit" value="Change" name="Submit" />
</td>
</tr>
</table>
</fieldset>
</div>
</form>
Controller:
[HttpGet]
public ActionResult PassChange()
{
return View();
}
Above code load the Change password Page the first time:
Output:
When we click on Change Password menu and type the existing Login Id, old password and new password and click change button the control go to on the parameterizes PassChange() Action Method
In the PassChange() action method we write the code to update the password via Stored Procedure.
Code:
public ActionResult PassChange(Zytron.Models.LoginDetails model)
{
if (isUserAndPasswordExist(model.OldPassword.Trim(),model.LoginID.Trim(),"PasswordChange"))
{
using (var con = new SqlConnection
(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
{
var cmd = new SqlCommand("sp_PasswordChange", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = model.Password;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.VarChar)).Value = model.LoginID.Trim();
try
{
if (con.State != ConnectionState.Open)
con.Open();
cmd.ExecuteNonQuery();
}
finally
{
if (con.State != ConnectionState.Closed)
con.Close();
}
ViewBag.message = "Password Update Successfully...!!!";
return View();
}
}
else
ViewBag.message = "Password Not Changed...!!!";
return View();
}
The above code will update the password from the table.
isUserAndPasswordExist():this is a Boolean return type method which is created to check the user id and password is existed or not.
It has three parameters and we are also using the Stored procedure for select the data from tables.
1. Password
2. User Id
3. Check where from call this method. This means I am calling this method from passChange() action method as well Delete() action method.
Below is isUserAndPasswordExist() code:
private bool isPasswordExist(string OldPass, string userID,string str)
{
using (var con = new SqlConnection
(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
{
//var cmd = new SqlCommand("select vPassword from LoginInfo where vPassword='" + pass + "'", con);
var cmd = new SqlCommand("sp_CheckUseAandPassword", con); // here use Select Procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.VarChar)).Value = userID;
cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = OldPass;
con.Open();
var reader = cmd.ExecuteReader();
if (reader.Read())
{
if (str == "PasswordChange")
{
if (OldPass == reader[1].ToString())
return true;
else
return false;
}
else if (str == "Delete")
{
if (userID==reader[0].ToString() && OldPass == reader[1].ToString())
return true;
else
return false;
}
}
return false;
}
}
Delete:
View:
DeleteAccount.CHTML
@model Zytron.Models.LoginDetails
@{
ViewBag.Title = "DeleteAccount";
}
<h2>DeleteAccount</h2>
<form id="frmLogin" method="post">
<div class="container">
<fieldset>
<legend class="legend"></legend>
<table cellpadding="10" cellspacing="0" width="100%">
<tr>
<td>
<span class="LabelFormat">Enter LoginID</span>
</td>
<td>
@Html.TextBoxFor(m => m.LoginID, null, new { @class = "TextboxFormat", @style = " tabindex :1;" })
</td>
<td style="width: 5px;">
@Html.ValidationMessageFor(m => m.LoginID, null, new { @class = "ErrorMessage", @id = "valMsgForUName" })
</td>
</tr>
<tr>
<td>
<span class="LabelFormat">Password</span>
</td>
<td>
@Html.PasswordFor(m => m.Password, new { @class = "TextboxFormat", @style = " tabindex :1;" })
</td>
<td style="width: 5px;">
@Html.ValidationMessageFor(m => m.Password, null, new { @class = "ErrorMessage", @id = "valMsgForPwd" })
</td>
</tr>
<tr>
<td>@ViewBag.message
</td>
<td>
<input type="submit" value="Delete" name="Submit" />
</td>
</tr>
</table>
</fieldset>
</div>
</form>
Controller:
[HttpGet]
public ActionResult DeleteAccount()
{
return View();
}
Above code is loading the Delete Account view first time.
After loading the view on the browser the output is below:
This is before pressing the Delete button your table:
After pressing Delete key:
Above you will see the vs1491’s information will remove permanently from our table.
Conclusion:
In this article we understand how we insert, update, delete and select the data from your table using Stored Procedure.
anil babu
25-Jun-2013Horas Panjaitan
30-Mar-2013Hi Vijay Shuklacan youhelpme?I have adropdownlistthat containscode /id.
Iwant to takethe nameof thecodethatis inthe dropdownlist
this my code
protected void cmbroom_SelectedIndexChanged(object sender, EventArgs e)
{
kon = new MySqlConnection(ConfigurationManager.ConnectionStrings["mydatabase"].ConnectionString);
kon.Open();
string cmdText = "SELECT coderoom FROM tblroom WHERE coderoom=@coderoom";
data = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmdText, kon);
adapter.SelectCommand.Parameters.AddWithValue("@coderoom", cmbroom.SelectedValue);
cmbroom.SelectedIndex = -0;
adapter.Fill(data);
cmbroom.DataSource = data;
cmbroom.DataBind();
}
Ialsowant to display thename of the roomafter theselectcoderoom
Dorababu M
22-Jan-2013