CSharp使用Sqlite

概览：

• 参考

• 安装Sqlite
• 封装函数
  • 调用
• 调试

参考

• 阿凡卢 C#操作SQLite数据库

安装Sqlite

以Visual Studio 2019 为例，右击项目名称》》选择“管理NuGet程序包”，然后在浏览里搜索"Sqlite"，安装"System.Data.SqLite.Core"的包。

封装函数

class SqliteOperation
    {
        public SQLiteConnection LiteConnection;

        public void CreateDatebase(string database_name)
        {
            SQLiteConnection.CreateFile(database_name);
        }

        public void ConnecToDatebase(string database_name)
        {
            LiteConnection = new SQLiteConnection("Data Source=" + database_name + ";Version=3;");
            LiteConnection.Open();
        }

        public void CloseToDatebase(string database_name)
        {
            LiteConnection = new SQLiteConnection("Data Source=" + database_name + ";Version=3;");
            LiteConnection.Close();
        }

        public void CreateSqliteTable(string table_name)
        {
            string sql_command = "create table " + table_name + "(title varchar(127), post date,edit date,tags varchar(127),categories vachar(127))";
            SQLiteCommand command = new SQLiteCommand(sql_command, LiteConnection);
            command.ExecuteNonQuery();
        }

        public void InsertSqlite(string table_name, string insert_title, DateTime insert_post, DateTime insert_edit, string insert_tags, string insert_categories)
        {
            string sql_command = "insert into " + table_name + " (title, post,edit,tags,categories) values (\"" + insert_title + "\",\"" + insert_post + "\",\"" + insert_edit + "\",\"" + insert_tags + "\",\"" + insert_categories + "\")";
            SQLiteCommand command = new SQLiteCommand(sql_command, LiteConnection);
            command.ExecuteNonQuery();
        }

    }
}

调用

class Program
    {
        static bool CheckDatabaseExists(string database_name)
        {
            bool status = false;
            if (File.Exists(database_name))
            {
                status = true;
            }
            return status;
        }

        static void Main(string[] args)
        {
            //sql test
            SqliteOperation Article_Sqlite = new SqliteOperation();
            string database_table = "Article";
            //数据库操作
            string database_full_name = "sql_test.sqlite";
            if (!CheckDatabaseExists(database_full_name))
            {
                Console.WriteLine("数据库不存在");
                Article_Sqlite.CreateDatebase(database_full_name);
                Article_Sqlite.ConnecToDatebase(database_full_name);
                Console.WriteLine("创建数据库:"+database_full_name+" Successful& 连接数据库成功.");
                //create table
                Article_Sqlite.CreateSqliteTable("Article");
                Console.WriteLine("创建表: Article 成功");
            }
            else
            {
                Console.WriteLine("数据库已存在");
                Article_Sqlite.ConnecToDatebase(database_full_name);
                //insert item
                Article_Sqlite.InsertSqlite("Article", "hello world", DateTime.Now, DateTime.Now, "hexo,linux,github", "hexo,linux,github");
                Console.WriteLine("Insert Successful.");
                Console.WriteLine("更新数据库成功");
            }

            Article_Sqlite.CloseToDatebase(database_full_name);
            Console.WriteLine("Unit Test Done");
            Console.ReadKey();
        }

    }

Console output 第一次

数据库不存在
创建数据库:sql_test.sqlite Successful& 连接数据库成功.
创建表: Article 成功

Console output 第二次

数据库已存在
Insert Successful.
更新数据库成功

调试

sqlbrowser