C#导入Excel至Access数据库时DataColumn与DataRow类型转换报错求助

核心错误原因 #

你遇到的无法将类型为'System.Data.DataColumn'的对象强制转换为类型'System.Data.DataRow'错误，根源是第一个foreach循环错误地遍历了DataTable的列集合（tbl.Columns）——DataColumn是表格的列定义，不是存储数据的行，自然不能用行索引（row[0]这类）去取值，且遍历行必须用tbl.Rows集合。

具体修复步骤 #

1. 修正Player表的遍历逻辑
   遍历CSV数据行必须使用tbl.Rows集合，变量类型为DataRow，而非tbl.Columns和DataColumn。

2. 修复SQL语句语法错误

   • Club表的INSERT语句：INSERT INTO Club ([ClubName) VALUES(@club) 中[ClubName缺少闭合的]，应改为INSERT INTO Club ([ClubName]) VALUES(@club)
   • League表的INSERT语句同理：INSERT INTO League ([LeagueName) VALUES(@league) 改为INSERT INTO League ([LeagueName]) VALUES(@league)

3. 修正参数类型错误
   Club表的参数param8被定义为OleDbType.Integer，但实际要存入的是俱乐部名称字符串，应改为OleDbType.VarChar。

4. 修正League表的参数引用错误
   League表的循环里错误地给param8赋值，应该使用对应的param9。

5. 优化参数复用
   每次修改CommandText后，清理原有参数再添加新参数，避免参数重复累积导致的潜在问题。

修复后的完整代码 #

public class ExcelConversion
{
    public string filepath { get; set; }
    public void FileImport()
    {
        FileInfo file = new FileInfo("top5_leagues_player.csv");
        string fileNameAccess = "FMSDatabase.mdb";
        string ConnectionStringAccess = string.Format("Data Source={0}; Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=false", fileNameAccess);
        
        using (OleDbConnection cnn = new OleDbConnection($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{file.DirectoryName}\";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
        {
            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [top5_leagues_player.csv]", cnn))
            {   
                cnn.Open();
                using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                {
                    DataTable tbl = new DataTable("MyTable");
                    adp.Fill(tbl);
                    
                    using (OleDbConnection dbcnn = new OleDbConnection(ConnectionStringAccess))
                    {
                        dbcnn.Open();
                        OleDbCommand cmdAccess = dbcnn.CreateCommand();
                        cmdAccess.CommandType = CommandType.Text;

                        // 导入Player表
                        cmdAccess.CommandText = "INSERT INTO Player ([PlayerId], [PlayerName], [Age], [Nationality], [Price]) VALUES(@id, @name, @age, @nationality, @price)";
                        cmdAccess.Parameters.Add(new OleDbParameter("@id", OleDbType.Integer));
                        cmdAccess.Parameters.Add(new OleDbParameter("@name", OleDbType.VarChar));
                        cmdAccess.Parameters.Add(new OleDbParameter("@age", OleDbType.Integer));
                        cmdAccess.Parameters.Add(new OleDbParameter("@nationality", OleDbType.VarChar));
                        cmdAccess.Parameters.Add(new OleDbParameter("@price", OleDbType.Decimal));

                        // 正确遍历数据行
                        foreach (DataRow row in tbl.Rows)
                        {
                            cmdAccess.Parameters["@id"].Value = Convert.ToInt32(row[0]);
                            cmdAccess.Parameters["@name"].Value = row[1].ToString();
                            cmdAccess.Parameters["@age"].Value = Convert.ToInt32(row[3]);
                            cmdAccess.Parameters["@nationality"].Value = row[5].ToString();
                            cmdAccess.Parameters["@price"].Value = Convert.ToDecimal(row[4]);
                            cmdAccess.ExecuteNonQuery();
                        }

                        // 导入Club表 - 清理原有参数
                        cmdAccess.Parameters.Clear();
                        cmdAccess.CommandText = "INSERT INTO Club ([ClubName]) VALUES(@club)";
                        OleDbParameter paramClub = new OleDbParameter("@club", OleDbType.VarChar);
                        cmdAccess.Parameters.Add(paramClub);
                        foreach (DataRow row in tbl.Rows)
                        {
                            paramClub.Value = row[12].ToString();
                            cmdAccess.ExecuteNonQuery();
                        }

                        // 导入League表 - 清理参数
                        cmdAccess.Parameters.Clear();
                        cmdAccess.CommandText = "INSERT INTO League ([LeagueName]) VALUES(@league)";
                        OleDbParameter paramLeague = new OleDbParameter("@league", OleDbType.VarChar);
                        cmdAccess.Parameters.Add(paramLeague);
                        foreach (DataRow row in tbl.Rows)
                        {
                            paramLeague.Value = row[17].ToString();
                            cmdAccess.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
    }
}

额外提示 #

• 确认CSV列索引和数据库字段的对应关系（比如row[3]对应Age、row[5]对应Nationality，要和CSV实际列顺序匹配）
• 可添加异常捕获逻辑，处理数据转换失败（比如非数字格式的Age）的情况
• 若数据量较大，建议使用OleDbDataAdapter.Update或批量插入，比逐行ExecuteNonQuery效率更高

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