Model returned null报错求助：Excel导入SQL功能正常但持续报错

我开发了一个数据采集程序，用来将Excel文件导入SQL数据库。目前导入功能可以正常运行，但始终会弹出错误。已经反复检查过所有模型都有返回值，还是解决不了这个问题。

错误截图：

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["BoardExamConnectionstring"].ConnectionString);
OleDbConnection Econ;

private void ExcelConn(string filepath)
{
    string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filepath);
    Econ = new OleDbConnection(constr);
}

private void InsertExceldata(string fileepath, string filename)
{
    string fullpath = Server.MapPath("/excelfolder/") + filename;
    ExcelConn(fullpath);
    string query = string.Format("Select * from [{0}]", "Sheet1$");

    OleDbCommand Ecom = new OleDbCommand(query, Econ);
    Econ.Open();

    DataSet ds = new DataSet();
    OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);
    Econ.Close();
    oda.Fill(ds);

    DataTable dt = ds.Tables[0];

    SqlBulkCopy objbulk = new SqlBulkCopy(con);
    objbulk.DestinationTableName = "dbo.Student";
    objbulk.ColumnMappings.Add("StudentID", "StudentID");
    objbulk.ColumnMappings.Add("FirstName", "FirstName");
    objbulk.ColumnMappings.Add("LastName", "LastName");
    objbulk.ColumnMappings.Add("Program", "Program");
    objbulk.ColumnMappings.Add("YearGraduate", "YearGraduate");
    objbulk.ColumnMappings.Add("BoardScore", "BoardScore");

    con.Open();
    objbulk.WriteToServer(dt);
    con.Close();
}

StudentDAL _studentDAL = new StudentDAL();

// GET: Student
public ActionResult Index()
{
    var studentList = _studentDAL.GetallStudents();
    if (studentList.Count == 0)
    {
        TempData["InfoMessage"] = "Student data unavailable in the Database.";
    }
    return View(studentList);
}

[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
    string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
    string filepath = "/excelfolder/" + filename;
    file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
    InsertExceldata(filepath, filename);
    
    return View("index",new MyObject());
}

// GET: Student/Details/5
public ActionResult Details(int id)
{
    try
    {
        var student = _studentDAL.GetStudentByID(id).FirstOrDefault();
        if (student == null)
        {
            TempData["InfoMessage"] = "Student unavailable with ID" + id.ToString();
            return RedirectToAction("Index");
        }
        return View(student);
    }
    catch (Exception ex)
    {
        TempData["ErrorMessage"] = ex.Message;
        return View();
    }
}

// GET: Student/Create
public ActionResult Create()
{
    return View();
}

// POST: Student/Create
[HttpPost]
public ActionResult Create(Student student)
{
    bool IsInserted = false;
    try
    {
        if (ModelState.IsValid)
        {
            IsInserted = _studentDAL.InsertStudent(student);
            if (IsInserted)
            {
                TempData["SuccessMessage"] = "Student data saved successfully!";
            }
            else
            {
                TempData["ErrorMessage"] = "Unable to save student data.";
            }
        }
        return RedirectToAction("Index");
    }
    catch (Exception ex)
    {
        TempData["ErrorMessage"] = ex.Message;
        return View();
    }
}

// GET: Student/Edit/5
public ActionResult Edit(int id)
{
    var students = _studentDAL.GetStudentByID(id).FirstOrDefault();
    if (students == null)
    {
        TempData["InfoMessage"] = "Student unavailable with ID" + id.ToString();
        return RedirectToAction("Index");
    }
    return View(students);
}

// POST: Student/Edit/5
[HttpPost, ActionName("Edit")]
public ActionResult UpdateStudent(Student student)
{
    try
    {
        if (ModelState.IsValid)
        {
            bool IsUpdated = _studentDAL.UpdateStudent(student);
            if (IsUpdated)
            {
                TempData["SuccessMessage"] = "Student data updated successfully!";
            }
            else
            {
                TempData["ErrorMessage"] = "Unable to update student data.";
            }
        }
        return RedirectToAction("Index");
    }
    catch (Exception ex)
    {
        TempData["ErrorMessage"] = ex.Message;
        return View();
    }
}

// GET: Student/Delete/5
public ActionResult Delete(int id)
{
    try
    {
        var student = _studentDAL.GetStudentByID(id).FirstOrDefault();
        if (student == null)
        {
            TempData["InfoMessage"] = "Student unavailable with ID" + id.ToString();
            return RedirectToAction("Index");
        }
        return View(student);
    }
    catch (Exception ex)
    {
        TempData["ErrorMessage"] = ex.Message;
        return View();
    }
}

// POST: Student/Delete/5
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmation(int id)
{
    try
    {
        string result = _studentDAL.DeleteStudent(id);
        if (result.Contains("deleted"))
        {
            TempData["SuccessMessage"] = result;
        }
        else
        {
            TempData["ErrorMessage"] = result;
        }
        return RedirectToAction("Index");
    }
    catch (Exception ex)
    {
        TempData["ErrorMessage"] = ex.Message;
        return View();
    }
}

核心问题：视图模型类型不匹配 #

你的[HttpPost] Index方法最后返回的是new MyObject()，但Index视图是绑定到Student集合类型的（参考GET方法返回的studentList），类型不匹配直接导致了错误。

修正方法有两种：

1. 导入完成后重新获取学生列表，返回正确类型的数据：

[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
    string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
    string filepath = "/excelfolder/" + filename;
    file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
    InsertExceldata(filepath, filename);
    
    // 重新获取最新的学生列表
    var studentList = _studentDAL.GetallStudents();
    if (studentList.Count == 0)
    {
        TempData["InfoMessage"] = "Student data unavailable in the Database.";
    }
    return View(studentList);
}

2. 使用PRG（Post/Redirect/Get）模式，直接重定向到GET的Index方法，避免视图绑定问题：

[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
    string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
    string filepath = "/excelfolder/" + filename;
    file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
    InsertExceldata(filepath, filename);
    
    // 重定向到GET方法，自动获取最新数据
    return RedirectToAction("Index");
}

额外优化：规范数据库连接管理 #

你的代码中SqlConnection con是类级别的字段，复用同一个连接实例可能导致连接状态异常。建议在方法内使用using语句创建连接，确保连接自动释放：

private void InsertExceldata(string fileepath, string filename)
{
    string fullpath = Server.MapPath("/excelfolder/") + filename;
    ExcelConn(fullpath);
    string query = string.Format("Select * from [{0}]", "Sheet1$");

    DataSet ds = new DataSet();
    // 使用using管理OleDb连接
    using (OleDbConnection econ = Econ)
    {
        econ.Open();
        OleDbDataAdapter oda = new OleDbDataAdapter(query, econ);
        oda.Fill(ds);
    }
    DataTable dt = ds.Tables[0];

    // 使用using管理Sql连接
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["BoardExamConnectionstring"].ConnectionString))
    {
        SqlBulkCopy objbulk = new SqlBulkCopy(con);
        objbulk.DestinationTableName = "dbo.Student";
        objbulk.ColumnMappings.Add("StudentID", "StudentID");
        objbulk.ColumnMappings.Add("FirstName", "FirstName");
        objbulk.ColumnMappings.Add("LastName", "LastName");
        objbulk.ColumnMappings.Add("Program", "Program");
        objbulk.ColumnMappings.Add("YearGraduate", "YearGraduate");
        objbulk.ColumnMappings.Add("BoardScore", "BoardScore");
        
        con.Open();
        objbulk.WriteToServer(dt);
    }
}

内容的提问来源于stack exchange，提问作者juan miguel nieto