SQLite数据库的使用

1.SQLite简介

SQLite是一个流行的关系型数据库管理系统，SQLite是轻量级的，SQLite不需要复杂的安装或配置过程。数据库就是一个磁盘文件，不需要运行一个独立的服务进程，可以很方便地在不同的应用程序和设备之间共享和传输。

2.使用C#语言示例

①单独创建一个SQLiteHelper类

internal class SQLiteHelper
{
    private string _connectionString;

    public SQLiteHelper(string connectionString)
    {
        _connectionString = connectionString;
    }

    // 执行非查询SQL命令（如INSERT、UPDATE、DELETE）  
    public void ExecuteNonQuery(string sql)
    {
        using (var connection = new SQLiteConnection(_connectionString))
        {
            connection.Open();
            using (var command = new SQLiteCommand(sql, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }

    // 执行查询并返回第一行的第一列数据  
    public object ExecuteScalar(string sql)
    {
        using (var connection = new SQLiteConnection(_connectionString))
        {
            connection.Open();
            using (var command = new SQLiteCommand(sql, connection))
            {
                return command.ExecuteScalar();
            }
        }
    }

    // 执行查询并返回数据表  
    public SQLiteDataReader ExecuteReader(string sql)
    {
        var connection = new SQLiteConnection(_connectionString);
        connection.Open();
        var command = new SQLiteCommand(sql, connection);
        return command.ExecuteReader();
    }

    // 注意：ExecuteReader的调用者需要确保调用其Dispose方法或使用using语句  
}

②调用SQLiteHelper类，查询表数据，并赋值到List列表中

        private List<CustomerModel> void GetData()
        {
            string _connectionString = "Data Source=mydatabase.db;Version=3;";
            List<CustomerModel> dataSourceList = new List<CustomerModel>();
            var dbHelper = new SQLiteHelper(_connectionString);

            string sql = "SELECT * FROM CustomerTable order by UpdateTime desc,Name asc";
            using (var reader = dbHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    CustomerModel model = new CustomerModel();                    
                    model.ID = reader["ID"].ToString();
                    model.Name = reader["Name"].ToString();
                    model.Telephone = reader["Telephone"].ToString();
                    model.CreateTime= Convert.ToDateTime(reader["CreateTime"]);
                    model.UpdateTime= Convert.ToDateTime(reader["UpdateTime"]);
                    dataSourceList.Add(model);
                }
            }
            return dataSourceList;
        }


    public class CustomerModel
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string Telephone { get; set; }
        public DateTime CreateTime { get; set; }
        public DateTime UpdateTime { get; set; }
    }