C#数据表到Oracle存储过程
我需要做的是将C#DataTable传递给Oracle存储过程。
这是我所做的:
Oracle方面:
创建一个类型:
create or replace TYPE CUSTOM_TYPE AS OBJECT
(
attribute1 VARCHAR(10),
attribute2 VARCHAR(10)
);
创建了一张桌子
create or replace TYPE CUSTOM_TYPE_ARRAY AS TABLE OF CUSTOM_TYPE;
创建一个存储过程
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
帮帮我!
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'是模式名称,如果不作为包含自定义类型的模式所有者进行连接,则它必须对应于数据库。
链接地址: http://www.djcxy.com/p/20295.html上一篇: C# DataTable to Oracle Stored Procedure
下一篇: How to get stored procedure parameter types and sizes in Oracle?