|
|
using Oracle.ManagedDataAccess.Client;
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Text;
|
|
namespace DataexchangeServer.Helper
|
{
|
public class OracleDb
|
{
|
//public static string connectionString = "Data Source = 192.168.1.19; Persist Security Info=True;User ID = abtdev; Password=abt_dev";
|
public static string connectionString = "Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL))); Persist Security Info=True;User ID = jfprd; Password=jf_prd";
|
private OracleTransaction _transaction;
|
private readonly string _connectionString;
|
private OracleConnection _connection4Tran;
|
private readonly Queue<SqlTask> _transactionTaskList;
|
public OracleDb(string connectionString)
|
{
|
_transactionTaskList = new Queue<SqlTask>();
|
this._connectionString = connectionString;
|
}
|
|
public DataTable ExecuteDataTable(string sql)
|
{
|
return this.ExecuteDataTable(sql, CommandType.Text, null);
|
}
|
|
private DataTable ExecuteDataTable(string sql, CommandType commandType, OracleParameter[] parameters)
|
{
|
DataTable dataTable = new DataTable(Guid.NewGuid().ToString());
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
{
|
using (OracleCommand command = new OracleCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (OracleParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
new OracleDataAdapter(command).Fill(dataTable);
|
}
|
}
|
return dataTable;
|
}
|
|
|
public object ExecuteScalar(string sql)
|
{
|
return this.ExecuteScalar(sql, CommandType.Text, null);
|
}
|
|
private object ExecuteScalar(string sql, CommandType commandType, OracleParameter[] parameters)
|
{
|
object obj2;
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
{
|
using (OracleCommand command = new OracleCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (OracleParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
connection.Open();
|
obj2 = command.ExecuteScalar();
|
}
|
}
|
return obj2;
|
}
|
|
public bool SaveDataToDB(DataTable dataTable)
|
{
|
//dataTable.AcceptChanges();
|
//foreach (DataRow drTemp in dataTable.Rows)
|
//{
|
// drTemp.SetAdded();
|
//}
|
try
|
{
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
{
|
using (OracleCommand command = new OracleCommand("select * from " + dataTable.TableName+" WHERE 1<>1", connection))
|
{
|
OracleDataAdapter adapter = new OracleDataAdapter(command);
|
|
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
|
DataTable dsNew = new DataTable();
|
int count = adapter.Fill(dsNew);
|
//for (int i = 0; i < dataTable.Rows.Count; i++)
|
//{
|
// DataRow dr = dsNew.NewRow();
|
// for (int j = 0; j < dataTable.Columns.Count; j++)
|
// {
|
// dr[dsNew.Columns[j].ColumnName] = dataTable.Rows[i][j];
|
// }
|
// dsNew.Rows.Add(dr);
|
//}
|
count = adapter.Update(dataTable);
|
adapter.UpdateBatchSize = 5000;
|
//adapter.Update(dataTable);
|
return true;
|
//DataTable tbchange = dataTable.GetChanges();
|
//if (tbchange != null)
|
// try
|
// {
|
// adapter.Update(dataTable);
|
// }
|
// catch (Exception)
|
// {
|
|
// return false;
|
// }
|
|
}
|
}
|
}
|
catch (Exception)
|
{
|
return false;
|
}
|
// return true;
|
}
|
|
|
private class SqlTask
|
{
|
public string Text { get; private set; }
|
public CommandType CommandType { get; private set; }
|
public OracleParameter[] Parameters { get; private set; }
|
|
public SqlTask(string text, CommandType commandType, OracleParameter[] paras)
|
{
|
Text = text;
|
CommandType = commandType;
|
Parameters = paras;
|
}
|
}
|
|
/// <summary>
|
/// 执行一条计算查询结果语句，返回查询结果（object）。
|
/// </summary>
|
/// <param name="SQLString">计算查询结果语句</param>
|
/// <returns>查询结果（object）</returns>
|
public 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);
|
}
|
}
|
}
|
}
|
public int ExecuteNonQuery(string sql)
|
{
|
return this.ExecuteNonQuery(sql, CommandType.Text, null);
|
}
|
|
private int ExecuteNonQuery(string sql, CommandType commandType, OracleParameter[] parameters)
|
{
|
return this.ExecuteNonQuery(sql, commandType, parameters, false);
|
}
|
|
private int ExecuteNonQuery(string sql, CommandType commandType, OracleParameter[] parameters, bool joinTransaction)
|
{
|
int num;
|
OracleCommand command;
|
if (joinTransaction)
|
{
|
if ((this._transaction == null) || (this._connection4Tran == null))
|
{
|
throw new Exception("事务未初始化！");
|
}
|
_transactionTaskList.Enqueue(new SqlTask(sql, commandType, parameters));
|
}
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
{
|
using (OracleCommand commandt = new OracleCommand(sql, connection))
|
{
|
commandt.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (OracleParameter parameter in parameters)
|
{
|
commandt.Parameters.Add(parameter);
|
}
|
}
|
connection.Open();
|
num = commandt.ExecuteNonQuery();
|
}
|
}
|
return num;
|
}
|
|
|
/// <summary>
|
/// 执行多条SQL语句，实现数据库事务。
|
/// </summary>
|
/// <param name="SQLStringList">多条SQL语句</param>
|
public bool 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();
|
return true;
|
}
|
catch (OracleException E)
|
{
|
tx.Rollback();
|
return false;
|
}
|
}
|
}
|
}
|
}
|
