本文整理自网络,侵删。
本文实例讲述了C#操作SQLite方法。分享给大家供大家参考。具体分析如下:
地址:
System.Data.Sqlite入手。。。
首先import/using:
代码如下:
using System.Data.SQLite;
书籍
Connection和Command:
private SQLiteConnection conn; private SQLiteCommand cmd;
连接db:
conn = new SQLiteConnection("Data Source=c:\\test.db"); conn.Open();
INSERT/UPDATE:
cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO user(email,name) VALUES ('email','name')"; cmd.ExecuteNonQuery(); cmd.CommandText = "UPDATE userSET name = 'Codelicious' WHERE ID = 1"; cmd.ExecuteNonQuery();
SELECT:
cmd.CommandText = "SELECT ID, name FROM user"; SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("ID: " + reader.GetInt16(0)); Console.WriteLine("name: " + reader.GetString(1)); } }
模板程序:
using System; using System.Data; using System.Data.Common; using System.Data.SQLite; namespace SQLiteQueryBrowser { /// <summary> /// 说明:这是一个针对System.Data.SQLite的数据库常规操作封装的通用类。 /// </summary> public class SQLiteDBHelper { private string connectionString = string.Empty; /// <summary> /// 构造函数 /// </summary> /// <param name="dbPath">SQLite数据库文件路径</param> public SQLiteDBHelper(string dbPath) { this.connectionString = "Data Source=" + dbPath; } /// <summary> /// 创建SQLite数据库文件 /// </summary> /// <param name="dbPath">要创建的SQLite数据库文件路径</param> public static void CreateDB(string dbPath) { using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) { connection.Open(); using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE Demo"; command.ExecuteNonQuery(); } } } /// <summary> /// 对SQLite数据库执行增删改操作,返回受影响的行数。 /// </summary> /// <param name="sql">要执行的增删改的SQL语句</param> /// <param name="parameters">执行增删改语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters) { int affectedRows = 0; using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (DbTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; if (parameters != null) { command.Parameters.AddRange(parameters); } affectedRows = command.ExecuteNonQuery(); } transaction.Commit(); } } return affectedRows; } /// <summary> /// 执行一个查询语句,返回一个关联的SQLiteDataReader实例 /// </summary> /// <param name="sql">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters) { SQLiteConnection connection = new SQLiteConnection(connectionString); SQLiteCommand command = new SQLiteCommand(sql, connection); if (parameters != null) { command.Parameters.AddRange(parameters); } connection.Open(); return command.ExecuteReader(CommandBehavior.CloseConnection); } /// <summary> /// 执行一个查询语句,返回一个包含查询结果的DataTable /// </summary> /// <param name="sql">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters) { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { if (parameters != null) { command.Parameters.AddRange(parameters); } SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); DataTable data = new DataTable(); adapter.Fill(data); return data; } } } /// <summary> /// 执行一个查询语句,返回查询结果的第一行第一列 /// </summary> /// <param name="sql">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public Object ExecuteScalar(string sql, SQLiteParameter[] parameters) { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { if (parameters != null) { command.Parameters.AddRange(parameters); } SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); DataTable data = new DataTable(); adapter.Fill(data); return data; } } } /// <summary> /// 查询数据库中的所有数据类型信息 /// </summary> /// <returns></returns> public DataTable GetSchema() { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); DataTable data=connection.GetSchema("TABLES"); connection.Close(); //foreach (DataColumn column in data.Columns) //{ // Console.WriteLine(column.ColumnName); //} return data; } } } }
完整的程序例子:
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.Common; using System.Data.SQLite; using SQLiteQueryBrowser; namespace SQLiteDemo { class Program { static void Main(string[] args) { //CreateTable(); //InsertData(); ShowData(); Console.ReadLine(); } public static void CreateTable() { string dbPath = "D:\\Demo.db3"; //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteDBHelper.CreateDB("D:\\Demo.db3"); } SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)"; db.ExecuteNonQuery(sql, null); } public static void InsertData() { string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)"; SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); for (char c = "A"; c <= "Z"; c++) { for (int i = 0; i < 100; i++) { SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@Name",c+i.ToString()), new SQLiteParameter("@TypeName",c.ToString()), new SQLiteParameter("@addDate",DateTime.Now), new SQLiteParameter("@UpdateTime",DateTime.Now.Date), new SQLiteParameter("@Time",DateTime.Now.ToShortTimeString()), new SQLiteParameter("@Comments","Just a Test"+i) }; db.ExecuteNonQuery(sql, parameters); } } } public static void ShowData() { //查询从50条起的20条记录 string sql = "select * from test3 order by id desc limit 50 offset 20"; SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1)); } } } } }
在实际情况中,采用通用类大批量插入数据会有些慢,这是因为在System.Data.SQLite中的操作如果没有指定操作,则会被当做一个事物,如果需要一次性写入大量记录,则建议显式创建一个事物,在这个事务中完成所有的操作比较好,这样的话比每次操作创建一个事物的效率要提升很多。
相关阅读 >>
基于java实现一个简单的单词本android app的实践
更多相关阅读请进入《Sqlite》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
相关推荐
评论
管理员已关闭评论功能...
- 欢迎访问木庄网络博客
- 可复制:代码框内的文字。
- 方法:Ctrl+C。