public List <Tuple <int, string> > DajVsetkyTypyVlakov()
{
List <Tuple <int, string> > ret = new List <Tuple <int, string> >();
OracleDataReader oraReader = null;
try
{
_connection.Open();
OracleCommand oraCommand = new OracleCommand("SELECT id_druhu,nazov_druhu FROM druh_vlaku", _connection);
oraReader = oraCommand.ExecuteReader();
if (oraReader.HasRows)
{
while (oraReader.Read())
{
var idcko = oraReader.GetInt32(0);
var nazov = oraReader.GetString(1);
ret.Add(new Tuple <int, string>(idcko, nazov));
}
}
}
finally
{
oraReader?.Close();
_connection.Close();
}
return(ret);
}
public List <string> FetchDatabases()
{
VerifyConnection();
OracleDataReader reader = null;
List <string> result = new List <string>();
OracleCommand cmd = new OracleCommand("SELECT DISTINCT(OWNER) AS DATABASE_NAME FROM DBA_SEGMENTS WHERE OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX'))", (OracleConnection)_connection);
try
{
if (!_isTesting)
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(reader.GetString(0));
}
}
}
finally
{
reader?.Close();
}
return(result);
}
public string SimulatorVlaku(int idvlaku)
{
OracleDataReader oraReader = null;
try
{
_connection.Open();
var sql = $"select s.nazov from stanica s " +
$"where s.poloha in ( select deref(v.vozen).aktual_poloha " +
$"from vozen_vlak v where deref(v.vlak).id_vlak = {idvlaku} and cas_do is null) ";
OracleCommand oraCommand = new OracleCommand(sql, _connection);
oraReader = oraCommand.ExecuteReader();
if (oraReader.HasRows)
{
while (oraReader.Read())
{
return(oraReader.GetString(0));
}
}
return("");
}
finally
{
oraReader?.Close();
_connection.Close();
}
}
public bool Fill()
{
try {
conn = new OracleConnection(GetConnectionString());
conn.Open();
transaction = conn.BeginTransaction();
} catch (Exception e) {
MessageBox.Show("При подключении к базе данных произошла ошибка:\n\n" + e.Message);
conn.Close();
conn.Dispose();
return false;
}
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TO_CHAR(id), l_name from akriko.cat_executors WHERE id in (SELECT distinct(ispolnitel_cik_id) FROM akriko.appeal) ORDER BY UPPER(l_name)";
try {
dr = cmd.ExecuteReader();
while (dr.Read()) {
if (dr.IsDBNull(0) || dr.IsDBNull(1)) {
MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n");
break;
} else {
UsersGrid.Rows.Add();
UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["executor_id"].Value = dr.GetString(0);
UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["akriko"].Value = dr.GetString(1);
}
}
dr.Close();
dr.Dispose();
} catch (Exception e) {
MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n" + e.Message);
}
if (UsersGrid.Rows.Count == 0) {
MessageBox.Show("Отсутствуют неотконвертированные исполнители", "Конвертация исполнителей", MessageBoxButtons.OK, MessageBoxIcon.Information);
ConvertButton.Enabled = false;
}
FillDeloDepartmentsColumn();
return true;
}
public override DbDataReader ExecuteReader(string sqlString, params DbParameter[] dbParameter)
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionString;
OracleCommand cmd = new OracleCommand();
OracleDataReader rdr = null;
try
{
//Prepare the command to execute
PrepareCommand(cmd, conn, sqlString, null, CommandType.Text, dbParameter);
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(rdr);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
rdr?.Close();
cmd.Dispose();
conn.Close();
}
return(null);
}
private bool EvaluatePedZoneRules(string plate, DateTime dt, bool bTipoArticulo, int iTipoArticulo, ref int iRuleId)
{
OracleConnection oraDBConn = null;
OracleCommand oraCmd = null;
ILogger logger = null;
OracleDataReader dr = null;
bool bRuleFound = false;
try
{
Database d = OPS.Components.Data.DatabaseFactory.GetDatabase();
logger = DatabaseFactory.Logger;
System.Data.IDbConnection DBCon = d.GetNewConnection();
oraDBConn = (OracleConnection)DBCon;
oraDBConn.Open();
if (oraDBConn.State == System.Data.ConnectionState.Open)
{
oraCmd = new OracleCommand();
oraCmd.Connection = (OracleConnection)oraDBConn;
StringBuilder sqlQuery = new StringBuilder();
if (bTipoArticulo)
{
sqlQuery.AppendFormat("select * from pedestrian_zone_rules where PDR_DART_ID = {0}", iTipoArticulo);
//oraCmd.CommandText = "select * from pedestrian_zone_rules where PDR_DART_ID is not null";
}
else
{
sqlQuery.Append("select * from pedestrian_zone_rules where PDR_DART_ID is null");
//oraCmd.CommandText = "select * from pedestrian_zone_rules where PDR_DART_ID is null";
}
oraCmd.CommandText = sqlQuery.ToString();
dr = oraCmd.ExecuteReader();
while (dr.Read() && bRuleFound == false)
{
bRuleFound = true;
int iOrdinal = dr.GetOrdinal("PDR_DDAY_ID");
if (dr.IsDBNull(iOrdinal) == false)
{
int dday = dr.GetInt32(iOrdinal);
if (IsTypeOfDay(dt, dday) == false)
{
bRuleFound = false;
}
else
{
bool bTimeCheck = false;
iOrdinal = dr.GetOrdinal("PDR_DATE_INI");
if (dr.IsDBNull(iOrdinal) == false)
{
string date = dr.GetOracleValue(iOrdinal).ToString();
date = date.Substring(0, 10); // 10 es la longitud de DD/MM/YYYY
DateTime dtInit = DateTime.Parse(date);
//DateTime dtInit = DateTime.Parse(dr.GetString(iOrdinal).ToString());
if (dt < dtInit)
{
bRuleFound = false;
bTimeCheck = false;
}
else
{
bTimeCheck = true;
}
}
else
{
bTimeCheck = true;
}
iOrdinal = dr.GetOrdinal("PDR_DATE_END");
if (dr.IsDBNull(iOrdinal) == false)
{
string date = dr.GetOracleValue(iOrdinal).ToString();
date = date.Substring(0, 10); // 10 es la longitud de DD/MM/YYYY
DateTime dtEnd = DateTime.Parse(date);
//DateTime dtEnd = DateTime.Parse(dr.GetString(iOrdinal).ToString());
if (dt > dtEnd)
{
bRuleFound = false;
bTimeCheck = false;
}
else
{
bTimeCheck = true;
}
}
else
{
bTimeCheck = true;
}
if (bTimeCheck)
{
iOrdinal = dr.GetOrdinal("PDR_TIM_ID");
int iTimId = dr.GetInt32(iOrdinal);
int iMinutes = dt.Minute + dt.Hour * 60;
int iMinutesMin = GetTimMinutesMin(iTimId);
int iMinutesMax = GetTimMinutesMax(iTimId);
if (iMinutesMin <= iMinutes && iMinutes <= iMinutesMax)
{
}
else
{
bRuleFound = false;
}
}
}
}
if (bRuleFound)
{
iOrdinal = dr.GetOrdinal("PDR_ID");
iRuleId = dr.GetInt32(iOrdinal);
}
}
}
}
catch (Exception e)
{
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
dr = null;
}
if (oraCmd != null)
{
oraCmd.Dispose();
oraCmd = null;
}
if (oraDBConn != null)
{
oraDBConn.Close();
oraDBConn.Dispose();
oraDBConn = null;
}
}
return(bRuleFound);
}
//private int imgCnt = 0;
//private void debugGetImageNames(ref sCaseInfo caseInfo)
//{
// imgCnt++;
// string cntStr = imgCnt.ToString("00");
// caseInfo.mImageNP = string.Empty;
// caseInfo.mImage1 = @"C:\Development\iCapture\Doc\Img0" + cntStr + "_0.jpg";
// caseInfo.mImage2 = @"C:\Development\iCapture\Doc\Img0" + cntStr + "_1.jpg";
// if (imgCnt == 1)
// caseInfo.mImage2 = @"C:\Development\iCapture\Doc\Img001.sml_1.jpg";
// if (imgCnt == 2)
// {
// caseInfo.mImage3 = @"C:\Development\iCapture\Doc\Img0" + cntStr + "_2.jpg";
// caseInfo.mImage4 = @"C:\Development\iCapture\Doc\Img0" + cntStr + "_3.jpg";
// }
// else
// caseInfo.mImage3 = caseInfo.mImage4 = string.Empty;
// if (imgCnt > 2)
// imgCnt = 0;
//}
#endif
private bool ImagesFromCursor(OracleRefCursor cursor, ref sCaseInfo caseInfo)
{
caseInfo.mImage1 = caseInfo.mImage2 = caseInfo.mImage3 = caseInfo.mImage4 = caseInfo.mImageNP = string.Empty;
if (cursor is System.DBNull)
{
_error = "No image paths given.";
return(false);
}
OracleDataReader rd = cursor.GetDataReader();
int cnt = 0;
int printImageNo = 0;
while (rd.Read())
{
switch (cnt)
{
case 0:
caseInfo.mImage1 = rd.GetString(0);
caseInfo.mImage1ID = (int)rd.GetDecimal(1);
if (!rd.IsDBNull(2))
{
if (rd.GetDecimal(2) == 1)
{
printImageNo = 0;
}
}
break;
case 1:
caseInfo.mImage2 = rd.GetString(0);
caseInfo.mImage2ID = (int)rd.GetDecimal(1);
if (!rd.IsDBNull(2))
{
if (rd.GetDecimal(2) == 1)
{
printImageNo = 1;
}
}
break;
case 2:
caseInfo.mImage3 = rd.GetString(0);
caseInfo.mImage3ID = (int)rd.GetDecimal(1);
if (!rd.IsDBNull(2))
{
if (rd.GetDecimal(2) == 1)
{
printImageNo = 2;
}
}
break;
case 3:
caseInfo.mImage4 = rd.GetString(0);
caseInfo.mImage4ID = (int)rd.GetDecimal(1);
if (!rd.IsDBNull(2))
{
if (rd.GetDecimal(2) == 1)
{
printImageNo = 3;
}
}
break;
case 4:
caseInfo.mImageNP = rd.GetString(0);
break;
}
cnt++;
}
caseInfo.mPrintImageNo = printImageNo;
rd.Close();
rd.Dispose();
#if (DEBUG)
//debugGetImageNames(ref caseInfo);
#endif
caseInfo.mOnlyOneImage = TestSinglePhoto(ref caseInfo);
if (caseInfo.mOnlyOneImage)
{
caseInfo.mPrintImageNo = printImageNo = 0;
}
return(true);
}
private bool IsPredefinedDay(DateTime dt, ref int iDayTypeCode)
{
//select DAY_DDAY_ID from days where DAY_DATE = to_date('28/07/2010','dd:mm:yyyy')
OracleConnection oraDBConn = null;
OracleCommand oraCmd = null;
ILogger logger = null;
OracleDataReader dr = null;
bool bResult = false;
try
{
Database d = OPS.Components.Data.DatabaseFactory.GetDatabase();
logger = DatabaseFactory.Logger;
System.Data.IDbConnection DBCon = d.GetNewConnection();
oraDBConn = (OracleConnection)DBCon;
oraDBConn.Open();
if (oraDBConn.State == System.Data.ConnectionState.Open)
{
oraCmd = new OracleCommand();
oraCmd.Connection = (OracleConnection)oraDBConn;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendFormat("select DAY_DDAY_ID from days where DAY_DATE = to_date('{0}','dd:mm:yyyy')", dt.ToShortDateString());
oraCmd.CommandText = sqlQuery.ToString();
//oraCmd.CommandText = "select CGRP_ID from groups_childs where CGRP_CHILD = '" + unit +"'";
dr = oraCmd.ExecuteReader();
if (dr.Read())
{
int iOrdinal = dr.GetOrdinal("DAY_DDAY_ID");
iDayTypeCode = dr.GetInt32(iOrdinal);
bResult = true;
}
}
}
catch (Exception e)
{
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
dr = null;
}
if (oraCmd != null)
{
oraCmd.Dispose();
oraCmd = null;
}
if (oraDBConn != null)
{
oraDBConn.Close();
oraDBConn.Dispose();
oraDBConn = null;
}
}
return(bResult);
}
private int GetTimMinutesMin(int iTimId)
{
OracleConnection oraDBConn = null;
OracleCommand oraCmd = null;
ILogger logger = null;
OracleDataReader dr = null;
int iMinutesMin = -1;
try
{
Database d = OPS.Components.Data.DatabaseFactory.GetDatabase();
logger = DatabaseFactory.Logger;
System.Data.IDbConnection DBCon = d.GetNewConnection();
oraDBConn = (OracleConnection)DBCon;
oraDBConn.Open();
if (oraDBConn.State == System.Data.ConnectionState.Open)
{
oraCmd = new OracleCommand();
oraCmd.Connection = (OracleConnection)oraDBConn;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendFormat("select TIM_INI from timetables where TIM_ID = {0}", iTimId.ToString());
oraCmd.CommandText = sqlQuery.ToString();
//oraCmd.CommandText = "select TIM_INI from timetables where TIM_ID = " + iTimId.ToString();
dr = oraCmd.ExecuteReader();
if (dr.Read())
{
int iOrdinal = dr.GetOrdinal("TIM_INI");
iMinutesMin = (int)dr.GetOracleDecimal(iOrdinal);
}
}
}
catch (Exception e)
{
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
dr = null;
}
if (oraCmd != null)
{
oraCmd.Dispose();
oraCmd = null;
}
if (oraDBConn != null)
{
oraDBConn.Close();
oraDBConn.Dispose();
oraDBConn = null;
}
}
return(iMinutesMin);
}
private bool MoveNextNode()
{
if (_node_reader == null)
{
OracleCommand node_command = new OracleCommand("select * from node order by id");
node_command.Connection = _connection;
_node_reader = node_command.ExecuteReader();
if (!_node_reader.Read())
{
_node_reader.Close();
}
OracleCommand node_tag_command = new OracleCommand("select * from node_tags order by node_id");
node_tag_command.Connection = _connection;
_node_tag_reader = node_tag_command.ExecuteReader();
if (!_node_tag_reader.Read())
{
_node_tag_reader.Close();
}
}
// read next node.
if (!_node_reader.IsClosed)
{
// load/parse data.
long id = _node_reader.GetInt64(0);
long latitude_int = _node_reader.GetInt64(1);
long longitude_int = _node_reader.GetInt64(2);
long changeset_id = _node_reader.GetInt64(3);
bool visible = _node_reader.GetInt64(4) == 1;
DateTime timestamp = _node_reader.GetDateTime(5);
long tile = _node_reader.GetInt64(6);
long version = _node_reader.GetInt64(7);
string user = _node_reader.GetString(8);
long uid = _node_reader.GetInt64(9);
SimpleNode node = new SimpleNode();
node.Id = id;
node.Latitude = latitude_int;
node.Longitude = longitude_int;
node.ChangeSetId = changeset_id;
node.TimeStamp = timestamp;
node.UserId = null;
node.UserName = null;
node.Version = (ulong)version;
node.Visible = visible;
node.UserName = user;
node.UserId = uid;
if (!_node_tag_reader.IsClosed)
{
long returned_id = _node_tag_reader.GetInt64(0);
while (returned_id == node.Id.Value)
{
if (node.Tags == null)
{
node.Tags = new Dictionary<string, string>();
}
string key = _node_tag_reader.GetString(1);
string value = _node_tag_reader.GetString(2);
node.Tags.Add(key, value);
if (!_node_tag_reader.Read())
{
_node_tag_reader.Close();
}
returned_id = _node_tag_reader.GetInt64(0);
}
}
// set the current variable!
_current = node;
// advance the reader(s).
if (!_node_reader.Read())
{
_node_reader.Close();
}
if (!_node_tag_reader.IsClosed && !_node_tag_reader.Read())
{
_node_tag_reader.Close();
}
return true;
}
else
{
_node_reader.Close();
_node_reader.Dispose();
_node_reader = null;
_node_tag_reader.Close();
_node_tag_reader.Dispose();
_node_tag_reader = null;
_current_type = SimpleOsmGeoType.Way;
return false;
}
}
public static Acteur GetActeur(string id)
{
string naam = string.Empty;
DateTime dob = new DateTime(1, 1, 1);
string woonplek = string.Empty;
string baan = string.Empty;
List<Prijs> prijzen = new List<Prijs>();
List<Film> films = new List<Film>();
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT f.filmid, f.titel FROM film f, filmacteurregel fcr WHERE f.filmid = fcr.filmid AND fcr.acteurid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", Convert.ToInt32(id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
films.Add(new Film(reader.GetInt32(0).ToString(), reader.GetString(1)));
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT p.titel, p.jaar, p.prizeid FROM acteurprijsregel apr, prijs p WHERE p.prizeid = apr.prizeid AND apr.acteurid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", Convert.ToInt32(id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
prijzen.Add(new Prijs(reader.GetString(0), reader.GetInt32(1), reader.GetInt32(2)));
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT a.naam, a.dob, a.woonplek, a.baan FROM acteur a WHERE a.acteurid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", Convert.ToInt32(id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
naam = reader.GetString(0);
dob = reader.GetDateTime(1);
woonplek = reader.GetString(2);
baan = reader.GetString(3);
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
Acteur acteur = new Acteur(naam, dob, woonplek, baan, prijzen, films, Convert.ToInt32(id));
return acteur;
}
public bool IsDataAvailable(string strSql)
{
try
{
objConnection = open_connection();
objCommand = new OracleCommand(strSql, objConnection);
objDatareader = objCommand.ExecuteReader();
if (objDatareader.HasRows)
{
return true;
}
else
{
return false;
}
}
catch (OracleException objError)
{
ErrorStr = objError.Message;
// ErrorMsg(objConnection, "MSG1107");
err_flag = true;
return false;
}
finally
{
objConnection.Close();
objDatareader.Close();
objCommand.Dispose();
}
}
public static bool InsertUser(string username, string password)
{
int id = 0;
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT MAX(userid) FROM gebruiker";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
reader = cmd.ExecuteReader();
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
return false;
}
}
id = id + 1;
using (connection = new OracleConnection(connectionstring))
{
query = "INSERT INTO gebruiker(userid, gebruikersnaam, wachtwoord) VALUES (:puserid, :pgebruikersnaam, :pwachtwoord)";
cmd.CommandText = query;
cmd.Connection = connection;
connection.Open();
try
{
cmd.Parameters.Clear();
cmd.Parameters.Add("userid", id);
cmd.Parameters.Add("username", username);
cmd.Parameters.Add("password", password);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
return false;
}
}
}
public string ErrorMsg(string ErrCode)
{
objConnection = open_connection();
strSql = "Select error_desc from STD_ERROR_DICTIONARY where error_code='" + ErrCode + "'";
objCommand = new OracleCommand(strSql, objConnection);
objDatareader = objCommand.ExecuteReader();
while (objDatareader.Read() == true)
{
ErrDesc = objDatareader["error_desc"].ToString();
}
objDatareader.Close();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
return ErrDesc;
}
public ListBox fill_listbox(ListBox lstGen, string strSql, string strValue, string strText, string strExtraValue, string strExtraText)
{
objConnection = open_connection();
if (open_con == true)
{
try
{
objCommand = new OracleCommand(strSql, objConnection);
objDatareader = objCommand.ExecuteReader();
lstGen.DataSource = objDatareader;
lstGen.DataTextField = strText;
lstGen.DataValueField = strValue;
lstGen.DataBind();
objDatareader.Close();
objConnection.Close();
if (strExtraText != "")
{
lstGen.Items.Insert(0, new ListItem(strExtraText, strExtraValue));
}
if (lstGen.Items.Count > 0)
{
lstGen.SelectedIndex = 0;
}
list_bind = true;
return lstGen;
}
catch (OracleException objError)
{
if (objError.Message.Substring(0, 21) == "Table does not exist.")
{
ErrorStr = ErrorMsg(objConnection, "MSG1201");
}
list_bind = false;
return null;
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
}
else
{
return null;
}
}
internal static IEnumerable<object[]> FetchRecordsFromReader(OracleDataReader reader, int rowCount, bool prefetch)
{
if (!CanFetchFromReader(reader))
{
yield break;
}
var fieldTypes = new string[reader.FieldCount];
var internalTypes = reader.GetInternalDataTypes();
for (var i = 0; i < reader.FieldCount; i++)
{
var fieldType = reader.GetDataTypeName(i);
fieldTypes[i] = internalTypes[i] == OracleRowId.InternalCode ? OracleRowId.TypeName : fieldType;
}
for (var i = 0; i < rowCount; i++)
{
if (!CanFetchFromReader(reader))
{
yield break;
}
object[] values;
try
{
if (reader.Read())
{
values = BuildValueArray(reader, fieldTypes, prefetch);
}
else
{
reader.Close();
yield break;
}
}
catch
{
if (!reader.IsClosed)
{
reader.Close();
}
throw;
}
yield return values;
}
}
public static int ValidateUser(string email, string wachtwoord)
{
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT userid, gebruikersnaam FROM gebruiker WHERE gebruikersnaam = :pemail AND wachtwoord = :pwachtwoord";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("email", email);
cmd.Parameters.Add("wachtwoord", wachtwoord);
reader = cmd.ExecuteReader();
while (reader.Read())
{
var emailCheck = reader.GetString(1);
if (emailCheck == email)
{
return reader.GetInt32(0);
}
cmd.Dispose();
connection.Dispose();
reader.Close();
return 0;
}
return 0;
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
return 0;
}
}
}
public static List<ListItem> ObtainRegisseurs()
{
List<ListItem> regisseurList = new List<ListItem>();
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT r.naam, r.regisseurid FROM regisseur r";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
reader = cmd.ExecuteReader();
while (reader.Read())
{
regisseurList.Add(new ListItem(reader.GetString(0), reader.GetInt32(1).ToString()));
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
return regisseurList;
}
public static List<Film> ObtainFilms()
{
List<Film> films = new List<Film>();
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT filmid, titel FROM film";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
string titel = reader.GetString(1);
films.Add(new Film(id.ToString(), titel));
}
return films;
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
return null;
}
}
}
public DropDownList populate_list(DropDownList ddlistGen, string strSql, string strValue, string strText, string strExtraValue, string strExtraText)
{
objConnection = open_connection();
if (open_con == true)
{
try
{
objCommand = new OracleCommand(strSql, objConnection);
objDatareader = objCommand.ExecuteReader();
ddlistGen.DataSource = objDatareader;
ddlistGen.DataTextField = strText;
ddlistGen.DataValueField = strValue;
ddlistGen.DataBind();
objDatareader.Close();
objConnection.Close();
if (strExtraText != "")
{
ddlistGen.Items.Insert(0, new ListItem(strExtraText, strExtraValue));
ddlistGen.SelectedIndex = 0;
}
list_bind = true;
return ddlistGen;
}
catch (OracleException objError)
{
if (objError.Message.Substring(0, 21) == "Table does not exist.")
{
ErrorStr = ErrorMsg(objConnection, "MSG1201");
}
if (objError.Message.Substring(59, 30) == "ORA-00904: invalid column name")
{
ErrorStr = ErrorMsg(objConnection, "MSG1202");
}
list_bind = false;
return null;
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
}
else
{
return null;
}
}
/// <summary>
/// Permet de sélectionner des enregistrements suivant un critère de recherche et de trier ces résultats
/// </summary>
/// <param name="connection">Objet de connexion à la base de données</param>
/// <param name="strWhere">Critère de recherche</param>
/// <param name="strSort">Critère de trie</param>
/// <returns></returns>
public bool SelectRight(OracleConnection connection, string strWhere, string strSort)
{
OracleCommand sqlCommand = null;
OracleDataReader sqlReader = null;
string sql = "SELECT ro.id_role, ro.role, ro.date_creation, ro.date_modification, ro.commentary, ro.activation, ri.id_profile FROM MOU01.role ro, MOU01.right ri";
if (strWhere != "")
{
sql = sql + " WHERE " + strWhere;
}
if (strSort != "")
{
sql = sql + " ORDER BY " + strSort;
}
bool bResult = false;
try
{
sqlCommand = new OracleCommand(sql, connection);
sqlReader = sqlCommand.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
Role newRole = new Role();
newRole.Id = sqlReader.GetInt64(0);
newRole.Name = sqlReader.GetString(1);
newRole.DateCreation = sqlReader.GetDateTime(2);
if (!sqlReader.IsDBNull(3))
{
newRole.DateModification = sqlReader.GetDateTime(3);
}
if (!sqlReader.IsDBNull(4))
{
newRole.Commentary = sqlReader.GetString(4);
}
if (sqlReader.GetInt16(5) == AccountMgmt.Common.Constants.ActivationLevel)
{
newRole.Activation = true;
}
else
{
newRole.Activation = false;
}
newRole.IdProfile = sqlReader.GetInt64(6);
m_listRole.Add(newRole);
}
bResult = true;
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
finally
{
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
public static void UpdateElementTransform(IfcStore _model, string projectNumber, string projectName)
{
DBOperation.beginTransaction();
DBOperation.commitInterval = 5000;
string currStep = string.Empty;
BIMRLCommon _refBIMRLCommon = new BIMRLCommon();
int commandStatus = -1;
int currInsertCount = 0;
OracleCommand command = new OracleCommand(" ", DBOperation.DBConn);
XbimMatrix3D m3D = new XbimMatrix3D();
if (string.IsNullOrEmpty(projectName))
{
projectName = projectNumber + " - Federated";
}
string modelName;
if (!string.IsNullOrEmpty(_model.FileName))
{
modelName = Path.GetFileNameWithoutExtension(_model.FileName);
}
else
{
modelName = projectNumber + " - " + projectName;
}
command.CommandText = "SELECT FEDERATEDID FROM bimrl_federatedmodel WHERE MODELNAME = '" + modelName + "' AND PROJECTNUMBER='" + projectNumber + "' AND PROJECTNAME='" + projectName + "'";
object oFedID = command.ExecuteScalar();
if (oFedID == null)
{
return;
}
int fedID = int.Parse(oFedID.ToString());
command.CommandText = "SELECT ELEMENTID, LINENO FROM " + DBOperation.formatTabName("bimrl_element", fedID) + " WHERE GEOMETRYBODY IS NOT NULL";
OracleDataReader reader = command.ExecuteReader();
SortedDictionary <int, string> elemList = new SortedDictionary <int, string>();
while (reader.Read())
{
string elemid = reader.GetString(0);
int lineNo = reader.GetInt32(1);
elemList.Add(lineNo, elemid);
}
reader.Close();
Xbim3DModelContext context = new Xbim3DModelContext(_model);
foreach (KeyValuePair <int, string> elemListItem in elemList)
{
//IEnumerable<XbimGeometryData> geomDataList = _model.GetGeometryData(elemListItem.Key, XbimGeometryType.TriangulatedMesh);
IIfcProduct product = _model.Instances[elemListItem.Key] as IIfcProduct;
IEnumerable <XbimShapeInstance> shapeInstances = context.ShapeInstancesOf(product).Where(x => x.RepresentationType == XbimGeometryRepresentationType.OpeningsAndAdditionsIncluded);
if (shapeInstances.Count() == 0)
{
continue; // SKip if the product has no geometry
}
XbimMeshGeometry3D prodGeom = new XbimMeshGeometry3D();
IXbimShapeGeometryData shapeGeom = context.ShapeGeometry(shapeInstances.FirstOrDefault().ShapeGeometryLabel);
//XbimModelExtensions.Read(prodGeom, shapeGeom.ShapeData, shapeInstances.FirstOrDefault().Transformation);
//XbimGeometryData sdoGeomData = geomDataList.First();
//m3D = sdoGeomData.Transform;
//m3D = XbimMatrix3D.FromArray(sdoGeomData.DataArray2); // Xbim 3.0 removes Tranform property
m3D = shapeInstances.FirstOrDefault().Transformation;
string sqlStmt = "update " + DBOperation.formatTabName("BIMRL_ELEMENT", fedID) + " set TRANSFORM_COL1=:1, TRANSFORM_COL2=:2, TRANSFORM_COL3=:3, TRANSFORM_COL4=:4"
+ " Where elementid = '" + elemListItem.Value + "'";
// int status = DBOperation.updateGeometry(sqlStmt, sdoGeomData);
currStep = sqlStmt;
command.CommandText = sqlStmt;
try
{
OracleParameter[] sdoGeom = new OracleParameter[4];
for (int i = 0; i < sdoGeom.Count(); ++i)
{
sdoGeom[i] = command.Parameters.Add((i + 1).ToString(), OracleDbType.Object);
sdoGeom[i].Direction = ParameterDirection.Input;
sdoGeom[i].UdtTypeName = "MDSYS.SDO_GEOMETRY";
sdoGeom[i].Size = 1;
}
SdoGeometry trcol1 = new SdoGeometry();
trcol1.Dimensionality = 3;
trcol1.LRS = 0;
trcol1.GeometryType = (int)SdoGeometryTypes.GTYPE.POINT;
int gType = trcol1.PropertiesToGTYPE();
SdoPoint trcol1V = new SdoPoint();
trcol1V.XD = m3D.M11;
trcol1V.YD = m3D.M12;
trcol1V.ZD = m3D.M13;
trcol1.SdoPoint = trcol1V;
sdoGeom[1].Value = trcol1;
SdoGeometry trcol2 = new SdoGeometry();
trcol2.Dimensionality = 3;
trcol2.LRS = 0;
trcol2.GeometryType = (int)SdoGeometryTypes.GTYPE.POINT;
gType = trcol2.PropertiesToGTYPE();
SdoPoint trcol2V = new SdoPoint();
trcol2V.XD = m3D.M21;
trcol2V.YD = m3D.M22;
trcol2V.ZD = m3D.M23;
trcol2.SdoPoint = trcol2V;
sdoGeom[2].Value = trcol2;
SdoGeometry trcol3 = new SdoGeometry();
trcol3.Dimensionality = 3;
trcol3.LRS = 0;
trcol3.GeometryType = (int)SdoGeometryTypes.GTYPE.POINT;
gType = trcol3.PropertiesToGTYPE();
SdoPoint trcol3V = new SdoPoint();
trcol3V.XD = m3D.M31;
trcol3V.YD = m3D.M32;
trcol3V.ZD = m3D.M33;
trcol3.SdoPoint = trcol3V;
sdoGeom[3].Value = trcol3;
SdoGeometry trcol4 = new SdoGeometry();
trcol4.Dimensionality = 3;
trcol4.LRS = 0;
trcol4.GeometryType = (int)SdoGeometryTypes.GTYPE.POINT;
gType = trcol4.PropertiesToGTYPE();
SdoPoint trcol4V = new SdoPoint();
trcol4V.XD = m3D.OffsetX;
trcol4V.YD = m3D.OffsetY;
trcol4V.ZD = m3D.OffsetZ;
trcol4.SdoPoint = trcol4V;
sdoGeom[4].Value = trcol4;
commandStatus = command.ExecuteNonQuery();
command.Parameters.Clear();
currInsertCount++;
if (currInsertCount % DBOperation.commitInterval == 0)
{
//Do commit at interval but keep the long transaction (reopen)
DBOperation.commitTransaction();
}
}
catch (OracleException e)
{
string excStr = "%%Error - " + e.Message + "\n\t" + currStep;
_refBIMRLCommon.StackPushError(excStr);
//command.Dispose(); // Log Oracle error and continue
command = new OracleCommand(" ", DBOperation.DBConn);
// throw;
}
catch (SystemException e)
{
string excStr = "%%Insert Error - " + e.Message + "\n\t" + currStep;
_refBIMRLCommon.StackPushError(excStr);
throw;
}
DBOperation.commitTransaction();
command.Dispose();
}
}
public String ProcessNews()
{
using (Logger l = new Logger("PipeCatcher"))
{
OraCmd.CommandText = ProcName;
OraCmd.CommandType = System.Data.CommandType.StoredProcedure;
OraCmd.Parameters.Clear();
OraCmd.Parameters.Add(new OracleParameter("NRECID", OracleDbType.Decimal, System.Data.ParameterDirection.Input)).Value = RecId;
OraCmd.Parameters.Add(new OracleParameter("REFCURSOR", OracleDbType.RefCursor, System.Data.ParameterDirection.Output));
// OraCmd.Parameters["NRECID"].Value = RecId;
if (OraCmd.Connection.State != System.Data.ConnectionState.Closed)
{
OraCmd.Connection.Close();
}
OraCmd.Connection.Open();
OraReader = OraCmd.ExecuteReader();
var str = String.Format("\nCALL {0}({1})", ProcName, RecId);
if (OraReader.HasRows)
{
var f = new FlexHelper("PipeCatcher.Call." + ProcName);
str += "+++";
OraReader.Read();
f.AddArg("@ProcName", ProcName);
for (int i = 0; i < OraReader.FieldCount; i++)
{
f.AddArg(OraReader.GetName(i), OraReader[i]);
str += "\n" + OraReader.GetName(i) + "\t: " + OraReader[i];
}
f.Fire(Program.CoreGate);
}
else
{
str += "---";
}
OraReader.Close();
return str;
}
}
private void ReadOracleVersion(string dsn)
{
string version, compile, oracleversion, error;
try
{
OracleConnectionStringBuilder csb = new OracleConnectionStringBuilder();
csb.DataSource = dsn;
csb.UserID = _user;
csb.Password = _password;
OracleConnection conn = new OracleConnection(csb.ConnectionString);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT build,svcpack,compile FROM sysadm.vversion";
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (rdr.Read())
{
version = rdr.GetString(rdr.GetOrdinal("BUILD")) + "." + rdr.GetString(rdr.GetOrdinal("SVCPACK"));
compile = rdr.GetString(rdr.GetOrdinal("COMPILE")).ToString();
}
else
{
version = "-";
compile = "-";
}
cmd.CommandText = "SELECT MAX(v.version) version FROM sys.product_component_version v WHERE upper(v.product) LIKE '%ORACLE%'";
OracleDataReader rdr2 = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (rdr2.Read())
{
oracleversion = rdr2.GetString(rdr2.GetOrdinal("VERSION"));
}
else
{
oracleversion = "-";
}
rdr.Close();
rdr.Dispose();
rdr2.Close();
rdr2.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
error = " On ";
Databases.Add(new VersionType(dsn, version, compile, oracleversion, string.Empty, string.Empty, string.Empty, error, string.Empty, string.Empty));
}
catch (OracleException ex)
{
var ver = from d in previousDatabases
where d._name == dsn
select d._version;
version = String.Join("", ver);
var comp = from d in previousDatabases
where d._name == dsn
select d._compile;
compile = String.Join("", comp);
var ora = from d in previousDatabases
where d._name == dsn
select d._oracleversion;
oracleversion = String.Join("", ora);
if (ex.Number == 12514)
{
error = " Off ";
}
else
{
error = ex.Message.Replace("\r\n", string.Empty);
}
Databases.Add(new VersionType(dsn, version, compile, oracleversion, string.Empty, string.Empty, string.Empty, error, string.Empty, string.Empty));
}
}
private bool MoveNextWay()
{
if (_way_reader == null)
{
OracleCommand way_command = new OracleCommand("select * from way order by id");
way_command.Connection = _connection;
_way_reader = way_command.ExecuteReader();
if (!_way_reader.Read())
{
_way_reader.Close();
}
OracleCommand way_tag_command = new OracleCommand("select * from way_tags order by way_id");
way_tag_command.Connection = _connection;
_way_tag_reader = way_tag_command.ExecuteReader();
if (!_way_tag_reader.IsClosed && !_way_tag_reader.Read())
{
_way_tag_reader.Close();
}
OracleCommand way_node_command = new OracleCommand("select * from way_nodes order by way_id,sequence_id");
way_node_command.Connection = _connection;
_way_node_reader = way_node_command.ExecuteReader();
if (!_way_node_reader.IsClosed && !_way_node_reader.Read())
{
_way_node_reader.Close();
}
}
// read next way.
if (!_way_reader.IsClosed)
{
// load/parse data.
long id = _way_reader.GetInt64(0);
long changeset_id = _way_reader.GetInt64(1);
DateTime timestamp = _way_reader.GetDateTime(2);
bool visible = _way_reader.GetInt64(3) == 1;
long version = _way_reader.GetInt64(4);
string user = _way_reader.GetString(5);
long uid = _way_reader.GetInt64(6);
SimpleWay way = new SimpleWay();
way.Id = id;
way.ChangeSetId = changeset_id;
way.TimeStamp = timestamp;
way.UserId = uid;
way.UserName = user;
way.Version = (ulong)version;
way.Visible = visible;
if (!_way_tag_reader.IsClosed)
{
long returned_id = _way_tag_reader.GetInt64(_way_tag_reader.GetOrdinal("way_id"));
while (returned_id == way.Id.Value)
{
if (way.Tags == null)
{
way.Tags = new Dictionary<string, string>();
}
string key = _way_tag_reader.GetString(1);
string value = _way_tag_reader.GetString(2);
way.Tags.Add(key, value);
if (!_way_tag_reader.Read())
{
_way_tag_reader.Close();
returned_id = -1;
}
else
{
returned_id = _way_tag_reader.GetInt64(0);
}
}
}
if (!_way_node_reader.IsClosed)
{
long returned_id = _way_node_reader.GetInt64(_way_node_reader.GetOrdinal("way_id"));
while (returned_id == way.Id.Value)
{
if (way.Nodes == null)
{
way.Nodes = new List<long>();
}
long node_id = _way_node_reader.GetInt64(1);
way.Nodes.Add(node_id);
if (!_way_node_reader.Read())
{
_way_node_reader.Close();
returned_id = -1;
}
else
{
returned_id = _way_node_reader.GetInt64(0);
}
}
}
// set the current variable!
_current = way;
// advance the reader(s).
if (!_way_reader.Read())
{
_way_reader.Close();
}
if (!_way_tag_reader.IsClosed && !_way_tag_reader.Read())
{
_way_tag_reader.Close();
}
if (!_way_node_reader.IsClosed && !_way_node_reader.Read())
{
_way_node_reader.Close();
}
return true;
}
else
{
_way_reader.Close();
_way_reader.Dispose();
_way_reader = null;
_way_tag_reader.Close();
_way_tag_reader.Dispose();
_way_tag_reader = null;
_current_type = SimpleOsmGeoType.Relation;
return false;
}
}
public static List <EstiloColumna> GetEstilos(string formName, string gridName)
{
List <EstiloColumna> listaEstilos = new List <EstiloColumna>();
OracleCommand command = Sesion.Instancia.Conexion.CrearCommand();
command.CommandText = @"SELECT b.DGVCOLUMN, b.TEXTO_COLUMNA, nvl(b.FORMATO_COLUMNA,' '), b.ALINEACION_COLUMNA, b.ANCHO_COLUMNA,nvl( b.MULTILINEA_COLUMNA,0) ,a.DISLPLAYINDEX
FROM P_GF_FORMULARIOSGRILLAS A, P_GF_ESTILOCOLUMNAS B
WHERE a.dgvcolumn =b.dgvcolumn AND upper(a.form) = upper(:p_FormName) AND upper(a.datagridview) = upper(:p_gridName)
ORDER BY DISLPLAYINDEX ";
command.Parameters.Add(new OracleParameter("p_FormName", OracleDbType.Varchar2, formName, System.Data.ParameterDirection.Input));
command.Parameters.Add(new OracleParameter("p_gridName", OracleDbType.Varchar2, gridName, System.Data.ParameterDirection.Input));
OracleDataReader odr = null;
try
{
odr = command.ExecuteReader();
EstiloColumna estilo;
string alineacion = string.Empty;
int multilinea = 0;
while (odr.Read())
{
estilo = new EstiloColumna();
estilo.NombreColumna = odr.GetString(0);
estilo.TextoColumna = odr.GetString(1);
estilo.Formato = odr.GetString(2);
alineacion = odr.GetString(3);
switch (alineacion)
{
case "LEFT":
estilo.Alineacion = AlineacionColumna.Izq;
break;
case "RIGHT":
estilo.Alineacion = AlineacionColumna.Der;
break;
case "CENTER":
estilo.Alineacion = AlineacionColumna.Centro;
break;
}
estilo.Ancho = (int)odr.GetDecimal(4);
multilinea = (int)odr.GetDecimal(5);
if (multilinea == 0)
{
estilo.MultiLinea = false;
}
else
{
estilo.MultiLinea = true;
}
listaEstilos.Add(estilo);
}
}
catch (OracleException e)
{
PistaMgr.Instance.Error("SisFalla", e);
}
catch (Exception e)
{
PistaMgr.Instance.Error("SisFalla", e);
}
finally
{
if (odr != null)
{
odr.Close();
odr.Dispose();
}
Sesion.Instancia.Conexion.DisposeCommand(command);
}
return(listaEstilos);
}
/// <summary>
/// Supprime l'affectation entre les rôles du profile et l'utilisateur, revoke aussi les rôles à l'utilisateur
/// </summary>
/// <param name="connection"></param>
/// <param name="strUserOracle">UserOrcale</param>
/// <returns></returns>
public bool Delete(OracleConnection connection, string strUserOracle)
{
bool bResult = false;
// Start a local transaction
OracleCommand command = new OracleCommand();
command.Connection = connection;
OracleDataReader sqlReader = null;
OracleTransaction transaction = null;
string sql = "", strRoleList = "";
try
{
//lock de la table affectation
sql = "LOCK TABLE MOU01.affectation IN EXCLUSIVE MODE";
command.CommandText = sql;
command.ExecuteNonQuery();
try
{
transaction = connection.BeginTransaction();
//Recherche des rôles à REVOKER
sql = "SELECT nom, SUM(nb) FROM(" +
"(SELECT role.role nom, '+'||COUNT(role.role) nb FROM MOU01.role, MOU01.right, MOU01.profile, MOU01.affectation " +
"WHERE Role.id_role = Right.id_role " +
" AND right.id_profile = profile.id_profile " +
" AND affectation.id_profile = profile.id_profile " +
" AND affectation.id_user_=" + this.m_dIdUser.ToString("0") + " " +
"GROUP BY role.role) " +
"UNION ALL " +
"(SELECT role.role nom, '-'||COUNT(role.role) nb FROM MOU01.role, MOU01.right " +
"WHERE Role.id_role = Right.id_role" +
" AND right.id_profile=" + this.m_dIdProfile.ToString("0") + " " +
"GROUP BY role.role)) GROUP BY nom HAVING SUM(nb)=0";
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
if (strRoleList != "")
{
strRoleList += ", ";
}
strRoleList = strRoleList + sqlReader.GetString(0);
}
//revoke les roles du profile à l'utilisateur
if (strRoleList != "")
{
sql = "REVOKE " + strRoleList + " FROM " + strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
//mise à jour de la table affectation
sql = "DELETE FROM MOU01.affectation WHERE id_user_=" + this.m_dIdUser.ToString("0") + " AND id_profile=" + this.m_dIdProfile.ToString("0");
command.CommandText = sql;
command.ExecuteNonQuery();
transaction.Commit();
sql = "COMMIT";
command.CommandText = sql;
command.ExecuteNonQuery();
bResult = true;
}
catch (Exception error)
{
transaction.Rollback();
sql = "ROLLBACK";
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("Problème lors de la suppression de l'affectation de l'utilisateur au profile avec l'erreur : " + error.Message);
}
}
catch (Exception error)
{
sql = "ROLLBACK";
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("Problème lors du lock de la table Affectation avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
public static void Main(string[] args)
{
string connectionString =
"Data Source=testdb;" +
"User ID=scott;" +
"Password=tiger;";
OracleConnection connection = null;
connection = new OracleConnection(connectionString);
connection.Open();
Console.WriteLine("Setup test package and data...");
OracleCommand cmddrop = connection.CreateCommand();
cmddrop.CommandText = "DROP TABLE TESTTABLE";
try {
cmddrop.ExecuteNonQuery();
}
catch (OracleException e) {
Console.WriteLine("Ignore this error: " + e.Message);
}
cmddrop.Dispose();
cmddrop = null;
Console.WriteLine("Create table TESTTABLE...");
OracleCommand cmd = connection.CreateCommand();
// create table TESTTABLE
cmd.CommandText =
"create table TESTTABLE (\n" +
" col1 numeric(18,0),\n" +
" col2 varchar(32),\n" +
" col3 date, col4 blob)";
cmd.ExecuteNonQuery();
Console.WriteLine("Insert 3 rows...");
// insert some rows into TESTTABLE
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(45, 'Mono', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(136, 'Fun', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(526, 'System.Data.OracleClient', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
Console.WriteLine("commit...");
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Update blob...");
// update BLOB and CLOB columns
OracleCommand select = connection.CreateCommand();
select.Transaction = connection.BeginTransaction();
select.CommandText = "SELECT col1, col4 FROM testtable FOR UPDATE";
OracleDataReader readerz = select.ExecuteReader();
if (!readerz.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
// update blob_value
Console.WriteLine(" Update BLOB column on table testtable...");
OracleLob blob = readerz.GetOracleLob(1);
byte[] bytes = new byte[6] {
0x31, 0x32, 0x33, 0x34, 0x35, 0x036
};
blob.Write(bytes, 0, bytes.Length);
blob.Close();
readerz.Close();
select.Transaction.Commit();
select.Dispose();
select = null;
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Create package...");
// create Oracle package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE TestTablePkg\n" +
"AS\n" +
" TYPE T_CURSOR IS REF CURSOR;\n"+
"\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR);\n"+
"END TestTablePkg;";
cmd.ExecuteNonQuery();
// create Oracle package body for package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +
" IS\n" +
" BEGIN\n" +
" OPEN tableCursor FOR\n" +
" SELECT *\n" +
" FROM TestTable;\n" +
" END GetData;\n" +
"END TestTablePkg;";
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = null;
Console.WriteLine("Set up command and parameters to call stored proc...");
OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection);
command.CommandType = CommandType.StoredProcedure;
OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
Console.WriteLine("Execute...");
command.ExecuteNonQuery();
Console.WriteLine("Get OracleDataReader for cursor output parameter...");
OracleDataReader reader = (OracleDataReader)parameter.Value;
Console.WriteLine("Read data***...");
int r = 0;
while (reader.Read())
{
Console.WriteLine("Row {0}", r);
for (int f = 0; f < reader.FieldCount; f++)
{
Console.WriteLine("FieldType: " + reader.GetFieldType(f).ToString());
object val = "";
if (f == 3)
{
Console.WriteLine("blob");
//OracleLob lob = reader.GetOracleLob (f);
//val = lob.Value;
val = reader.GetValue(f);
if (((byte[])val).Length == 0)
{
val = "Empty Blob (Not Null)";
}
else
{
val = BitConverter.ToString((byte[])val);
}
}
else
{
val = reader.GetOracleValue(f);
}
Console.WriteLine(" Field {0} Value: {1}", f, val);
}
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
Console.WriteLine("Clean up...");
reader.Close();
reader = null;
command.Dispose();
command = null;
connection.Close();
connection = null;
}
private string GetDayCode(int dday)
{
OracleConnection oraDBConn = null;
OracleCommand oraCmd = null;
ILogger logger = null;
OracleDataReader dr = null;
string dayCode = String.Empty;
try
{
Database d = OPS.Components.Data.DatabaseFactory.GetDatabase();
logger = DatabaseFactory.Logger;
System.Data.IDbConnection DBCon = d.GetNewConnection();
oraDBConn = (OracleConnection)DBCon;
oraDBConn.Open();
if (oraDBConn.State == System.Data.ConnectionState.Open)
{
oraCmd = new OracleCommand();
oraCmd.Connection = (OracleConnection)oraDBConn;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendFormat("select DDAY_CODE from DAYS_DEF where DDAY_ID = {0}", dday.ToString());
oraCmd.CommandText = sqlQuery.ToString();
//oraCmd.CommandText = "select CGRP_ID from groups_childs where CGRP_CHILD = '" + unit +"'";
dr = oraCmd.ExecuteReader();
if (dr.Read())
{
int iOrdinal = dr.GetOrdinal("DDAY_CODE");
dayCode = dr.GetString(iOrdinal);
}
}
}
catch (Exception e)
{
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
dr = null;
}
if (oraCmd != null)
{
oraCmd.Dispose();
oraCmd = null;
}
if (oraDBConn != null)
{
oraDBConn.Close();
oraDBConn.Dispose();
oraDBConn = null;
}
}
return(dayCode);
}
//*-----------------------------------------------------------
private void OnMouseDoubleClick()
{
if (dataGrid2.SelectedItems.Count <= 0)
{
return;
}
myViewModel?.Clear();
DataRowView drv = (DataRowView)dataGrid2.SelectedItem;
pnm_empno.Text = (string)drv.Row["bas_empno"];
bas_name.Text = (string)drv.Row["bas_name"];
//--DB Handling(Start)-------------------------------------
try
{
con = Utility.SetOracleConnection();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = SQLStatement.SelectSQL;
cmd.Parameters.Add("cmt_empno", OracleDbType.Varchar2).Value = pnm_empno.Text;
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
var data = new UcSubC30ViewModel
{
Pnm_empno = dr.GetString(0),
Pnm_date = Utility.FormatDate(dr.GetString(1)),
Pnm_type = dr.IsDBNull(2) ? "" : dr.GetString(2),
Pnm_content = dr.IsDBNull(3) ? "" : dr.GetString(3),
Pnm_pos = dr.IsDBNull(4) ? "" : dr.GetString(4),
Pnm_dept = dr.IsDBNull(5) ? "" : dr.GetString(5),
Key1 = dr.GetString(0),
Key2 = dr.GetString(1),
DataStatus = ""
};
myViewModel.Add(data);
}
dr.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
finally
{
if (con != null)
{
con.Close();
}
}
//--DB Handling(End)-------------------------------------
SearchCount.Text = myViewModel.Count.ToString();
if (myViewModel.Count == 0)
{
UserMessage.Text = "조건을 만족하는 자료가 없습니다.";
Utility.SetFuncBtn(MainBtn, "1");
return;
}
UserMessage.Text = "자료가 정상적으로 조회 되었습니다.";
Utility.SelectingFocusingGridControl(dataGrid, tableView, 0);
Utility.SetFuncBtn(MainBtn, "2");
}
private bool EvalZonePlateRules(string plate, DateTime dt, ref int iTipoArticulo, int iUnitID, int iGrpID)
{
OracleConnection oraDBConn = null;
OracleCommand oraCmd = null;
ILogger logger = null;
OracleDataReader dr = null;
bool bValidRuleFound = false;
try
{
Database d = OPS.Components.Data.DatabaseFactory.GetDatabase();
logger = DatabaseFactory.Logger;
System.Data.IDbConnection DBCon = d.GetNewConnection();
oraDBConn = (OracleConnection)DBCon;
oraDBConn.Open();
if (oraDBConn.State == System.Data.ConnectionState.Open)
{
oraCmd = new OracleCommand();
oraCmd.Connection = (OracleConnection)oraDBConn;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendFormat("select* from pedestrian_zone_plate_rules where PDPR_VEHICLEID= '{0}'", plate);
oraCmd.CommandText = sqlQuery.ToString();
//oraCmd.CommandText = "select* from pedestrian_zone_plate_rules where PDPR_VEHICLEID= '" + plate + "'";
dr = oraCmd.ExecuteReader();
while (dr.Read())
{
bValidRuleFound = true;
int iOrdinal = 0;
if (iUnitID != -1 && iGrpID != -1)
{
iOrdinal = dr.GetOrdinal("PDPR_UNI_ID");
if (dr.IsDBNull(iOrdinal) == false)
{
if (iUnitID != int.Parse(dr.GetInt32(iOrdinal).ToString()))
{
bValidRuleFound = false;
}
}
iOrdinal = dr.GetOrdinal("PDPR_GRP_ID");
if (dr.IsDBNull(iOrdinal) == false)
{
if (iGrpID != int.Parse(dr.GetInt32(iOrdinal).ToString()))
{
bValidRuleFound = false;
}
}
}
else
{
bValidRuleFound = false; // A NO SER QUE SOLO TENGA UNA CÄMARA DE ENTRADA
}
iOrdinal = dr.GetOrdinal("PDPR_DDAY_ID");
if (dr.IsDBNull(iOrdinal) == false)
{
int dday = int.Parse(dr.GetInt32(iOrdinal).ToString());
if (IsTypeOfDay(dt, dday) == false)
{
bValidRuleFound = false;
}
else
{
bool bTimeCheck = false;
iOrdinal = dr.GetOrdinal("PDPR_DATE_INI");
if (dr.IsDBNull(iOrdinal) == false)
{
string date = dr.GetOracleValue(iOrdinal).ToString();
date = date.Substring(0, 10); // 10 es la longitud de DD/MM/YYYY
DateTime dtInit = DateTime.Parse(date);
if (dt < dtInit)
{
bValidRuleFound = false;
bTimeCheck = false;
}
else
{
bTimeCheck = true;
}
}
else
{
bTimeCheck = true;
}
iOrdinal = dr.GetOrdinal("PDPR_DATE_END");
if (dr.IsDBNull(iOrdinal) == false)
{
string date = dr.GetOracleValue(iOrdinal).ToString();
date = date.Substring(0, 10); // 10 es la longitud de DD/MM/YYYY
DateTime dtEnd = DateTime.Parse(date);
if (dt > dtEnd)
{
bValidRuleFound = false;
bTimeCheck = false;
}
else
{
bTimeCheck = true;
}
}
else
{
bTimeCheck = true;
}
iOrdinal = dr.GetOrdinal("PDPR_TIM_ID");
if (dr.IsDBNull(iOrdinal) == false)
{
int iTimId = int.Parse(dr.GetInt32(iOrdinal).ToString());
if (bTimeCheck)
{
int iMinutes = dt.Minute + dt.Hour * 60;
int iMinutesMin = GetTimMinutesMin(iTimId);
int iMinutesMax = GetTimMinutesMax(iTimId);
if (iMinutesMin <= iMinutes && iMinutes <= iMinutesMax)
{
}
else
{
bValidRuleFound = false;
}
}
}
}
}
if (bValidRuleFound == true)
{
iOrdinal = dr.GetOrdinal("PDPR_DART_ID");
iTipoArticulo = dr.GetInt32(iOrdinal);
}
}
}
}
catch (Exception e)
{
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
dr = null;
}
if (oraCmd != null)
{
oraCmd.Dispose();
oraCmd = null;
}
if (oraDBConn != null)
{
oraDBConn.Close();
oraDBConn.Dispose();
oraDBConn = null;
}
}
return(bValidRuleFound);
}
public PagedObj <KHUYENMAI> QueryPageGiamGiaLoaiHang(string stringConnect, PagedObj <KHUYENMAI> page, string strKey, string maDonVi)
{
var TotalItem = 0;
List <KHUYENMAI> ListKhuyenMai = new List <KHUYENMAI>();
try
{
using (OracleConnection connection = new OracleConnection(stringConnect))
{
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = @"TIMKIEM_KHUYENMAI_PAGINATION";
command.Parameters.Add(@"P_MADONVI", OracleDbType.NVarchar2, 50).Value = maDonVi;
command.Parameters.Add(@"P_TUKHOA", OracleDbType.NVarchar2, 500).Value = strKey.ToString().ToUpper().Trim();
command.Parameters.Add(@"P_PAGENUMBER", OracleDbType.Int32).Value = page.CurrentPage;
command.Parameters.Add(@"P_PAGESIZE", OracleDbType.Int32).Value = page.ItemsPerPage;
command.Parameters.Add(@"P_LOAI_KHUYENMAI", OracleDbType.NVarchar2, 20).Value = TypeBuildCode.GGLOAIHANG.ToString();
command.Parameters.Add(@"P_TOTALITEM", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
command.Parameters.Add(@"CURSOR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
OracleDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
if (dataReader["TOTAL_ITEM"] != null)
{
int.TryParse(dataReader["TOTAL_ITEM"].ToString(), out TotalItem);
}
}
if (dataReader.NextResult())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
KHUYENMAI KhuyenMai = new KHUYENMAI();
if (dataReader["ID"] != null)
{
KhuyenMai.ID = dataReader["ID"].ToString();
}
if (dataReader["MA_KHUYENMAI"] != null)
{
KhuyenMai.MA_KHUYENMAI = dataReader["MA_KHUYENMAI"].ToString();
}
if (dataReader["TUNGAY"] != DBNull.Value)
{
KhuyenMai.TUNGAY = DateTime.Parse(dataReader["TUNGAY"].ToString());
}
if (dataReader["DENNGAY"] != DBNull.Value)
{
KhuyenMai.DENNGAY = DateTime.Parse(dataReader["DENNGAY"].ToString());
}
if (dataReader["THOIGIAN_TAO"] != null)
{
KhuyenMai.THOIGIAN_TAO = dataReader["THOIGIAN_TAO"].ToString();
}
if (dataReader["TUGIO"] != null)
{
KhuyenMai.TUGIO = dataReader["TUGIO"].ToString();
}
if (dataReader["DENGIO"] != null)
{
KhuyenMai.DENGIO = dataReader["DENGIO"].ToString();
}
if (dataReader["MAKHO_KHUYENMAI"] != null)
{
KhuyenMai.MAKHO_KHUYENMAI = dataReader["MAKHO_KHUYENMAI"].ToString();
}
if (dataReader["DIENGIAI"] != null)
{
KhuyenMai.DIENGIAI = dataReader["DIENGIAI"].ToString();
}
if (dataReader["UNITCODE"] != null)
{
KhuyenMai.UNITCODE = dataReader["UNITCODE"].ToString();
}
if (dataReader["TRANGTHAI"] != DBNull.Value)
{
int TRANGTHAI = 0;
int.TryParse(dataReader["TRANGTHAI"].ToString(), out TRANGTHAI);
KhuyenMai.TRANGTHAI = TRANGTHAI;
}
if (dataReader["I_CREATE_DATE"] != DBNull.Value)
{
KhuyenMai.I_CREATE_DATE = DateTime.Parse(dataReader["I_CREATE_DATE"].ToString());
}
ListKhuyenMai.Add(KhuyenMai);
}
}
}
}
dataReader.Close();
page.Data = ListKhuyenMai;
page.TotalItems = TotalItem;
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
connection.Dispose();
}
}
}
catch
{
throw new Exception("Lỗi không thể truy xuất chương trình khuyến mãi");
}
return(page);
}
private void MainForm_Load(object sender, EventArgs e)
{
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
//dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
loadImageList();
tss_dbname.Text = LogDB.ConnectionDatabase;
tss_version.Text = LogDB.dbversion;
this.Text += " [ " + LogDB.ConnectionDatabase + " ]";
//If now choose database is oracle,loading all oracle database's user space,behave inside user
if (LogDB.ORACLE.Equals(LogDB.ConnectionDatabase))
{
//string currendUser = ORACLEDBHelp.ExecuteScalar(LogDB.ORACLE_CURRENT_USERNAME).ToString();
string currendUser = string.Empty;
OracleDataReader reader = ORACLEDBHelp.ExecuteDataReader(LogDB.ORACLE_CURRENT_USERNAME);
if (reader.Read())
{
currendUser = reader[0].ToString();
}
reader.Close();
OracleDataReader oracle_table_reader = ORACLEDBHelp.ExecuteDataReader(LogDB.ORACLE_CURRENT_USERNAME_TABLE);
List <string> tabName = new List <string>();
while (oracle_table_reader.Read())
{
tabName.Add(oracle_table_reader[0].ToString());
}
if (!oracle_table_reader.IsClosed)
{
oracle_table_reader.Close();
}
OracleDataReader oracleReader = ORACLEDBHelp.ExecuteDataReader(LogDB.ORACLE_ALL_USER_SCRIPT);
while (oracleReader.Read())
{
if (oracleReader[0].ToString().Equals(currendUser))
{
//If current user is now loaded
TreeNode nodeUser = new TreeNode(oracleReader[0].ToString() + "(Current user)");
nodeUser.ImageIndex = 3;
TreeNode nodeTable = new TreeNode("Table");
foreach (string item in tabName)
{
TreeNode nodeInnerTable = new TreeNode(item);
nodeInnerTable.ImageIndex = 0;
nodeTable.Nodes.Add(nodeInnerTable);
}
nodeUser.Nodes.Add(nodeTable);
nodeUser.ForeColor = Color.Red;
tv_allDB.Nodes.Add(nodeUser);
}
else
{
//Loading all user information
TreeNode nodeUser = new TreeNode(oracleReader[0].ToString());
nodeUser.ImageIndex = 3;
TreeNode nodeTable = new TreeNode("Low permissions");
nodeTable.ImageIndex = 2;
nodeTable.ForeColor = Color.Gray;
nodeUser.Nodes.Add(nodeTable);
tv_allDB.Nodes.Add(nodeUser);
}
}
if (!oracleReader.IsClosed)
{
oracleReader.Close();
}
}
//If now choose database is sqlserver,loading all sqlserver database,behave system database
else if (LogDB.SQLSERVER.Equals(LogDB.ConnectionDatabase))
{
SqlDataReader sqlserverReader = SQLSERVERDBHelp.ExecuteDataReader(LogDB.SQLSERVER_ALL_TABLE_SCRIPT);
while (sqlserverReader.Read())
{
string databaseName = sqlserverReader[0].ToString();
TreeNode nodeDatabase = new TreeNode(databaseName);
nodeDatabase.ImageIndex = 1;
SqlDataReader sqlReader = SQLSERVERDBHelp.ReBuilderSqlDataReader("select * from " + databaseName + ".dbo.sysobjects where xtype='U'");
while (sqlReader.Read())
{
string tagName = sqlReader[0].ToString();
TreeNode table = new TreeNode(tagName);
table.ImageIndex = 0;
nodeDatabase.Nodes.Add(table);
}
if (!sqlReader.IsClosed)
{
sqlReader.Close();
}
tv_allDB.Nodes.Add(nodeDatabase);
}
if (!sqlserverReader.IsClosed)
{
sqlserverReader.Close();
}
}
//If now choose database is mysql,loading all mysql database,behave system database
else if (LogDB.MYSQL.Equals(LogDB.ConnectionDatabase))
{
MySqlDataReader mysqlReader = MYSQLDBHelp.ExecuteDataReader(LogDB.MYSQL_ALL_TABLE_SCRIPT);
while (mysqlReader.Read())
{
string databaseName = mysqlReader[0].ToString();
TreeNode databaseNode = new TreeNode(databaseName);
databaseNode.ImageIndex = 1;
MySqlDataReader myReader = MYSQLDBHelp.ReBuildMySQLDataReader("use " + databaseName + ";show tables");
while (myReader.Read())
{
TreeNode table = new TreeNode(myReader[0].ToString());
table.ImageIndex = 0;
databaseNode.Nodes.Add(table);
}
if (!myReader.IsClosed)
{
myReader.Close();
}
tv_allDB.Nodes.Add(databaseNode);
}
if (!mysqlReader.IsClosed)
{
mysqlReader.Close();
}
}
}
/// <summary>
/// Méthodes de la classe Profile
/// </summary>
#region
/// <summary>
/// Ajout d'un profile en base
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public bool Add(OracleConnection connection)
{
bool bResult = false;
// Start a local transaction
OracleTransaction transaction = connection.BeginTransaction();
OracleCommand command = new OracleCommand();
command.Connection = connection;
OracleDataReader sqlReader = null;
string sql = "";
try
{
//crée un nouvel Id pour le projet
sql = "SELECT MOU01.SEQ_PROFILE.NEXTVAL FROM DUAL";
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
if (sqlReader.Read())
{
this.m_dIdProfile = long.Parse(sqlReader.GetValue(0).ToString());
}
short dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
if (this.m_bActivation)
{
dActivation = AccountMgmt.Common.Constants.ActivationLevel;
}
else
{
dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
}
string strDateCreation = "TO_DATE ('" + m_dtCreation.Day.ToString("00") + "/" + m_dtCreation.Month.ToString("00") + "/" + m_dtCreation.Year.ToString("0000") + " " + m_dtCreation.Hour.ToString("00") + ":" + m_dtCreation.Minute.ToString("00") + ":" + m_dtCreation.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
//string strDateCreation = "TO_DATE ('" + this.m_dtCreation.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
if (this.m_strOracleProfile == "")
{
this.m_strOracleProfile = "DEFAULT";
}
sql = "INSERT INTO MOU01.profile VALUES (" + this.m_dIdProfile.ToString("0") + ",'" + this.m_strProfile.Replace("'", "''") + "'," + this.m_dIdModule.ToString("0") + "," + strDateCreation + ",NULL,'" + this.m_strCommentary.Replace("'", "''") + "'," + dActivation.ToString("0") + ", '" + this.m_strOracleProfile + "')"; //"', '" + this.m_strRsrcConsoGroup + "')";
command.CommandText = sql;
command.ExecuteNonQuery();
transaction.Commit();
bResult = true;
}
catch (Exception error)
{
transaction.Rollback();
MessageBox.Show("Problème lors de la création d'un profile avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
public bool Sessions(out string[] headings, ref List <sSessionInfo> sessions)
{
headings = null;
sessions.Clear();
_error = string.Empty;
try
{
OracleParameter par;
var cmd = new OracleCommand("ITS.OFFENCE_CAPTURE.GET_CAPTURE_SCREEN", DbConnection);
cmd.CommandType = CommandType.StoredProcedure;
// Inputs
par = new OracleParameter("P_USER_ID", _credentialID);
par.Direction = ParameterDirection.Input;
par.OracleDbType = OracleDbType.Int32;
cmd.Parameters.Add(par);
cmd.Parameters.Add(new OracleParameter("O_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_CURSOR"));
cmd.ExecuteNonQuery();
if (!(cmd.Parameters["O_CURSOR"].Value is System.DBNull))
{
OracleRefCursor rc = (OracleRefCursor)cmd.Parameters["O_CURSOR"].Value;
OracleDataReader rd = rc.GetDataReader();
headings = new string[rd.FieldCount];
for (int i = 0; i < rd.FieldCount; i++)
{
headings[i] = rd.GetName(i);
}
int locationCodeIndex = -1;
int machineIdIndex = -1;
int camDateIndex = -1;
int camSessionIndex = -1;
int nothingDoneIndex = -1;
for (int i = 0; i < headings.Length; i++)
{
if (headings[i].ToUpper() == "LOCATION_CODE")
{
locationCodeIndex = i;
}
else if (headings[i].ToUpper() == "CAM_DATE")
{
camDateIndex = i;
}
else if (headings[i].ToUpper() == "CAM_SESSION")
{
camSessionIndex = i;
}
else if (headings[i].ToUpper() == "NOTHING_DONE")
{
nothingDoneIndex = i;
}
else if (headings[i].ToUpper() == "MACHINE_ID")
{
machineIdIndex = i;
}
}
if (locationCodeIndex < 0)
{
_error += "LOCATION_CODE could not be found in the data.";
}
if (camDateIndex < 0)
{
_error += "CAM_DATE could not be found in the data.";
}
if (camSessionIndex < 0)
{
_error += "CAM_SESSION could not be found in the data.";
}
if (nothingDoneIndex < 0)
{
_error += "NOTHING_DONE could not be found in the data.";
}
if (machineIdIndex < 0)
{
_error += "MACHINE_ID could not be found in the data.";
}
if (_error.Length > 0)
{
rd.Close();
rd.Dispose();
cmd.Dispose();
return(false);
}
sessions = new List <sSessionInfo>();
while (rd.Read())
{
sSessionInfo session;
session.mColumns = new object[rd.FieldCount];
rd.GetValues(session.mColumns);
session.mLocationCode = session.mColumns[locationCodeIndex].ToString();
session.mCamDate = session.mColumns[camDateIndex].ToString(); // YYMMdd = YY/MM/DD
session.mCamSession = session.mColumns[camSessionIndex].ToString();
session.mNothingDone = int.Parse(session.mColumns[nothingDoneIndex].ToString());
session.mNothingDoneCol = nothingDoneIndex;
session.mCamDateCol = camDateIndex;
session.mMachineId = session.mColumns[machineIdIndex].ToString();
sessions.Add(session);
}
rd.Close();
rd.Dispose();
}
else
{
cmd.Dispose();
_error = "No sessions for capturing exist.";
return(false);
}
cmd.Dispose();
}
catch (Exception ex)
{
_error = ex.Message;
ErrorCheckFriendly();
return(false);
}
return(true);
}
public static Film ObtainFilm(string id)
{
Film _film;
string titel = string.Empty;
string beschrijving = string.Empty;
Regisseur filmRegisseur = new Regisseur("test", 1);
string genre = String.Empty;
List<Prijs> filmPrijs = new List<Prijs>();
List<Recensie> filmRecensies = new List<Recensie>();
List<Acteur> cast = new List<Acteur>();
double filmRating = 0;
using (connection = new OracleConnection(connectionstring))
{
//string titel, string beschrijving, Regisseur filmRegisseur, string genre, List<Prijs> filmPrijs,
//List<Recensie> filmRecensies, List<Acteur> cast, double filmRating
query = "SELECT f.titel, f.beschrijving, r.naam, g.naam, r.regisseurid FROM film f, regisseur r, genre g, filmgenreregel fgr WHERE f.regisseurid = r.regisseurid AND f.filmid = :pid AND f.filmid = fgr.filmid AND fgr.genreid = g.genreid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", Convert.ToInt32(id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
titel = reader.GetString(0);
beschrijving = reader.GetString(1);
filmRegisseur = new Regisseur(reader.GetString(2), reader.GetInt32(4));
genre = reader.GetString(3);
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
using (connection = new OracleConnection(connectionstring))
{
//string titel, string beschrijving, Regisseur filmRegisseur, string genre, List<Prijs> filmPrijs,
//List<Recensie> filmRecensies, List<Acteur> cast, double filmRating
query = "SELECT AVG(r.ratingnumber) FROM rating r WHERE r.filmid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", Convert.ToInt32(id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
filmRating = reader.GetInt32(0);
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
using (connection = new OracleConnection(connectionstring))
{
//string titel, string beschrijving, Regisseur filmRegisseur, string genre, List<Prijs> filmPrijs,
//List<Recensie> filmRecensies, List<Acteur> cast, double filmRating
query = "SELECT a.naam, a.acteurid FROM acteur a, filmacteurregel facr, film f WHERE a.acteurid = facr.acteurid AND facr.filmid = :pid AND f.filmid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", Convert.ToInt32(id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
cast.Add(new Acteur(reader.GetString(0), reader.GetInt32(1)));
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
using (connection = new OracleConnection(connectionstring))
{
//string titel, string beschrijving, Regisseur filmRegisseur, string genre, List<Prijs> filmPrijs,
//List<Recensie> filmRecensies, List<Acteur> cast, double filmRating
query = "SELECT p.titel, p.jaar, p.prizeid FROM prijs p, filmprijsregel fpr, film f WHERE p.prizeid = fpr.prizeid AND fpr.filmid = :pid AND f.filmid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", Convert.ToInt32(id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
filmPrijs.Add(new Prijs(reader.GetString(0), reader.GetInt32(1), reader.GetInt32(2)));
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
using (connection = new OracleConnection(connectionstring))
{
//string titel, string beschrijving, Regisseur filmRegisseur, string genre, List<Prijs> filmPrijs,
//List<Recensie> filmRecensies, List<Acteur> cast, double filmRating
query = "SELECT g.gebruikersnaam, g.userid, r.reviewid, r.rbody FROM recensie r, gebruiker g, film f WHERE r.userid = g.userid AND f.filmid = :pid AND r.filmid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", Convert.ToInt32(id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
filmRecensies.Add(new Recensie(new Gebruiker(reader.GetString(0), reader.GetInt32(1)), Convert.ToInt32(id),
reader.GetString(3), reader.GetInt32(2)));
;
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
_film = new Film(titel, beschrijving, filmRegisseur, genre, filmPrijs, filmRecensies, cast, filmRating);
return _film;
}
/// <summary>
/// Permet de sélectionner des enregistrements suivant un critère de recherche et de trier ces résultats
/// </summary>
/// <param name="connection">Objet de connexion à la base de données</param>
/// <returns></returns>
public bool Select(OracleConnection connection, string strWhere, string strSort)
{
OracleCommand sqlCommandProfile = null;
OracleDataReader sqlReaderProfile = null;
//string sqlProfile = "SELECT id_profile, profile, id_module, date_creation, date_modification, commentary, activation, Profile_oracle, Rsrc_conso_group FROM MOU01.profile";
string sqlProfile = "SELECT id_profile, profile, id_module, date_creation, date_modification, commentary, activation, Profile_oracle FROM MOU01.profile";
if (strWhere != "")
{
sqlProfile = sqlProfile + " WHERE " + strWhere;
}
if (strSort != "")
{
sqlProfile = sqlProfile + " ORDER BY " + strSort;
}
bool bResult = false;
try
{
sqlCommandProfile = new OracleCommand(sqlProfile, connection);
sqlReaderProfile = sqlCommandProfile.ExecuteReader();
while (sqlReaderProfile.Read())
{
Profile newProfile = new Profile();
newProfile.Id = sqlReaderProfile.GetInt64(0);
newProfile.Name = sqlReaderProfile.GetString(1);
newProfile.IdModule = sqlReaderProfile.GetInt64(2);
newProfile.DateCreation = sqlReaderProfile.GetDateTime(3);
if (!sqlReaderProfile.IsDBNull(4))
{
newProfile.DateModification = sqlReaderProfile.GetDateTime(4);
}
if (!sqlReaderProfile.IsDBNull(5))
{
newProfile.Commentary = sqlReaderProfile.GetString(5);
}
if (sqlReaderProfile.GetInt16(6) == AccountMgmt.Common.Constants.ActivationLevel)
{
newProfile.Activation = true;
}
else
{
newProfile.Activation = false;
}
if (!sqlReaderProfile.IsDBNull(7))
{
newProfile.OracleProfile = sqlReaderProfile.GetString(7);
}
/*if(!sqlReaderProfile.IsDBNull(8))
* newProfile.RsrcConsoGroup = sqlReaderProfile.GetString(8);*/
m_listProfile.Add(newProfile);
}
bResult = true;
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
//deconnexion profile
try
{
// Fermeture de la base de données
if (sqlReaderProfile != null)
{
sqlReaderProfile.Close();
sqlReaderProfile.Dispose();
}
if (sqlCommandProfile != null)
{
sqlCommandProfile.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
return(bResult);
}
private void HienThiAllMessages(DataGridView grd, List <classMessage> Messages)
{
//try
//{
int n = Messages.Count;
string[] message;
for (int i = 0; i < n; i++)
{
int k = 0;
if (Messages[i] != null)
{
message = new string[128];
message[k++] = Messages[i].DataElement[0];
message[k++] = Messages[i].DataElement[1];
message[k++] = Messages[i].DataElement[2];
message[k++] = FormatAmount(Messages[i].DataElement[3], Messages[i].DataElement[48]);
message[k++] = FormatAmount(Messages[i].DataElement[4], Messages[i].DataElement[49]);
message[k++] = FormatAmount(Messages[i].DataElement[5], Messages[i].DataElement[50]);
message[k++] = Messages[i].DataElement[48]; //DE49
message[k++] = Messages[i].DataElement[49]; //DE50
message[k++] = Messages[i].DataElement[50]; //DE51
message[k++] = Messages[i].DataElement[71]; //DE72
message[k++] = Messages[i].DataElement[8];
message[k++] = Messages[i].DataElement[9];
message[k++] = Messages[i].DataElement[11];
message[k++] = Messages[i].DataElement[13];
message[k++] = Messages[i].DataElement[21];
message[k++] = Messages[i].DataElement[22];
message[k++] = Messages[i].DataElement[23];
message[k++] = Messages[i].DataElement[24];
message[k++] = Messages[i].DataElement[25];
message[k++] = Messages[i].DataElement[29];
message[k++] = Messages[i].DataElement[30];
message[k++] = Messages[i].DataElement[31];
message[k++] = Messages[i].DataElement[32];
message[k++] = Messages[i].DataElement[36];
message[k++] = Messages[i].DataElement[37];
message[k++] = Messages[i].DataElement[39];
message[k++] = Messages[i].DataElement[40];
message[k++] = Messages[i].DataElement[41];
message[k++] = Messages[i].DataElement[42];
message[k++] = Messages[i].DataElement[47];
//message[25] = Messages[i].DataElement[48];
//message[26] = Messages[i].DataElement[49];
//message[27] = Messages[i].DataElement[50];
message[k++] = Messages[i].DataElement[53];
message[k++] = Messages[i].DataElement[54];
message[k++] = Messages[i].DataElement[61];
message[k++] = Messages[i].DataElement[62];
message[k++] = Messages[i].DataElement[70];
//message[33] = Messages[i].DataElement[71];
message[k++] = Messages[i].DataElement[72];
message[k++] = Messages[i].DataElement[92];
message[k++] = Messages[i].DataElement[93];
message[k++] = Messages[i].DataElement[94];
message[k++] = Messages[i].DataElement[99];
message[k++] = Messages[i].DataElement[110];
///////hhhh them phi giao dich
//message[k++] = Messages[i].DataElement[122];
message[k++] = Messages[i].AdditionalData[150];
//message[k++] = Messages[i].DataElement[123];
////message[k++] = FormatAmount(Messages[i].AdditionalData[151],Messages[i].AdditionalData[150]);
message[k++] = Messages[i].AdditionalData[151];
//message[k++] = Messages[i].DataElement[124];
message[k++] = Messages[i].AdditionalData[152];
//message[k++] = Messages[i].DataElement[126];
////message[k++] = FormatAmount(Messages[i].AdditionalData[153], Messages[i].AdditionalData[152]);
message[k++] = Messages[i].AdditionalData[153];
//22222
//string card_full = Messages[i].DataElement[1];
//string infor = null;
//infor=GetLOC_CardType_Branch(card_full);
//if (infor != null)
//{
// message[k++] = infor.Substring(0,12);
// message[k++] = infor.Substring(13,3);
// message[k++] = infor.Substring(17);
//}
//////
//string card_full = Messages[i].DataElement[1];
//DataTable table = new DataTable();
//table.Rows.Clear();
//GetInfor_Card(card_full);
//table = GetLOC_CardType_Branch2(card_full);
//if (table.Rows.Count > 0)
//{
// foreach (DataRow row in table.Rows)
// {
// message[k++] = row.ItemArray[0].ToString();
// message[k++] = row.ItemArray[1].ToString();
// message[k++] = row.ItemArray[2].ToString();
// }
//}
////////
//////////3333
string card_full = null;
card_full = Messages[i].DataElement[1];
OracleConnection conn = new OracleConnection();
conn = OracleDBConnection.OpenConnection("CCPS_DW");
if (card_full != null)
{
string str_get_pan = "select ecd2(" + card_full + ",'') from dual";
OracleCommand cmd = new OracleCommand(str_get_pan, conn);
cmd.CommandType = CommandType.Text;
string pan_temp = null;
pan_temp = Convert.ToString(cmd.ExecuteScalar());
if (pan_temp != null)
{
string str_get_infor = "select loc,branch,card_type from "
+ "( select PX_DW005_PAN pan,F9_DW005_LOC_ACCT loc ,FX_DW005_CRD_PGM card_type,FX_DW005_BRCH_CDE branch "
+ "from dw005 "
+ "union "
+ "select PX_DW006_OWN_PAN pan,F9_DW006_LOC_ACCT loc ,FX_DW006_CRD_PGM card_type,FX_DW006_BRCH_CDE branch "
+ "from dw006 ) "
+ "where pan='"
+ pan_temp + "'";
string brach_temp = null;
cmd = new OracleCommand(str_get_infor, conn);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr["loc"].ToString() != null)
{
message[k++] = dr["loc"].ToString();
}
if (dr["branch"].ToString() != null)
{
message[k++] = dr["branch"].ToString();
}
if (dr["card_type"].ToString() != null)
{
message[k++] = dr["card_type"].ToString();
}
dr.Close();
}
}
conn.Close();
//////////
////////
grd.Rows.Add(message);
}
}
//}
//catch (Exception ex)
//{
// classMasterCardToolLogWriter.WriteLog("Error GetKichHoatThe()at DataAccess, " + ex.Message);
//}
}
public IEnumerable <AgentSearch> SearchAgents(AgentSearch AgentSearch)
{
string sql = "";
string sqlWhere = "";
if (AgentSearch.AGT_CODE != null && AgentSearch.AGT_CODE != "")
{
sqlWhere = sqlWhere + "(UPPER(t.agt_code) LIKE '%" + AgentSearch.AGT_CODE.ToUpper() + "%') AND";
}
if (AgentSearch.AGT_NAME != null && AgentSearch.AGT_NAME != "")
{
sqlWhere = sqlWhere + "(UPPER(t.AGT_FULL_NAME) LIKE '%" + AgentSearch.AGT_NAME.ToUpper() + "%') AND";
}
if (AgentSearch.AGT_ADDRESS != null && AgentSearch.AGT_ADDRESS != "")
{
sqlWhere = sqlWhere + "(UPPER(t.AGT_FULL_ADDRESS) LIKE '%" + AgentSearch.AGT_ADDRESS.ToUpper() + "%') AND";
}
if (AgentSearch.AGT_MOBILE != null && AgentSearch.AGT_MOBILE != "")
{
sqlWhere = sqlWhere + "(UPPER(t.AGT_MOBILE) LIKE '%" + AgentSearch.AGT_MOBILE.ToUpper() + "%') AND";
}
if (AgentSearch.AGT_NIC_NO != null && AgentSearch.AGT_NIC_NO != "")
{
sqlWhere = sqlWhere + "(UPPER(t.AGT_NIC_NO) LIKE '%" + AgentSearch.AGT_NIC_NO.ToUpper() + "%') AND";
}
if (sqlWhere.Length > 0)
{
sqlWhere = sqlWhere.Substring(0, sqlWhere.Length - 3);
}
sqlWhere = sqlWhere + "and t.agt_effective_end_date is null";
List <AgentSearch> AgentList = new List <AgentSearch>();
DataTable dataTable = new DataTable();
OracleDataReader dataReader = null;
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
sql = "select t.agt_id,t.agt_code,t.AGT_FULL_NAME,t.AGT_FULL_ADDRESS,t.agt_nic_no,t.agt_mobile from hci_tbl_agent t where (" + sqlWhere + ")";
command = new OracleCommand(sql, connection);
try
{
connection.Open();
dataReader = command.ExecuteReader();
dataTable.Load(dataReader);
dataReader.Close();
connection.Close();
AgentList = (from DataRow drow in dataTable.Rows
select new AgentSearch()
{
AGT_ID = drow[0].ToString(),
AGT_CODE = drow[1].ToString(),
AGT_NAME = drow[2].ToString(),
AGT_ADDRESS = drow[3].ToString(),
AGT_NIC_NO = drow[4].ToString(),
AGT_MOBILE = drow[5].ToString()
}).ToList();
}
catch (Exception exception)
{
if (dataReader != null)
{
dataReader.Close();
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(AgentList);
}
private void LOGIN()
{
string Username = txtUserName.Text.Trim();
string passWord = txtPassWord.Text.Trim();
string passMd5 = MD5Encrypt.MD5Hash(passWord).Trim();
//check true false login
try
{
//BEGIN LOGIN
if (Config.CheckConnectToServer()) // nếu có mạng lan
{
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ERBusConnection"].ConnectionString))
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
try
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = string.Format(@"SELECT USERNAME,MANHANVIEN,TENNHANVIEN,UNITCODE FROM NGUOIDUNG WHERE USERNAME = '******' AND PASSWORD = '******'");
OracleDataReader dataReader = null;
dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
Session.Session.CurrentMaNhanVien = dataReader["MANHANVIEN"].ToString();
Session.Session.CurrentTenNhanVien = dataReader["TENNHANVIEN"].ToString();
Session.Session.CurrentUnitCode = dataReader["UNITCODE"].ToString();
Session.Session.CurrentCodeStore = Session.Session.CurrentUnitCode;
SYNCHRONIZE_DATA.SYNCHRONIZE_KYKETOAN();
SYNCHRONIZE_DATA.KHOASODULIEU();
SYNCHRONIZE_DATA.SYNCHRONIZE_KHOASO();
cmd.Parameters.Clear();
cmd.CommandText = string.Format(@"SELECT TEN_CUAHANG,DIACHI,SODIENTHOAI FROM CUAHANG WHERE MA_CUAHANG = '" + Session.Session.CurrentUnitCode + "'");
OracleDataReader dataReaderDonVi = null;
dataReaderDonVi = cmd.ExecuteReader();
if (dataReaderDonVi.HasRows)
{
while (dataReaderDonVi.Read())
{
Session.Session.CurrentPhone = dataReaderDonVi["SODIENTHOAI"].ToString();
Session.Session.CurrentAddress = dataReaderDonVi["DIACHI"].ToString();
Session.Session.CurrentNameStore = dataReaderDonVi["TEN_CUAHANG"].ToString();
}
}
dataReaderDonVi.Close();
Session.Session.CurrentUserName = dataReader["USERNAME"].ToString();
Session.Session.CurrentNgayPhatSinh = FrmXuatBanLeService.GET_NGAYHACHTOAN_CSDL_ORACLE();
Session.Session.CurrentTableNamePeriod = FrmXuatBanLeService.GET_TABLE_NAME_NGAYHACHTOAN_CSDL_ORACLE();
cmd.Parameters.Clear();
cmd.CommandText = string.Format(@"SELECT GIATRI_CHU FROM THAMSOHETHONG WHERE MA_THAMSO = 'DEFAULT_KHOBANLE' AND UNITCODE = '" + Session.Session.CurrentUnitCode + "'");
OracleDataReader dataReaderThamSo = null;
dataReaderThamSo = cmd.ExecuteReader();
if (dataReaderThamSo.HasRows)
{
while (dataReaderThamSo.Read())
{
Session.Session.CurrentWareHouse = dataReaderThamSo["GIATRI_CHU"].ToString();
}
}
else
{
Session.Session.CurrentWareHouse = "KH2";
}
SplashScreenManager.ShowForm(typeof(WaitForm1));
SYNCHRONIZE_DATA.SYNCHRONIZE_NGUOIDUNG();
SYNCHRONIZE_DATA.SYNCHRONIZE_KHACHHANG();
SYNCHRONIZE_DATA.SYNCHRONIZE_KHUYENMAI();
SYNCHRONIZE_DATA.SYNCHRONIZE_KHUYENMAI_CHITIET();
SYNCHRONIZE_DATA.SYNCHRONIZE_THAMSOHETHONG();
SYNCHRONIZE_DATA.SYNCHRONIZE_LOAIHANG();
SYNCHRONIZE_DATA.SYNCHRONIZE_NHOMHANG();
SYNCHRONIZE_DATA.SYNCHRONIZE_MATHANG();
SYNCHRONIZE_DATA.SYNCHRONIZE_MATHANG_GIA();
SYNCHRONIZE_DATA.SYNCHRONIZE_CUAHANG();
SYNCHRONIZE_DATA.SYNCHRONIZE_DONVITINH();
SYNCHRONIZE_DATA.SYNCHRONIZE_THUE();
SYNCHRONIZE_DATA.SYNCHRONIZE_HANGKHACHHANG();
SYNCHRONIZE_DATA.SYNCHRONIZE_NHACUNGCAP();
SplashScreenManager.CloseForm();
FrmMain frmMain = new FrmMain();
frmMain.ShowDialog();
break;
}
}
else
{
NotificationLauncher.ShowNotificationWarning("Thông báo", "Thông tin đăng nhập không đúng", 1, "0x1", "0x8", "normal");
txtPassWord.Text = "";
txtPassWord.Focus();
}
}
catch (Exception ex)
{
NotificationLauncher.ShowNotificationError("Thông báo", "Không có kết nối với cơ sở dữ liệu máy chủ", 1, "0x1", "0x8", "normal");
WriteLogs.LogError(ex);
}
finally
{
connection.Close();
connection.Dispose();
}
}
else
{
NotificationLauncher.ShowNotificationError("Thông báo", "Không có kết nối với cơ sở dữ liệu", 1, "0x1", "0x8", "normal");
}
}
}
else
{
//Kết nối với SQL
using (SqlConnection connectionSa = new SqlConnection(ConfigurationManager.ConnectionStrings["ERBusCashier"].ConnectionString))
{
connectionSa.Open();
if (connectionSa.State == ConnectionState.Open)
{
try
{
SqlCommand cmdSelectSa = new SqlCommand();
cmdSelectSa.Connection = connectionSa;
cmdSelectSa.CommandText = string.Format(@"SELECT USERNAME,MANHANVIEN,TENNHANVIEN,UNITCODE FROM [dbo].[NGUOIDUNG] WHERE USERNAME = '******' AND PASSWORD = '******'");
SqlDataReader dataReader = null;
dataReader = cmdSelectSa.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
Session.Session.CurrentMaNhanVien = dataReader["MANHANVIEN"].ToString();
Session.Session.CurrentTenNhanVien = dataReader["TENNHANVIEN"].ToString();
Session.Session.CurrentUnitCode = dataReader["UNITCODE"].ToString();
Session.Session.CurrentCodeStore = Session.Session.CurrentUnitCode;
SqlCommand cmdSelectCuaHang = new SqlCommand();
cmdSelectCuaHang.Parameters.Clear();
cmdSelectCuaHang.Connection = connectionSa;
cmdSelectCuaHang.CommandText = string.Format(@"SELECT TEN_CUAHANG,DIACHI,SODIENTHOAI FROM [dbo].[CUAHANG] WHERE MA_CUAHANG = '" + Session.Session.CurrentUnitCode + "'");
SqlDataReader dataReaderDonVi = null;
dataReaderDonVi = cmdSelectCuaHang.ExecuteReader();
if (dataReaderDonVi.HasRows)
{
while (dataReaderDonVi.Read())
{
Session.Session.CurrentPhone = dataReaderDonVi["SODIENTHOAI"].ToString();
Session.Session.CurrentAddress = dataReaderDonVi["DIACHI"].ToString();
Session.Session.CurrentNameStore = dataReaderDonVi["TEN_CUAHANG"].ToString();
}
}
dataReaderDonVi.Close();
Session.Session.CurrentUserName = dataReader["USERNAME"].ToString();
//nếu mất mạng thì ngày phát sinh là ngày hiện tại
Session.Session.CurrentNgayPhatSinh = FrmXuatBanLeService.GET_NGAYHACHTOAN_CSDL_SQLSERVER();
SqlCommand cmdSelectThamSo = new SqlCommand();
cmdSelectThamSo.Connection = connectionSa;
cmdSelectThamSo.Parameters.Clear();
cmdSelectThamSo.CommandText = string.Format(@"SELECT GIATRI_CHU FROM dbo.THAMSOHETHONG WHERE MA_THAMSO = 'DEFAULT_KHOBANLE'");
SqlDataReader dataReaderThamSo = null;
dataReaderThamSo = cmdSelectThamSo.ExecuteReader();
if (dataReaderThamSo.HasRows)
{
while (dataReaderThamSo.Read())
{
Session.Session.CurrentWareHouse = dataReaderThamSo["GIATRI_CHU"].ToString();
}
dataReaderThamSo.Close();
}
else
{
Session.Session.CurrentWareHouse = "KH2";
}
FrmMain frmMain = new FrmMain();
frmMain.ShowDialog();
break;
}
dataReader.Close();
}
else
{
NotificationLauncher.ShowNotificationWarning("Thông báo", "Thông tin đăng nhập không đúng", 1, "0x1", "0x8", "normal");
txtPassWord.Text = "";
txtPassWord.Focus();
}
}
catch (Exception ex)
{
NotificationLauncher.ShowNotificationError("Thông báo", "Không có kết nối với cơ sở dữ liệu máy bán", 1, "0x1", "0x8", "normal");
WriteLogs.LogError(ex);
}
finally
{
connectionSa.Close();
connectionSa.Dispose();
}
}
else
{
NotificationLauncher.ShowNotificationError("Thông báo", "Không có kết nối với cơ sở dữ liệu", 1, "0x1", "0x8", "normal");
}
}
}
//END LOGIN
}
catch (Exception ex)
{
NotificationLauncher.ShowNotificationError("Thông báo", "Không có kết nối với cơ sở dữ liệu máy bán", 1, "0x1", "0x8", "normal");
WriteLogs.LogError(ex);
}
}
private void FillDeloDepartmentsColumn()
{
((DataGridViewComboBoxColumn)UsersGrid.Columns["delo"]).Items.Add("");
cmd = conn.CreateCommand();
cmd.CommandText = "select SUBSTR(p.due, 0, INSTR(p.due, '.', 3 )) departmentCode, p.due personCode, (SELECT d.classif_name FROM department d WHERE d.due = SUBSTR(p.due, 0, INSTR(p.due, '.', 3 ))) departmentName, classif_name from department p where DELETED=0 and due != '0.' order by due";
try {
dr = cmd.ExecuteReader();
while (dr.Read()) {
if (!dr.IsDBNull(0)) {
((DataGridViewComboBoxColumn)UsersGrid.Columns["delo"]).Items.Add(dr.GetString(3));
DeloPersons[dr.GetString(3)] = new DeloPerson(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3));
}
}
} catch (Exception e) {
MessageBox.Show("При чтении данных о подразделениях из базы данных произошла ошибка:\n\n" + e.Message);
}
if (dr != null) {
dr.Close();
dr.Dispose();
}
if (cmd != null) {
cmd.Dispose();
}
}
public UploadDoc getLeader_HProfilePicByAgentID(string vAGT_CODE)
{
UploadDoc UploadDocObj1 = new UploadDoc();
OracleDataReader dataReader1 = null;
OracleConnection connection1 = new OracleConnection(ConnectionString);
OracleCommand command1;
string sql = "";
sql = "SELECT " +
"CASE WHEN t.ID IS NULL THEN 0 ELSE t.ID END, " +
"t.AGT_CODE || ' - ' || A.AGT_FULL_NAME, " +
"CASE WHEN t.DOC_TYPE_ID IS NULL THEN 0 ELSE t.DOC_TYPE_ID END, " +
"CASE WHEN t.DOC_URL IS NULL THEN '' ELSE t.DOC_URL END, " +
"CASE WHEN t.CREATED_BY IS NULL THEN '' ELSE t.CREATED_BY END, " +
"CASE WHEN t.CREATED_DATE IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE t.CREATED_DATE END, " +
"CASE WHEN t.EFFECTIVE_END_DATE IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.EFFECTIVE_END_DATE, 'DD/MM/RRRR') END, DT.DOC_TYPE_NAME, t.doc_description " +
"FROM HCI_TBL_UPLOADED_DOC t, Hci_Tbl_Uploaded_Doc_Type DT, HCI_TBL_AGENT A " +
"WHERE A.AGT_CODE=:vAGT_CODE AND DT.doc_type_id = T.doc_type_id AND t.EFFECTIVE_END_DATE is null AND t.DOC_TYPE_ID = 1 AND A.AGT_CODE = T.AGT_CODE AND A.AGT_EFFECTIVE_END_DATE IS NULL";
command1 = new OracleCommand(sql, connection1);
command1.Parameters.Add(new OracleParameter("vAGT_CODE", vAGT_CODE));
connection1.Open();
try
{
dataReader1 = command1.ExecuteReader();
if (dataReader1.HasRows)
{
dataReader1.Read();
UploadDocObj1.Id = Convert.ToInt32(dataReader1[0]);
UploadDocObj1.AgtCode = dataReader1[1].ToString();
UploadDocObj1.DocTypeId = Convert.ToInt32(dataReader1[2].ToString());
UploadDocObj1.DocUrl = dataReader1[3].ToString();
UploadDocObj1.CreatedBy = dataReader1[4].ToString();
UploadDocObj1.CreatedDate = dataReader1[5].ToString();
UploadDocObj1.EffectiveEndDate = dataReader1[6].ToString();
UploadDocObj1.DocTypeDesc = dataReader1[7].ToString();
UploadDocObj1.DocDescription = dataReader1[8].ToString();
dataReader1.Close();
connection1.Close();
}
else
{
return(null);
}
}
catch (Exception exception)
{
if (dataReader1 != null || connection1.State == ConnectionState.Open)
{
dataReader1.Close();
connection1.Close();
}
}
finally
{
connection1.Close();
}
return(UploadDocObj1);
}
void Backup(string executor_id)
{
cmd = conn.CreateCommand();
cmd.CommandText = "select TO_CHAR(id) from akriko.appeal WHERE ISPOLNITEL_CIK_ID=" + executor_id;
try {
dr = cmd.ExecuteReader();
while (dr.Read()) {
if (!dr.IsDBNull(0)) {
this.Log(dr.GetString(0), executor_id);
}
}
} catch (Exception e) {
MessageBox.Show("При чтении данных о подразделениях из базы данных произошла ошибка:\n\n" + e.Message);
}
if (dr != null) {
dr.Close();
dr.Dispose();
}
if (cmd != null) {
cmd.Dispose();
}
}
private void Form1_Load(object sender, EventArgs e)
{
con1 = new OracleConnection("Data Source=XE;User ID=project;password=project");
//print
/* using (NorthwindEntities db = new NorthwindEntities())
* {
* BindingSource.DataSource = db.Product.ToList();
* }*/
// MessageBox.Show(gname);
textBox3.Text = "";
textBox6.Text = "";
textBox7.Text = "";
comboBox1.Text = "--SELECT--";
comboBox2.Text = "--SELECT--";
comboBox3.Text = "--SELECT--";
comboBox4.Text = "--SELECT--";
this.label1.BackColor = System.Drawing.Color.Transparent;
this.label2.BackColor = System.Drawing.Color.Transparent;
this.label3.BackColor = System.Drawing.Color.Transparent;
this.label4.BackColor = System.Drawing.Color.Transparent;
this.label5.BackColor = System.Drawing.Color.Transparent;
this.label6.BackColor = System.Drawing.Color.Transparent;
this.label7.BackColor = System.Drawing.Color.Transparent;
this.label8.BackColor = System.Drawing.Color.Transparent;
this.label9.BackColor = System.Drawing.Color.Transparent;
this.label10.BackColor = System.Drawing.Color.Transparent;
NEWRECORD();
CLEARFORM();
dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = " dd-MMM-yyyy ";
textBox3.Text = Convert.ToString(0);
textBox7.Text = "";
//from
QUERY = "select distinct(s_from) from buses";
CMD = new OracleCommand(QUERY, CON1);
CON1.Open();
RDR = CMD.ExecuteReader();
while (RDR.Read())
{
comboBox1.Items.Add(RDR["s_from"]);
}
RDR.Close();
CON1.Close();
//to
QUERY = "select distinct(d_to) from buses";
CMD = new OracleCommand(QUERY, CON1);
CON1.Open();
RDR = CMD.ExecuteReader();
while (RDR.Read())
{
comboBox2.Items.Add(RDR["d_to"]);
}
RDR.Close();
CON1.Close();
//bustype
QUERY = "select distinct(bus_type) from buses";
CMD = new OracleCommand(QUERY, CON1);
CON1.Open();
RDR = CMD.ExecuteReader();
while (RDR.Read())
{
comboBox4.Items.Add(RDR["bus_type"]);
}
RDR.Close();
CON1.Close();
//bustiming
QUERY = "select distinct(timing) from buses";
CMD = new OracleCommand(QUERY, CON1);
CON1.Open();
RDR = CMD.ExecuteReader();
while (RDR.Read())
{
comboBox3.Items.Add(RDR["timing"]);
}
RDR.Close();
textBox2.Text = gname;
CON1.Close();
// BUSNO();
//PNO();
}
public String ProcessNews()
{
using (Logger l = new Logger("PipeCatcher"))
{
OraCmd.CommandText = ProcName;
OraCmd.CommandType = System.Data.CommandType.StoredProcedure;
OraCmd.Parameters.Clear();
//OraCmd.Parameters.Add(new OracleParameter("NRECID", OracleDbType.Decimal, System.Data.ParameterDirection.Input)).Value = RecId;
OraCmd.Parameters.Add(new OracleParameter("REFCURSOR", OracleDbType.RefCursor, System.Data.ParameterDirection.Output));
if (OraCmd.Connection.State != System.Data.ConnectionState.Closed)
{
OraCmd.Connection.Close();
}
OraCmd.Connection.Open();
OraReader = OraCmd.ExecuteReader();
var str = String.Format("\nCALL {0}", ProcName);
// var mainevt = new FlexEvent("PipeCatcher.Call." + ProcName);
// mainevt.Arguments.Add("@ProcName", ProcName);
if (OraReader.HasRows)
{
var iRow = 0;
FlexEvent evt = new FlexEvent("PipeCatcher.Call." + ProcName + ".Row." + (++iRow));
str += "+++";
while (OraReader.Read())
{
for (int i = 0; i < OraReader.FieldCount; i++)
{
evt.Arguments.Add(OraReader.GetName(i), OraReader[i]);
str += "\n" + OraReader.GetName(i) + "\t: " + OraReader[i];
}
str += "\n********************";
// mainevt.Arguments.Add(String.Format("Row{0}", ++iRow), evt);
Program.CoreGate.PushEvent(evt);
evt.Arguments.Clear();
}
// Program.CoreGate.PushEvent(mainevt);
}
else
{
str += "---";
}
OraReader.Close();
l.msg(str);
return str;
}
}
protected override void DoDiscoverWork(object sender, DoWorkEventArgs e)
{
string sql =
"SELECT O.object_name, O.created, O.last_ddl_time, O.status, S.text,"
+ " COALESCE(E.IsError, 0) AS IsError"
+ " FROM dba_objects O"
+ " JOIN dba_source S"
+ " ON S.owner = O.owner"
+ " AND S.name = O.object_name"
+ " AND S.type = O.object_type"
+ " AND S.line = 1"
+ " LEFT OUTER JOIN"
+ " (SELECT DISTINCT E.name, 1 AS IsError"
+ " FROM User_Errors E"
+ " WHERE E.type = 'PACKAGE BODY') E"
+ " ON E.name = O.object_name"
+ " WHERE O.owner = '" + schemaName + "'"
+ " AND O.object_type = 'PACKAGE BODY'"
+ " ORDER BY O.object_name";
OracleCommand cmd = new OracleCommand(sql, dbase.OraConnection);
try
{
SchemataPackageBody packageBody;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Logger.WriteLine(schemaName + "." + reader.GetString(0));
packageBody = new SchemataPackageBody(dbase, srvnode, reader.GetString(0));
packageBody.AddProperty("Name", reader.GetString(0));
packageBody.AddProperty("Created", reader.GetDateTime(1).ToString());
packageBody.AddProperty("Last DDL Time", reader.GetDateTime(2).ToString());
packageBody.AddProperty("Status", reader.GetString(3));
string preamble = reader.GetString(4).ToString().Trim().ToLower();
packageBody.IsWrapped = preamble.Contains("wrapped");
packageBody.AddProperty("Wrapped", packageBody.IsWrapped ? "Yes" : "No");
if ((int)reader.GetDecimal(5) == 1)
{
packageBody.ForeColor = System.Drawing.Color.Red;
}
else if (reader.GetString(3) == "INVALID")
{
packageBody.ForeColor = System.Drawing.Color.Green;
}
discoveries.Add(packageBody);
}
reader.Close();
reader.Dispose();
reader = null;
}
catch (Exception exc)
{
River.Orqa.Dialogs.ExceptionDialog.ShowException(exc);
}
cmd.Dispose();
cmd = null;
}
private bool MoveNextRelation()
{
if (_relation_reader == null)
{
OracleCommand relation_command = new OracleCommand("select * from relation order by id");
relation_command.Connection = _connection;
_relation_reader = relation_command.ExecuteReader();
if (!_relation_reader.Read())
{
_relation_reader.Close();
}
OracleCommand relation_tag_command = new OracleCommand("select * from relation_tags order by relation_id");
relation_tag_command.Connection = _connection;
_relation_tag_reader = relation_tag_command.ExecuteReader();
if (!_relation_tag_reader.IsClosed && !_relation_tag_reader.Read())
{
_relation_tag_reader.Close();
}
OracleCommand relation_node_command = new OracleCommand("select * from relation_members order by relation_id,sequence_id");
relation_node_command.Connection = _connection;
_relation_member_reader = relation_node_command.ExecuteReader();
if (!_relation_member_reader.IsClosed && !_relation_member_reader.Read())
{
_relation_member_reader.Close();
}
}
// read next relation.
if (!_relation_reader.IsClosed)
{
// load/parse data.
long id = _relation_reader.GetInt64(0);
long changeset_id = _relation_reader.GetInt64(1);
DateTime timestamp = _relation_reader.GetDateTime(2);
bool visible = _relation_reader.GetInt64(3) == 1;
long version = _relation_reader.GetInt64(4);
string user = _relation_reader.GetString(5);
long uid = _relation_reader.GetInt64(6);
SimpleRelation relation = new SimpleRelation();
relation.Id = id;
relation.ChangeSetId = changeset_id;
relation.TimeStamp = timestamp;
relation.UserId = null;
relation.UserName = null;
relation.Version = (ulong)version;
relation.Visible = visible;
relation.UserName = user;
relation.UserId = uid;
if (!_relation_tag_reader.IsClosed)
{
long returned_id = _relation_tag_reader.GetInt64(0);
while (returned_id == relation.Id.Value)
{
if (relation.Tags == null)
{
relation.Tags = new Dictionary<string, string>();
}
string key = _relation_tag_reader.GetString(1);
string value = _relation_tag_reader.GetString(2);
relation.Tags.Add(key, value);
if (!_relation_tag_reader.Read())
{
_relation_tag_reader.Close();
returned_id = -1;
}
else
{
returned_id = _relation_tag_reader.GetInt64(0);
}
}
}
if (!_relation_member_reader.IsClosed)
{
long returned_id = _relation_member_reader.GetInt64(0);
while (returned_id == relation.Id.Value)
{
if (relation.Members == null)
{
relation.Members = new List<SimpleRelationMember>();
}
string member_type = _relation_member_reader.GetString(1);
long member_id = _relation_member_reader.GetInt64(2);
object member_role = _relation_member_reader.GetValue(3);
SimpleRelationMember member = new SimpleRelationMember();
member.MemberId = member_id;
if (member_role != DBNull.Value)
{
member.MemberRole = member_role as string;
}
switch (member_type)
{
case "Node":
member.MemberType = SimpleRelationMemberType.Node;
break;
case "Way":
member.MemberType = SimpleRelationMemberType.Way;
break;
case "Relation":
member.MemberType = SimpleRelationMemberType.Relation;
break;
}
relation.Members.Add(member);
if (!_relation_member_reader.Read())
{
_relation_member_reader.Close();
returned_id = -1;
}
else
{
returned_id = _relation_member_reader.GetInt64(0);
}
}
}
// set the current variable!
_current = relation;
// advance the reader(s).
if (!_relation_reader.Read())
{
_relation_reader.Close();
}
if (!_relation_tag_reader.IsClosed && !_relation_tag_reader.Read())
{
_relation_tag_reader.Close();
}
if (!_relation_member_reader.IsClosed && !_relation_member_reader.Read())
{
_relation_member_reader.Close();
}
return true;
}
else
{
_relation_reader.Close();
_relation_reader.Dispose();
_relation_reader = null;
_relation_tag_reader.Close();
_relation_tag_reader.Dispose();
_relation_tag_reader = null;
_current_type = SimpleOsmGeoType.Relation;
return false;
}
}
public static Regisseur GetRegisseur(int id)
{
string naam = string.Empty;
DateTime dob = new DateTime(1, 1, 1);
string woonplek = string.Empty;
int regisseurid = 0;
List<Film> films = new List<Film>();
List<Prijs> prijzen = new List<Prijs>();
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT r.naam, r.dob, r.woonplek, r.regisseurid FROM regisseur r WHERE r.regisseurid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", id);
reader = cmd.ExecuteReader();
while (reader.Read())
{
naam = reader.GetString(0);
dob = reader.GetDateTime(1);
woonplek = reader.GetString(2);
regisseurid = reader.GetInt32(3);
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT p.titel, p.jaar, p.prizeid FROM prijs p, regisseurprijsregel rpr WHERE p.prizeid = rpr.prizeid AND rpr.regisseurid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", id);
reader = cmd.ExecuteReader();
while (reader.Read())
{
prijzen.Add(new Prijs(reader.GetString(0), reader.GetInt32(1), reader.GetInt32(2)));
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT f.filmid, f.titel FROM film f, regisseur r WHERE r.regisseurid = :pid AND f.regisseurid = :pid";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("id", id);
reader = cmd.ExecuteReader();
while (reader.Read())
{
films.Add(new Film(reader.GetInt32(0).ToString(), reader.GetString(1)));
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
return new Regisseur(naam, dob, woonplek, prijzen, films, regisseurid);
}
private void button7_Click(object sender, EventArgs e)
{
try
{
String str1, p1, p2, p3, p4, p5, p6, count;
connection.Open();
p1 = textBox1.Text.ToString();
p2 = textBox2.Text.ToString();
str1 = "select count(*) from angajati WHERE NUME=" + "\'" + p1 + "\'" + " AND " + "PRENUME = " + "\'" + p2 + "\'";
query = new OracleCommand(str1, connection);
OracleDataReader reader = query.ExecuteReader();
reader.Read();
count = reader.GetValue(0).ToString();
int coun1 = Int32.Parse(count);
reader.Close();
connection.Close();
if (coun1 > 0)
{
MessageBox.Show("ok‐ s-a gasit");
button10.Visible = true;
}
else
{
button10.Visible = false;
MessageBox.Show("nu s-a gasit");
}
}
catch (Exception ex)
{
MessageBox.Show("error " + ex.ToString());
}
try
{
String str1, p1, p2, p3, p4, p5, p6, count;
connection.Open();
p1 = textBox1.Text.ToString();
p2 = textBox2.Text.ToString();
str1 = "select * from angajati WHERE NUME=" + "\'" + p1 + "\'" + " AND " + "PRENUME = " + "\'" + p2 + "\'";
query = new OracleCommand(str1, connection);
OracleDataReader reader = query.ExecuteReader();
reader.Read();
textBox1.Text = reader.GetValue(1).ToString();
textBox2.Text = reader.GetValue(2).ToString();
textBox3.Text = reader.GetValue(3).ToString();
textBox4.Text = reader.GetValue(4).ToString();
textBox5.Text = reader.GetValue(5).ToString();
textBox6.Text = reader.GetValue(6).ToString();
reader.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("error " + ex.ToString());
}
}
public static bool InsertRecensie(int userid, int filmid, string body)
{
int id = 0;
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT MAX(reviewid) FROM recensie";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
reader = cmd.ExecuteReader();
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
return false;
}
}
id = id + 1;
using (connection = new OracleConnection(connectionstring))
{
query = @"INSERT INTO recensie (reviewid, userid, filmid, rbody) VALUES(:pid, :puserid, :pfilmid, :precensiebody)";
cmd.CommandText = query;
cmd.Connection = connection;
connection.Open();
try
{
cmd.Parameters.Clear();
cmd.Parameters.Add("id", id);
cmd.Parameters.Add("userid", userid);
cmd.Parameters.Add("filmid", filmid);
cmd.Parameters.Add("recensiebody", body);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
return false;
}
}
}
/// <summary>
/// Permet de sélectionner des enregistrements suivant un critère de recherche et de trier ces résultats
/// </summary>
/// <param name="connection">Objet de connexion à la base de données</param>
/// <param name="idProfile">Identificateur du profile affecté</param>
/// <param name="strSort">Critère de trie</param>
/// <returns></returns>
public bool Select(OracleConnection connection, long idProfile, string strSort)
{
OracleCommand sqlCommand = null;
OracleDataReader sqlReader = null;
string sql = "SELECT u.id_user_, u.id_language_data_i, u.id_region_data, u.user_, u.user_oracle, u.service, u.date_creation, u.date_modification, u.commentary, u.activation, u.id_language_user_, u.pwd, u.date_beginning, u.date_end, u.id_user_modification, u.id_temporary_tablespace, u.dba_status, id_source," +
" a.admin, a.date_creation, a.date_modification, a.commentary, a.activation, a.date_beginning, a.date_end FROM MOU01.list_user u, MOU01.AFFECTATION a " +
" WHERE u.id_user_ = a.id_user_ AND a.id_profile = " + idProfile.ToString("0");
if (strSort != "")
{
sql = sql + " ORDER BY " + strSort;
}
bool bResult = false;
try
{
sqlCommand = new OracleCommand(sql, connection);
sqlReader = sqlCommand.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
User newUser = new User();
newUser.Id = long.Parse(sqlReader.GetValue(0).ToString()); //sqlReader.GetInt64(0);
newUser.IdLanguageData = long.Parse(sqlReader.GetValue(1).ToString()); //sqlReader.GetInt64(1);
newUser.IdRegionData = long.Parse(sqlReader.GetValue(2).ToString()); //sqlReader.GetInt64(2);
newUser.Name = sqlReader.GetString(3);
newUser.UserOracle = sqlReader.GetString(4);
newUser.Service = sqlReader.GetString(5);
newUser.DateCreation = sqlReader.GetDateTime(6);
if (!sqlReader.IsDBNull(7))
{
newUser.DateModification = sqlReader.GetDateTime(7);
}
if (!sqlReader.IsDBNull(8))
{
newUser.Commentary = sqlReader.GetString(8);
}
if (/*sqlReader.GetInt16(9)*/ short.Parse(sqlReader.GetValue(9).ToString()) == AccountMgmt.Common.Constants.ActivationLevel)
{
newUser.Activation = true;
}
else
{
newUser.Activation = false;
}
newUser.IdLanguageUser = long.Parse(sqlReader.GetValue(10).ToString()); //sqlReader.GetInt64(10);
if (!sqlReader.IsDBNull(11))
{
newUser.Password = sqlReader.GetString(11);
}
if (!sqlReader.IsDBNull(12))
{
newUser.DateBeginning = sqlReader.GetDateTime(12);
}
if (!sqlReader.IsDBNull(13))
{
newUser.DateEnd = sqlReader.GetDateTime(13);
}
if (!sqlReader.IsDBNull(14))
{
newUser.IdUserModification = long.Parse(sqlReader.GetValue(14).ToString()); //sqlReader.GetInt64(14);
}
newUser.IdTemporaryTableSpace = long.Parse(sqlReader.GetValue(15).ToString()); //sqlReader.GetInt64(15);
if (!sqlReader.IsDBNull(16))
{
newUser.Admin = short.Parse(sqlReader.GetValue(16).ToString());
}
else
{
newUser.Admin = AccountMgmt.Common.Constants.NoAdminLevel;
}
if (!sqlReader.IsDBNull(17))
{
newUser.IdSource = long.Parse(sqlReader.GetValue(17).ToString());
}
Affectation newAffectation = new Affectation();
newAffectation.IdUser = long.Parse(sqlReader.GetValue(0).ToString()); //sqlReader.GetInt64(0);
newAffectation.IdProfile = idProfile;
if (sqlReader.GetInt16(18) == AccountMgmt.Common.Constants.AdminLevel)
{
newAffectation.Admin = true;
}
else
{
newAffectation.Admin = false;
}
newAffectation.DateCreation = sqlReader.GetDateTime(19);
if (!sqlReader.IsDBNull(20))
{
newAffectation.DateModification = sqlReader.GetDateTime(20);
}
if (!sqlReader.IsDBNull(21))
{
newAffectation.Commentary = sqlReader.GetString(21);
}
if (sqlReader.GetInt16(22) == AccountMgmt.Common.Constants.ActivationLevel)
{
newAffectation.Activation = true;
}
else
{
newAffectation.Activation = false;
}
if (!sqlReader.IsDBNull(23))
{
newAffectation.DateBeginning = sqlReader.GetDateTime(23);
}
if (!sqlReader.IsDBNull(24))
{
newAffectation.DateEnd = sqlReader.GetDateTime(24);
}
newAffectation.AffectedUser = newUser;
m_listAffectedUser.Add(newAffectation);
}
bResult = true;
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
finally
{
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
public static List<ListItem> GetGenres(string name)
{
List<ListItem> filmGenreList = new List<ListItem>();
using (connection = new OracleConnection(connectionstring))
{
query = "SELECT f.titel, f.filmid FROM film f, filmgenreregel fgr, genre g WHERE f.filmid = fgr.filmid AND fgr.genreid = g.genreid AND g.naam = :pname";
cmd.CommandText = query;
cmd.Connection = connection;
try
{
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add("name", name);
reader = cmd.ExecuteReader();
while (reader.Read())
{
filmGenreList.Add(new ListItem(reader.GetString(0), reader.GetInt32(1).ToString()));
}
}
catch (Exception ex)
{
cmd.Dispose();
connection.Dispose();
reader.Close();
}
}
return filmGenreList;
}
