Programs Knowledge

方式一:使用DataTable 讀取DB資料

using System.Data;//使用"DataTable"必需的

using System.Data.OracleClient;//取得"Oracle相關物件"必需的

//連線設定分別需要輸入HOST、PORT、SID、User Id and Password

 //而System_Identity(SID)查詢方法有2種：

//1.在Oracle的安裝目錄下，例如D:\oracle\product\10.2.0\db_1\dbs，看文件名如SPFILEXE.ORA，接下來將文件

    名稱的開頭＂SPPILE＂拿掉，剩下的字串為＂XE＂就是SID

//2.在PL/SQL中輸入查詢語句：＂select instance_name from v$instance; ＂即可

OracleConnection oConn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=

    (PROTOCOL=TCP)(HOST=192.***.***.***)(PORT=1521)))(CONNECT_DATA=(SID=XE)));User Id=system;

    Password=oracle");

string queryStr = "select * from A_USER_INFO";

OracleDataAdapter oda = new OracleDataAdapter(queryStr, oConn);

DataTable dt = new DataTable("TableName");//TableName可以隨便取

//利用OracleDataAdapter的Fill方法將DB取出的data塞入DataTable中, 再利用DataTable取出值

oda.Fill(dt);

//show to GridView

dataGridView1.DataSource = dt;

//從DB Load Data到Array

object[][] LoadData = new object[dt.Rows.Count][];

for (int i = 0; i < dt.Rows.Count; i++)

{

    LoadData[i] = new object[dt.Columns.Count];

    for (int j = 0; j < dt.Columns.Count; j++)

    {

        LoadData[i][j] = dt.Rows[i][j].ToString();

    }

}

方式二:使用OracleDataReader讀取DB資料

OracleConnection oConn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=

    (PROTOCOL=TCP)(HOST=192.***.***.***)(PORT=1521)))(CONNECT_DATA=(SID=XE)));User Id=system;

    Password=oracle");

oConn.Open();

string queryStr = "select * from A_USER_INFO";

//設定SQL命令

OracleCommand oCommand = new OracleCommand(queryStr, oConn);

//建立一個存放DB資料的陣列

object[] tempResult;

 List<object[]> listResult = new List<object[]>();

using (OracleDataReader reader = oCommand.ExecuteReader(CommandBehavior.SequentialAccess))

{

    while (reader.Read())

    {

        tempResult = new object[reader.FieldCount];

        for (int i = 0; i < reader.FieldCount; i++)

        {

            if (!reader.IsDBNull(i))

                tempResult[i] = reader[i];

        }

        listResult.Add(tempResult);

     }

}

//最後將收集起來的資料存放在陣列中

object[][] Result = listResult.ToArray();

方式三:使用UTF-8來辨識Where Condition的中文, 並設定DateTime欄位格式

public object[][] LoadData1(string targetTable, string[] columns,

    string[] columnsType, string conditions, string[] conditionValues)

{

    string queryStr = "select ";

    for (int i = 0; i < columns.Length; i++)

    {

        if (columnsType[i].ToUpper().Equals("DATETIME"))

            queryStr += "to_date(to_char(" + columns[i].Trim() +

                ", 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') " + columns[i].Trim();

        else if (columnsType[i].ToUpper().Equals("NUMBER"))

            queryStr += "to_number(" + columns[i].Trim() + ") " + columns[i].Trim();

        else

            queryStr += columns[i].Trim();

        if (i != (columns.Length - 1))

            queryStr += ", ";

        else

            queryStr += string.Format(" from {0}", targetTable);

    }

    if (!conditions.Equals(string.Empty))

        queryStr += " " + conditions;

    List<object[]> result = new List<object[]>();

    OracleConnection oconn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.***.***.***)(PORT=1521)))(CONNECT_DATA=(SID=XE)));User Id=system;Password=oracle");

    try

    {

        oconn.Open();

        OracleCommand command = new OracleCommand(queryStr, oconn);

        if (queryStr.Contains("condition"))

        {

            for (int i = 0; i < conditionValues.Length; i++)

            {

                command.Parameters.Add("condition" + (i + 1).ToString(), OracleType.Raw).Value

                    = System.Text.Encoding.GetEncoding("UTF-8").GetBytes(conditionValues[i]);

            }

        }

        using (OracleDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))

        {

            while (reader.Read())

            {

                object[] data = new object[reader.FieldCount];

                for (int i = 0; i < reader.FieldCount; i++)

                {

                    if (!reader.IsDBNull(i))

                        data[i] = reader[i];

                    else

                        data[i] = null;

                }

                result.Add(data);

            }

        }

    }

    catch (Exception ex)

    {

        return new object[1][] { new object[2] { "error", ex.Message.ToString() } };

    }

    finally

    {

        oconn.Close();

    }

    return result.ToArray();

}

static void Main(string[] args)

{

    Program p = new Program();

    object[][] data1 = p.LoadData1("a_user_info", new string[] { "account", "created_time" },

    new string[] { "string", "DateTime" }, "where sex = 'Man' order by account", null);

    //使用utl_raw.cast_to_varchar2(:condition1)辨識中文

    object[][] data2 = p.LoadData1("a_user_info", new string[] { "account", "created_time" },

        new string[] { "string", "DateTime" }, "where name = utl_raw.cast_to_varchar2(:condition1) order by account", new string[] { "小明" });

    //Condition 使用 like

    object[][] data3 = p.LoadData1("a_user_info", new string[] { "account", "created_time" },

        new string[] { "string", "DateTime" }, "where name like utl_raw.cast_to_varchar2(:condition1) order by account", new string[] { "%小明%" });

}