[Implement] 存取 DB Data 至 Array

2012-10-25

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

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

//連線設定分別需要輸入HOST、PORT、SID、User Id and Password
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資料

    (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欄位格式

    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[] { "%小明%" });
}

回首頁