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);
}
}
}
}