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; }
}