public bool TableExists(string tableName)
{
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);
}
// need to return true here if no error
// the table may be empty but it does exist if no error
return(true);
}
}
catch { }
return(false);
}
public DbDataReader Find(
int siteId,
string loginProvider,
string providerKey)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserLogins ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteId = :SiteId ");
sqlCommand.Append(" AND ");
sqlCommand.Append("LoginProvider = :LoginProvider ");
sqlCommand.Append(" AND ");
sqlCommand.Append("ProviderKey = :ProviderKey ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[3];
arParams[0] = new SqliteParameter(":LoginProvider", DbType.String);
arParams[0].Value = loginProvider;
arParams[1] = new SqliteParameter(":ProviderKey", DbType.String);
arParams[1].Value = providerKey;
arParams[2] = new SqliteParameter(":SiteId", DbType.Int32);
arParams[2].Value = siteId;
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
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(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
return(AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
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(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Value = siteId;
return(AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetByUser(
int siteId,
string userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserClaims ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteId = :SiteId ");
sqlCommand.Append(" AND ");
sqlCommand.Append("UserId = :UserId ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":UserId", DbType.String);
arParams[0].Value = userId;
arParams[1] = new SqliteParameter(":SiteId", DbType.Int32);
arParams[1].Value = siteId;
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_UserLocation table.
/// </summary>
/// <param name="rowID"> rowID </param>
public DbDataReader GetOne(Guid rowID)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("rowid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = rowID.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_userlocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("rowid = :rowid ");
sqlCommand.Append(";");
//return AdoHelper.ExecuteReader(
// readConnectionString,
// CommandType.StoredProcedure,
// "mp_userlocation_select_one(:rowid)",
// arParams);
return(AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_SystemLog table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public DbDataReader GetPageAscending(
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
//totalPages = 1;
//int totalRows = GetCount();
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SystemLog ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY ID ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT :PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET :OffsetRows ");
}
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":PageSize", DbType.Int32);
arParams[0].Value = pageSize;
arParams[1] = new SqliteParameter(":OffsetRows", DbType.Int32);
arParams[1].Value = pageLowerBound;
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
//disable warning about not really being async
// we know it is not, and for Sqlite there is probably no benefit to making it really async
#pragma warning disable 1998
public async Task <DbDataReader> SchemaVersionGetAll(CancellationToken cancellationToken = default(CancellationToken))
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SchemaVersion ");
sqlCommand.Append("ORDER BY ApplicationName ");
sqlCommand.Append(";");
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
null));
}
public DbDataReader GetRolesUserIsNotIn(
int siteId,
int userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT r.* ");
sqlCommand.Append("FROM mp_Roles r ");
sqlCommand.Append("LEFT OUTER JOIN mp_UserRoles ur ");
sqlCommand.Append("ON r.RoleID = ur.RoleID ");
sqlCommand.Append("AND ur.UserID = ?UserID ");
sqlCommand.Append("WHERE r.SiteID = ?SiteID ");
sqlCommand.Append("AND ur.UserID IS NULL ");
sqlCommand.Append("ORDER BY r.DisplayName ;");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new MySqlParameter("?UserID", MySqlDbType.Int32);
arParams[1].Value = userId;
return(AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetRoleMembers(int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("mp_UserRoles.UserID, ");
sqlCommand.Append("mp_Users.Name, ");
sqlCommand.Append("mp_Users.LoginName, ");
sqlCommand.Append("mp_Users.Email ");
sqlCommand.Append("FROM mp_UserRoles ");
sqlCommand.Append("INNER JOIN mp_Users ");
sqlCommand.Append("ON mp_Users.UserID = mp_UserRoles.UserID ");
sqlCommand.Append("WHERE mp_UserRoles.RoleID = ?RoleID ; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?RoleID", MySqlDbType.Int32);
arParams[0].Value = roleId;
return(AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
public DbDataReader AutoComplete(string query, int maxRows)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT TOP(" + maxRows.ToString(CultureInfo.InvariantCulture) + ") ");
sqlCommand.Append(" * ");
sqlCommand.Append("FROM mp_GeoCountry ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("([Name] LIKE @Query + '%') ");
sqlCommand.Append("OR ([ISOCode2] LIKE @Query + '%') ");
sqlCommand.Append(") ");
sqlCommand.Append("ORDER BY [ISOCode2] ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@Query", SqlDbType.NVarChar, 255);
arParams[0].Value = query;
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetRoleMembers(int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("mp_userroles.userid, ");
sqlCommand.Append("mp_users.name, ");
sqlCommand.Append("mp_users.loginname, ");
sqlCommand.Append("mp_users.email ");
sqlCommand.Append("FROM mp_userroles ");
sqlCommand.Append("INNER JOIN mp_users ");
sqlCommand.Append("ON mp_users.userid = mp_userroles.userid ");
sqlCommand.Append("WHERE mp_userroles.roleid = :roleid ; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
return(AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
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 TOP(1) * ");
sqlCommand.Append("FROM mp_GeoZone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("CountryGuid = @CountryGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("[Code] = @Code ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@CountryGuid", SqlDbType.UniqueIdentifier);
arParams[0].Value = countryGuid;
arParams[1] = new SqlCeParameter("@Code", SqlDbType.NVarChar, 255);
arParams[1].Value = code;
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
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,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetRoleMembers(int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("ur.UserID, ");
sqlCommand.Append("u.[Name], ");
sqlCommand.Append("u.Email, ");
sqlCommand.Append("u.LoginName ");
sqlCommand.Append("FROM mp_UserRoles ur ");
sqlCommand.Append("JOIN mp_Users u ");
sqlCommand.Append("ON u.UserID = ur.UserID ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ur.RoleID = @RoleID ");
sqlCommand.Append("ORDER BY u.[Name] ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@RoleID", SqlDbType.Int);
arParams[0].Value = roleId;
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public List <T> Find <T>(string strSql) where T : new()
{
List <T> list = new List <T>();
IDataReader sdr = null;
IDbConnection connection = null;
try
{
connection = GetConnection();
bool closeConnection = GetWillConnectionState();
strSql = strSql.ToUpper();
String columns = SQLBuilderHelper.fetchColumns(strSql);
T entity = new T();
PropertyInfo[] properties = ReflectionHelper.GetProperties(entity.GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.SELECT, properties);
sdr = AdoHelper.ExecuteReader(closeConnection, connection, CommandType.Text, strSql, null);
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sdr != null)
{
sdr.Close();
}
}
return(list);
}
/// <summary>
/// Gets a page of data from the mp_UserLocation table.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public IDataReader GetPageBySite(
Guid siteGuid,
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
//totalPages = 1;
//int totalRows = GetCountBySite(siteGuid);
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") * ");
sqlCommand.Append("FROM mp_UserLocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("[IPAddressLong] ");
sqlCommand.Append(") AS t1 ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
arParams[0].Value = siteGuid;
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with rows from the mp_Users table which have the passed in IP Address
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public DbDataReader GetUsersByIPAddress(Guid siteGuid, string ipv4Address)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT u.* ");
sqlCommand.Append("FROM mp_UserLocation ul ");
sqlCommand.Append("JOIN [mp_Users] u ");
sqlCommand.Append("ON ul.UserGuid = u.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("(u.SiteGuid = @SiteGuid OR @SiteGuid = '00000000-0000-0000-0000-000000000000') ");
sqlCommand.Append("AND ");
sqlCommand.Append("ul.IPAddress = @IPAddress ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ul.[LastCaptureUTC] DESC ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
arParams[0].Value = siteGuid;
arParams[1] = new SqlCeParameter("@IPAddress", SqlDbType.NVarChar, 50);
arParams[1].Value = ipv4Address;
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetByName(int siteId, string roleName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("AND RoleName = @RoleName ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
arParams[0].Value = siteId;
arParams[1] = new SqlCeParameter("@RoleName", SqlDbType.NVarChar, 50);
arParams[1].Value = roleName;
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_RedirectList table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public DbDataReader GetBySiteAndUrl(int siteId, string oldUrl)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_redirectlist ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteid = :siteid ");
sqlCommand.Append("AND oldUrl = :oldurl ");
sqlCommand.Append("AND expireutc < :currenttime ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("oldurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Value = oldUrl;
arParams[2] = new NpgsqlParameter("currenttime", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[2].Value = DateTime.UtcNow;
return(AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetRolesUserIsNotIn(
int siteId,
int userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT r.* ");
sqlCommand.Append("FROM mp_Roles r ");
sqlCommand.Append("LEFT OUTER JOIN mp_UserRoles ur ");
sqlCommand.Append("ON r.RoleID = ur.RoleID ");
sqlCommand.Append("AND ur.UserID = @UserID ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("r.SiteID = @SiteID ");
sqlCommand.Append("AND ur.UserID IS NULL ");
sqlCommand.Append("ORDER BY r.[DisplayName] ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
arParams[0].Value = siteId;
arParams[1] = new SqlCeParameter("@UserID", SqlDbType.Int);
arParams[1].Value = userId;
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with rows from the mp_Users table which have the passed in IP Address
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public DbDataReader GetUsersByIPAddress(Guid siteGuid, string ipv4Address)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT u.* ");
sqlCommand.Append("FROM mp_UserLocation ul ");
sqlCommand.Append("JOIN mp_Users u ");
sqlCommand.Append("ON ul.UserGuid = u.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("(u.SiteGuid = :SiteGuid OR :SiteGuid = '00000000-0000-0000-0000-000000000000') ");
sqlCommand.Append("AND ul.IPAddress = :IPAddress ");
sqlCommand.Append("ORDER BY ul.LastCaptureUTC DESC ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":SiteGuid", DbType.String);
arParams[0].Value = siteGuid.ToString();
arParams[1] = new SqliteParameter(":IPAddress", DbType.String);
arParams[1].Value = ipv4Address;
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_RedirectList table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public DbDataReader GetBySiteAndUrl(int siteId, string oldUrl)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_RedirectList ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = :SiteID ");
sqlCommand.Append("AND OldUrl = :OldUrl ");
sqlCommand.Append("AND ExpireUtc < :CurrentTime ");
sqlCommand.Append(";");
SQLiteParameter[] arParams = new SQLiteParameter[3];
arParams[0] = new SQLiteParameter(":SiteID", DbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new SQLiteParameter(":OldUrl", DbType.String);
arParams[1].Value = oldUrl;
arParams[2] = new SQLiteParameter(":CurrentTime", DbType.DateTime);
arParams[2].Value = DateTime.UtcNow;
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_BannedIPAddresses table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public DbDataReader GetPage(
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
//totalPages = 1;
//int totalRows = GetCount();
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") * ");
sqlCommand.Append("FROM mp_BannedIPAddresses ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("BannedIP ");
//sqlCommand.Append("DESC ");
sqlCommand.Append(") AS t1 ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("t1.BannedIP ");
sqlCommand.Append("DESC ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("t2.BannedIP ");
sqlCommand.Append(";");
//SqlCeParameter[] arParams = new SqlCeParameter[1];
//arParams[0] = new SqlCeParameter("@ApplicationID", SqlDbType.UniqueIdentifier);
//arParams[0].Value = applicationId;
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
null));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_RedirectList table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public DbDataReader GetBySiteAndUrl(int siteId, string oldUrl)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_RedirectList ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("AND OldUrl = @OldUrl ");
sqlCommand.Append("AND ExpireUtc < @CurrentTime ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@OldUrl", FbDbType.VarChar, 255);
arParams[1].Value = oldUrl;
arParams[2] = new FbParameter("@CurrentTime", FbDbType.TimeStamp);
arParams[2].Value = DateTime.UtcNow;
return(AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
private DbDataReader GetSchemaId(string applicationName)
{
//return GetReader(
// connectionString,
// "mp_SchemaVersion",
// " WHERE LOWER(ApplicationName) = '" + applicationName.ToLower() + "'");
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SchemaVersion ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LOWER(ApplicationName) = @ApplicationName ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@ApplicationName", SqlDbType.NVarChar, 255);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = applicationName.ToLowerInvariant();
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public DbDataReader AutoComplete(Guid countryGuid, string query, int maxRows)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GeoZone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("CountryGuid = :CountryGuid ");
sqlCommand.Append("AND ( ");
sqlCommand.Append("(Name LIKE :Query) ");
sqlCommand.Append("OR (Code LIKE :Query) ");
sqlCommand.Append(") ");
sqlCommand.Append("ORDER BY Code ");
sqlCommand.Append("LIMIT " + maxRows.ToString());
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":CountryGuid", DbType.String);
arParams[0].Value = countryGuid.ToString();
arParams[1] = new SqliteParameter(":Query", DbType.String);
arParams[1].Value = query + "%";
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetAll()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.SiteID, ");
sqlCommand.Append("s.SiteGuid, ");
sqlCommand.Append("sf.Guid, ");
sqlCommand.Append("sf.FolderName ");
sqlCommand.Append("FROM mp_SiteFolders sf ");
sqlCommand.Append("JOIN mp_Sites s ");
sqlCommand.Append("ON sf.SiteGuid = s.SiteGuid ");
sqlCommand.Append("ORDER BY sf.FolderName ");
sqlCommand.Append(";");
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
null));
}
/// <summary>
/// Gets a page of data from the mp_Language table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
public DbDataReader GetPage(
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Language ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY [Sort] ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT :PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET :OffsetRows ");
}
sqlCommand.Append(";");
SQLiteParameter[] arParams = new SQLiteParameter[2];
arParams[0] = new SQLiteParameter(":PageSize", DbType.Int32);
arParams[0].Value = pageSize;
arParams[1] = new SQLiteParameter(":OffsetRows", DbType.Int32);
arParams[1].Value = pageLowerBound;
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
public DbDataReader GetPage(
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
//totalPages = 1;
//int totalRows = GetCount();
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
int offset = 0;
if (pageNumber > 1)
{
offset = (pageSize * pageNumber) - pageSize;
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.SiteID, ");
sqlCommand.Append("s.SiteGuid, ");
sqlCommand.Append("sf.Guid, ");
sqlCommand.Append("sf.FolderName ");
sqlCommand.Append("FROM mp_SiteFolders sf ");
sqlCommand.Append("JOIN mp_Sites s ");
sqlCommand.Append("ON sf.SiteGuid = s.SiteGuid ");
sqlCommand.Append("ORDER BY sf.FolderName ");
sqlCommand.Append("OFFSET " + offset.ToString(CultureInfo.InvariantCulture) + " ROWS ");
sqlCommand.Append("FETCH NEXT " + pageSize.ToString(CultureInfo.InvariantCulture) + "ROWS ONLY ");
sqlCommand.Append(";");
return(AdoHelper.ExecuteReader(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
null));
}