public CacheConfig getConfigurazioneCache(string idAmministrazione) { CacheConfig info = null; DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_CONFIG_CACHE"); queryDef.setParam("idAmministrazione", "'" + idAmministrazione + "'"); logger.Debug("getConfigurazioneCache - query :" + queryDef.getSQL()); string commandText = queryDef.getSQL(); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { if (reader != null) { while (reader.Read()) { info = new CacheConfig(); info.caching = reader.GetInt32(reader.GetOrdinal("CACHING")) == 1 ? true : false; info.doc_root_server = reader.GetString(reader.GetOrdinal("DOC_ROOT_SERVER")); info.massima_dimensione_caching = reader.GetDouble(reader.GetOrdinal("MASSIMA_DIMENSIONE_CACHING")); info.massima_dimensione_file = reader.GetDouble(reader.GetOrdinal("MASSIMA_DIMENSIONE_FILE")); info.idAmministrazione = reader.GetString(reader.GetOrdinal("idAmministrazione")); info.ora_fine_cache = reader.GetString(reader.GetOrdinal("ORA_FINE_CACHE")); info.ora_inizio_cache = reader.GetString(reader.GetOrdinal("ORA_INIZIO_CACHE")); info.urlwscaching = reader.GetString(reader.GetOrdinal("urlwscaching")); info.url_ws_caching_locale = reader.GetString(reader.GetOrdinal("url_ws_caching_locale")); info.doc_root_server_locale = reader.GetString(reader.GetOrdinal("doc_root_server_locale")); } } } } return(info); }
public DocsPaVO.Caching.InfoFileCaching massimaVersioneDelDocumentoComponents(string docnumber) { DocsPaVO.Caching.InfoFileCaching info = null; DocsPaUtils.Query sql = DocsPaUtils.InitQuery.getInstance().getQuery("S_CACHE_MAX_VESIONE_COMPONENTS"); sql.setParam("docnumber", docnumber.ToString()); logger.Debug("massimaVersioneDelDocumentoComponents - query:" + sql.getSQL()); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(sql.getSQL())) { if (reader != null) { while (reader.Read()) { info = new InfoFileCaching(); info.CacheFilePath = reader.GetString(reader.GetOrdinal("pathcache")); info.ext = reader.GetString(reader.GetOrdinal("ext")); info.Version_id = reader.GetInt32(reader.GetOrdinal("version_id")); } } } } return(info); }
//Get Records public static cs_recordcollection GetRecords() { Database db = DatabaseFactory.CreateDatabase(); DbCommand cw = db.GetStoredProcCommand("sp_CANNED_getRecords"); using (System.Data.IDataReader dr = db.ExecuteReader(cw)) { cs_recordcollection coll = new cs_recordcollection(); while (dr.Read()) { cs_record k = new cs_record( dr.GetInt32(dr.GetOrdinal("cannid")), dr["recordid"].ToString(), dr["headertext"].ToString(), (dr["audience"] == DBNull.Value) ? "" : dr["audience"].ToString(), (dr["cancertype"] == DBNull.Value) ? "" : dr["cancertype"].ToString(), (dr["language"] == DBNull.Value) ? "" : dr["language"].ToString(), (dr["prodformat"] == DBNull.Value) ? "" : dr["prodformat"].ToString(), (dr["race"] == DBNull.Value) ? "" : dr["race"].ToString(), (dr["series"] == DBNull.Value) ? "" : dr["series"].ToString(), (dr["subject"] == DBNull.Value) ? "" : dr["subject"].ToString(), dr.GetInt32(dr.GetOrdinal("active")) ); coll.Add(k); } return(coll); } }
/// <summary> /// /// </summary> /// <param name="userId"></param> /// <returns></returns> public DocsPaVO.utente.InfoUtente ricercaInfoutente(string userId) { DocsPaVO.utente.InfoUtente utente = null; DocsPaUtils.Query q = DocsPaUtils.InitQuery.getInstance().getQuery("S_INFOUTENTE"); q.setParam("user_id", "'" + userId + "'"); logger.Debug("ricercaInfoutente - query: " + q.getSQL()); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(q.getSQL())) { if (reader != null) { while (reader.Read()) { utente = new DocsPaVO.utente.InfoUtente(); utente.idGruppo = reader.GetInt32(reader.GetOrdinal("GROUPS_SYSTEM_ID")).ToString(); utente.idPeople = reader.GetInt32(reader.GetOrdinal("system_id")).ToString(); utente.userId = reader.GetString(reader.GetOrdinal("user_id")); utente.idAmministrazione = reader.GetInt32(reader.GetOrdinal("ID_AMM")).ToString(); } } } } return(utente); }
public static DocsPaVO.amministrazione.EsitoOperazione ImpostaLivelloSottoUO(DocsPaVO.amministrazione.OrgUO currentUO, string segno, int delta) { int rowsAffected; string commandText = string.Empty; string condizioneLivello = string.Empty; DocsPaUtils.Query queryDef = null; DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider(); DocsPaDB.DBProvider dbProvider2 = new DocsPaDB.DBProvider(); System.Data.IDataReader reader = null; DocsPaVO.amministrazione.EsitoOperazione esito = new DocsPaVO.amministrazione.EsitoOperazione(); DocsPaVO.amministrazione.OrgUO sottoUO = null; // prende tutte le sotto UO queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_CORR_GLOB_GENERIC"); queryDef.setParam("param1", "SYSTEM_ID, NUM_LIVELLO"); queryDef.setParam("param2", "ID_PARENT = " + currentUO.IDCorrGlobale); commandText = queryDef.getSQL(); logger.Debug(commandText); reader = dbProvider.ExecuteReader(commandText); while (reader.Read()) { sottoUO = new DocsPaVO.amministrazione.OrgUO(); sottoUO.IDCorrGlobale = reader.GetValue(reader.GetOrdinal("SYSTEM_ID")).ToString(); sottoUO.Livello = reader.GetValue(reader.GetOrdinal("NUM_LIVELLO")).ToString(); condizioneLivello = segno + Convert.ToString(delta); // aggiorna livello sotto UO queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("U_DPACorrGlobali"); queryDef.setParam("param1", "NUM_LIVELLO = NUM_LIVELLO " + condizioneLivello); queryDef.setParam("param2", "SYSTEM_ID = " + sottoUO.IDCorrGlobale); commandText = queryDef.getSQL(); logger.Debug(commandText); dbProvider2.ExecuteNonQuery(commandText, out rowsAffected); if (rowsAffected == 0) { esito.Codice = 3; esito.Descrizione = "fallito aggiornamento del livello della UO con ID: " + sottoUO.IDCorrGlobale; break; } else { esito = ImpostaLivelloSottoUO(sottoUO, segno, delta); } } return(esito); }
/// <summary> /// Creazione oggetto "OrgFunzioneAnagrafica" da datareader /// </summary> /// <param name="reader"></param> /// <returns></returns> private static OrgFunzioneAnagrafica CreateFunzioneAnagrafica(System.Data.IDataReader reader) { OrgFunzioneAnagrafica retValue = new OrgFunzioneAnagrafica(); retValue.Codice = reader.GetValue(reader.GetOrdinal("CODICE")).ToString(); retValue.Descrizione = reader.GetValue(reader.GetOrdinal("DESCRIZIONE")).ToString(); retValue.TipoFunzione = reader.GetValue(reader.GetOrdinal("TIPO_FUNZIONE")).ToString(); return(retValue); }
public InfoFileCaching getFileComponents(string docnumber, string version_id, string idAmministrazione) { InfoFileCaching info = null; DocsPaUtils.Query q = DocsPaUtils.InitQuery.getInstance().getQuery("S_COMPONENTS_FILE"); q.setParam("docnumber", docnumber); q.setParam("idAmministrazione", "'" + idAmministrazione + "'"); q.setParam("version_id", version_id); string queryString = q.getSQL(); logger.Debug("getFileComponents - query: " + queryString); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(queryString)) { if (reader != null) { while (reader.Read()) { info = new InfoFileCaching(); info.alternate_path = reader.GetString(reader.GetOrdinal("ALTERNATE_PATH")); info.CacheFilePath = reader.GetString(reader.GetOrdinal("PATH")); info.comptype = reader.GetString(reader.GetOrdinal("COMPTYPE")); info.DocNumber = reader.GetInt32(reader.GetOrdinal("DOCNUMBER")); info.ext = reader.GetString(reader.GetOrdinal("EXT")); info.var_impronta = reader.GetString(reader.GetOrdinal("VAR_IMPRONTA")); info.file_size = reader.GetInt32(reader.GetOrdinal("FILE_SIZE")); info.locked = int.Parse(reader.GetString(reader.GetOrdinal("LOCKED"))); info.Version_id = reader.GetInt32(reader.GetOrdinal("VERSION_ID")); } } } } return(info); }
public void FillChartData(tblObject content, System.Data.IDataReader reader, string xFieldName, Dictionary <int, string> yFieldNames, List <ChartSeries> series, Chart chart) { SetSeriesSchema(reader, yFieldNames, xFieldName, series, chart); while (reader.Read()) { int i = 0; foreach (KeyValuePair <int, string> yField in yFieldNames) { string yFieldName = yField.Value.ToString(); object ob = string.IsNullOrEmpty(yFieldName) ? reader[yField.Key] : reader[yFieldName]; object o = ConvertChartYValue(ob); //object o = (ob is DBNull) ? null : ConvertChartYValue(ob); series[i].data.Add(o); Type type = reader.GetFieldType(reader.GetOrdinal(yFieldName)); if (!(o is DBNull) && IsNumeric(type)) { try { if (!content.Neg && Convert.ToDecimal(o) < 0) { content.Neg = true; } } catch { } } i++; } content.xAxis.Add(ConvertChartXValue(reader[xFieldName])); } }
public bool isActiveCache(string idAmministrazione) { bool result = false; DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_CONFIG_CACHING"); queryDef.setParam("idAmministrazione", "'" + idAmministrazione + "'"); logger.Debug("isActiveCache - query: " + queryDef.getSQL()); string commandText = queryDef.getSQL(); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { if (reader != null) { while (reader.Read()) { if (reader.GetInt32(reader.GetOrdinal("CACHING")) == 1) { result = true; } } } } } return(result); }
public string recuperaPathComponents(string docnumber, string version_id) { string path = null;//string.Empty; DocsPaUtils.Query sql = DocsPaUtils.InitQuery.getInstance().getQuery("S_COMPONENTS_FILE"); sql.setParam("docnumber", docnumber); sql.setParam("version_id", version_id); logger.Debug("recuperaPathComponents - query: " + sql.getSQL()); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(sql.getSQL())) { if (reader != null) { while (reader.Read()) { path = reader.GetString(reader.GetOrdinal("PATH")).ToString(); } } } } if (path == null) { path = string.Empty; } return(path); }
public void GetImpronta(out string impronta, string versionId, string docNumber, string idAmministrazione) { impronta = ""; try { DocsPaUtils.Query q = DocsPaUtils.InitQuery.getInstance().getQuery("S_CACHE_ALL"); q.setParam("docnumber", docNumber); q.setParam("idAmministrazione", "'" + idAmministrazione + "'"); q.setParam("version_id", versionId); string queryString = q.getSQL(); logger.Debug("GetImpronta - query :" + queryString); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(queryString)) { if (reader != null) { while (reader.Read()) { impronta = reader.GetString(reader.GetOrdinal("VAR_IMPRONTA")); } } } } } catch (Exception e) { logger.Debug("GetImpronta - errore: " + e.Message); } finally { this.CloseConnection(); } }
/// <summary> /// Returns all OPC Sample Group key and Name map /// </summary> /// <returns>Sample group key-Name map</returns> public Dictionary <string, string> GetAllOPCGrpIdAndName(bool checkLoc) { string Function_Name = "GetAllOPCGrpIdAndName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); Dictionary <string, string> opcGrpIDToNameDic = new Dictionary <string, string>(); string localSQL; localSQL = "SELECT PKEY, NAME FROM DATALOG_DP_GROUP_TREND"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); if (LocationClauseStr.Length != 0 && checkLoc) { localSQL += " WHERE" + LocationClauseStr; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { string grpID = "", grpName = ""; if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { grpID = drReader[COLUMN_PKEY].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { grpName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } opcGrpIDToNameDic.Add(grpID, grpName); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(opcGrpIDToNameDic); }
public static void Main() { SQLRelayConnection sqlrcon = new SQLRelayConnection("Data Source=sqlrserver:9000;User ID=user;Password=password;Retry Time=0;Tries=1;Debug=false"); sqlrcon.Open(); SQLRelayCommand sqlrcom = (SQLRelayComand)sqlrcon.CreateCommand(); sqlrcom.CommandText = "select col1,col2 from exampletable"; try { System.Data.IDataReader datareader = sqlrcom.ExecuteReader(); // read row... datareader.Read(); // get the raw data of the first field in a variety of ways Object o1 = datareader.GetValue(0); o1 = datareader[0]; o1 = datareader.GetValue(datareader.GetOrdinal("col1")); o1 = datareader["col1"]; Object[] os1 = datareader.GetValues(); o1 = os1[0]; // get the first field as a string String s1 = datareader.GetString(0); // get the raw data of the second field in a variety of ways Object o2 = datareader.GetValue(1); o2 = datareader[1]; o2 = datareader.GetValue(datareader.GetOrdinal("col2")); o2 = datareader["col2"]; Object[] os2 = datareader.GetValues(); o2 = os2[1]; // get the second field as a string String s2 = datareader.GetString(1); // read another row... datareader.Read(); ... do something with this row ... ... fetch more rows ... }
public void Start(SP.ISPDatabase sharepointDatabase, SP.SPListDefinition list, UI.IProgressNotifier notifier) { notifier.Reset("Initializing..."); _Formatter.Initialize(); SP.SPFieldDefinitionCollection oFieldList = list.Fields; notifier.SetProgress("Retrieving records from Sharepoint database...", 0); System.Data.IDataReader oReader = sharepointDatabase.GetListItemsAsReader(list, true, false); oReader.Read(); int nTotalRecords = oReader.GetInt32(0); int nProcessedRecords = 0; notifier.SetProgress(string.Format("{0} records retrieved. Now exporting...", nTotalRecords.ToString()), 10); oReader.NextResult(); while (oReader.Read()) { int nListItemID = Convert.ToInt32(oReader["ID"]); _Formatter.BeginExportRow(nListItemID.ToString()); foreach (SP.SPFieldDefinition oField in oFieldList) { _Formatter.ExportField(oField.InternalName, sharepointDatabase.GetFieldText(oField, oReader)); } if (_Formatter.SupportAttachments && oReader.GetBoolean(oReader.GetOrdinal("HasAttachments"))) { DataTable oAttachmentList = sharepointDatabase.GetListItemAttachmentsList(list, nListItemID, true); int nCounter = 1; foreach (DataRow oAttachment in oAttachmentList.Rows) { _Formatter.ExportAttachment(nCounter, oAttachment["Folder"].ToString(), oAttachment["Filename"].ToString(), (byte[])oAttachment["Content"]); nCounter++; } } _Formatter.EndExportRow(); nProcessedRecords++; if (nProcessedRecords % 100 == 0) { notifier.SetProgress(null, (short)(10 + (nProcessedRecords * 90 / nTotalRecords))); } } _Formatter.Terminate(); notifier.SetComplete(null); }
public InfoFileCaching[] ricercaDocumemtoInCache(string aggiornato, string idAmministrazione) { System.Collections.Generic.List <InfoFileCaching> list = new System.Collections.Generic.List <InfoFileCaching>(); DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_CACHE"); queryDef.setParam("idAmministrazione", "'" + idAmministrazione + "'"); queryDef.setParam("aggiornato", aggiornato); logger.Debug("ricercaDocumemtoInCache - query: " + queryDef.getSQL()); string commandText = queryDef.getSQL(); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { if (reader != null) { while (reader.Read()) { InfoFileCaching info = new InfoFileCaching(); info.Aggiornato = reader.GetInt32(reader.GetOrdinal("AGGIORNATO")); info.CacheFilePath = reader.GetString(reader.GetOrdinal("PATHCACHE")); info.DocNumber = reader.GetInt32(reader.GetOrdinal("DOCNUMBER")); info.file_size = reader.GetInt32(reader.GetOrdinal("FILE_SIZE")); info.idAmministrazione = reader.GetString(reader.GetOrdinal("idAmministrazione")); info.Version_id = reader.GetInt32(reader.GetOrdinal("VERSION_ID")); list.Add(info); } } } } return(list.ToArray()); }
/// <summary> /// Creazione oggetto "OrgFunzione" da datareader /// </summary> /// <param name="reader"></param> /// <returns></returns> private static OrgFunzione CreateFunzione(System.Data.IDataReader reader) { OrgFunzione retValue = new OrgFunzione(); if (!reader.IsDBNull(reader.GetOrdinal("ID_ASSOCIAZIONE"))) { retValue.ID = reader.GetValue(reader.GetOrdinal("ID_ASSOCIAZIONE")).ToString(); } if (!reader.IsDBNull(reader.GetOrdinal("ID_TIPO_FUNZIONE"))) { retValue.IDTipoFunzione = reader.GetValue(reader.GetOrdinal("ID_TIPO_FUNZIONE")).ToString(); } string associato = reader.GetString(reader.GetOrdinal("ASSOCIATO")); retValue.Associato = Convert.ToBoolean(associato); retValue.FunzioneAnagrafica = CreateFunzioneAnagrafica(reader); return(retValue); }
/// <summary> /// Reperimento info delle ragioni trasmissione /// </summary> /// <param name="idAmministrazione"></param> /// <returns></returns> public static OrgRagioneTrasmissione[] GetInfoRagioniTrasmissione(string idAmministrazione) { ArrayList retValue = new ArrayList(); OrgRagioneTrasmissione ragione = null; DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_DPARagTrasm_ALL"); queryDef.setParam("param1", "system_id as ID, var_desc_ragione as DESCRIZIONE, cha_tipo_dest as TIPO_DESTINATARIO"); if (idAmministrazione == null || idAmministrazione.ToUpper().Equals("NULL")) { queryDef.setParam("param2", "WHERE id_amm IS NULL"); } else { queryDef.setParam("param2", "WHERE id_amm = " + idAmministrazione); } string commandText = queryDef.getSQL(); logger.Debug(commandText); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { while (reader.Read()) { ragione = new OrgRagioneTrasmissione(); ragione.ID = reader.GetValue(reader.GetOrdinal("ID")).ToString(); ragione.Codice = reader.GetString(reader.GetOrdinal("DESCRIZIONE")); ragione.TipoDestinatario = OrgRagioneTrasmissione.ParseTipoDestinatario(reader.GetString(reader.GetOrdinal("TIPO_DESTINATARIO"))); retValue.Add(ragione); } } } return((OrgRagioneTrasmissione[])retValue.ToArray(typeof(OrgRagioneTrasmissione))); }
public object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner) { int index = rs.GetOrdinal(names[0]); if (rs.IsDBNull(index)) { return(string.Empty); } else { return(rs[index]); } }
public object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner) { ListSet result = new ListSet(); Int32 index = rs.GetOrdinal(names[0]); if (rs.IsDBNull(index) || String.IsNullOrEmpty((String)rs[index])) { return(result); } foreach (String s in ((String)rs[index]).Split(cStringSeparator)) { result.Add(s); } return(result); }
private void SetColumnIndex(System.Data.IDataReader reader) { lock (this) { if (!mLoadColumnIndex) { foreach (ReadProperty rp in Properties) { try { rp.Index = reader.GetOrdinal("p_" + rp.Mapper.Handler.Property.Name); } catch (Exception e_) { throw new PeanutException(string.Format(DataMsg.READER_COLUMN_NOFOUND, rp.Mapper.ColumnName), e_); } } mLoadColumnIndex = true; } } }
public string recuperaPathCache(InfoFileCaching info) { string path = string.Empty; DocsPaUtils.Query sql = DocsPaUtils.InitQuery.getInstance().getQuery("S_VERIFICA_FILE_IN_CACHE"); sql.setParam("docnumber", info.DocNumber.ToString()); sql.setParam("idAmministrazione", "'" + info.idAmministrazione + "'"); sql.setParam("version_id", info.Version_id.ToString()); logger.Debug("recuperaPathCache - query: " + sql.getSQL()); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { using (System.Data.IDataReader reader = dbProvider.ExecuteReader(sql.getSQL())) { if (reader != null) { while (reader.Read()) { path = reader.GetString(reader.GetOrdinal("PATHCACHE")); } } } } return(path); }
/// <summary> /// Retrieve a record from the database. /// </summary> /// <param name="id"></param> /// <returns></returns> /// <remarks></remarks> public Datafile RetrieveDatafile(int id) { DbCommand cmd = store.GetStoredProcCommand("RetrieveDatafileByID"); store.AddInParameter(cmd, "id", DbType.String, id); using (System.Data.IDataReader reader = store.ExecuteReader(cmd)) { if (reader.Read() == true) { Datafile newfile = new Datafile(); newfile.ID = reader.GetInt32(reader.GetOrdinal("id")); newfile.Category = System.Convert.ToString(reader.SafeGetString(reader.GetOrdinal("category"))); newfile.Group = System.Convert.ToString(reader.SafeGetString(reader.GetOrdinal("group"))); newfile.Filename = System.Convert.ToString(reader.SafeGetString(reader.GetOrdinal("filename"))); newfile.Extension = System.Convert.ToString(reader.SafeGetString(reader.GetOrdinal("extension"))); newfile.Content = (byte[])(reader.GetValue(reader.GetOrdinal("content"))); return(newfile); } else { return(null); } } }
public static Product GetPubByProductID(string productid) { Database db = DatabaseFactory.CreateDatabase(); //DbCommand cw = db.GetStoredProcCommand("sp_ncipl_getPubbyProductID"); DbCommand cw = db.GetStoredProcCommand("sp_KIOSK_getPubbyProductID"); db.AddInParameter(cw, "productid", DbType.String, productid); using (System.Data.IDataReader dr = db.ExecuteReader(cw)) { if (dr.Read()) { Product k = new Product( dr.GetInt32(dr.GetOrdinal("pubid")), dr["productid"].ToString(), dr["BOOKSTATUS"].ToString(), dr["DISPLAYSTATUS"].ToString(), dr["longtitle"].ToString(), dr["shorttitle"].ToString(), dr["abstract"].ToString(), dr["summary"].ToString(), (dr["RECORDUPDATEDATE"] == DBNull.Value) ? "" : dr["RECORDUPDATEDATE"].ToString(), (dr["RECORDUPDATEDATE"] == DBNull.Value) ? DateTime.MinValue : (DateTime)dr["RECORDUPDATEDATE"], dr.GetInt32(dr.GetOrdinal("ISONLINE")), dr["URL"].ToString(), dr["URL2"].ToString(), dr["thumbnailfile"].ToString(), dr.GetInt32(dr.GetOrdinal("QUANTITY_AVAILABLE")), dr.GetInt32(dr.GetOrdinal("maxqty_exhibit")), "", //nerdo field: obsolete "", //nerdo field: obsolete "", //nerdo field: obsolete "", //nerdo field: obsolete "", //nerdo field: obsolete 0, //nerdo field: obsolete 0, //nerdo field: obsolete dr["AUDIENCE"].ToString(), dr["AWARDS"].ToString(), dr["CANCERTYPE"].ToString(), dr["LANGUAGE"].ToString(), dr["FORMAT"].ToString(), dr["SERIES"].ToString(), dr["SUBJECT"].ToString(), dr["NIHNUM"].ToString(), (dr["recordcreatedate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)dr["recordcreatedate"], (dr["REVISED_MONTH"] == DBNull.Value) ? 0 : dr.GetInt32(dr.GetOrdinal("REVISED_MONTH")), (dr["REVISED_DAY"] == DBNull.Value) ? 0 : dr.GetInt32(dr.GetOrdinal("REVISED_DAY")), (dr["REVISED_YEAR"] == DBNull.Value) ? 0 : dr.GetInt32(dr.GetOrdinal("REVISED_YEAR")), (dr["REVISED_DATE"] == DBNull.Value) ? DateTime.MinValue : (DateTime)dr["REVISED_DATE"], (dr["REVISED_DATE_TYPE"] == DBNull.Value) ? "" : dr["REVISED_DATE_TYPE"].ToString(), dr.GetInt32(dr.GetOrdinal("TOTAL_NUM_PAGE")), dr["PUB_STATUS"].ToString() ); return(k); } else { throw new ArgumentException("Publication ID not found", "value"); } } }
public IValueReader this[string key] => new ValueReader(dbDataReader, dbDataReader.GetOrdinal(key));
/// <summary> /// Returns Sample Group details of the specified Sample group Key. /// </summary> /// <param name="opcGrpID">Sample group key</param> /// <returns>Sample Group entity</returns> public EtyDataLogDPGroupTrend GetOPCGrpByID(double opcGrpID) { string Function_Name = "GetOPCGrpByID"; LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0}", opcGrpID)); EtyDataLogDPGroupTrend etyDpGroupTrend = new EtyDataLogDPGroupTrend(); string localSQL = " SELECT DP.NAME,DP.DESCRIPTION,DP.SAMPLE_INTERVAL,DP.INTERVAL_TYPE,DP.START_TIME,DP.DELTA_VALUE,DP.ENABLED,DP.LOCATION_KEY,LOC.NAME AS LOCATION_NAME" + " FROM DATALOG_DP_GROUP_TREND DP,LOCATION LOC WHERE DP.PKEY = " + opcGrpID.ToString() + "AND LOC.PKEY = DP.LOCATION_KEY"; /* string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); * if (LocationClauseStr.Length != 0) * { * localSQL += " AND" + LocationClauseStr; * }*/ System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { etyDpGroupTrend.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { etyDpGroupTrend.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_INTERVAL))) { etyDpGroupTrend.Interval = Convert.ToInt32(drReader[COLUMN_SAMPLE_INTERVAL]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_INTERVAL_TYPE))) { etyDpGroupTrend.IntervalType = drReader[COLUMN_INTERVAL_TYPE].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_START_TIME))) { etyDpGroupTrend.StartTime = drReader[COLUMN_START_TIME].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DELTA_VALUE))) { etyDpGroupTrend.DeltaValue = Convert.ToDouble(drReader[COLUMN_DELTA_VALUE]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENABLED))) { etyDpGroupTrend.Disabled = !(DAOHelper.ChangeStrToBool(drReader[COLUMN_ENABLED].ToString())); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_LOCATION_KEY))) { etyDpGroupTrend.LocationKey = Convert.ToDouble(drReader[COLUMN_LOCATION_KEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(LOCATION_NAME))) { etyDpGroupTrend.LocationName = DAOHelper.convert8859P1ToGB2312(drReader[LOCATION_NAME].ToString()); } } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDpGroupTrend); }
/// <summary> /// get train timetable data /// </summary> /// <param name="train id">train id</param> /// <param name="isPlannedData">is planned data</param> /// <returns>train time table</returns> public List <TrainTimeTableData> GetTrainTimeTableData(bool isPlanned, int TrainServiceId, DateTime Date) { string FUNCTION_NAME = "GetTrainTimeTableData"; LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(), EDebugLevelManaged.DebugInfo, "Function Entered"); List <TrainTimeTableData> res; res = GetCacheTrainTimeTableData(isPlanned, TrainServiceId); if (res != null) { LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(), EDebugLevelManaged.DebugInfo, "Returning Cache Data"); return(res); } //if data returned is null, get data from database res = new List <TrainTimeTableData>(); string localSQL; if (isPlanned) { localSQL = " SELECT SERVICE_ID,ORDER_ID as STATION_ORDER_ID,A_TIME,D_TIME, DISPLAY_NAME " + " FROM ATS_PLANNED_TRAIN_DATA, LOCATION WHERE " + " SERVICE_ID = @TrainServiceId" + " AND D_TIME >= @StartDateValue" + " AND A_TIME <= @EndDateValue" + " AND STATION_ID = PKEY" + " AND STATION_ID NOT IN ( " + m_ExcludeStationList + " ) ORDER BY SERVICE_ID, A_TIME"; } else { localSQL = " SELECT SERVICE_ID,ORDER_ID as STATION_ORDER_ID,A_TIME,D_TIME, DISPLAY_NAME" + " FROM ATS_PRACTICAL_TRAIN_DATA, LOCATION WHERE " + " SERVICE_ID = @TrainServiceId" + " AND D_TIME >= @StartDateValue" + " AND A_TIME <= @EndDateValue" + " AND STATION_ID = PKEY" + " AND STATION_ID NOT IN ( " + m_ExcludeStationList + " ) ORDER BY SERVICE_ID, A_TIME"; } SqlParameter parameter1 = new SqlParameter(); parameter1.ParameterName = "TrainServiceId"; parameter1.DbType = DbType.Int32; parameter1.Value = TrainServiceId; parameter1.Direction = System.Data.ParameterDirection.Input; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(parameter1); SqlParameter parameter2 = new SqlParameter(); parameter2.ParameterName = "StartDateValue"; parameter2.DbType = DbType.DateTime; parameter2.Value = Date; parameter2.Direction = System.Data.ParameterDirection.Input; parameters.Add(parameter2); SqlParameter parameter3 = new SqlParameter(); parameter3.ParameterName = "EndDateValue"; parameter3.DbType = DbType.DateTime; parameter3.Value = Date.AddHours(23).AddMinutes(59).AddSeconds(59); parameter3.Direction = System.Data.ParameterDirection.Input; parameters.Add(parameter3); System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithParams(localSQL, parameters); try { while (drReader != null && drReader.Read()) { TrainTimeTableData timeTableData = new TrainTimeTableData(); if (!drReader.IsDBNull(drReader.GetOrdinal("A_TIME"))) { DateTime dt; if (DateTime.TryParse(drReader["A_TIME"].ToString(), out dt)) { timeTableData.ArrTime = dt; } } if (!drReader.IsDBNull(drReader.GetOrdinal("D_TIME"))) { DateTime dt; if (DateTime.TryParse(drReader["D_TIME"].ToString(), out dt)) { timeTableData.DeptTime = dt; } } if (!drReader.IsDBNull(drReader.GetOrdinal("SERVICE_ID"))) { timeTableData.TrainServiceId = Convert.ToInt32(drReader["SERVICE_ID"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("STATION_ORDER_ID"))) { timeTableData.StationID = Convert.ToInt32(drReader["STATION_ORDER_ID"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("DISPLAY_NAME"))) { timeTableData.StationName = Convert.ToString(drReader["DISPLAY_NAME"]); } res.Add(timeTableData); } } catch (System.Exception ex) { LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(), EDebugLevelManaged.DebugInfo, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(), EDebugLevelManaged.DebugInfo, "Function Entered"); return(res); }
public List <EtyDataLogDPGroupTrend> GetAllOPCGrpsBySort(string sortColumn, string sortOrder) { string Function_Name = "GetAllOPCGrpsBySort"; LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0},{1}", sortColumn, sortOrder)); List <EtyDataLogDPGroupTrend> etyDPGrpTrendList = new List <EtyDataLogDPGroupTrend>(); string localSQL; localSQL = " SELECT DP.PKEY,DP.NAME,DP.DESCRIPTION,DP.SAMPLE_INTERVAL,DP.INTERVAL_TYPE,DP.START_TIME,DP.DELTA_VALUE,DP.ENABLED,DP.LOCATION_KEY,LOC.NAME AS LOCATION_NAME " + " FROM DATALOG_DP_GROUP_TREND DP, LOCATION LOC WHERE DP.LOCATION_KEY = LOC.PKEY"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL += " ORDER BY " + sortColumn; sortOrder = sortOrder.ToUpper(); if (sortOrder.Equals("ASCENDING") || sortOrder.Equals("ASC")) { localSQL += " ASC "; } else { localSQL += " DESC "; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyDataLogDPGroupTrend etyDPGroupTrend = new EtyDataLogDPGroupTrend(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { etyDPGroupTrend.SampleGrpID = Convert.ToDouble(drReader[COLUMN_PKEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { etyDPGroupTrend.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { etyDPGroupTrend.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_INTERVAL))) { etyDPGroupTrend.Interval = Convert.ToDouble(drReader[COLUMN_SAMPLE_INTERVAL]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_INTERVAL_TYPE))) { etyDPGroupTrend.IntervalType = drReader[COLUMN_INTERVAL_TYPE].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_START_TIME))) { etyDPGroupTrend.StartTime = drReader[COLUMN_START_TIME].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DELTA_VALUE))) { etyDPGroupTrend.DeltaValue = Convert.ToDouble(drReader[COLUMN_DELTA_VALUE]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENABLED))) { if (drReader[COLUMN_ENABLED].ToString() == "Y") { etyDPGroupTrend.Disabled = false; } else { etyDPGroupTrend.Disabled = true; } } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_LOCATION_KEY))) { etyDPGroupTrend.LocationKey = Convert.ToDouble(drReader[COLUMN_LOCATION_KEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(LOCATION_NAME))) { etyDPGroupTrend.LocationName = DAOHelper.convert8859P1ToGB2312(drReader[LOCATION_NAME].ToString()); } etyDPGrpTrendList.Add(etyDPGroupTrend); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDPGrpTrendList); }
// /// <summary> // /// insert a record into TRENDVIEWER_LOG // /// </summary> // /// <param name="etyTrendLog">a record of trending log</param> // /// <returns>true/false(successful/failed)</returns> // public bool InsertTrendViewerLog(EtyTrendLog etyTrendLog) // { // string Function_Name = "InsertTrendViewerLog"; // LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); // bool result = false; // /*string localSQL = " INSERT INTO TRENDVIEWER_LOG(DATA_PT_HOST," + // "DATA_PT_SERVER,DATA_PT_NAME,DATA_PT_VALUE,DATA_PT_DATE) " + // " VALUES( '" + etyTrendLog.Data_PT_Host + "'" + // " ,'" + etyTrendLog.Data_PT_Server + "'" + // " ,'" + etyTrendLog.Data_PT_Name + "'" + // " ,'" + etyTrendLog.Data_PT_Value + "'" + // " , to_date('" + etyTrendLog.Data_PT_Time.ToString("yyyyMMddHHmmss") + "','YYYYMMDDHH24MISS') " + // " )"; // result = SimpleDatabase.getInstance().ExecuteAndReturnNonQuery(localSQL);*/ // string localSQL = " INSERT INTO TRENDVIEWER_LOG(DATA_PT_HOST," + // "DATA_PT_SERVER,DATA_PT_NAME,DATA_PT_VALUE,DATA_PT_DATE) " + // " VALUES( '" + etyTrendLog.Data_PT_Host + "'" + // " ,'" + etyTrendLog.Data_PT_Server + "'" + // " ,'" + etyTrendLog.Data_PT_Name + "'" + // " ,'" + etyTrendLog.Data_PT_Value + "'" + // " ," + SimpleDatabase.GetInstance().GetParameterDelimiter() + "DateValue)"; // SqlParameter parameter = new SqlParameter(); // parameter.ParameterName = "DateValue"; // parameter.DbType = DbType.DateTime; // parameter.Value = etyTrendLog.Data_PT_Time; // parameter.Direction = System.Data.ParameterDirection.Input; // List<SqlParameter> parameters = new List<SqlParameter>(); // parameters.Add(parameter); // result = SimpleDatabase.GetInstance().ExecuteNonQueryWithParams(localSQL, parameters); // // LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); // return result; // } /// <summary> /// get dp log list for a historical dp to plot /// </summary> /// <param name="histDP">the given historical dp for charting</param> /// <param name="startTime">start time</param> /// <param name="endTime">end time</param> /// <returns>trend log list</returns> public List <EtyDataLogDPLogTrend> GetHistDPLogList(EtyHistDataPoint histDP, DateTime startTime, DateTime endTime) { string Function_Name = "GetHistDPLogList"; List <EtyDataLogDPLogTrend> res = new List <EtyDataLogDPLogTrend>(); string localSQL; //changed by luxiangmei, to make the logic for historical mode the same as mixed mode. // if (usedInMixMode) //used in mixed mode // { localSQL = " SELECT CREATETIME,VALUE " + " FROM DATALOG_DP_LOG_TREND WHERE ENTITY_KEY = " + histDP.EntityKey + " AND CREATETIME >= @StartDateValue" + " AND CREATETIME <= @EndDateValue" + " ORDER BY CREATETIME"; SqlParameter parameter1 = new SqlParameter(); parameter1.ParameterName = "StartDateValue"; parameter1.DbType = DbType.DateTime; DateTime dtStart = new DateTime(histDP.DPStartDateTime.Year, histDP.DPStartDateTime.Month, histDP.DPStartDateTime.Day, startTime.Hour, startTime.Minute, startTime.Second); parameter1.Value = dtStart; parameter1.Direction = System.Data.ParameterDirection.Input; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(parameter1); SqlParameter parameter2 = new SqlParameter(); parameter2.ParameterName = "EndDateValue"; parameter2.DbType = DbType.DateTime; DateTime dtEnd = new DateTime(histDP.DPEndDateTime.Year, histDP.DPEndDateTime.Month, histDP.DPEndDateTime.Day, endTime.Hour, endTime.Minute, endTime.Second); parameter2.Value = dtEnd; parameter2.Direction = System.Data.ParameterDirection.Input; parameters.Add(parameter2); System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithParams(localSQL, parameters); try { while (drReader != null && drReader.Read()) { EtyDataLogDPLogTrend entity = new EtyDataLogDPLogTrend(); if (!drReader.IsDBNull(drReader.GetOrdinal("CREATETIME"))) { DateTime dt; if (DateTime.TryParse(drReader["CREATETIME"].ToString(), out dt)) { entity.CreateTime = dt; } } if (!drReader.IsDBNull(drReader.GetOrdinal("VALUE"))) { entity.Value = Convert.ToDouble(drReader["VALUE"]); } res.Add(entity); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } return(res); }
public static void Main(String[] args) { tlscert = "/usr/local/firstworks/etc/sqlrelay.conf.d/client.pem"; tlsca = "/usr/local/firstworks/etc/sqlrelay.conf.d/ca.pem"; if (Environment.OSVersion.ToString().Contains("Windows")) { tlscert = "C:\\Program Files\\Firstworks\\etc\\sqlrelay.conf.d\\client.pfx"; tlsca = "C:\\Program Files\\Firstworks\\etc\\sqlrelay.conf.d\\ca.pfx"; } // open connection and command SQLRelayConnection sqlrcon = new SQLRelayConnection("Data Source=sqlrelay:9000:/tmp/test.socket;Retry Time=0;Tries=1;Tls=yes;Tlscert=" + tlscert + ";Tlsvalidate=ca;Tlsca=" + tlsca + ";Debug=false"); sqlrcon.Open(); // execute scalar SQLRelayCommand sqlrcom = (SQLRelayCommand)sqlrcon.CreateCommand(); Console.WriteLine("EXECUTE SCALAR:"); sqlrcom.CommandText = "select 1 from dual"; Int64 value = ExecuteScalar(sqlrcom); checkSuccess(value, 1); Console.WriteLine("\n"); // drop the table Console.WriteLine("DROP TABLE:"); sqlrcom = new SQLRelayCommand("drop table testtable"); sqlrcom.Connection = sqlrcon; ExecuteNonQuery(sqlrcom); Console.WriteLine("\n"); // create the table Console.WriteLine("CREATE TABLE:"); sqlrcom = new SQLRelayCommand("create table testtable (testnumber number, testchar char(40), testvarchar varchar2(40), testdate date, testlong long, testclob clob, testblob blob)", sqlrcon); ExecuteNonQuery(sqlrcom); Console.WriteLine("\n"); // insert Console.WriteLine("INSERT:"); sqlrcom.CommandText = "insert into testtable values (1, 'testchar1', 'testvarchar1', '01-JAN-2001', 'testlong1', 'testclob1', empty_blob())"; sqlrcom.Prepare(); Int64 affectedrows = ExecuteNonQuery(sqlrcom); Console.WriteLine("\n"); // affected rows Console.WriteLine("AFFECTED ROWS:"); checkSuccess(affectedrows, 1); Console.WriteLine("\n"); // bind by position Console.WriteLine("BIND BY POSITION:"); sqlrcom.CommandText = "insert into testtable values (:var1, :var2, :var3, :var4, :var5, :var6, :var7)"; sqlrcom.Parameters.Add("1", 2); sqlrcom.Parameters.Add("2", "testchar2"); sqlrcom.Parameters.Add("3", "testvarchar2"); sqlrcom.Parameters.Add("4", new DateTime(2001, 1, 1, 0, 0, 0, 0)); sqlrcom.Parameters.Add("5", "testlong2"); SQLRelayParameter var6 = new SQLRelayParameter(); var6.ParameterName = "6"; var6.Value = "testclob2"; var6.SQLRelayType = SQLRelayType.Clob; sqlrcom.Parameters.Add(var6); SQLRelayParameter var7 = new SQLRelayParameter(); var7.ParameterName = "7"; var7.Value = System.Text.Encoding.Default.GetBytes("testblob2"); var7.SQLRelayType = SQLRelayType.Blob; sqlrcom.Parameters.Add(var7); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); sqlrcom.Parameters.Add("1", 3); sqlrcom.Parameters.Add("2", "testchar3"); sqlrcom.Parameters.Add("3", "testvarchar3"); sqlrcom.Parameters.Add("4", new DateTime(2003, 1, 1, 0, 0, 0, 0)); sqlrcom.Parameters.Add("5", "testlong3"); var6.Value = "testclob3"; sqlrcom.Parameters.Add(var6); var7.Value = System.Text.Encoding.Default.GetBytes("testblob3"); var7.SQLRelayType = SQLRelayType.Object; var7.DbType = DbType.Binary; sqlrcom.Parameters.Add(var7); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // bind by name sqlrcom.Parameters.Add("var1", 4); sqlrcom.Parameters.Add("var2", "testchar4"); sqlrcom.Parameters.Add("var3", "testvarchar4"); sqlrcom.Parameters.Add("var4", new DateTime(2004, 1, 1, 0, 0, 0, 0)); sqlrcom.Parameters.Add("var5", "testlong4"); var6.Value = "testclob4"; sqlrcom.Parameters.Add(var6); var7.Value = System.Text.Encoding.Default.GetBytes("testblob4"); sqlrcom.Parameters.Add(var7); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); sqlrcom.Parameters.Add("var1", 5); sqlrcom.Parameters.Add("var2", "testchar5"); sqlrcom.Parameters.Add("var3", "testvarchar5"); sqlrcom.Parameters.Add("var4", new DateTime(2005, 1, 1, 0, 0, 0, 0)); sqlrcom.Parameters.Add("var5", "testlong5"); var6.Value = "testclob5"; sqlrcom.Parameters.Add(var6); var7.Value = System.Text.Encoding.Default.GetBytes("testblob5"); sqlrcom.Parameters.Add(var7); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // null and empty binds Console.WriteLine("NULL BINDS:"); sqlrcom.Parameters.Add("1", null); sqlrcom.Parameters.Add("2", null); sqlrcom.Parameters.Add("3", null); sqlrcom.Parameters.Add("4", null); sqlrcom.Parameters.Add("5", null); sqlrcom.Parameters.Add("6", null); sqlrcom.Parameters.Add("7", null); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // select Console.WriteLine("SELECT:"); sqlrcom.CommandText = "select * from testtable order by testnumber"; System.Data.IDataReader datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); Console.WriteLine("\n"); // column count Console.WriteLine("COLUMN COUNT:"); checkSuccess(datareader.FieldCount, 7); Console.WriteLine("\n"); // column names Console.WriteLine("COLUMN NAMES:"); checkSuccess(datareader.GetName(0), "TESTNUMBER"); checkSuccess(datareader.GetName(1), "TESTCHAR"); checkSuccess(datareader.GetName(2), "TESTVARCHAR"); checkSuccess(datareader.GetName(3), "TESTDATE"); checkSuccess(datareader.GetName(4), "TESTLONG"); checkSuccess(datareader.GetName(5), "TESTCLOB"); checkSuccess(datareader.GetName(6), "TESTBLOB"); Console.WriteLine("\n"); // column types Console.WriteLine("COLUMN TYPES:"); checkSuccess(datareader.GetDataTypeName(0), "NUMBER"); checkSuccess(datareader.GetFieldType(0).ToString(), "System.Int64"); checkSuccess(datareader.GetDataTypeName(1), "CHAR"); checkSuccess(datareader.GetFieldType(1).ToString(), "System.String"); checkSuccess(datareader.GetDataTypeName(2), "VARCHAR2"); checkSuccess(datareader.GetFieldType(2).ToString(), "System.String"); checkSuccess(datareader.GetDataTypeName(3), "DATE"); checkSuccess(datareader.GetFieldType(3).ToString(), "System.DateTime"); checkSuccess(datareader.GetDataTypeName(4), "LONG"); checkSuccess(datareader.GetFieldType(4).ToString(), "System.Byte[]"); checkSuccess(datareader.GetDataTypeName(5), "CLOB"); checkSuccess(datareader.GetFieldType(5).ToString(), "System.String"); checkSuccess(datareader.GetDataTypeName(6), "BLOB"); checkSuccess(datareader.GetFieldType(6).ToString(), "System.Byte[]"); Console.WriteLine("\n"); // schema table Console.WriteLine("SCHEMA TABLE:"); DataTable schematable = datareader.GetSchemaTable(); checkSuccess(Convert.ToString(schematable.Rows[0]["ColumnName"]), "TESTNUMBER"); checkSuccess(Convert.ToInt64(schematable.Rows[0]["ColumnOrdinal"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[0]["ColumnSize"]), 22); checkSuccess(Convert.ToInt64(schematable.Rows[0]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[0]["NumericScale"]), 129); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseColumnName"]), "TESTNUMBER"); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["DataType"]), "System.Int64"); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[0]["ProviderType"]), "NUMBER"); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[0]["ProviderSpecificDataType"]), "NUMBER"); checkSuccess(Convert.ToString(schematable.Rows[0]["DataTypeName"]), "NUMBER"); checkSuccess(Convert.ToString(schematable.Rows[0]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["ColumnName"]), "TESTCHAR"); checkSuccess(Convert.ToInt64(schematable.Rows[1]["ColumnOrdinal"]), 1); checkSuccess(Convert.ToInt64(schematable.Rows[1]["ColumnSize"]), 40); checkSuccess(Convert.ToInt64(schematable.Rows[1]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[1]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseColumnName"]), "TESTCHAR"); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["DataType"]), "System.String"); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[1]["ProviderType"]), "CHAR"); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[1]["ProviderSpecificDataType"]), "CHAR"); checkSuccess(Convert.ToString(schematable.Rows[1]["DataTypeName"]), "CHAR"); checkSuccess(Convert.ToString(schematable.Rows[1]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["ColumnName"]), "TESTVARCHAR"); checkSuccess(Convert.ToInt64(schematable.Rows[2]["ColumnOrdinal"]), 2); checkSuccess(Convert.ToInt64(schematable.Rows[2]["ColumnSize"]), 40); checkSuccess(Convert.ToInt64(schematable.Rows[2]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[2]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseColumnName"]), "TESTVARCHAR"); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["DataType"]), "System.String"); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[2]["ProviderType"]), "VARCHAR2"); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[2]["ProviderSpecificDataType"]), "VARCHAR2"); checkSuccess(Convert.ToString(schematable.Rows[2]["DataTypeName"]), "VARCHAR2"); checkSuccess(Convert.ToString(schematable.Rows[2]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["ColumnName"]), "TESTDATE"); checkSuccess(Convert.ToInt64(schematable.Rows[3]["ColumnOrdinal"]), 3); checkSuccess(Convert.ToInt64(schematable.Rows[3]["ColumnSize"]), 7); checkSuccess(Convert.ToInt64(schematable.Rows[3]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[3]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseColumnName"]), "TESTDATE"); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["DataType"]), "System.DateTime"); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[3]["ProviderType"]), "DATE"); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[3]["ProviderSpecificDataType"]), "DATE"); checkSuccess(Convert.ToString(schematable.Rows[3]["DataTypeName"]), "DATE"); checkSuccess(Convert.ToString(schematable.Rows[3]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["ColumnName"]), "TESTLONG"); checkSuccess(Convert.ToInt64(schematable.Rows[4]["ColumnOrdinal"]), 4); checkSuccess(Convert.ToInt64(schematable.Rows[4]["ColumnSize"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[4]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[4]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseColumnName"]), "TESTLONG"); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["DataType"]), "System.Byte[]"); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[4]["ProviderType"]), "LONG"); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsLong"]), true); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[4]["ProviderSpecificDataType"]), "LONG"); checkSuccess(Convert.ToString(schematable.Rows[4]["DataTypeName"]), "LONG"); checkSuccess(Convert.ToString(schematable.Rows[4]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["ColumnName"]), "TESTCLOB"); checkSuccess(Convert.ToInt64(schematable.Rows[5]["ColumnOrdinal"]), 5); checkSuccess(Convert.ToInt64(schematable.Rows[5]["ColumnSize"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[5]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[5]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseColumnName"]), "TESTCLOB"); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["DataType"]), "System.String"); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[5]["ProviderType"]), "CLOB"); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[5]["ProviderSpecificDataType"]), "CLOB"); checkSuccess(Convert.ToString(schematable.Rows[5]["DataTypeName"]), "CLOB"); checkSuccess(Convert.ToString(schematable.Rows[5]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["ColumnName"]), "TESTBLOB"); checkSuccess(Convert.ToInt64(schematable.Rows[6]["ColumnOrdinal"]), 6); checkSuccess(Convert.ToInt64(schematable.Rows[6]["ColumnSize"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[6]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[6]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseColumnName"]), "TESTBLOB"); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["DataType"]), "System.Byte[]"); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[6]["ProviderType"]), "BLOB"); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsLong"]), true); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[6]["ProviderSpecificDataType"]), "BLOB"); checkSuccess(Convert.ToString(schematable.Rows[6]["DataTypeName"]), "BLOB"); checkSuccess(Convert.ToString(schematable.Rows[6]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["XmlSchemaCollectionName"]), ""); Console.WriteLine("\n"); // fields by index Console.WriteLine("FIELDS BY INDEX:"); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt16(0), 1); checkSuccess(datareader.GetInt32(0), 1); checkSuccess(datareader.GetInt64(0), 1); checkSuccess(Convert.ToInt64(datareader[0]), 1); checkSuccess(datareader.GetString(1), "testchar1 "); checkSuccess(Convert.ToString(datareader[1]), "testchar1 "); checkSuccess(datareader.GetString(2), "testvarchar1"); checkSuccess(Convert.ToString(datareader[2]), "testvarchar1"); checkSuccess(datareader.GetString(3), "01-JAN-01"); checkSuccess(Convert.ToString(datareader[3]), "1/1/2001 12:00:00 AM"); checkSuccess(datareader.GetString(4), "testlong1"); checkSuccess(System.Text.Encoding.Default.GetString((Byte[])datareader[4]), "testlong1"); checkSuccess(datareader.GetString(5), "testclob1"); checkSuccess(Convert.ToString(datareader[5]), "testclob1"); checkSuccess(datareader[6], null); Console.WriteLine("\n"); // fields by name Console.WriteLine("FIELDS BY NAME:"); checkSuccess(Convert.ToInt64(datareader["TESTNUMBER"]), 1); checkSuccess(datareader.GetInt16(datareader.GetOrdinal("TESTNUMBER")), 1); checkSuccess(datareader.GetInt32(datareader.GetOrdinal("TESTNUMBER")), 1); checkSuccess(datareader.GetInt64(datareader.GetOrdinal("TESTNUMBER")), 1); checkSuccess(Convert.ToString(datareader["TESTCHAR"]), "testchar1 "); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTCHAR")), "testchar1 "); checkSuccess(Convert.ToString(datareader["TESTVARCHAR"]), "testvarchar1"); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTVARCHAR")), "testvarchar1"); checkSuccess(Convert.ToString(datareader["TESTDATE"]), "1/1/2001 12:00:00 AM"); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTDATE")), "01-JAN-01"); checkSuccess(System.Text.Encoding.Default.GetString((Byte[])datareader["TESTLONG"]), "testlong1"); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTLONG")), "testlong1"); checkSuccess(Convert.ToString(datareader["TESTCLOB"]), "testclob1"); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTCLOB")), "testclob1"); checkSuccess(datareader["TESTBLOB"], null); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTBLOB")), ""); Console.WriteLine("\n"); // fields by array Console.WriteLine("FIELDS BY ARRAY:"); Object[] fields = new Object[datareader.FieldCount]; checkSuccess((Int64)datareader.GetValues(fields), datareader.FieldCount); checkSuccess(Convert.ToInt64(fields[0]), 1); checkSuccess(Convert.ToString(fields[1]), "testchar1 "); checkSuccess(Convert.ToString(fields[2]), "testvarchar1"); checkSuccess(Convert.ToString(fields[3]), "1/1/2001 12:00:00 AM"); checkSuccess(System.Text.Encoding.Default.GetString((Byte[])fields[4]), "testlong1"); checkSuccess(Convert.ToString(fields[5]), "testclob1"); checkSuccess(fields[6], null); Console.WriteLine("\n"); // more rows Console.WriteLine("MORE ROWS:"); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt64(0), 2); checkSuccess(System.Text.Encoding.Default.GetString((Byte[])datareader[6]), "testblob2"); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt64(0), 3); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt64(0), 4); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt64(0), 5); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetString(0), ""); checkSuccess(datareader.Read(), false); checkSuccess(datareader.GetString(0), null); Console.WriteLine("\n"); // commit and rollback Console.WriteLine("COMMIT AND ROLLBACK:"); SQLRelayConnection sqlrcon2 = new SQLRelayConnection("Data Source=sqlrelay:9000:/tmp/test.socket;Retry Time=0;Tries=1;Tls=yes;Tlscert=" + tlscert + ";Tlsvalidate=ca;Tlsca=" + tlsca + ";Debug=false"); sqlrcon2.Open(); SQLRelayCommand sqlrcom2 = new SQLRelayCommand("select count(*) from testtable", sqlrcon2); checkSuccess(Convert.ToInt64(sqlrcom2.ExecuteScalar()), 0); SQLRelayTransaction sqlrtran = sqlrcon.BeginTransaction(); sqlrtran.Commit(); checkSuccess(Convert.ToInt64(sqlrcom2.ExecuteScalar()), 6); sqlrtran = sqlrcon.BeginTransaction(); sqlrcom.CommandText = "insert into testtable values (6, 'testchar6', 'testvarchar6', '01-JAN-2006', 'testlong6', 'testclob6', empty_blob())"; checkSuccess(sqlrcom.ExecuteNonQuery(), 1); sqlrcom.CommandText = "select count(*) from testtable"; checkSuccess(Convert.ToInt64(sqlrcom.ExecuteScalar()), 7); sqlrtran.Rollback(); checkSuccess(Convert.ToInt64(sqlrcom2.ExecuteScalar()), 6); sqlrcon2.Close(); Console.WriteLine("\n"); // output bind by name Console.WriteLine("OUTPUT BINDS BY NAME:"); sqlrcom.CommandText = "begin :numvar:=1; :stringvar:='hello'; :floatvar:=2.5; :datevar:='03-FEB-2001'; end;"; SQLRelayParameter numvar = new SQLRelayParameter(); numvar.ParameterName = "numvar"; numvar.Direction = ParameterDirection.Output; numvar.DbType = DbType.Int64; sqlrcom.Parameters.Add(numvar); SQLRelayParameter stringvar = new SQLRelayParameter(); stringvar.ParameterName = "stringvar"; stringvar.Direction = ParameterDirection.Output; stringvar.DbType = DbType.String; stringvar.Size = 20; sqlrcom.Parameters.Add(stringvar); SQLRelayParameter floatvar = new SQLRelayParameter(); floatvar.ParameterName = "floatvar"; floatvar.Direction = ParameterDirection.Output; floatvar.DbType = DbType.Double; sqlrcom.Parameters.Add(floatvar); SQLRelayParameter datevar = new SQLRelayParameter(); datevar.ParameterName = "datevar"; datevar.Direction = ParameterDirection.Output; datevar.DbType = DbType.DateTime; sqlrcom.Parameters.Add(datevar); checkSuccess(ExecuteNonQuery(sqlrcom), 1); checkSuccess(Convert.ToInt64(numvar.Value), 1); checkSuccess(Convert.ToString(stringvar.Value), "hello"); checkSuccess(Convert.ToInt64(stringvar.Size), 5); checkSuccess(Convert.ToString(floatvar.Value), "2.5"); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Year), 2001); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Month), 2); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Day), 3); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // output bind by position Console.WriteLine("OUTPUT BINDS BY POSITION:"); sqlrcom.CommandText = "begin :numvar:=1; :stringvar:='hello'; :floatvar:=2.5; :datevar:='03-FEB-2001'; end;"; numvar = new SQLRelayParameter(); numvar.ParameterName = "1"; numvar.Direction = ParameterDirection.Output; numvar.DbType = DbType.Int64; sqlrcom.Parameters.Add(numvar); stringvar = new SQLRelayParameter(); stringvar.ParameterName = "2"; stringvar.Direction = ParameterDirection.Output; stringvar.DbType = DbType.String; stringvar.Size = 20; sqlrcom.Parameters.Add(stringvar); floatvar = new SQLRelayParameter(); floatvar.ParameterName = "3"; floatvar.Direction = ParameterDirection.Output; floatvar.DbType = DbType.Double; sqlrcom.Parameters.Add(floatvar); datevar = new SQLRelayParameter(); datevar.ParameterName = "4"; datevar.Direction = ParameterDirection.Output; datevar.DbType = DbType.DateTime; sqlrcom.Parameters.Add(datevar); checkSuccess(ExecuteNonQuery(sqlrcom), 1); checkSuccess(Convert.ToInt64(numvar.Value), 1); checkSuccess(Convert.ToString(stringvar.Value), "hello"); checkSuccess(Convert.ToInt64(stringvar.Size), 5); checkSuccess(Convert.ToString(floatvar.Value), "2.5"); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Year), 2001); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Month), 2); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Day), 3); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // cursor binds using NextResult Console.WriteLine("CURSOR BINDS USING NEXTRESULT:"); sqlrcom.CommandText = "create or replace package types is type cursorType is ref cursor; end;"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "create or replace function sp_testtable(value in number) return types.cursortype is l_cursor types.cursorType; begin open l_cursor for select * from testtable where testnumber>value; return l_cursor; end;"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "begin :curs1:=sp_testtable(2); :curs2:=sp_testtable(0); end;"; SQLRelayParameter curs1 = new SQLRelayParameter(); curs1.ParameterName = "curs1"; curs1.SQLRelayType = SQLRelayType.Cursor; curs1.Direction = ParameterDirection.Output; curs1.Value = null; sqlrcom.Parameters.Add(curs1); SQLRelayParameter curs2 = new SQLRelayParameter(); curs2.ParameterName = "curs2"; curs2.SQLRelayType = SQLRelayType.Cursor; curs2.Direction = ParameterDirection.Output; curs2.Value = null; sqlrcom.Parameters.Add(curs2); datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); sqlrcom.Parameters.Clear(); datareader.Read(); checkSuccess(datareader.GetInt64(0), 3); datareader.Read(); checkSuccess(datareader.GetInt64(0), 4); datareader.Read(); checkSuccess(datareader.GetInt64(0), 5); datareader.Close(); checkSuccess(datareader.NextResult(), true); datareader.Read(); checkSuccess(datareader.GetInt64(0), 1); datareader.Read(); checkSuccess(datareader.GetInt64(0), 2); datareader.Read(); checkSuccess(datareader.GetInt64(0), 3); datareader.Close(); checkSuccess(datareader.NextResult(), false); sqlrcom.CommandText = "drop package types"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // cursor binds Console.WriteLine("CURSOR BINDS:"); sqlrcom.CommandText = "create or replace package types is type cursorType is ref cursor; end;"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "create or replace function sp_testtable(value in number) return types.cursortype is l_cursor types.cursorType; begin open l_cursor for select * from testtable where testnumber>value; return l_cursor; end;"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "begin :curs1:=sp_testtable(2); :curs2:=sp_testtable(0); end;"; curs1 = new SQLRelayParameter(); curs1.ParameterName = "curs1"; curs1.SQLRelayType = SQLRelayType.Cursor; curs1.Direction = ParameterDirection.Output; curs1.Value = null; sqlrcom.Parameters.Add(curs1); curs2 = new SQLRelayParameter(); curs2.ParameterName = "curs2"; curs2.SQLRelayType = SQLRelayType.Cursor; curs2.Direction = ParameterDirection.Output; curs2.Value = null; sqlrcom.Parameters.Add(curs2); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); SQLRelayDataReader curs1reader = (SQLRelayDataReader)curs1.Value; curs1reader.Read(); checkSuccess(curs1reader.GetInt64(0), 3); curs1reader.Read(); checkSuccess(curs1reader.GetInt64(0), 4); curs1reader.Read(); checkSuccess(curs1reader.GetInt64(0), 5); curs1reader.Close(); SQLRelayDataReader curs2reader = (SQLRelayDataReader)curs2.Value; curs2reader.Read(); checkSuccess(curs2reader.GetInt64(0), 1); curs2reader.Read(); checkSuccess(curs2reader.GetInt64(0), 2); curs2reader.Read(); checkSuccess(curs2reader.GetInt64(0), 3); curs2reader.Close(); sqlrcom.CommandText = "drop package types"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // clob and blob output bind Console.WriteLine("CLOB AND BLOB OUTPUT BINDS:"); sqlrcom.CommandText = "drop table testtable1"; ExecuteNonQuery(sqlrcom); sqlrcom.CommandText = "create table testtable1 (testclob clob, testblob blob)"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "insert into testtable1 values ('hello', :var1)"; SQLRelayParameter var1 = new SQLRelayParameter(); var1.ParameterName = "var1"; var1.Value = System.Text.Encoding.Default.GetBytes("hello"); var1.SQLRelayType = SQLRelayType.Blob; sqlrcom.Parameters.Add(var1); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); sqlrcom.CommandText = "begin select testclob into :clobvar from testtable1; select testblob into :blobvar from testtable1; end;"; SQLRelayParameter clobvar = new SQLRelayParameter(); clobvar.Direction = ParameterDirection.Output; clobvar.ParameterName = "clobvar"; clobvar.SQLRelayType = SQLRelayType.Clob; sqlrcom.Parameters.Add(clobvar); SQLRelayParameter blobvar = new SQLRelayParameter(); blobvar.ParameterName = "blobvar"; blobvar.SQLRelayType = SQLRelayType.Blob; blobvar.Direction = ParameterDirection.Output; sqlrcom.Parameters.Add(blobvar); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); checkSuccess(Convert.ToString(clobvar.Value), "hello", 5); checkSuccess(clobvar.Size, 5); checkSuccess(System.Text.Encoding.Default.GetString((byte[])blobvar.Value), "hello"); checkSuccess(blobvar.Size, 5); sqlrcom.CommandText = "drop table testtable1"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // null and empty clobs and blobs Console.WriteLine("NULL AND EMPTY CLOBS AND BLOBS:"); sqlrcom.CommandText = "create table testtable1 (testclob1 clob, testclob2 clob, testblob1 blob, testblob2 blob)"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "insert into testtable1 values (:testclob1, :testclob2, :testblob1, :testblob2)"; SQLRelayParameter testclob1 = new SQLRelayParameter(); testclob1.ParameterName = "testclob1"; testclob1.SQLRelayType = SQLRelayType.Clob; testclob1.Value = ""; sqlrcom.Parameters.Add(testclob1); SQLRelayParameter testclob2 = new SQLRelayParameter(); testclob2.ParameterName = "testclob2"; testclob2.SQLRelayType = SQLRelayType.Clob; testclob2.Value = null; sqlrcom.Parameters.Add(testclob2); SQLRelayParameter testblob1 = new SQLRelayParameter(); testblob1.ParameterName = "testblob1"; testblob1.SQLRelayType = SQLRelayType.Blob; testblob1.Value = System.Text.Encoding.Default.GetBytes(""); sqlrcom.Parameters.Add(testblob1); SQLRelayParameter testblob2 = new SQLRelayParameter(); testblob2.ParameterName = "testblob2"; testblob2.SQLRelayType = SQLRelayType.Blob; testblob2.Value = null; sqlrcom.Parameters.Add(testblob2); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); sqlrcom.CommandText = "select * from testtable1"; datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); checkSuccess(datareader.Read(), true); // FIXME: I'd expect these to come out as empty strings, not null's checkSuccess(datareader.GetString(0), ""); checkSuccess(datareader.GetString(1), ""); checkSuccess(datareader.GetString(2), ""); checkSuccess(datareader.GetString(3), ""); sqlrcom.CommandText = "drop table testtable1"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // switching connection of command Console.WriteLine("SWITCHING CONNECTION OF COMMAND:"); sqlrcom.Connection = sqlrcon2; sqlrcom.CommandText = "select count(*) from testtable"; try { sqlrcom.ExecuteScalar(); checkSuccess(false, true); } catch { // this should fail because sqlrcon2 was closed earlier checkSuccess(true, true); } sqlrcom.Connection = sqlrcon; checkSuccess(ExecuteScalar(sqlrcom), 6); Console.WriteLine("\n"); // closed datareader Console.WriteLine("CLOSED DATAREADER:"); sqlrcom.CommandText = "select * from testtable"; datareader = sqlrcom.ExecuteReader(); checkSuccess(datareader != null, true); datareader.Read(); datareader.Close(); checkSuccess(datareader.IsClosed, true); try { datareader.Read(); checkSuccess(false, true); } catch { // this should fail because datareader was closed earlier checkSuccess(true, true); } Console.WriteLine("\n"); // has rows Console.WriteLine("HAS ROWS:"); sqlrcom.CommandText = "select * from testtable"; datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); checkSuccess(((SQLRelayDataReader)datareader).HasRows, true); sqlrcom.CommandText = "delete from testtable"; checkSuccess(ExecuteNonQuery(sqlrcom), 6); sqlrcom.CommandText = "select * from testtable"; datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); checkSuccess(((SQLRelayDataReader)datareader).HasRows, false); Console.WriteLine("\n"); // drop table Console.WriteLine("DROP TABLE:"); sqlrcom.CommandText = "drop table testtable"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // invalid queries sqlrcon.Close(); }
/// <summary> /// /// </summary> /// <param name="rs"></param> /// <param name="name"></param> /// <returns></returns> public override object Get(System.Data.IDataReader rs, string name) { return(Get(rs, rs.GetOrdinal(name))); }