public void ExecuteCneZcxExcel()
{
string zcxStr = ConfigurationManager.AppSettings["ZCXConnStr"].ToString();
using (var zcxConn = new OracleConnection(zcxStr))
{
using (OracleCommand cmd = new OracleCommand())
{
zcxConn.Open();
cmd.Connection = zcxConn;
cmd.CommandText = "sp_MergeZCXEnergyYieldData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
zcxConn.Close();
}
//chemical ver. 2.3 rewrite this class
using (OracleCommand cmd = new OracleCommand())
{
zcxConn.Open();
cmd.Connection = zcxConn;
cmd.CommandText = "sp_MergeZCXChemistryOutputData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
zcxConn.Close();
}
}
}
/// <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>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString)
{
using (OracleConnection connection = new OracleConnection(conStr))
{
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 IHttpActionResult CreateTestEntry(Process p)
{
string ConStr = Static.Secrets.ApiConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string iStr = @"INSERT INTO ifc.qmes_tpm_repairs_imp (order_nr, manager_nr, closemean_nr, initial_diagnosis, repair_actions, STATUS, IS_ADJUSTMENT, REASONCODE2, REASONCODE3)
VALUES ('{0}','{1}','{2}','{3}', '{4}', '{5}','{6}','{8}','{9}')";
iStr = string.Format(iStr, p.Number, p.Manager, p.FinishedBy, p.InitialDiagnosis, p.RepairActions, p.Status, p.IsAdjustment, p.ReasonCode2, p.ReasonCode3);
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(iStr, Con);
try
{
Command.ExecuteNonQuery();
return(Ok());
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
protected void RegisterButton_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
// Hash and salt the password using Bcyrpt before it gets sent to the database
var saltedPassword = BCryptHelper.HashPassword(password.Text, Global.Salt);
using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
{
// Declare the stored procedure to execute and send it the parameters it needs
OracleCommand objCmd = new OracleCommand("tickets_api.insertGuest", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
objCmd.Parameters.Add("p_FirstName", OracleDbType.Varchar2, first_name.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_LastName", OracleDbType.Varchar2, last_name.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_Email", OracleDbType.Varchar2, email.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_Password", OracleDbType.Varchar2, saltedPassword, ParameterDirection.Input);
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception)
{
labelMessage.Text = "Could not register. Try again later.";
}
objConn.Close();
}
if (string.IsNullOrEmpty(labelMessage.Text))
Response.Redirect("Login.aspx");
}
}
//string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.130.23)(PORT=8903))(CONNECT_DATA=(SERVICE_NAME = settle_primary)));User Id=settle;Password=settle;";
static void Main123(string[] args)
{
ArrayList arr = GetAttId1();
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
for (int i = 0; i < arr.Count; i++)
{
double ds = 0;
int total = 1;
for (int j = 0; j < total; j++)
{
DateTime startDate = DateTime.Now;
OracleParameter[] oracleParameters = new OracleParameter[1];
oracleParameters[0] = new OracleParameter("p_attachmentId", arr[i]);
OracleCommand cmd = new OracleCommand("SP_CHECKCINEMA_MT_BYATTID", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(oracleParameters);
cmd.CommandTimeout = 1800;
cmd.ExecuteNonQuery();
DateTime endDate = DateTime.Now;
ds += (endDate - startDate).TotalSeconds;
}
Console.WriteLine(arr[i] + "：" + ds / total);
}
conn.Close();
}
Console.ReadLine();
}
public List <Estudiante> ObtenerTodosEstudiantes()
{
List <Estudiante> listaEstudiantes = new List <Estudiante>();
try
{
using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(_connection))
{
using (var command = new OracleCommand())
{
conn.Open();
command.BindByName = true;
command.CommandText = "Select * from Estudiante";
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var estudiante = _mapper.Map <Estudiante>(reader);
listaEstudiantes.Add(estudiante);
}
}
}
}
catch (Exception ex)
{
}
return(listaEstudiantes);
}
public int LoginUsingPassword(string username, string password)
{
var result = 0;
var userNameParam = new OracleParameter("USERNAME", OracleDbType.Varchar2, username, ParameterDirection.Input);
var userPassParam = new OracleParameter("PASSWORD", OracleDbType.Varchar2, password, ParameterDirection.Input);
var resultPassParam = new OracleParameter("result_tab", OracleDbType.Int32, ParameterDirection.ReturnValue);
using (OracleConnection connection = new OracleConnection(_connectionString))
{
OracleCommand cmd = new OracleCommand("LOGINFUNC", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.Parameters.Add(resultPassParam);
cmd.Parameters.Add(userNameParam);
cmd.Parameters.Add(userPassParam);
connection.Open();
cmd.ExecuteNonQuery();
var res = cmd.Parameters["result_tab"].Value.ToString();
result = Convert.ToInt32(res);
connection.Close();
// context.SaveChanges();
}
return(result);
}
public virtual List <T> ExecuteReader <T>(CommandType cmdType,
string cmdText, System.Func <IDataReader, T> transform, params OracleParameter[] commandParameters)
{
var myList = new List <T>();
using (var connection = new OracleConnection(ConnectionString))
{
connection.Open();
using (var command = new OracleCommand(cmdText, connection))
{
command.CommandType = cmdType;
if (commandParameters != null)
{
command.Parameters.AddRange(commandParameters);
}
// Since none of the rows are likely to be large, we will execute this without specifying a CommandBehavior
// This will cause the default (non-sequential) access mode to be used
using (var reader = command.ExecuteReader())
{
//if (reader.Read())
//{
while (reader.Read())
{
myList.Add(transform(reader));
}
//}
}
}
}
return(myList);
}
public void ImportDataTable(Cells c)
{
DataTable tableData = ImportWorkSheet(c);
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmdText = "INSERT INTO Cofeed VALUES( ";
for (var i = 0; i < tableData.Columns.Count; i++)
{
cmdText += ":v" + i + ",";
}
cmdText += "sysdate,null)";
var cmd = new OracleCommand(cmdText, con);
for (var j = 0; j < tableData.Rows.Count; j++)
{
cmd.Parameters.Clear();
for (var i = 0; i < tableData.Columns.Count; i++)
{
var par = new OracleParameter("v" + i, OracleDbType.NVarchar2)
{
Direction = ParameterDirection.Input,
Value = tableData.Rows[j][i].ToString()
};
cmd.Parameters.Add(par);
}
cmd.ExecuteNonQuery();
}
con.Close();
}
}
public void ImportDataTable(Cells c, out int total)
{
DataTable datatable = ImportWorkSheet(c);
total = datatable.Rows.Count;
using (OracleConnection conn = new OracleConnection(Connectionstr))
{
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{
foreach (DataRow dr in datatable.Rows)
{
string sql1 = CreateInsertSql(dr);
using (OracleCommand cmd = new OracleCommand(sql1, conn))
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
conn.Close();
throw;
}
}
}
public DateTime GetMaxStartTime()
{
object o = null;
using (OracleConnection conn = new OracleConnection(Connectionstr))
{
try
{
conn.Open();
string sql = "select max(startTime ) as MaxTime from CofeedProduct";
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
o = cmd.ExecuteScalar();
}
}
catch
{
// ignored
}
finally
{
conn.Close();
}
}
return(Convert.ToDateTime(o));
}
public int viewTroops()
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DbConnect.oradb);
String strSQL = "SELECT * FROM V_PlayerTroops";
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(strSQL, conn);
Oracle.ManagedDataAccess.Client.OracleDataReader dr = cmd.ExecuteReader();
//read the record in dr
dr.Read();
if (dr.IsDBNull(0))
{
playerTroops = 1;
}
else
{
playerTroops = Convert.ToInt16(dr.GetValue(0)) + 1;
}
conn.Close();
return(playerTroops);
}
/// <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;
}
}
public IHttpActionResult GetMachine(int Id)
{
try
{
if (RuntimeSettings.MockServer)
{
string status = Id % 2 == 0 ? "PR" : "ST";
Machine machine = new Machine {
Id = Id, Name = $"Linia {Id}", State = status, Type = 3, VisibleInAPS = true
};
return(Ok(machine));
}
else
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = $@"SELECT MACHINE_ID, MACHINE_NR, STATE, MACHINE_TYPE_ID, IS_VISIBLE_APS
FROM QMES_FO_MACHINE
WHERE MACHINE_ID = {Id}";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
if (reader.HasRows)
{
Machine m = new Machine();
while (reader.Read())
{
m.Id = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_ID")].ToString());
m.Name = reader[reader.GetOrdinal("MACHINE_NR")].ToString();
m.State = reader[reader.GetOrdinal("STATE")].ToString();
m.Type = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_TYPE_ID")].ToString());
m.VisibleInAPS = reader[reader.GetOrdinal("IS_VISIBLE_APS")].ToString() == "T" ? true : false;
}
Logger.Info("GetMachines: Sukces, zwracam maszynę {Id}", m.Id);
return(Ok(m));
}
else
{
Logger.Info("GetMachines: Porażka, nie znaleziono maszyny {Id}", Id);
return(NotFound());
}
}
}
}
catch (Exception ex)
{
Logger.Error("Błąd w GetMachine. ID:{Id}, Szczegóły: {Message}", Id, ex.ToString());
return(InternalServerError(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>
/// 执行sql执行增删改
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <param name="oracleParameters">所传参数（必须按照存储过程参数顺序）</param>
/// <param name="strConn">链接字符串</param>
/// <returns></returns>
public static int ExecToSqlNonQuery(string cmdText)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
OracleCommand cmd = new OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
return(result);
}
}
public void ExecuteMax()
{
try
{
using (conn = new OracleConnection(ConfigurationManager.AppSettings["mergeData"]))
{
conn.Open();
tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxChemicalIndustryTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyGasTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyOilTable";
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
catch (Exception e)
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
private async Task <List <Operation2Product> > _GetOperation2Product(string operationNumbers)
{
try
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = $@"SELECT DISTINCT op.OPERATION_ID, op.OPERATION_NR, pr.PRODUCT_ID, pr.PRODUCT_NR, pr.SUB_PROD_TYPE
FROM QMES_WIP_OPERATION op LEFT OUTER JOIN
QMES_WIP_ORDER2PRODUCT o2p ON o2p.OPERATION_ID = op.OPERATION_ID LEFT OUTER JOIN
QCM_PRODUCTS pr ON pr.PRODUCT_ID = o2p.PRODUCT_ID
WHERE (op.OPERATION_NR IN ({operationNumbers})) AND (pr.SUB_PROD_TYPE IN ('WR', 'PP'))
ORDER BY op.OPERATION_ID, pr.SUB_PROD_TYPE DESC";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
List <Operation2Product> Items = new List <Operation2Product>();
if (reader.HasRows)
{
while (reader.Read())
{
Operation2Product o = new Operation2Product();
o.OPERATION_ID = Convert.ToInt32(reader["OPERATION_ID"].ToString());
o.OPERATION_NR = reader["OPERATION_NR"].ToString();
o.PRODUCT_ID = Convert.ToInt32(reader["PRODUCT_ID"].ToString());
o.PRODUCT_NR = reader["PRODUCT_NR"].ToString();
o.SUB_PROD_TYPE = reader["SUB_PROD_TYPE"].ToString();
Items.Add(o);
}
}
else
{
}
return(Items);
}
}
catch (Exception ex)
{
throw;
}
}
public string Add()
{
string _Result = "OK";
string ConStr = Static.Secrets.OracleConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string iStr = @"INSERT INTO QMES_WIP_ORDER (ORDER_ID, ORDER_NR, NAME, DESCRIPTION_LONG, ORDER_TYPE_ID, SCHEDULED_START_DATE, SCHEDULED_END_DATE, MACHINE_ID, STATUS, C_USER, C_DATE, LM_USER, LM_DATE, TYPE_STATE_ID)
VALUES (:TheId, :TheNumber, :TheName, :Description, :TypeId, :ScheduledStart, :ScheduledFinish, :MachineId, :Status, :CreatedBy, :CreatedOn, :LmBy, :LmOn, :StateId)";
try
{
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(iStr, Con);
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("TheId", 1051275),
new OracleParameter("TheNumber", this.Number),
new OracleParameter("TheName", this.Name),
new OracleParameter("Description", this.Description),
new OracleParameter("TypeId", (int)this.Type),
new OracleParameter("ScheduledStart", this.ScheduledStartDate),
new OracleParameter("ScheduledFinish", this.ScheduledFinishDate),
new OracleParameter("MachineId", this.Machine.MesId),
new OracleParameter("Status", "OK"),
new OracleParameter("CreatedBy", 247),
new OracleParameter("CreatedOn", DateTime.Now),
new OracleParameter("LmBy", 247),
new OracleParameter("LmOn", DateTime.Now),
new OracleParameter("StateId", 10)
};
Command.Parameters.AddRange(parameters);
//OracleParameter outputParameter = new OracleParameter("returnedId", OracleDbType.Decimal);
//outputParameter.Direction = System.Data.ParameterDirection.Output;
//Command.Parameters.Add(outputParameter);
Command.ExecuteNonQuery();
//decimal id = Convert.ToDecimal(outputParameter.Value);
}catch (Exception ex)
{
_Result = $"Error: {ex.Message}";
}
return(_Result);
}
private void Connecter()
{
try
{
string dSource =
"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 205.237.244.251)(PORT = 1521)) (CONNECT_DATA =(SERVICE_NAME = orcl.clg.qc.ca)))";
string bd = "Data Source = " + dSource + "; User Id =" + "paymente" + "; Password = "******"ORACLE1";
conn.ConnectionString = bd;
conn.Open();
// MessageBox.Show(conn.State.ToString());
}
catch (Exception se)
{
MessageBox.Show(se.Message);
}
}
/// <summary>
/// 执行存储过程没有返回值
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="outParameters">参数</param>
/// <param name="oracleParameters">所传参数（必须按照存储过程参数顺序）</param>
/// <param name="strConn">链接字符串</param>
/// <returns></returns>
public static void ExecToStoredProcedure(string cmdText, OracleParameter[] oracleParameters, string strConn)
{
using (OracleConnection conn = new OracleConnection(strConn))
{
OracleCommand cmd = new OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(oracleParameters);
cmd.CommandTimeout = 1800;
conn.Open();
Console.WriteLine("3：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff"));
cmd.ExecuteNonQuery();
Console.WriteLine("4：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff"));
Console.WriteLine(cmd.Parameters["p_returnvalue"].Value.ToString());
conn.Close();
}
}
private DateTime GetMaxUnxi()
{
object o;
using (OracleConnection conn = new OracleConnection(ConfigurationManager.AppSettings["CnECon"]))
{
using (OracleCommand cmd = new OracleCommand("select max(updateDate) from " + _tablename, conn))
{
conn.Open();
o = cmd.ExecuteScalar();
conn.Close();
}
}
return(DateTime.Parse(o.ToString()));
}
public void reserver(int idclient, int idchambre, DateTime debut, DateTime fin)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = "pr_reservation";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("leclient", OracleDbType.Int32).Value = idclient;
cmd.Parameters.Add("lachambre", OracleDbType.Int32).Value = idchambre;
cmd.Parameters.Add("ledebut", OracleDbType.Date).Value = debut;
cmd.Parameters.Add("lafin", OracleDbType.Date).Value = fin;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
}
public static void ExecCinema_MT(int attid)
{
Console.WriteLine(DateTime.Now.ToString(_formatString));
//OracleConnection conn, OracleTransaction tran
string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME = orcl)));User Id=settle;Password=settle;";
OracleConnection conn = new OracleConnection(conStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
var paramArray = new[]
{
new OracleParameter("@p_attachmentId", OracleDbType.Int32)
{
Value = attid
},
//new OracleParameter("@QueryDateType", OracleDbType.Varchar2) { Value = queryDateType },
//new OracleParameter("@OrgType", OracleDbType.Varchar2) { Value = orgType },
//new OracleParameter("@TrustType", OracleDbType.NVarchar2) { Value = trustType },
// new OracleParameter("@Total", OracleDbType.Int32,ParameterDirection.Output),
//new OracleParameter("@cur",OracleDbType.RefCursor,ParameterDirection.Output)
};
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_CHECKCinema_MT_BYattID";
cmd.CommandTimeout = 90;
cmd.Parameters.AddRange(paramArray);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
tran.Commit();
}
}
catch (Exception ee)
{
tran.Rollback();
Console.WriteLine(ee.Message);
}
finally {
conn.Close();
}
Console.WriteLine(DateTime.Now.ToString(_formatString));
}
/// <summary>
/// 执行语句，不带返回值
/// </summary>
/// <param name="cmdStr"></param>
public static void ExecuteSqlNonQuery(string cmdStr)
{
using (OraDBContext db = new OraDBContext())
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection();
conn = (OracleConnection)db.Database.Connection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdStr;
//执行
cmd.ExecuteNonQuery();
}
}
public void ExecuteInsertSql(string tableName, DataTable tableData, string minReDate, int paraCount, bool isHasHistoryValue = false)
{
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmd = new OracleCommand("delete " + tableName + " where RE_DATE>='" + minReDate + "'", con);
cmd.ExecuteNonQuery();
con.Close();
}
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmdText = "INSERT INTO " + tableName + " VALUES( ";
for (var i = 0; i < paraCount; i++)
{
cmdText += ":v" + i + ",";
}
if (isHasHistoryValue)
{
cmdText += "sysdate,null)";
}
else
{
cmdText += "sysdate)";
}
var cmd = new OracleCommand(cmdText, con);
for (var j = 0; j < tableData.Rows.Count; j++)
{
cmd.Parameters.Clear();
for (var i = 0; i < paraCount; i++)
{
var par = new OracleParameter("v" + i, OracleDbType.NVarchar2)
{
Direction = ParameterDirection.Input,
Value = tableData.Rows[j][i].ToString()
};
cmd.Parameters.Add(par);
}
cmd.ExecuteNonQuery();
}
con.Close();
}
}
/// <summary>
/// 通过EF执行sql语句获得Table
/// </summary>
/// <param name="db">数据库上下文</param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static DataTable SqlQueryForDataTatable(string sql)
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection();
conn.ConnectionString = connectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
conn.Close();
return(table);
}
/// <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;
}
}
public IHttpActionResult CreateTpmEntry(Process p)
{
string ConStr = Static.Secrets.ApiConnectionString;
try
{
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string iStr = @"INSERT INTO ifc.qmes_tpm_repairs_imp (order_nr, manager_nr, start_date, closemean_nr, end_date, initial_diagnosis, repair_actions, STATUS, IS_ADJUSTMENT, REASONCODE2, REASONCODE3)
VALUES (:TheNumber, :Manager, :StartDate, :FinishedBy, :EndDate, :InitialDiagnosis, :RepairActions, :Status, :IS_ADJUSTMENT, :REASONCODE2, :REASONCODE3)";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(iStr, Con);
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("TheNumber", p.Number),
new OracleParameter("Manager", p.Manager),
new OracleParameter("StartDate", p.StartDate),
new OracleParameter("FinishedBy", p.FinishedBy),
new OracleParameter("EndDate", p.EndDate),
new OracleParameter("InitialDiagnosis", p.InitialDiagnosis),
new OracleParameter("RepairActions", p.RepairActions),
new OracleParameter("Status", p.Status),
new OracleParameter("IS_ADJUSTMENT", p.IsAdjustment),
new OracleParameter("REASONCODE2", p.ReasonCode2),
new OracleParameter("REASONCODE3", p.ReasonCode3)
};
Command.Parameters.AddRange(parameters);
Command.ExecuteNonQuery();
return(Ok());
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public IHttpActionResult GetEntry(string id)
{
string ConStr = Static.Secrets.ApiConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = string.Format("SELECT * FROM ifc.qmes_tpm_repairs_imp WHERE order_nr = '{0}'", id);
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
Process p = new Process();
if (reader.HasRows)
{
while (reader.Read())
{
p = new Process();
p.Number = reader[reader.GetOrdinal("order_nr")].ToString();
p.Manager = reader[reader.GetOrdinal("manager_nr")].ToString();
p.StartDate = null;
p.EndDate = null;
p.FinishedBy = reader[reader.GetOrdinal("closemean_nr")].ToString();
p.InitialDiagnosis = reader[reader.GetOrdinal("initial_diagnosis")].ToString();
p.RepairActions = reader[reader.GetOrdinal("repair_actions")].ToString();
p.Status = reader[reader.GetOrdinal("STATUS")].ToString();
p.IsAdjustment = reader[reader.GetOrdinal("IS_ADJUSTMENT")].ToString();
p.ReasonCode2 = reader[reader.GetOrdinal("REASONCODE2")].ToString();
p.ReasonCode3 = reader[reader.GetOrdinal("REASONCODE3")].ToString();
}
return(Ok(p));
}
else
{
return(NotFound());
}
}
