C#操作SQLite辦法實例詳解。本站提示廣大學習愛好者：（C#操作SQLite辦法實例詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是C#操作SQLite辦法實例詳解正文
本文實例講述了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中的操作假如沒有指定操作，則會被當作一個事物，假如須要一次性寫入年夜量記載，則建議顯式創立一個事物，在這個事務中完成一切的操作比擬好，如許的話比每次操作創立一個事物的效力要晉升許多。
終究應用VS2008供給的功效，可以看到外面的數據以下：
須要解釋的是在System.Data.SQLite中數據類型的劃定不適很嚴厲，從創立Test3表的SQL語句來看，表中addDate、UpdateTime、Time分離是DateTime、Date、Time類型字段，但現實上我們拔出的時刻沒有依照這個劃定，終究顯示的成果也是盡可能遵守數據庫字段的界說。
總結
System.Data.SQLite確切是一個異常玲珑精干的數據庫，作為對SQLite的封裝（SQLite可以在Android等類型的手機上應用Java拜訪），它仍然是體較小，同比機能高、內存消費小、無需裝置僅需一個dll便可以運轉的長處（假如在Mobile手機上則須要兩個文件），獨一的一個缺陷是沒有比擬的GUI（圖形用戶界面），不外正由於如斯它才得以體積小。
在現實開辟中沒有圖形用戶界面能夠有些未便，我們可使用VS來檢查和操作數據，我本身也做了一個小東東，便於治理和保護數據，界面以下：
假如你要開辟數據量在10萬條以下的運用，我建議你測驗考試應用一下System.Data.SQLite，它也許是一個不錯的選擇。
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));
}
}
}
願望本文所述對年夜家的C#法式設計有所贊助。
