如何在C# ASP.NET中为控制器注入SQLite连接与仓储实例

我正在开发一个使用SQLite数据库的简单应用，采用仓储模式处理所有数据库操作。已编写依赖AddressRepository的AddressController，以及依赖SqliteConnection的AddressRepository实现，但不知如何配置使控制器能获取仓储实例，相关代码如下：

控制器代码 #

using Microsoft.AspNetCore.Mvc;
using WebApplication2.Models;
using WebApplication2.Data;

namespace WebApplication2.Controllers
{
    [ApiController]
    public class AddressController : Controller
    {
        private readonly AddressRepository _addressReposity;
        public AddressController(AddressRepository addressRepository)
        {
            _addressReposity = addressRepository;
        }

        [HttpGet("/addresses")]
        [ProducesResponseType(200, Type = typeof(List<(int, Address)>))]
        public IActionResult getAddresses(Address address, bool orderByAscending)
        {
            List<(int, Address)> addresses = _addressReposity.getAddresses(address, orderByAscending);

            if (!ModelState.IsValid)
                return BadRequest(ModelState);

            return Ok(addresses);
        }

        [HttpPost("/addresses")]
        [ValidateAntiForgeryToken]
        public IActionResult addAddress(Address address)
        { 
            return View();
        }

        [HttpGet("/addresses/{id}")]
        [ProducesResponseType(200, Type = typeof(Address))]
        public IActionResult getSingleAddress(int id) 
        {
            if(!_addressReposity.existAddress(id))
                return BadRequest(ModelState);

            if (!ModelState.IsValid)
                return BadRequest(ModelState);

            return Ok(_addressReposity.getSingleAddress(id));
        }
        [HttpPut("/addresses/{id}")]
        public IActionResult updateAddress(Address address, int id) 
        {
            if (!_addressReposity.existAddress(id))
                return BadRequest(ModelState);

            if (!ModelState.IsValid)
                return BadRequest(ModelState);

            _addressReposity.UpdateAddress(address, id);

            return Ok();
        }

        [HttpDelete("/addresses/{id}")]
        public IActionResult deleteAddress(int id) 
        {
            if (!_addressReposity.existAddress(id))
                return BadRequest(ModelState);

            if (!ModelState.IsValid)
                return BadRequest(ModelState);

            return Ok();

        }
    }
}

仓储代码 #

using System.Data;
using WebApplication2.Models;
using Microsoft.Data.Sqlite;

namespace WebApplication2.Data
{
    public class AddressRepository : IAddressRepository
    {
        private readonly SqliteConnection _db_connection;
        public AddressRepository(SqliteConnection db_connection) 
        {
            _db_connection = db_connection;
        }
        public List<(int, Address)> readerToAddresses(SqliteDataReader reader)
        {
            List<(int, Address)> addresses = new List<(int, Address)>();
            while (reader.Read())
            {
                int ID = reader.GetInt32("ID");
                string street =  reader[2].ToString();
                string city   =  reader[3].ToString();
                string zipcode = reader[4].ToString();
                int houseNumber = (int)reader[5];
                string addition;
                if (reader[6] == null)
                    addition = null;
                else
                    addition = reader[6].ToString();
                addresses.Append((ID, new Address(street, city, zipcode, houseNumber)));

            }
            return addresses;
        }

