public static string CheckDPISConnect()
{
string ret = "false";
string connStr = DPISConnectionString;
OracleConnection conn = new OracleConnection(connStr);
try
{
conn.Open();
string ServerName = conn.DataSource;
string DbName = conn.Database;
ret = "true|ServerName = " + ServerName + "&DatabaseName=" + DbName;
}
catch (Exception ex)
{
ret = "false|Exception " + ex.Message + Environment.NewLine + ex.StackTrace + Environment.NewLine;
ret += "ConnectionString = " + connStr;
}
finally
{
conn.Close();
conn.Dispose();
}
return ret;
}
public void createPost(Member member)
{
try
{
String connstr = "Data source = studentoracle.students.ittralee.ie/orcl;User id = t00171832;Password="******";";
OracleConnection conn = new OracleConnection(connstr);
conn.Open();
string query = "insert into posts values('" + member.Username + "', '" + member.Email + "' , " + member.Phone + " , :BlobParameter , '" + member.Image + "' , '"
+ member.JoinDate + "' , " + member.MsgPrivate + " , '" + member.LastSeen + ")";
MessageBox.Show(query);
//insert the byte as oracle parameter of type blob
OracleParameter blobParameter = new OracleParameter();
blobParameter.OracleType = OracleType.Blob;
blobParameter.ParameterName = "BlobParameter";
blobParameter.Value = member.Image;
OracleCommand cmnd = new OracleCommand(query, conn);
cmnd.Parameters.Add(blobParameter);
cmnd.ExecuteNonQuery();
cmnd.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
}
}
public static bool TestConnect(string user, string pwd, string serviceName, out string msg)
{
//Data Source=lan_189;User ID=user_ahdy_shi;Password=user_ahdy_shi
string conn = string.Format("Data Source={0};User ID={1};Password={2}", serviceName.Trim(), user, pwd);
try
{
using (OracleConnection con = new OracleConnection(conn))
{
OracleConnection.ClearPool(con);
System.Threading.Thread.Sleep(100);
con.Open();
con.Close();
con.Dispose();
}
msg = "连接" + serviceName.ToUpper() + "成功";
return true;
}
catch (Exception ex)
{
msg = ex.Message;
return false;
}
}
public List<String> make_connection()
{
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string selectStmt = string.Format("select car.*, mar.IMENAMARKA from VOZILA car, MODELI mod, MARKA mar where car.IMENAMODEL = mod.IMENAMODEL and mod.IMENAMARKA = mar.IMENAMARKA ");
OracleCommand cmd = new OracleCommand(selectStmt, conn);
OracleDataReader dataReader = cmd.ExecuteReader();
List<String> redovi = new List<string>();
while (dataReader.Read())
{
string temp = dataReader.GetString(1).ToString() + " ";
temp = temp + dataReader.GetString(16).ToString() + " ";
temp = temp + dataReader.GetString(15).ToString() + " ";
temp = temp + dataReader.GetValue(2).ToString() + " ";
temp = temp + dataReader.GetValue(3).ToString() + " ";
temp = temp + dataReader.GetValue(4).ToString() + " ";
temp = temp + dataReader.GetValue(5).ToString() + " ";
temp = temp + dataReader.GetValue(6).ToString() + " ";
temp = temp + dataReader.GetValue(7).ToString() ;
redovi.Add(temp);
}
conn.Close();
conn.Dispose();
return redovi;
}
/// <summary>
/// 关闭数据库
/// </summary>
/// <param name="connection">OracleConnection连接对象</param>
public static void Close(OracleConnection connection)
{
if (connection != null)
{
try
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
}
catch (System.Exception ex)
{
connection.Close();
connection.Dispose();
Tracer.Debug("关闭数据库出错:" + ex.ToString());
}
}
}
/// <summary>
/// 关闭数据库
/// </summary>
/// <param name="connection">OracleConnection连接对象</param>
public static void Close(OracleConnection connection)
{
if (connection != null)
{
try
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
}
catch (System.Exception ex)
{
connection.Close();
connection.Dispose();
throw new System.Exception(ex.Message);
}
}
}
/// <summary>
/// Detects if connection to database can be established based on username,
/// password and database name.
/// </summary>
/// <param name="p_datasource">Connection string in the .net form off: "User Id="+username+";Password="******";Data Source="+database;</param>
public static void Check(string p_datasource)
{
OracleConnection con = new OracleConnection();
//using connection string attributes to connect to Oracle Database
con.ConnectionString = p_datasource;
try
{
con.Open();
Console.WriteLine("Connection succesfull");
Console.WriteLine("Server version: {0}", con.ServerVersion);
}
catch (OracleException e)
{
Console.WriteLine("Connection failed");
Console.WriteLine(e.Message);
Environment.Exit(1);
}
finally
{
con.Close();
con.Dispose();
}
}
/// <summary>
/// 执行一个SQL语句命令，没有返回数据集，只返回影响记录数
/// </summary>
/// <param name="conn">OracleConnection</param>
/// <param name="sqlString">SQL语句</param>
/// <param name="param">参数数组</param>
/// <returns></returns>
public static int ExecuteSQL(OracleConnection conn,string sqlString, params OracleParameter[] param)
{
if (conn.State==ConnectionState.Closed)
{
conn.Open();
}
int n= ExecuteNonQuery(conn, CommandType.Text, sqlString, param);
conn.Close();
conn.Dispose();
return n;
}
/// <summary>
/// 执行一个OracleCommand命令并返回结果集
/// </summary>
/// <remarks>
/// 例如：
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">有效的 OracleConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
catch (System.Exception ex)
{
connection.Close();
connection.Dispose();
LogHelper.WriteLog("ExecuteDataset出错:" + ex.ToString());
return null;
}
}
public static int ExecuteSQL(OracleConnection conn, string sqlString, params OracleParameter[] parameterValues)
{
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, conn, (OracleTransaction)null, CommandType.Text, sqlString, parameterValues);
return ExecuteNonQuery(conn, CommandType.Text, sqlString, parameterValues);
}
catch (System.Exception ex)
{
conn.Close();
conn.Dispose();
LogHelper.WriteLog("ExecuteSQL数据库出错:" + ex.ToString());
return 0;
}
}
/// <summary>
/// 打开数据库
/// </summary>
/// <param name="connection">OracleConnection连接对象</param>
public static void Open(OracleConnection connection)
{
if (connection != null)
{
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
}
catch (System.Exception ex)
{
connection.Close();
connection.Dispose();
LogHelper.WriteLog("打开数据库出错:" + ex.ToString());
}
}
}
protected void GV_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
using (OracleConnection conn = new OracleConnection(DBHelper.ConnectionString))
{
string reason_id = GV.DataKeys[e.RowIndex].Values[0].ToString();
string reason_desc = ((TextBox)GV.Rows[e.RowIndex].FindControl("TxtDesc")).Text;
string active = ((CheckBox)(GV.Rows[e.RowIndex].FindControl("ChkActive"))).Checked == true ? "1" : "0";
string sqlupdate = "update jp_lack_reason set reason_desc = '" + reason_desc + "',is_valid='" + active + "' where reason_id = '" + reason_id + "' ";
OracleCommand updatecomm = new OracleCommand(sqlupdate, conn);
try
{
conn.Open();
updatecomm.ExecuteNonQuery();
GV.EditIndex = -1;
GVDataBind();
}
catch (Exception ex)
{
conn.Close();
Response.Write("<script language=javascript>alert('" + ex.Message + "')</script>");
}
finally
{
updatecomm.Dispose();
conn.Dispose();
conn.Close();
}
}
}
/// <summary>
/// 返回结果集的第一行第一列; 执行完成没有关闭OracleConnection连接,需要手动关闭
/// </summary>
/// <remarks>
/// 例如：
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">有效的 OracleConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程T-OleDb的名称或命令</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回结果集的第一行第一列</returns>
public static object ExecuteScalar(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
return cmd.ExecuteScalar();
}
catch (System.Exception e)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
throw new System.Exception(e.Message, e);
}
}
private void CallProce(string RECORD_ID, string ICCARD_TRANS_FLAG, string NAME_TRANS_FLAG, string ERR_MSG)
{
OracleConnection conn = new OracleConnection(OracleHelper.OraConnectString);
OracleCommand comm = new OracleCommand();
comm.Connection = conn;
if (conn.State == ConnectionState.Closed) conn.Open();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "UPDATE_CIMC_LBR_IDCARD_TRANS";
OracleTransaction oTran = conn.BeginTransaction();
comm.Transaction = oTran;
try
{
comm.Parameters.Add("RECORD_ID_", OracleType.Int32).Value = Convert.ToInt32(RECORD_ID);
comm.Parameters.Add("ICCARD_TRANS_FLAG_", OracleType.VarChar, 1).Value = ICCARD_TRANS_FLAG;
comm.Parameters.Add("NAME_TRANS_FLAG_", OracleType.VarChar, 1).Value = NAME_TRANS_FLAG;
comm.Parameters.Add("ERR_MSG_", OracleType.VarChar, 3000).Value = ERR_MSG;
comm.ExecuteNonQuery();
oTran.Commit();
}
catch (Exception ex)
{
oTran.Rollback();
throw new Exception(ex.Message);
}
finally
{
conn.Close();
conn.Dispose();
comm.Dispose();
}
}
public static void TestPersistSucurityInfo6()
{
Console.WriteLine("\nTestPersistSucurityInfo6 - external auth using persist security info");
string user = Environment.UserName;
if (!Environment.UserDomainName.Equals(String.Empty))
user = Environment.UserDomainName + "\\" + Environment.UserName;
Console.WriteLine("Environment UserDomainName and UserName: "******"Open connection using external authentication...");
OracleConnection con = new OracleConnection("Data Source=palis;Integrated Security=true");
Console.WriteLine("ConnectionString before open: " + con.ConnectionString);
try {
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT USER FROM DUAL";
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
Console.WriteLine("User: "******"USER")));
con.Close();
Console.WriteLine("ConnectionString after close: " + con.ConnectionString);
}
catch (Exception e) {
Console.WriteLine("Exception caught: " + e.Message);
Console.WriteLine("Probably not setup for external authentication. This is fine.");
}
con.Dispose();
Console.WriteLine("ConnectionString after dispose: " + con.ConnectionString);
con = null;
Console.WriteLine("\n\n");
}
/// <summary>
/// 执行SQL语句,返回DataTable;执行完成自动关闭OracleConnection连接
/// </summary>
/// <param name="connection">有效的 OracleConnection</param>
/// <param name="cmd">OracleCommand对象</param>
/// <param name="sql">SQL语句</param>
/// <returns></returns>
public static DataTable GetDataTable(OracleConnection connection, OracleCommand cmd, string sql)
{
try
{
PrepareCommand(cmd, connection, (OracleTransaction)null, CommandType.Text, sql, (OracleParameter[])null);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();
connection.Dispose();
return ds.Tables[0];
}
catch (System.Exception e)
{
connection.Close();
connection.Dispose();
throw new System.Exception(e.Message, e);
}
}
public void CheckSMSStatus(Object stateInfo)
{
try
{
var _url = ConfigurationManager.AppSettings["ServiceURL"];
var _user = ConfigurationManager.AppSettings["wsUser"];
var _pass = ConfigurationManager.AppSettings["wsPwd"];
var connstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
var ws = new LG.SMS.Service();
ws.Url = _url;
var sql = " SELECT moseq " +
",shortcode " +
",cell_no " +
",msgbody " +
",msgtype " +
",mttotalseq " +
",mtseqref " +
",cpid" +
" FROM tb_mt_hist " +
" WHERE send_flag ='N' AND ROWNUM <=100";
var oradbConnection = new OracleConnection(connstring);
var sqlcmd = new OracleCommand(sql, oradbConnection);
var _dataAdapter = new OracleDataAdapter();
_dataAdapter.SelectCommand = sqlcmd;
var _dataTable = new DataTable();
_dataAdapter.Fill(_dataTable);
StringBuilder rtbLog = new StringBuilder();
sql = string.Empty;
int count = (_dataTable.Rows == null ? 0 : _dataTable.Rows.Count);
//Task[] tasks = new Task[] { };
//if (count > 0) Array.Resize(ref tasks, count);
int index = 0;
for (index = 0; index < count; index++ )
{
int idtm = index;
//Application.DoEvents();
// Dim _mt_send_datetime = Format(Date.Now, "MMddyyyyHHmmss")
//tasks[idtm] = Task.Factory.StartNew(() =>
{
DataRow _row = _dataTable.Rows[idtm];
System.Console.WriteLine("<==============================MT sending==============================>");
System.Console.WriteLine("MT started : " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " to " + (_row["cell_no"] == null ? string.Empty : _row["cell_no"].ToString()));
var _result = ws.SendMT((_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()),
(_row["shortcode"] == null ? string.Empty : _row["shortcode"].ToString()),
(_row["cell_no"] == null ? string.Empty : _row["cell_no"].ToString()),
(_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()),
(_row["msgtype"] == null ? string.Empty : _row["msgtype"].ToString()),
(_row["mttotalseq"] == null ? string.Empty : _row["mttotalseq"].ToString()),
(_row["mtseqref"] == null ? string.Empty : _row["mtseqref"].ToString()),
(_row["cpid"] == null ? string.Empty : _row["cpid"].ToString()),
"1",
_user,
_pass);
//int _result = 200;
byte attemp = 1;
int mo_cnt = 0;
do
{
var _mt_sent_datetime = DateTime.Now.ToString("MMddyyyyHHmmss");
if (attemp <= 3)
{
if (_result == 200)
{
sql = " UPDATE tb_mt_hist SET send_flag ='Y', " +
" result ='" + _result.ToString() + "'," +
" send_time ='" + _mt_sent_datetime + "', " +
" finish_time ='" + _mt_sent_datetime + "' " +
" WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'";
System.Console.WriteLine("Success :" + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
break;
}
else
{
sql = " UPDATE tb_mt_hist SET result ='" + _result.ToString() + "', " +
" send_time ='" + _mt_sent_datetime + "'" +
" WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'";
System.Console.WriteLine("Fail : " + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
attemp++;
}
}
else
{
sql = " UPDATE tb_mt_hist SET send_flag ='Y', " +
" result ='" + _result.ToString() + "'," +
" send_time ='" + _mt_sent_datetime + "', " +
" finish_time ='" + _mt_sent_datetime + "' " +
" WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'";
System.Console.WriteLine("Fail : " + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
break;
}
mo_cnt++;
} while (attemp == 5);
mo_cnt++;
System.Console.WriteLine("...done, cnt : " + mo_cnt.ToString());
var oradbConnectionSub = new OracleConnection(connstring);
oradbConnectionSub.Open();
using (var sqlcmdsub = new OracleCommand(sql, oradbConnectionSub))
{
sqlcmdsub.CommandType = CommandType.Text;
sqlcmdsub.ExecuteNonQuery();
}
oradbConnectionSub.Close();
oradbConnectionSub.Dispose();
}//);
}
//Task.WaitAll(tasks);
sqlcmd.Dispose();
_dataAdapter.Dispose();
_dataTable.Dispose();
oradbConnection.Close();
oradbConnection.Dispose();
}
catch (Exception ex)
{
System.Console.WriteLine("<==========================MT sending errors===============================>");
System.Console.WriteLine(ex.Message + ", " + DateTime.Now.ToString("yyyyMMdd HH:mm:ss"));
}
string stt = smsindex.ToString();
if (smsindex % 10 == 1) stt += "ST";
else if (smsindex % 10 == 2) stt += "ND";
else if (smsindex % 10 == 3) stt += "RD";
else stt += "TH";
System.Console.WriteLine("---------------------------------------------------------------------------");
System.Console.WriteLine("---------------------- END SMS CONSOLE APPLICATION AT " + stt.PadRight(21, '-'));
System.Console.WriteLine("---------------------------------------------------------------------------");
System.Console.WriteLine("\n\n");
smsindex++;
//reset index
if (smsindex > 1000000) smsindex = 0;
System.Console.Read();
}
/// <summary>
/// 执行一个OracleCommand(返回一个结果OracleDataReader);执行完成没有关闭OracleConnection连接,需要手动关闭
/// </summary>
/// <param name="connection">有效的 OracleConnection</param>
/// <param name="transaction">有效的 OracleTransaction, or 'null'</param>
/// <param name="commandType">命令类型 (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">以一个数组的形式返回OracleParameters </param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by OracleProvider</param>
/// <returns></returns>
private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
OracleDataReader dr = null;
try
{
if (connectionOwnership == OracleConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
// dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
dr = cmd.ExecuteReader();
}
if (transaction != null)
{
transaction.Commit();
}
return (OracleDataReader)dr;
}
catch (System.Exception e)
{
if (transaction != null)
{
transaction.Rollback();
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
throw new System.Exception(e.Message, e);
}
}
/// <summary>
/// 查询审批流程
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public List<FLOW_FLOWRECORDDETAIL_T> GetFlowInfo(OracleConnection con, string FormID, string FlowGUID, string CheckState, string Flag, string ModelCode, string CompanyID, string EditUserID)
{
List<FLOW_FLOWRECORDDETAIL_T> list = new List<FLOW_FLOWRECORDDETAIL_T>();
try
{
List<FlowType> FlowTypeList = new List<FlowWFService.FlowType>();
FlowTypeList.Add(FlowType.Approval);
FlowTypeList.Add(FlowType.Pending);
//Debug.WriteLine("GetFlowInfo\n");
//Debug.WriteLine(DateTime.Now.ToString());
//Debug.WriteLine("\n");
//有formid和modelcode不对返回数据量作限制，否则只返回前20条master数据
if (!string.IsNullOrEmpty(FormID) && !string.IsNullOrEmpty(ModelCode))
{
list = FlowBLL2.GetFlowInfoV(con, FormID, FlowGUID, CheckState, Flag, ModelCode, CompanyID, EditUserID, FlowTypeList);
}
else
{
Tracer.Debug("GetFlowInfoTop: formID:" + FormID + "--FlowGuid:" + FlowGUID
+ "--CheckState:" + CheckState + "--Flag:" + Flag + "--ModelCode:" + ModelCode + "--CompanyID:" + CompanyID + "--EditUserID:" + EditUserID);
list = FlowBLL2.GetFlowInfoTop(con, FormID, FlowGUID, CheckState, Flag, ModelCode, CompanyID, EditUserID, FlowTypeList);
}
}
catch (Exception ex)
{
if (con.State == ConnectionState.Open)
{
con.Close();
con.Dispose();
}
LogHelper.WriteLog("GetFlowInfo异常信息 formid：" + FormID + ":" + ex.ToString());
//Tracer.Debug("GetFlowInfo: -" + FormID + "-" + ex.InnerException +"\n"+ ex.Message);
throw ex;
}
if (con.State == ConnectionState.Open)
{
con.Close();
con.Dispose();
}
return list;
}
/// <summary>
/// 事务对象
/// </summary>
// private OracleTransaction Transaction { get; set; }
#region 开始事务
/// <summary>
/// 开始事务
/// </summary>
/// <param name="conn">OracleConnection连接对象</param>
public OracleCommand BeginTransaction(OracleConnection connObjection)
{
try
{
OracleCommand cmd = new OracleCommand();
OracleTransaction Transaction = null;
if (Transaction == null || Transaction.Connection != connObjection)
{
if (connObjection != null && connObjection.State == ConnectionState.Open)
{
cmd.Connection = connObjection;
Transaction = connObjection.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = Transaction;
}
else
{
connObjection.Open();
cmd.Connection = connObjection;
Transaction = connObjection.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = Transaction;
}
}
return cmd;
}
catch (System.Exception e)
{
connObjection.Close();
connObjection.Dispose();
throw new System.Exception(e.Message, e);
}
}
/// <summary>
/// 关闭数据库
/// </summary>
/// <param name="connection">OracleConnection连接对象</param>
public static void Close(OracleConnection connection)
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
connection = null;
if (Transaction != null)
{
Transaction = null;
}
}
}
/// <summary>
/// 执行SQL语句命令(通过OracleConnection);执行完成没有关闭OracleConnection连接,需要手动关闭
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">命令类型(SQL语句或存储过程)</param>
/// <param name="commandText">命令</param>
/// <param name="commandParameters">参数数组</param>
/// <returns></returns>
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
return cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
connection.Close();
connection.Dispose();
throw new System.Exception(e.Message, e);
}
}
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
return cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
connection.Close();
connection.Dispose();
LogHelper.WriteLog("ExecuteNonQuery出错:" + ex.ToString());
return 0;
}
}
/// <summary>
/// 执行SQL语句命令(通过OracleConnection);执行完成自动关闭OracleConnection连接
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="sqlString">SQL语句</param>
/// <param name="parameterValues">参数数组,可以为Null</param>
/// <returns></returns>
public static int ExecuteSQL(OracleConnection connection, string sqlString, params OracleParameter[] parameterValues)
{
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, CommandType.Text, sqlString, parameterValues);
return cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
throw new System.Exception(e.Message, e);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
}
}
/// <summary>
/// 通过事务执行SQL语句命令
/// </summary>
/// <param name="SQLStringList"></param>
public static void ExecuteSQLTransaction(OracleConnection connection, List<string> SQLStringList)
{
connection.Open();
OracleCommand command = new OracleCommand();
command.Connection = connection;
OracleTransaction transaction = connection.BeginTransaction();
if (transaction != null)
{
//command.Transaction = transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
}
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
string str = SQLStringList[i].ToString();
if (str.Trim().Length > 1)
{
command.CommandText = str;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (OracleException e)
{
transaction.Rollback();
string stringlist = "";
foreach (var str in SQLStringList)
{
stringlist += str.ToString() + "\r\n";
}
LogHelper.WriteLog("MsOracle.ExecuteSQLTransaction", stringlist, e);
throw new System.Exception(e.Message);
}
finally
{
connection.Close();
connection.Dispose();
}
}
/// <summary>
/// 执行SQL/存储过程返回 DataSet ;执行完成没有关闭OracleConnection连接,需要手动关闭
/// </summary>
/// <remarks>
/// 例如：
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">有效的 OracleConnection</param>
/// <param name="commandType">命令类型:SQL语句或存储过程</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <returns>返回DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText)
{
try
{
return ExecuteDataset(connection, commandType, commandText, (OracleParameter[])null);
}
catch (System.Exception e)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
throw new System.Exception(e.Message, e);
}
}
public static DataTable ExecuteTable(OracleConnection connection, OracleCommand cmd, string commandText)
{
try
{
PrepareCommand(cmd, connection, (OracleTransaction)null, CommandType.Text, commandText, (OracleParameter[])null);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
catch (System.Exception ex)
{
connection.Close();
connection.Dispose();
LogHelper.WriteLog("ExecuteDataset出错:" + ex.ToString());
return null;
}
}
/// <summary>
/// 执行一个存储过程并返回结果集 ;执行完成没有关闭OracleConnection连接,需要手动关闭
/// </summary>
/// <remarks>
/// 这个方法并没有提供存取到输出参数或返回值参数.
///
/// 例如：
/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">有效的 OracleConnection</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">params object[]</param>
/// <returns>返回DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, string spName, params object[] parameterValues)
{
try
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
OracleParameter[] commandParameters = OracleProviderParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
catch (System.Exception e)
{
connection.Close();
connection.Dispose();
throw new System.Exception(e.Message, e);
}
}
//根据SQL返回数据的值，如果为空返回的是null
private string[,] FnGetValue(string sql, string[,] arrValue)
{
string strSql = sql;
int m = arrValue.GetLength(0);
int n = arrValue.GetLength(1);
string[,] arrDetails = new string[m, n];
System.Data.OracleClient.OracleConnection oraConn =
new System.Data.OracleClient.OracleConnection(
ConfigurationManager.ConnectionStrings["oraConnectionString"].ConnectionString);
System.Data.OracleClient.OracleCommand oraComm = new System.Data.OracleClient.OracleCommand(strSql, oraConn);
oraConn.Open();
System.Data.OracleClient.OracleDataReader oraData = oraComm.ExecuteReader();
//读取相关数据
for (int i = 0; i < m; i++)
{
if (oraData.Read())
{
for (int j = 0; j < n; j++)
{
arrDetails[i, j] = oraData.IsDBNull(j) ? null : oraData.GetValue(j).ToString();
}
}
else
{
break;
}
}
oraData.Close();
oraComm.Cancel();
oraConn.Close();
oraData.Dispose();
oraComm.Dispose();
oraConn.Dispose();
// arrReturn = fnSetLeftingArr(arrDetails);
return arrDetails;
}
/// <summary>
///执行SQL语句,返回DataTable;执行完成没有关闭OracleConnection连接,需要手动关闭
/// </summary>
/// <remarks>
/// 例如：
/// DataTable ds = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">有效的 OracleConnection</param>
/// <param name="commandType">命令类型:SQL语句或存储过程</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回DataSet</returns>
public static DataTable ExecuteTable(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
catch (System.Exception e)
{
connection.Close();
connection.Dispose();
throw new System.Exception(e.Message, e);
}
}