How to Insert, Edit and Delete Data into Grid View Control Asp.Net MVC using stored procedure ?

Here’s a step-by-step guide on how to insert, edit, and delete data into a GridView control in ASP.NET MVC using stored procedures:

Step 1: Create the Database Table and Stored Procedures

1. Create a SQL Server database and a table to store the data.

sql

CREATE TABLE Products (

Id INT PRIMARY KEY IDENTITY(1,1),

Name VARCHAR(50) NOT NULL,

Price DECIMAL(18,2) NOT NULL

);

1. Create stored procedures for insert, update, and delete operations:

sql

— Insert Procedure

CREATE PROCEDURE InsertProduct

@Name VARCHAR(50),

@Price DECIMAL(18,2)

AS

BEGIN

INSERT INTO Products (Name, Price) VALUES (@Name, @Price)

END

— Update Procedure

CREATE PROCEDURE UpdateProduct

@Id INT,

@Name VARCHAR(50),

@Price DECIMAL(18,2)

AS

BEGIN

UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id

END

— Delete Procedure

CREATE PROCEDURE DeleteProduct

@Id INT

AS

BEGIN

DELETE FROM Products WHERE Id = @Id

END

Step 2: Create the Model

1. Create a model class named ProductModel.cs in the Models folder:

csharp

public class ProductModel

{

public int Id { get; set; }

public string Name { get; set; }

public decimal Price { get; set; }

}

Step 3: Create the Controller

1. Create a controller named ProductController.cs:

csharp

public class ProductController : Controller

{

private string connectionString = “YourConnectionStringHere”;

public ActionResult Index()

{

List<ProductModel> products = new List<ProductModel>();

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand cmd = new SqlCommand(“SELECT Id, Name, Price FROM Products”, conn);

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

products.Add(new ProductModel

{

Id = (int)reader[“Id”],

Name = reader[“Name”].ToString(),

Price = (decimal)reader[“Price”]

});

}

}

return View(products);

}

[HttpPost]

public ActionResult Insert(ProductModel model)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand cmd = new SqlCommand(“InsertProduct”, conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue(“@Name”, model.Name);

cmd.Parameters.AddWithValue(“@Price”, model.Price);

cmd.ExecuteNonQuery();

}

return RedirectToAction(“Index”);

}

[HttpPost]

public ActionResult Update(ProductModel model)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand cmd = new SqlCommand(“UpdateProduct”, conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue(“@Id”, model.Id);

cmd.Parameters.AddWithValue(“@Name”, model.Name);

cmd.Parameters.AddWithValue(“@Price”, model.Price);

cmd.ExecuteNonQuery();

}

return RedirectToAction(“Index”);

}

[HttpPost]

public ActionResult Delete(int id)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand cmd = new SqlCommand(“DeleteProduct”, conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue(“@Id”, id);

cmd.ExecuteNonQuery();

}

return RedirectToAction(“Index”);

}

}

Step 4: Create the Index View

1. Create the Index view named Index.cshtml:

xml

@model IEnumerable<YourNamespace.Models.ProductModel>

@{

ViewBag.Title = “Products”;

}

<h2>Products</h2>

<table class=”table”>

<thead>

<tr>

<th>Name</th>

<th>Price</th>

<th>Actions</th>

</tr>

</thead>

<tbody>

@foreach (var product in Model)

{

<tr>

<td>@product.Name</td>

<td>@product.Price</td>

<td>

<button class=”btn btn-primary btn-sm edit-product” data-id=”@product.Id” data-name=”@product.Name” data-price=”@product.Price”>Edit</button>

<button class=”btn btn-danger btn-sm delete-product” data-id=”@product.Id”>Delete</button>

</td>

</tr>

}

</tbody>

</table>

<div class=”modal fade” id=”editModal” tabindex=”-1″ role=”dialog”>

<div class=”modal-dialog” role=”document”>

<div class=”modal-content”>

<div class=”modal-header”>

<h5 class=”modal-title”>Edit Product</h5>

<button type=”button” class=”close” data-dismiss=”modal” aria-label=”Close”>

<span aria-hidden=”true”>&times;</span>

</button>

</div>

<div class=”modal-body”>

<input type=”hidden” id=”editId” />

<div class=”form-group”>

<label for=”editName”>Name</label>

<input type=”text” class=”form-control” id=”editName” />

</div>

<div class=”form-group”>

<label for=”editPrice”>Price</label>

<input type=”number” class=”form-control” id=”editPrice” />

</div>

</div>

<div class=”modal-footer”>

<button type=”button” class=”btn btn-primary” id=”saveChanges”>Save changes</button>

<button type=”button” class=”btn btn-secondary” data-dismiss=”modal”>Close</button>

</div>

</div>

</div>

</div>

<script src=”https://code.jquery.com/jquery-3.6.0.min.js”></script>

<script src=”https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js”></script>

<script>

