ODP.NET 用户定义类型实现
5.00/5 (3投票s)
如何使用 ODP.NET 用户定义类型功能
引言
ODP.NET 支持 Oracle 用户定义类型 (UDT)。它可以使用自定义类型来表示 Oracle UDT。这在将复杂的实体参数传递给 Oracle 存储过程时非常有用。不必传递许多参数，只需传递一个 .NET 自定义对象参数即可。这种技术使代码更简洁、更易于维护。本文档提供了一个示例代码演练，以帮助理解如何实现 ODP.NET UDT。
使用代码
步骤 1：在 Oracle 中创建一个示例表。
create table articles
( article_id number(6) not null,
article_name varchar(50) not null,
update_date date,
constraint articles_pk primary key (article_id)
);
步骤 2：在 schema 级别创建一个对象类型。创建一个函数来返回一个 UDT 参数，以及一个带有 UDT 参数的存储过程。
create or replace type articles_typ as object (
article_id number(6),
article_name varchar(50),
update_date date
);
create or replace function get_article(i_article_id number)
return articles_typ
is
l_article articles_typ;
begin
l_article := new articles_typ(null, null, null);
select article_id, article_name, update_date
into l_article.article_id, l_article.article_name, l_article.update_date
from articles where article_id = i_article_id;
return l_article;
end;
create or replace procedure add_article(i_article in articles_typ)
is
begin
if i_article is null then
raise_application_error(-20000, 'Parameter i_article can not be null!');
end if;
insert into articles (article_id, article_name, update_date)
values (i_article.article_id, i_article.article_name, i_article.update_date);
end;
步骤 3：在 schema 级别创建一个嵌套表类型。创建一个函数来返回一个集合参数，以及一个带有集合参数的存储过程。
create or replace type articles_list_typ as table of articles_typ;
create or replace procedure add_articles(i_articles_list in articles_list_typ)
is
begin
if i_articles_list is null then
raise_application_error(-20000, 'Parameter i_articles_list can not be null!');
end if;
for i in i_articles_list.first .. i_articles_list.last
loop
insert into articles (article_id, article_name, update_date)
values (i_articles_list(i).article_id, i_articles_list(i).article_name, i_articles_list(i).update_date);
end loop;
end;
create or replace function get_all_articles
return articles_list_typ
is
l_articles_list articles_list_typ;
cursor c is
select * from articles;
begin
l_articles_list := new articles_list_typ();
for article_rec in c
loop
l_articles_list.extend;
l_articles_list(l_articles_list.last) := new articles_typ(article_rec.article_id,
article_rec.article_name, article_rec.update_date);
end loop;
return l_articles_list;
end;
步骤 4：创建一个 Visual Studio C# 项目。创建一个映射到 Oracle 用户定义类型的自定义类。该自定义类必须实现 IOracleCustomType 和 INullable 接口。创建一个用于创建自定义实例的自定义类型工厂类。该自定义类型工厂类必须实现 IOracleCustomTypeFactory 类。以下是代码列表。代码注释提供了所有实现细节。
// A custom class mapping to an Oracle user defined type.
// Provided all required implementations by ODP.NET developer guide to represent the Oracle UDT as custom type.
// The custom class must implement IOracleCustomType and INullable interfaces.
// Note: Any Oracle UDT name must be uppercase.
public class Article : IOracleCustomType, INullable
{
// A private member indicating whether this object is null.
private bool ObjectIsNull;
// The OracleObjectMapping attribute is required to map .NET custom type member to Oracle object attribute.
[OracleObjectMapping("ARTICLE_ID")]
public int ArticleId { get; set; }
[OracleObjectMapping("ARTICLE_NAME")]
public string ArticleName { get; set; }
[OracleObjectMapping("UPDATE_DATE")]
public DateTime UpdateDate { get; set; }
// Implementation of interface IOracleCustomType method FromCustomObject.
// Set Oracle object attribute values from .NET custom type object.
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "ARTICLE_ID", ArticleId);
OracleUdt.SetValue(con, pUdt, "ARTICLE_NAME", ArticleName);
OracleUdt.SetValue(con, pUdt, "UPDATE_DATE", UpdateDate);
}
// Implementation of interface IOracleCustomType method ToCustomObject.
// Set .NET custom type object members from Oracle object attributes.
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
ArticleId = (int)OracleUdt.GetValue(con, pUdt, "ARTICLE_ID");
ArticleName = (string)OracleUdt.GetValue(con, pUdt, "ARTICLE_NAME");
UpdateDate = (DateTime)OracleUdt.GetValue(con, pUdt, "UPDATE_DATE");
}
// A property of interface INullable. Indicate whether the custom type object is null.
public bool IsNull
{
get { return ObjectIsNull; }
}
// Static null property is required to return a null UDT.
public static Article Null
{
get
{
Article obj = new Article();
obj.ObjectIsNull = true;
return obj;
}
}
}
// A custom type factory class is required to create an instance of a custom type representing an Oracle object type.
// The custom type factory class must implement IOralceCustomTypeFactory class.
// The OracleCustomTypeMapping attribute is required to indicate the Oracle UDT for this factory class.
[OracleCustomTypeMapping("YOUR_SCHEMA_NAME.ARTICLES_TYP")]
public class ArticleFactory : IOracleCustomTypeFactory
{
// Implementation of interface IOracleCustomTypeFactory method CreateObject.
// Return a new .NET custom type object representing an Oracle UDT object.
public IOracleCustomType CreateObject()
{
return new Article();
}
}
步骤 5：创建一个映射到 Oracle 集合类型的自定义类。该自定义类必须实现 IOracleCustomType 和 INullable 接口。创建一个用于创建集合实例的自定义类型工厂类。该自定义类型工厂类必须实现 IOracleCustomTypeFactory 和 IOracleArrayTypeFactory。以下是代码列表。代码注释提供了所有实现细节。
// A custom class mapping to an Oracle collection type.
public class ArticleList : IOracleCustomType, INullable
{
// The OracleArrayMapping attribute is required to map .NET class member to Oracle collection type.
[OracleArrayMapping()]
public Article[] objArticles;
// A private member indicating whether this object is null.
private bool ObjectIsNull;
// Implementation of interface IOracleCustomType method FromCustomObject.
// Set Oracle collection value from .NET custom type member with OracleArrayMapping attribute.
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, 0, objArticles);
}
// Implementation of interface IOracleCustomType method ToCustomObject.
// Set .NET custom type member with OracleArrayMapping attribute from Oracle collection value.
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
objArticles = (Article[])OracleUdt.GetValue(con, pUdt, 0);
}
// A property of interface INullable. Indicate whether the custom type object is null.
public bool IsNull
{
get { return ObjectIsNull; }
}
// Static null property is required to return a null UDT.
public static ArticleList Null
{
get
{
ArticleList obj = new ArticleList();
obj.ObjectIsNull = true;
return obj;
}
}
}
// A custom type factory class is required to crate an instance of a custom type representing an Oracle collection type.
// The custom type factory class must implement IOralceCustomTypeFactory and IOracleArrayTypeFactory class.
// The OracleCustomTypeMapping attribute is required to indicate the Oracle UDT for this factory class.
[OracleCustomTypeMapping("YOUR_SCHEMA_NAME.ARTICLES_LIST_TYP")]
public class ArticleListFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
// Implementation of interface IOracleCustomTypeFactory method CreateObject.
// Return a new .NET custom type object representing an Oracle UDT collection object.
public IOracleCustomType CreateObject()
{
return new ArticleList();
}
// Implementation of interface IOracleArrayTypeFactory method CreateArray to return a new array.
public Array CreateArray(int numElems)
{
return new Article[numElems];
}
// Implementation of interface IOracleArrayTypeFactory method CreateStatusArray to return a new OracleUdtStatus array.
public Array CreateStatusArray(int numElems)
{
return null;
}
}
步骤 6：创建一个类来使用这些 UDT 类。
public class UdtClass1
{
private string strConStr = "DATA SOURCE=YOUR_ORALCE_DATA_SOURCE;USER ID=YOUR_USER_ID;PASSWORD=YOUR_PASSWORD";
public bool InsertArticleRecord(Article a)
{
OracleConnection cn = new OracleConnection(strConStr);
cn.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "add_article";
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p = new OracleParameter();
p.OracleDbType = OracleDbType.Object;
p.Direction = ParameterDirection.Input;
p.Value = a;
p.UdtTypeName = "YOUR_SCHEMA_NAME.ARTICLES_TYP";
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
cn.Dispose();
return true;
}
public Article GetArticleRecord(int intArticleId)
{
Article a;
OracleConnection cn = new OracleConnection(strConStr);
cn.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "get_article";
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter pRet = new OracleParameter();
pRet.OracleDbType = OracleDbType.Object;
pRet.Direction = ParameterDirection.ReturnValue;
pRet.UdtTypeName = "YOUR_SCHEMA_NAME.ARTICLES_TYP";
cmd.Parameters.Add(pRet);
OracleParameter pIn = new OracleParameter();
pIn.OracleDbType = OracleDbType.Int32;
pIn.Direction = ParameterDirection.Input;
pIn.Value = intArticleId;
cmd.Parameters.Add(pIn);
cmd.ExecuteNonQuery();
a = (Article)cmd.Parameters[0].Value;
cmd.Dispose();
cn.Close();
cn.Dispose();
return a;
}
public bool InsertArticleRecords(ArticleList al)
{
OracleConnection cn = new OracleConnection(strConStr);
cn.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "add_articles";
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p = new OracleParameter();
p.OracleDbType = OracleDbType.Array;
p.Direction = ParameterDirection.Input;
p.Value = al;
p.UdtTypeName = "YOUR_SCHEMA_NAME.ARTICLES_LIST_TYP";
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
cn.Dispose();
return true;
}
public ArticleList GetAllArticles()
{
OracleConnection cn = new OracleConnection(strConStr);
ArticleList al;
cn.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "get_all_articles";
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter pRet = new OracleParameter();
pRet.OracleDbType = OracleDbType.Array;
pRet.Direction = ParameterDirection.ReturnValue;
pRet.UdtTypeName = "YOUR_SCHEMA_NAME.ARTICLES_LIST_TYP";
cmd.Parameters.Add(pRet);
cmd.ExecuteNonQuery();
al = (ArticleList)cmd.Parameters[0].Value;
cmd.Dispose();
cn.Close();
cn.Dispose();
return al;
}
}
步骤 7：创建一个测试类来测试这些 UDT 类。
[TestClass()]
public class UdtClass1Test
{
[TestMethod()]
public void InsertArticleRecordTest()
{
UdtClass1 target = new UdtClass1();
Article a = new Article() { ArticleId = 1, ArticleName = "article one", UpdateDate = DateTime.Today };
bool actual;
actual = target.InsertArticleRecord(a);
Assert.AreEqual(true, actual);
}
[TestMethod()]
public void GetArticleRecordTest()
{
UdtClass1 target = new UdtClass1();
int intArticleId = 1;
Article actual;
actual = target.GetArticleRecord(intArticleId);
Assert.AreEqual("article one", actual.ArticleName);
}
[TestMethod()]
public void InsertArticleRecordsTest()
{
UdtClass1 target = new UdtClass1();
ArticleList al = new ArticleList();
Article[] articles = new Article[2]
{
new Article() { ArticleId = 2, ArticleName = "article two", UpdateDate = DateTime.Today },
new Article() { ArticleId = 3, ArticleName = "article three", UpdateDate = DateTime.Today }
};
al.objArticles = articles;
bool actual;
actual = target.InsertArticleRecords(al);
Assert.AreEqual(true, actual);
}
[TestMethod()]
public void GetAllArticlesTest()
{
UdtClass1 target = new UdtClass1();
ArticleList actual;
actual = target.GetAllArticles();
Assert.AreEqual(3, actual.objArticles.Length);
}
}
关注点
使用 ODP.NET 用户定义类型功能可以将复杂的自定义对象和集合作为参数传递给 Oracle 存储过程。