SQLite是一种开放源码嵌入式数据库,提供了零配置(zero-configuration)运行模式,并且资源占用非常少. 我在开发现在这个手机项目的时候, 之前选着的是Sql Ce.一个是性能,还有就是使用性上面,后者更重要吧. 实在没有理由要用户在使用我们的软件时候, 还专门安装Sql Ce3.X.
2.类似软件(Apache Derby,Hsqldb IBM资源):
Derby 是100 % 的 Java 编程语言关系数据库,并提供了存储过程和触发器(Sqlite中没有),行级锁定,可以执行事务提交和回退操作,并支持加密。
Hsqldb(百度百科HSQLDB,官方首页)是一个开放源代码的JAVA数据库,其具有标准的SQL语法和JAVA接口,它可以自由使用和分发,非常简洁和快速的。它具有Server模式,进程内模式(In-Process)和内存模式(Memory-Only)三种。
等等 , 开源的数据库项目是在还是比较多.更多请参考: 常用嵌入式数据库概览
3.安装下载:
因为这次项目开发时在windows ce中. sqlite也有版本支持CompactFramework
下载页面 安装文件下载 SQLite-1.0.64.0-setup.exe(安装和在VS2005/2008中使用请参考:Visual Studio 2005/2008 Design-Time Support), 数据库管理工具下载: Sqliteman
4.在WCE6中使用SQLite
关于mobile开发的准备工作这里就不介绍了.
1)工具准备:Windows Mobile 开发工具和资源
2)连接设置:<GPRS网络连接设置 >,<如何设置 Windows Mobile 6.0 模拟器上网?>
5.创建数据库和连接测试:
1.打开Sqliteman,选着file-new-输入FileName(数据库名)
2.创建示例数据库:
--记录配置信息,系统信息 --drop table Config; CREATE TABLE Config ( id INTEGER PRIMARY KEY, cname VARCHAR(50) NOT NULL, cvalue VARCHAR(50) NOT NULL, ctype VARCHAR(20) NOT NULL );
4.添加SqliteHelper.cs
1
2
3
4using System.Data;
5using System.Data.SQLite;
6using System;
7using System.Collections.Generic;
8using log4net.Core;
9using log4net;
10
11namespace SQLiteDAL
12{
13 public class SqliteHelper
14 {
15 // Define a static logger variable so that it references the
16 // Logger instance named "MyApp".
17 private static readonly ILog log = LogManager.GetLogger(typeof(SqliteHelper));
18 private static String fullname = System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase;
19 private static String myAppPath = System.IO.Path.GetDirectoryName(fullname);
20
21 /**//// <summary>
22 /// 获得连接对象
23 /// </summary>
24 /// <returns></returns>
25 public static SQLiteConnection GetSQLiteConnection()
26 {
27 return new SQLiteConnection("Data Source=" + myAppPath + "\\royal.db");
28 }
29
30 private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
31 {
32 if (conn.State != ConnectionState.Open)
33 conn.Open();
34 cmd.Parameters.Clear();
35 cmd.Connection = conn;
36 cmd.CommandText = cmdText;
37
38 cmd.CommandType = CommandType.Text;
39 cmd.CommandTimeout = 30;
40
41 if (p != null)
42 {
43 foreach (object parm in p)
44 cmd.Parameters.AddWithValue(string.Empty, parm);
45 }
46 }
47
48 public static DataSet ExecuteDataset(string cmdText, params object[] p)
49 {
50 Debug(cmdText, p);
51 DataSet ds = new DataSet();
52 SQLiteCommand command = new SQLiteCommand();
53 using (SQLiteConnection connection = GetSQLiteConnection())
54 {
55 PrepareCommand(command, connection, cmdText, p);
56 SQLiteDataAdapter da = new SQLiteDataAdapter(command);
57 da.Fill(ds);
58 }
59 return ds;
60 }
61
62 public static DataRow ExecuteDataRow(string cmdText, params object[] p)
63 {
64 Debug(cmdText, p);
65 DataSet ds = ExecuteDataset(cmdText, p);
66 if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
67 return ds.Tables[0].Rows[0];
68 return null;
69 }
70
71 /**//// <summary>
72 /// 返回受影响的行数
73 /// </summary>
74 /// <param name="cmdText">a</param>
75 /// <param name="commandParameters">传入的参数</param>
76 /// <returns></returns>
77 public static int ExecuteNonQuery(string cmdText, params object[] p)
78 {
79 Debug(cmdText, p);
80 int resutCode = 0;
81 using (SQLiteCommand command = new SQLiteCommand())
82 {
83 using (SQLiteConnection connection = GetSQLiteConnection())
84 {
85 if (connection.State != ConnectionState.Open)
86 connection.Open();
87 using (SQLiteTransaction mytransaction = connection.BeginTransaction())
88 {
89 try
90 {
91 PrepareCommand(command, connection, cmdText, p);
92 resutCode = command.ExecuteNonQuery();
93 mytransaction.Commit();
94 }
95 catch (Exception ex)
96 {
97 mytransaction.Rollback();
98 log.Error("发生异常信息:"+ex.Message);
99 resutCode = -1;
100 }
101 }
102 }
103 }
104 log.Info("返回码:" + resutCode);
105 return resutCode;
106 }
107
108 /**//// <summary>
109 /// 执行非查询sql语句,返回受影响的行数
110 /// </summary>
111 /// <param name="cmdText">a</param>
112 /// <param name="commandParameters">传入的参数</param>
113 /// <returns></returns>
114 public static int ExecuteNonQuery(string cmdText, IList<object[]> ps)
115 {
116 int resutCode = 0;
117 using (SQLiteCommand command = new SQLiteCommand())
118 {
119 using (SQLiteConnection connection = GetSQLiteConnection())
120 {
121 if (connection.State != ConnectionState.Open)
122 connection.Open();
123 using (SQLiteTransaction mytransaction = connection.BeginTransaction())
124 {
125 log.Info("批量操作Begin:{");
126 foreach (object[] p in ps)
127 {
128 if (log.IsDebugEnabled)
129 {
130 string temp = "";
131 foreach (object obj in p)
132 {
133 temp += obj.ToString() + ";";
134 }
135 log.Debug("执行语句:" + cmdText);
136 log.Debug("执行的参数:" + temp);
137 }
138
139 try
140 {
141 PrepareCommand(command, connection, cmdText, p);
142 resutCode += command.ExecuteNonQuery();
143 }
144 catch (Exception ex)
145 {
146 if (mytransaction.Connection != null)
147 {
148 mytransaction.Rollback();
149 log.Error("发生异常信息:" + ex.Message);
150 resutCode = -1;
151 }
152 }
153 }
154 mytransaction.Commit();
155 log.Info("}批量操作结束!");
156 }
157 }
158 return resutCode;
159 }
160 }
161
162
163 /**//// <summary>
164 /// 返回SqlDataReader对象
165 /// </summary>
166 /// <param name="cmdText"></param>
167 /// <param name="commandParameters">传入的参数</param>
168 /// <returns></returns>
169 public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
170 {
171 Debug(cmdText, p);
172 SQLiteDataReader reader=null;
173 using (SQLiteCommand command = new SQLiteCommand())
174 {
175 SQLiteConnection connection = GetSQLiteConnection();
176 try
177 {
178 PrepareCommand(command, connection, cmdText, p);
179 reader = command.ExecuteReader(CommandBehavior.CloseConnection);
180 }
181 catch(Exception ex)
182 {
183 }
184 }
185 return reader;
186 }
187
188 /**//// <summary>
189 /// 返回结果集中的第一行第一列,忽略其他行或列
190 /// </summary>
191 /// <param name="cmdText"></param>
192 /// <param name="commandParameters">传入的参数</param>
193 /// <returns></returns>
194 public static object ExecuteScalar(string cmdText, params object[] p)
195 {
196 Debug(cmdText, p);
197 using (SQLiteCommand cmd = new SQLiteCommand())
198 {
199
200 using (SQLiteConnection connection = GetSQLiteConnection())
201 {
202 PrepareCommand(cmd, connection, cmdText, p);
203 return cmd.ExecuteScalar();
204 }
205 }
206 }
207
208 private static void Debug(string cmdText, params object[] p)
209 {
210 //如果在调试模式下:
211 if (log.IsDebugEnabled)
212 {
213 string temp="";
214 foreach(object obj in p)
215 {
216 temp+=obj.ToString()+";";
217 }
218 log.Debug("执行语句:"+cmdText);
219 log.Debug("执行的参数:" + temp);
220 }
221 }
222 }
223}
224
待续…