SQLite操作总结

引用：

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

转载于:https://www.cnblogs.com/lhlong/p/5971668.html