private static void SetSeriesSchema(System.Data.IDataReader reader, Dictionary <int, string> yFieldNames, string xFieldName, List <ChartSeries> series, Chart chart)
{
if (chart.ChartType == ChartType.Gauge)
{
if (reader.FieldCount == 0)
{
throw new DuradosException("Select statement must contain 1 column");
}
string fieldName = reader.GetName(0);
yFieldNames.Add(0, fieldName);
series.Add(new ChartSeries()
{
name = fieldName, data = new List <object>()
});
return;
}
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (!fieldName.Equals(xFieldName, StringComparison.InvariantCultureIgnoreCase) || string.IsNullOrEmpty(xFieldName) && i > 0)
{
yFieldNames.Add(i, fieldName);
series.Add(new ChartSeries()
{
name = fieldName, data = new List <object>()
});
}
}
}
public void OnReadAll(IDataWriter <string> dataWriter)
{
for (int i = 0; i < dbDataReader.FieldCount; i++)
{
ValueInterface.WriteValue(dataWriter[dbDataReader.GetName(i)], dbDataReader[i]);
}
}
/// <summary>
/// 根据数据阅读器对象，查询实体对象集合(注意查询完毕将自动释放该阅读器对象)
/// </summary>
/// <param name="reader">数据阅读器对象</param>
/// <returns>实体类集合</returns>
public static List <T> QueryList(System.Data.IDataReader reader)
{
List <T> list = new List <T>();
using (reader)
{
if (reader.Read())
{
int fcount = reader.FieldCount;
string[] names = new string[fcount];
object[] values = null;// new object[fcount];
for (int i = 0; i < fcount; i++)
{
names[i] = reader.GetName(i);
}
do
{
values = new object[fcount];
reader.GetValues(values);
T t = new T();
t.PropertyNames = names;
t.PropertyValues = values;
list.Add(t);
} while (reader.Read());
}
}
return(list);
}
/// <summary>
/// Mapea una entidad desde la BD
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private static IList <T> MapearEntidad(System.Data.IDataReader reader)
{
IList <T> Result = new List <T>();
if (!reader.IsClosed)
{
while (reader.Read())
{
T Entidad = Reflection.createObject <T>();
for (int i = 0; i < reader.FieldCount; i++)
{
String columnName = "";
columnName = reader.GetName(i);
Object value = GetValue(reader, columnName);
if (value != null)
{
Reflection.setValueProperty(Entidad, GetPropertyName(TitleCaseString(columnName)), value);
}
}
Result.Add(Entidad);
}
}
return(Result);
}
public static System.Data.DataTable ConverDataReaderToDataTable(System.Data.IDataReader reader)
{
System.Data.DataTable result;
if (null == reader)
{
result = null;
}
else
{
System.Data.DataTable dataTable = new System.Data.DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
int fieldCount = reader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
}
dataTable.BeginLoadData();
object[] values = new object[fieldCount];
while (reader.Read())
{
reader.GetValues(values);
dataTable.LoadDataRow(values, true);
}
dataTable.EndLoadData();
result = dataTable;
}
return(result);
}
/// <summary>
/// 创建 String 系统数据读取器的字段名称迭代器。
/// </summary>
/// <param name="dbDataReader">系统数据读取器</param>
/// <returns>返回一个 yield 关键字实现的迭代器</returns>
public static IEnumerable <string> CreateNamesIterator(System.Data.IDataReader dbDataReader)
{
var length = dbDataReader.FieldCount;
for (int i = 0; i < length; i++)
{
yield return(dbDataReader.GetName(i));
}
}
public static List <string> GetDataReaderField(System.Data.IDataReader dr)
{
System.Collections.Generic.List <string> fields = new System.Collections.Generic.List <string>();
for (int i = 0; i < dr.FieldCount; i++)
{
fields.Add(dr.GetName(i));
}
return(fields);
}
/// <summary>
/// Allow to verify if db information has a specify column name
/// </summary>
/// <param name="dr">IDataReader from db</param>
/// <param name="columnName">Column name</param>
/// <returns>Boolean</returns>
protected bool HasColumn(System.Data.IDataReader dr, string columnName)
{
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
{
return(true);
}
}
return(false);
}
internal static List <DataReaderField> GetDataReaderFields(System.Data.IDataReader reader)
{
List <DataReaderField> list = new List <DataReaderField>();
for (int i = 0; i < reader.FieldCount; i++)
{
DataReaderField column = new DataReaderField(i, reader.GetName(i), reader.GetFieldType(i));
if (list.SingleOrDefault((DataReaderField x) => x.LowerName == column.LowerName) == null)
{
list.Add(column);
}
}
return(list);
}
private object ToObject(System.Data.IDataReader dataReader, object _obj, string alias)
{
Type type = _obj.GetType();
object obj = ((ICloneable)_obj).Clone();
System.Collections.Hashtable filedValue = new System.Collections.Hashtable();
for (int index = 0; index < dataReader.FieldCount; index++)
{
filedValue.Add(dataReader.GetName(index), dataReader[index]);
}
foreach (System.Reflection.PropertyInfo property in type.GetProperties())
{
SetEntityValue(property.Name, obj, filedValue[property.Name]);
}
((IbindDb)obj).BindDb(GetDb(), GetUnityContainer(), GetCache(), GetPluginName());
return(obj);
}
/// <summary>
/// 把数据表以CSV格式导出到文本流
/// </summary>
/// <param name="writer">只写文本流</param>
/// <param name="dr">数据表</param>
/// <param name="headers">列头对应字典</param>
/// <param name="adrs">adrs</param>
public static int ExportCSV(TextWriter writer, System.Data.IDataReader dr, System.Collections.Hashtable headers, params System.Action <IDataRecord>[] adrs)
{
int cCount = dr.FieldCount;
for (int i = 0; i < cCount; i++)
{
if (i != 0)
{
writer.Write(',');
}
string field = dr.GetName(i);
object obj = GetHashtableValue(headers, field);
if (obj == null)
{
writer.Write(CSVFormat(field));
}
else
{
writer.Write(CSVFormat(obj.ToString()));
}
}
writer.WriteLine();
int count = 0;
while (dr.Read())
{
count++;
for (int i = 0; i < cCount; i++)
{
if (i != 0)
{
writer.Write(',');
}
writer.Write(CSVFormat(dr.GetValue(i)));
}
writer.WriteLine();// ('\r');
for (var i = 0; i < adrs.Length; i++)
{
adrs[i](dr);
}
}
writer.Flush();
return(count);
}
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 * from my_first_table";
try
{
System.Data.IDataReader datareader = sqlrcom.ExecuteReader();
for (UInt32 index = 0; index < sqlrcom.FieldCount; index++)
{
String name = datareader.GetName(index);
String datatype = datareader.GetDataTypename(index);
String nativedatatype = datareader.GetFieldType(index).ToString();
... do someting with the column data ...
}
private List <Dictionary <string, object> > ExecutaQuery(System.Data.Common.DbCommand cmd)
{
List <Dictionary <string, object> > Linhas = new List <Dictionary <string, object> >();
try
{
if (cmd.CommandText.Contains("CALL "))
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.CommandTimeout = 1800;
using (System.Data.IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary <string, object> linha = new Dictionary <string, object>();
// Colunas
for (int i = 0; i < reader.FieldCount; i++)
{
linha.Add(reader.GetName(i), reader.GetValue(i) == DBNull.Value ? null : reader.GetValue(i));
}
// Adiciona Dictionary da Linha
Linhas.Add(linha);
}
}
}
catch (Exception ex)
{
string txtComando = Util.DevolveStringCommand(cmd);
throw new Exception(string.Format("Erro ao executar comando no banco de dados.\r\nTexto: {0}\r\nDetalhes: {1}", txtComando, ex.Message), ex);
}
return(Linhas);
}
/// <summary>
/// 通过dbreader填充数据到列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
public static IList <T> FillList <T>(System.Data.IDataReader reader)
{
try
{
//先获取列名信息
List <string> fieldsList = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
fieldsList.Add(reader.GetName(i));
}
IList <T> lst = new List <T>();
while (reader.Read())
{
T RowInstance = Activator.CreateInstance <T>();
foreach (System.Reflection.PropertyInfo Property in typeof(T).GetProperties())
{
//try
//{
if (!fieldsList.Contains(Property.Name))
{
continue;
}
if (reader[Property.Name] != DBNull.Value)
{
Property.SetValue(RowInstance, reader[Property.Name], null);
}
//}
//catch
//{
// continue;
//}
}
lst.Add(RowInstance);
}
return(lst);
}
finally
{
reader.Close();
}
}
/// <summary>
/// 根据数据阅读器对象，查询实体对象集合(注意查询完毕将自动释放该阅读器对象)
/// </summary>
/// <param name="reader">数据阅读器对象</param>
/// <param name="factEntityType">具体实体类的类型</param>
/// <returns>实体类集合</returns>
public static IList QueryList(System.Data.IDataReader reader, Type factEntityType)
{
//下面一行代码被注释，以便于实体类多级派生，感谢网友 有事M我 发现此问题
//if (factEntityType.BaseType != typeof(EntityBase))
if (!factEntityType.IsSubclassOf(typeof(EntityBase)))
{
throw new Exception("当前类型不是 EntityBase 的派生类型：" + factEntityType.FullName);
}
IList list = (IList)Activator.CreateInstance(typeof(List <>).MakeGenericType(factEntityType));
using (reader)
{
if (reader.Read())
{
int fcount = reader.FieldCount;
string[] names = new string[fcount];
object[] values = null;
for (int i = 0; i < fcount; i++)
{
names[i] = reader.GetName(i);
}
do
{
values = new object[fcount];
reader.GetValues(values);
EntityBase t = (EntityBase)Activator.CreateInstance(factEntityType);
t.PropertyNames = names;
t.PropertyValues = values;
list.Add(t);
} while (reader.Read());
}
}
return(list);
}
internal static void setValuesFromReader <T>(T obj, d.IDataReader reader)
where T : class
{
//reader.FieldCount
for (int i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
//this might be slow this is where some mapper would be handy
//if considering larger results sets
//which are mostly not existing
if (reader.GetFieldType(i) == typeof(byte[]))
{
//reader all bytes
long size = reader.GetBytes(i, 0, null, 0, 0);
byte[] values = new byte[size];
int bufferSize = 1024;
long bytesRead = 0;
int curPos = 0;
while (bytesRead < size)
{
bytesRead += reader.GetBytes(i, curPos, values, curPos, bufferSize);
curPos += bufferSize;
}
obj.SetValue(fieldName, values);
}
else
{
if (reader.IsDBNull(i))
{
obj.SetValue(fieldName, null);
}
else
{
var value = reader.GetValue(i);
obj.SetValue(fieldName, value);
}
}
}
}
public static IEnumerable <Dictionary <string, object> > AsEnumerableDictionary(this System.Data.IDataReader source)
{
if (source == null)
{
throw new ArgumentNullException("source");
}
while (source.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < source.FieldCount; i++)
{
object value = source.GetValue(i);
// return an empty string for dbnull value of field type string
if (source.GetFieldType(i) == typeof(string) && source.IsDBNull(i))
{
value = string.Empty;
}
row.Add(source.GetName(i), value);
}
yield return(row);
}
}
internal static void setValuesFromReader <T>(T obj, d.IDataReader reader)
where T : IBattleAxe
{
//reader.FieldCount
for (int i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
if (reader.GetFieldType(i) == typeof(byte[]))
{
//reader all bytes
long size = reader.GetBytes(i, 0, null, 0, 0);
byte[] values = new byte[size];
int bufferSize = 1024;
long bytesRead = 0;
int curPos = 0;
while (bytesRead < size)
{
bytesRead += reader.GetBytes(i, curPos, values, curPos, bufferSize);
curPos += bufferSize;
}
obj[fieldName] = values;
}
else
{
var value = reader.GetValue(i);
if (reader.IsDBNull(i))
{
obj[fieldName] = null;
}
else
{
obj[fieldName] = value;
}
}
}
}
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();
}
public string GetName(int i)
{
return(_innerReader.GetName(i));
}
public virtual Durados.Web.Mvc.UI.Helpers.tblObject GetChartData(string chartId, string queryString)
{
string errorMessage;
string createMessage;
string userId;
string userRole;
SetErrorMessageValues(out errorMessage, out createMessage, out userId, out userRole);
if (string.IsNullOrEmpty(chartId))
{
throw new ChartIdIsMissingException();
}
int id = Convert.ToInt32(chartId.Replace("Chart", ""));
int?dashboardId = Database.GetDashboardPK(id);
if (!dashboardId.HasValue)
{
throw new DashboardIdIsMissingException();
}
if (!Map.Database.Dashboards[dashboardId.Value].Charts.ContainsKey(id))
{
throw new ChartIdNotFoundException(chartId);
}
Chart chart = Map.Database.Dashboards[dashboardId.Value].Charts[id];
if (string.IsNullOrEmpty(chart.SQL))
{
throw new SqlStatementIsMissingException();
}
System.Data.IDbConnection connection = null;
System.Data.IDataReader reader = null;
try
{
Durados.Web.Mvc.UI.Helpers.tblObject content = GetChartJsonObject(chart);
// Connect
System.Data.IDbCommand cmd = BuildConnectionAndCommand(userId, userRole, chart, queryString, ref connection);
// Read
try
{
reader = cmd.ExecuteReader();
}
catch (Exception exception)
{
throw new SqlExecutionFailureException(exception.Message);
}
string xFieldName = chart.XField;
Dictionary <int, string> yFieldNames = new Dictionary <int, string>();
//if(!string.IsNullOrEmpty(chart.YField) && chart.ChartType!=ChartType.Gauge)
// yFieldNames.AddRange(chart.YField.Split(','));
content.Neg = false;
List <Durados.Web.Mvc.UI.Helpers.ChartSeries> series = new List <Durados.Web.Mvc.UI.Helpers.ChartSeries>();
if (reader.FieldCount >= 2 || chart.ChartType == ChartType.Gauge && reader.FieldCount == 1)
{
if (string.IsNullOrEmpty(xFieldName) || chart.ChartType == ChartType.Gauge)
{
xFieldName = reader.GetName(0);
}
FillChartData(content, reader, xFieldName, yFieldNames, series, chart);
}
else
{
string message = chart.ChartType != ChartType.Gauge ? string.Format(errorMessage, "SQL", "The select statement must contain at least two columns.", chartId) : string.Format(errorMessage, "SQL", "The select statement must contain one column.", chartId);
throw new SqlReturnsLessThanTwoColumnsException(chartId);
}
SetChartProperties(chart, content, xFieldName, yFieldNames, series);
//System.Web.Script.Serialization.JavaScriptSerializer jss = new System.Web.Script.Serialization.JavaScriptSerializer();
//return jss.Serialize(content);
return(content);
}
catch (Exception exception)
{
throw new ChartException(exception.Message);
}
finally
{
try
{
reader.Close();
}
catch { }
try
{
connection.Close();
}
catch { }
}
}
