public DbDataReader GetSiteSettingsExList(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT e.* ");
sqlCommand.Append("FROM mp_SiteSettingsEx e ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_SiteSettingsExDef d ");
sqlCommand.Append("ON ");
sqlCommand.Append("e.KeyName = d.KeyName ");
sqlCommand.Append("AND e.GroupName = d.GroupName ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("e.SiteID = @SiteID ");
sqlCommand.Append("ORDER BY d.GroupName, d.SortOrder ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
return(AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GeoZone table.
/// </summary>
/// <param name="guid"> guid </param>
public DbDataReader GetByCode(Guid countryGuid, string code)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GeoZone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("(CountryGuid = @CountryGuid ");
sqlCommand.Append("OR CountryGuid = UPPER(@CountryGuid)) ");
sqlCommand.Append("AND Code = @Code ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@CountryGuid", FbDbType.Char, 36);
arParams[0].Value = countryGuid.ToString();
arParams[1] = new FbParameter("@Code", FbDbType.VarChar, 255);
arParams[1].Value = code;
return(AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetSiteRoles(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("r.RoleID, ");
sqlCommand.Append("r.SiteID, ");
sqlCommand.Append("r.RoleName, ");
sqlCommand.Append("r.DisplayName, ");
sqlCommand.Append("r.SiteGuid, ");
sqlCommand.Append("r.RoleGuid, ");
sqlCommand.Append("COUNT(ur.UserID) As MemberCount ");
sqlCommand.Append("FROM mp_Roles r ");
sqlCommand.Append("LEFT OUTER JOIN mp_UserRoles ur ");
sqlCommand.Append("ON ur.RoleID = r.RoleID ");
sqlCommand.Append("WHERE r.SiteID = @SiteID ");
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("r.RoleID, ");
sqlCommand.Append("r.SiteID, ");
sqlCommand.Append("r.RoleName, ");
sqlCommand.Append("r.DisplayName, ");
sqlCommand.Append("r.SiteGuid, ");
sqlCommand.Append("r.RoleGuid ");
sqlCommand.Append("ORDER BY r.DisplayName ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
return(AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetOne(Guid guid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SiteFolders ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = @Guid ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@Guid", FbDbType.VarChar, 36);
arParams[0].Value = guid.ToString();
return(AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public bool TableExists(string tableName)
{
//FbConnection connection = new FbConnection(GetConnectionString());
//string[] restrictions = new string[4];
//restrictions[2] = tableName;
//connection.Open();
//DataTable table = connection.GetSchema("Tables", restrictions);
//connection.Close();
//if (table != null)
//{
// return (table.Rows.Count > 0);
//}
//return false;
try
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM " + tableName + "; ");
using (DbDataReader reader = AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
null))
{
if (reader.Read())
{
return(true);
}
// if we didn'tget anerror it exists
return(true);
}
}
catch { }
return(false);
}
/// <summary>
/// Gets an IDataReader with one row from the mp_UserLocation table.
/// </summary>
/// <param name="rowID"> rowID </param>
public DbDataReader GetOne(Guid rowID)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserLocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowID = @RowID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@RowID", FbDbType.Char, 36);
arParams[0].Value = rowID.ToString();
return(AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams));
}