using SharpCoreDB.Data;

const string connectionString = "Data Source=./myapp.db;Password=SecurePassword!";

using var connection = new SharpCoreDBConnection(connectionString);
await connection.OpenAsync();

// Create command
using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM users WHERE age > @minAge";
command.Parameters.AddWithValue("@minAge", 18);

// Execute
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"User: {reader["name"]}");
}

// Get factory
var factory = DbProviderFactories.GetFactory("SharpCoreDB");

// Create connection
using var connection = factory.CreateConnection();
connection.ConnectionString = "Data Source=./app.db;Password=secure!";
await connection.OpenAsync();

services.AddSharpCoreDBDataProvider("Data Source=./app.db;Password=secure!");

// Inject DbConnection factory
public class UserRepository
{
    private readonly DbConnection _connection;

    public UserRepository(SharpCoreDBConnectionFactory factory)
    {
        _connection = factory.CreateConnection();
    }
}

public class UserRepository
{
    private readonly string _connectionString;

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

    public async Task<User> GetUserAsync(int id)
    {
        using var connection = new SharpCoreDBConnection(_connectionString);
        await connection.OpenAsync();

        using var command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM users WHERE id = @id";
        command.Parameters.AddWithValue("@id", id);

        using var reader = await command.ExecuteReaderAsync();
        if (await reader.ReadAsync())
        {
            return new User
            {
                Id = (int)reader["id"],
                Name = (string)reader["name"],
                Age = (int)reader["age"]
            };
        }

        return null;
    }
}

public async Task<DataSet> GetUserDataSetAsync()
{
    using var connection = new SharpCoreDBConnection(_connectionString);
    await connection.OpenAsync();

    using var adapter = new DbDataAdapter
    {
        SelectCommand = connection.CreateCommand()
        {
            CommandText = "SELECT id, name, age FROM users"
        }
    };

    var dataSet = new DataSet();
    await adapter.FillAsync(dataSet);
    return dataSet;
}