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