鸟语天空

System.Data.SQLite

作者：追风剑情 发布于：2018-4-26 14:40 分类：C#

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

1. 下载System.Data.SQLite.dll并添加到项目中

下载地址: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

带bundle的为混合编译(同时支持x86和x64)，只需在项目中引用System.Data.SQLite.dll动态库即可。

示例

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.Data;
using System.Data.SQLite;

namespace SqliteTest
{
    public class SqliteHelper
    {
        private SQLiteConnection dbConnection;
        private SQLiteCommand dbCommand;
        private SQLiteDataReader dataReader;

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="connectionString">例如: "data source=mydb.db"</param>
        public bool Connect(string db_path)
        {
            try
            {
                //如果db文件不存在,会自动创建一个新的db文件
                string connectionString = string.Format("data source={0}", db_path);
                dbConnection = new SQLiteConnection(connectionString);
                dbConnection.Open();
                return true;
            }
            catch (Exception e)
            {
                Log(e.ToString());
            }
            return false;
        }

        //执行SQL：查询sqlite版本号
        public string SQLiteVersion()
        {
            string version = "0.0.0";
            SQLiteDataReader dataReader = ExecuteQuery("SELECT sqlite_version() AS 'SQLite Version'");
            if (dataReader == null)
                return version;
            if (!dataReader.HasRows)
                return version;
            if (!dataReader.Read())
                return version;
            if (dataReader.IsDBNull(0))
                return version;
            version = dataReader.GetString(0);
            dataReader.Close();
            return version;
        }

        //执行SQL: 查
        public SQLiteDataReader ExecuteQuery(string sql)
        {
            try
            {
                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sql;
                dataReader = dbCommand.ExecuteReader();
            }
            catch (Exception e)
            {
                Log(e.Message);
            }

            return dataReader;
        }

        //执行SQL：增、删、改操作
        public int ExecuteNonQuery(string sql)
        {
            int result_code = -1;
            try
            {
                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sql;
                result_code = dbCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log(e.Message);
            }

            return result_code;
        }

        public void Close()
        {
            if (dbCommand != null)
            {
                dbCommand.Cancel();
            }
            dbCommand = null;

            if (dataReader != null)
            {
                dataReader.Close();
            }
            dataReader = null;

            if (dbConnection != null)
            {
                dbConnection.Close();
            }
            dbConnection = null;
        }

        static void Log(string s)
        {
            Console.WriteLine("SqliteHelper: {0}", s);
        }
        // 将对象字段转SQL字段
        public string ObjectToSqlFields(Type type)
        {
            FieldInfo[] fis = type.GetFields();
            StringBuilder sb = new StringBuilder();
            string field = "";
            for (int i = 0; i < fis.Length; i++)
            {
                FieldInfo fi = fis[i];
                switch (fi.FieldType.Name)
                {
                    case "Boolean":
                    case "Byte":
                    case "Int16":
                    case "UInt16":
                    case "Int32":
                    case "UInt32":
                    case "Int64":
                    case "UInt64":
                        field = string.Format("{0} INTEGER,", fi.Name);
                        break;
                    case "Single":
                    case "Double":
                        field = string.Format("{0} REAL,", fi.Name);
                        break;
                    case "String":
                        field = string.Format("{0} TEXT,", fi.Name);
                        break;
                    case "Byte[]":
                        field = string.Format("{0} BLOB,", fi.Name);
                        break;
                }
                sb.Append(field);
            }
            string fields = sb.ToString();
            fields = fields.Remove(fields.Length - 1);//删除最后一个逗号
            return fields;
        }

        public string ObjectToSqlFields(Object obj)
        {
            Type type = obj.GetType();
            return ObjectToSqlFields(type);
        }

        // 对象转SQL命令
        public SQLiteCommand ObjectToCommand(Object obj, out string sql_fields, out string sql_values, out string sql_field_value)
        {
            dbCommand = dbConnection.CreateCommand();
            Type type = obj.GetType();
            FieldInfo[] fis = type.GetFields();
            StringBuilder sb_field = new StringBuilder();
            StringBuilder sb_value = new StringBuilder();
            StringBuilder sb_field_value = new StringBuilder();
            string value = "";
            for (int i = 0; i < fis.Length; i++)
            {
                FieldInfo fi = fis[i];
                switch (fi.FieldType.Name)
                {
                    case "String":
                        value = "\"" + fi.GetValue(obj).ToString() + "\"";
                        sb_field.Append(fi.Name + ",");
                        sb_value.Append(value + ",");
                        sb_field_value.Append(string.Format("{0}={1},", fi.Name, value));
                        break;
                    case "Byte[]":
                        string value_name = "@" + fi.Name;
                        sb_field.Append(fi.Name + ",");
                        sb_value.Append(value_name + ",");
                        dbCommand.Parameters.Add(value_name, DbType.Binary).Value = fi.GetValue(obj);
                        break;
                    default:
                        value = fi.GetValue(obj).ToString();
                        sb_field.Append(fi.Name + ",");
                        sb_value.Append(value + ",");
                        sb_field_value.Append(string.Format("{0}={1},", fi.Name, value));
                        break;
                }
            }
            sql_fields = sb_field.ToString();
            sql_fields = sql_fields.Remove(sql_fields.Length - 1);//删除最后一个逗号
            sql_values = sb_value.ToString();
            sql_values = sql_values.Remove(sql_values.Length - 1);//删除最后一个逗号
            sql_field_value = sb_field_value.ToString();
            sql_field_value = sql_field_value.Remove(sql_field_value.Length - 1);//删除最后一个逗号
            return dbCommand;
        }

