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

罗分明网络博客 C#类库与方法 7534
首次发布：2017-05-12
2022-05-25 更新

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
/// <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}条记录");
    }
}