C#使用SQLite数据库详解

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); 



        }

    }

} 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值