WINDOWS MOBILE下如何使用SQLLITE
1. 首先需要装一个能解析SQLLITE 数据的SQL开发工具 ,方便查看其中数据,我用的一般有2个一个是SqliteDev,一个是sqliteadmin
2.需要两个DLL,System.Data.SQLite.dll,SQLite.Interop.065.DLL,这两个文件必须要放在手持设备的程序目录里
3.一个SQLLITE读写的class
using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
namespace DataBase
{
/// <summary>
/// SQLite数据库操作类, guaik.com
/// </summary>
public class SQLiteHelper
{
public void test()
{
//A。 该方法主要是利用了 SQLiteParameter 的功能，读取blob字段。代码如下：
SQLiteConnection m_conn = null;
FileStream m_filestream = null;
try
{
m_filestream = new FileStream(@"d:\pcinfo\17.jpg", FileMode.Open, FileAccess.Read); //读取图片
SQLiteCommand m_commd2 = new SQLiteCommand();
m_commd2.CommandText = "UPDATE test1 set timage=@idimage WHERE tparendid=78";
Byte[] m_byte = new Byte[m_filestream.Length]; //存放图片
m_filestream.Read(m_byte, 0, m_byte.Length);
m_filestream.Close();
SQLiteParameter param_m
= new SQLiteParameter("@idimage", DbType.Binary, m_byte.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, m_byte); //很多参数阿，注意DBType.Binary
m_commd2.Parameters.Add(param_m);
m_commd2.Connection = m_conn;
m_commd2.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
//MessageBox.Show("未能存入图片");
}
//sqlite 读取二进制MP3文件
// SQLiteConnection _conn = new SQLiteConnection("Data Source=RWDB.db3;Pooling=False");
// SQLiteCommand _cmd = new SQLiteCommand(_conn);
// SQLiteDataReader _reader;
// object[] _field = new object[1];
// byte[] _soundstream;
// _conn.Open();
// _cmd.CommandText = "SELECT [sound] FROM [sound]";
// using (_reader = _cmd.ExecuteReader())
// {
// while (_reader.Read())
// {
// _reader.GetValues(_field);
// _soundstream = (byte[])_field[0];
// if (!(_player.OpenStream(true, false, ref _soundstream, System.Convert.ToUInt32(_soundstream.Length), TStreamFormat.sfMp3)))
// {
// MessageBox.Show(_player.GetError(), string.Empty, MessageBoxButtons.OK, MessageBoxIcon.Error);
// }
// _player.StartPlayback();
// }
// }
// _conn.Close();
//sqlite 读取二进制图片文件
//private void GetImage(string fullName)
//{
// SQLiteConnection sqlite_con =
// new SQLiteConnection("Data Source=|DataDirectory|dbasedict.s3db;" +
// "Version=3;New=False;Compress=True;");
// String querry2 = "select iimages from dictionario where word = '" +
// searchBox.Text + "'";
// SQLiteDataAdapter adap3 = new SQLiteDataAdapter(querry2, sqlite_con);
// DataSet set = new DataSet();
// adap3.Fill(set, "dictionario");
// DataTable dataTable = new DataTable();
// dataTable = (DataTable)set.Tables[0];
// MemoryStream ms = null;
// foreach (DataRow row in dataTable.Rows)
// {
// int id = Convert.ToInt32(row["iimages"]);
// byte[ data = (byte[)row["dictionario"];
// ms = new MemoryStream(data);
// }
// pictureBox1.Image = Image.FromStream(ms);
//}
}
#region ExecuteNonQuery
/// <summary>
/// 执行 SQL 语句并返回受影响的行数
/// </summary>
/// <param name="connString">数据库连接串</param>
/// <param name="commandText">SQL语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string connString, string commandText)
{
return ExecuteNonQuery(connString, commandText, (SQLiteParameter[])null);
}
/// <summary>
/// 执行 SQL 语句并返回受影响的行数
/// </summary>
/// <param name="connString">数据库连接串</param>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQL语句参数</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string connString, string commandText, params SQLiteParameter[] paras)
{
int count = 0;
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
SQLiteTransaction tran = null;
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = commandText;
if (paras != null) cmd.Parameters.AddRange(paras);
#region 事务处理
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
count = cmd.ExecuteNonQuery();
tran.Commit();
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
#endregion
}
return count;
}
#endregion
#region ExecuteReader
/// <summary>
/// 将 CommandText 发送到 Connection 并生成一个 IDataReader
/// </summary>
/// <param name="connString">数据库连接串</param>
/// <param name="commandText">SQL语句</param>
/// <returns>IDataReader</returns>
public static IDataReader ExecuteReader(string connString, string commandText)
{
return ExecuteReader(connString, commandText, (SQLiteParameter[])null);
}
/// <summary>
/// 将 CommandText 发送到 Connection 并生成一个 IDataReader
/// </summary>
/// <param name="connString">数据库连接串</param>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQL语句参数</param>
/// <returns>IDataReader</returns>
public static IDataReader ExecuteReader(string connString, string commandText, params SQLiteParameter[] paras)
{
SQLiteConnection conn = new SQLiteConnection(connString);
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = commandText;
if (paras != null) cmd.Parameters.AddRange(paras);
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion
#region ExecuteScalar
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <param name="connString">数据库连接串</param>
/// <param name="commandText">SQL语句</param>
/// <returns>第一行的第一列的数据</returns>
public static Object ExecuteScalar(string connString, string commandText)
{
return ExecuteScalar(connString, commandText, (SQLiteParameter[])null);
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <param name="connString">数据库连接串</param>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQL语句参数</param>
/// <returns>第一行的第一列的数据</returns>
public static Object ExecuteScalar(string connString, string commandText, params SQLiteParameter[] paras)
{
Object obj = null;
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = commandText;
if (paras != null) cmd.Parameters.AddRange(paras);
try
{
conn.Open();
obj = cmd.ExecuteScalar();
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
return obj;
}
#endregion
#region GetDataSet
/// <summary>
/// 执行查询，并返回一个DataSet
/// </summary>
/// <param name="connString">数据库连接串</param>
/// <param name="commandText">SQL语句</param>
/// <returns>第一行的第一列的数据</returns>
public static DataSet GetDataSet(string connString, string commandText)
{
return GetDataSet(connString, commandText, (SQLiteParameter[])null);
}
/// <summary>
/// 执行查询，并返回一个DataSet
/// </summary>
/// <param name="connString">数据库连接串</param>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQL语句参数</param>
/// <returns>第一行的第一列的数据</returns>
public static DataSet GetDataSet(string connString, string commandText, params SQLiteParameter[] paras)
{
DataSet ds = new DataSet();
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = commandText;
if (paras != null) cmd.Parameters.AddRange(paras);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
}
return ds;
}
#endregion
}
}
3.读取当前文件配置 这个任意你怎么写了
string filePath = Global.FilePath;
filePath = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(filePath) + "\\", "MarketInfor");
if (!System.IO.File.Exists(filePath))
{
MessageBox.Show("没有找到单据数据库文件", "出错");
Application.Exit();
}
Global.DbFilePath = filePath;
Global.DbCon = "Data Source=" + filePath + ";Version=3;New=True;Compress=True;";
4.剩下的就是那些操作 这个不解释了,好了,就这么简单
DataSet ds = SQLiteHelper.GetDataSet(Global.DbCon, strSql);
int iretrun = SQLiteHelper.ExecuteNonQuery(Global.DbCon, strsql);
