public async Task <bool> Exists(
string folderName,
CancellationToken cancellationToken)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Text, 50);
arParams[0].Value = folderName;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_sitefolders ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("foldername = :foldername ");
sqlCommand.Append(";");
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken);
int count = Convert.ToInt32(result);
return(count > 0);
}
public async Task <bool> Exists(
string folderName,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SiteFolders ");
sqlCommand.Append("WHERE FolderName = ?FolderName ; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?FolderName", MySqlDbType.VarChar, 255);
arParams[0].Value = folderName;
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken);
int count = Convert.ToInt32(result);
return(count > 0);
}
public async Task <int> RoleCreate(
Guid roleGuid,
Guid siteGuid,
int siteId,
string roleName,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_roles (");
sqlCommand.Append("siteid, ");
sqlCommand.Append("rolename, ");
sqlCommand.Append("displayname, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("roleguid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":siteid, ");
sqlCommand.Append(":rolename, ");
sqlCommand.Append(":displayname, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":roleguid )");
sqlCommand.Append(";");
sqlCommand.Append(" SELECT CURRVAL('mp_roles_roleid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[5];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("rolename", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Value = roleName;
arParams[2] = new NpgsqlParameter("displayname", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[2].Value = roleName;
arParams[3] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Value = siteGuid.ToString();
arParams[4] = new NpgsqlParameter("roleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[4].Value = roleGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken);
int newID = Convert.ToInt32(result);
return(newID);
}
public async Task <int> RoleCreate(
Guid roleGuid,
Guid siteGuid,
int siteId,
string roleName,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_Roles (");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("RoleName, ");
sqlCommand.Append("DisplayName, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("RoleGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteID, ");
sqlCommand.Append("?RoleName, ");
sqlCommand.Append("?DisplayName, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?RoleGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[5];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new MySqlParameter("?RoleName", MySqlDbType.VarChar, 50);
arParams[1].Value = roleName;
arParams[2] = new MySqlParameter("?DisplayName", MySqlDbType.VarChar, 50);
arParams[2].Value = roleName;
arParams[3] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[3].Value = siteGuid.ToString();
arParams[4] = new MySqlParameter("?RoleGuid", MySqlDbType.VarChar, 36);
arParams[4].Value = roleGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken);
int newID = Convert.ToInt32(result);
return(newID);
}
public async Task <bool> AddUser(
int roleId,
int userId,
Guid roleGuid,
Guid userGuid
)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
arParams[1] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Value = userId;
arParams[2] = new NpgsqlParameter("roleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Value = roleGuid.ToString();
arParams[3] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Value = userGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.StoredProcedure,
"mp_userroles_insert(:roleid,:userid,:roleguid,:userguid)",
arParams);
int rowsAffected = Convert.ToInt32(result);
return(rowsAffected > -1);
}
public async Task <int> RoleCreate(
Guid roleGuid,
Guid siteGuid,
int siteId,
string roleName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("rolename", NpgsqlTypes.NpgsqlDbType.Text, 50);
arParams[1].Value = roleName;
arParams[2] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Value = siteGuid.ToString();
arParams[3] = new NpgsqlParameter("roleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Value = roleGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.StoredProcedure,
"mp_roles_insert(:siteid,:rolename,:siteguid,:roleguid)",
arParams);
int newID = Convert.ToInt32(result);
return(newID);
}
public async Task <bool> AddUser(
int roleId,
int userId,
Guid roleGuid,
Guid userGuid
)
{
//MS SQL proc checks that no matching record exists, may need to check that
//here
FbParameter[] arParams = new FbParameter[4];
arParams[0] = new FbParameter(":UserID", FbDbType.Integer);
arParams[0].Value = userId;
arParams[1] = new FbParameter(":RoleID", FbDbType.Integer);
arParams[1].Value = roleId;
arParams[2] = new FbParameter(":UserGuid", FbDbType.Char, 36);
arParams[2].Value = roleGuid.ToString();
arParams[3] = new FbParameter(":RoleGuid", FbDbType.Char, 36);
arParams[3].Value = roleGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_USERROLES_INSERT ("
+ AdoHelper.GetParamString(arParams.Length) + ")",
arParams);
int newID = Convert.ToInt32(result);
return(newID > -1);
}
public async Task <bool> RemoveUser(int roleId, int userId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
arParams[1] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Value = userId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_userroles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("roleid = :roleid ");
sqlCommand.Append("AND ");
sqlCommand.Append("userid = :userid ");
sqlCommand.Append(";");
//object result = await AdoHelper.ExecuteScalarAsync(
// writeConnectionString,
// CommandType.StoredProcedure,
// "mp_userroles_delete(:roleid,:userid)",
// arParams);
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
int rowsAffected = Convert.ToInt32(result);
return(rowsAffected > -1);
}
public async Task <int> RoleCreate(
Guid roleGuid,
Guid siteGuid,
int siteId,
string roleName)
{
FbParameter[] arParams = new FbParameter[5];
arParams[0] = new FbParameter(":SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new FbParameter(":RoleName", FbDbType.VarChar, 50);
arParams[1].Value = roleName;
arParams[2] = new FbParameter(":DisplayName", FbDbType.VarChar, 50);
arParams[2].Value = roleName;
arParams[3] = new FbParameter(":SiteGuid", FbDbType.Char, 36);
arParams[3].Value = siteGuid.ToString();
arParams[4] = new FbParameter(":RoleGuid", FbDbType.Char, 36);
arParams[4].Value = roleGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_ROLES_INSERT ("
+ AdoHelper.GetParamString(arParams.Length) + ")",
arParams);
int newID = Convert.ToInt32(result);
return(newID);
}
public async Task <bool> Exists(int siteId, string roleName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE SiteID = ?SiteID AND RoleName = ?RoleName ; ");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new MySqlParameter("?RoleName", MySqlDbType.VarChar, 50);
arParams[1].Value = roleName;
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
sqlCommand.ToString(),
arParams);
int count = Convert.ToInt32(result);
return(count > 0);
}
public async Task <int> Create(
int siteId,
string userId,
string claimType,
string claimValue)
{
FbParameter[] arParams = new FbParameter[4];
arParams[0] = new FbParameter(":UserId", FbDbType.VarChar, 128);
arParams[0].Value = userId;
arParams[1] = new FbParameter(":ClaimType", FbDbType.VarChar, -1);
arParams[1].Value = claimType;
arParams[2] = new FbParameter(":ClaimValue", FbDbType.VarChar, -1);
arParams[2].Value = claimValue;
arParams[3] = new FbParameter(":SiteId", FbDbType.Integer);
arParams[3].Value = siteId;
string statement = "EXECUTE PROCEDURE mp_USERCLAIMS_INSERT ("
+ AdoHelper.GetParamString(arParams.Length) + ")";
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.StoredProcedure,
statement,
arParams);
int newID = Convert.ToInt32(result);
return(newID);
}
/// <summary>
/// Gets a count of rows in the mp_GeoZone table.
/// </summary>
public async Task <int> GetCount(
Guid countryGuid,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_GeoZone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("CountryGuid = ?CountryGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?CountryGuid", MySqlDbType.VarChar, 36);
arParams[0].Value = countryGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken);
return(Convert.ToInt32(result));
}
public async Task <bool> Delete(int roleId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_roles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("roleid = :roleid ");
sqlCommand.Append("AND rolename <> 'Admins' ");
sqlCommand.Append("AND rolename <> 'Content Administrators' ");
sqlCommand.Append("AND rolename <> 'Authenticated Users' ");
sqlCommand.Append("AND rolename <> 'Role Admins' ");
sqlCommand.Append(";");
//object result = await AdoHelper.ExecuteScalarAsync(
// writeConnectionString,
// CommandType.StoredProcedure,
// "mp_roles_delete(:roleid)",
// arParams);
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
int rowsAffected = Convert.ToInt32(result);
return(rowsAffected > -1);
}
public async Task <bool> Update(int roleId, string roleName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
arParams[1] = new NpgsqlParameter("rolename", NpgsqlTypes.NpgsqlDbType.Text, 50);
arParams[1].Value = roleName;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_roles ");
sqlCommand.Append("SET ");
sqlCommand.Append("displayname = :rolename ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("roleid = :roleid ");
sqlCommand.Append(";");
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
int rowsAffected = Convert.ToInt32(result);
return(rowsAffected > -1);
}
public async Task <bool> Exists(int siteId, string roleName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("rolename", NpgsqlTypes.NpgsqlDbType.Text, 50);
arParams[1].Value = roleName;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_roles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteid = :siteid ");
sqlCommand.Append("AND rolename = :rolename ");
sqlCommand.Append(";");
//object result = await AdoHelper.ExecuteScalarAsync(
// readConnectionString,
// CommandType.StoredProcedure,
// "mp_roles_roleexists(:siteid,:rolename)",
// arParams);
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
int count = Convert.ToInt32(result);
return(count > 0);
}
/// <summary>
/// Gets a count of rows in the mp_GeoCountry table.
/// </summary>
public async Task <int> GetCount()
{
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.StoredProcedure,
"mp_GeoCountry_GetCount",
null);
return(Convert.ToInt32(result));
}
//public DbDataReader GetPageListForAdmin(int siteId)
//{
// SqlParameterHelper sph = new SqlParameterHelper(
// logFactory,
// readConnectionString,
// "mp_Pages_SelectList",
// 1);
// sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
// return sph.ExecuteReader();
//}
public async Task <int> GetHostCount(CancellationToken cancellationToken)
{
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.StoredProcedure,
"mp_SiteHosts_GetCount",
null,
cancellationToken);
return(Convert.ToInt32(result));
}
public async Task <int> GetCountOfUsersInRole(int siteId, int roleId, string searchInput)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("Count(*) ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("WHERE u.SiteID = ?SiteID ");
sqlCommand.Append("AND ");
sqlCommand.Append("u.UserID IN (");
sqlCommand.Append("SELECT UserID FROM mp_UserRoles ");
sqlCommand.Append("WHERE RoleID = ?RoleID ");
sqlCommand.Append(")");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (u.Name LIKE ?SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.LoginName LIKE ?SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.Email LIKE ?SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.LastName LIKE ?SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.FirstName LIKE ?SearchInput) ");
sqlCommand.Append(")");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new MySqlParameter("?RoleID", MySqlDbType.Int32);
arParams[1].Value = roleId;
arParams[2] = new MySqlParameter("?SearchInput", MySqlDbType.VarChar, 50);
arParams[2].Value = "%" + searchInput + "%";
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
sqlCommand.ToString(),
arParams);
return(Convert.ToInt32(result));
}
public async Task <int> GetCountOfUsersInRole(int siteId, int roleId, string searchInput)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COUNT(*) ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("JOIN mp_UserRoles ur ");
sqlCommand.Append("ON u.UserID = ur.UserID ");
sqlCommand.Append("AND ur.RoleID = @RoleID ");
sqlCommand.Append("WHERE u.SiteID = @SiteID ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (UPPER(u.Name) LIKE UPPER(@SearchInput)) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (UPPER(u.LoginName) LIKE UPPER(@SearchInput)) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (UPPER(u.Email) LIKE UPPER(@SearchInput)) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (UPPER(u.LastName) LIKE UPPER(@SearchInput)) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (UPPER(u.FirstName) LIKE UPPER(@SearchInput)) ");
sqlCommand.Append(")");
}
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@RoleID", FbDbType.Integer);
arParams[1].Value = roleId;
arParams[2] = new FbParameter("@SearchInput", FbDbType.VarChar, 50);
arParams[2].Value = "%" + searchInput + "%";
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(Convert.ToInt32(result));
}
public async Task <int> GetCountOfUsersInRole(int siteId, int roleId, string searchInput)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("COUNT(*) ");
sqlCommand.Append("FROM mp_users u ");
sqlCommand.Append("JOIN mp_userroles ur ");
sqlCommand.Append("ON u.userid = ur.userid ");
sqlCommand.Append("AND ur.roleid = :roleid ");
sqlCommand.Append("WHERE u.siteid = :siteid ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (u.name LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.loginname LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.email LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.lastname LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.firstname LIKE :searchinput) ");
sqlCommand.Append(")");
}
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Value = roleId;
arParams[2] = new NpgsqlParameter("searchinput", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[2].Value = "%" + searchInput + "%";
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(Convert.ToInt32(result));
}
/// <summary>
/// Gets a count of rows in the mp_Language table.
/// </summary>
public async Task <int> GetCount()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Language ");
sqlCommand.Append(";");
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
sqlCommand.ToString(),
null);
return(Convert.ToInt32(result));
}
public async Task <int> Create(
int siteId,
string userId,
string claimType,
string claimValue,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_userclaims (");
sqlCommand.Append("siteid, ");
sqlCommand.Append("userid, ");
sqlCommand.Append("claimtype, ");
sqlCommand.Append("claimvalue )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":siteid, ");
sqlCommand.Append(":userid, ");
sqlCommand.Append(":claimtype, ");
sqlCommand.Append(":claimvalue )");
sqlCommand.Append(";");
sqlCommand.Append(" SELECT CURRVAL('mp_userclaimsid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[0].Value = userId;
arParams[1] = new NpgsqlParameter("claimtype", NpgsqlTypes.NpgsqlDbType.Text);
arParams[1].Value = claimType;
arParams[2] = new NpgsqlParameter("claimvalue", NpgsqlTypes.NpgsqlDbType.Text);
arParams[2].Value = claimValue;
arParams[3] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Value = siteId;
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken);
int newID = Convert.ToInt32(result);
return(newID);
}
public async Task <int> Create(
int siteId,
string userId,
string claimType,
string claimValue,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_UserClaims (");
sqlCommand.Append("SiteId, ");
sqlCommand.Append("UserId, ");
sqlCommand.Append("ClaimType, ");
sqlCommand.Append("ClaimValue )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteId, ");
sqlCommand.Append("?UserId, ");
sqlCommand.Append("?ClaimType, ");
sqlCommand.Append("?ClaimValue )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?UserId", MySqlDbType.VarChar, 128);
arParams[0].Value = userId;
arParams[1] = new MySqlParameter("?ClaimType", MySqlDbType.Text);
arParams[1].Value = claimType;
arParams[2] = new MySqlParameter("?ClaimValue", MySqlDbType.Text);
arParams[2].Value = claimValue;
arParams[3] = new MySqlParameter("?SiteId", MySqlDbType.Int32);
arParams[3].Value = siteId;
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken);
int newID = Convert.ToInt32(result);
return(newID);
}
public async Task <int> GetCount(CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SystemLog ");
sqlCommand.Append(";");
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
null,
cancellationToken);
return(Convert.ToInt32(result));
}
public async Task <bool> Delete(int roleId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.StoredProcedure,
"mp_roles_delete(:roleid)",
arParams);
int rowsAffected = Convert.ToInt32(result);
return(rowsAffected > -1);
}
public async Task <bool> Exists(string folderName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Text, 50);
arParams[0].Value = folderName;
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.StoredProcedure,
"mp_sitefolders_exists(:foldername)",
arParams);
int count = Convert.ToInt32(result);
return(count > 0);
}
public async Task <bool> Exists(int siteId, string roleName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("rolename", NpgsqlTypes.NpgsqlDbType.Text, 50);
arParams[1].Value = roleName;
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.StoredProcedure,
"mp_roles_roleexists(:siteid,:rolename)",
arParams);
int count = Convert.ToInt32(result);
return(count > 0);
}
public async Task <int> GetCountOfSiteRoles(int siteId, string searchInput)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("Count(*) ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE SiteID = ?SiteID ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (DisplayName LIKE ?SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (RoleName LIKE ?SearchInput) ");
sqlCommand.Append(")");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new MySqlParameter("?SearchInput", MySqlDbType.VarChar, 50);
arParams[1].Value = "%" + searchInput + "%";
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(Convert.ToInt32(result));
}
/// <summary>
/// Gets a count of rows in the mp_GeoZone table.
/// </summary>
public async Task <int> GetCount(Guid countryGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_GeoZone ");
sqlCommand.Append("WHERE CountryGuid = @CountryGuid ");
sqlCommand.Append("OR CountryGuid = UPPER(@CountryGuid) ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@CountryGuid", FbDbType.Char, 36);
arParams[0].Value = countryGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
sqlCommand.ToString(),
arParams);
return(Convert.ToInt32(result));
}
public async Task <Guid> GetSiteGuid(string folderName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[0].Value = folderName;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COALESCE( ");
sqlCommand.Append("(SELECT siteguid FROM mp_sitefolders where foldername = :foldername limit 1), ");
sqlCommand.Append("(SELECT siteguid FROM mp_sites order by siteid limit 1) ");
sqlCommand.Append(") ");
sqlCommand.Append(";");
//object result = await AdoHelper.ExecuteScalarAsync(
// readConnectionString,
// CommandType.StoredProcedure,
// "mp_sitefolders_selectsiteguid(:foldername)",
// arParams);
object result = await AdoHelper.ExecuteScalarAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
string strGuid = result.ToString();
if (strGuid.Length == 36)
{
return(new Guid(strGuid));
}
else
{
return(Guid.Empty);
}
}
