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; }