/// <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); }