C# DataTable 转换为 Oracle 存储过程

Question

我需要做的是将一个C# DataTable传递给一个Oracle存储过程。

以下是我的操作：

Oracle端：

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# 部分：

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();
    }

但是我出现了一个错误：

无效的参数绑定，参数名称：P_TABLE_IN

救命啊！

- Muhammad Khairul

---

似乎你不能直接这样做 - 这篇CodeProject文章展示了一种可能的解决方法，使用XML。 - marc_s

上面链接的Code Project文章的不便之处在于您需要为数据结构创建表格，这意味着灵活性较差。 - EAmez

Answer 1

DataTable不能直接绑定。您需要为您想要从.NET访问的任何UDT创建自定义类。在这里，我提供了一个简单的示例，演示如何以半通用的方式将DataTable映射到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 }; }
    }
}

函数ConvertDataTypeToUdt是通用的，如果您提供适当的类，它会自动映射数据表。下一步将完全自动化映射，使目标数据类型由数据表本身定义。在自定义类型属性中的“HUSQVIK”是模式名称，如果您不作为包含自定义类型的模式的所有者连接，则必须与您的数据库对应。

- Husqvik