        public string getSearchAddressesString(string start, Address search_info)
        {
            string search_command_string = start;
            if (search_info != null)
            {
                foreach (var prop in search_info.GetType().GetProperties())
                {
                    if (prop.GetValue(search_info) != null)
                    {
                        if (prop.PropertyType.Name == "String")
                        {
                            search_command_string += prop.Name.ToString() + " = '" + prop.GetValue(search_info).ToString() + "'" + ", ";
                        }
                        else if (prop.PropertyType.Name == "Int32")
                        {
                            search_command_string += prop.Name.ToString() + " = " + ((int)prop.GetValue(search_info)).ToString() + ", ";
                        }
                    }
                }
            }
            return search_command_string;
        }
        public List<(int, Address)> getAddresses(Address search_info, bool orderByAscending)
        {
            string search_command_string = $"SELECT * FROM ADDRESSES WHERE ";

            search_command_string = getSearchAddressesString(search_command_string, search_info);

            if (search_command_string == "SELECT * FROM ADDRESSES WHERE ")
                search_command_string = "SELECT * FROM ADDRESSES";

            SqliteCommand search_command = new SqliteCommand(search_command_string, _db_connection);
            SqliteDataReader reader = search_command.ExecuteReader();
            return readerToAddresses(reader);
        }
        public void InsertAddress(Address address)
        {
            string insert_command_string_part_one = "INSERT INTO ADDRESSES (";
            string insert_command_string_part_two = "VALUES(";
            if (address != null)
            {
                foreach (var prop in address.GetType().GetProperties())
                {
                    insert_command_string_part_one += prop.Name.ToString() + ", ";
                    if (prop.GetValue(address) != null)
                    {
                        if (prop.PropertyType.Name == "String")
                        {
                            insert_command_string_part_two += "'" + prop.GetValue(address).ToString() + "'" + ", "; 
                        }
                        else if (prop.PropertyType.Name == "Int32")
                        {
                            insert_command_string_part_two += ((int)prop.GetValue(address)).ToString() + ", ";
                        }
                    }
                }
            }
            insert_command_string_part_one = insert_command_string_part_one.Substring(0, insert_command_string_part_one.Length - 2) + ") ";
            insert_command_string_part_two = insert_command_string_part_two.Substring(0, insert_command_string_part_two.Length - 2) + ')';
            SqliteCommand insert_command = new SqliteCommand(insert_command_string_part_one + insert_command_string_part_two, _db_connection);
            insert_command.ExecuteNonQuery();
        }

        public void UpdateAddress(Address address, int id)
        {
            bool not_all_null = false;
            string update_command_string = "UPDATE ADDRESSES SET ";
            if (address != null)
            {
                foreach (var prop in address.GetType().GetProperties())
                {
                    if (prop.GetValue(address) != null)
                    {
                        not_all_null = true;
                        if (prop.PropertyType.Name == "String")
                        {
                            update_command_string += prop.Name.ToString() + " = '" + prop.GetValue(address).ToString() + "'" + ", ";
                        }
                        else if (prop.PropertyType.Name == "Int32")
                        {
                            update_command_string += prop.Name.ToString() + " = " + ((int)prop.GetValue(address)).ToString() + ", ";
                        }
                    }
                }
                if (not_all_null) 
                {
                    update_command_string = update_command_string.Substring(0, update_command_string.Length - 2) + $" WHERE ID = {(int)id}";
                    SqliteCommand update_command = new SqliteCommand(update_command_string, _db_connection);
                    update_command.ExecuteNonQuery();
                }
            }
        }

        public void DeleteAddress(int id)
        {
            string delete_command_string = $"DELETE FROM ADDRESSES WHERE ID = {(int)id}";
            SqliteCommand delete_command = new SqliteCommand(delete_command_string, _db_connection);
            delete_command.ExecuteNonQuery();
        }

        public bool existAddress(int id)
        {
            string exists_command_string = $"SELECT * FROM ADDRESSES WHERE ID={id}";
            SqliteCommand exists_command = new SqliteCommand(exists_command_string, _db_connection);
            SqliteDataReader reader = exists_command.ExecuteReader();

            while (reader.Read())
                return true;

            return false;
        }

        public Address getSingleAddress(int id)
        {
            string get_address_string = $"SELECT * FROM ADDRESSES WHERE ID={id}";
            SqliteCommand single_address_command = new SqliteCommand(get_address_string, _db_connection);
            SqliteDataReader reader = single_address_command.ExecuteReader();
            return readerToAddresses(reader)[0].Item2;
        }
    }
}

