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