使用ASP.NET MVC更新数据库

debugcn 发表于 Dev

敦古延

我的数据库中有一个表，如下所示：

Create table Employee
(
  Id int Primary Key Identity(1,1),
  Name nvarchar(50),
  Gender nvarchar(10),
  City nvarchar(50),
  Salary decimal(18,2),
  DateOfBirth DateTime
)
GO
-- Insert some test data into Employee table
Insert into Employee values('Pranaya','Male','Mumbai',4000,'02/03/1977')
Insert into Employee values('Anurag','Male','Hyderabad',5000,'04/06/1979')
Insert into Employee values('Priyanka','Female','Bangalore',1000,'01/05/1979')
Insert into Employee values('Subrat','Male','Hyderabad',2000,'03/07/1981')
Insert into Employee values('Sudhanshu','Male','Mumbai',3000,'02/04/1978')
Insert into Employee values('Preety','Female','Bangalore',4000,'02/03/1974')
Insert into Employee values('Sandeep','Male','Hyderabad',5000,'04/06/1972')
Insert into Employee values('Sambit','Male','Bangalore',6000,'07/05/1975')
Insert into Employee values('Hina','Female','Mumbai',3000,'09/08/1976')
GO

然后我创建一个存储过程来更新员工

Create procedure spUpdateEmployee      
  @Id int,
  @Name nvarchar(50),      
  @Gender nvarchar (10),      
  @City nvarchar (50), 
  @Salary decimal(18,2),     
  @DateOfBirth DateTime 
as      
Begin      
  Update Employee Set
    Name = @Name,
    Gender = @Gender,
    City = @City,
    Salary = @Salary,
    DateOfBirth = @DateOfBirth
  Where   Id = @Id
End

我创建模范员工

 public class employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
        public decimal Salary { get; set; }
        public DateTime DateOfBirth { get; set; }
    }

我创建类EmployeeBusinessLayer包含方法updateEmployee

public class EmployeeBusinessLayer
{
public void UpdateEmployee(employee e)
        {
            string connectionStrings = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
            using (SqlConnection con =new SqlConnection(connectionStrings))
            {
                SqlCommand cmd = new SqlCommand("spUpdateEmployee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter paId = new SqlParameter();
                paId.ParameterName = "@Id";
                paId.Value = e.ID;
                cmd.Parameters.Add(paId);

                SqlParameter paName= new SqlParameter();
                paId.ParameterName = "@Name";
                paId.Value = e.Name;
                cmd.Parameters.Add(paName);

                SqlParameter paGender = new SqlParameter();
                paId.ParameterName = "@Gender";
                paId.Value = e.Gender;
                cmd.Parameters.Add(paGender);

                SqlParameter paCity = new SqlParameter();
                paId.ParameterName = "@City";
                paId.Value = e.City;
                cmd.Parameters.Add(paCity);

                SqlParameter paSalary = new SqlParameter();
                paId.ParameterName = "@Salary";
                paId.Value = e.Salary;
                cmd.Parameters.Add(paSalary);

                SqlParameter paDate = new SqlParameter();
                paId.ParameterName = "@DateOfBirth";
                paId.Value = e.DateOfBirth;
                cmd.Parameters.Add(paDate);
                con.Open();
                cmd.ExecuteNonQuery();
            }

        }
}

我相信以上都是正确的。当我在EmployeeController中创建Edit操作时

[HttpGet]
        public ActionResult Edit(int id)
        {
            EmployeeBusinessLayer ebl = new EmployeeBusinessLayer();
            employee e = ebl.GetAllEmployess().FirstOrDefault(emp => emp.ID == id);
            return View(e);
        }



        [HttpPost]
        public ActionResult Edit(employee employee)
        {
            if (ModelState.IsValid)
            {
                EmployeeBusinessLayer employeeBusinessLayer = new EmployeeBusinessLayer();
                employeeBusinessLayer.UpdateEmployee(employee);
                return RedirectToAction("Index");
            }
            return View(employee);
        }

然后我从页面得到错误：过程或函数'spUpdateEmployee'期望未提供参数'@Id'。

我只是通过主要内容来说明我的问题，其余的我都可以肯定。能告诉我任何解决该错误的解决方案吗？我使用了Visual Studio 2019。

里沙卜·纳格帕尔

您可以按如下所示修改EmployeeBusinessLayer类中的UpdateEmployee方法

public class EmployeeBusinessLayer
{
public void UpdateEmployee(employee e)
        {
            string connectionStrings = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
            using (SqlConnection con =new SqlConnection(connectionStrings))
            {
                SqlCommand cmd = new SqlCommand("spUpdateEmployee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Id", e.ID);
                cmd.Parameters.AddWithValue("@Name", e.Name);
                cmd.Parameters.AddWithValue("@Gender", e.Gender);
                cmd.Parameters.AddWithValue("@City", e.City);
                cmd.Parameters.AddWithValue("@Salary", e.Salary);
                cmd.Parameters.AddWithValue("@DateOfBirth", e.DateOfBirth);
                con.Open();
                cmd.ExecuteNonQuery();
            }
        }
}

我对堆栈溢出的第一个答案。希望能帮助到你。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。