Crud operations using stored procedure and entity framework in asp.net mvc

CRUD Operations using Stored Procedure and Entity Framework in ASP.NET MVC

In ASP.NET MVC, CRUD operations (Create, Read, Update, Delete) can be implemented using stored procedures and Entity Framework, which provides an object-relational mapping (ORM) framework for accessing databases.

1. Creating a Model

First, create a model class that represents the data you want to store in the database. For example, let’s create a “Product” model with properties like “Id”, “Name”, “Price”, and “Category”.

    
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Category { get; set; }
}

2. Creating the Database Context

Next, create a database context class that derives from the Entity Framework’s DbContext class. This class will define a DbSet for the “Product” model and establish a connection to the database.

    
public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
}

3. Creating Stored Procedures

Now, create the necessary stored procedures in your database to perform the CRUD operations on the “Product” table. This can be done using SQL Server Management Studio or any other database management tool.

For example, you can create stored procedures like “Product_Insert”, “Product_SelectAll”, “Product_Update”, and “Product_Delete” to handle the respective operations.

4. Implementing CRUD Operations

In the MVC Controller class, you can define methods to perform CRUD operations using the stored procedures and the Entity Framework’s database context.

4.1. Create Operation

To insert a new product into the database, you can create a method like the following:

    
public void Create(Product product)
{
    using (var db = new ApplicationDbContext())
    {
        db.Database.ExecuteSqlCommand("EXEC Product_Insert @Name, @Price, @Category",
            new SqlParameter("@Name", product.Name),
            new SqlParameter("@Price", product.Price),
            new SqlParameter("@Category", product.Category));
    }
}

4.2. Read Operation

To retrieve all products from the database, you can create a method like the following:

    
public List<Product> GetAll()
{
    using (var db = new ApplicationDbContext())
    {
        return db.Products.SqlQuery("EXEC Product_SelectAll").ToList();
    }
}

4.3. Update Operation

To update a product in the database, you can create a method like the following:

    
public void Update(Product product)
{
    using (var db = new ApplicationDbContext())
    {
        db.Database.ExecuteSqlCommand("EXEC Product_Update @Id, @Name, @Price, @Category",
            new SqlParameter("@Id", product.Id),
            new SqlParameter("@Name", product.Name),
            new SqlParameter("@Price", product.Price),
            new SqlParameter("@Category", product.Category));
    }
}

4.4. Delete Operation

To delete a product from the database, you can create a method like the following:

    
public void Delete(int id)
{
    using (var db = new ApplicationDbContext())
    {
        db.Database.ExecuteSqlCommand("EXEC Product_Delete @Id",
            new SqlParameter("@Id", id));
    }
}

4.5. Executing the Operations

You can now call these methods from your MVC Controller actions to perform the desired CRUD operations.

    
public class ProductController : Controller
{
    public ActionResult Create(Product product)
    {
        // Call the Create method
        // Redirect to the appropriate view
    }
    
    public ActionResult Index()
    {
        // Call the GetAll method
        // Pass the products list to the Index view
    }
    
    public ActionResult Edit(Product product)
    {
        // Call the Update method
        // Redirect to the appropriate view
    }
    
    public ActionResult Delete(int id)
    {
        // Call the Delete method
        // Redirect to the appropriate view
    }
}

Conclusion

This is a basic example of implementing CRUD operations using stored procedures and Entity Framework in ASP.NET MVC. Depending on your application requirements, you can modify and expand upon this approach to handle more complex scenarios.

Crud operations using stored procedure and entity framework in asp.net mvc