        // 根据对象字段创建数据表
        public int CreateTable(Object table)
        {
            Type type = table.GetType();
            return CreateTable(type);
        }

        public int CreateTable(Type type)
        {
            string table_name = type.Name;
            table_name = table_name.Replace("_Table", "");
            table_name = table_name.Replace("Table", "");
            string fields = ObjectToSqlFields(type);
            //rowid: 默认创建的主键
            //datetime('now', 'localtime') 本地电脑时间
            //datetime('now', 'utc') UTC时间
            //strftime('%s','now') 时间戳,从1970-01-01算起的秒数
            string sql = string.Format("CREATE TABLE IF NOT EXISTS {0} ({1},timestamp default (strftime('%s','now')))", table_name, fields);
            Console.WriteLine("Create SQL: " + sql);
            return ExecuteNonQuery(sql);
        }

        // 插入对象
        public int Insert(Object obj)
        {
            int result_code = -1;
            try
            {
                string table_name = obj.GetType().Name;
                string sql_fields;
                string sql_values;
                string sql_field_value;
                dbCommand = ObjectToCommand(obj, out sql_fields, out sql_values, out sql_field_value);
                string sql = string.Format("INSERT INTO {0} ({1}) VALUES({2})", table_name, sql_fields, sql_values);
                dbCommand.CommandText = sql;
                Console.WriteLine("Insert SQL: "+sql);
                result_code = dbCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log(e.Message);
            }
            return result_code;
        }

        // 更新记录
        public int Update(Object obj, string sql_where_condition)
        {
            int result_code = -1;
            try
            {
                string table_name = obj.GetType().Name;
                string sql_fields;
                string sql_values;
                string sql_field_value;
                dbCommand = ObjectToCommand(obj, out sql_fields, out sql_values, out sql_field_value);
                string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", table_name, sql_field_value, sql_where_condition);
                dbCommand.CommandText = sql;
                Console.WriteLine("Update SQL: " + sql);
                result_code = dbCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log(e.Message);
            }
            return result_code;
        }

        // 删除记录
        public int Delete(string table_name, string sql_where_condition)
        {
            string sql = string.Format("DELETE FROM {0} WHERE {1}", table_name, sql_where_condition);
            return ExecuteNonQuery(sql);
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Collections.Specialized;

namespace SqliteTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SqliteHelper sqlite = new SqliteHelper();
            bool success = sqlite.Connect(@"D:\ssss\trunk\client\.svn\wc.db");
            Console.WriteLine("Connect: {0}", success);
            SQLiteDataReader reader = sqlite.ExecuteQuery(@"select rowid, local_relpath from NODES order by rowid ASC limit 0,4");
            Console.WriteLine("FieldCount: {0}", reader.FieldCount);
            Console.WriteLine("VisibleFieldCount: {0}", reader.VisibleFieldCount);
            Console.WriteLine("StepCount: {0}", reader.StepCount);
            Console.WriteLine("HasRows: {0}", reader.HasRows);

            /*StringBuilder sb = new StringBuilder();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                //reader.GetDataTypeName(i);//获取列数据类型
                //reader.GetName(i);//获取列名称
                //sb.Append(reader.GetName(i) + ",");
            }
            Console.WriteLine(sb.ToString());*/

            while(reader.Read())
            {
                //按列名读取每一行数据
                Console.WriteLine("{0}, {1}",
                    reader.GetInt32(reader.GetOrdinal("rowid")),
                    reader.GetString(reader.GetOrdinal("local_relpath")));
            }
            Console.Read();
        }
    }
}

运行测试

开发中可能会遇到的报错:

