Skip to content

Instantly share code, notes, and snippets.

@jpann
Last active December 26, 2015 00:19
Show Gist options
  • Save jpann/7063765 to your computer and use it in GitHub Desktop.
Save jpann/7063765 to your computer and use it in GitHub Desktop.
Playing around with a new SqlUtil class.
void Main()
{
var count = SqlUtil.ExecuteScalar<int>("SELECT COUNT(SSRSReports) FROM SSRSReports");
Console.WriteLine("count = " + count);
var reports = SqlUtil.ExecuteQueryReader<SSRSItem>("SELECT * FROM SSRSReports WHERE IsSubReport <> 'N'", c => SSRSItem.LoadFromRecord(c));
foreach (var report in reports)
{
Console.WriteLine(report);
}
}
public static class SqlUtil
{
private static SqlConnection GetConnection()
{
string connectionString = "Data Source=(local);Initial Catalog=Database;Trusted_Connection=yes;Connect Timeout=0;";
SqlConnection conn = new SqlConnection(connectionString);
return conn;
}
public static T ExecuteScalar<T>(string query)
{
return Execute<T>(c => c.ExecuteScalar<T>(query));
}
public static T Execute<T>(Func<SqlConnection, T> func)
{
using (SqlConnection conn = SqlUtil.GetConnection())
{
conn.Open();
return func(conn);
}
}
// Extension
public static T ExecuteScalar<T>(this SqlConnection conn, string query)
{
using (var command = new SqlCommand(query, conn))
{
return (T)command.ExecuteScalar();
}
}
public static IEnumerable<T> ExecuteQueryReader<T>(string query, Func<SqlDataReader, T> recordReader)
{
using (SqlConnection conn = SqlUtil.GetConnection())
{
conn.Open();
using (var command = new SqlCommand(query, conn))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return recordReader(reader);
}
}
}
}
}
}
public enum ItemType
{
Unknown,
Folder,
Report,
Resource,
LinkedReport,
DataSource,
Model,
ReportInEdition
}
public class SSRSItem
{
public string PK { get; set; }
public string Name { get; set; }
public string Path { get; set; }
public string IsGlobal { get; set; }
public bool IsUserDefined { get; set; }
public string IsAssignedProfHistory { get; set; }
public string SecurityGroup { get; set; }
public string CheckOutProfessionals { get; set; }
public string Professionals { get; set; }
public string ReportState { get; set; }
public string TemporaryPath { get; set; }
public string ReportType { get; set; }
public ItemType ItemType { get; set; }
public string FinancialType { get; set; }
public string SCFType { get; set; }
public bool IsAddOn { get; set; }
public string AddonType { get; set; }
public string Hidden { get; set; }
public string CreatedBy { get; set; }
public string ModifiedBy { get; set; }
public string Description { get; set; }
public string CurrentReportId { get; set; }
public bool IsSubReport { get; set; }
public static SSRSItem LoadFromRecord(IDataRecord record)
{
SSRSItem item = new SSRSItem();
item.PK = record.SafeGetString("SSRSReports");
item.Name = record.SafeGetString("ReportName");
item.Path = record.SafeGetString("ReportPath");
item.IsGlobal = record.SafeGetString("IsGlobal");
item.IsUserDefined = record.SafeGetString("IsUserDefined") == "Y" ? true : false;
item.IsAssignedProfHistory = record.SafeGetString("IsAssignedProfHistory");
item.SecurityGroup = record.SafeGetString("SecurityGroup");
item.CheckOutProfessionals = record.SafeGetString("CheckOutProfessionals");
item.Professionals = record.SafeGetString("Professionals");
item.ReportState = record.SafeGetString("ReportState");
item.TemporaryPath = record.SafeGetString("TemporaryReportPath");
item.ReportType = record.SafeGetString("ReportType");
item.ItemType = ItemType.Report;
item.FinancialType = record.SafeGetString("FinancialsType");
item.SCFType = record.SafeGetString("SCFType");
return item;
}
public static SSRSItem LoadFromReader(SqlDataReader reader)
{
SSRSItem item = new SSRSItem();
item.PK = reader.SafeGetString("SSRSReports");
item.Name = reader.SafeGetString("ReportName");
item.Path = reader.SafeGetString("ReportPath");
item.IsGlobal = reader.SafeGetString("IsGlobal");
item.IsUserDefined = reader.SafeGetString("IsUserDefined") == "Y" ? true : false;
item.IsAssignedProfHistory = reader.SafeGetString("IsAssignedProfHistory");
item.SecurityGroup = reader.SafeGetString("SecurityGroup");
item.CheckOutProfessionals = reader.SafeGetString("CheckOutProfessionals");
item.Professionals = reader.SafeGetString("Professionals");
item.ReportState = reader.SafeGetString("ReportState");
item.TemporaryPath = reader.SafeGetString("TemporaryReportPath");
item.ReportType = reader.SafeGetString("ReportType");
item.ItemType = ItemType.Report;
item.FinancialType = reader.SafeGetString("FinancialsType");
item.SCFType = reader.SafeGetString("SCFType");
return item;
}
}
public static class IDataRecordExtension
{
public static string SafeGetString(this IDataRecord record, int colIndex)
{
if (!record.IsDBNull(colIndex))
return record.GetString(colIndex);
else
return string.Empty;
}
public static string SafeGetString(this IDataRecord record, string colName)
{
if (!(Convert.IsDBNull(record[colName])))
{
return record[colName].ToString();
}
else
{
return string.Empty;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment