private Int32 ReadNullableInt32(SqliteDataReader reader, Int32 column, Int32 value)
{
if (reader.IsDBNull(column))
return value;
return reader.GetInt32(column);
}
public DBRow[] SelectRows(
DBColumn[] columnSelections, Tuple <string, string> tableSelection, DBJoinTable[] tableJoins,
DBCondition[] conditions
)
{
List <DBRow> rowsOut = new List <DBRow>();
List <string> columnSelectionStrings = new List <string>();
List <string> tableJoinStrings = new List <string>();
#if USE_SQLITE
foreach (DBColumn column in columnSelections)
{
columnSelectionStrings.Add(
column.TableChar + "." + column.Name
);
}
// Convert the join objects into DB-specific join strings.
foreach (DBJoinTable join in tableJoins)
{
tableJoinStrings.Add(String.Format(
"LEFT OUTER JOIN {0} AS {1} ON {2}.{3}={4}.{5} ",
join.TableName,
join.TableChar,
tableSelection.Item2,
join.MainKey,
join.TableChar,
join.ForeignKey
));
}
string[] conditionStrings = TransformConditionStrings(conditions);
string selectQueryString = String.Format(
"SELECT {0} FROM {1} AS {2} {3} WHERE {4} ORDER BY t.src_ip ASC",
string.Join(",", columnSelectionStrings),
tableSelection.Item1,
tableSelection.Item2,
string.Join(" ", tableJoinStrings),
string.Join(" AND ", conditionStrings)
);
using (SQLiteCommand trafficReadCommand = new SQLiteCommand(
selectQueryString, database
)) {
/*
* trafficReadCommand.Parameters.AddWithValue( "@ipLowParameter", DBConversions.IP_LOCAL_LOW );
* trafficReadCommand.Parameters.AddWithValue( "@ipHighParameter", DBConversions.IP_LOCAL_HIGH );
* trafficReadCommand.Parameters.AddWithValue( "@timeStartParameter", this.startTime );
* trafficReadCommand.Parameters.AddWithValue( "@timeEndParameter", this.endTime );
*/
// Add the proxies.
foreach (DBCondition condition in conditions)
{
#if USE_SQLITE_MANAGED
trafficReadCommand.Parameters.Add(
TransformProxyToken(condition),
condition.TestValue
);
#else
trafficReadCommand.Parameters.AddWithValue(
TransformProxyToken(condition),
condition.TestValue
);
#endif // USE_SQLITE_MANAGED
}
using (SQLiteDataReader reader = trafficReadCommand.ExecuteReader()) {
while (reader.Read())
{
int columnIndexIter = 0;
DBRow rowIter = new DBRow();
foreach (DBColumn column in columnSelections)
{
string columnKey = column.TableChar + "." + column.Name;
switch (column.Type)
{
case DBDataType.STRING:
rowIter.Set(columnKey, "");
if (!reader.IsDBNull(columnIndexIter))
{
rowIter.Set(columnKey, reader.GetString(columnIndexIter));
}
break;
case DBDataType.INT:
rowIter.Set(columnKey, reader.GetInt32(columnIndexIter));
break;
case DBDataType.DOUBLE:
rowIter.Set(columnKey, reader.GetDouble(columnIndexIter));
break;
}
columnIndexIter++;
}
rowsOut.Add(rowIter);
}
}
}
#endif
return(rowsOut.ToArray());
}