C#DataTable到Oracle存储过程 [英] C# DataTable to Oracle Stored Procedure

问题描述

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

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

这是我所做的:

Oracle方面:

1. 创建类型:

1. Created a type:

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

创建了表格

Created a table

create or replace TYPE CUSTOM_TYPE_ARRAY AS TABLE OF CUSTOM_TYPE;

创建了一个存储过程

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

Invalid parameter binding Parameter name: P_TABLE_IN

帮助！

推荐答案

DataTable无法直接绑定.您需要为要从.NET访问的任何UDT创建自定义类.在这里，我给出了一个简单的示例，说明如何以半通用的方式将DataTable映射到UDT:

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

函数ConvertDataTypeToUdt是通用的，如果提供适当的类，它将自动映射数据表.下一步将完全自动化映射，以便由数据表本身定义目标数据类型.自定义类型属性中的"HUSQVIK"是架构名称，如果您不以包含自定义类型的架构的所有者身份进行连接，则必须与您的数据库相对应.

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.

这篇关于C#DataTable到Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！