[转]C# 操作Sqlite

    public class BookDAL
    {
        public static bool CreateBook(Book book)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection("Data Source=db/Books.sqlite;");
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO Book(ID, BookName, Price, Rowguid) VALUES(@ID1, @BookName1, @Price1, @Rowguid1)";
                cmd.Parameters.Add(new SQLiteParameter("ID1", book.ID));
                cmd.Parameters.Add(new SQLiteParameter("BookName1", book.BookName));
                cmd.Parameters.Add(new SQLiteParameter("Price1", book.Price));
                cmd.Parameters.Add(new SQLiteParameter("Rowguid1", book.Rowguid));

                int i = cmd.ExecuteNonQuery();
                return i == 1;
            }
            catch (SQLiteException se)
            {
                MessageBox.Show(se.Message + " \n\n" + se.Source + "\n\n" + se.StackTrace + "\n\n" + se.Data);
                return false;
            }
            catch (ArgumentException ae)
            {
                MessageBox.Show(ae.Message + " \n\n" + ae.Source + "\n\n" + ae.StackTrace + "\n\n" + ae.Data);
                return false;
            }
            catch (Exception ex)
            {
                //Do　any　logging　operation　here　if　necessary
                MessageBox.Show(ex.Message + "\n\n" + ex.Source + "\n\n" + ex.StackTrace + "\n\n" + ex.Data);
                return false;
            }            
        }

        public static bool UpdateBookByID(Book book)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection("Data Source=db/Books.sqlite;"))
                {
                    conn.Open();
                    SQLiteCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "update Book set BookName=@BookName1,Price=@Price1, Rowguid=@Rowguid1 where ID=@ID1;";
                    cmd.Parameters.Add(new SQLiteParameter("ID1", book.ID));
                    cmd.Parameters.Add(new SQLiteParameter("BookName1", book.BookName));
                    cmd.Parameters.Add(new SQLiteParameter("Price1", book.Price));
                    cmd.Parameters.Add(new SQLiteParameter("Rowguid1", book.Rowguid));
                    int i = cmd.ExecuteNonQuery();
                    return i == 1;
                }
            }
            catch (ArgumentException ae)
            {
                MessageBox.Show(ae.Message + " \n\n" + ae.Source + "\n\n" + ae.StackTrace);
                return false;
            }
            catch (Exception ex)
            {
                //Do　any　logging　operation　here　if　necessary
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public static bool UpdateBookByGuid(Book book)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection("Data Source=db/Books.sqlite;"))
                {
                    conn.Open();
                    SQLiteCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "update Book set ID=@ID1,BookName=@BookName1,Price=@Price1 where Rowguid=@Rowguid1;";
                    cmd.Parameters.Add(new SQLiteParameter("ID1", book.ID));
                    cmd.Parameters.Add(new SQLiteParameter("BookName1", book.BookName));
                    cmd.Parameters.Add(new SQLiteParameter("Price1", book.Price));
                    cmd.Parameters.Add(new SQLiteParameter("Rowguid1", book.Rowguid));
                    int i = cmd.ExecuteNonQuery();
                    return i == 1;
                }
            }
            catch (ArgumentException ae)
            {
                MessageBox.Show(ae.Message + " \n\n" + ae.Source + "\n\n" + ae.StackTrace);
                return false;
            }
            catch (Exception ex)
            {
                //Do　any　logging　operation　here　if　necessary
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public static bool DeleteBook(int ID)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection("Data Source=db/Books.sqlite;"))
                {
                    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 (ArgumentException ae)
            {
                MessageBox.Show(ae.Message + " \n\n" + ae.Source + "\n\n" + ae.StackTrace);
                return false;
            }
            catch (Exception ex)
            {
                //Do　any　logging　operation　here　if　necessary
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public static Book GetBookByID(int ID)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection("Data Source=db/Books.sqlite;"))
                {
                    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 (ArgumentException ae)
            {
                MessageBox.Show(ae.Message + " \n\n" + ae.Source + "\n\n" + ae.StackTrace);
                return null;
            }
            catch (Exception ex)
            {
                //Do　any　logging　operation　here　if　necessary
                throw new Exception(ex.Message);
            }
        }

        public static DataTable GetAllBook()
        {
            DataTable dt = new DataTable();
            try
            {
                SQLiteConnection conn = new SQLiteConnection("Data Source=db/Books.sqlite;");
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(conn);
                cmd.CommandText = "SELECT * FROM Book";
                cmd.CommandType = CommandType.Text;
                //Console.WriteLine(cmd.CommandText);
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                else {
                    //throw new NullReferenceException("No Record Available.");
                }

                dr.Close();
                conn.Close();
                                
            }
            catch (ArgumentException ae)
            {
                MessageBox.Show(ae.Message + " \n\n" + ae.Source + "\n\n" + ae.StackTrace + "\n\n" + ae.Data);
            }
            catch (Exception ex)
            {
                //throw new Exception(ex.Message);
                MessageBox.Show(ex.Message + " \n\n" + ex.Source + "\n\n" + ex.StackTrace + "\n\n" + ex.Data);
            }

            return dt;
        }
    }