public async Task <bool> DeleteAll(CancellationToken cancellationToken)
{
//TODO: does firebird support truncate table?
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,
CommandType.Text,
sqlCommand.ToString(),
true,
null,
cancellationToken);
return(rowsAffected > 0);
}
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(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@Id", FbDbType.Integer);
arParams[0].Value = id;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
true,
arParams,
cancellationToken);
return(rowsAffected > -1);
}
public async Task <bool> Create(
int siteId,
string loginProvider,
string providerKey,
string providerDisplayName,
string userId,
CancellationToken cancellationToken)
{
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,
CommandType.Text,
sqlCommand.ToString(),
true,
arParams,
cancellationToken);
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,
CancellationToken cancellationToken)
{
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,
CommandType.Text,
sqlCommand.ToString(),
true,
arParams,
cancellationToken);
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,
CancellationToken cancellationToken)
{
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,
CommandType.Text,
sqlCommand.ToString(),
true,
arParams,
cancellationToken);
return(rowsAffected > 0);
}
public async Task <bool> Update(
int roleId,
string roleName,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_Roles ");
sqlCommand.Append("SET DisplayName = @RoleName ");
sqlCommand.Append("WHERE RoleID = @RoleID ;");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@RoleID", FbDbType.Integer);
arParams[0].Value = roleId;
arParams[1] = new FbParameter("@RoleName", FbDbType.VarChar, 50);
arParams[1].Value = roleName;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
true,
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,
CommandType.Text,
sqlCommand.ToString(),
true,
null);
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 );");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@Guid", FbDbType.VarChar, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new FbParameter("@SiteGuid", FbDbType.VarChar, 36);
arParams[1].Value = siteGuid.ToString();
arParams[2] = new FbParameter("@FolderName", FbDbType.VarChar, 255);
arParams[2].Value = folderName;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
true,
arParams,
cancellationToken);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_Currency table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="title"> title </param>
/// <param name="code"> code </param>
/// <param name="symbolLeft"> symbolLeft </param>
/// <param name="symbolRight"> symbolRight </param>
/// <param name="decimalPointChar"> decimalPointChar </param>
/// <param name="thousandsPointChar"> thousandsPointChar </param>
/// <param name="decimalPlaces"> decimalPlaces </param>
/// <param name="value"> value </param>
/// <param name="lastModified"> lastModified </param>
/// <param name="created"> created </param>
/// <returns>int</returns>
public async Task <bool> Create(
Guid guid,
string title,
string code,
string symbolLeft,
string symbolRight,
string decimalPointChar,
string thousandsPointChar,
string decimalPlaces,
decimal value,
DateTime lastModified,
DateTime created,
CancellationToken cancellationToken)
{
FbParameter[] arParams = new FbParameter[11];
arParams[0] = new FbParameter("@Guid", FbDbType.Char, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new FbParameter("@Title", FbDbType.VarChar, 50);
arParams[1].Value = title;
arParams[2] = new FbParameter("@Code", FbDbType.Char, 3);
arParams[2].Value = code;
arParams[3] = new FbParameter("@SymbolLeft", FbDbType.VarChar, 15);
arParams[3].Value = symbolLeft;
arParams[4] = new FbParameter("@SymbolRight", FbDbType.VarChar, 15);
arParams[4].Value = symbolRight;
arParams[5] = new FbParameter("@DecimalPointChar", FbDbType.Char, 1);
arParams[5].Value = decimalPointChar;
arParams[6] = new FbParameter("@ThousandsPointChar", FbDbType.Char, 1);
arParams[6].Value = thousandsPointChar;
arParams[7] = new FbParameter("@DecimalPlaces", FbDbType.Char, 1);
arParams[7].Value = decimalPlaces;
arParams[8] = new FbParameter("@Value", FbDbType.Decimal);
arParams[8].Value = value;
arParams[9] = new FbParameter("@LastModified", FbDbType.TimeStamp);
arParams[9].Value = lastModified;
arParams[10] = new FbParameter("@Created", FbDbType.TimeStamp);
arParams[10].Value = created;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_Currency (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("Code, ");
sqlCommand.Append("SymbolLeft, ");
sqlCommand.Append("SymbolRight, ");
sqlCommand.Append("DecimalPointChar, ");
sqlCommand.Append("ThousandsPointChar, ");
sqlCommand.Append("DecimalPlaces, ");
sqlCommand.Append("\"Value\", ");
sqlCommand.Append("LastModified, ");
sqlCommand.Append("Created )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@Guid, ");
sqlCommand.Append("@Title, ");
sqlCommand.Append("@Code, ");
sqlCommand.Append("@SymbolLeft, ");
sqlCommand.Append("@SymbolRight, ");
sqlCommand.Append("@DecimalPointChar, ");
sqlCommand.Append("@ThousandsPointChar, ");
sqlCommand.Append("@DecimalPlaces, ");
sqlCommand.Append("@Value, ");
sqlCommand.Append("@LastModified, ");
sqlCommand.Append("@Created )");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
true,
arParams,
cancellationToken);
return(rowsAffected > 0);
}