保存dataTable到SQLite数据库（数据保存数据库是否存在相同数据，存在则不保存）

2022-05-25 更新

保存dataTable到SQLite数据库（数据保存数据库是否存在相同数据，存在则不保存）以下是核心代码

/// <summary>
/// 创建一个DataTable 并带有数据
/// </summary>
/// <returns></returns>
private DataTable CreateDataTable()
{//原创来自 www.luofenming.com
    DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("UserName", typeof(string)));
    dt.Columns.Add(new DataColumn("UserQQ", typeof(string)));
    for (int i = 0; i < 10; i++)
    {
        DataRow dr = dt.NewRow();
        dr["UserName"] = "lqwvje" + i;
        dr["UserQQ"] = "10000" + i;
        dt.Rows.Add(dr);
    }
    return dt;
}
/// <summary>
/// 执行sql  并返回受影响行
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="p"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sqlStr, params SQLiteParameter[] p)
{
    using (SQLiteConnection conn = new SQLiteConnection("Data Source= data.db;Pooling=true;FailIfMissing=false"))
    {
        using (SQLiteCommand command = new SQLiteCommand(sqlStr, conn))
        {
            try
            {
                conn.Open();
                if (p != null)
                {
                    command.Parameters.AddRange(p);
                }
                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -99;
            }
        }
    }
}
/// <summary>
/// 查询单个数据
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="p"></param>
/// <returns></returns>
public object ExecuteScalar(string sqlStr, params SQLiteParameter[] p)
{
    using (SQLiteConnection conn = new SQLiteConnection("Data Source= data.db;Pooling=true;FailIfMissing=false"))
    {
        using (SQLiteCommand command = new SQLiteCommand(sqlStr, conn))
        {
            try
            {
                conn.Open();
                if (p != null)
                {
                    command.Parameters.AddRange(p);
                }
                return command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                return -99;
            }
        }
    }
}
/// <summary>
/// 判断是否存在
/// </summary>
/// <param name="pairs"></param>
/// <returns></returns>
public bool IsExists(string tableName,Dictionary<string, object> pairs)
{
    if (pairs != null && pairs.Count > 0)
    {
        string sqlStr = $"select count(*) from {tableName}  where ";
        List<string> where = new List<string>();
        List<SQLiteParameter> paras = new List<SQLiteParameter>();
        foreach (KeyValuePair<string, object> kv in pairs)
        {
            where.Add($" {kv.Key}=@{kv.Key}");
            paras.Add(new SQLiteParameter("@" + kv.Key, kv.Value));
        }
        return int.Parse(ExecuteScalar(sqlStr + string.Join(" and ", where), paras.ToArray()).ToString()) > 0;
    }
    return false;
}
/// <summary>
/// 添加数据
/// </summary>
public void Add()
{
    DataTable data = CreateDataTable();
    if (data != null && data.Rows.Count > 0)
    {
        Dictionary<string, object> pairs = new Dictionary<string, object>();
        string sqlStr;
        SQLiteParameter[] paras;
        int success = 0, isExists = 0;
        foreach (DataRow dr in data.Rows)
        {
            pairs["UserName"] = dr["UserName"];
            if (!IsExists("TestTable", pairs))
            {
                sqlStr = "insert into TestTable (UserName,UserQQ) values (@UserName,@UserQQ)";
                paras = new SQLiteParameter[]
                {
            new SQLiteParameter("@UserName",dr["UserName"]),
            new SQLiteParameter("@UserQQ",dr["UserQQ"])
                };
                if (ExecuteNonQuery(sqlStr, paras) > 0)
                {
                    success++;
                }
            }
            else
            {
                isExists++;
            }
        }
        MessageBox.Show($"成功添加{success}条记录，已存在{isExists}条记录");
    }
}