/// <summary>
/// 執行非查詢的SQL語句
/// </summary>
/// <param name="SQL">SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <returns></returns>
public int GetNonQueryBySQL(string SQL, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open(); //打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = SQL;
int num = cmd.ExecuteNonQuery();
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(num);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 執行SQL語句返回DataTable
/// </summary>
/// <param name="SQL">SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <returns></returns>
public DataTable GetDataTableBySQL(string SQL, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open(); //打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = SQL;
Oracle.ManagedDataAccess.Client.OracleDataAdapter adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataTable.TableName = "數據集";
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(dataTable);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 調用存儲返回String字符串信息和DataTable數據表格（最後兩個位置必須為返回參數，一個為輸出字符串另一個為游標，位置不能顛倒）
/// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string="",out_cursor=""}</para>
/// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值， out_cursor為游標不需要輸入值</para>
/// </summary>
/// <param name="storageName">存儲名稱</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <param name="obj">存儲參數對象</param>
/// <param name="dataTable">返回結果集</param>
/// <returns></returns>
public string GetStringAndDataTableByStorageName(string storageName, string DBUrl, object obj, out DataTable dataTable)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open();//打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = storageName; //存儲名稱
cmd.CommandType = CommandType.StoredProcedure;
PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); //獲取object中的字段名和值
for (int i = 0; i < properties.Length; i++)
{
if (i == (properties.Length - 2))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, short.MaxValue).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else if (i == (properties.Length - 1))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else
{ //設定輸入的類型和值
cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null); //賦值
}
}
DataTable dt = new DataTable();
dt.TableName = "數據集";
Oracle.ManagedDataAccess.Client.OracleDataAdapter oda = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
oda.Fill(dt);
dataTable = dt; //返回數據結果集
string message = cmd.Parameters[properties[properties.Length - 2].Name].Value.ToString(); //獲取輸出的字符串
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(message);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 調用存儲返回String字符串信息（最後一個位置必須為String類型字符，位置不能顛倒）
/// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string=""}</para>
/// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值</para>
/// </summary>
/// <param name="storageName"></param>
/// <param name="DBUrl"></param>
/// <param name="obj"></param>
/// <returns></returns>
public string GetStringDataByStorageName(string storageName, string DBUrl, object obj)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open();
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = storageName; //存儲名稱
cmd.CommandType = CommandType.StoredProcedure;
PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); //獲取object中的字段名和值
for (int i = 0; i < properties.Length; i++)
{
if (i == (properties.Length - 1))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, short.MaxValue).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else
{ //設定輸入的類型和值
cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null); //賦值
}
}
cmd.ExecuteNonQuery();
string message = cmd.Parameters[properties[properties.Length - 1].Name].Value.ToString(); //獲取返回的值
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(message);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
// retourne un DataSet avec le contenu d'une procedure qui retourne plusieurs résultats
public DataSet GetSelectProc(string Nom_Procedure)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet dataset = new DataSet();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = Nom_Procedure;
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter curseur =
new OracleParameter("curseur", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add(curseur);
connection.Open();
cmd.ExecuteNonQuery();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(dataset);
cmd.Dispose();
connection.Close();
return(dataset);
}
}
/// <summary>
/// 執行多條SQL語句，實現數據庫事務。
/// </summary>
/// <param name="SQLStringList">多條SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
public int GetNonQueryByManySQL(ArrayList SQLStringList, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl))
{
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
Oracle.ManagedDataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int num = 0;
for (int i = 0; i < SQLStringList.Count; i++)
{
string SQL = SQLStringList[i].ToString();//獲取SQL語句
if (SQL.Trim().Length > 1)
{
cmd.CommandText = SQL;
num = cmd.ExecuteNonQuery();
}
tx.Commit(); //提交事務
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
}
return(num); //返回執行結果數量
}
catch (Oracle.ManagedDataAccess.Client.OracleException E)
{
tx.Rollback();//事務回滾
throw new Exception(E.Message);
}
}
}