$(document).ready(function () {

$(“.edit-product”).click(function () {

var id = $(this).data(“id”);

var name = $(this).data(“name”);

var price = $(this).data(“price”);

$(“#editId”).val(id);

$(“#editName”).val(name);

$(“#editPrice”).val(price);

$(“#editModal”).modal(“show”);

});

$(“#saveChanges”).click(function () {

var id = $(“#editId”).val();

var name = $(“#editName”).val();

var price = $(“#editPrice”).val();

$.ajax({

url: “@Url.Action(“Update”, “Product”)”,

type: “POST”,

data: { id: id, name: name, price: price },

success: function () {

location.reload();

}

});

});

$(“.delete-product”).click(function () {

var id = $(this).data(“id”);

if (confirm(“Are you sure you want to delete this product?”)) {

$.ajax({

url: “@Url.Action(“Delete”, “Product”)”,

type: “POST”,

data: { id: id },

success: function () {

location.reload();

}

});

}

});

});

</script>

Step 5: Configure the Connection String

1. Update the connection string in the Web.config file:

xml

<connectionStrings>

<add name=”DefaultConnection” connectionString=”Data Source=YOUR_SERVER;Initial Catalog=YourDatabaseName;Integrated Security=True” providerName=”System.Data.SqlClient” />

</connectionStrings>

Step 6: Run the Application

1. Start the application and navigate to the products page.
2. You can insert a new product by filling in the name and price and clicking the “Save” button.
3. To edit a product, click the “Edit” button, update the name and price, and click “Save changes”.
4. To delete a product, click the “Delete” button and confirm the deletion.

Step-by-Step Algorithm: CRUD with GridView and Stored Procedures (ASP.Net MVC)

This algorithm outlines the steps for implementing CRUD (Create, Read, Update, Delete) operations on data displayed in a GridView control using stored procedures in ASP.Net MVC.

1. Model Definition

• Define a model class representing a single data record displayed in the GridView.
• The model properties should correspond to the columns returned by your stored procedures.

2. Stored Procedure Creation

• Create stored procedures for each CRUD operation:
  • Select: Retrieve data for populating the GridView.
  • Insert: Insert a new data record.
  • Update: Update an existing data record.
  • Delete: Delete a data record.

3. Database Connection (Optional):

• Establish a connection string in your web.config file to connect to the database.
• Alternatively, use dependency injection to manage database connections.

4. Controller Implementation

A. Index Action Method (Read):

• Create an action method named Index to display the initial GridView.
• Use Entity Framework or another data access layer to call the “Select” stored procedure.
• Pass the retrieved data to the view as a model collection.

B. Create Action Method:

• Create an action method named Create to handle adding a new record.
• This method should receive the new data as parameters based on your model properties.
• Use Entity Framework or your data access layer to call the “Insert” stored procedure with the new data.
• Redirect to the Index action method to refresh the GridView.

C. Edit Action Method:

• Create an action method named Edit to handle editing an existing record.
• This method can receive the record ID as a parameter.
• Use Entity Framework or your data access layer to retrieve the specific record data based on the ID.
• Pass the retrieved data to the view as a model object for editing.

D. Update Action Method:

• Create an action method named Update to handle saving changes to an existing record.
• This method should receive the edited data as parameters based on your model properties.
• Use Entity Framework or your data access layer to call the “Update” stored procedure with the edited data.
• Redirect to the Index action method to refresh the GridView.

E. Delete Action Method:

• Create an action method named Delete to handle deleting a record.
• This method can receive the record ID as a parameter.
• Use Entity Framework or your data access layer to call the “Delete” stored procedure with the ID.
• Redirect to the Index action method to refresh the GridView (optional, you can display a confirmation message first).

5. View Implementation (cshtml):

A. GridView:

• Use the @Html.GridView helper to render the GridView control.
• Set the DataSource property to the model collection passed from the controller’s Index action method.
• Define columns for each model property you want to display.

B. Create Form:

• Create a form for adding new records (optional, can be a separate view or modal).
• Include input elements corresponding to each model property for user input.
• Use the @Html.ActionLink or @Url.Action helper to link the form submission to the controller’s Create action method.

C. Edit Form (Optional):

• In the Edit view, display the retrieved record data in pre-populated input elements.
• Use the @Html.ActionLink or @Url.Action helper to link the form submission to the controller’s Update action method, passing the record ID.

D. Delete Confirmation (Optional):

• Implement a confirmation dialog before deleting a record using JavaScript or a separate action method for confirmation.

6. Data Binding and Stored Procedure Calls:

• Use data binding syntax (@Model.<property>) to bind data from the controller’s model to the GridView and edit form elements.
• Use Entity Framework or your chosen data access layer to execute stored procedures within the controller action methods. Pass any necessary parameters (e.g., record ID).

7. Error Handling:

• Implement error handling in the controller for potential database errors or invalid user input during CRUD operations.

Benefits:

• Simplifies data manipulation and management within your ASP.Net MVC application.
• Leverages stored procedures for database operations, promoting code reusability and maintainability.

Note:This is a generic algorithm. The specific implementation specifics will rely on your database schema, stored procedure structure, and intended user interface.

This implementation employs a SQL Server database and stored procedures to perform insert, update, and delete actions. The GridView is replaced with a table, and the edit and remove functions are implemented using JavaScript. You can improve this example by including features such as validation, error handling, and paging.