c#数据表到Oracle存储过程

本文关键字：存储过程 Oracle 数据表 | 更新日期: 2023-09-27 18:07:02

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

下面是我所做的:

甲骨文的一面:

  1. 创建类型:

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

  2. 创建表

    create or replace TYPE CUSTOM_TYPE_ARRAY AS TABLE OF CUSTOM_TYPE;

  3. 创建存储过程

    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

帮助!

c#数据表到Oracle存储过程

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'是模式名，如果你不作为包含自定义类型的模式的所有者连接，它必须对应于你的数据库。

相关文章：