Inserting Data in Database using Stored Procedure in ASP.NET MVC
In ASP.NET MVC, you can insert data into a database using stored procedures. Stored procedures are pre-compiled queries that are stored in the database itself. They provide better performance and security compared to directly executing SQL statements.
Step 1: Create a Stored Procedure
The first step is to create a stored procedure in your database. Here’s an example of creating a simple stored procedure for inserting data into a table:
CREATE PROCEDURE InsertEmployee @FirstName nvarchar(50), @LastName nvarchar(50), @Email nvarchar(100) AS BEGIN INSERT INTO Employees (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email) END
Make sure to replace “Employees” with the actual table name in your database.
Step 2: Create a Model
Create a model class that represents the data you want to insert into the database. For example, if you have an “Employee” table with columns “FirstName”, “LastName”, and “Email”, create a corresponding “Employee” model class with properties for each column.
public class Employee
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
}
Step 3: Create a Controller
Create a controller class that handles the insertion of data into the database. Here’s an example of a controller action that calls the stored procedure to insert data:
public class EmployeeController : Controller
{
public ActionResult Insert(Employee employee)
{
using (SqlConnection connection = new SqlConnection("YourConnectionStringHere"))
{
SqlCommand command = new SqlCommand("InsertEmployee", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@FirstName", employee.FirstName);
command.Parameters.AddWithValue("@LastName", employee.LastName);
command.Parameters.AddWithValue("@Email", employee.Email);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
return RedirectToAction("Index");
}
}
Make sure to replace “YourConnectionStringHere” with the actual connection string for your database.
Step 4: Create a View
Create a view that allows users to enter the data for insertion. Here’s an example of a simple view with a form:
@model YourNamespace.Employee
@using (Html.BeginForm("Insert", "Employee", FormMethod.Post))
{
@Html.LabelFor(model => model.FirstName)
@Html.TextBoxFor(model => model.FirstName)
@Html.LabelFor(model => model.LastName)
@Html.TextBoxFor(model => model.LastName)
@Html.LabelFor(model => model.Email)
@Html.TextBoxFor(model => model.Email)
}
Make sure to replace “YourNamespace” with the actual namespace of your model class.
Step 5: Test the Application
Run your application and navigate to the view you created. Fill in the form and click the “Submit” button. The data will be inserted into the database using the stored procedure you created.
This is a basic example of how to insert data into a database using a stored procedure in ASP.NET MVC. You can extend this by adding validation, error handling, and other features based on your requirements.