public void TestSQL()
{
using (H3Reader reader = new H3Reader("SELECT TOP 1 * FROM GA_Activity ORDER BY [Date] DESC", "ConnectionsDBContext"))
{
if (reader.HasRows)
{
reader.Read();
Console.WriteLine("SQL Lookup [ReportName] = " + reader["ReportName"]);
}
}
}
/// <summary>
/// Initialize the DatabaseRow with a single row form a Halide.H3Reader object.
/// </summary>
/// <param name="reader">An open Halide.H3Reader object that can be used to read a row from a database.</param>
public DatabaseRow(H3Reader reader)
{
_fieldCount = reader.FieldCount;
try
{
_dataItems = new DatabaseItem[_fieldCount];
if (_dataIndex != null) _dataIndex.Clear();
for (int x = 0; x < _fieldCount; x++)
{
_dataItems[x] = new DatabaseItem();
String columnName = reader.ColumnName(x).ToLower();
_dataIndex.Add(columnName, x);
_dataItems[x].IsNullable = reader.GetColumnAllowNulls(columnName);
_dataItems[x].ColumnName = columnName;
_dataItems[x].ColumnSize = reader.GetColumnSize(columnName);
_dataItems[x].ColumnSQLType = reader.GetDataTypeName(columnName).ToLower();
_dataItems[x].IsAutoIncrementing = reader.GetColumnIsAutoIncrement(columnName);
_dataItems[x].IsIdentity = reader.GetColumnIsIdentity(columnName);
_dataItems[x].IsPrimaryKey = reader.GetColumnIsKey(columnName);
_dataItems[x].SystemDataType = reader.GetColumnSystemDataType(columnName).ToLower();
_dataItems[x].Value = reader.GetString(columnName);
}
}
catch
{
}
}
/// <summary>
/// Instantiate the class with a complete SQL statement.
/// </summary>
/// <example>
/// <code>
/// using Argentini.Halide;
/// ...
/// H3DataRow dataRow = new H3DataRow("SELECT TOP 1 * FROM Data WHERE ID=5;", "SqlServer01");
/// </code>
/// </example>
/// <param name="sqlCommand">Complete SQL command string to use for reading the desired row.</param>
/// <param name="UseconnectionStringName">Name of a connection string within the Web.config file.</param>
public H3DataRow(String sqlCommand, String UseconnectionStringName)
{
ConnectionStringName = UseconnectionStringName;
DataPresent = false;
using (H3Reader reader = new H3Reader(sqlCommand, true, UseconnectionStringName))
{
if (reader.HasRows)
{
DataPresent = true;
reader.Read();
Column = new DatabaseRow(reader);
}
else
{
Column = new DatabaseRow();
}
}
}
/// <summary>
/// Generate CSV data from a SQL Server request. Converts quotation marks to """.
/// </summary>
/// <param name="select">SQL Server command to execute, which retrieves a dataset.</param>
/// <param name="connectionStringName">Connection String name in the Web.config file.</param>
/// <param name="includeHeaders">Include column names as a header row in the CSV file.</param>
/// <param name="alwaysUseQuotes">Wrap all values in quotation marks. False will omit quotation marks around numeric values.</param>
/// <returns>StringBuilder result with the new CSV data.</returns>
public static StringBuilder GenerateCSV(String select, String connectionStringName, Boolean includeHeaders, Boolean alwaysUseQuotes)
{
StringBuilder csv = new StringBuilder();
using (H3Reader reader = new H3Reader(select, true, connectionStringName))
{
#region Build header row
if (includeHeaders)
{
for (Int32 x = 0; x < reader.FieldCount; x++)
{
if (x > 0)
{
csv.Append(",");
}
csv.Append("\"" + reader.ColumnName(x) + "\"");
}
csv.Append("\r\n");
}
#endregion
#region Build data rows
ArrayList types = new ArrayList();
types.Add("int");
types.Add("bigint");
types.Add("float");
types.Add("decimal");
types.Add("numeric");
types.Add("money");
types.Add("tinyint");
types.Add("int");
types.Add("smallint");
types.Add("smallmoney");
types.Add("real");
while (reader.Read())
{
for (Int32 x = 0; x < reader.FieldCount; x++)
{
if (x > 0)
{
csv.Append(",");
}
if (!alwaysUseQuotes && types.Contains(reader.GetDataTypeName(x).ToLower()))
{
csv.Append(reader[x]);
}
else
{
csv.Append("\"" + reader[x].Replace("\"", """) + "\"");
}
}
csv.Append("\r\n");
}
#endregion
}
return csv;
}
/// <summary>
/// Loads the results of a SQL query into a DataTable object.
/// Uses the default connection string "Halide".
/// </summary>
/// <example>
/// <code>
/// using Argentini.Halide;
/// ...
/// DataTable dt = H3Sql.ReadTable("SELECT TOP 5 FROM tablename;");
/// </code>
/// </example>
/// <param name="statement">SQL statement to execute.</param>
/// <returns>DataTable object.</returns>
public static DataTable ReadTable(string statement)
{
DataTable dt = new DataTable();
using (H3Reader reader = new H3Reader(statement))
{
dt = reader.ReadTable();
}
return (dt);
}
/// <summary>
/// Loads the results of a SQL query into a DataTable object.
/// </summary>
/// <example>
/// <code>
/// using Argentini.Halide;
/// ...
/// DataTable dt = H3Sql.ReadTable("SELECT TOP 5 FROM tablename;", "SqlServer01");
/// </code>
/// </example>
/// <param name="statement">SQL statement to execute.</param>
/// <param name="connectionStringName">Name of a connection string in the Web.config file.</param>
/// <returns>DataTable object.</returns>
public static DataTable ReadTable(string statement, string connectionStringName)
{
DataTable dt = new DataTable();
using (H3Reader reader = new H3Reader(statement, connectionStringName))
{
dt = reader.ReadTable();
}
return (dt);
}
