SQLite資料庫的增刪改查代碼

//搜集整理了一下網上的代碼.找了半天居然找不到一條插入語句.好鬱悶的

//感覺速度還可以.小檔案.很多小應用程式在用這個資料庫

//SQLite使用辦法.直接COPYDLL檔案System.Data.SQLite.DLL到應用程式DEBUG目錄下。 然後在項目中添加引用,找到這個檔案即可

//添加引用

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.IO;
using System.Net;
using System.Data.SQLite;

 

private static string FavoriteDbPath = System.Environment.CurrentDirectory + "//Favorite.db";

private bool CreateSQLiteDb()
        {
            try
            {
                System.Data.SQLite.SQLiteConnection.CreateFile(FavoriteDbPath);
                System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder ConnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                ConnStr.DataSource = FavoriteDbPath;
                ConnStr.Password = "Admin";//設定密碼，SQLite ADO.NET實現了資料庫密碼保護
                Conn.ConnectionString = ConnStr.ToString();
                Conn.Open();
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                string Sql = "CREATE TABLE Admin(UserName varchar(20),UserPass varchar(20))";
                cmd.CommandText = Sql;
                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                Conn.Dispose();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        public bool CreateLinkDataTable()
        {
            try
            {
                System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder ConnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                ConnStr.DataSource = FavoriteDbPath;
                ConnStr.Password = "Admin";//設定密碼，SQLite ADO.NET實現了資料庫密碼保護
                Conn.ConnectionString = ConnStr.ToString();
                Conn.Open();
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                string Sql = "CREATE TABLE [FavoriteList] ([ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[LinkText] NVARCHAR(256)  NULL,[LinkUrl]  NVARCHAR(1000),[AddTime] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL)";
                cmd.CommandText = Sql;
                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                Conn.Dispose();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public bool InsertLinkDataTable(string strLinkText,string strLinkUrl,DateTime dtFileTime)
        {
            try
            {
                System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder ConnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                ConnStr.DataSource = FavoriteDbPath;
                ConnStr.Password = "Admin";//設定密碼，SQLite ADO.NET實現了資料庫密碼保護
                Conn.ConnectionString = ConnStr.ToString();
                Conn.Open();
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                string strInsertSQL = "INSERT INTO [FavoriteList] (LinkText,LinkUrl,AddTime) VALUES('" + strLinkText + "','" + strLinkUrl + "','" + dtFileTime + "');";
                Console.WriteLine(strInsertSQL);
                cmd.CommandText = strInsertSQL;
                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                Conn.Dispose();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }
        public bool GetLinkDataTableRecordList()
        {
            try
            {
                System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder ConnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                ConnStr.DataSource = FavoriteDbPath;
                ConnStr.Password = "Admin";//設定密碼，SQLite ADO.NET實現了資料庫密碼保護
                Conn.ConnectionString = ConnStr.ToString();
                Conn.Open();
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                cmd.Connection = Conn;
                cmd.CommandText = "select * from [FavoriteList]";
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine("編號 " + System.Convert.ToString(dr["id"]) + "           文本 " + (string)dr["Linktext"] + "           地址 " + (string)dr["LinkUrl"]);
                }
                Conn.Dispose();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        private void 建立資料庫檔案ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (System.IO.File.Exists(FavoriteDbPath))
                {
                    System.IO.File.Delete(FavoriteDbPath);
                }
                if (CreateSQLiteDb())
                {
                    MessageBox.Show("資料庫建立成功");
                }
                else
                {
                    MessageBox.Show("資料庫建立失敗");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("建立資料庫檔案出錯" + ex.Message);
            }
        }

        private void 建立收藏夾資料表ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (CreateLinkDataTable())
                {
                    MessageBox.Show("建立收藏夾資料表成功");
                }
                else
                {
                    MessageBox.Show("建立收藏夾資料表失敗");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("建立收藏夾資料表出錯" + ex.Message);
            }
        }

        private void 查詢收藏夾資料表ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (GetLinkDataTableRecordList())
                {
                    MessageBox.Show("查詢收藏夾資料表成功");
                }
                else
                {
                    MessageBox.Show("查詢收藏夾資料表失敗");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("查詢收藏夾資料表出錯" + ex.Message);
            }
        }

        private void 插入新的資料記錄ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (InsertLinkDataTable("GJKHKJHK","HLKJLJL",DateTime.Now))
                {
                    MessageBox.Show("插入新的資料記錄成功");
                }
                else
                {
                    MessageBox.Show("插入新的資料記錄失敗");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("插入新的資料記錄出錯" + ex.Message);
            }
        }