C# DataTable to Oracle Stored Procedure

Question

What I need to do is to pass a C# DataTable to an Oracle stored procedure.

Here is what I have done:

Oracle side:

1. Created a type:

   create or replace TYPE CUSTOM_TYPE AS OBJECT 
   ( 
       attribute1 VARCHAR(10),
       attribute2 VARCHAR(10)
   );
   

2. Created a table

   create or replace TYPE CUSTOM_TYPE_ARRAY AS TABLE OF CUSTOM_TYPE;
   

3. Created a stored procedure

   create or replace PROCEDURE SP_TEST
   (
       P_TABLE_IN IN CUSTOM_TYPE_ARRAY,
       P_RESULT_OUT OUT SYS_REFCURSOR 
   ) AS 
   --P_TABLE_IN CUSTOM_TYPE_ARRAY;
   BEGIN
       OPEN P_RESULT_OUT FOR
   
       SELECT attribute1, attribute2
       FROM TABLE(P_TABLE_IN);
   END SP_TEST;
   

C# side:

void Run()
{
        OracleConnection oraConn = new OracleConnection();
        oraConn.ConnectionString = ConfigurationManager.ConnectionStrings["NafasV2ConnectionString"].ToString();
        DataSet dataset = new DataSet();
        DataTable Dt = new DataTable();
        OracleDataAdapter da = new OracleDataAdapter();
        OracleCommand cmd = new OracleCommand();

        try
        {
            FormTVP(ref Dt);
            PopulateTVP(ref Dt);
            oraConn.Open();
            cmd.Connection = oraConn;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SP_TEST";

            OracleParameter parm1 = new OracleParameter("P_TABLE_IN", OracleDbType.RefCursor,100,"xx");
            parm1.Value = Dt;
            parm1.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(parm1);

            OracleParameter parm2 = new OracleParameter("P_RESULT_OUT", OracleDbType.RefCursor);
            parm2.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(parm2);

            da.SelectCommand = cmd;
            da.Fill(dataset);
            ASPxLabel1.Text = "OK!!";
        }
        catch (Exception ex)
        {
            ASPxLabel1.Text = "DIE. REASON: " + ex.Message;
        }
        finally
        {
            da.Dispose();
            cmd.Dispose();
            oraConn.Close();
            oraConn.Dispose();
        }

    }

    void FormTVP(ref DataTable Dt)
    {
        DataColumn attribute1 = Dt.Columns.Add("ATTRIBUTE1", typeof(String));
        DataColumn attribute2 = Dt.Columns.Add("ATTRIBUTE2", typeof(String));
        Dt.AcceptChanges();
    }

    void PopulateTVP(ref DataTable Dt)
    {
        DataRow Dr = Dt.NewRow();
        Dr["ATTRIBUTE1"] = "MK1";
        Dr["ATTRIBUTE2"] = "MK2";
        Dt.Rows.Add(Dr);

        DataRow Dr1 = Dt.NewRow();
        Dr1["ATTRIBUTE1"] = "HH1";
        Dr1["ATTRIBUTE2"] = "HH2";
        Dt.Rows.Add(Dr1);

        Dt.AcceptChanges();
    }

But I'm getting an error:

Invalid parameter binding Parameter name: P_TABLE_IN

HELP!

Answer 1

DataTable cannot be bound directly. You need to create custom class for any UDT you want to access from .NET. Here I made simple example how to map in half-generic way DataTable to UDT:

void Main()
{
    var dataTable = BuildSourceData();

    using (var connection = new OracleConnection("DATA SOURCE=hq_pdb_tcp;PASSWORD=oracle;USER ID=HUSQVIK"))
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = "BEGIN HUSQVIK.SP_TEST(:P_TABLE_IN, :P_RESULT_OUT); END;";
            command.BindByName = true;

            var p1 = command.CreateParameter();
            p1.ParameterName = "P_TABLE_IN";
            p1.OracleDbType = OracleDbType.Array;
            p1.UdtTypeName = "HUSQVIK.CUSTOM_TYPE_ARRAY";
            p1.Value = ConvertDataTableToUdt<CustomTypeArray, CustomType>(dataTable);
            command.Parameters.Add(p1);

            var p2 = command.CreateParameter();
            p2.Direction = ParameterDirection.Output;
            p2.ParameterName = "P_RESULT_OUT";
            p2.OracleDbType = OracleDbType.RefCursor;
            command.Parameters.Add(p2);

            command.ExecuteNonQuery();

            using (var reader = ((OracleRefCursor)p2.Value).GetDataReader())
            {
                var row = 1;
                while (reader.Read())
                {
                    Console.WriteLine($"Row {row++}: Attribute1 = {reader[0]}, Attribute1 = {reader[1]}");
                }
            }
        }
    }
}

