sqlite资源、安装、及应用示例

1 sqlite资源

sqlite资源下载页面：https://www.sqlite.org/download.html

下载以下两个文件即可

2 sqlite安装

解压sqlite到相应位置，然后把里面的这几个文件复制到相应文件夹，并修改坏境变量的Path，增加指向这个文件夹的路径。

3 sqlite教程

sqlite菜鸟教程：https://www.runoob.com/sqlite/sqlite-tutorial.html

4 sqlite查看程序

navicat for sqlite

链接：https://pan.baidu.com/s/1piHAofhscobi_ocNeQys6w 
提取码：8n5i 
 

5 sqlite的应用代码示例（c#）

5.1 nuget添加以下dll

5.2 代码示例

工具类及使用方法

internal sealed class SqliteCmd
    {
        private string m_dbPath;
        public SqliteCmd(string dbPath)
        {
            m_dbPath = dbPath;
        }
        //---创建数据库
        internal void CreateDB()
        {
            SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
            cn.Open();
            cn.Close();
        }

        //---删除数据库
        internal void DeleteDB()
        {
            if (System.IO.File.Exists(m_dbPath))
            {
                System.IO.File.Delete(m_dbPath);
            }
        }

        /// <summary>
        /// 添加表
        /// 示例CREATE TABLE t1(id varchar(4),score int)
        /// </summary>
        /// <param name="cmdStr"></param>
        internal void CreateTable(string cmdStr)
        {
            SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
            if (cn.State != System.Data.ConnectionState.Open)
            {
                cn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = cn;
                cmd.CommandText = cmdStr;
                cmd.ExecuteNonQuery();
            }
            cn.Close();
        }

        /// <summary>
        /// 删除表
        /// 示例：DROP TABLE IF EXISTS t1
        /// </summary>
        /// <param name="cmdStr"></param>
        internal void DeleteTable(string tableName)
        {
            SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
            if (cn.State != System.Data.ConnectionState.Open)
            {
                cn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = cn;
                cmd.CommandText = $"DROP TABLE IF EXISTS {tableName}";
                cmd.ExecuteNonQuery();
            }
            cn.Close();
        }

        internal void ReadTable(string tableName)
        {
            SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
            cn.Open();
            SQLiteCommand cmd = cn.CreateCommand();

            cmd.CommandText = $"PRAGMA table_info('{tableName}')";

            //写法一
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            foreach (DataRow r in table.Rows)
            {
                Console.WriteLine($"{r["cid"]},{r["name"]},{r["type"]},{r["notnull"]},{r["dflt_value"]},{r["pk"]} ");
            }
            Console.WriteLine();
        }

        /// <summary>
        /// 执行命令行
        /// </summary>
        /// <param name="cmdStr"></param>
        internal void ExecuteCmdstr(string cmdStr)
        {
            SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
            if (cn.State != System.Data.ConnectionState.Open)
            {
                cn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = cn;
                cmd.CommandText = cmdStr;
                cmd.ExecuteNonQuery();
            }
            cn.Close();
        }

        /// <summary>
        /// 加上transaction，批量执行命令行
        /// </summary>
        /// <param name="SQLStringList"></param>
        internal  void ExecuteSqlTran(List<string> SQLStringList)
        {
            using (SQLiteConnection conn = new SQLiteConnection("data source=" + m_dbPath))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                SQLiteTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

    }

SqliteCmd sqliteCmd = new SqliteCmd(@"D:\mytest\SqilteTest.db");
                sqliteCmd.CreateDB();//创建数据库
                sqliteCmd.ExecuteCmdstr("CREATE TABLE REVITPROPERTY("+
                                        "ID INT PRIMARY KEY     NOT NULL,"+
                                        "NAME           TEXT    NOT NULL,"+
                                        "CATEGORY       CHAR(50)"
                                        );//创建表格