public static User GetUser(string username)
{
User user = null;
if (username != null)
{
try
{
using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
{
OracleCommand command = new OracleCommand();
command.CommandText = "SELECT password,customerId,securityQuestion,securityAnswer,email FROM Users WHERE username LIKE :username";
command.Parameters.Add(":username", OracleDbType.NVarchar2).Value = username;
command.Connection = connection;
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string password = reader["password"].ToString();
string customerId = reader["customerId"].ToString();
string securityQuestion = reader["securityQuestion"].ToString();
string securityAnswer = reader["securityAnswer"].ToString();
string email = reader["email"].ToString();
user = new User(username, password, customerId, securityQuestion, securityAnswer, email);
}
}
}
catch (Exception e)
{
user = null;
Logger.LogException(e);
}
}
return user;
}
public List <Store> getStores()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT STORE.* FROM STORE"
};
var stores = new List <Store>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
stores.Add(new Store()
{
StoreID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("STOREID"))),
StoreName = reader.GetString(reader.GetOrdinal("STORENAME")),
StoreURL = reader.GetString(reader.GetOrdinal("STOREURL")),
PhoneNumber = reader.GetString(reader.GetOrdinal("PHONENUMBER")),
Kosher = reader.GetString(reader.GetOrdinal("KOSHER")) == "T",
});
}
return(stores);
}
public bool new_customers(int id)
{
var con = new OracleConnection(constr);
con.Open();
var objCmd = new OracleCommand
{
Connection = con,
CommandText = "Tables.new_customers",
CommandType = CommandType.StoredProcedure
};
var prm = new OracleParameter
{
Direction = ParameterDirection.ReturnValue,
DbType = DbType.Int64
};
objCmd.Parameters.Add(prm);
objCmd.Parameters.Add("new_cust", OracleDbType.Int64).Direction = ParameterDirection.Input;
objCmd.Parameters["new_cust"].Value = id;
objCmd.ExecuteNonQuery();
return(Convert.ToInt32(objCmd.Parameters[0].Value) == 1);
}
public List <Pizza> getPizzas()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT Pizza.* FROM Pizza"
};
var pizzas = new List <Pizza>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
pizzas.Add(new Pizza
{
BaseID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("BASEID"))),
OrderID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("ORDERID"))),
ToppingIdL = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPPINGIDL"))),
ToppingIdR = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPPINGIDR"))),
PizzaID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("PIZZAID")))
});
}
return(pizzas);
}
public List <Order> getOrders()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT ORDERS.* FROM ORDERS"
};
var orders = new List <Order>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
orders.Add(new Order
{
OrderID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("ORDERID"))),
OrderDeliv = reader.GetString(reader.GetOrdinal("ORDERDELIV")) == "1",
CustID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTID"))),
StoreID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("STOREID")))
});
}
return(orders);
}
public List <Employee> getEmployees()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT EMPLOYEE.* FROM EMPLOYEE"
};
var employees = new List <Employee>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
employees.Add(new Employee
{
EmployeeID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("EMPLOYEEID"))),
EmployeeName = reader.GetString(reader.GetOrdinal("EMPLOYEENAME")),
Bod = reader.GetDateTime(reader.GetOrdinal("BOD")),
ShomerShabat = reader.GetString(reader.GetOrdinal("SHOMERSHABAT")) == "1",
StoreID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("STOREID"))),
RankID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKID")))
});
}
return(employees);
}
public List <Customer> getCustomers()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT CUSTOMERS.* FROM CUSTOMERS"
};
var customers = new List <Customer>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
customers.Add(new Customer
{
CustID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTID"))),
CustName = reader.GetString(reader.GetOrdinal("CUSTNAME")),
CustAddress = reader.GetString(reader.GetOrdinal("CUSTADDRESS")),
CustCc = reader.GetString(reader.GetOrdinal("CUSTCC")),
CustCredit = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTCREDIT")))
});
}
return(customers);
}
public bool Limpeza()
{
bool ret = false;
try
{
using (OracleConnection connection = new OracleConnection(RetornoCIP.CNX_C3))
{
OracleCommand command = new OracleCommand("TRUNCATE TABLE CCCTBLBRE", connection);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
if (Utils._logger != null)
Utils._logger.Info("Limpeza ok");
ret = true;
}
}
catch (Exception ex)
{
if (Utils._logger != null)
Utils._logger.Error(ex.Message);
}
return ret;
}
public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ASSET_ID", this.ASSET_ID);
if ((START_DATEIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "START_DATE", this.START_DATE);
}
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ITEM_ID", this.ITEM_ID);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "PACKAGE_NAME", this.PACKAGE_NAME);
if ((AMOUNTIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "AMOUNT", this.AMOUNT);
}
if ((ITEM_NOIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ITEM_NO", this.ITEM_NO);
}
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ITEM_NAME", this.ITEM_NAME);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "OFFER_ID", this.OFFER_ID);
if ((DURATIONIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "DURATION", this.DURATION);
}
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "PACKAGE_ID", this.PACKAGE_ID);
if ((PK_NOIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "PK_NO", this.PK_NO);
}
if ((QUOTAIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "QUOTA", this.QUOTA);
}
}
/// <summary>
/// 针对包含Blob字段的特殊处理
/// 【由于使用微软企业库导致使用了Blob字段的存储过程报错：不能将System.Byte[] 绑定到 Blob。。。。，所以这里要对含有Blob字段的存储过程做特殊处理】
/// </summary>
/// <param name="db"></param>
/// <param name="command"></param>
/// <param name="rowsAffected">受影响的行数</param>
/// <returns></returns>
public bool ExecuteContainBlob(Database db, IDbCommand command, out int rowsAffected)
{
bool isContainBlob = false;
rowsAffected = 0;
if (db is OracleDatabase)
{
for (int i = 0; i < command.Parameters.Count; i++)
{
if (((OracleParameter)command.Parameters[i]).OracleType.ToString() == "Blob")
{
isContainBlob = true;
break;
}
}
if (isContainBlob && command.CommandType == CommandType.StoredProcedure)
{
//OracleDataAccessClient.OracleConnection conn = new OracleDataAccessClient.OracleConnection(db.ConnectionString);
//conn.Open();
using (OracleDataAccessClient.OracleConnection conn = new OracleDataAccessClient.OracleConnection(db.ConnectionString))
{
OracleDataAccessClient.OracleCommand commandNew = new OracleDataAccessClient.OracleCommand(command.CommandText, conn);
commandNew.CommandType = CommandType.StoredProcedure;
CreateOracleParameter(command, commandNew);
rowsAffected = commandNew.ExecuteNonQuery();
//commandNew.Dispose();
//conn.Close();
}
}
}
return(isContainBlob);
}
private bool ColumnIsBoolean(string nameOwner, string nameTable, string nameColumn)
{
List <string> listIndicateColumns = new List <string>();
string strSQL = DistinctsValuesQuery(nameOwner, nameTable, nameColumn);
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionDTOP();
conn.Open();
try
{
OracleCommand cmd = new OracleCommand(strSQL, conn);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
listIndicateColumns.Add(dr[0].ToString());
}
if (listIndicateColumns.Count <= 2 && (listIndicateColumns.Contains("S") || listIndicateColumns.Contains("N")))
{
return(true);
}
return(false);
}
finally { conn.Close(); }
}
/// <summary>
/// 将数据读取到 DataSet 中.
/// </summary>
public void ReadDataToDataSet()
{
Console.WriteLine("使用DataAdapter，将数据填充到DataSet中，然后脱离数据库，直接对DataSet进行处理。");
// 建立数据库连接.
OracleConnection conn = new OracleConnection(connString);
// 创建一个适配器
OracleDataAdapter adapter = new OracleDataAdapter(SQL, conn);
// 创建DataSet，用于存储数据.
DataSet testDataSet = new DataSet();
// 执行查询，并将数据导入DataSet.
adapter.Fill(testDataSet, "result_data");
// 关闭数据库连接.
conn.Close();
// 处理DataSet中的每一行数据.
foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("NO:{0} ; Date:{1} ; Money:{2} ",
testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"]
);
}
}
public void addPizza(Pizza d)
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "insert into pizza values (:BASEID, :ORDERID, :TOPPINGIDL, :TOPPINGIDR, :PIZZAID)"
};
cmd.Parameters.Add("BASEID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["BASEID"].Value = d.BaseID;
cmd.Parameters.Add("ORDERID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["ORDERID"].Value = d.OrderID;
cmd.Parameters.Add("TOPPINGIDL", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["TOPPINGIDL"].Value = d.ToppingIdL;
cmd.Parameters.Add("TOPPINGIDR", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["TOPPINGIDR"].Value = d.ToppingIdR;
cmd.Parameters.Add("PIZZAID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["PIZZAID"].Value = d.PizzaID;
cmd.ExecuteNonQuery();
DataSource.pizzas = getPizzas();
}
public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "SRC_NO", this.SRC_NO);
if ((PURCHASE_DATEIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "PURCHASE_DATE", this.PURCHASE_DATE);
}
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "SERIAL_ID", this.SERIAL_ID);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "PAY_TYPE", this.PAY_TYPE);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "SRC_CODE", this.SRC_CODE);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CARD_EXPIRY", this.CARD_EXPIRY);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CVC2", this.CVC2);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "MAS_NO", this.MAS_NO);
if ((PK_NOIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "PK_NO", this.PK_NO);
}
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "APPROVAL_CODE", this.APPROVAL_CODE);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "SRC_DATE", this.SRC_DATE);
if ((MAS_DATEIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "MAS_DATE", this.MAS_DATE);
}
if ((SERIAL_NOIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "SERIAL_NO", this.SERIAL_NO);
}
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "DETAILS", this.DETAILS);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "MAS_CODE", this.MAS_CODE);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CARD_TYPE", this.CARD_TYPE);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CARD_NO", this.CARD_NO);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "STATUS_FLG", this.STATUS_FLG);
}
/// <summary>
/// 根据配置文件中所配置的数据库类型和传入的
/// 数据库链接字符串来创建相应数据库连接对象
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
public static DbConnection CreateDbConnection(string connectionString)
{
DbConnection conn = null;
switch (DbHelper.DbType)
{
case DatabaseType.SqlServer:
conn = new SqlConnection(connectionString);
break;
case DatabaseType.Oracle:
conn = new OracleConnection(connectionString);
break;
case DatabaseType.MySql:
conn = new MySqlConnection(connectionString);
break;
case DatabaseType.Access:
conn = new OleDbConnection(connectionString);
break;
case DatabaseType.SQLite:
conn = new SQLiteConnection(connectionString);
break;
default:
throw new Exception("数据库类型目前不支持！");
}
return conn;
}
public Oracle.DataAccess.Client.OracleConnection RetornaCnx()
{
string ConexaoCliente = "Data Source=" + DataSource + ";User Id=" + Login + ";Password=" + Senha;
Oracle.DataAccess.Client.OracleConnection cnx = new Oracle.DataAccess.Client.OracleConnection(ConexaoCliente);
return(cnx);
}
public virtual void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt) {
this.AMOUNTIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "AMOUNT");
if ((AMOUNTIsNull == false)) {
this.AMOUNT = ((decimal)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "AMOUNT")));
}
this.UPDATE_DATEIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "UPDATE_DATE");
if ((UPDATE_DATEIsNull == false)) {
this.UPDATE_DATE = ((System.DateTime)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "UPDATE_DATE")));
}
this.FLEXFIELD_1 = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "FLEXFIELD_1")));
this.INSERT_DATEIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "INSERT_DATE");
if ((INSERT_DATEIsNull == false)) {
this.INSERT_DATE = ((System.DateTime)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "INSERT_DATE")));
}
this.FLEXFIELD_3 = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "FLEXFIELD_3")));
this.UPDATE_USER = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "UPDATE_USER")));
this.FLEXFIELD_2 = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "FLEXFIELD_2")));
this.ORDER_NOIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "ORDER_NO");
if ((ORDER_NOIsNull == false)) {
this.ORDER_NO = ((decimal)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "ORDER_NO")));
}
this.FLEXFIELD_4 = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "FLEXFIELD_4")));
this.INSERT_USER = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "INSERT_USER")));
this.NOIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "NO");
if ((NOIsNull == false)) {
this.NO = ((decimal)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "NO")));
}
this.ITEM_NO = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "ITEM_NO")));
}
public Object DLookUp(String column,String table,String condition)
{
OracleConnection objConexion;
OracleDataAdapter objComando;
DataSet requestquery = new DataSet();
object result;
objConexion = new OracleConnection(driver);
objConexion.Open();
if(condition.Equals("")){
objComando = new OracleDataAdapter("Select " + column + " from " + table, objConexion);
}
else
{
objComando = new OracleDataAdapter("Select " + column + " from " + table + " where " + condition, objConexion);
}
try
{
objComando.Fill(requestquery);
result = requestquery.Tables[0].Rows[0][requestquery.Tables[0].Columns.IndexOf(column)];
}
catch (Exception e)
{
result = -1;
}
objConexion.Close();
return result;
}
/// <summary>DatabaseKoppeling() is de constructor class.
/// <para> Hierin wordt de login + password en het adress van de oracle database ingevoerd.</para>
/// </summary>
static DatabaseKoppeling()
{
conn = new OracleConnection();
String pcn = "233610";
String pw = "2159122";
conn.ConnectionString = "User Id=" + pcn + ";Password="******";Data Source=" + "//webdb.hi.fontys.nl:1521/cicdb.informatica.local" + ";";
}
public OracleTransformationProvider(Dialect dialect, string connectionString)
: base(dialect, connectionString)
{
_connection = new OracleConnection();
_connection.ConnectionString = _connectionString;
_connection.Open();
}
public void Connect()
{
con = new OracleConnection();
con.ConnectionString = connectionstring;
con.Open();
Console.WriteLine("Connection succesfull");
}
public OracleConnection NewConnection(string username, string password)
{
string oradb = "Data Source=HUNDIPLUG12; User Id="+ username + "; Password="******";";
connection = new OracleConnection(oradb); // C#
connection.Open();
return connection;
}
public static bool IsAvailable(string columnName, string value)
{
bool available = false;
if (!String.IsNullOrWhiteSpace(columnName) && value != null)
{
try
{
using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
{
OracleCommand command = new OracleCommand();
command.CommandText = String.Format("SELECT COUNT(*) FROM Users WHERE {0} LIKE :value",columnName);
command.Parameters.Add(":value", OracleDbType.NVarchar2).Value = value;
command.Connection = connection;
connection.Open();
int count = Convert.ToInt32(command.ExecuteScalar());
if (count==0)
available = true;
else
available = false;
}
}
catch (Exception e)
{
available = false;
Logger.LogException(e);
}
}
return available;
}
private void buttonupdate_Click(object sender, EventArgs e)
{
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "update pharm.DRUG set D_NAME = '" + updatename.Text + "', MFD = '"+ textBox3.Text +"', EXP= '"+ updateexp.Text + "' , COST ="+ updatecost.Text +", QUANTITY = "+ textBox2.Text + " WHERE DRUG_ID = " + textBox1.Text;
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not inserted");
else
MessageBox.Show("Success!");
conn.Dispose();
}
public int Insert(EmployeeTSI info)
{
OracleConnection conn = new OracleConnection(ConStr);
try
{
int EmpId=0;
OracleCommand command = new OracleCommand("EmpTSI_update", conn);
command.CommandType = CommandType.StoredProcedure;
var _params = command.Parameters;
conn.Open();
SetParameterValue(_params, "p_empl_id_no", info.Empl_Id_No, OracleDbType.Int32);
SetParameterValue(_params, "p_tsi", info.TSI, OracleDbType.Varchar2);
SetParameterValue(_params, "p_user_id", this.UserId, OracleDbType.Varchar2);
SetParameterValue(_params, "p_img", info.IMG, OracleDbType.Blob);
SetParameterValue(_params, "p_rtf", info.RTF, OracleDbType.Blob);
SetParameterValue(_params, "p_rif", info.RIF, OracleDbType.Blob);
SetParameterValue(_params, "p_rmf", info.RMF, OracleDbType.Blob);
SetParameterValue(_params, "p_rrf", info.RRF, OracleDbType.Blob);
SetParameterValue(_params, "p_rsf", info.RSF, OracleDbType.Blob);
SetParameterValue(_params, "p_ltf", info.LTF, OracleDbType.Blob);
SetParameterValue(_params, "p_lif", info.LIF, OracleDbType.Blob);
SetParameterValue(_params, "p_lmf", info.LMF, OracleDbType.Blob);
SetParameterValue(_params, "p_lrf", info.LRF, OracleDbType.Blob);
SetParameterValue(_params, "p_lsf", info.LSF, OracleDbType.Blob);
command.ExecuteNonQuery();
conn.Close();
return EmpId;
}
catch (Exception ex)
{
throw ex;
}
}
public static bool AddIncident(Incident i, int missieUID)
{
using (OracleConnection o = new OracleConnection(connstring))
{
using (OracleCommand c = new OracleCommand())
{
try
{
c.Connection = o;
o.Open();
c.CommandText = "INSERT INTO TIncident (Beschrijving) VALUES ('" + i.Beschrijving + "')";
c.ExecuteNonQuery();
c.CommandText = "SELECT MAX(ID) FROM TIncident";
OracleDataReader dr = c.ExecuteReader();
if (dr.Read())
{
c.CommandText = "INSERT INTO TMissie_Incident (MissieID, IncidentID) VALUES ('" + missieUID + "', '" + GetInt(dr[0]) + "')";
c.ExecuteNonQuery();
}
return true;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return false;
}
finally
{
o.Close();
}
}
}
}
//Opens the Connection
private static void OpenDbConnection()
{
try
{
if (_OdacConn == null)
{
_OdacConn = new Oracle.DataAccess.Client.OracleConnection();
}
//Just Force the ConnectionString
_OdacConn.ConnectionString = OdacConnectionString;
if (_OdacConn.State != System.Data.ConnectionState.Closed)
{
_OdacConn.Close();
}
int connAttempts = 0;
do
{
connAttempts++;
_OdacConn.Open();
}while (_OdacConn.State != System.Data.ConnectionState.Open && connAttempts < MAX_DB_CONNECT_ATTEMPTS);
}
catch (Exception ex)
{
LogToFileServer("Server", "ERR: " + ex.Message + " TRACE: " + ex.StackTrace);
}
}
protected void Page_Load(object sender, EventArgs e)
{
//todo: (004-xxxDataReader) remover o botão, mantendo a funcionalidade da página
#region solução de "remover o botão, mantendo a funcionalidade da página"
//ver o arquivo WebForm1.aspx
#endregion
DropDownList1.SelectedIndexChanged += DropDownList1SelectedIndexChanged;
if (IsPostBack) return;
DropDownList1.Items.Clear();
DropDownList1.Items.Add(new ListItem("(escolha uma opção)", "-1"));
using (var c = new OracleConnection(@"DATA SOURCE=127.0.0.1/ORCL;USER ID=chinook;PASSWORD=p4ssw0rd;"))
{
var k = new OracleCommand("SELECT * FROM GENRE", c);
c.Open();
var dr = k.ExecuteReader();
while (dr.Read())
{
DropDownList1.Items.Add(new ListItem(dr[1].ToString(), dr[0].ToString()));
}
c.Close();
DropDownList1.DataBind();
}
}
public IBeheerContextEntity GetBusinessObject(string qry, BeheerContextEntity beheerObject)
{
using (var conn = new OracleConnection(_oradb))
{
conn.Open();
var cmd = new OracleCommand
{
Connection = conn,
CommandText = qry,
CommandType = CommandType.Text
};
cmd.Connection = conn;
var reader = cmd.ExecuteReader();
var smartReader = new SmartDataReader(reader);
BeheerContextEntity businessObject = null;
while (smartReader.Read())
{
businessObject = new BeheerContextEntity
{
Id = smartReader.GetInt32("id"),
DataKeyValue = smartReader.GetString(beheerObject.DataKeyName, ""),
Tablename = beheerObject.Tablename,
DataKeyName = beheerObject.DataKeyName
};
}
return businessObject;
}
}
public DataTable Consulta
(
string pQuery,
string pConnectionString
)
{
DataTable lTable = new DataTable();
DataSet lDataSet = new DataSet();
OracleConnection lConnection = new OracleConnection(pConnectionString);
try
{
lConnection.Open();
OracleDataAdapter lDataAdapter = new OracleDataAdapter(pQuery, pConnectionString);
lDataAdapter.Fill(lDataSet);
lTable = lDataSet.Tables[0];
return lTable;
}
finally
{
lConnection.Close();
}
}
public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "NOTE", this.oNOTE);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "COGNOME", this.oCOGNOME);
if ((REF_ID_ALLIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "REF_ID_ALL", this.oREF_ID_ALL);
}
if ((ID_PRAIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ID_PRA", this.oID_PRA);
}
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CODFIS", this.oCODFIS);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "NOME", this.oNOME);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "DDN_MESE", this.oDDN_MESE);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "SESSO", this.oSESSO);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "DDN_GIORNO", this.oDDN_GIORNO);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "REF_ID_DOC", this.oREF_ID_DOC);
if ((NUMEROIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "NUMERO", this.oNUMERO);
}
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "DDN_ANNO", this.oDDN_ANNO);
if ((REF_ID_SOTIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "REF_ID_SOT", this.oREF_ID_SOT);
}
if ((REF_ID_REQIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "REF_ID_REQ", this.oREF_ID_REQ);
}
}
private void button1_Click(object sender, EventArgs e)
{
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "Delete from pharm.DRUG where DRUG_ID=" + textBox1.Text + " ";
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not deleted");
else
MessageBox.Show("Successfully deleted!");
conn.Dispose();
}
public RoleDependencyManager(OracleConnection conn)
{
if (conn == null)
throw new ArgumentNullException("Oracle connection");
this.conn = conn;
}
public static OracleConnection ConnectionData()
{
string cnStr = "Data Source=(DESPRIPTION =(ADDPRESS_LIST =(ADDPRESS = (PROTOCOL = TCP)(HOST= localhost)(PORT= = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORCL))); User Id = QUANLY; Password=123456";
cn = new OracleConnection(cnStr);
return cn;
}
public UsAuth getAuthForIdUser(string email, string passwd)
{
OracleConnection connection = null;
OracleDataReader dr = null;
UsAuth auth = null;
try
{
using (connection = new OracleConnection(OracleHelper.connectionString()))
{
using (var command = new OracleCommand("US_AUTH_GETFOR_USER", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("P_CUR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
command.Parameters.Add("EMAIL", email);
command.Parameters.Add("PASWD", passwd);
using (dr = command.ExecuteReader())
{
if (dr.HasRows)
{
auth = new UsAuth()
{
IdUser = Convert.ToInt32(dr["IDUSER"].ToString()),
Email = dr["EMAIL"].ToString(),
FirstName = dr["FIRSTNAME"].ToString(),
LastName = dr["LASTNAME"].ToString(),
Role = new UsRole()
{
Id = Convert.ToInt32(dr["IDROLE"].ToString()),
Name = dr["NAMEROLE"].ToString()
},
Photo = dr["PHOTO"].ToString(),
ListRoleModule = new DARoleModule().getRoleModuleForRole(Convert.ToInt32(dr["IDROLE"].ToString())),
ListRoleModulePrivilege = new DARoleModulePrivilege().getRoleModulePrivilegeForRole(Convert.ToInt32(dr["IDROLE"].ToString()))
};
}
}
}
}
return auth;
}
catch (Exception e)
{
dr.Dispose();
if (connection.State == ConnectionState.Open)
connection.Dispose();
LogHelper.WriteLog(e);
throw e;
}
finally
{
auth = null;
}
}
/// <summary>
/// Use only to retrieve user on initialization
/// </summary>
/// <param name="query">The input query</param>
/// <returns>A DataTable containing the results of the query</returns>
public static DataTable HaalGebruikersOp(string query)
{
using (OracleConnection conn = new OracleConnection(connectionstring2))
{
try
{
conn.Open();
OracleCommand cmd = new OracleCommand(query);
cmd.Connection = conn;
try
{
OracleDataReader reader = cmd.ExecuteReader();
DataTable result = new DataTable();
result.Load(reader);
conn.Close();
return result;
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
return new DataTable();
}
}
}
public string CheckConnection()
{
DB_Toolbox env_dbkit = new DB_Toolbox();
using (OracleConnection connection =
new OracleConnection())
{
connection.ConnectionString =
constr;
try
{
connection.Open();
// DBstatus = ("Connection Successful!");
return ("EMIN-00001-Connection Successful!");
//stops the console from closing
//until you hit the ENTER key
//Console.ReadLine();
}
catch (OracleException ex)
{
//DBstatus = ("Connection Error!");
return ("EMIN-00000-Connection Error! " + env_dbkit.DB_ERROR_FORMATTER("ORACLE", ex.ToString()));
//Console.WriteLine(ex.ToString());
// stops the console from closing
//until you hit the ENTER key
// Console.ReadLine();
}
}
}
private Connection()
{
try
{
string Dsource = "(DESCRIPTION="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=bd.thronewars.ca)(PORT=1521)))"
+ "(CONNECT_DATA=(SERVICE_NAME=ORCL)))";
String ChaineConnexion = "Data Source=" + Dsource
+ ";User Id=THRONE; Password =Warst";
conn = new OracleConnection(ChaineConnexion);
conn.Open();
if (conn.State.ToString() != "Open")
{
// to do
}
}
catch (OracleException ex)
{
Erreur.ErrorMessage(ex);
}
}
public virtual IDb OpenDb()
{
IDbConnection cn = new Oracle.DataAccess.Client.OracleConnection(connString);
//cn.Open();
IDb db = new OrlDb(this, cn);
return db;
}
public void Open(string connString)
{
if (_internalConnection == null)
_internalConnection = new OracleConnection(connString);
if (_internalConnection.State != ConnectionState.Open)
_internalConnection.Open();
}
private void button1_Click_1(object sender, EventArgs e)
{
int a;
a = Convert.ToInt32(textBox4.Text);
a = int.Parse(textBox4.Text);
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "update pharm.DRUG set QUANTITY =QUANTITY - " + a + "";
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not inserted");
else
MessageBox.Show("Success!");
conn.Dispose();
}
public override void SqlActivate(Oracle.DataAccess.Client.OracleConnection conn, int resID)
{
string insertRest = @"UPDATE MENI
SET VALIDNOST_MENI = 1
WHERE RESTORAN_ID = :ResID AND IME_MENI = :ImeMeni";
OracleCommand cmd = new OracleCommand(insertRest, conn);
OracleParameter prm = new OracleParameter("ResID", OracleDbType.Int64);
prm.Value = resID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("ImeMeni", OracleDbType.Varchar2);
prm.Value = Ime;
cmd.Parameters.Add(prm);
int br;
try
{
br = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw new NotImplementedException("Проверете ја вашата конекција");
}
if (br == 0)
{
throw new NotImplementedException("Не постои ставката");
}
ValidnostMeni = true;
}
public static void GetEmployeeMatches(List<Employee> list, DPFP.Sample Sample,int FingerNo)
{
OracleConnection conn = new OracleConnection(ConStr);
Employee info = null;
string FingerDesc = Util.GetFingerDesc(FingerNo);
try
{
string _result = string.Empty;
string sql = "select Empl_Id_No,RTF,RIF,RMF,RRF,RSF,LTF,LIF,LMF,LRF,LSF from EMPTSI";
OracleCommand command = new OracleCommand(sql, conn);
command.CommandType = CommandType.Text;
conn.Open();
OracleDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
info = dcEmployee.GetInfo(reader, Sample, FingerDesc);
AddInfo(list, info);
}
}
if (conn.State == ConnectionState.Open) conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public Database(string serverHostName, string userName, string userPassword, string sidDbInstanceName = "orcl", int port = 1521)
{
this.serverHostName = serverHostName;
this.sidDbInstanceName = sidDbInstanceName;
this.userName = userName;
this.userPassword = userPassword;
this.port = port;
// SID = identify a particular database instance on a computer
string database = $"Data Source = (DESCRIPTION = " +
$"(ADDRESS = (PROTOCOL = TCP)(HOST = {serverHostName})(PORT = {port}))" +
$"(CONNECT_DATA = (SID = {sidDbInstanceName})));" +
$"User Id = {userName};Password = {userPassword};";
try
{
db = new OracleConnection(database);
db.Open();
System.Windows.MessageBox.Show($"Success!\nConnected to Oracle {db.ServerVersion}", "Connecting to database...");
}
catch (OracleException e)
{
string issue = $"Failed to connect to database host < {serverHostName} >\nException: {e.Message}\nSource: {e.Source}";
System.Windows.MessageBox.Show(issue, "This app will not work correctly until connected, closing...");
App.Current.Shutdown();
}
}
/// <summary>
/// 数据库连接关闭
/// </summary>
public void CloseConn()
{
if (_oracleConn == null) return;
_oracleConn.Close();
_oracleConn.Dispose();
_oracleConn = null;
}
public virtual IDb OpenDb()
{
IDbConnection cn = new Oracle.DataAccess.Client.OracleConnection(connString);
//cn.Open();
IDb db = new OrlDb(this, cn);
return(db);
}
private string SaveToDatabase(byte[] btReport, string id)
{
// Write to J Drive
string filepath = "";
string timestamp = DateTime.Now.ToString("MMddHHmmssfff");
string filename = "fax" + id + "_" + timestamp + ".pdf";
string credentials = Data.getJCredential();
String[] credentialsArray = credentials.Split("|".ToCharArray());
string COMPUTER_IP = credentialsArray[0].ToString().Split(':')[1];
string DOMAIN = credentialsArray[1].ToString().Split(':')[1];
string USER_NAME = credentialsArray[2].ToString().Split(':')[1];
string PASSWORD = credentialsArray[3].ToString().Split(':')[1];
try
{
using (NetworkShareAccesser.Access(COMPUTER_IP, DOMAIN, USER_NAME, PASSWORD))
{
filepath = "\\\\" + COMPUTER_IP + "\\j_drive\\Clients\\LifeEvent\\" + filename;
File.WriteAllBytes(filepath, btReport);
filepath = filepath.Replace("\\\\" + COMPUTER_IP + "\\j_drive\\", "J:\\");
}
}
catch
{
}
// Save to Database
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
try
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Enrollment_Wizard_LE.SaveFaxImage", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", Request.Params["DpNo"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", SQLStatic.Sessions.GetUserName(HttpContext.Current.Request.Cookies["Session_ID"].Value.ToString()));
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
finally
{
conn.Close();
conn.Dispose();
}
return(filename);
}
static void Main(string[] args)
{
Oracle.DataAccess.Client.OracleConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
// Tạo một đối tượng Command gọi thủ tục Get_Employee_Info.
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("Get_Employee_Info", conn);
// Kiểu của Command là StoredProcedure
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// Thêm tham số @p_Emp_Id và sét giá trị của nó = 100.
cmd.Parameters.Add("@p_Emp_Id", Oracle.DataAccess.Client.OracleDbType.Int32).Value = 100;
// Thêm tham số @v_Emp_No kiểu Varchar(20).
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("@v_Emp_No", Oracle.DataAccess.Client.OracleDbType.Varchar2, 20));
cmd.Parameters.Add(new OracleParameter("@v_First_Name", Oracle.DataAccess.Client.OracleDbType.Varchar2, 50));
cmd.Parameters.Add(new OracleParameter("@v_Last_Name", Oracle.DataAccess.Client.OracleDbType.Varchar2, 50));
cmd.Parameters.Add(new OracleParameter("@v_Hire_Date", OracleDbType.Date));
// Đăng ký tham số @v_Emp_No là OUTPUT.
cmd.Parameters["@v_Emp_No"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@v_First_Name"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@v_Last_Name"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@v_Hire_Date"].Direction = System.Data.ParameterDirection.Output;
// Thực thi thủ tục.
cmd.ExecuteNonQuery();
// Lấy các giá trị đầu ra.
string empNo = cmd.Parameters["@v_Emp_No"].Value.ToString();
string firstName = cmd.Parameters["@v_First_Name"].Value.ToString();
string lastName = cmd.Parameters["@v_Last_Name"].Value.ToString();
object hireDateObj = cmd.Parameters["@v_Hire_Date"].Value;
Console.WriteLine("hireDateObj type: " + hireDateObj.GetType().ToString());
Oracle.DataAccess.Types.OracleDate hireDate = (Oracle.DataAccess.Types.OracleDate)hireDateObj;
Console.WriteLine("Emp No: " + empNo);
Console.WriteLine("First Name: " + firstName);
Console.WriteLine("Last Name: " + lastName);
Console.WriteLine("Hire Date: " + hireDate);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
Console.WriteLine(e.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
Console.Read();
}
private static void GetFile(string person_id, char type, System.Web.HttpResponse Response)
{
string cmd = "";
switch (type)
{
case 'P':
cmd = "research_dir_user.profile_views.get_profile_picture";
break;
case 'C':
cmd = "research_dir_user.profile_views.get_profile_cv";
break;
default:
Response.Write("Error:type not defined");
return;
}
using (Oracle.DataAccess.Client.OracleConnection orCN = HealthIS.Apps.Util.getDBConnection())
{
orCN.Open();
OracleCommand orCmd = new OracleCommand(cmd, orCN);
orCmd.CommandType = System.Data.CommandType.StoredProcedure;
orCmd.Parameters.Add("p_person_id", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.Input;
orCmd.Parameters["p_person_id"].Value = person_id;
orCmd.Parameters.Add("r_cur", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
OracleDataAdapter adapt = new OracleDataAdapter(orCmd);
System.Data.DataSet orDS = new System.Data.DataSet();
orCmd.ExecuteNonQuery();
adapt.Fill(orDS);
if (orDS.Tables[0].Rows.Count > 0)
{
System.Data.DataRow dr = orDS.Tables[0].Rows[0];
byte[] barray = (byte[])dr["file_binary"];
Response.ContentType = (String)dr["file_mimetype"];
Response.AddHeader("Content-Disposition", "attachment; filename=" + person_id + "." + dr["file_ext"].ToString() + ";");
Response.AddHeader("Content-Length", barray.Length.ToString());
Response.OutputStream.Write(barray, 0, barray.Length);
}
else
{
Response.Write("Error, no file found for person_id '" + person_id + "'");
}
orDS.Dispose();
adapt.Dispose();
orCmd.Dispose();
orCN.Close();
orCN.Dispose();
}
}
//The FromCustomObject method is required as part of the IOracleCustomType
//interface. This function allows you to define the mapping to use when filling a
//UDT object with data from your UDT class
public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con,
System.IntPtr pUdt)
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ID", this.ID);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "NAME", this.NAME);
if ((PRICEIsNull == false))
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "PRICE",
this.PRICE);
}
}
private void SQLToCSV(string nameOwner, string query, string domainFileName, string pathDomain)
{
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionDTOP();
conn.Open();
OracleCommand cmd = new OracleCommand(query, conn);
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
CreateExcelFile(nameOwner, domainFileName, pathDomain, dr);
}
dr.Close();
dr.Dispose();
conn.Close();
}
public override void SQLUpdate(Oracle.DataAccess.Client.OracleConnection conn, int resID)
{
string insertRest = @"UPDATE STAVKA
SET OPIS_STAVKA = :OpisStavka, CENA_STAVKA = :CenaStavka, IME_STAVKA = :ImeStavka, DODATOK_STAVKA = 0
WHERE RESTORAN_ID = :ResID AND IME_MENI = :ImeMeni AND STAVKA_ID = :StavkaID";
OracleCommand cmd = new OracleCommand(insertRest, conn);
OracleParameter prm = new OracleParameter("OpisStavka", OracleDbType.Varchar2);
prm.Value = Opis;
cmd.Parameters.Add(prm);
prm = new OracleParameter("CenaStavka", OracleDbType.Int64);
prm.Value = this.ComputeCost();
cmd.Parameters.Add(prm);
prm = new OracleParameter("ImeStavka", OracleDbType.Varchar2);
prm.Value = Ime;
cmd.Parameters.Add(prm);
prm = new OracleParameter("ResID", OracleDbType.Int64);
prm.Value = resID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("ImeMeni", OracleDbType.Varchar2);
prm.Value = Parent.GetName();
cmd.Parameters.Add(prm);
prm = new OracleParameter("StavkaID", OracleDbType.Int64);
prm.Value = ID;
cmd.Parameters.Add(prm);
int br;
try
{
br = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw new NotImplementedException("Проверете ја вашата конекција");
}
if (br == 0)
{
throw new DuplicatePrimaryKeyException("Не постои ставката");
}
}
public static Database GetInstance()
{
string dbConnName = DataConnectionName;
if (_db == null)
{
try
{
_db = new Database(dbConnName);
}
catch
{
string connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ToString();
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connstr);
conn.Open();
_db = new Database(conn);
}
}
else
{
//重试连接，以下代码尚未测试，请进行测试。。。by liuzhy 20140421
int n = 3; //设定重试多少次；
int delay = 200; //设定每次重试间隔多少毫秒；
string connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ToString();
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connstr);
while (_db.Connection.State != System.Data.ConnectionState.Open)
{
try
{
conn.Open();
_db = new Database(conn);
}
catch (Exception ex)
{
//可以记录一下访问失败的一些信息，比如时间、ip地址、ex.Message等
}
System.Threading.Thread.Sleep(delay);
n--;
if (n.Equals(0))
{
break;
}
}
}
_db.OpenSharedConnection();
return(_db);
}
//This method is the opposite. It allows you to define the mapping to use when
//populating your UDT class with data from a retrieved UDT object.
public virtual void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con,
System.IntPtr pUdt)
{
this.ID = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt,
"ID")));
this.NAME = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt,
"NAME")));
this.PRICEIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt,
"PRICE");
if ((PRICEIsNull == false))
{
this.PRICE =
((decimal)(Oracle.DataAccess.Types.OracleUdt.GetValue(con,
pUdt, "PRICE")));
}
}
public virtual void CloseDb(OrlDb db)
{
if (!db.IsClosed)
{
if (!db.IsAdapter && db.Connection != null)
{
Oracle.DataAccess.Client.OracleConnection cn = (Oracle.DataAccess.Client.OracleConnection)db.Connection;
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
cn.Dispose();
db.Connection = null;
}
}
}
private void _openConn()
{
//this.theConn = DB.GetInstance().Connection as OracleConnection;
string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["oracle"].ToString();
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connstr);
theConn = conn;
//conn.Open();
OpenConn();
this.theConnStr = this.theConn.ConnectionString;
this.theProviderName = "Oracle.DataAccess.Client";
this.theComd = theConn.CreateCommand();
this.theSql = "";
}
public int ExecuteNonQuery(string cmdText, CommandType cmdType, List <Oracle.DataAccess.Client.OracleParameter> parameters = null)
{
using (var sCon = new Oracle.DataAccess.Client.OracleConnection(GetConnectionString()))
{
using (var sCmd = new Oracle.DataAccess.Client.OracleCommand(cmdText, sCon))
{
sCmd.CommandTimeout = 600;
sCmd.CommandType = cmdType;
if (null != parameters)
{
sCmd.Parameters.AddRange(parameters.ToArray());
}
sCon.Open();
return(sCmd.ExecuteNonQuery());
}
}
}
public static IEnumerable <Customer> getCustomerStored()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "tables.getcustomertable",
CommandType = CommandType.StoredProcedure
};
var reader = cmd.ExecuteNonQuery();
return(null);
}
public OWDB(string DataBaseName, string LoginUser, string LoginPassword)
{
OracleConnection1 = new Oracle.DataAccess.Client.OracleConnection();
// Sample Connectionstring 'User Id=owuser;Password=owuser;Data source=WHTRUNK'
OracleConnection1.ConnectionString =
"User Id=" +
LoginUser +
";Password="******";Data source=" +
DataBaseName +
"";
OracleConnection1.Open();
OracleTransaction1 = OracleConnection1.BeginTransaction();
}
public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
try
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
OracleDataAdapter MyAdapter = new OracleDataAdapter();
MyAdapter.SelectCommand = cmd;
DataSet ds = new DataSet();
MyAdapter.Fill(ds);
cmd.Parameters.Clear();
DataTable table = ds.Tables[0];
ds.Dispose();
connection.Close();
return table;
}
catch
{
connection.Close();
throw;
}
}
}
