SQLite是一种轻量级的嵌入式数据库引擎，广泛应用于各种开发项目中。System.Data.SQLite库，提供了许多用于操作数据库的功能和API。本文将分模块讲解如何使用SQLite数据库，包括数据库连接、创建表、插入数据、查询数据和更新数据等方面。以及使用Sqltie构建案例实战。
SQLite基本用法
1、引用和连接数据库
首先，在你的项目中引入 System.Data.SQLite 命名空间。然后，创建一个 SQLiteConnection 对象，并使用它连接到 SQLite 数据库。
using System.Data.SQLite;
// 创建连接对象
SQLiteConnection connection = new SQLiteConnection("Data Source=mydatabase.db;Version=3;");
// 打开连接
connection.Open();
// 关闭连接
connection.Close();2、创建表
在连接数据库后，你可以使用 SQLiteCommand 对象执行 SQL 语句来创建表。
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "CREATE TABLE IF NOT EXISTS Employees (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age INT);";
command.ExecuteNonQuery();
}这里我们使用 CREATE TABLE IF NOT EXISTS 语句来创建名为 "Employees" 的表。该表包含三列：Id、Name 和 Age。注意，AUTOINCREMENT 关键字用于自动递增生成主键值。
3、插入数据
使用 INSERT INTO 语句，可以向表中插入数据。
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "INSERT INTO Employees (Name, Age) VALUES ('John Doe', 25);";
command.ExecuteNonQuery();
}这里我们将名为 "John Doe" 的员工信息插入到 "Employees" 表中。
4、查询数据
使用 SELECT 语句，可以从表中检索数据。
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "SELECT * FROM Employees;";
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
int age = reader.GetInt32(2);
Console.WriteLine($"ID: {id}, Name: {name}, Age: {age}");
}
}
}这里我们使用 SELECT * 来查询 "Employees" 表中的所有数据，并将结果打印到控制台。
5、更新和删除数据
使用 UPDATE 和 DELETE 语句，可以更新和删除表中的数据。
// 更新数据
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "UPDATE Employees SET Age = 30 WHERE Name = 'John Doe';";
command.ExecuteNonQuery();
}
// 删除数据
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "DELETE FROM Employees WHERE Id = 1;";
command.ExecuteNonQuery();
}这里我们使用 UPDATE 语句将名为 "John Doe" 的员工年龄更新为 30，并使用 DELETE 语句删除主键为 1 的员工数据。
SQLite案例实战
以下是一个使用 ASP.NET Core Web API 和 SQLite 数据库构建完整权限管理系统的代码示例：
1、创建项目
首先，创建一个 ASP.NET Core Web API 项目。
dotnet new webapi -n PermissionManagementSystem
cd PermissionManagementSystem2、添加依赖项
在项目的 .csproj 文件中，添加对Microsoft.EntityFrameworkCore.Sqlite 和 Microsoft.EntityFrameworkCore.Design 的依赖。
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>net7.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="7.0.5" />
<PackageReference Include="Swashbuckle.AspNetCore" Version="6.4.0" />
</ItemGroup>
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="5.0.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.6">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
</ItemGroup>
</Project>运行以下命令以安装这些依赖项：
dotnet restore3、创建数据模型
在项目中创建一个名为 Permission 的数据模型类，用于表示权限。
using System.ComponentModel.DataAnnotations;
public class Permission
{
[Key]
public int Id { get; set; }
[Required]
public string Name { get; set; }
// 其他属性...
// 导航属性
public ICollection<User> Users { get; set; }
}同时，创建一个名为 User 的数据模型类，用于表示用户。
using System.ComponentModel.DataAnnotations;
public class User
{
[Key]
public int Id { get; set; }
[Required]
public string Username { get; set; }
[Required]
public string Password { get; set; }
// 其他属性...
// 导航属性
public ICollection<Permission> Permissions { get; set; }
}4、配置数据库上下文
创建一个名为 AppDbContext 的数据库上下文类，用于与 SQLite 数据库进行交互。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
namespace PermissionManagementSystem
{
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
{
}
public DbSet<User> Users { get; set; }
public DbSet<Permission> Permissions { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Data Source=permissions.db");
}
}
}这里我们使用 SQLite 数据库作为数据存储，并指定数据库文件为 permissions.db。
5、创建控制器
创建一个名为 PermissionsController 的控制器，用于处理权限相关的 HTTP 请求。
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;
[ApiController]
[Route("api/[controller]")]
public class PermissionsController : ControllerBase
{
private readonly AppDbContext _dbContext;
public PermissionsController(AppDbContext dbContext)
{
_dbContext = dbContext;
}
[HttpGet]
public ActionResult<IEnumerable<Permission>> GetPermissions()
{
var permissions = _dbContext.Permissions.ToList();
return Ok(permissions);
}
[HttpPost]
public ActionResult<Permission> CreatePermission(Permission permission)
{
_dbContext.Permissions.Add(permission);
_dbContext.SaveChanges();
return CreatedAtAction(nameof(GetPermission), new { id = permission.Id }, permission);
}
[HttpGet("{id}")]
public ActionResult<Permission> GetPermission(int id)
{
var permission = _dbContext.Permissions.Find(id);
if (permission == null)
{
return NotFound();
}
return Ok(permission);
}
[HttpPut("{id}")]
public IActionResult UpdatePermission(int id, Permission permission)
{
if (id != permission.Id)
{
return BadRequest();
}
_dbContext.Entry(permission).State = EntityState.Modified;
_dbContext.SaveChanges();
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeletePermission(int id)
{
var permission = _dbContext.Permissions.Find(id);
if (permission == null)
{
return NotFound();
}
_dbContext.Permissions.Remove(permission);
_dbContext.SaveChanges();
return NoContent();
}
}这个控制器包含了用于处理权限的 CRUD 操作的相应动作方法。
6、注册服务和启动应用程序
在 Startup.cs 中注册数据库上下文服务，并启动应用程序。
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
namespace PermissionManagementSystem
{
public class Startup
{
private readonly IConfiguration _configuration;
public Startup(IConfiguration configuration)
{
_configuration = configuration;
}
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<AppDbContext>(options =>
options.UseSqlite(_configuration.GetConnectionString("DefaultConnection")));
services.AddControllers();
services.AddEndpointsApiExplorer();
services.AddSwaggerGen();
}
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
// Configure the HTTP request pipeline.
if (env.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}7、配置连接字符串
在 appsettings.json 文件中，添加连接字符串配置。
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=permissions.db"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}这里我们指定了数据库连接字符串为 Data Source=permissions.db。
8、运行应用程序
运行以下命令启动应用程序：
dotnet run现在，你可以通过发送 HTTP 请求到 /api/permissions 路由来使用这个权限管理系统。
这是一个简单的示例，展示了如何使用 ASP.NET Core Web API 和 SQLite 数据库构建一个基本的权限管理系统。你可以根据实际需求扩展和优化这些代码，并添加身份验证和授权等功能来完善系统。
