using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System;
public class OrcaleAccess
{
public static OracleConnection dbConnection;
static string host = "访问地址";
static string database = "数据库名";
static string id = "账户";
static string pwd = "密码";
public OrcaleAccess()
{
OpenSql();
}
public static void OpenSql()
{
try
{
string connectionString = string.Format("User ID={0};Password={1};Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = {2})(PORT = {3}))) (CONNECT_DATA =(SERVICE_NAME = {4})))", id, pwd, host, "1521", database);
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
}
catch (Exception e)
{
throw new Exception("服务器连接失败,请重新检查是否打开Orcale服务。" + e.Message.ToString());
}
}
/// <summary>
/// 关闭数据库
/// </summary>
public void Close()
{
if (dbConnection != null)
{
dbConnection.Close();
dbConnection.Dispose();
dbConnection = null;
}
}
/// <summary>
/// 返回表的查询结果
/// </summary>
/// <returns>The all.</returns>
/// <param name="Name">Name.</param>
public OracleDataReader SelectAll(string Name)
{
string query = "select * from " + " " + Name;
return ExecuteQuery(query);
}
/// <summary>
/// 条件查找
/// </summary>
/// <returns>The where.</returns>
/// <param name="tableName">表名</param>
/// <param name="items">选择的字段</param>
/// <param name="col">查找根据的字段</param>
/// <param name="operation">查找根据的运算符</param>
/// <param name="values">查找根据的值</param>
public OracleDataReader SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
{
if (col.Length != operation.Length || operation.Length != values.Length)
{
throw new Exception("col.Length != operation.Length != values.Length");
}
string query = "SELECT " + items[0];
for (int i = 1; i < items.Length; ++i)
{
query += ", " + items[i];
}
query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
for (int i = 1; i < col.Length; ++i)
{
query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
}
return ExecuteQuery(query);
}
/// <summary>
/// eg:根据时间段查找
/// </summary>
/// <param name="tableName"></param>
/// <param name="items"></param>
/// <param name="col"></param>
/// <param name="old"></param>
/// <param name="latter"></param>
/// <param name="uuid"></param>
/// <returns></returns>
public OracleDataReader SelectTimeWhere(string tableName, string[] items, string[] col, string[] old, string[] latter , string uuid)
{
string query = "SELECT " + items[0];
for (int i = 1; i < items.Length; ++i)
{
query += ", " + items[i];
}
query += " FROM " + tableName + " WHERE " + col[0]+" BETWEEN TO_DATE ( "+ "'" + old[0] + "' ,"+ " '" + "yyyy-MM-dd HH24:mi:ss" + "' " + ") AND TO_DATE (" + " '" + latter[0] + "' ,"+ " '" + "yyyy-MM-dd HH24:mi:ss " + "' )" + " AND "+ " UUID = "+ " '" + uuid + "' ";
return ExecuteQuery(query);
}
/// <summary>
/// 返回检索结果
/// </summary>
/// <returns>The query.</returns>
/// <param name="sqlString">Sql string.</param>
public static OracleDataReader ExecuteQuery(string sqlString)
{
if (dbConnection.State == ConnectionState.Open)
{
OracleDataReader dr = null; //表的集合
try
{
OracleCommand cmd = new OracleCommand(sqlString, dbConnection);
dr = cmd.ExecuteReader();
}
catch (Exception ee)
{
throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());
}
finally
{
}
return dr;
}
return null;
}
}
OrcaleAccess
注意:使用时必须包含Oracle.ManagedDataAccess.dll,System.Data.dll。
private void SelectRealTime()
{
OrcaleAccess orcale = new OrcaleAccess();
OracleDataReader od = orcale.SelectAll("表名");
while (od.Read())
{
RealTime ele = new RealTime();
ele.RealTime_TIME = od["TIME"].ToString();
realtimeTable.Add(ele);
}
orcale.Close();
}
使用案例