特殊函数

对于 SQL 中使用一些特殊函数, 例如 TO_CHAR 在神通驱动 .NET DATA PROVIDER 存在一些缺陷, 为了解决这个问题, .NET DATA PROVIDER 提供临时的解决方案, 需要在连接字符串中配置 SPECIALFUNCTIONCAST=TRUE. 对于在代码层面上, 不需要做任何的修改.

SQL

SELECT TO_CHAR(?,'YYYY-MM')

等效于

SELECT TO_CHAR(?:TIMESTAMP,'YYYY-MM')

示例

[TestMethod]
public void TestSelect1BindMethod()
{
    using var command = _connect.CreateCommand();

    command.CommandText = "select to_char(@c1);";
    command.Parameters.Add(new OscarParameter { ParameterName = "c1", OscarDbType = OscarDbType.Date, Value = DateTime.Now });

    try
    {
        var reader = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

[TestMethod]
public void TestSelect2BindMethod()
{
    using var command = _connect.CreateCommand();

    command.CommandText = "select to_char(@c1);";
    command.Parameters.Add(new OscarParameter { ParameterName = "c1", OscarDbType = OscarDbType.TimeStamp, Value = DateTime.Now });

    try
    {
        var reader = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

[TestMethod]
public void TestSelect3BindMethod()
{
    using var command = _connect.CreateCommand();

    command.CommandText = "select to_char(@c1);";
    command.Parameters.Add(new OscarParameter { ParameterName = "c1", OscarDbType = OscarDbType.TimestampTZ, Value = DateTimeOffset.Now });

    try
    {
        var reader = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

[TestMethod]
public void TestSelect4BindMethod()
{
    using var command = _connect.CreateCommand();

    command.CommandText = "select to_char(@c1);";
    command.Parameters.Add(new OscarParameter { ParameterName = "c1", OscarDbType = OscarDbType.Time, Value = TimeSpan.FromHours(22) });

    try
    {
        var reader = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

[TestMethod]
public void TestSelect5BindMethod()
{
    using var command = _connect.CreateCommand();

    command.CommandText = "select to_char(@c1);";
    command.Parameters.Add(new OscarParameter { ParameterName = "c1", OscarDbType = OscarDbType.Intervaldts, Value = new TimeSpan(11, 22, 12, 23) });

    try
    {
        var reader = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

[TestMethod]
public void TestSelect6BindMethod()
{
    using var command = _connect.CreateCommand();

    var now = DateTime.Now;

    command.CommandText = "select to_char(@c1);";
    command.Parameters.Add(new OscarParameter { ParameterName = "c1", OscarDbType = OscarDbType.Intervalytm, Value = now });

    try
    {
        using(var reader = command.ExecuteReader())
        {
            if(reader.Read())
            {
                var v1 = reader.GetString(0);
            }
        }
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

[TestMethod]
public void TestSelect7BindMethod()
{
    using var command = _connect.CreateCommand();

    command.CommandText = "select to_char(@c1),to_char(@c2),to_char(@c3),to_char(@c4),to_char(@c5),to_char(@c6),to_char(@c7)";
    command.Parameters.Add(new OscarParameter { ParameterName = "c1", OscarDbType = OscarDbType.Int, Value = 122332 });
    command.Parameters.Add(new OscarParameter { ParameterName = "c2", OscarDbType = OscarDbType.Integer, Value = 122332 });
    command.Parameters.Add(new OscarParameter { ParameterName = "c3", OscarDbType = OscarDbType.BigInt, Value = 1223321223 });
    command.Parameters.Add(new OscarParameter { ParameterName = "c4", OscarDbType = OscarDbType.Text, Value = "2323" });
    command.Parameters.Add(new OscarParameter { ParameterName = "c5", OscarDbType = OscarDbType.Decimal, Value = 27238.23m });
    command.Parameters.Add(new OscarParameter { ParameterName = "c6", OscarDbType = OscarDbType.Decimal, Value = 2223.23 });
    command.Parameters.Add(new OscarParameter { ParameterName = "c7", OscarDbType = OscarDbType.Real, Value = (float)12.23 });
    try
    {
        var reader = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

引用

OscarConnection

OscarCommand

OscarParameter

OscarDataReader