“System.BadImageFormatException”类型的第一次机会异常在 System.Data.SQLite.dll 中发生
SqliteHelper: System.BadImageFormatException: 试图加载格式不正确的程序。 (异常来自 HRESULT:0x8007000B)
   在 System.Data.SQLite.UnsafeNativeMethods.sqlite3_config_none(SQLiteConfigOpsEnum op)
   在 System.Data.SQLite.SQLite3.StaticIsInitialized() 位置 c:\dev\sqlite\dotnet\System.Data.SQLite\SQLite3.cs:行号 3931
   在 System.Data.SQLite.SQLiteLog.Initialize() 位置 c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteLog.cs:行号 141
   在 System.Data.SQLite.SQLiteConnection..ctor(String connectionString, Boolean parseViaFramework) 位置 c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:行号 1722
   在 System.Data.SQLite.SQLiteConnection..ctor(String connectionString) 位置 c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:行号 1659
   
原因：当前所使用的.NET Framework版本与System.Data.SQLite所使用的版本不一致
属性->应用程序->目标框架

如果还是报这个错，请用Visual Studio 的NuGet工具安装

示例二：将数据库中的数据显示到控件中

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.IO;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace Test7
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            Init();
        }

        private void Init()
        {
            string db_path = Environment.CurrentDirectory + "\\info.db";
            Console.WriteLine(db_path);

            SqliteHelper db = new SqliteHelper();
            bool success = db.Connect(db_path);
            if (!success)
            {
                MessageBox.Show("连接数据库失败!");
                return;
            }

            Console.WriteLine("Connect: {0}", success);

            /*
            string sql_create_table = "CREATE TABLE IF NOT EXISTS user_info(row_id INTEGER PRIMARY KEY AUTOINCREMENT, sex INT, name TEXT, reg_date CURRENT_TIMESTAMP)";
            int result_code = db.ExecuteNonQuery(sql_create_table);
            Console.WriteLine("result_code: {0}", result_code);

            string sql_insert = @"INSERT INTO user_info(sex, name, reg_date) VALUES(0, '明明', CURRENT_TIMESTAMP)";
            result_code = db.ExecuteNonQuery(sql_insert);
            Console.WriteLine("result_code: {0}", result_code);
             * */

            string sql_query = "SELECT * FROM 'user_info'";
            SQLiteDataReader reader = db.ExecuteQuery(sql_query);
            while(reader.Read())
            {
                //逐行读取数据库中记录
                int row_id = reader.GetInt32(reader.GetOrdinal("row_id"));
                int sex = reader.GetInt32(reader.GetOrdinal("sex"));
                string name = reader.GetString(reader.GetOrdinal("name"));
                string reg_date = reader.GetString(reader.GetOrdinal("reg_date"));
                //将数据插入DataGridView控件中
                int index = this.dataGridView1.Rows.Add();
                this.dataGridView1.Rows[index].Cells[0].Value = row_id;
                this.dataGridView1.Rows[index].Cells[1].Value = sex;
                this.dataGridView1.Rows[index].Cells[2].Value = name;
                this.dataGridView1.Rows[index].Cells[3].Value = reg_date;
            }
            //设置列标题居中显示
            this.dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }
    }
}

运行测试

示例三：根据对象字段创建数据表

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data.SQLite;

namespace ConsoleApp4
{
    class Program
    {
        static void Main(string[] args)
        {
            SqliteHelper sqlite = new SqliteHelper();
            bool success = sqlite.Connect(@"D:\wc.db");
            Console.WriteLine("Connect: {0}", success);

            Object obj = new UserInfo();
            int resultCode = sqlite.CreateTable(obj);
            Console.WriteLine("返回码：" + resultCode);

            Console.ReadKey();
        }
    }

    public class UserInfo
    {
        public int id = 1;
        public string name = "aaa";
        public int age = 17;
        public byte[] bs = null;
    }
}

运行测试

Navicat for SQLite打开db文件查看

示例四：插入、更新记录

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data.SQLite;

namespace ConsoleApp4
{
    class Program
    {
        static void Main(string[] args)
        {
            SqliteHelper sqlite = new SqliteHelper();
            bool success = sqlite.Connect(@"D:\wc.db");
            Console.WriteLine("Connect: {0}", success);

            UserInfo obj = new UserInfo();
            int result_code = sqlite.CreateTable(typeof(UserInfo));
            Console.WriteLine("创建表 返回码="+result_code);

            result_code = sqlite.Insert(obj);
            Console.WriteLine("插入记录 返回码=" + result_code);

            obj.name = "name_update";
            obj.age = 25;
            obj.bytes = new byte[] { 90, 58, 39 };
            result_code = sqlite.Update(obj, "id=1");
            Console.WriteLine("更新记录 返回码=" + result_code);

            Console.ReadKey();
        }
    }

    public class UserInfo
    {
        public int id = 1;
        public string name = "name";
        public int age = 17;
        public byte[] bytes = new byte[] { 19, 48, 199 };
    }
}

运行测试

Navicat for SQLite打开db文件查看

更多示例：

DataGridView显示数据库数据

在 .NET Core 工程中使用SQLite

标签: C#