appsettings.json #

{
  "ConnectionStrings": {
    "DefaultConnection": "Addresses.sqlite"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

配置步骤指引 #

1. 在Program.cs注册依赖注入服务 #

ASP.NET Core通过依赖注入容器管理实例，需要将SqliteConnection和仓储类/接口注册到容器中：

var builder = WebApplication.CreateBuilder(args);

// 添加控制器服务
builder.Services.AddControllers();

// 注册SqliteConnection：作用域生命周期，每个请求一个实例
builder.Services.AddScoped<SqliteConnection>(sp => 
{
    var connStr = builder.Configuration.GetConnectionString("DefaultConnection");
    var connection = new SqliteConnection($"Data Source={connStr}");
    connection.Open();
    return connection;
});

// 注册仓储：推荐面向接口注册，提升可测试性和扩展性
builder.Services.AddScoped<IAddressRepository, AddressRepository>();

var app = builder.Build();

// 中间件配置
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();

2. 修改控制器依赖为接口（可选但推荐） #

将控制器对AddressRepository的依赖改为IAddressRepository，符合依赖倒置原则：

private readonly IAddressRepository _addressReposity;
public AddressController(IAddressRepository addressRepository)
{
    _addressReposity = addressRepository;
}

3. 修复SQL注入风险（必须） #

当前仓储直接拼接SQL字符串，存在严重安全漏洞，全部改为参数化查询。示例修改getAddresses方法：

public List<(int, Address)> getAddresses(Address search_info, bool orderByAscending)
{
    var addresses = new List<(int, Address)>();
    var sqlBuilder = new StringBuilder("SELECT * FROM ADDRESSES");
    var parameters = new List<SqliteParameter>();

    if (search_info != null)
    {
        var conditions = new List<string>();
        foreach (var prop in search_info.GetType().GetProperties())
        {
            var value = prop.GetValue(search_info);
            if (value != null)
            {
                conditions.Add($"{prop.Name} = @{prop.Name}");
                parameters.Add(new SqliteParameter($"@{prop.Name}", value));
            }
        }

        if (conditions.Any())
        {
            sqlBuilder.Append(" WHERE ").Append(string.Join(" AND ", conditions));
        }
    }

    // 添加排序逻辑
    sqlBuilder.Append(orderByAscending ? " ORDER BY ID ASC" : " ORDER BY ID DESC");

    using var command = new SqliteCommand(sqlBuilder.ToString(), _db_connection);
    command.Parameters.AddRange(parameters.ToArray());
    
    using var reader = command.ExecuteReader();
    while (reader.Read())
    {
        int ID = reader.GetInt32("ID");
        string street = reader["Street"].ToString();
        string city = reader["City"].ToString();
        string zipcode = reader["Zipcode"].ToString();
        int houseNumber = reader.GetInt32("HouseNumber");
        string addition = reader.IsDBNull(reader.GetOrdinal("Addition")) ? null : reader["Addition"].ToString();
        
        addresses.Add((ID, new Address(street, city, zipcode, houseNumber, addition)));
    }

    return addresses;
}

其他增删改方法需参照此逻辑修改，彻底消除SQL注入风险。

4. 初始化SQLite数据库表 #

确保启动时自动创建ADDRESSES表，在Program.cs中添加：

// 初始化数据库表
using (var scope = app.Services.CreateScope())
{
    var services = scope.ServiceProvider;
    var connection = services.GetRequiredService<SqliteConnection>();
    var createTableSql = @"
        CREATE TABLE IF NOT EXISTS ADDRESSES (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            Street TEXT NOT NULL,
            City TEXT NOT NULL,
            Zipcode TEXT NOT NULL,
            HouseNumber INTEGER NOT NULL,
            Addition TEXT
        );";
    using var command = new SqliteCommand(createTableSql, connection);
    command.ExecuteNonQuery();
}

内容的提问来源于stack exchange，提问作者berend