To save data into SQL Server and display it in a DataGridView (or a similar table format) on the same page using ASP.NET MVC with a stored procedure, follow these steps:
Step 1: Create the Stored Procedure
First, create a stored procedure in your SQL Server database to handle the insertion of employee data.
sql
CREATE PROCEDURE [dbo].[usp_InsertEmployee]
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Email VARCHAR(50),
@Phone VARCHAR(20),
@HireDate DATE,
@Department VARCHAR(50),
@Salary DECIMAL(10,2)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Employees (FirstName, LastName, Email, Phone, HireDate, Department, Salary)
VALUES (@FirstName, @LastName, @Email, @Phone, @HireDate, @Department, @Salary);
END
Step 2: Create the Employee Model
Create a model class that represents the employee data.
csharp
public class Employee
{
public int EmployeeID { get; set; } // Assuming this is an auto-incremented primary key
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
public DateTime HireDate { get; set; }
public string Department { get; set; }
public decimal Salary { get; set; }
}
Step 3: Create the Employee Controller
In the controller, create actions for displaying the registration form and handling form submissions. After saving the data, retrieve the updated list of employees to display.
csharp
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
public class EmployeeController : Controller
{
private string connectionString = “YourConnectionString”;
public ActionResult Index()
{
List<Employee> employees = GetEmployees();
return View(employees);
}
[HttpPost]
public ActionResult Index(Employee employee)
{
if (ModelState.IsValid)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(“usp_InsertEmployee”, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue(“@FirstName”, employee.FirstName);
command.Parameters.AddWithValue(“@LastName”, employee.LastName);
command.Parameters.AddWithValue(“@Email”, employee.Email);
command.Parameters.AddWithValue(“@Phone”, employee.Phone);
command.Parameters.AddWithValue(“@HireDate”, employee.HireDate);
command.Parameters.AddWithValue(“@Department”, employee.Department);
command.Parameters.AddWithValue(“@Salary”, employee.Salary);
connection.Open();
command.ExecuteNonQuery();
}
}
// Retrieve updated employee list
List<Employee> employees = GetEmployees();
return View(employees);
}
private List<Employee> GetEmployees()
{
List<Employee> employees = new List<Employee>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(“SELECT FROM Employees”, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
employees.Add(new Employee
{
EmployeeID = (int)reader[“EmployeeID”],
FirstName = reader[“FirstName”].ToString(),
LastName = reader[“LastName”].ToString(),
Email = reader[“Email”].ToString(),
Phone = reader[“Phone”].ToString(),
HireDate = (DateTime)reader[“HireDate”],
Department = reader[“Department”].ToString(),
Salary = (decimal)reader[“Salary”]
});
}
}
return employees;
}
}
Step 4: Create the View
Create a view to display the registration form and the list of employees. Use a form to submit new employee data and a table to display existing employees.
xml
@model IEnumerable<Employee>
@{
ViewBag.Title = “Employee Registration”;
}
<h2>Employee Registration</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class=”form-group”>
@Html.LabelFor(m => m.FirstName)
@Html.TextBox(“FirstName”, null, new { @class = “form-control” })
@Html.ValidationMessage(“FirstName”, “”, new { @class = “text-danger” })
</div>
<div class=”form-group”>
@Html.LabelFor(m => m.LastName)
@Html.TextBox(“LastName”, null, new { @class = “form-control” })
@Html.ValidationMessage(“LastName”, “”, new { @class = “text-danger” })
</div>
<div class=”form-group”>
@Html.LabelFor(m => m.Email)
@Html.TextBox(“Email”, null, new { @class = “form-control” })
@Html.ValidationMessage(“Email”, “”, new { @class = “text-danger” })
</div>
<div class=”form-group”>
@Html.LabelFor(m => m.Phone)
@Html.TextBox(“Phone”, null, new { @class = “form-control” })
@Html.ValidationMessage(“Phone”, “”, new { @class = “text-danger” })
</div>
<div class=”form-group”>
@Html.LabelFor(m => m.HireDate)
@Html.TextBox(“HireDate”, null, new { @class = “form-control”, type = “date” })
@Html.ValidationMessage(“HireDate”, “”, new { @class = “text-danger” })
</div>
<div class=”form-group”>
@Html.LabelFor(m => m.Department)
@Html.TextBox(“Department”, null, new { @class = “form-control” })
@Html.ValidationMessage(“Department”, “”, new { @class = “text-danger” })
</div>
<div class=”form-group”>
@Html.LabelFor(m => m.Salary)
@Html.TextBox(“Salary”, null, new { @class = “form-control”, type = “number”, step = “0.01” })
@Html.ValidationMessage(“Salary”, “”, new { @class = “text-danger” })
</div>
<button type=”submit” class=”btn btn-primary”>Register</button>
}
<h2>Employee List</h2>
<table class=”table”>
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Phone</th>
<th>Hire Date</th>
<th>Department</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
@foreach (var employee in Model)
{
<tr>
<td>@employee.FirstName</td>
<td>@employee.LastName</td>
<td>@employee.Email</td>
<td>@employee.Phone</td>
<td>@employee.HireDate.ToShortDateString()</td>
<td>@employee.Department</td>
<td>@employee.Salary.ToString(“C”)</td>
</tr>
}
</tbody>
</table>
Summary
This implementation allows you to:
- Insert employee data into the SQL Server database using a stored procedure.
- Retrieve and display the list of employees on the same page after a successful insertion.
When you submit the form, the new employee information is saved, and the revised employee list is presented immediately. Handle any potential problems and validate input data as needed to improve the user experience and data integrity.
