/// <summary>
/// Loads the results of a SQL query into a DataTable object.
/// </summary>
/// <example>
/// <code>
/// using Mezzocode.Halide3;
/// ...
/// 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);
}
/// <summary>
/// Loads the results of a SQL query into a DataTable object.
/// Uses the default connection string "Halide".
/// </summary>
/// <example>
/// <code>
/// using Mezzocode.Halide3;
/// ...
/// 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>
/// 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;
}