从列表到数据库记录正在重复

I have the following code to create a list<> with three records and populate an access database with this list. It works but the database is populated with the list's first record three times, i.e., the rows are repeated. Any help would be greatly appreciated. Thank you

我有以下代码来创建一个包含三个记录的列表<>并使用此列表填充访问数据库。它工作，但数据库填充了列表的第一个记录三次，即重复行。任何帮助将不胜感激。谢谢

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;

namespace ConsoleApplication7
{
    class Program
    {
        static void Main(string[] args)
        {
            var list = new List<Project>(){
                new Project{ProjectId="ID1",ProjectName="AAAA"}, 
                new Project{ProjectId="ID2",ProjectName="BBBB"},
                new Project{ProjectId="ID3",ProjectName="CCCC"},
            };


            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\td\Desktop\Test2.accdb";
            OleDbConnection con = new OleDbConnection(connectionString);
            string accessQuery = "INSERT INTO [table] (ProjectId, ProjectName) VALUES (@ProjectId, @ProjectName)";
            OleDbCommand cmd = new OleDbCommand(accessQuery, con);

            con.Open();


            for (int i = 0; i < list.Count; i++)
            {
                cmd.Parameters.AddWithValue("@ProjectId", list[i].ProjectId);
                cmd.Parameters.AddWithValue("@ProjectName", list[i].ProjectName);
                cmd.ExecuteNonQuery();
            }

        }
    }

    public class Project
    {
        public string ProjectId { get; set; }
        public string ProjectName { get; set; }
    }
}

1 个解决方案

#1

You are creating the command outside your for loop and then attempting to add the parameters to the same command. I am guessing that when you call cmd.Parameters.AddWithValue for i > 0 those values are being silently ignored. Try

您正在for循环外创建命令，然后尝试将参数添加到同一命令。我猜测当你为i> 0调用cmd.Parameters.AddWithValue时，这些值被默默地忽略。尝试

string accessQuery = "INSERT INTO [table] (ProjectId, ProjectName) VALUES (@ProjectId, @ProjectName)";
OleDbCommand cmd = new OleDbCommand(accessQuery, con);    
con.Open();

for (int i = 0; i < list.Count; i++)
{
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("@ProjectId", list[i].ProjectId);
    cmd.Parameters.AddWithValue("@ProjectName", list[i].ProjectName);
    cmd.ExecuteNonQuery();
}

If that doesn't work (not at my dev machine, I can't test right now), you can always move the command creation inside your for loop and leave everything else the same.

如果这不起作用（不是在我的开发机器上，我现在无法测试），你总是可以在for循环中移动命令创建并保持其他所有内容相同。