public AjouterForm(Oracle.ManagedDataAccess.Client.OracleConnection connection, DataSet dataSet, DataGridView dgv)
{
InitializeComponent();
conn = connection;
data = dataSet;
dataGridView = dgv;
}
public IHttpActionResult GetRecentProducts(int MachineId)
{
try
{
List <ProductionRecord> Records = new List <ProductionRecord>();
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
using (var reader = Utilities.GetRecentProductData(MachineId, Con))
{
while (reader.Read())
{
ProductionRecord pr = new ProductionRecord();
pr.ProductId = Convert.ToInt32(reader["PRODUCT_ID"].ToString());
pr.ProductNumber = reader["PRODUCT_NR"].ToString();
pr.StartedOn = Convert.ToDateTime(reader["STARTED_DATE"].ToString());
pr.FinishedOn = Convert.ToDateTime(reader["FINISHED_DATE"].ToString());
pr.Quantity = Convert.ToDouble(reader["QUANTITY"].ToString());
pr.QuantityKg = Convert.ToDouble(reader["QUANTITY_KG"].ToString());
Records.Add(pr);
}
}
return(Ok(Records));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
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 void insertData(string sql)
{
/*using (OracleConnection connection = new OracleConnection(connectionString))
* using (OracleCommand command = new OracleCommand("p", connection))
* {
* command.Parameters.Add(new OracleParameter("PRIX", 50));
* command.Parameters.Add(new OracleParameter("IDDORTOIR", 1));
*
* command.Connection.Open();
* command.ExecuteNonQuery();
* command.Connection.Close();
* }*/
OracleConnection connection = new OracleConnection(connectionString);
//GIVE PROCEDURE NAME
OracleCommand command = new OracleCommand("p", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new OracleParameter("PRIX", 5000));
command.Parameters.Add(new OracleParameter("IDDORTOIR", 1));
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
}
/// <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;
}
}
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");
}
}
/// <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;
}
}
//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 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 virtual async Task <List <T> > ExecuteReaderAsync <T>(CommandType cmdType,
string cmdText, Func <IDataReader, T> transform, params OracleParameter[] commandParameters)
{
var myList = new List <T>();
using (var connection = new OracleConnection(ConnectionString))
{
await connection.OpenAsync();
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 = await command.ExecuteReaderAsync())
{
//if (await reader.ReadAsync())
//{
while (await reader.ReadAsync())
{
myList.Add(transform(reader));
}
//}
}
}
}
return(myList);
}
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;
}
}
}
/// <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));
}
}
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);
}
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 Modifier(Oracle.ManagedDataAccess.Client.OracleConnection connection, int num, DataSet dataSet, DataGridView dgv)
{
InitializeComponent();
conn = connection;
numId = num;
data = dataSet;
dataGridView = dgv;
}
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));
}
}
public DataSet getChambres()
{
DataSet dataset = new DataSet();
OracleConnection myBD = new OracleConnection(connectionString);
dataset = GetSelectProc("pr_get_chambres");
return(dataset);
}
private OracleConnection GetConnection()
{
var connectionString = configuration.GetSection("ConnectionStrings").GetSection("UserConnection").Value;
_logger.LogDebug("create connection" + connectionString);
var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
_logger.LogInformation("oracle connection created.");
return(conn);
}
public void FillData(DataTable tb, string str, int language)
{
Regex reg = new Regex(@"<tr>([\w\s\S]+?)</tr>");
OracleConnection conn = new OracleConnection(connstr);
try
{
if (reg.IsMatch(str))
{
MatchCollection coll = reg.Matches(str);
for (int i = 1; i < coll.Count; i++)
{
string tr = coll[i].Value;
tr = tr.Replace("<td class=\"spec\">", "@").Replace("<td>", "@").Replace("</tr>", "");
tr = new Regex(@"</td>\s+").Replace(tr, "@");
tr = new Regex(@"<tr>\s+").Replace(tr, "");
string[] strArray = tr.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);
// datatype, dataname, unit, figure, updateDate, fetchTime
DataRow dr = tb.NewRow();
dr["id"] = strArray[0].Replace(" ", "");
string code = getCode(1, i, conn);
if (string.IsNullOrEmpty(code))
{
code = strArray[0].Replace(" ", "");
}
dr["code"] = code;
dr["datatype"] = strArray[1].Replace(" ", "");
dr["dataname"] = strArray[2].Replace(" ", "");
dr["priceDate"] = strArray[3].Replace(" ", "");
dr["unit"] = strArray[4].Replace(" ", "");
dr["figure"] = strArray[5].Replace(" ", "");
dr["updateDate"] = strArray[6].Replace(" ", "").Replace("/", "-");
//dr["fetchTime"] = (long)(DateTime.Now - new DateTime(1970, 1, 1)).TotalMilliseconds;
dr["fetchTime"] = DateTime.Now.ToString("dd-MMM-yyyy hh:mm:ss tt");
dr["Lanuage"] = language;
//if (Convert.ToDateTime (dr["updateDate"])!= DateTime.Now.Date )
//{
// continue;
//}
tb.Rows.Add(dr);
}
}
}
catch (Exception e)
{
}
finally
{
conn.Close();
}
}
public static void OpenDb(IDbConnection Db)
{
if (Db == null)
{
Db = new Oracle.ManagedDataAccess.Client.OracleConnection(Datos.connectionString);
}
if (Db.State == ConnectionState.Closed)
{
Db.Open();
}
}
public static DataTable GetDataTableBySqlSettle(string conStr, string sql)
{
DataSet ds = new DataSet();
OracleConnection con = new OracleConnection(conStr);
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
return(ds.Tables[0]);
}
public static DataTable GetDataTableBySql2(string sql)
{
DataSet ds = new DataSet();
//string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=124.207.105.120)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME = orcl11g.us.oracle.com)));User Id=settle;Password=settle;";
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;";
OracleConnection con = new OracleConnection(conStr);
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
return(ds.Tables[0]);
}
public void ExecuteMetals(OracleConnection conn, OracleTransaction tran)
{
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxMetalTable";
cmd.CommandTimeout = 300;
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
///--------------------------------------------------------------------------
/// <summary>
/// 楽商用受入データ作成クラスコンストラクタ</summary>
/// <param name="preFrm">
/// 親フォーム</param>
/// <param name="dtsC">
/// NHBR_CLIDataSet</param>
/// <param name="_dts">
/// NHBRDataSet:データセットオブジェクト</param>
/// <param name="Conn">
/// Oracle.ManagedDataAccess.Client.OracleConnection : 接続情報</param>
/// <param name="myCode">
/// 担当者コード</param>
///--------------------------------------------------------------------------
public OCROutput(Form preFrm, NHBR_CLIDataSet dtsC, NHBRDataSet _dts, Oracle.ManagedDataAccess.Client.OracleConnection Conn, string myCode)
{
_preForm = preFrm;
_dtsC = dtsC;
dts = _dts;
_hTbl = dtsC.FAX注文書;
_pTbl = _dts.パターンID;
_Conn = Conn;
_myCode = myCode;
cnfAdp.Fill(dts.環境設定);
}
/// <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);
}
}
/// <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;
}
}
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);
}
/// <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();
}
}
/// <summary>
/// OuvrirConnection: Ouvre la connection à ka vase de dibb.es
/// en utilisant ODP. Peut retourner des exceptions.
/// </summary>
public void OuvrirConnection()
{
if (maConnection == null )
maConnection = new Oracle.ManagedDataAccess.Client.OracleConnection(CreerConnectionString());
maConnection.Open();
ConnectionOuverte = true;
}