WPF操作SQLite

IDE:VS2017 community

通过NuGet获取SQLite

SQLite增、删、改、查：

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace HelloSQLite
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        string DBPath = "Data Source=" + AppDomain.CurrentDomain.BaseDirectory + @"HelloSQLite.db";
        private SQLiteConnection DBConnection = null;

        public MainWindow()
        {
            InitializeComponent();
        }

        private void tbSQLiteOpen_Click(object sender, RoutedEventArgs e)
        {
            DBConnection = new SQLiteConnection(DBPath);
            DBConnection?.Open();
        }

        private void tbSQLiteCreateTable_Click(object sender, RoutedEventArgs e)
        {
            int ret = -1;
            string sql;
            bool tableExists = false;
            SQLiteCommand cmd = new SQLiteCommand();

            cmd.Connection = DBConnection;

            /* 表是否存在 */
            sql = "SELECT * FROM sqlite_master WHERE type='table' and name='persons'";
            cmd.CommandText = sql;
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    tableExists = true;
                }
            }

            /* 表不存在，则创建表 */
            if (!tableExists)
            {
                sql = "CREATE TABLE IF NOT EXISTS persons(id INTEGER PRIMARY KEY, name VARCHAR(20), age INTEGER, address VARCHAR(100), data BLOB);";
                cmd.CommandText = sql;
                ret = cmd.ExecuteNonQuery();
                Console.WriteLine($"创建表返回: {ret}");
            }
        }

        private void tbSQLiteInsert_Click(object sender, RoutedEventArgs e)
        {
            int ret = -1;
            string sql;
            byte[] datas = new byte[] { 0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07};
            SQLiteCommand cmd = new SQLiteCommand();

            cmd.Connection = DBConnection;

            /* 插入一条数据 */
#if DEBUG // 方式一
            sql = $"INSERT INTO persons(id, name, age, address, data) VALUES (@id, @name, @age, @address, @data)";

            cmd.CommandText = sql;
            cmd.Parameters.Add(new SQLiteParameter("@id", 2));
            cmd.Parameters.Add(new SQLiteParameter("@name", "王五"));
            cmd.Parameters.Add(new SQLiteParameter("@age", 12));
            cmd.Parameters.Add(new SQLiteParameter("@address", "中国北京"));
            cmd.Parameters.Add(new SQLiteParameter("@data", datas));
#else // 方式二
            sql = string.Format($"INSERT INTO persons(id, name, age, address) VALUES ('{1000}','{"张三"}','{20}', '{"中国-广东深圳坂田"}')");
            cmd.CommandText = sql;
#endif
            ret = cmd.ExecuteNonQuery();
            Console.WriteLine($"{ret}行被插入！");
        }

        private void tbSQLiteDelete_Click(object sender, RoutedEventArgs e)
        {
            int ret = -1;
            string sql;
            SQLiteCommand cmd = new SQLiteCommand();

            cmd.Connection = DBConnection;

            /* 删除数据 */
            sql = "DELETE FROM persons";
            cmd.CommandText = sql;
            ret = cmd.ExecuteNonQuery();

            Console.WriteLine($"{ret}行被删除！");
        }

        private void tbSQLiteUpdate_Click(object sender, RoutedEventArgs e)
        {
            int ret = -1;
            string sql;
            SQLiteCommand cmd = new SQLiteCommand();

            cmd.Connection = DBConnection;

            /* 更新数据 */
            sql = "UPDATE persons SET age=21 WHERE id=2";
            cmd.CommandText = sql;
            ret = cmd.ExecuteNonQuery();

            Console.WriteLine($"{ret}行被修改！");
        }

        private void tbSQLiteQuery_Click(object sender, RoutedEventArgs e)
        {
            string sql;
            SQLiteCommand cmd = new SQLiteCommand();

            cmd.Connection = DBConnection;

            /* 查询数据库 */
            sql = "select * from persons";
            cmd.CommandText = sql;

            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                Console.WriteLine($"Depth: {reader.Depth}");
                Console.WriteLine($"HasRows: {reader.HasRows}");
                Console.WriteLine($"Column count: {reader.FieldCount}");
                Console.WriteLine($"RecordsAffected: {reader.RecordsAffected}");
                Console.WriteLine($"StepCount: {reader.StepCount}");
                Console.WriteLine($"VisibleFieldCount: {reader.VisibleFieldCount}");
                Console.WriteLine($"*************************************************");
                while (reader.Read())
                {
                    Console.WriteLine($"ID: {reader.GetInt32(0)} {Environment.NewLine}" +
                                    $"Name: {reader.GetString(1)} {Environment.NewLine}" +
                                    $"Age: {reader.GetInt32(2)} {Environment.NewLine}" +
                                    $"Address: {reader.GetString(3)} {Environment.NewLine}");
                    
                    long size = reader.GetStream(4).Length;// 获取第4列长度
                    byte[] d = new byte[size];
                    long len = reader.GetBytes(4, 0, d, 0, (int)size);
                    Console.WriteLine(StringHelper.Bytes2HexString(d));
                    Console.WriteLine($"=======================================================================");
                }
            }
        }

        private void tbSQLiteClose_Click(object sender, RoutedEventArgs e)
        {
            DBConnection?.Close();
        }
    }
}

byte数组转十六进制字符串

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HelloSQLite
{
    public class StringHelper
    {
        public static string Bytes2HexString(byte[] bytes)
        {
            string hexString = string.Empty;

            if (null != bytes)
            {
                StringBuilder sb = new StringBuilder();

                for (int i = 0; i < bytes.Length; i++)
                {
                    sb.Append(bytes[i].ToString("X2") + " ");
                }

                hexString = sb.ToString();
            }
            return hexString;
        }
    }
}