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);
}
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");
}
}
// 使用事务时使用
public static DataSet GetDataSet(
Oracle.ManagedDataAccess.Client.OracleTransaction tran,
string commandText,
List <SqlParameter> paramsList,
CommandType argCommandType = CommandType.StoredProcedure)
{
DataSet r = new DataSet();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = tran.Connection.CreateCommand();
cmd.Transaction = tran;
cmd.CommandType = argCommandType;
cmd.CommandText = commandText;
if (paramsList != null && paramsList.Count > 0)
{
cmd.Parameters.AddRange(paramsList.ToArray());
}
OracleDataAdapter sda = new OracleDataAdapter(cmd);
sda.Fill(r);
return(r);
}
public void Order(int eq_Id, int client_Id, int empl_Id, int status_id, int maker_id, DateTime dateO)
{
OracleConnection conn = DBUtils.GetDBConnection();
try
{
conn.Open();
// MessageBox.Show("Open1");
OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "System.cwpack1.makeOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("eq_Id", OracleDbType.Int32).Value = eq_Id;
cmd.Parameters.Add("client_Id", OracleDbType.Int32).Value = client_Id;
cmd.Parameters.Add("empl_Id", OracleDbType.Int32).Value = empl_Id;
cmd.Parameters.Add("status_id", OracleDbType.Int32).Value = status_id;
cmd.Parameters.Add("dateO", OracleDbType.Date).Value = dateO;
// Выполнить процедуру.
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
MessageBox.Show(ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public void showHistoryClientOrders()
{
OracleConnection conn = DBUtils.GetDBConnection();
try
{
conn.Open();
OracleDataAdapter objAdapter = new OracleDataAdapter();
OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "System.cwpack1.showHistoryClientOrders";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("cl_id", OracleDbType.Int32).Value = int.Parse(Id.ToString());
cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
objAdapter.SelectCommand = cmd;
DataTable dtEmp = new DataTable();
objAdapter.Fill(dtEmp);
dataGridView2.DataSource = dtEmp;
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
MessageBox.Show(ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public DataTable GetHomeItemAll()
{
var paramArray = new[]
{
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cmaDb = new CMAEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
cmaDb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
spCmd.CommandText = "GetHomeItemAll";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
OracleDataAdapter da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
return(ds.Tables[0]);
}
}
}
public static List <CoalTrafficPortChartEntity> GetCoalTrafficPortData(string key)
{
const string proName = "CoalTrafficPort_ChartData";
var paras = new[] {
new OracleParameter("P_key", key),
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
var chartTable = new DataTable();
using (var cnE = new CNE_ZCXNewEntities())
{
using (var spCmd = new OracleCommand())
{
cnE.Database.Connection.Open();
spCmd.Connection = (OracleConnection)(cnE.Database.Connection);
spCmd.CommandText = proName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paras);
var da = new OracleDataAdapter(spCmd);
da.Fill(chartTable);
}
}
return(DataTableSerializer.ToList <CoalTrafficPortChartEntity>(chartTable));
}
private DataSet GetDataSetBySp(string inName, OracleParameter[] inParams)
{
using (var IPPDB = new IPPEntities())
{
using (var spCmd = new OracleCommand())
{
DataSet ds = null;
IPPDB.Database.Connection.Open();
spCmd.Connection = new OracleConnection(IPPDB.Database.Connection.ConnectionString);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParams != null)
{
spCmd.Parameters.AddRange(inParams);
}
var da = new OracleDataAdapter(spCmd);
ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
/// <summary>
/// Get data by store procedure
/// </summary>
/// <param name="inName">sp name</param>
/// <param name="inParms">parameters</param>
/// <returns></returns>
protected DataSet GetZCXDataSetBySp(string inName, OracleParameter[] inParms)
{
using (var cnEDB = new ZCXEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
DataSet ds = null;
cnEDB.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cnEDB.Database.Connection.ConnectionString);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParms != null)
{
spCmd.Parameters.AddRange(inParms);
}
OracleDataAdapter da = new OracleDataAdapter(spCmd);
ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
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();
}
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 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]);
}
/// <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 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>
/// 执行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 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 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();
}
}
public virtual async Task <List <T> > ExecuteReaderAsync <T>(CommandType cmdType,
string cmdText, IList <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[0](reader));
}
var transformCounter = transform.Count();
var transformIndex = 1;
while (transformCounter > 1)
{
if (transform.Count() > 1)
{
if (await reader.NextResultAsync())
{
while (await reader.ReadAsync())
{
myList.Add(transform[transformIndex](reader));
}
}
}
transformIndex++;
transformCounter--;
}
//}
}
}
}
return(myList);
}
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);
}
public void insert_new_address_client(Connection connection, int id_client_max, string city)
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("get_total_price1", connection._connection);
command.CommandText = "insert into addressC VALUES(:id_c,:city)";
command.Parameters.Add(new OracleParameter("id_c", id_client_max));
command.Parameters.Add(new OracleParameter("city", city));
command.ExecuteNonQuery();
connection.Close();
}
public void AdjustStock(int quantity, int product_d, Connection connection)
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("adjust_stock", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("list_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = quantity;
command.Parameters.Add("id_product", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = product_d;
command.ExecuteNonQuery();
connection.Close();
}
public DataTable GetHomeItems(int moduleId, string itemName, int startPage, int pageSize, out int total)
{
var paramArray = new[]
{
new OracleParameter("P_ModuleId", OracleDbType.Int32)
{
Value = moduleId
},
new OracleParameter("P_ItemName", OracleDbType.Varchar2)
{
Value = itemName
},
new OracleParameter("P_StartPage", OracleDbType.Int32)
{
Value = startPage
},
new OracleParameter("P_PageSize", OracleDbType.Int32)
{
Value = pageSize
},
new OracleParameter("P_Total", OracleDbType.Int32, ParameterDirection.Output),
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cmaDb = new CMAEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
cmaDb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
spCmd.CommandText = "GetHomeItems";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
OracleDataAdapter da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
object value;
value = spCmd.Parameters["P_Total"].Value;
total = value.ToString() != "null" ? Convert.ToInt32(value.ToString()) : 0;
return(ds.Tables[0]);
}
}
}
public List <string> GetBallanceColumnNames(int reportId, string areacode, string productCode)
{
var ds = new DataTable();
using (var zcxDb = new CNE_ZCXNewEntities())
{
using (var spCmd = new OracleCommand())
{
string str = "select distinct MARKET_YEAR from GetBalanceTableData where length(MARKET_YEAR)=9 ";
if (!string.IsNullOrEmpty(areacode))
{
str += " and areacode=" + areacode;
}
if (!string.IsNullOrEmpty(productCode))
{
str += " and productCode=" + productCode;
}
str += " and reportid=" + reportId + " order by MARKET_YEAR desc";
zcxDb.Database.Connection.Open();
spCmd.Connection = (OracleConnection)zcxDb.Database.Connection;
spCmd.CommandText = str;
spCmd.CommandTimeout = 0;
var da = new OracleDataAdapter(spCmd);
da.Fill(ds);
}
}
var columnNames = new List <string>();
var rowItor = ds.Rows.GetEnumerator();
while (rowItor.MoveNext())
{
string years = ((DataRow)rowItor.Current)[0].ToString();
int startYear = Convert.ToInt32(years.Substring(0, 4));
int endYear = Convert.ToInt32(years.Substring(5, 4));
if (endYear == startYear + 1)
{
columnNames.Add(years);
}
}
if (columnNames.Count == 0)
{
columnNames.Add((DateTime.Now.Year - 1) + "/" + DateTime.Now.Year);
columnNames.Add((DateTime.Now.Year - 2) + "/" + (DateTime.Now.Year - 1));
columnNames.Add((DateTime.Now.Year - 3) + "/" + (DateTime.Now.Year - 2));
}
return(columnNames);
}
/// <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();
}
}
public static System.Data.DataSet GetDataSet(string connectionString,
string commandText,
List <SqlParameter> paramsList,
bool isRollbackForTest = false,
CommandType argCommandType = CommandType.StoredProcedure
)
{
DataSet r = new DataSet();
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = null;
Oracle.ManagedDataAccess.Client.OracleTransaction tran = null;
if (isRollbackForTest == true) // 实现测试回滚
{
tran = conn.BeginTransaction();
cmd = tran.Connection.CreateCommand();
cmd.Transaction = tran;
}
else // 普通执行模式
{
cmd = conn.CreateCommand();
}
cmd.CommandType = argCommandType;
cmd.CommandText = commandText;
if (paramsList != null && paramsList.Count > 0)
{
cmd.Parameters.AddRange(paramsList.ToArray());
}
OracleDataAdapter sda = new OracleDataAdapter(cmd);
sda.Fill(r);
// 实现测试回滚
if (isRollbackForTest == true)
{
tran.Rollback();
}
}
return(r);
}
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));
}
public void Execute(OracleConnection conn, OracleTransaction tran)
{
using (OracleCommand cmd = new OracleCommand("delete from GDT_AgricultureMax", conn))
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxAgricultrueTable";
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
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 DataTable GetEnergyInvntoryData(string tableName, string columns, string order, string filter = "1=1")
{
using (var cnE = new CneNewEntities())
{
using (var spCmd = new OracleCommand())
{
var ds = new DataSet();
cnE.Database.Connection.Open();
spCmd.Connection = (OracleConnection)cnE.Database.Connection;
spCmd.CommandText = "SELECT " + columns + " FROM " + tableName + " where " + filter + " order by " + order;
spCmd.CommandTimeout = 0;
var da = new OracleDataAdapter(spCmd);
da.Fill(ds);
return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
}
}
}