public bool CheckUseAcademicPositionName()
{
bool result = true;
OracleConnection conn = ConnectionDB.GetOracleConnection();
// Create the command
OracleCommand command = new OracleCommand("SELECT count(ACAD_NAME) FROM TB_ACADEMIC_POSITION WHERE ACAD_NAME = :ACAD_NAME ", conn);
// Add the parameters.
command.Parameters.Add(new OracleParameter("ACAD_NAME", ACAD_NAME));
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
int count = (int)(decimal)command.ExecuteScalar();
if (count >= 1)
{
result = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
command.Dispose();
conn.Close();
}
return result;
}
private void button1_Click(object sender, EventArgs e)
{
#region connection
try
{
con = new OracleConnection();
con.ConnectionString = "Data Source=orcl.iacsd.org;User ID=user101;Password=user101;Unicode=True";
cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
//Provide the name of stored procedu re
cmd.CommandText = "select * from Emp";
cmd.Connection = con;
con.Open();
MessageBox.Show(" Number of Rows =" + cmd.ExecuteScalar().ToString());
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
#endregion
}
/// <summary>
/// 通过sql语句获得字段值
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns>返回sql语句查询结果的第一记录的第一个字段内容
/// 如果sql语句执行异常或者没有记录，返回空字符串""
/// </returns>
public string GetFieldValue(string sqlstr)
{
string result = string.Empty;
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = sqlstr;
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
object obj = dbCommand.ExecuteScalar();
if (obj != null)
{
result = obj.ToString();
}
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " GetFieldValue:" + sqlstr + "\r\n" + e.Message);
return(result);
}
return(result);
}
/// <summary></summary>
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
///
/// SELECT 语句
/// <returns></returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用
public object ExecuteScalar(string sql)
{
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(null);
}
try
{
if (oracleConnection.State == System.Data.ConnectionState.Closed)
{
oracleConnection.Open();
}
oracleCommand = new OracleCommand(sql, oracleConnection);
return(oracleCommand.ExecuteScalar());
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return(null);
}
}
}
public void run()
{
OracleConnection con = null;
OracleTransaction txn;
Exception exp = null;
try
{
BeginCase("OracleTransaction Rollback");
//
//prepare data
base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
string Result = "";
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
txn = con.BeginTransaction();
OracleCommand cmd = new OracleCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
cmd.ExecuteNonQuery();
txn.Rollback();
//
//
cmd = new OracleCommand("Select LastName From Employees Where EmployeeID = 100", con);
Result = cmd.ExecuteScalar().ToString();
Compare(Result,"Last100" );
this.Log(Result);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public string ExecuteScalar(string sql)
{
try
{
using (OracleConnection connection = new OracleConnection(_ConnectionString))
{
WriteLog("[Oracle]" + sql);
connection.Open();
var cmd = new OracleCommand(sql, connection);
object o = cmd.ExecuteScalar();
string s = null;
if (o != null && Object.Equals(o, System.DBNull.Value) == false)
{
s = o.ToString();
}
WriteLog("[Oracle]查询结果：" + s);
return s;
}
}
catch (Exception ex)
{
if (AfterExceptionThrow != null) AfterExceptionThrow(ex);
else throw ex;
return null;
}
}
public int GetNextIdWithTransNewAppWithArea(string area, System.Data.OracleClient.OracleCommand cmd)
{
cmd.CommandText = "";
cmd.CommandText = @"SELECT nvl(to_number(max(SUBSTR(APPLICATION_NO, 4, 10))),0)+1 FROM CGD_APPLICANT_MASTER
where to_number(SUBSTR(APPLICATION_NO, 2, 2)) ='" + area + "'";
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
public void DoNormalConnectionTest()
{
using (OracleConnection conn = new OracleConnection(_ConnectionString))
{
OracleCommand comm = new OracleCommand("select * from web_sql");
comm.Connection = conn;
conn.Open();
comm.ExecuteScalar();
Thread.Sleep(_SleepTime);
conn.Close();
}
}
public void SetUp()
{
Exception exp = null;
BeginCase("Setup");
try
{
//prepare Data
OracleCommand cmdPrepare = new OracleCommand("", new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString));
cmdPrepare.Connection.Open();
cmdPrepare.CommandText = "DELETE FROM Employees WHERE EmployeeID = 99999";
cmdPrepare.ExecuteScalar();
cmdPrepare.Connection.Close();
cmdPrepare.Dispose();
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
cmd = new OracleCommand("", con);
con.Open();
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
}
protected override void Execute(CodeActivityContext context)
{
List<Dictionary<string, string>> retorno = new List<Dictionary<string, string>>();
using (OracleConnection connection =
new OracleConnection(connectionStringDestino.Get(context)))
{
connection.Open();
using (OracleCommand command = new OracleCommand(query.Get(context), connection))
{
LogScheduler.Save("Oracle", "Incio Query : \n " + query.Get(context));
AddParameters(context, command);
if (persist.Get(context))
{
try
{
command.ExecuteScalar();
}
catch (Exception ex)
{
LogScheduler.Save("OracleUpdate", ex.Message);
}
}
else
GetResultadoOracle(retorno, command);
LogScheduler.Save("Oracle", "Fim Query : \n " + query.Get(context));
}
connection.Close();
}
resultQuery.Set(context, retorno);
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
//Currently not running on Oracle/DB2: .Net-Failed, GH:Pass
//not runnig on postgres because "The 'current catalog' property is not supported by the 'Mainsoft.JDBC.OLEDB.1' provider."
if (ConnectedDataProvider.GetDbType(con) != DataBaseServer.Oracle &&
ConnectedDataProvider.GetDbType(con) != DataBaseServer.DB2 &&
ConnectedDataProvider.GetDbType(con) != DataBaseServer.Sybase &&
ConnectedDataProvider.GetDbType(con) != DataBaseServer.PostgreSQL)
{
try
{
BeginCase("Change DataBase");
((IDbConnection)con).ChangeDatabase("GHTDB_EX");
Compare(((IDbConnection)con).Database , "GHTDB_EX");
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("Check DataBase Changed ");
OracleCommand cmd = new OracleCommand("select count(*) from Customers", con);
object obj = cmd.ExecuteScalar();
Compare(obj != null, true);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
}
public void run()
{
Exception exp = null;
int intRecordsAffected = 0;
string sql = "Update Shippers Set CompanyName=:CompName Where ShipperID = 2";
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleCommand cmd = new OracleCommand("", con);
con.Open();
//get expected result
cmd.CommandText = "select count(*) from Shippers where ShipperID = 2";
int ExpectedRows = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = sql;
//Currently not running on DB2: .Net-Failed, GH:Pass
//if (con.Provider.IndexOf("IBMDADB2") >= 0) return ;
cmd.Parameters.Add(new OracleParameter());
cmd.Parameters[0].ParameterName = "CompName";
cmd.Parameters[0].OracleType = OracleType.VarChar; //System.InvalidOperationException:
cmd.Parameters[0].Size = 20; //System.InvalidOperationException
cmd.Parameters[0].SourceColumn = "CompanyName";
cmd.Parameters[0].Value = "Comp1";
try
{
BeginCase("Prepare Exception - missing OracleType");
cmd.Prepare();
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
cmd.Parameters[0].OracleType = OracleType.VarChar;
// try
// {
// BeginCase("Prepare Exception - missing Size");
// try
// {
// cmd.Parameters[0].Size = 0;
// cmd.Prepare();
// }
// catch (Exception ex) {exp = ex;}
// Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName );
// exp=null;
// }
// catch(Exception ex){exp = ex;}
// finally{EndCase(exp); exp = null;}
// cmd.Parameters[0].Size = 20;
try
{
BeginCase("Prepare Exception - missing Size");
try
{
con.Close();
cmd.Prepare();
}
catch (Exception ex) {exp = ex;}
Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName );
exp=null;
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
con.Open();
try
{
BeginCase("ExecuteNonQuery first time");
intRecordsAffected = cmd.ExecuteNonQuery();
Compare(intRecordsAffected , ExpectedRows);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("ExecuteNonQuery second time, chage value");
cmd.Parameters[0].Value = "Comp2";
intRecordsAffected = cmd.ExecuteNonQuery();
Compare(intRecordsAffected , ExpectedRows);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public object ExcuteScalarSql(string sqlCmd, string[] strParams, object[] strValues)
{
if (string.IsNullOrEmpty(sqlCmd))
throw new ArgumentNullException("sqlCmd");
if ((strParams == null && strValues != null)
|| (strParams != null && strValues == null)
|| (strParams != null && strParams.Length != strValues.Length))
throw new ArgumentException(
string.Format("Parameters do not match values when excuting SQL {0}.!", sqlCmd));
var cmd = new OracleCommand { Connection = _conn, CommandText = sqlCmd };
if (_isInTransaction)
cmd.Transaction = _trans;
if (strParams != null)
{
for (int i = 0; i < strParams.Length; i++)
cmd.Parameters.AddWithValue(strParams[i], strValues[i]);
}
return cmd.ExecuteScalar();
}
/// <summary>
/// Execute a OracleCommand(that returns a 1x1 resultset) against the specified OracleConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid OracleConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the OracleParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
// This routine writes a User Event audit entry
// into the SYS_USE_ADUIT table
public void WriteUserEvent(string AppName, string UserID, UserEvent uEvent)
{
System.Data.OracleClient.OracleConnection ConnOracle;
System.Data.OracleClient.OracleCommand CmdOracle;
//Parameters for the Oracle Package
OracleParameter return_val = new OracleParameter("v_success", OracleType.VarChar, 1);
return_val.Direction = ParameterDirection.ReturnValue;
OracleParameter p_external_user_id = new OracleParameter("p_external_user_id", OracleType.VarChar, 250);
OracleParameter p_system_use = new OracleParameter("p_system_use", OracleType.VarChar, 250);
OracleParameter p_system_activity = new OracleParameter("p_system_activity", OracleType.VarChar, 250);
ConnOracle = new System.Data.OracleClient.OracleConnection();
CmdOracle = new System.Data.OracleClient.OracleCommand();
try
{
p_external_user_id.Value = UserID;
switch (uEvent)
{
case UserEvent.LogonSuccessful:
p_system_activity.Value = "Logon successful";
break;
case UserEvent.LogonFailed:
p_system_activity.Value = "Logon failed";
break;
case UserEvent.Logoff:
p_system_activity.Value = "Logoff";
break;
case UserEvent.SessionTimeout:
p_system_activity.Value = "Session timeout";
break;
}
p_system_use.Value = AppName;
//Use Oracle Package
ConnOracle.ConnectionString = GSA.R7BD.Utility.DataAccess.ConnStrBDFApps();
CmdOracle.Connection = ConnOracle;
CmdOracle.CommandText = "bdfapps.SYS_USE_AUDIT_PKG.CREATE_SYS_USE_AUDIT_REC";
CmdOracle.CommandType = CommandType.StoredProcedure;
CmdOracle.Parameters.Clear();
CmdOracle.Parameters.Add(p_external_user_id);
CmdOracle.Parameters.Add(p_system_activity);
CmdOracle.Parameters.Add(p_system_use);
CmdOracle.Parameters.Add(return_val);
if (ConnOracle.State != ConnectionState.Open)
{
ConnOracle.Open();
}
CmdOracle.ExecuteScalar();
//Check the return value
if (CmdOracle.Parameters["v_success"].Value.ToString().Trim() == "F")
{
GSA.R7BD.Utility.EventLog.AddWebErrors(AppName, "GSA.R7BD.Utility", "Audit", "Call to bdfapps.SYS_USE_AUDIT_PKG.CREATE_SYS_USE_AUDIT_REC failed.");
}
}
catch (Exception ex)
{
// Write the exception to the error log
GSA.R7BD.Utility.EventLog.AddWebErrors(AppName, "GSA.R7BD.Utility", "Audit", ex.Message);
}
finally
{
if (ConnOracle.State == ConnectionState.Open)
{
ConnOracle.Close();
}
}
}
public override object ExecuteScalar(CommandType commandType, string commandText, ParameterCollection commandParameters)
{
object obj2 = null;
using (OracleCommand command = new OracleCommand())
{
this.PrepareCommand(command, commandType, commandText, commandParameters);
obj2 = command.ExecuteScalar();
command.Parameters.Clear();
}
return obj2;
}
//[Test]
public void DoTestTypes1(OracleConnection conn)
{
DataSet ds = new DataSet();
OracleCommand comm = new OracleCommand("",conn);
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = comm;
string tableName = getDbObjectName("Employees",conn);
int expectedRowsCount = 8;
#region Select by full table name in the same catalog
string[] arr = new string[2];
arr[0] = "LastName";
arr[1] = "FirstName";
prepareTableForTest(conn,expectedRowsCount,"Employees","EmployeeID",arr);
comm.CommandText="select max(EmployeeID) from " + tableName;
// on some databases the max is on a field which is decimal
decimal maxEmployee = decimal.Parse(comm.ExecuteScalar().ToString()) - expectedRowsCount;
comm.CommandText = "SELECT EmployeeID FROM " + tableName + " where EmployeeID > " + maxEmployee.ToString() ;
da.Fill(ds);
Exception exp = null;
try
{
BeginCase("Select by full table name in the same catalog");
Compare(ds.Tables[0].Rows.Count ,expectedRowsCount );
}
catch(Exception ex) {exp = ex;}
finally {cleanTableAfterTest(conn,"Employees","EmployeeID",Convert.ToInt32(maxEmployee));
EndCase(exp); exp = null;}
#endregion //Select by full table name in the same catalog
}
/// <summary>
/// ִ�ж���SQL��䣬ʵ�����ݿ�����
/// </summary>
/// <param name="SQLStringList">����SQL���</param>
public static bool ExecuteSqlTran(string conStr, List<CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:Υ��Ҫ��" + c.CommandText + "�������select count(..�ĸ�ʽ");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:Υ��Ҫ��" + c.CommandText + "����ֵ�������0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:Υ��Ҫ��" + c.CommandText + "����ֵ�������0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:Υ��Ҫ��" + c.CommandText + "������Ӱ����");
// return false;
}
}
}
tx.Commit();
return true;
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// Execute a OracleCommand (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">An array of OracleParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (transaction == null)
throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null)
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// ����/ɾ��/�� sys_Roles
/// </summary>
/// <param name="fam">sys_RolesTableʵ����</param>
/// <returns>����0������</returns>
public override int sys_RolesInsertUpdate(sys_RolesTable fam)
{
int rInt = 0;
using (OracleConnection Conn = GetSqlConnection())
{
string CommTxt;
OracleCommand cmd = new OracleCommand();
cmd.Connection = Conn;
if (fam.DB_Option_Action_ == "Insert")
{
CommTxt = "Insert into sys_Roles(RoleID,R_UserID,R_RoleName,R_Description)VALUES(SEQ_sys_Roles_ID.NEXTVAL,:R_UserID,:R_RoleName,:R_Description)";
cmd.CommandText = CommTxt;
cmd.Parameters.Add("R_UserID", OracleType.Int32).Value = fam.R_UserID; //�ǽ������û�ID
cmd.Parameters.Add("R_RoleName", OracleType.NVarChar).Value = fam.R_RoleName + ""; //��ɫ����
cmd.Parameters.Add("R_Description", OracleType.NVarChar).Value = fam.R_Description + ""; //��ɫ����
}
else if (fam.DB_Option_Action_ == "Update")
{
CommTxt = "UPDATE sys_Roles SET R_UserID = :R_UserID,R_RoleName = :R_RoleName,R_Description = :R_Description WHERE (RoleID = :RoleID)";
cmd.CommandText = CommTxt;
cmd.Parameters.Add("R_UserID", OracleType.Int32).Value = fam.R_UserID; //�ǽ������û�ID
cmd.Parameters.Add("R_RoleName", OracleType.NVarChar).Value = fam.R_RoleName + ""; //��ɫ����
cmd.Parameters.Add("R_Description", OracleType.NVarChar).Value = fam.R_Description + ""; //��ɫ����
cmd.Parameters.Add("RoleID", OracleType.Int32).Value = fam.RoleID;
}
else if (fam.DB_Option_Action_ == "Delete")
{
CommTxt = "Delete from sys_Roles WHERE (RoleID = :RoleID)";
cmd.CommandText = CommTxt;
cmd.Parameters.Add("RoleID", OracleType.Int32).Value = fam.RoleID;
}
else
throw new ApplicationException("��ʶ��IJ�������!");
Conn.Open();
OracleTransaction Tran = Conn.BeginTransaction();
cmd.Transaction = Tran;
try
{
rInt = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
if (fam.DB_Option_Action_ == "Insert")
{
cmd.CommandText = "select SEQ_sys_Roles_ID.CURRVAL from dual";
rInt = Convert.ToInt32(cmd.ExecuteScalar());
}
Tran.Commit();
}
catch (Exception ex)
{
Tran.Rollback();
throw new ApplicationException(ex.ToString());
}
finally
{
Tran.Dispose();
cmd.Dispose();
Conn.Dispose();
Conn.Close();
}
}
return rInt;
}
public int GetNextIdWithTransNew(string tableName, string columnName, System.Data.OracleClient.OracleCommand cmd)
{
cmd.CommandText = "";
cmd.CommandText = "SELECT NVL(MAX(" + columnName + "),0)+1 FROM " + tableName;
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
public override object ExecuteScalar()
{
return(cmd.ExecuteScalar());
}
/// <summary>
/// 执行select count查询，返回行数
/// </summary>
/// <param name="ConnectionString">数据库连接</param>
/// <param name="strSql">执行的SQL语句</param>
/// <returns></returns>
public static int SelectCountSql(string strSql)
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionString;
conn.Open();
OracleCommand dbCommand = new OracleCommand();
dbCommand.Connection = conn;
dbCommand.CommandText = strSql;
dbCommand.CommandType = CommandType.Text;
object obj = dbCommand.ExecuteScalar();
conn.Close();
int count = 0;
int.TryParse(obj.ToString(), out count);
return count;
}
/// <summary>
/// 执行查询，返回第一行第一列的值
/// </summary>
/// <param name="ConnectionString">数据库连接</param>
/// <param name="strSql">执行的SQL语句</param>
/// <returns></returns>
public static object SelectSql(string strSql)
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionString;
conn.Open();
OracleCommand dbCommand = new OracleCommand();
dbCommand.Connection = conn;
dbCommand.CommandText = strSql;
dbCommand.CommandType = CommandType.Text;
object obj = dbCommand.ExecuteScalar();
conn.Close();
return obj;
}
public static object RunSingle(string sqlcmd, out string errMsg)
{
errMsg = "";
string conn = OracleHelper.retStr();
using (OracleConnection connection = new OracleConnection(conn))
{
using (OracleCommand omd = new OracleCommand(sqlcmd, connection))
{
try
{
connection.Open();
object obj = omd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.OracleClient.OracleException e)
{
errMsg = e.Message;
return null;
}
finally { connection.Close(); }
}
}
}
/// <summary>
/// This method will prepare table for test
/// </summary>
private int prepareTableForTest(OracleConnection con,int recordsNumber,string baseTableName,string keyField
,params string[] otherNonNullableFieldsName)
{
string tableName = getDbObjectName(baseTableName,con);
OracleCommand cmd = new OracleCommand("select max(" + keyField + ") from " + tableName,con);
string str_ret = cmd.ExecuteScalar().ToString();
// Console.WriteLine("ExecuteScalar:" + str_ret);
// on some databases the max is on a field which is decimal
decimal maxRecord = decimal.Parse(str_ret);
int resultCount = Convert.ToInt32(maxRecord)+recordsNumber;
string sqlStmt = string.Empty;
string valueStmt = string.Empty;
//Constrcut the statemnet once : --> TODO://Move this logic to seperate method
for(int i=0;i<otherNonNullableFieldsName.Length;i++)
{
sqlStmt+= otherNonNullableFieldsName[i] + ",";
valueStmt+="'a',";
}
//Trim the last ","
if (otherNonNullableFieldsName.Length > 0)
{
sqlStmt = sqlStmt.Remove(sqlStmt.Length-1,1);
sqlStmt = "," + sqlStmt;
valueStmt = valueStmt.Remove(valueStmt.Length-1,1);
valueStmt = "," + valueStmt;
}
for (int index=Convert.ToInt32(maxRecord)+1;index<=resultCount;index++)
{
cmd.CommandText="Insert into " + tableName + " (" + keyField + sqlStmt + ") values ("
+ index + valueStmt + ")";
cmd.ExecuteNonQuery();
}
return Convert.ToInt32(maxRecord);
}
/// <summary>
///返回结果集的第一行第一列
/// </summary>
/// <remarks>
/// 例如：
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</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(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
try
{
object obj = cmd.ExecuteScalar();
if (transaction != null)
{
transaction.Commit();
}
return obj;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
return null;
}
}
// Método para manipular Insert, Update, Delete con identidad
public override void ejecutarSQL(string pSql, Object[] myParamArray, ref string pNumero)
{
limpiarEstado();
try
{
OracleCommand cmd = new OracleCommand(pSql, conexion);
cmd.CommandType = CommandType.Text;
for (int j = 0; j < myParamArray.Length; j++)
{
cmd.Parameters.Add((OracleParameter)myParamArray[j]);
}
if (this.hayTransaccion)
{
cmd.Transaction = this.transaccion;
}
pNumero = "";
pNumero = cmd.ExecuteScalar().ToString();
}
catch (OracleException error)
{
ProcesarExcepcion(error);
}
}
public int GetNextIdWithTransNewApp(string tableName, string columnName, System.Data.OracleClient.OracleCommand cmd)
{
cmd.CommandText = "";
cmd.CommandText = @"SELECT nvl(to_number(max(SUBSTR(" + columnName + ", 4, 10))),0)+1 FROM " + tableName;
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
/// <summary>
/// Execute an OracleCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Executes the SQL statement using <see cref="OracleConnection"/>, and returns the value in the first column
/// of the first row in the resultset.
/// </summary>
/// <param name="sql">The SQL statement to be executed.</param>
/// <param name="connection">The <see cref="OracleConnection"/> to use for executing the SQL statement.</param>
/// <param name="parameters">The parameters to be passed to the SQL stored procedure being executed.</param>
/// <returns>Value in the first column of the first row in the resultset.</returns>
public static object ExecuteScalar(this OracleConnection connection, string sql, params object[] parameters)
{
OracleCommand command = new OracleCommand(sql, connection);
command.PopulateParameters(parameters);
return command.ExecuteScalar();
}
/// <summary>
/// ִ��һ�������ѯ�����䣬���ز�ѯ�����object����
/// </summary>
/// <param name="SQLString">�����ѯ������</param>
/// <returns>��ѯ�����object��</returns>
public static object GetSingle(string connectionString, 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 ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
}
}
public ActionResult Show(string id)
{
string connectionString = "Data Source=HRMSDEV;user id=PERSON;password=PERSON;Unicode=True;";
using (OracleConnection connection = new OracleConnection(connectionString))
{
string sql = "Select MEMO_FILE from KMIINTRANET_MASTER_MEMO where MEMO_ID='" + id + "'";
OracleCommand cmd = new OracleCommand(sql, connection);
cmd.CommandType = CommandType.Text;
connection.Open();
object pdf = cmd.ExecuteScalar();
try
{
return File((byte[])pdf, "application/pdf");
}
catch
{
return null;
}
finally
{
connection.Close();
}
}
}
public static object RunSingle_SP(string sp_name, OracleCommand comm, out string errMsg)
{
errMsg = "";
string conn = OracleHelper.retStr();
OracleConnection connect = new OracleConnection(conn);
try
{
comm.Connection = connect;
comm.CommandText = sp_name;
comm.CommandType = CommandType.StoredProcedure;
return comm.ExecuteScalar();
}
catch (Exception e)
{
errMsg = e.Message;
return null;
}
finally
{
connect.Close();
}
}
public static object ExecuteScalar(string commandText, CommandType commandType, OracleParameter[] parameters)
{
object result = null;
OracleConnection oracleConnection = new OracleConnection(OrcaleHelper.m_connectionString);
checked
{
try
{
OracleCommand oracleCommand = new OracleCommand(commandText, oracleConnection);
try
{
oracleCommand.CommandType = commandType;
bool flag = parameters != null;
if (flag)
{
for (int i = 0; i < parameters.Length; i++)
{
OracleParameter value = parameters[i];
oracleCommand.Parameters.Add(value);
}
}
oracleConnection.Open();
result = RuntimeHelpers.GetObjectValue(oracleCommand.ExecuteScalar());
}
finally
{
bool flag = oracleCommand != null;
if (flag)
{
((IDisposable)oracleCommand).Dispose();
}
}
}
finally
{
bool flag = oracleConnection != null;
if (flag)
{
((IDisposable)oracleConnection).Dispose();
}
}
return result;
}
}
/// <summary>
/// 返回结果集的第一行第一列
/// </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)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
return cmd.ExecuteScalar();
}
/// <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(System.Data.OracleClient.OracleException e)
{
throw new Exception(e.Message);
}
}
}
}