从csv文件中读取的数据,一直重复写入Microsoft SQL

Question

Visual Studio C#
从csv文件中读取数据 写入Microsoft SQL
csv文件里有500条数据，每运行一次都会重复添加这500条，怎么改每次只添加新的数据？

有关debug的问题，一点头绪都没有希望大家帮忙

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DataAccess.Models;
using NLog;

namespace DataAccess
{
    public class Adapter
    {
        Logger log = LogManager.GetCurrentClassLogger();
        public List<Client> GetAllClientData()
        {
            try
            {
                string sql = "SELECT * FROM ClientInOut";
                using (var connection = Helpers.GetConnection())
                {
                    return connection.Query<Client>(sql).ToList();
                }

            }
            catch (Exception e)
            {
                log.Error(e, "Error when reading client details.");
                return null;
            }
            
        }

        public void AddNewClientData()
        {
            
            try
            {
                string[] fileLines = File.ReadAllLines("ClientList.csv");
                foreach (string line in fileLines)
                {
                    string[] lineParts = line.Split(',');
                    Client client = new Client()
                    {
                        ClientName = lineParts[0],
                        ClientPhone = lineParts[1],
                        EntryTime = DateTime.Parse(lineParts[2]),
                        ExitTime = DateTime.Parse(lineParts[3])
                    };

                    bool clientExists = false;//if client not exist

                    // Check that the user does not already exist
                    string sqlGet = "Select * FROM ClientInOut";
                    using (var connection = Helpers.GetConnection())
                    {
                        connection.Open();
                        List<Client> existingClients = connection.Query<Client>(sqlGet).ToList();
                        for (int i = 0; i < fileLines.Length; i++)
                        {
                            foreach (Client existingCustomer in existingClients)
                            {
                                if (existingCustomer == client)
                                {
                                    clientExists = true;
                                }
                            }
                        }
                    }
                    // add the new client 
                    if (clientExists == false)
                    {
                        string sql =
                        "INSERT INTO ClientInOut (ClientName, ClientPhone, EntryTime, ExitTime) Values (@ClientName, @ClientPhone, @EntryTime, @ExitTime);

                        using (var connection = Helpers.GetConnection())
                        {
                            connection.Open();
                            var affectedRows = connection.Execute(sql, client);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                log.Error(e, "Error when reading client exists details.");
                Console.WriteLine(e.Message);
            }
        }
    }
}

Answer 1

foreach (Client existingCustomer in existingClients)
{
if (existingCustomer == client)

这里判断有问题，client和existingCustomer是不同的对象，即使对象中字段值都一样，但是是不相等的。应该获取对象的值进行判断，而且组合成sql语句判断一次就行，不用全部读出数据后判断换，数据多的话服务器要被拖垮

判断部分改为下这样

                    // Check that the user does not already exist
                    
                    using (var connection = Helpers.GetConnection())
                    {
                        connection.Open();
                        //通过名称和电话判断，还要判断时间题主可以加上
                        string sqlGet = "Select * FROM ClientInOut where ClientName='"+client.ClientName.Replace("'","''")+"' and ClientPhone='"+client.ClientPhone.Replace("'","''")+"'";
                        clientExists=connection.Query<Client>(sqlGet).ToList().Count>0;//////////////////////这里判断取出的数据大于0存在
                        /*List<Client> existingClients = connection.Query<Client>(sqlGet).ToList();
                        for (int i = 0; i < fileLines.Length; i++)
                        {
                            foreach (Client existingCustomer in existingClients)
                            {
                                if (existingCustomer == client)
                                {
                                    clientExists = true;
                                }
                            }
                        }*/
                    }

有帮助或启发麻烦点个【采纳该答案】，谢谢~~有其他问题可以继续交流~