友情提示:本文所述代码下载地址 http://download.csdn.net/source/2299805
SQLite 是一个嵌入式的关系数据库系统,运用十分广泛。在一些数据处理量不大的应用程序中,使用SQLite可以很大程度的降低部署时的工作量。要使VS2008支持SQLite十分简单,只需要下载 SQLite-1.0.66.0-setup.exe,安装即可。下载地址http://download.csdn.net/detail/andylaufzf/3787179
打开Visual Studio 2008,新建一个Console Application,项目名称为SQLite,并为项目添加System.Data.SQLite的引用。再添加一个数据库连接,此时可以看到,更改数据源窗口中多了一个SQLite Database File的数据源选项,如图:
挑选此类型的数据源,并且New一个Database文件test.db,密码123,如图:
接下来再新数据库中添加一张表Book,如图:
下面开始为此表建立一个Data Access类,以展示在VS2008中如何使用SQLite,可以想象,和操作其他数据库是几乎一样的。
首先,新建一个实体类 Book.cs
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- namespace SQLite
- {
- 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类:BookDAL
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data.SQLite;
- namespace SQLite
- {
- class BookDAL
- {
- private const string sConn = "Data Source=G://Exercise//Visual Studio 2008//SQLite//test.db;Version=3;Password=123;";
- public static bool Create(Book book)
- {
- try
- {
- using (SQLiteConnection conn = new SQLiteConnection(sConn))
- {
- conn.Open();
- SQLiteCommand cmd = conn.CreateCommand();
- cmd.CommandText = "INSERT INTO book(ID,BookName,Price) 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(sConn))
- {
- 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(sConn))
- {
- 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(sConn))
- {
- 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;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- namespace SQLite
- {
- class Program
- {
- static void Main(string[] args)
- {
- if (BookDAL.Delete(1) == false)
- Console.WriteLine("删除 ID=1 失败");
- if (BookDAL.Delete(2) == false)
- Console.WriteLine("删除 ID=2 失败");
- Book book = new Book();
- book.ID = 1;
- book.BookName = "Book A";
- book.Price = 40.0m;
- BookDAL.Create(book);
- book.ID = 2;
- book.BookName = "第二本书";
- book.Price = 42.0m;
- BookDAL.Create(book);
- book = BookDAL.GetbyID(2);
- Console.WriteLine(book.ID + " " + book.BookName + " " + book.Price);
- book.Price = 24.5m;
- 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 第二本书 42
2 第二本书 24.5
1 Book A 40