//Example Usage of SQLDataAdapter to run Queries
public static DataTable ExampleQuery3()
{
DataTable dtAllCategories;
dtAllCategories = SQLDataAdapter.Query4DataTable("SELECT * FROM categories");
return(dtAllCategories);
}
public static List <Event.Event> getAllEventsList(int iPublishStatus = 0, bool bActiveOnly = false)
{
DataTable dtEvents = new DataTable();
string sQuery = string.Empty;
switch (iPublishStatus)
{
case (0): //All Events
sQuery = "SELECT iEventId FROM calendarevent WHERE (bActive = 1 OR bActive = " + Convert.ToInt32(bActiveOnly) + ")";
break;
case (1): //Submitted Events Only
sQuery = "SELECT iEventId FROM calendarevent WHERE (bActive = 1 OR bActive = " + Convert.ToInt32(bActiveOnly) + ") AND bPublished = 0";
break;
case (2): //Published Events Only
sQuery = "SELECT iEventId FROM calendarevent WHERE (bActive = 1 OR bActive = " + Convert.ToInt32(bActiveOnly) + ") AND bPublished = 1";
break;
}
dtEvents = SQLDataAdapter.Query4DataTable(sQuery);
List <Event.Event> list = new List <Event.Event>();
foreach (DataRow row in dtEvents.Rows)
{
int iEventId = (int)row[0];
Event.Event e = new Event.Event(iEventId);
list.Add(e);
}
return(list);
}
public static DataTable GetPreselectedFilters()
{
DataTable dtSelected = new DataTable();
string sQuery = "Select * from preselectedcalendarfilters";
dtSelected = SQLDataAdapter.Query4DataTable(sQuery);
return(dtSelected);
}
public static DataTable GetCalendarSettings()
{
DataTable dtSettings = new DataTable();
string sQuery = "SELECT * FROM calendarsettings";
dtSettings = SQLDataAdapter.Query4DataTable(sQuery);
return(dtSettings);
}
/// <summary>
/// Returns a single rowed table which has all of that event's data.
/// </summary>
/// <param name="iEventId">Event to Query for</param>
/// <returns>Single rowed Datatable with all event attributes.</returns>
public static DataTable GetEvent(int?iEventId)
{
DataTable dtEvents = new DataTable();
string sQuery = "SELECT * FROM calendarevent WHERE iEventId = " + iEventId;
dtEvents = SQLDataAdapter.Query4DataTable(sQuery);
return(dtEvents);
}
/// <summary>
/// Returns a table of events. returns all events by default but parameters can be used to get only active/published events
/// </summary>
/// <param name="bPublishedOnly">Determines whether only published events are returned. Default: false</param>
/// <param name="bActiveOnly">Determines whether only active events are returned. Default: false</param>
/// <returns>Multi rowed table with each row holding an event's attributes.</returns>
public static DataTable GetSubmittedEvents()
{
DataTable dtEvents = new DataTable();
string sQuery = "SELECT * FROM calendarevent WHERE bPublished = 0 AND bActive = 1";
dtEvents = SQLDataAdapter.Query4DataTable(sQuery);
return(dtEvents);
}
public static List <int> getEventsForProperty(int iPropertyId)
{
List <int> liEvents = new List <int>();
string sQuery = "SELECT iEventId FROM eventproperties WHERE iPropertyId = " + iPropertyId;
DataTable dtEvents = SQLDataAdapter.Query4DataTable(sQuery);
foreach (DataRow row in dtEvents.Rows)
{
liEvents.Add((int)row[0]);
}
return(liEvents);
}
public static List <Property.Property> getPropertyList(PropertyType propertyType, bool bActiveOnly = false)
{
List <Property.Property> li = new List <Property.Property>();
string sQuery = "SELECT * FROM property WHERE iPropertyTypeId = " + propertyType.PropertyTypeId + " AND (bActive = 1 or bActive = " + Convert.ToInt32(bActiveOnly) + ")";
DataTable dtPropertys = SQLDataAdapter.Query4DataTable(sQuery);
foreach (DataRow row in dtPropertys.Rows)
{
Property.Property p = new Property.Property();
p.PropertyId = int.Parse(row["iPropertyId"].ToString());
p.Name = row["vProperty"].ToString();
p.PropertyType = propertyType;
li.Add(p);
}
return(li);
}
public static List <PropertyType> getAllPropertyTypes(bool bActiveOnly = false)
{
List <PropertyType> li = new List <PropertyType>();
string sQuery = "SELECT * FROM propertytype WHERE (bActive = 1 or bActive = " + Convert.ToInt32(bActiveOnly) + ")";
DataTable dtPropertyTypes = SQLDataAdapter.Query4DataTable(sQuery);
foreach (DataRow row in dtPropertyTypes.Rows)
{
PropertyType pt = new PropertyType();
pt.PropertyTypeId = int.Parse(row["iPropertyTypeId"].ToString());
pt.Name = row["vPropertyType"].ToString();
pt.PropertyList = getPropertyList(pt, true);
li.Add(pt);
}
return(li);
}
/// <summary>
/// Returns a table of events. returns all events by default but parameters can be used to get only active/published events
/// </summary>
/// <param name="iPublishStatus">Determines the published status of the list to be returned 0: all events, 1: Submitted only, 2: Published only</param>
/// <param name="bActiveOnly">Determines whether only active events are returned. Default: false</param>
/// <returns>Multi rowed table with each row holding an event's attributes.</returns>
public static DataTable GetAllEvents(int iPublishStatus = 0, bool bActiveOnly = false)
{
DataTable dtEvents = new DataTable();
string sQuery = string.Empty;
switch (iPublishStatus)
{
case (0): //All Events
sQuery = "SELECT * FROM calendarevent WHERE (bActive = 1 OR bActive = " + Convert.ToInt32(bActiveOnly) + ")";
break;
case (1): //Submitted Events Only
sQuery = "SELECT * FROM calendarevent WHERE (bActive = 1 OR bActive = " + Convert.ToInt32(bActiveOnly) + ") AND bPublished = 0";
break;
case (2): //Published Events Only
sQuery = "SELECT * FROM calendarevent WHERE (bActive = 1 OR bActive = " + Convert.ToInt32(bActiveOnly) + ") AND bPublished = 1";
break;
}
dtEvents = SQLDataAdapter.Query4DataTable(sQuery);
return(dtEvents);
}