基於C#語言Oracle.ManagedDataAccess操作Oracle資料庫連線語句
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Configuration;
using Oracle.ManagedDataAccess;
using Oracle.ManagedDataAccess.Types;
using Oracle.ManagedDataAccess.Client;
namespace DB
{
/// <summary>
/// 資料訪問基礎類(基於Oracle) Copyright (C) Maticsoft
/// 可以使用者可以修改滿足自己專案的需要。
/// </summary>
public abstract class DbHelperOra
{
//資料庫連線字串(web.config來配置)，可以動態更改connectionString支援多資料庫.
public static string connectionString = PubConstant.ConnectionString;
public DbHelperOra()
{
}
#region 公用方法
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
public static bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
public static bool Exists(string strSql, params OracleParameter[] cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 執行簡單SQL語句
/// <summary>
/// 執行SQL語句，返回影響的記錄數
/// </summary>
/// <param name="SQLString">SQL語句</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSql(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString,connection))
{
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch(OracleException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 執行多條SQL語句，實現資料庫事務。
/// </summary>
/// <param name="SQLStringList">多條SQL語句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection=conn;
OracleTransaction tx=conn.BeginTransaction();
cmd.Transaction=tx;
try
{
for(int n=0;n<SQLStringList.Count;n++)
{
string strsql=SQLStringList[n].ToString();
if (strsql.Trim().Length>1)
{
cmd.CommandText=strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch(OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// 執行帶一個儲存過程引數的的SQL語句。
/// </summary>
/// <param name="SQLString">SQL語句</param>
/// <param name="content">引數內容,比如一個欄位是格式複雜的文章，有特殊符號，可以通過這個方式新增</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSql(string SQLString,string content)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(SQLString,connection);
OracleParameter myParameter = new OracleParameter("@content", OracleDbType.Varchar2);
myParameter.Value = content ;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch(OracleException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向資料庫裡插入影象格式的欄位(和上面情況類似的另一種例項)
/// </summary>
/// <param name="strSQL">SQL語句</param>
/// <param name="fs">影象位元組,資料庫的欄位型別為image的情況</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(strSQL,connection);
OracleParameter myParameter = new OracleParameter("@fs", OracleDbType.LongRaw);
myParameter.Value = fs ;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch(OracleException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 執行一條計算查詢結果語句，返回查詢結果（object）。
/// </summary>
/// <param name="SQLString">計算查詢結果語句</param>
/// <returns>查詢結果（object）</returns>
public static object GetSingle(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using(OracleCommand cmd = new OracleCommand(SQLString,connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch(OracleException e)
{
connection.Close();
throw new Exception(e.Message);
}
}
}
}
/// <summary>
/// 執行查詢語句，返回OracleDataReader ( 注意：呼叫該方法後，一定要對SqlDataReader進行Close )
/// </summary>
/// <param name="strSQL">查詢語句</param>
/// <returns>OracleDataReader</returns>
public static OracleDataReader ExecuteReader(string strSQL)
{
OracleConnection connection = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(strSQL,connection);
try
{
connection.Open();
OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch(OracleException e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 執行查詢語句，返回DataSet
/// </summary>
/// <param name="SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OracleDataAdapter command = new OracleDataAdapter(SQLString,connection);
command.Fill(ds,"ds");
}
catch(OracleException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
#endregion
#region 執行帶引數的SQL語句
/// <summary>
/// 執行SQL語句，返回影響的記錄數
/// </summary>
/// <param name="SQLString">SQL語句</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSql(string SQLString,params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
PrepareCommand(cmd, connection, null,SQLString, cmdParms);
int rows=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch(OracleException E)
{
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 執行多條SQL語句，實現資料庫事務。
/// </summary>
/// <param name="SQLStringList">SQL語句的雜湊表（key為sql語句，value是該語句的OracleParameter[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
OracleCommand cmd = new OracleCommand();
try
{
//迴圈
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText=myDE.Key.ToString();
OracleParameter[] cmdParms=(OracleParameter[])myDE.Value;
PrepareCommand(cmd,conn,trans,cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <summary>
/// 執行一條計算查詢結果語句，返回查詢結果（object）。
/// </summary>
/// <param name="SQLString">計算查詢結果語句</param>
/// <returns>查詢結果（object）</returns>
public static object GetSingle(string SQLString,params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
PrepareCommand(cmd, connection, null,SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch(OracleException e)
{
throw new Exception(e.Message);
}
}
}
}
/// <summary>
/// 執行查詢語句，返回OracleDataReader ( 注意：呼叫該方法後，一定要對SqlDataReader進行Close )
/// </summary>
/// <param name="strSQL">查詢語句</param>
/// <returns>OracleDataReader</returns>
public static OracleDataReader ExecuteReader(string SQLString,params OracleParameter[] cmdParms)
{
OracleConnection connection = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand();
try
{
PrepareCommand(cmd, connection, null,SQLString, cmdParms);
OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return myReader;
}
catch(OracleException e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 執行查詢語句，返回DataSet
/// </summary>
/// <param name="SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString,params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null,SQLString, cmdParms);
using( OracleDataAdapter da = new OracleDataAdapter(cmd) )
{
DataSet ds = new DataSet();
try
{
da.Fill(ds,"ds");
cmd.Parameters.Clear();
}
catch(OracleException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
private static void PrepareCommand(OracleCommand cmd,OracleConnection conn,OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (OracleParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
#endregion
#region 儲存過程操作
/// <summary>
/// 執行儲存過程 返回SqlDataReader ( 注意：呼叫該方法後，一定要對SqlDataReader進行Close )
/// </summary>
/// <param name="storedProcName">儲存過程名</param>
/// <param name="parameters">儲存過程引數</param>
/// <returns>OracleDataReader</returns>
public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
{
OracleConnection connection = new OracleConnection(connectionString);
OracleDataReader returnReader;
connection.Open();
OracleCommand command = BuildQueryCommand( connection,storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
/// <summary>
/// 執行儲存過程
/// </summary>
/// <param name="storedProcName">儲存過程名</param>
/// <param name="parameters">儲存過程引數</param>
/// <param name="tableName">DataSet結果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
OracleDataAdapter sqlDA = new OracleDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
connection.Close();
return dataSet;
}
}
/// <summary>
/// 構建 OracleCommand 物件(用來返回一個結果集，而不是一個整數值)
/// </summary>
/// <param name="connection">資料庫連線</param>
/// <param name="storedProcName">儲存過程名</param>
/// <param name="parameters">儲存過程引數</param>
/// <returns>OracleCommand</returns>
private static OracleCommand BuildQueryCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters)
{
OracleCommand command = new OracleCommand( storedProcName, connection );
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
return command;
}
/// <summary>
/// 執行儲存過程，返回影響的行數
/// </summary>
/// <param name="storedProcName">儲存過程名</param>
/// <param name="parameters">儲存過程引數</param>
/// <param name="rowsAffected">影響的行數</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
int result;
connection.Open();
OracleCommand command = BuildIntCommand(connection,storedProcName, parameters );
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
//Connection.Close();
return result;
}
}
/// <summary>
/// 建立 OracleCommand 物件例項(用來返回一個整數值)
/// </summary>
/// <param name="storedProcName">儲存過程名</param>
/// <param name="parameters">儲存過程引數</param>
/// <returns>OracleCommand 物件例項</returns>
private static OracleCommand BuildIntCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters)
{
OracleCommand command = BuildQueryCommand(connection,storedProcName, parameters );
command.Parameters.Add( new OracleParameter ( "ReturnValue",
OracleDbType.Int32, 4, ParameterDirection.ReturnValue,
false,0,0,string.Empty,DataRowVersion.Default,null ));
return command;
}
#endregion
}
}
.net 通過ODP.NET Managed 連線Oracle資料庫
使用方法：
OracleConnection con = new OracleConnection();
con.ConnectionString = "user id=his3;password=his3;data source=192.168.1.237/ORCL";
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from gy_yonghuxx";
cmd.Connection = con;
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
da.Dispose();
cmd.Dispose();
con.Dispose();
注意：
在OleDbParameter 作為引數時，沒有SqlParameter那樣靈活，用SqlParameter時，在定義和賦值時的順序可以隨便寫，而OledbParmeter 則要求嚴格的順序，否則雖然不會報錯，但是可能會出現期望之外的情況如沒有執行或執行出錯。
工具類：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Configuration;
using System.Data;
using System.Reflection;
namespace NCToOA
{
public class OracleHelper
{
#region 變數
/// <summary>
/// 資料庫連線物件
/// </summary>
private static OracleConnection _con = null;
public static string constr = ConfigurationManager.ConnectionStrings["OracleStr"].ToString();
#endregion
#region 屬性
/// <summary>
/// 獲取或設定資料庫連線物件
/// </summary>
public static OracleConnection Con
{
get
{
if (OracleHelper._con == null)
{
OracleHelper._con = new OracleConnection();
}
if (OracleHelper._con.ConnectionString == "")
{
OracleHelper._con.ConnectionString = OracleHelper.constr;
}
return OracleHelper._con;
}
set
{
OracleHelper._con = value;
}
}
#endregion
#region 方法
#region 執行返回一行一列的資料庫操作
/// <summary>
/// 執行返回一行一列的資料庫操作
/// </summary>
/// <param name="commandText">Oracle語句或儲存過程名</param>
/// <param name="commandType">Oracle命令型別</param>
/// <param name="param">Oracle命令引數陣列</param>
/// <returns>第一行第一列的記錄</returns>
public static int ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param)
{
int count = 0;
using (OracleHelper.Con)
{
using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
{
//try
//{
cmd.CommandType = commandType;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
OracleHelper.Con.Open();
count = Convert.ToInt32(cmd.ExecuteScalar());
//}
//catch (Exception ex)
//{
// count = 0;
//}
}
}
return count;
}
#endregion
#region 執行不查詢的資料庫操作
/// <summary>
/// 執行不查詢的資料庫操作
/// </summary>
/// <param name="commandText">Oracle語句或儲存過程名</param>
/// <param name="commandType">Oracle命令型別</param>
/// <param name="param">Oracle命令引數陣列</param>
/// <returns>受影響的行數</returns>
public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param)
{
int result = 0;
using (OracleHelper.Con)
{
using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
{
try
{
cmd.CommandType = commandType;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
OracleHelper.Con.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
result = 0;
}
}
}
return result;
}
#endregion
#region 執行返回一條記錄的泛型物件
/// <summary>
/// 執行返回一條記錄的泛型物件
/// </summary>
/// <typeparam name="T">泛型型別</typeparam>
/// <param name="reader">只進只讀物件</param>
/// <returns>泛型物件</returns>
private static T ExecuteDataReader<T>(IDataReader reader)
{
T obj = default(T);
try
{
Type type = typeof(T);
obj = (T)Activator.CreateInstance(type);//從當前程式集裡面通過反射的方式建立指定型別的物件
//obj = (T)Assembly.Load(OracleHelper._assemblyName).CreateInstance(OracleHelper._assemblyName + "." + type.Name);//從另一個程式集裡面通過反射的方式建立指定型別的物件
PropertyInfo[] propertyInfos = type.GetProperties();//獲取指定型別裡面的所有屬性
foreach (PropertyInfo propertyInfo in propertyInfos)
{
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (fieldName.ToLower() == propertyInfo.Name.ToLower())
{
object val = reader[propertyInfo.Name];//讀取表中某一條記錄裡面的某一列
if (val != null && val != DBNull.Value)
{
if (val.GetType() == typeof(decimal) || val.GetType() == typeof(int))
{
propertyInfo.SetValue(obj, Convert.ToInt32(val), null);
}
else if (val.GetType() == typeof(DateTime))
{
propertyInfo.SetValue(obj, Convert.ToDateTime(val), null);
}
else if (val.GetType() == typeof(string))
{
propertyInfo.SetValue(obj, Convert.ToString(val), null);
}
}
break;
}
}
}
}
catch (Exception ex)
{
throw;
}
return obj;
}
#endregion
#region 執行返回一條記錄的泛型物件
/// <summary>
/// 執行返回一條記錄的泛型物件
/// </summary>
/// <typeparam name="T">泛型型別</typeparam>
/// <param name="commandText">Oracle語句或儲存過程名</param>
/// <param name="commandType">Oracle命令型別</param>
/// <param name="param">Oracle命令引數陣列</param>
/// <returns>實體物件</returns>
public static T ExecuteEntity<T>(string commandText, CommandType commandType, params OracleParameter[] param)
{
T obj = default(T);
using (OracleHelper.Con)
{
using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
OracleHelper.Con.Open();
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
obj = OracleHelper.ExecuteDataReader<T>(reader);
}
}
}
return obj;
}
#endregion
#region 執行返回多條記錄的泛型集合物件
/// <summary>
/// 執行返回多條記錄的泛型集合物件
/// </summary>
/// <typeparam name="T">泛型型別</typeparam>
/// <param name="commandText">Oracle語句或儲存過程名</param>
/// <param name="commandType">Oracle命令型別</param>
/// <param name="param">Oracle命令引數陣列</param>
/// <returns>泛型集合物件</returns>
public static List<T> ExecuteList<T>(string commandText, CommandType commandType, params OracleParameter[] param)
{
List<T> list = new List<T>();
using (OracleHelper.Con)
{
using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
{
try
{
cmd.CommandType = commandType;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
OracleHelper.Con.Open();
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
T obj = OracleHelper.ExecuteDataReader<T>(reader);
list.Add(obj);
}
}
catch (Exception ex)
{
list = null;
}
}
}
return list;
}
#endregion
#endregion
}
}
OracleHelper
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
namespace WserviceDemo
{
public class OracleHelper
{
#region 變數
/// <summary>
/// 資料庫連線物件
/// </summary>
public static string constr = "User Id=bpm_live_test;password=btbpmtest;" +"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=114.55.3.60)(PORT=1521))" +
"(CONNECT_DATA=(SERVICE_NAME=oracle)))";
#endregion
private static string connStr ="User Id=bpm_live_test;Password=btbpmtest;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=114.55.3.60)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=oracle)))";
#region 執行SQL語句,返回受影響行數
public static int ExecuteNonQuery(string sql, params OracleParameter[] parameters)
{
using (OracleConnection conn = new OracleConnection(connStr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
#endregion
#region 執行SQL語句,返回DataTable;只用來執行查詢結果比較少的情況
public static DataTable ExecuteDataTable(string sql, params OracleParameter[] parameters)
{
using (OracleConnection conn = new OracleConnection(connStr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataTable datatable = new DataTable();
adapter.Fill(datatable);
return datatable;
}
}
}
#endregion
/// <summary>
/// 執行SQL語句
/// </summary>
/// <param name="sSQL">SQL語句</param>
/// <param name="Params">引數列表</param>
/// <returns>查詢到的資料DataSet</returns>
public static DataSet ExecuteSQL(string sSQL, object[][] Params)
{
try
{
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(sSQL, connStr);
if (Params != null)
{
for (int i = 0; i <= Params.Length - 1; i++)
{
OracleParameter oraclePar = new OracleParameter();
oraclePar.Direction = ParameterDirection.Input;
try
{
oraclePar.OracleDbType = (OracleDbType) Params[i][1];
}
catch
{
switch (Params[i][1].ToString())
{
case "1":
oraclePar.OracleDbType = OracleDbType.Varchar2;
break;
case "2":
oraclePar.OracleDbType = OracleDbType.Int32;
break;
case "3":
oraclePar.OracleDbType = OracleDbType.Date;
break;
default:
oraclePar.OracleDbType = OracleDbType.Varchar2;
break;
}
}
oraclePar.ParameterName = Params[i][2].ToString().ToUpper();
oraclePar.Value = Params[i][3];
adapter.SelectCommand.Parameters.Add(oraclePar);
}
}
try
{
adapter.Fill(ds);
return ds;
}
catch (Exception ex)
{
return null;
}
finally
{
ds.Dispose();
adapter.Dispose();
}
}
finally
{
}
}
/// <summary>
/// 執行儲存過程
/// </summary>
/// <param name="sProcName">儲存過程名稱</param>
/// <param name="Params">引數列表</param>
/// <returns>返回的資料</returns>
public static DataSet ExecuteProc(string sProcName, object[][] Params)
{
try
{
OracleConnection OraConnection = new OracleConnection(connStr);
OracleCommand oraCmd = new OracleCommand("", OraConnection);
oraCmd.CommandText = sProcName;
oraCmd.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
DataTable dsTable = new DataTable();
DataRow dr = dsTable.NewRow();
OracleDataAdapter adapter = null;
for (int i = 0; i <= Params.Length - 1; i++)
{
OracleParameter oraclePar = new OracleParameter();
try
{
oraclePar.Direction = (ParameterDirection) Params[i][0];
}
catch
{
switch (Params[i][0].ToString().ToUpper())
{
case "INPUT":
oraclePar.Direction = ParameterDirection.Input;
break;
case "OUTPUT":
oraclePar.Direction = ParameterDirection.Output;
break;
default:
oraclePar.Direction = ParameterDirection.Input;
break;
}
}
try
{
oraclePar.OracleDbType = (OracleDbType) Params[i][1];
}
catch
{
switch (Params[i][1].ToString())
{
case "1":
oraclePar.OracleDbType = OracleDbType.Varchar2;
break;
case "2":
oraclePar.OracleDbType = OracleDbType.Int32;
break;
case "3":
oraclePar.OracleDbType = OracleDbType.Date;
break;
default:
oraclePar.OracleDbType = OracleDbType.Varchar2;
break;
}
}
oraclePar.ParameterName = Params[i][2].ToString().ToUpper();
oraclePar.Size = Params[i][3].ToString().Length;
if (oraclePar.Direction == ParameterDirection.Output)
{
oraclePar.Size = 1000;
}
oraclePar.Value = Params[i][3].ToString();
oraCmd.Parameters.Add(oraclePar);
}
adapter = new OracleDataAdapter(oraCmd);
try
{
adapter.Fill(ds);
for (int i = 0; i <= adapter.SelectCommand.Parameters.Count - 1; i++)
{
if (adapter.SelectCommand.Parameters[i].Direction == ParameterDirection.Output)
{
dsTable.Columns.Add(adapter.SelectCommand.Parameters[i].ParameterName, typeof (string));
dr[adapter.SelectCommand.Parameters[i].ParameterName] =
adapter.SelectCommand.Parameters[i].Value;
}
}
dsTable.Rows.Add(dr);
ds.Tables.Clear();
ds.Tables.Add(dsTable);
return ds;
}
finally
{
OraConnection.Dispose();
oraCmd.Dispose();
ds.Dispose();
dsTable.Dispose();
adapter.Dispose();
}
}
finally
{
}
}
/// <summary>
/// 示例1 ：執行SQL語句 使用ExecuteSQL 附加引數列表Params為空
/// 獲取伺服器時間，
/// </summary>
/// <returns>返回伺服器時間</returns>
public static DateTime GetServerDate()
{
DateTime dtNow;
string sSQL = @" SELECT SYSDATE FROM DUAL ";
dtNow = (DateTime) (ExecuteSQL(sSQL, null).Tables[0].Rows[0][0]); //
return dtNow;
}
/// <summary>
/// 示例2： 執行SQL語句 使用ExecuteSQL 附加引數列表Params不為空
/// 更新資料
/// </summary>
/// <param name="sTable"></param>
/// <param name="sRackLoc"></param>
/// <param name="sStatus"></param>
/// <returns></returns>
public static DataSet UpdateStatus(string sTable, string sLocation, string sStatus)
{
object[][] Params = new object[3][];
string sSQL = string.Format(@" UPDATE {0}
SET STATUS = :STATUS
WHERE ID = :ID
AND LOC = :LOC ", sTable);
Params[0] = new object[] {ParameterDirection.Input, OracleDbType.Varchar2, "RACK_STATUS", sStatus};
Params[1] = new object[] {ParameterDirection.Input, OracleDbType.Varchar2, "PDLINE_ID", "1"};
Params[2] = new object[] {ParameterDirection.Input, OracleDbType.Varchar2, "CRANE_LOC", sLocation};
return ExecuteSQL(sSQL, Params);
}
#region 執行返回一行一列的資料庫操作
/// <summary>
/// 執行返回一行一列的資料庫操作
/// </summary>
/// <param name="commandText">Oracle語句或儲存過程名</param>
/// <param name="commandType">Oracle命令型別</param>
/// <param name="param">Oracle命令引數陣列</param>
/// <returns>第一行第一列的記錄</returns>
public static int ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param)
{
int count = 0;
using (var conn = new OracleConnection(constr))
{
using (var cmd = new OracleCommand(commandText, conn))
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
conn.Open();
count = Convert.ToInt32(cmd.ExecuteScalar());
}
}
return count;
}
#endregion
#region 執行不查詢的資料庫操作
/// <summary>
/// 執行不查詢的資料庫操作
/// </summary>
/// <param name="commandText">Oracle語句或儲存過程名</param>
/// <param name="commandType">Oracle命令型別</param>
/// <param name="param">Oracle命令引數陣列</param>
/// <returns>受影響的行數</returns>
public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param)
{
int result = 0;
using (var conn = new OracleConnection(constr))
{
using (var cmd = new OracleCommand(commandText, conn))
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
conn.Open();
result = cmd.ExecuteNonQuery();
}
}
return result;
}
#endregion
#region 執行返回一條記錄的泛型物件
/// <summary>
/// 執行返回一條記錄的泛型物件
/// </summary>
/// <typeparam name="T">泛型型別</typeparam>
/// <param name="reader">只進只讀物件</param>
/// <returns>泛型物件</returns>
private static T ExecuteDataReader<T>(IDataReader reader)
{
T obj = default(T);
Type type = typeof (T);
obj = (T) Activator.CreateInstance(type); //從當前程式集裡面通過反射的方式建立指定型別的物件
//obj = (T)Assembly.Load(OracleHelper._assemblyName).CreateInstance(OracleHelper._assemblyName + "." + type.Name);//從另一個程式集裡面通過反射的方式建立指定型別的物件
var propertyInfos = type.GetProperties(); //獲取指定型別裡面的所有屬性
foreach (var propertyInfo in propertyInfos)
{
for (int i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
if (fieldName.ToLower() == propertyInfo.Name.ToLower())
{
var val = reader[propertyInfo.Name.ToUpper()]; //讀取表中某一條記錄裡面的某一列資訊
if (val != null && val != DBNull.Value)
propertyInfo.SetValue(obj,
Convert.ChangeType(val, propertyInfo.PropertyType.IsGenericType
? propertyInfo.PropertyType.GetGenericArguments()[0]
: propertyInfo.PropertyType), null); //給物件的某一個屬性賦值
break;
}
}
}
return obj;
}
#endregion
#region 執行返回一條記錄的泛型物件
/// <summary>
/// 執行返回一條記錄的泛型物件
/// </summary>
/// <typeparam name="T">泛型型別</typeparam>
/// <param name="commandText">Oracle語句或儲存過程名</param>
/// <param name="commandType">Oracle命令型別</param>
/// <param name="param">Oracle命令引數陣列</param>
/// <returns>實體物件</returns>
public static T ExecuteEntity<T>(string commandText, CommandType commandType, params OracleParameter[] param)
{
T obj = default(T);
using (var conn = new OracleConnection(constr))
{
using (var cmd = new OracleCommand(commandText, conn))
{
cmd.CommandType = commandType;
if (param != null && param.Length > 0)
{
cmd.Parameters.AddRange(param);
}
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = OracleHelper.ExecuteDataReader<T>(reader);
}
}
}
return obj;
}
#endregion
#region 執行返回多條記錄的泛型集合物件
/// <summary>
/// 執行返回多條記錄的泛型集合物件
/// </summary>
/// <typeparam name="T">泛型型別</typeparam>
/// <param name="commandText">Oracle語句或儲存過程名</param>
/// <param name="commandType">Oracle命令型別</param>
/// <param name="param">Oracle命令引數陣列</param>
/// <returns>泛型集合物件</returns>
public static List<T> ExecuteList<T>(string commandText, CommandType commandType, params OracleParameter[] param)
{
List<T> list = new List<T>();
using (var conn = new OracleConnection(constr))
{
using (var cmd = new OracleCommand(commandText, conn))
{
cmd.CommandType = commandType;
if (param!=null&¶m.Length>0)
{
cmd.Parameters.AddRange(param);
}
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
T obj = OracleHelper.ExecuteDataReader<T>(reader);
list.Add(obj);
}
reader.Close();
}
}
return list;
}
#endregion
#region 過載
/// <summary>
/// 執行返回資料的第一行第一列
/// </summary>
/// <param name="commandText">查詢語句</param>
/// <returns></returns>
public static int ExecuteScalar(string commandText)
{
return ExecuteScalar(commandText, CommandType.Text);
}
/// <summary>
/// 執行查詢返回一個泛型集合
/// </summary>
/// <typeparam name="T">單條資料模型</typeparam>
/// <param name="commandText">查詢命令</param>
/// <returns>查詢結果集</returns>
public static List<T> ExecuteList<T>(string commandText)
{
return ExecuteList<T>(commandText, CommandType.Text);
}
/// <summary>
/// 執行查詢以返回單條記錄
/// </summary>
/// <typeparam name="T">資料模型</typeparam>
/// <param name="commandText">查詢命令</param>
/// <returns>查詢結果物件</returns>
public static T ExecuteEntity<T>(string commandText)
{
return ExecuteEntity<T>(commandText, CommandType.Text);
}
/// <summary>
/// 執行不返回結果集的查詢
/// </summary>
/// <param name="commandText">查詢命令</param>
/// <returns>受影響的行數</returns>
public static int ExecuteNonQuery(string commandText)
{
return ExecuteNonQuery(commandText, CommandType.Text);
}
#endregion
/// <summary>
/// CodeFirst 資料檢查和自行
/// </summary>
/// <param name="autoExcute">是否自動執行資料遷移</param>
/// <returns></returns>
public static bool CodeFirstVerify(bool autoExcute)
{
return CodeFirstMigrate();
}
/// <summary>
/// CodeFirst 資料遷移
/// </summary>
/// <returns></returns>
public static bool CodeFirstMigrate()
{
var asm = Assembly.GetExecutingAssembly();
var types = asm.GetExportedTypes().ToList();
try
{
// App_Code 資料夾
var asm1 = Assembly.Load("__code");
types.AddRange(asm1.GetExportedTypes());
}
catch
{
}
// 表型別判斷委託
Func<Attribute[], bool> IsTable = o =>
{
foreach (Attribute a in o)
{
if (a is TableAttribute)
return true;
}
return false;
};
var CosType = types.Where(o =>
{
var attri = Attribute.GetCustomAttributes(o, false);
return IsTable(attri);
});
foreach (var type in CosType)
{
//OracleTransaction trans = new OracleTransaction();
// 遷移符合條件的型別
var table = GetObjectFromAttribute<TableAttribute>(type);
if (!string.IsNullOrWhiteSpace(table.Name))
{
// 檢查模型和資料庫以進行遷移
var tableName = table.Name.ToUpper();
// 查詢資料庫是否存在此表
var count = ExecuteScalar(
"SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '" + tableName + "'");
if (count == 0)
{
// 建立資料表
var createTable = new StringBuilder();
var createSequence = new StringBuilder();
var createTrigger = new StringBuilder();
var enableTrigger = "";
createTable.AppendLine("CREATE TABLE \"" + tableName + "\" (");
// 表格模型成員列表
var props = type.GetProperties();
var priKeys = new List<string>();
var identis = new List<string>();
foreach (var prop in props)
{
bool isKey; // 是否主鍵標誌
bool isIdent; // 是否標識列
createTable.AppendLine("\"" + prop.Name.ToUpper() + "\" "
+ ConvertToOracleType(prop, out isKey, out isIdent) + ",");
if (isKey)
priKeys.Add(prop.Name.ToUpper());
if (isIdent)
identis.Add(prop.Name.ToUpper());
}
if (priKeys.Count != 0)
createTable.AppendLine(" CONSTRAINT \"" + tableName + "_PK\" PRIMARY KEY (\"" + priKeys[0] +
"\") ENABLE");
createTable.AppendLine(") ");
var resultCount = ExecuteNonQuery(createTable.ToString());
//createTable.AppendLine("/");
createSequence.AppendLine("CREATE SEQUENCE \"" + tableName + "_SEQ\" MINVALUE 1 " +
"MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE");
resultCount = ExecuteNonQuery(createSequence.ToString());
//createTable.AppendLine("/");
if (identis.Count != 0)
{
createTrigger.AppendLine("CREATE OR REPLACE TRIGGER \"BI_" + tableName + "\" ");
createTrigger.AppendLine(" before insert on \"" + tableName + "\" ");
createTrigger.AppendLine(" for each row");
createTrigger.AppendLine("begin");
createTrigger.AppendLine(" if :NEW.\"" + identis[0] + "\" is null then");
createTrigger.AppendLine(" select \"" + tableName + "_SEQ\".nextval into :NEW.\"" +
identis[0] + "\" from sys.dual;");
createTrigger.AppendLine(" end if;");
createTrigger.AppendLine("end;");
resultCount = ExecuteNonQuery(createTrigger.ToString());
//createTable.AppendLine("/");
enableTrigger = "ALTER TRIGGER \"BI_" + tableName + "\" ENABLE";
resultCount = ExecuteNonQuery(enableTrigger);
//createTable.AppendLine("/");
}
//resultCount = ExecuteNonQuery(createTable.ToString());
}
else
{
// 表結構修改
// 查詢現有表列
var cols = ExecuteList<ColumnModel>(
"SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '" + tableName + "' ORDER BY COLUMN_ID");
// 獲取模型屬性名集合
var propNames = new List<string>();
var colNames = new List<string>();
foreach (var prop in type.GetProperties())
{
propNames.Add(prop.Name.ToUpper());
}
foreach (var col in cols)
{
colNames.Add(col.Column_Name.ToUpper());
}
// 表格欄位和屬性列表的差集
var excepts = propNames.Except(colNames).ToList();
foreach (var prop in type.GetProperties())
{
var propName = prop.Name.ToUpper();
bool isKey; // 是否主鍵標誌
bool isIdent; // 是否標識列
var colType = ConvertToOracleType(prop, out isKey, out isIdent);
if (excepts.Contains(propName))
// 新增資料欄位
ExecuteNonQuery("alter table \"" + tableName
+ "\" add (\"" + propName + "\" " + colType + ")");
}
excepts = colNames.Except(propNames).ToList();
foreach (var col in cols)
{
if (excepts.Contains(col.Column_Name))
// 刪除資料欄位
ExecuteNonQuery("alter table \"" + tableName
+ "\" drop column \"" + col.Column_Name + "\"");
else
{
bool isKey; // 是否主鍵標誌
bool isIdent; // 是否標識列
var props = type.GetProperties();
var colType = ConvertToOracleType(
props.Where(a => a.Name.ToUpper() == col.Column_Name).First(), out isKey,
out isIdent);
if (colType != col.Data_Type)
{
if (col.Data_Type == "NUMBER" && col.Data_Scale != null)
{
// Decimal 型別比較
if (colType != "NUMBER(38," + col.Data_Scale + ")")
ExecuteNonQuery("alter table \"" + tableName
+ "\" modify (\"" + col.Column_Name + "\" " + colType + ")");
}
else
{
try
{
// 修改欄位型別
ExecuteNonQuery("alter table \"" + tableName
+ "\" modify (\"" + col.Column_Name + "\" " + colType + ")");
}
catch
{
}
}
}
}
}
}
}
else
throw new Exception("Oracle 資料表模型 " + type.Name + " 未設定表名！");
}
return true;
}
/// <summary>
/// 將常用資料型別轉換為 Oracle 資料型別
/// </summary>
/// <param name="type">系統資料型別</param>
/// <param name="isKey">獲取成員是否主鍵</param>
/// <param name="isIdent">是否標識列</param>
/// <returns></returns>
static string ConvertToOracleType(PropertyInfo propInfo, out bool isKey, out bool isIdent)
{
isKey = false;
isIdent = false;
var type = propInfo.PropertyType;
switch (type.Name)
{
case "Int32":
// 主鍵檢查
var key = GetObjectFromAttribute<KeyAttribute>(propInfo);
if (key != null)
{
isKey = true;
// 標識列
var identity = GetObjectFromAttribute<DatabaseGeneratedAttribute>(propInfo);
if (identity != null && identity.DatabaseGeneratedOption == DatabaseGeneratedOption.Identity)
isIdent = true;
return "NUMBER";
}
else
return "NUMBER NOT NULL ENABLE";
case "String":
var returnStr = "";
// 字串長度
var lenAttr = GetObjectFromAttribute<StringLengthAttribute>(propInfo);
if (lenAttr == null)
returnStr += "NVARCHAR2(2000)";
else
returnStr += "NVARCHAR2(" + lenAttr.MaximumLength + ")";
// 是否必須
var Required = GetObjectFromAttribute<RequiredAttribute>(propInfo);
if (Required != null && !Required.AllowEmptyStrings)
returnStr += " NOT NULL ENABLE";
else
returnStr += "";
return returnStr;
case "DateTime":
return "TIMESTAMP (6) WITH LOCAL TIME ZONE NOT NULL ENABLE";
case "Decimal":
return "NUMBER(38,2) NOT NULL ENABLE";
case "Boolean":
return "CHAR(1) NOT NULL ENABLE";
case "Nullable`1":
// 可為空值型別
switch (type.GetGenericArguments()[0].Name)
{
case "Int32":
return "NUMBER";
case "DateTime":
return "TIMESTAMP (6) WITH LOCAL TIME ZONE";
case "Decimal":
return "NUMBER(38,2)";
case "Boolean":
return "CHAR(1)";
}
return "";
}
return string.Empty;
}
/// <summary>
/// 從特性中獲取該特性型別的物件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <returns></returns>
static T GetObjectFromAttribute<T>(MemberInfo element) where T : Attribute
{
return (T) Attribute.GetCustomAttribute(element, typeof (T));
}
}
/// <summary>
/// 查詢表結構模型
/// </summary>
public class ColumnModel
{
/// <summary>
/// 表名
/// </summary>
public string Table_Name { get; set; }
/// <summary>
/// 列名
/// </summary>
public string Column_Name { get; set; }
/// <summary>
/// 資料型別
/// </summary>
public string Data_Type { get; set; }
/// <summary>
/// 欄位可否為空
/// </summary>
public string Nullable { get; set; }
/// <summary>
/// 字元長度
/// </summary>
public int Char_Length { get; set; }
/// <summary>
/// 小數
/// </summary>
public int? Data_Scale { get; set; }
}
}
相關文章
- SQL資料庫連線語句SQL資料庫
- 資料庫操作語句資料庫
- R語言連線資料庫（MySQL)R語言資料庫MySql
- 使用C#語言操作ADO資料庫 (轉)C#資料庫
- 資料庫常用操作SQL語句資料庫SQL
- 資料庫操作語言DDL資料庫
- oracle資料庫常用語句Oracle資料庫
- Oracle資料庫語句大全Oracle資料庫
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- C#連線和操作Oracle資料C#Oracle
- SQL資料庫操作語言DCLSQL資料庫
- 易語言連結資料庫資料庫
- 資料庫學習（二）資料操作語言：資料庫
- Oracle 資料庫監控SQL語句Oracle資料庫SQL
- SQL語言基礎(多表連線)SQL
- java連線oracle執行sql語句JavaOracleSQL
- C語言連線Oracle （轉載）C語言Oracle
- Oracle dos連線資料庫基本操作Oracle資料庫
- java語言操作Oracle資料庫中的CLOB資料型別 (轉)JavaOracle資料庫資料型別
- 【資料庫】SQL語句資料庫SQL
- oracle-資料庫- insert 插入語句Oracle資料庫
- idea內建資料庫 + sql語句庫表操作Idea資料庫SQL
- SQL語言基礎(資料控制語言)SQL
- SQL語言基礎(SELECT語句)SQL
- mysql常用語句練習-基於ecshop2.7.3資料庫(1)MySql資料庫
- 值得白嫖的資料庫常用操作語句彙總（資料庫、資料表、資料操作）資料庫
- Oracle Dataguard基本操作語句Oracle
- 3.DQL資料查詢語言（內連線，外連線，自連線）
- mysql資料庫sql語句基礎知識MySql資料庫
- MySQL 5 資料庫基礎語句總結MySql資料庫
- C#連線資料庫C#資料庫
- Oracle資料操作和控制語言詳解(轉)Oracle
- Oracle資料庫SQL語句執行過程Oracle資料庫SQL
- oracle資料庫：耗cpu sql語句優化Oracle資料庫SQL優化
- Oracle資料庫維護常用SQL語句集合Oracle資料庫SQL
- oracle資料庫巡檢（一）基本sql語句Oracle資料庫SQL
- 資料庫查詢語句資料庫
- 資料庫常用sql 語句資料庫SQL