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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
namespace SQLite
{
public class DBTable<T> : IDBTable
where T : new()
{
public string TableName { get; private set; }
/// <summary>
/// 每次获取，自动++
/// </summary>
public long FreeID
{
get
{
MaxID++;
return MaxID;
}
}
public long MaxID { get; set; } = -1;
public string DDL { get; private set; }
public string INDEX { get; private set; }
/// <summary>
/// 时间列 名称
/// </summary>
public string TimeFieldName { get; private set; } = null;
public SQLiteHelper sqliteHelper;
public DBTable()
{
TableName = SQLiteHelper.GetTableName(typeof(T));
DDL = SQLiteHelper.GetCreateTableCommandText(typeof(T));
INDEX = SQLiteHelper.GetCreateIndexCommandText(typeof(T));
updateTimeFieldName();
}
void updateTimeFieldName()
{
var type = typeof(T);
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (var propertyInfo in propertyInfos)
{
//忽略
if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
continue;
if (!(propertyInfo.GetCustomAttributes(typeof(TimeAttribute), false).Count() > 0))
continue;//没有Time
//找到了
TimeFieldName = propertyInfo.Name;
return;
}
}
public void Init(SQLiteHelper sQLiteHelper)
{
sqliteHelper = sQLiteHelper;
}
public void Create()
{
//创建数据库
sqliteHelper.ExecuteNonQuery(DDL);
//创建index
if(!string.IsNullOrEmpty(INDEX))
sqliteHelper.ExecuteNonQuery(INDEX);
}
public string GetDeleteSQL(DateTime del_time)
{
if (string.IsNullOrEmpty(TimeFieldName))
return null;
string del_time_str = del_time.ToStringOfSQLiteFieldType();
//获取 比 del_time 小的 最后一个ID
var ret = sqliteHelper.ExecuteScalar($"SELECT ID FROM {TableName} WHERE {TimeFieldName} < {del_time_str} ORDER BY ID DESC LIMIT 1");
if (ret != null)
{
long max_id = Convert.ToInt64(ret);
return $"DELETE FROM {TableName}" +
$" WHERE ID < {max_id}";
}
return null;
}
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);
}
/// <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()
{
}
}
}