C#中如何从同一Customer类插入Address和Privacy数组数据？

问题背景 #

当前定义了Customer类，包含Address和Privacy两个集合属性，执行插入数据库的C#代码时，抛出「Not enough values for host variables」错误。相关代码及结构如下：

JSON结构 #

"customer": {               
    "address": [
      { 
           "city": "California",
           "country": "USA"
      }
  ],
     "privacy": [
      {
           "username": "James",
           "password": "123"
      }
  ]
}

C#实体类映射代码 #

public class Address
{
    public string city { get; set; }
    public string country { get; set; }
}

public class Customer
{
    public List<Address> address { get; set; }
    public List<Privacy> privacy { get; set; }
}

public class Privacy
{
    public string username { get; set; }
    public string password { get; set; }
}

public class Root
{
    public Customer customer { get; set; }
}

报错的插入代码 #

//insert
command= new OdbcCommand("INSERT INTO customer"
                          + "(@city,@country,@username,@password)"
                          + "VALUES(?,?,?,?)", DbConnection);
command.Connection = DbConnection;
command.Transaction = transaction;
command.CommandType = System.Data.CommandType.StoredProcedure;

//add values
//customer address[]
foreach (var customer_address in customer.address)
{           
  DbCommandInsertLeads.Parameters.AddWithValue("@city", (object)customer_address.city?? DBNull.Value);                 
  DbCommandInsertLeads.Parameters.AddWithValue("@country", (object)customer_address.country ?? DBNull.Value);
}

//customer privacy[]
foreach (var customer_privacy in customer.privacy)
{                 
  DbCommandInsertLeads.Parameters.AddWithValue("@username", (object)customer_privacy.username?? DBNull.Value);                                  
  DbCommandInsertLeads.Parameters.AddWithValue("@password", (object)customer_privacy.password?? DBNull.Value);
}

错误原因分析 #

1. SQL语句格式错误：
   • INSERT语句的列名部分格式错误，正确写法是(city, country, username, password)，而非带@前缀的(@city,@country,@username,@password)。
   • 错误设置了CommandType = StoredProcedure，但实际执行的是普通SQL语句，应该设为CommandType.Text。
2. 参数添加逻辑错误：
   • 循环添加参数会导致重复添加同名参数，ODBC无法匹配占位符数量。
   • 变量名混乱：定义的命令对象是command，但添加参数时用的是DbCommandInsertLeads，两者不匹配。
3. 数据结构与数据库表设计不匹配：
   • Customer类中的address和privacy是集合（一对多关系），但直接插入单张customer表不符合数据库设计规范，单表无法存储多条地址或隐私数据。

解决方案 #

方案1：修正单表插入逻辑（仅适用于每个Customer对应一条Address和一条Privacy的场景） #

如果数据库customer表确实包含city、country、username、password字段，且每个客户只有一条地址和隐私数据，修改代码如下：

// 修正SQL语句和命令类型
command = new OdbcCommand("INSERT INTO customer (city, country, username, password) VALUES (?, ?, ?, ?)", DbConnection);
command.Connection = DbConnection;
command.Transaction = transaction;
command.CommandType = System.Data.CommandType.Text; // 改为Text

// 获取第一条地址数据（需确保address集合不为空）
var address = customer.address.FirstOrDefault();
// 获取第一条隐私数据（需确保privacy集合不为空）
var privacy = customer.privacy.FirstOrDefault();

// 添加参数，避免重复添加
command.Parameters.AddWithValue("?", address?.city ?? DBNull.Value);
command.Parameters.AddWithValue("?", address?.country ?? DBNull.Value);
command.Parameters.AddWithValue("?", privacy?.username ?? DBNull.Value);
command.Parameters.AddWithValue("?", privacy?.password ?? DBNull.Value);

// 执行插入
command.ExecuteNonQuery();

方案2：按一对多关系设计数据库并修正插入逻辑（推荐） #

如果每个客户可能有多个地址或隐私数据，需拆分数据库表：

1. 创建主表customer：包含客户主键（如customer_id，自增）等基础字段。
2. 创建关联表customer_address：包含customer_id、city、country字段。
3. 创建关联表customer_privacy：包含customer_id、username、password字段。

然后按以下步骤插入数据：

// 1. 插入主表customer，获取自增ID
var insertCustomerCmd = new OdbcCommand("INSERT INTO customer DEFAULT VALUES; SELECT SCOPE_IDENTITY();", DbConnection);
insertCustomerCmd.Connection = DbConnection;
insertCustomerCmd.Transaction = transaction;
int customerId = Convert.ToInt32(insertCustomerCmd.ExecuteScalar());

// 2. 插入地址数据到customer_address表
foreach (var addr in customer.address)
{
    var insertAddrCmd = new OdbcCommand("INSERT INTO customer_address (customer_id, city, country) VALUES (?, ?, ?)", DbConnection);
    insertAddrCmd.Connection = DbConnection;
    insertAddrCmd.Transaction = transaction;
    insertAddrCmd.Parameters.AddWithValue("?", customerId);
    insertAddrCmd.Parameters.AddWithValue("?", addr.city ?? DBNull.Value);
    insertAddrCmd.Parameters.AddWithValue("?", addr.country ?? DBNull.Value);
    insertAddrCmd.ExecuteNonQuery();
}

// 3. 插入隐私数据到customer_privacy表
foreach (var priv in customer.privacy)
{
    var insertPrivCmd = new OdbcCommand("INSERT INTO customer_privacy (customer_id, username, password) VALUES (?, ?, ?)", DbConnection);
    insertPrivCmd.Connection = DbConnection;
    insertPrivCmd.Transaction = transaction;
    insertPrivCmd.Parameters.AddWithValue("?", customerId);
    insertPrivCmd.Parameters.AddWithValue("?", priv.username ?? DBNull.Value);
    insertPrivCmd.Parameters.AddWithValue("?", priv.password ?? DBNull.Value);
    insertPrivCmd.ExecuteNonQuery();
}

关键注意事项 #

• 确保数据库表字段与插入的参数类型匹配，避免类型转换错误。
• 处理集合为空的情况，避免FirstOrDefault()返回null导致的空引用异常。
• 使用事务确保多表插入的原子性，避免部分数据插入成功、部分失败的情况。

内容的提问来源于stack exchange，提问作者User_789465