Converting SQL Server DataRequest class to NpgSQL

Question

Trying to convert a SQL Server DataRequest class for use with PostgreSQL. Stuck on the DBTypes in this code. Can anyone give me a hand in this conversion?

/// <summary>
/// Creates a <see cref="SqlParameter"/> from the supplied parameters.
/// </summary>
/// <param name="name">The parameter name (<see cref="SqlParameter.ParameterName"/>)</param>
/// <param name="type">The SQL type (<see cref="SqlParameter.SqlDbType"/>)</param>
/// <param name="value">The parameter value (<see cref="SqlParameter.Value"/>)</param>
/// <param name="size">The parameter size (<see cref="SqlParameter.Size"/>)</param>
/// <param name="direction">The parameter direction (<see cref="SqlParameter.Direction"/>)</param>
/// <param name="sqlTypeName">The parameter SQL type (<see cref="SqlParameter.TypeName"/>). Applies to <see cref="SqlDbType.Structured"/> only.</param>
/// <returns>A <see cref="SqlParameter"/> object</returns>
protected SqlParameter NewSqlParameter(string name, SqlDbType type = SqlDbType.VarChar, object value = null, int? size = null, ParameterDirection direction = ParameterDirection.Input, string sqlTypeName = null)
{
    var parameter = new SqlParameter
    {
        SqlDbType = type,
        ParameterName = name,
        Value = value ?? DBNull.Value,
        Direction = direction
    };

    // Structured params i.e. DataTables require the TypeName defined in the SQL database
    if (type == SqlDbType.Structured && !string.IsNullOrEmpty(sqlTypeName))
    {
        parameter.TypeName = sqlTypeName;
    }

    // Only set the size if it is explicitly passed. Otherwise, we could get truncated at 0.
    if (size.HasValue)
    {
        parameter.Size = size.Value;
    }
    return parameter;
}

Here's my half-hearted attempt with the issues noted:

/// <summary>
/// Creates a <see cref="SqlParameter"/> from the supplied parameters.
/// </summary>
/// <param name="name">The parameter name (<see cref="NpgsqlParameter.ParameterName"/>)</param>
/// <param name="type">The SQL type (<see cref="NpgsqlParameter.NpgsqlDbType"/>)</param>
/// <param name="value">The parameter value (<see cref="NpgsqlParameter.Value"/>)</param>
/// <param name="size">The parameter size (<see cref="NpgsqlParameter.Size"/>)</param>
/// <param name="direction">The parameter direction (<see cref="NpgsqlParameter.Direction"/>)</param>
/// <param name="sqlTypeName">The parameter SQL type (<see cref="NpgsqlParameter.DataTypeName"/>). Applies to <see cref="SqlDbType.Structured"/> only.</param>
/// <returns>A <see cref="SqlParameter"/> object</returns>protected NpgsqlParameter NewSqlParameter(string name, DbType type =  DbType.String, object value = null, int? size = null, protected NpgsqlParameter NewSqlParameter(string name, NpgsqlDbType type = NpgsqlDbType.Varchar, object value = null, int? size = null, ParameterDirection direction = ParameterDirection.Input, string sqlTypeName = null)
{
    var parameter = new NpgsqlParameter
    {
        NpgsqlDbType = type,
        ParameterName = name,
        Value = value ?? DBNull.Value,
        Direction = direction
    };

    //********************************************************************************************
    // NOTE: There is no DbType of Structured in the NpgSql package. Not sure what to use here
    //********************************************************************************************

    // Structured params i.e. DataTables require the TypeName defined in the SQL database
    if (type == NpgsqlTypes.DbType.Structured && !string.IsNullOrEmpty(sqlTypeName))
    {
        parameter.DataTypeName = sqlTypeName;
    }

    // Only set the size if it is explicitly passed. Otherwise, we could get truncated at 0.
    if (size.HasValue)
    {
        parameter.Size = size.Value;
    }

    return parameter;
}

Answer 1

The above seems to be using SQL Server user-defined types (UDTs); PostgreSQL does have composite types (and the Npgsql library supports them well), but things work differently across the different databases.

Assuming you need to use user-defined types, you'll have to get familiar with how PostgreSQL composite types work via the links above.