使用Dapper将C#bool作为参数传递给Oracle

问题描述

我正在尝试使用Dapper将布尔值作为参数传递给Oracle，将其转换为数据库上的1/0字段，如下所示:

I'm trying to pass a bool as a parameter to Oracle using Dapper, translating to a 1/0 field on the database, like this:

public class Customer
{
    public bool Active { get; set; }
}
static void InsertCustomer()
{
    var customer = connect.QueryFirst<Customer>("select 1 active from dual"); // this works
    connect.Execute("insert into customers(active) values(:active)", customer); // this doesn't
}

但这会引发异常:

我知道我可以创建另一个属性public int ActiveInt => Active ? 1 : 2;，但是我想保持POCO类尽可能整洁，尤其是因为这些属性需要公开，Dapper才能将它们用作参数.

I know I can create another property public int ActiveInt => Active ? 1 : 2; but I would like to keep my POCO classes as clean as possible, especially because the properties need to be public for Dapper to use them as parameters.

我试图创建一个布尔类型处理程序，但它仅适用于查询列，不适用于参数: https://github.com/StackExchange/Dapper/issues/303

I tried to create a bool type handler, but it only works for query columns, not parameters: https://github.com/StackExchange/Dapper/issues/303

我还需要将整个对象作为参数传递，因此无法在传递参数时进行转换.

I also need to pass the whole object as parameter, so converting when passing the parameter isn't possible.

有办法吗?

推荐答案

我使用Dapper.SqlMapper.AddTypeMap(Type，DbType)方法.

I use Dapper.SqlMapper.AddTypeMap(Type, DbType) method.

public class Customer
{
    public bool Active { get; set; }
}

class Program : IDisposable
{
    private readonly DbConnection _connection;

    public Program()
    {
        _connection = DbOpen();
    }

    static void Main(string[] args)
    {
        SqlMapper.AddTypeMap(typeof(bool), DbType.Int32);

        using (var program = new Program())
        {
            program.Run();
        }
    }

    private void Run()
    {
        _connection.Execute($"INSERT INTO customers ( active ) VALUES (:Activate)", new { Activate = true });
        _connection.Execute($"INSERT INTO customers ( active ) VALUES (:Activate)", new { Activate = false });

        var customers = new List<Customer>()
        {
            new Customer() {Active = true},
            new Customer() {Active = false}
        };
        _connection.Execute($"INSERT INTO customers ( active ) VALUES (:{nameof(Customer.Active)})", customers);

        var results = _connection.Query<Customer>("SELECT * FROM customers");
        foreach (var customer results)
        {
            Console.WriteLine($"{nameof(Customer.Active)} is {customer.Active}");
        }
    }

    private static DbConnection DbOpen()
    {
        var connectionSetting = ConfigurationManager.ConnectionStrings["oracle"];
        var dbFactory = DbProviderFactories.GetFactory(connectionSetting.ProviderName);
        var conn = dbFactory.CreateConnection();
        conn.ConnectionString = connectionSetting.ConnectionString;
        conn.Open();
        return conn;
    }

    public void Dispose()
    {
        _connection?.Dispose();
    }
}

这篇关于使用Dapper将C#bool作为参数传递给Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！