ASP.NET MVC控制器中通过REST API填充MS SQL数据库失败求助

我正在开发基于ASP.NET MVC的CRUD应用，计划通过REST API获取数据填充自有MS SQL数据库，但尝试从API获取数据并插入数据库后，查询发现数据库始终为空，请求协助排查解决。

现有代码 #

控制器Index方法代码 #

public async Task<IActionResult> Index()
{
    List<Employee> EmpInfo = new List<Employee>();
    string Baseurl = "https://northwind.vercel.app/api/employess";
    string connectionUrl = "Server=(localdb)\\mssqllocaldb;Database=aspnet-WebApplication3-020c3741-9f8f-428c-848a-6bfe5a7d80c9;Trusted_Connection=True;MultipleActiveResultSets=true";

    using (var client = new HttpClient())
    {
        client.BaseAddress = new Uri(Baseurl);
        client.DefaultRequestHeaders.Clear();
        //Define request data format  
        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

        //get data from nw api 
        HttpResponseMessage Res = await client.GetAsync("northwind.now.sh/api/employess");

        // check for con success 
        if (Res.IsSuccessStatusCode)
        {
            //Storing the response details recieved from web api   
            var EmpResponse = Res.Content.ReadAsStringAsync().Result;
            EmpInfo = JsonConvert.DeserializeObject<List<Employee>>(EmpResponse);
        }

        using (SqlConnection connection = new SqlConnection(connectionUrl))
        {
            connection.Open();
            foreach (int i = 0;i < EmpInfo.Count;i++) 
            {
                string sqlCommandText = "INSERT INTO Employee VALUES(@id, @lastName, @firstName,@title,@titleOfCourtesy,@birthDate,@hireDate,@reportsTo)";

                using (SqlCommand command = new SqlCommand(sqlCommandText, connection)
                {
                    command.Parameters.AddWithValue("@id", EmpInfo.ElementAt(i).id; // Replace with your actual values
                    command.Parameters.AddWithValue("@lastName", EmpInfo.ElementAt(i).lastName);
                    command.Parameters.AddWithValue("@firstName", EmpInfo.ElementAt(i).firstName);
                    command.Parameters.AddWithValue("@title", EmpInfo.ElementAt(i).title); // Replace with your actual values
                    command.Parameters.AddWithValue("@titleOfCourtesy", EmpInfo.ElementAt(i).titleOfCourtesy);
                    command.Parameters.AddWithValue("@birthDate", EmpInfo.ElementAt(i).birthDate);
                    command.Parameters.AddWithValue("@hireDate", EmpInfo.ElementAt(i).hireDate); // Replace with your actual values
                    command.Parameters.AddWithValue("@reportsTo", EmpInfo.ElementAt(i).reportsTo);

                    // Execute the command
                    command.ExecuteNonQuery();
                }
            } 
        }
        //returning the employee list to view  
        return View(EmpInfo);
    }
}

Employee模型代码 #

namespace WebApplication3.Models
{
    public class Employee
    {
        public int id { get; set; }
        public string lastName { get; set; }
        public string firstName { get; set; }
        public string title { get; set; }
        public string titleOfCourtesy { get; set; }
        public string birthDate { get; set; }
        public string hireDate { get; set; }
        public Address Address { get; set; }
        public string reportsTo { get; set; }

        public Employee()
        {
        }
    }
}

核心问题排查与修复方案 #

1. API地址错误与拼写问题 #

• 原代码中Baseurl与GetAsync调用的域名不一致，且**employess是拼写错误，正确应为employees**，导致API请求失败，无法获取数据。
• 修复：统一API地址，使用正确端点：

  string Baseurl = "https://northwind.vercel.app/api/";
  HttpResponseMessage Res = await client.GetAsync("employees");
  

2. 异步方法同步调用导致死锁 #

• Res.Content.ReadAsStringAsync().Result会阻塞线程，引发异步流程死锁，导致数据无法正常读取。
• 修复：改用await调用异步方法：

  var EmpResponse = await Res.Content.ReadAsStringAsync();
  

3. 语法错误导致代码无法执行 #

• new SqlCommand(sqlCommandText, connection)和command.Parameters.AddWithValue("@id", EmpInfo.ElementAt(i).id均缺少闭合括号，代码编译失败，直接无法运行。
• 修复：补充完整括号：

  using (SqlCommand command = new SqlCommand(sqlCommandText, connection))
  {
      command.Parameters.AddWithValue("@id", EmpInfo.ElementAt(i).id);
      // ... 其他参数
  }
  

4. 字段类型不匹配导致反序列化失败 #

• 原模型中reportsTo定义为string，但Northwind API返回的reportsTo是int类型（可能为null），反序列化失败后EmpInfo为空列表，自然无数据插入。
• 修复：调整模型字段类型：

  public int? reportsTo { get; set; }
  

5. 异常处理缺失 #

• 无异常捕获逻辑，插入数据库失败时无法得知错误原因（如表结构不匹配、主键重复等）。
• 修复：添加try-catch块捕获异常，便于调试：

  try
  {
      // API请求与数据库插入逻辑
  }
  catch (Exception ex)
  {
      // 记录日志或返回错误视图
      return View("Error", ex.Message);
  }
  

6. 重复插入问题 #

• 每次访问Index页面都会执行插入逻辑，易引发主键冲突。
• 修复：插入前检查数据库中是否已存在该ID的员工：

  string checkExistSql = "SELECT COUNT(1) FROM Employee WHERE id = @id";
  using (SqlCommand checkCmd = new SqlCommand(checkExistSql, connection))
  {
      checkCmd.Parameters.AddWithValue("@id", EmpInfo[i].id);
      int count = (int)checkCmd.ExecuteScalar();
      if (count == 0)
      {
          // 执行插入逻辑
      }
  }
  

修复后的完整控制器代码示例 #

public async Task<IActionResult> Index()
{
    List<Employee> EmpInfo = new List<Employee>();
    string Baseurl = "https://northwind.vercel.app/api/";
    string connectionUrl = "Server=(localdb)\\mssqllocaldb;Database=aspnet-WebApplication3-020c3741-9f8f-428c-848a-6bfe5a7d80c9;Trusted_Connection=True;MultipleActiveResultSets=true";

    try
    {
        using (var client = new HttpClient())
        {
            client.BaseAddress = new Uri(Baseurl);
            client.DefaultRequestHeaders.Clear();
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            HttpResponseMessage Res = await client.GetAsync("employees");
            if (Res.IsSuccessStatusCode)
            {
                var EmpResponse = await Res.Content.ReadAsStringAsync();
                EmpInfo = JsonConvert.DeserializeObject<List<Employee>>(EmpResponse);
            }
        }

        if (EmpInfo.Any())
        {
            using (SqlConnection connection = new SqlConnection(connectionUrl))
            {
                await connection.OpenAsync();
                foreach (var emp in EmpInfo)
                {
                    // 检查是否已存在
                    string checkExistSql = "SELECT COUNT(1) FROM Employee WHERE id = @id";
                    using (SqlCommand checkCmd = new SqlCommand(checkExistSql, connection))
                    {
                        checkCmd.Parameters.AddWithValue("@id", emp.id);
                        int count = (int)await checkCmd.ExecuteScalarAsync();
                        if (count == 0)
                        {
                            string sqlCommandText = @"INSERT INTO Employee 
                                                    (id, lastName, firstName, title, titleOfCourtesy, birthDate, hireDate, reportsTo)
                                                    VALUES(@id, @lastName, @firstName, @title, @titleOfCourtesy, @birthDate, @hireDate, @reportsTo)";

                            using (SqlCommand command = new SqlCommand(sqlCommandText, connection))
                            {
                                command.Parameters.AddWithValue("@id", emp.id);
                                command.Parameters.AddWithValue("@lastName", emp.lastName ?? DBNull.Value);
                                command.Parameters.AddWithValue("@firstName", emp.firstName ?? DBNull.Value);
                                command.Parameters.AddWithValue("@title", emp.title ?? DBNull.Value);
                                command.Parameters.AddWithValue("@titleOfCourtesy", emp.titleOfCourtesy ?? DBNull.Value);
                                command.Parameters.AddWithValue("@birthDate", !string.IsNullOrEmpty(emp.birthDate) ? DateTime.Parse(emp.birthDate) : (object)DBNull.Value);
                                command.Parameters.AddWithValue("@hireDate", !string.IsNullOrEmpty(emp.hireDate) ? DateTime.Parse(emp.hireDate) : (object)DBNull.Value);
                                command.Parameters.AddWithValue("@reportsTo", emp.reportsTo ?? (object)DBNull.Value);

                                await command.ExecuteNonQueryAsync();
                            }
                        }
                    }
                }
            }
        }
        return View(EmpInfo);
    }
    catch (Exception ex)
    {
        // 可替换为日志记录逻辑
        return View("Error", new ErrorViewModel { RequestId = ex.Message });
    }
}

内容的提问来源于stack exchange，提问作者Talha Emre Ünal