小型数据库中推荐大家使用SQLite,在中小型软件、网站开发时,如果不需要对数据管理有太高的要求都可以采用SQLite,其最关键的一点是免费而且开源。像Adobe Reader,Firefox中都采用了SQLite。官方网站
http://www.sqlite.org/ ,当前版本是3.6.17。
一、SQLite基本特性
- 支持事务处理。
- 零配置。
- 支持大部分SQL92标准。
- 数据库存储在单个文件中。
- 默认支持十亿字节长的字符串。
- SQLite解释器小于300kb。
- 比C/S型的数据库更快。
- 简单便捷的API。
- 采用ANSI-C 编译,同时支持多种语言。
- 完全开源,完全免费。
- 跨平台。
- 自包含,不需要任何第三方组件。
SQLite 不支持以下SQL特性:
FOREIGN KEY constraints,Complete trigger support,Complete ALTER TABLE support, RIGHT and FULL OUTER JOIN,
Writing to ViEWs,GRANT and REVOKE。
二、GUI管理工具
在SQLite的Wiki 上列出了很多管理工具,参见
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools ,推荐大家使用SQLite2009 Pro,
下载见
http://osenxpsuite.net/?xp=3 ,也是免费的,最新版本的SQLite引擎是3.6.16。使用起来也很方便。
主界面如下:
三、.NET的Wrapper
很多语言都提供了SQLite 的Wrapper,见
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers 。
推荐使用SQLite.NET,官方网站
http://sqlite.phxsoftware.com/ ,SourceForge项目主页
http://sourceforge.net/projects/sqlite-dotnet2/ 。
SQLite.NET 还支持LINQ方式调用。同时提供VS插件,可以在VS中直接编辑数据库。
最后,附上包装SQLite 的C#代码用于举例怎么使用。
1
using System;
2
using System.Collections.Generic;
3
using System.Linq;
4
using System.Text;
5
using System.Data;
6
using System.Data.SQLite;
7![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
namespace AirLibrary
9![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
10![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
11
/// 数据库接口类
12
/// </summary>
13
public class SQLite : IDisposable
14![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
15![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Attribute#region Attribute
16
private string datasource = @"db\data.db3";
17
private bool isOpen;
18
private bool disposed = false;
19![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
private SQLiteConnection connection;
21
private Dictionary<string, string> parameters;
22
#endregion //Attribute
23![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Constructor#region Constructor
25
public SQLite()
26![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
27
init("");
28
}
29![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
31
/// 连接指定的数据库
32
/// </summary>
33
/// <param name="datasource">连接字符串</param>
34
public SQLite(string datasource)
35![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
36
init(datasource);
37
}
38![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
40
/// 清理托管资源
41
/// </summary>
42
public void Dispose()
43![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
44
Dispose(true);
45
GC.SuppressFinalize(this);
46
}
47![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
48![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
49
/// 清理所有使用资源
50
/// </summary>
51
/// <param name="disposing">如果为true则清理托管资源</param>
52
protected void Dispose(bool disposing)
53![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
54
if (!this.disposed)
55![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
56
// dispose all managed resources.
57
if (disposing)
58![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
59
this.isOpen = false;
60
connection.Dispose();
61
}
62![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
63
// dispose all unmanaged resources
64
this.close();
65![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
66
disposed = true;
67
}
68
}
69![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
70
~SQLite()
71![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
72
Dispose(false);
73
}
74
#endregion //Constructor
75![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
76![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Function#region Function
77
private void init(string datasource)
78![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
79
if (datasource != "")
80
this.datasource = datasource;
81
this.connection = new SQLiteConnection("data source = " + this.datasource);
82
this.parameters = new Dictionary<string, string>();
83
this.isOpen = false;
84
}
85![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
86
private bool checkDbExist()
87![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
88
if (System.IO.File.Exists(datasource))
89
return true;
90
else
91
return false;
92
}
93![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
94
private void open()
95![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
96
if (!checkDbExist())
97
throw new AirException("1001");
98![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
99
if (!isOpen)
100
connection.Open();
101![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
102
this.isOpen = true;
103
}
104![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
105
private void close()
106![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
107
if (isOpen)
108
connection.Close();
109![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
110
this.isOpen = false;
111
}
112
#endregion //Function
113![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
114![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Method#region Method
115![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
116
/// 添加参数
117
/// </summary>
118
/// <param name="key">参数名</param>
119
/// <param name="value">参数值</param>
120
public void AddParameter(string key, string value)
121![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
122
parameters.Add(key, value);
123
}
124![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
125![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
126
/// 执行SQL语句
127
/// </summary>
128
/// <param name="queryStr">SQL语句</param>
129
public void ExecuteNonQuery(string queryStr)
130![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
131
this.open();
132
using (SQLiteTransaction transaction = connection.BeginTransaction())
133![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
134
using (SQLiteCommand command = new SQLiteCommand(connection))
135![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
136
command.CommandText = queryStr;
137
foreach (KeyValuePair<string, string> kvp in this.parameters)
138![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
139
command.Parameters.Add(new SQLiteParameter(kvp.Key, kvp.Value));
140
}
141![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
142
command.ExecuteNonQuery();
143
}
144
transaction.Commit();
145
}
146
this.close();
147
this.parameters.Clear();
148
}
149![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
150![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
151
/// 执行SQL语句并返回所有结果
152
/// </summary>
153
/// <param name="queryStr">SQL语句</param>
154
/// <returns>返回DataTable</returns>
155
public DataTable ExecuteQuery(string queryStr)
156![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
157
DataTable dt = new DataTable();
158
this.open();
159
try
160![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
161
using (SQLiteCommand command = new SQLiteCommand(connection))
162![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
163
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
164
command.CommandText = queryStr;
165
foreach (KeyValuePair<string, string> kvp in this.parameters)
166![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
167
command.Parameters.Add(new SQLiteParameter(kvp.Key, kvp.Value));
168
}
169![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
170
adapter.Fill(dt);
171
}
172
}
173
catch (SQLiteException e)
174![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
175
throw new AirException("1002", e.Message);
176
}
177
finally
178![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
179
this.close();
180
this.parameters.Clear();
181
}
182
return dt;
183
}
184![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
185![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
186
/// 执行SQL语句并返回第一行
187
/// </summary>
188
/// <param name="queryStr">SQL语句</param>
189
/// <returns>返回DataRow</returns>
190
public DataRow ExecuteRow(string queryStr)
191![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
192
DataRow row;
193
this.open();
194
try
195![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
196
using (SQLiteCommand command = new SQLiteCommand(connection))
197![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
198
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
199
command.CommandText = queryStr;
200
foreach (KeyValuePair<string, string> kvp in this.parameters)
201![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
202
command.Parameters.Add(new SQLiteParameter(kvp.Key, kvp.Value));
203
}
204![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
205
DataTable dt = new DataTable();
206
adapter.Fill(dt);
207
if (dt.Rows.Count == 0)
208
row = null;
209
else
210
row = dt.Rows[0];
211
}
212
}
213
catch (SQLiteException e)
214![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
215
throw new AirException("1002", e.Message);
216
}
217
finally
218![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
219
this.close();
220
this.parameters.Clear();
221
}
222
return row;
223
}
224![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
225![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
226
/// 执行SQL语句并返回结果第一行的第一列
227
/// </summary>
228
/// <param name="queryStr">SQL语句</param>
229
/// <returns>返回值</returns>
230
public Object ExecuteScalar(string queryStr)
231![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
232
Object obj;
233
this.open();
234
using (SQLiteCommand command = new SQLiteCommand(connection))
235![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
236
command.CommandText = queryStr;
237
foreach (KeyValuePair<string, string> kvp in this.parameters)
238![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
239
command.Parameters.Add(new SQLiteParameter(kvp.Key, kvp.Value));
240
}
241![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
242
obj = command.ExecuteScalar();
243
}
244
this.close();
245
this.parameters.Clear();
246
return obj;
247
}
248
#endregion //Method
249
}
250
}
251![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
这里的异常类是我自己项目中用的,大家实际使用时换成自己的异常类就是了。这里只是SQLite的一些简单应用,欢迎大家讨论指点。