private DataTable BuildSourceData()
{
    var dataTable = new DataTable("CustomTypeArray");
    dataTable.Columns.Add(new DataColumn("Attribute1", typeof(string)));
    dataTable.Columns.Add(new DataColumn("Attribute2", typeof(string)));

    dataTable.Rows.Add("r1 c1", "r1 c2");
    dataTable.Rows.Add("r2 c1", "r2 c2");

    return dataTable;
}

public static object ConvertDataTableToUdt<TUdtTable, TUdtItem>(DataTable dataTable) where TUdtTable : CustomCollectionTypeBase<TUdtTable, TUdtItem>, new() where TUdtItem : CustomTypeBase<TUdtItem>, new()
{
    var tableUdt = Activator.CreateInstance<TUdtTable>();
    tableUdt.Values = (TUdtItem[])tableUdt.CreateArray(dataTable.Rows.Count);
    var fields = typeof(TUdtItem).GetFields();

    for (var i = 0; i < dataTable.Rows.Count; i++)
    {
        var itemUdt = Activator.CreateInstance<TUdtItem>();
        for (var j = 0; j < fields.Length; j++)
        {
            fields[j].SetValue(itemUdt, dataTable.Rows[i][j]);
        }

        tableUdt.Values[i] = itemUdt;
    }

    return tableUdt;
}

[OracleCustomTypeMapping("HUSQVIK.CUSTOM_TYPE_ARRAY")]
public class CustomTypeArray : CustomCollectionTypeBase<CustomTypeArray, CustomType>
{
}

[OracleCustomTypeMapping("HUSQVIK.CUSTOM_TYPE")]
public class CustomType : CustomTypeBase<CustomType>
{
    [OracleObjectMapping("ATTRIBUTE1")]
    public string Attribute1;
    [OracleObjectMapping("ATTRIBUTE2")]
    public string Attribute2;

    public override void FromCustomObject(OracleConnection connection, IntPtr pointerUdt)
    {
        OracleUdt.SetValue(connection, pointerUdt, "ATTRIBUTE1", Attribute1);
        OracleUdt.SetValue(connection, pointerUdt, "ATTRIBUTE2", Attribute2);
    }

    public override void ToCustomObject(OracleConnection connection, IntPtr pointerUdt)
    {
        Attribute1 = (string)OracleUdt.GetValue(connection, pointerUdt, "ATTRIBUTE1");
        Attribute2 = (string)OracleUdt.GetValue(connection, pointerUdt, "ATTRIBUTE2");
    }
}

public abstract class CustomCollectionTypeBase<TType, TValue> : CustomTypeBase<TType>, IOracleArrayTypeFactory where TType : CustomTypeBase<TType>, new()
{
    [OracleArrayMapping()]
    public TValue[] Values;

    public override void FromCustomObject(OracleConnection connection, IntPtr pointerUdt)
    {
        OracleUdt.SetValue(connection, pointerUdt, 0, Values);
    }

    public override void ToCustomObject(OracleConnection connection, IntPtr pointerUdt)
    {
        Values = (TValue[])OracleUdt.GetValue(connection, pointerUdt, 0);
    }

    public Array CreateArray(int numElems)
    {
        return new TValue[numElems];
    }

    public Array CreateStatusArray(int numElems)
    {
        return null;
    }
}

public abstract class CustomTypeBase<T> : IOracleCustomType, IOracleCustomTypeFactory, INullable where T : CustomTypeBase<T>, new()
{
    private bool _isNull;

    public IOracleCustomType CreateObject()
    {
        return new T();
    }

    public abstract void FromCustomObject(OracleConnection connection, IntPtr pointerUdt);

    public abstract void ToCustomObject(OracleConnection connection, IntPtr pointerUdt);

    public bool IsNull
    {
        get { return this._isNull; }
    }

    public static T Null
    {
        get { return new T { _isNull = true }; }
    }
}

Function ConvertDataTypeToUdt is generic and if you provide appropriate class it maps the data table automatically. Next step would to automate the mapping entirely so the target data type would be defined by the data table itself. The 'HUSQVIK' in custom type attributes is schema name and it must correspond to your database if you don't connect as owner of schema containing your custom types.