[轉]在.Net中使用Oracle的表型別和物件型別

weixin_34402090發表於2013-06-24
Oracle型別物件

本文轉自：http://www.cnblogs.com/studyzy/archive/2010/10/13/1850161.html

在一般的資料存取操作過程中，如果要對一個主表和對應的子表進行插入操作，那麼我們最常見的寫法就是寫兩個儲存過程或者SQL語句，一個負責主表資料插入，一個負責子表資料插入，然後在一個事務中實現主表和子表資料的插入。

現在遇到一個問題是，能否在一個儲存過程中實現主表和子表資料的插入呢？那麼就需要將一對多的資料作為儲存過程的引數傳入。這種情況下就需要使用表型別。下面以一個學生和班級的例子來說明：

先建立一個班級表和一個學生表，一個班級裡面有多個學生。




程式碼

CREATE TABLE CLASS
(
   CLASSID     NUMBER (38) PRIMARY KEY,
   CLASSNAME   VARCHAR2 (50 BYTE) NOT NULL
)；

CREATE TABLE STUDENT
(
  STUID        NUMBER(38) PRIMARY KEY,
  CLASSID      NUMBER(38)                       NOT NULL,
  STUNAME      NVARCHAR2(50)                    NOT NULL,
  STUGENDER    CHAR(1 BYTE),
  STUBIRTHDAY  DATE,
  DESCRIPTION  NVARCHAR2(2000)
)；

CREATE SEQUENCE CLASSID；

CREATE SEQUENCE STUDENTID；



首先我們需要在Oracle中建立一個學生的物件型別，這個物件型別中就是學生的屬性：




CREATE OR REPLACE type StudentType as object
(
       StuName nvarchar2(50),
       StuGender char(1),
       StuBirthday date,
       StuDescription nvarchar2(2000)
);



 

接下來是將這個學生型別建立成表型別：


CREATE OR REPLACE type StuList as table of StudentType;

接下來就是寫我們的一個插入儲存過程，將班級和學生列表作為引數傳入，具體指令碼為：




程式碼

CREATE OR REPLACE PROCEDURE ZY.AddClassStudent(

ClassName in varchar2,
Students in StuList
) IS

BEGIN
   insert into Class values(classid.nextval,ClassName);
   insert into Student(StuID,ClassID,StuName,Stugender,Stubirthday,Description)
   select studentid.nextval,classid.currval,StuName,StuGender,StuBirthday,studescription
   from TABLE(Students);
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       RAISE;
END AddClassStudent;



現在Oracle伺服器上的各個物件已經建立完成，接下來就是要編寫C#程式碼，連線到Oracle資料庫，插入資料了。


在C#專案中新增Oracle.DataAccess的引用，這是Oracle為.Net開發的類庫，可以從官網下載。新增引用後，再新增名稱空間：


using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;

 

然後再建立Student對應的類：




程式碼

public class Student : IOracleCustomType
    {
        #region IOracleCustomType Members

        public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            if (StudentName != null)
                OracleUdt.SetValue(con, pUdt, "STUNAME", StudentName);
            else
                throw new NullReferenceException("STUNAME is null");
            OracleUdt.SetValue(con, pUdt, "STUGENDER", Gender);
            OracleUdt.SetValue(con, pUdt, "STUBIRTHDAY", Birthday);
            OracleUdt.SetValue(con, pUdt, "STUDESCRIPTION", Description);
        }

        public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            StudentName = (String)OracleUdt.GetValue(con, pUdt, "STUNAME");
            Gender = (String)OracleUdt.GetValue(con, pUdt, "STUGENDER");
            Birthday = (DateTime)OracleUdt.GetValue(con, pUdt, "STUBIRTHDAY");
            Description = (String)OracleUdt.GetValue(con, pUdt, "STUDESCRIPTION");
        }

        #endregion

        [OracleObjectMappingAttribute("STUNAME")]
        public String StudentName { get; set; }

        [OracleObjectMapping("STUGENDER")]
        public string Gender { get; set; }

        [OracleObjectMapping("STUBIRTHDAY")]
        public DateTime Birthday { get; set; }

        [OracleObjectMapping("STUDESCRIPTION")]
        public string Description { get; set; }

    }



並新增Student類對應Oracle物件型別的對映，通過Attribute來指定：




程式碼

[OracleCustomTypeMappingAttribute("STUDENTTYPE")]
public class StudentFactory : IOracleCustomTypeFactory
{
    #region IOracleCustomTypeFactory Members

    public IOracleCustomType CreateObject()
    {
        return new Student();
    }

    #endregion
}



現在StudentType型別已經建立完成，接下來就是建立StuList型別對應的類：




程式碼

[OracleCustomTypeMappingAttribute("STULIST")]
public class StudentList_TabFactory : IOracleArrayTypeFactory
{
    #region IOracleArrayTypeFactory Members

    public Array CreateArray(int numElems)
    {
        return new Student[numElems];
    }

    public Array CreateStatusArray(int numElems)
    {
        return null;
    }

    #endregion
}



這裡可以看到，返回的是Student的陣列。現在準備工作都已經完成，接下來就是初始化一點資料，然後呼叫儲存過程了，程式碼如下：




程式碼

Student s1 = new Student() { StudentName = "張三", Birthday = Convert.ToDateTime("1984/12/29"), Gender = "M", Description = "HAHA。" };
           Student s2 = new Student() { StudentName = "李四", Birthday = Convert.ToDateTime("1982/12/29"), Gender = "F", Description = "A。" };
           Student s3 = new Student() { StudentName = "王五", Birthday = Convert.ToDateTime("1982/1/29"), Gender = "M", Description = "B。" };
           Student s4 = new Student() { StudentName = "小月月", Birthday = Convert.ToDateTime("1985/10/11"), Gender = "F", Description = "C。" };
           List<Student> ss1 = new List<Student>();
           ss1.Add(s1);
           ss1.Add(s2);
           ss1.Add(s3);
           ss1.Add(s4);

           string conn = "Data Source=BRDWDEV;User Id=zy;Password=123;";
           using (OracleConnection oc = new OracleConnection(conn))
           {
               oc.Open();

               OracleCommand cmd = oc.CreateCommand();
               cmd.CommandType = System.Data.CommandType.StoredProcedure;

               cmd.CommandText = "ZY.ADDCLASSSTUDENT";

               OracleParameter p0 = new OracleParameter();
               p0.OracleDbType = OracleDbType.Varchar2;
               p0.UdtTypeName = "CLASSNAME";
               p0.Value = "測試班級名";
               p0.Direction = ParameterDirection.Input;
               cmd.Parameters.Add(p0);

               OracleParameter p1 = new OracleParameter();
               p1.OracleDbType = OracleDbType.Array;
               p1.Direction = ParameterDirection.Input;
               p1.UdtTypeName = "STULIST";//注意這裡是型別，而不是引數名
               p1.Value = ss1.ToArray();//注意這裡應該是陣列
               cmd.Parameters.Add(p1);

               int count = cmd.ExecuteNonQuery();
               Console.WriteLine(count);
               oc.Close();
           }



以此類推，其實還可以把班級建立物件型別，然後再建立班級列表型別，這樣就可在一個儲存過程中插入多個班級，每個班級多個學生的資料。

【出自部落格園深藍居，轉載請註明作者出處】

 

 

相關文章