引用:
System.Data.SQLite.dll
System.Data.SqlClient.dll
SQLite.Interop.076.dll
注意::::SQLite.Interop.076.dll 这样的dll必须放在wince设备上(“计算机\BT-W_Series\\\Windows“或者“程序根目录“)
using System;
using System.Data.SQLite;
namespace Framework.DataAccess
{
public class SQLiteMager
{
//数据库连接
SQLiteConnection m_dbConnection;
static void Main(string[] args)
{
SQLiteMager p = new SQLiteMager();
}
public SQLiteMager()
{
createNewDatabase();
connectToDatabase();
createTable();
fillTable();
printHighscores();
}
//创建一个空的数据库
public void createNewDatabase()
{
SQLiteConnection.CreateFile("MyDatabase.sqlite");
}
//创建一个连接到指定数据库
public void connectToDatabase()
{
m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
}
//在指定数据库中创建一个table
public void createTable()
{
string sql = "create table highscores (name varchar(20), score int)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
//插入一些数据
public void fillTable()
{
string sql = "insert into highscores (name, score) values ('Me', 3000)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('Myself', 6000)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('And I', 9001)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
//使用sql查询语句,并显示结果
public void printHighscores()
{
string sql = "select * from highscores order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
Console.ReadLine();
}
}
}
eg::
#region SQLite 操作(检查数据库和表格是否存在,不存在则创建)
//,如果数据库不存在,则创建数据库 MyDatabase
if (!File.Exists("MyDatabase.sqlite")) SQLiteConnection.CreateFile("MyDatabase.sqlite");
//创建数据库MyDatabase的连接
SQLiteHelper _SQLiteHelper = new SQLiteHelper("Data Source=MyDatabase.sqlite;Version=3;DateTimeFormat=Ticks;");
//在MyDatabase数据库 执行-创建表格
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "CREATE TABLE IF NOT EXISTS highscores (name varchar(20), score int)");
DataSet obj2 = _SQLiteHelper.ExecuteDataSet(CommandType.Text, "select * from highscores order by score desc");
int dt2 = obj2.Tables[0].Rows.Count;
//在MyDatabase数据库 执行-插入数据
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me', 3000)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me2', 3002)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me', 3000)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me2', 3002)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me', 3000)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me2', 3002)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me', 3000)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me2', 3002)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me', 3000)");
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values ('Me2', 3002)");
//在MyDatabase数据库 执行-查询
DataSet obj = _SQLiteHelper.ExecuteDataSet(CommandType.Text, "select * from highscores order by score desc");
int dt= obj.Tables[0].Rows.Count;
//SQLiteMager ddds = new SQLiteMager();
#endregion