SQLite 是一個嵌入式的關系數據庫系統，使用十分廣泛。在一些數據量不大的應用程序中，如果使用SQLite可以極大的減少部署時的工作量。 要在C#中使用SQLite也很簡單，只要找一個C#的wrapper就可以了，例如，我使用的就是來自

http://sqlite.phxsoftware.com/? 的一個dll，System.Data.SQLite. 下載下來的文件是SQLite-1.0.65.0-setup.exe。只要安裝一下就可以使用了，非常方便。該程序符合ADO.NET的規范，并且支持Visual Studio的可視化表設計器。

打開Visual Studio 2008，新建一個Console Application，為此項目添加System.Data.SQLite的引用。添加一個數據庫連接，此時可以發現，新建連接中有了一個SQLite Database Connection，選擇此類型的數據連接，并且新建一個文件，

test.db3. 接下來在新數據庫中添加一張表，如下：

下面開始為此表建立一個Data Access類，以展示在C#中如何使用SQLite，可以想象，和操作其他數據庫是幾乎一樣的，感謝ADO.NET的功勞。

首先是一個實體類 Book.cs：

public class Book
??? {
??????? private int id;
??????? private string bookName;
??????? private decimal price;

??????? public int ID
??????? {
??????????? get { return id; }
??????????? set { id = value; }
??????? }
??????? public string BookName
??????? {
??????????? get { return bookName; }
??????????? set { bookName = value; }
??????? }
??????? public decimal Price
??????? {
??????????? get { return price; }
??????????? set { price = value; }
??????? }
??? }

編寫DAL類：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;

namespace ConsoleApplication1
{
??? public class BookDAL
??? {
??????? public static bool Create(Book book)
??????? {
??????????? try
??????????? {
??????????????? using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3"))
??????????????? {
??????????????????? conn.Open();
??????????????????? SQLiteCommand cmd = conn.CreateCommand();
??????????????????? cmd.CommandText = "insert into Book values(@ID,@BookName,@Price);";
??????????????????? cmd.Parameters.Add(new SQLiteParameter("ID", book.ID));
??????????????????? cmd.Parameters.Add(new SQLiteParameter("BookName", book.BookName));
??????????????????? cmd.Parameters.Add(new SQLiteParameter("Price", book.Price));
??????????????????? int i = cmd.ExecuteNonQuery();
??????????????????? return i == 1;
??????????????? }
??????????? }
??????????? catch (Exception)
??????????? {
??????????????? //Do any logging operation here if necessary
??????????????? return false;
??????????? }
??????? }

??????? public static bool Update(Book book)
??????? {
??????????? try
??????????? {
??????????????? using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3"))
??????????????? {
??????????????????? conn.Open();
??????????????????? SQLiteCommand cmd = conn.CreateCommand();
??????????????????? cmd.CommandText = "update Book set BookName=@BookName,Price=@Price where ID=@ID;";
??????????????????? cmd.Parameters.Add(new SQLiteParameter("ID", book.ID));
??????????????????? cmd.Parameters.Add(new SQLiteParameter("BookName", book.BookName));
??????????????????? cmd.Parameters.Add(new SQLiteParameter("Price", book.Price));
??????????????????? int i = cmd.ExecuteNonQuery();
??????????????????? return i == 1;
??????????????? }
??????????? }
??????????? catch (Exception)
??????????? {
??????????????? //Do any logging operation here if necessary
??????????????? return false;
??????????? }
??????? }

??????? public static bool Delete(int ID)
??????? {
??????????? try
??????????? {
??????????????? using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3"))
??????????????? {
??????????????????? conn.Open();
??????????????????? SQLiteCommand cmd = conn.CreateCommand();
??????????????????? cmd.CommandText = "delete from Book where ID=@ID;";
??????????????????? cmd.Parameters.Add(new SQLiteParameter("ID", ID));????????
??????????????????? int i = cmd.ExecuteNonQuery();
??????????????????? return i == 1;
??????????????? }
??????????? }
??????????? catch (Exception)
??????????? {
??????????????? //Do any logging operation here if necessary
??????????????? return false;
??????????? }
??????? }

??????? public static Book GetbyID(int ID)
??????? {
??????????? try
??????????? {
??????????????? using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3"))
??????????????? {
??????????????????? conn.Open();
??????????????????? SQLiteCommand cmd = conn.CreateCommand();
??????????????????? cmd.CommandText = "select * from Book where ID=@ID;";
??????????????????? cmd.Parameters.Add(new SQLiteParameter("ID", ID));
??????????????????? SQLiteDataReader dr = cmd.ExecuteReader();
??????????????????? if (dr.Read())
??????????????????? {
??????????????????????? Book book = new Book();
??????????????????????? book.ID = dr.GetInt32(0);
??????????????????????? book.BookName = dr.GetString(1);
??????????????????????? book.Price = dr.GetDecimal(2);
??????????????????????? return book;
??????????????????? }
??????????????????? else
??????????????????????? return null;
??????????????? }
??????????? }
??????????? catch (Exception)
??????????? {
??????????????? //Do any logging operation here if necessary
??????????????? return null;
??????????? }
??????? }
??? }
}

編寫測試主程序：

using System;

namespace ConsoleApplication1
{
??? class Program
??? {
??????? static void Main(string[] args)
??????? {
??????????? Book book = new Book();
??????????? book.ID = 1;
??????????? book.BookName = "Book A";
??????????? book.Price = 10.0m;
??????????? BookDAL.Create(book);

??????????? book.ID = 2;
??????????? book.BookName = "第二本書";
??????????? book.Price = 13.0m;
??????????? BookDAL.Create(book);

??????????? book = BookDAL.GetbyID(2);
??????????? Console.WriteLine(book.ID + " " + book.BookName + " " + book.Price);

??????????? book.Price = 11.1m;
??????????? BookDAL.Update(book);

??????????? book = BookDAL.GetbyID(2);
??????????? Console.WriteLine(book.ID + " " + book.BookName + " " + book.Price);
??????????? book = BookDAL.GetbyID(1);
??????????? Console.WriteLine(book.ID + " " + book.BookName + " " + book.Price);

??????? }
??? }
}

最終結果：

2 第二本書 13
2 第二本書 11.1
1 Book A 10

轉載于:https://www.cnblogs.com/jeriffe/articles/1806889.html

總結

以上是生活随笔為你收集整理的【转】在C#中使用SQLite的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。