/// <summary>
/// Inserts a row in the mp_GeoZone table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="countryGuid"> countryGuid </param>
/// <param name="name"> name </param>
/// <param name="code"> code </param>
/// <returns>bool</returns>
public async Task <bool> Create(
Guid guid,
Guid countryGuid,
string name,
string code,
CancellationToken cancellationToken)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("countryguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Value = countryGuid.ToString();
arParams[2] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Value = name;
arParams[3] = new NpgsqlParameter("code", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[3].Value = code;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_geozone (");
sqlCommand.Append("guid, ");
sqlCommand.Append("countryguid, ");
sqlCommand.Append("name, ");
sqlCommand.Append("code )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":countryguid, ");
sqlCommand.Append(":name, ");
sqlCommand.Append(":code ");
sqlCommand.Append(")");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_GeoZone table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="countryGuid"> countryGuid </param>
/// <param name="name"> name </param>
/// <param name="code"> code </param>
/// <returns>int</returns>
public async Task <bool> Create(
Guid guid,
Guid countryGuid,
string name,
string code,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_GeoZone (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("CountryGuid, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("Code )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?CountryGuid, ");
sqlCommand.Append("?Name, ");
sqlCommand.Append("?Code )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?CountryGuid", MySqlDbType.VarChar, 36);
arParams[1].Value = countryGuid.ToString();
arParams[2] = new MySqlParameter("?Name", MySqlDbType.VarChar, 255);
arParams[2].Value = name;
arParams[3] = new MySqlParameter("?Code", MySqlDbType.VarChar, 255);
arParams[3].Value = code;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken);
return(rowsAffected > 0);
}
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();
//MS SQL proc checks that no matching record exists, may need to check that
//here
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_userroles (");
sqlCommand.Append("userid, ");
sqlCommand.Append("roleid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("roleguid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":userid, ");
sqlCommand.Append(":roleid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":roleguid ");
sqlCommand.Append(")");
sqlCommand.Append(";");
//object result = await AdoHelper.ExecuteScalarAsync(
// writeConnectionString,
// CommandType.StoredProcedure,
// "mp_userroles_insert(:roleid,:userid,:roleguid,:userguid)",
// arParams);
//int rowsAffected = Convert.ToInt32(result);
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public async Task <bool> Add(
Guid guid,
Guid siteGuid,
string folderName,
CancellationToken cancellationToken)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[1].Value = siteGuid.ToString();
arParams[2] = new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Value = folderName;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_sitefolders (");
sqlCommand.Append("guid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("foldername )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":foldername ");
sqlCommand.Append(")");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken);
return(rowsAffected > 0);
}
public async Task <bool> Create(
int siteId,
string loginProvider,
string providerKey,
string providerDisplayName,
string userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_userlogins (");
sqlCommand.Append("loginprovider ,");
sqlCommand.Append("providerkey, ");
sqlCommand.Append("userid, ");
sqlCommand.Append("siteid, ");
sqlCommand.Append("providerdisplayname ");
sqlCommand.Append(") ");
sqlCommand.Append("VALUES (");
sqlCommand.Append(":loginprovider, ");
sqlCommand.Append(":providerkey, ");
sqlCommand.Append(":userid, ");
sqlCommand.Append(":siteid, ");
sqlCommand.Append(":providerdisplayname ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[5];
arParams[0] = new NpgsqlParameter("loginprovider", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[0].Value = loginProvider;
arParams[1] = new NpgsqlParameter("providerkey", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[1].Value = providerKey;
arParams[2] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[2].Value = userId;
arParams[3] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Value = siteId;
arParams[4] = new NpgsqlParameter("providerdisplayname", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[4].Value = providerDisplayName;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public async Task <bool> EnsureSettings()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_sitesettingsex ");
sqlCommand.Append("(");
sqlCommand.Append("siteid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("keyname, ");
sqlCommand.Append("keyvalue, ");
sqlCommand.Append("groupname ");
sqlCommand.Append(") ");
sqlCommand.Append("SELECT ");
sqlCommand.Append("t.siteid, ");
sqlCommand.Append("t.siteguid, ");
sqlCommand.Append("t.keyname, ");
sqlCommand.Append("t.defaultvalue, ");
sqlCommand.Append("t.groupname ");
sqlCommand.Append("FROM ");
sqlCommand.Append("( ");
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.siteid, ");
sqlCommand.Append("s.siteguid, ");
sqlCommand.Append("d.keyname, ");
sqlCommand.Append("d.defaultvalue, ");
sqlCommand.Append("d.groupname ");
sqlCommand.Append("FROM ");
sqlCommand.Append("mp_sites s, ");
//sqlCommand.Append("FULL OUTER JOIN ");
sqlCommand.Append("mp_sitesettingsexdef d ");
sqlCommand.Append(") as t ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_sitesettingsex e ");
sqlCommand.Append("ON ");
sqlCommand.Append("e.siteid = t.siteid ");
sqlCommand.Append("AND e.keyname = t.keyname ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("e.siteid IS NULL ");
sqlCommand.Append("; ");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
null);
return(rowsAffected > 0);
}
public async Task <bool> Create(
int siteId,
string loginProvider,
string providerKey,
string providerDisplayName,
string userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_UserLogins (");
sqlCommand.Append("LoginProvider ,");
sqlCommand.Append("ProviderKey, ");
sqlCommand.Append("ProviderDisplayName, ");
sqlCommand.Append("UserId, ");
sqlCommand.Append("SiteId ");
sqlCommand.Append(") ");
sqlCommand.Append("VALUES (");
sqlCommand.Append("@LoginProvider, ");
sqlCommand.Append("@ProviderKey, ");
sqlCommand.Append("@ProviderDisplayName, ");
sqlCommand.Append("@UserId, ");
sqlCommand.Append("@SiteId ");
sqlCommand.Append(")");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[5];
arParams[0] = new FbParameter("@LoginProvider", FbDbType.VarChar, 128);
arParams[0].Value = loginProvider;
arParams[1] = new FbParameter("@ProviderKey", FbDbType.VarChar, 128);
arParams[1].Value = providerKey;
arParams[2] = new FbParameter("@UserId", FbDbType.VarChar, 128);
arParams[2].Value = userId;
arParams[3] = new FbParameter("@SiteId", FbDbType.Integer);
arParams[3].Value = siteId;
arParams[4] = new FbParameter("@ProviderDisplayName", FbDbType.VarChar, 100);
arParams[4].Value = providerDisplayName;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public async Task <bool> Add(
Guid guid,
Guid siteGuid,
string folderName,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SiteFolders (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("FolderName )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?FolderName );");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?FolderName", MySqlDbType.VarChar, 255);
arParams[2].Value = folderName;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken);
return(rowsAffected > 0);
}
public async Task <bool> EnsureSettings()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SiteSettingsEx");
sqlCommand.Append("( ");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("KeyName, ");
sqlCommand.Append("KeyValue, ");
sqlCommand.Append("GroupName ");
sqlCommand.Append(")");
sqlCommand.Append("SELECT ");
sqlCommand.Append("t.SiteID, ");
sqlCommand.Append("t.SiteGuid, ");
sqlCommand.Append("t.KeyName, ");
sqlCommand.Append("t.DefaultValue, ");
sqlCommand.Append("t.GroupName ");
sqlCommand.Append("FROM ");
sqlCommand.Append("( ");
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.SiteID, ");
sqlCommand.Append("s.SiteGuid, ");
sqlCommand.Append("d.KeyName, ");
sqlCommand.Append("d.DefaultValue, ");
sqlCommand.Append("d.GroupName ");
sqlCommand.Append("FROM ");
sqlCommand.Append("mp_Sites s, ");
sqlCommand.Append("mp_SiteSettingsExDef d ");
sqlCommand.Append(") t ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_SiteSettingsEx e ");
sqlCommand.Append("ON ");
sqlCommand.Append("e.SiteID = t.SiteID ");
sqlCommand.Append("AND e.KeyName = t.KeyName ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("e.SiteID IS NULL ");
sqlCommand.Append("; ");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
null);
return(rowsAffected > 0);
}
public async Task <bool> Delete(Guid guid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Value = guid.ToString();
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.StoredProcedure,
"mp_sitefolders_delete(:guid)",
arParams);
return(rowsAffected > -1);
}
public async Task <bool> Delete(
int id,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_UserClaims ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Id = ?Id ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?Id", MySqlDbType.Int32);
arParams[0].Value = id;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken);
return(rowsAffected > 0);
}
/// <summary>
/// Deletes rows from the mp_SystemLog table. Returns true if rows deleted.
/// </summary>
public async Task <bool> DeleteAll()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_systemlog ");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
null);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_GeoCountry table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="name"> name </param>
/// <param name="iSOCode2"> iSOCode2 </param>
/// <param name="iSOCode3"> iSOCode3 </param>
/// <returns>int</returns>
public async Task <bool> Create(
Guid guid,
string name,
string iSOCode2,
string iSOCode3)
{
#region Bit Conversion
#endregion
FbParameter[] arParams = new FbParameter[4];
arParams[0] = new FbParameter("@Guid", FbDbType.Char, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new FbParameter("@Name", FbDbType.VarChar, 255);
arParams[1].Value = name;
arParams[2] = new FbParameter("@ISOCode2", FbDbType.Char, 2);
arParams[2].Value = iSOCode2;
arParams[3] = new FbParameter("@ISOCode3", FbDbType.Char, 3);
arParams[3].Value = iSOCode3;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_GeoCountry (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("ISOCode2, ");
sqlCommand.Append("ISOCode3 )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@Guid, ");
sqlCommand.Append("@Name, ");
sqlCommand.Append("@ISOCode2, ");
sqlCommand.Append("@ISOCode3 )");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Inserts a row in the mp_Language table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="name"> name </param>
/// <param name="code"> code </param>
/// <param name="sort"> sort </param>
/// <returns>int</returns>
public async Task <bool> Create(
Guid guid,
string name,
string code,
int sort)
{
#region Bit Conversion
#endregion
FbParameter[] arParams = new FbParameter[4];
arParams[0] = new FbParameter("@Guid", FbDbType.Char, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new FbParameter("@Name", FbDbType.VarChar, 255);
arParams[1].Value = name;
arParams[2] = new FbParameter("@Code", FbDbType.Char, 2);
arParams[2].Value = code;
arParams[3] = new FbParameter("@Sort", FbDbType.Integer);
arParams[3].Value = sort;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_Language (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("Code, ");
sqlCommand.Append("\"Sort\" )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@Guid, ");
sqlCommand.Append("@Name, ");
sqlCommand.Append("@Code, ");
sqlCommand.Append("@Sort )");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_GeoCountry table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="name"> name </param>
/// <param name="iSOCode2"> iSOCode2 </param>
/// <param name="iSOCode3"> iSOCode3 </param>
/// <returns>bool</returns>
public async Task <bool> Create(
Guid guid,
string name,
string iSOCode2,
string iSOCode3)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Value = name;
arParams[2] = new NpgsqlParameter("isocode2", NpgsqlTypes.NpgsqlDbType.Text, 2);
arParams[2].Value = iSOCode2;
arParams[3] = new NpgsqlParameter("isocode3", NpgsqlTypes.NpgsqlDbType.Text, 3);
arParams[3].Value = iSOCode3;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_geocountry (");
sqlCommand.Append("guid, ");
sqlCommand.Append("name, ");
sqlCommand.Append("isocode2, ");
sqlCommand.Append("isocode3 )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":name, ");
sqlCommand.Append(":isocode2, ");
sqlCommand.Append(":isocode3 ");
sqlCommand.Append(")");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_Language table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="name"> name </param>
/// <param name="code"> code </param>
/// <param name="sort"> sort </param>
/// <returns>bool</returns>
public async Task <bool> Create(
Guid guid,
string name,
string code,
int sort)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Value = name;
arParams[2] = new NpgsqlParameter("code", NpgsqlTypes.NpgsqlDbType.Text, 2);
arParams[2].Value = code;
arParams[3] = new NpgsqlParameter("sort", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Value = sort;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_language (");
sqlCommand.Append("guid, ");
sqlCommand.Append("name, ");
sqlCommand.Append("code, ");
sqlCommand.Append("sort )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":name, ");
sqlCommand.Append(":code, ");
sqlCommand.Append(":sort ");
sqlCommand.Append(")");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public async Task <bool> DeleteUserRoles(int userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_UserRoles ");
sqlCommand.Append("WHERE UserID = @UserID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@UserID", FbDbType.Integer);
arParams[0].Value = userId;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public async Task <bool> DeleteUserRolesBySite(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_UserRoles ");
sqlCommand.Append("WHERE RoleID IN (SELECT RoleID FROM mp_Roles WHERE SiteID = ?SiteID) ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Value = siteId;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_Language table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="name"> name </param>
/// <param name="code"> code </param>
/// <param name="sort"> sort </param>
/// <returns>int</returns>
public async Task <bool> Create(
Guid guid,
string name,
string code,
int sort)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_Language (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("Code, ");
sqlCommand.Append("Sort )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?Name, ");
sqlCommand.Append("?Code, ");
sqlCommand.Append("?Sort )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?Name", MySqlDbType.VarChar, 255);
arParams[1].Value = name;
arParams[2] = new MySqlParameter("?Code", MySqlDbType.VarChar, 2);
arParams[2].Value = code;
arParams[3] = new MySqlParameter("?Sort", MySqlDbType.Int32);
arParams[3].Value = sort;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Deletes a row from the mp_SystemLog table. Returns true if row deleted.
/// </summary>
/// <param name="id"> id </param>
/// <returns>bool</returns>
public async Task <bool> Delete(int id)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SystemLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ID = @ID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ID", FbDbType.Integer);
arParams[0].Value = id;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Deletes rows from the mp_SystemLog table. Returns true if rows deleted.
/// </summary>
/// <param name="id"> id </param>
/// <returns>bool</returns>
public async Task <bool> DeleteByLevel(string logLevel)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SystemLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LogLevel = @LogLevel ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@LogLevel", FbDbType.VarChar, 20);
arParams[0].Value = logLevel;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Deletes rows from the mp_SystemLog table. Returns true if rows deleted.
/// </summary>
/// <param name="id"> id </param>
/// <returns>bool</returns>
public async Task <bool> DeleteOlderThan(DateTime cutoffDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SystemLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LogDate < @CutoffDate ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@CutoffDate", FbDbType.TimeStamp);
arParams[0].Value = cutoffDate;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public async Task <bool> Delete(int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_Roles ");
sqlCommand.Append("WHERE RoleID = ?RoleID AND RoleName <> 'Admins' AND RoleName <> 'Content Administrators' AND RoleName <> 'Authenticated Users' AND RoleName <> 'Role Admins' ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?RoleID", MySqlDbType.Int32);
arParams[0].Value = roleId;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public async Task <bool> Delete(Guid guid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE 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();
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public async Task <bool> Update(
Guid guid,
Guid siteGuid,
string folderName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[1].Value = siteGuid.ToString();
arParams[2] = new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Value = folderName;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_sitefolders ");
sqlCommand.Append("SET ");
sqlCommand.Append("siteguid = :siteguid, ");
sqlCommand.Append("foldername = :foldername ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("guid = :guid ");
sqlCommand.Append(";");
//int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
// writeConnectionString,
// CommandType.StoredProcedure,
// "mp_sitefolders_update(:guid,:siteguid,:foldername)",
// arParams);
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public async Task <bool> DeleteByCountry(Guid countryGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_GeoZone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("CountryGuid = @Guid ");
sqlCommand.Append("OR CountryGuid = UPPER(@Guid) ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@CountryGuid", FbDbType.Char, 36);
arParams[0].Value = countryGuid.ToString();
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public async Task <bool> DeleteBySite(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_UserClaims ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteId = @SiteId ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteId", FbDbType.Integer);
arParams[0].Value = siteId;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Updates a row in the mp_GeoZone table. Returns true if row updated.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="countryGuid"> countryGuid </param>
/// <param name="name"> name </param>
/// <param name="code"> code </param>
/// <returns>bool</returns>
public async Task <bool> Update(
Guid guid,
Guid countryGuid,
string name,
string code)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_GeoZone ");
sqlCommand.Append("SET ");
sqlCommand.Append("CountryGuid = @CountryGuid, ");
sqlCommand.Append("Name = @Name, ");
sqlCommand.Append("Code = @Code ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = @Guid ");
sqlCommand.Append("OR Guid = UPPER(@Guid) ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[4];
arParams[0] = new FbParameter("@Guid", FbDbType.Char, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new FbParameter("@CountryGuid", FbDbType.Char, 36);
arParams[1].Value = countryGuid.ToString();
arParams[2] = new FbParameter("@Name", FbDbType.VarChar, 255);
arParams[2].Value = name;
arParams[3] = new FbParameter("@Code", FbDbType.VarChar, 255);
arParams[3].Value = code;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Updates a row in the mp_GeoZone table. Returns true if row updated.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="countryGuid"> countryGuid </param>
/// <param name="name"> name </param>
/// <param name="code"> code </param>
/// <returns>bool</returns>
public async Task <bool> Update(
Guid guid,
Guid countryGuid,
string name,
string code)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("countryguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Value = countryGuid.ToString();
arParams[2] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Value = name;
arParams[3] = new NpgsqlParameter("code", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[3].Value = code;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_geozone ");
sqlCommand.Append("SET ");
sqlCommand.Append("countryguid = :countryguid, ");
sqlCommand.Append("name = :name, ");
sqlCommand.Append("code = :code ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("guid = :guid ");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public async Task <bool> DeleteUserRolesBySite(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_userroles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("roleid IN (SELECT roleid FROM mp_roles where siteid = :siteid) ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
