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
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
namespace SQLite
{
public class DBTable<T> : IDBTable
where T : new()
{
public List<SQLiteHelper.ArrayFieldTypeInfo> ArrayFieldTypeInfos = new List<SQLiteHelper.ArrayFieldTypeInfo>();
public string TableName { get; private set; }
private long freeID = 0;
/// <summary>
/// 每次获取，自动++
/// </summary>
public long FreeID
{
get
{
return freeID++;
}
set {
freeID = value;
}
}
private string ddl;
public string DDL
{
get
{
if (ArrayFieldTypeInfos.Count() == 0)
return ddl;
else
return SQLiteHelper.GetCreateTableCommandText(typeof(T), ArrayFieldTypeInfos.ToArray());
}
}
SQLiteHelper sqliteHelper;
public DBTable()
{
TableName = SQLiteHelper.GetTableName(typeof(T));
ddl = SQLiteHelper.GetCreateTableCommandText(typeof(T));
}
public void Init(SQLiteHelper sQLiteHelper)
{
sqliteHelper = sQLiteHelper;
}
public void Create()
{
sqliteHelper.ExecuteNonQuery(SQLiteHelper.GetCreateTableCommandText(typeof(T), ArrayFieldTypeInfos.ToArray()));
}
public void Add(T t)
{
sqliteHelper.ExecuteNonQuery(SQLiteHelper.GetInsertCommandText(t));
}
public void Update(T t, string condition)
{
sqliteHelper.ExecuteNonQuery(SQLiteHelper.GetUpdateCommandText(t, condition));
}
public bool AddRange(IEnumerable<T> array)
{
List<string> querys = new List<string>();
foreach (T t in array)
{
querys.Add(SQLiteHelper.GetInsertCommandText(t));
}
return sqliteHelper.QueryTran(querys);
}
public List<T> Find(string condition)
{
string sql = $"SELECT * FROM {TableName}";
if (!string.IsNullOrEmpty(condition))
sql += " " + condition;
DataTable dataTable = sqliteHelper.ExecuteReader(sql);
return SQLiteHelper.ToObjs<T>(dataTable, ArrayFieldTypeInfos.ToArray());
}
/// <summary>
/// 获取最后N行数据
/// </summary>
/// <param name="count"></param>
/// <returns></returns>
public List<T> Last(int count)
{
return Find($"LIMIT (SELECT COUNT()-{count} FROM {TableName}),{count}");
}
/// <summary>
/// 获取前面N行数据
/// </summary>
/// <param name="count"></param>
/// <returns></returns>
public List<T> First(int count)
{
return Find($"LIMIT {count}");
}
public void Remove()
{
}
}
}