public override List <SecurityDomain> GetListInPage(int pageIndex, int pageSize, string exchange)
{
List <SecurityDomain> lst = new List <SecurityDomain>();
SecurityDomain tempSecurity;
string sql = string.Format("SELECT * FROM (SELECT SecurityID,SecurityCode,SecurityAbbr,CompanyCode,CompanyAbbr,ListingDate,ExchangeMarket,ROW_NUMBER() over(Order by SecurityID) rowNum FROM Security Where exchangeMarket = @ExchangeMarket) A WHERE A.rowNum Between {0} And {1} ", (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
SqlParameter prmExchange = new SqlParameter("@ExchangeMarket", SqlDbType.VarChar, 10)
{
Value = exchange
};
dbContext.ExecuteDataReader(reader => {
while (reader.Read())
{
tempSecurity = new SecurityDomain();
tempSecurity.SecurityID = reader.GetInt64(0);
tempSecurity.SecurityCode = reader.GetString(1);
tempSecurity.SecurityAbbr = reader.GetString(2);
tempSecurity.CompanyCode = reader.GetString(3);
tempSecurity.CompanyAbbr = reader.GetString(4);
tempSecurity.ListingDate = reader.GetDateTime(5);
tempSecurity.ExchangeMarket = reader.GetString(6);
lst.Add(tempSecurity);
}
}, sql, prmExchange);
return(lst);
}
public override void InsertIntoDB(SecurityDomain security)
{
string insertSQL = "INSERT INTO Security(SecurityCode,SecurityAbbr,CompanyAbbr,ListingDate,CompanyCode,ExchangeMarket)VALUES(@SecurityCode,@SecurityAbbr,@CompanyAbbr,@ListingDate,@CompanyCode,@ExchangeMarket)";
SqliteParameter prmCompanyCode = new SqliteParameter("@CompanyCode", DbType.String)
{
Value = security.CompanyCode
};
SqliteParameter prmCompanyAddr = new SqliteParameter("@CompanyAbbr", DbType.String)
{
Value = security.CompanyAbbr
};
SqliteParameter prmSecurityCode = new SqliteParameter("@SecurityCode", DbType.String)
{
Value = security.SecurityCode
};
SqliteParameter prmSecurityAbbr = new SqliteParameter("@SecurityAbbr", DbType.String)
{
Value = security.SecurityAbbr
};
SqliteParameter prmListingDate = new SqliteParameter("@ListingDate", DbType.DateTime)
{
Value = security.ListingDate
};
SqliteParameter prmExchangeMarket = new SqliteParameter("@ExchangeMarket", DbType.String)
{
Value = security.ExchangeMarket
};
dbContext.ExecuteNoQuery(insertSQL, prmCompanyCode, prmCompanyAddr, prmSecurityCode,
prmSecurityAbbr, prmListingDate, prmExchangeMarket);
}
public override List <SecurityDomain> GetListInPage(int pageIndex, int pageSize, string exchange)
{
List <SecurityDomain> lst = new List <SecurityDomain>();
SecurityDomain tempSecurity;
string sql = string.Format("SELECT SecurityID,SecurityCode,SecurityAbbr,CompanyCode,CompanyAbbr,ListingDate,ExchangeMarket FROM Security Where exchangeMarket = @ExchangeMarket Limit {0} Offset {1} ", pageSize, (pageIndex - 1) * pageSize);
SqliteParameter prmExchange = new SqliteParameter("@ExchangeMarket", DbType.String)
{
Value = exchange
};
dbContext.ExecuteDataReader(reader => {
while (reader.Read())
{
tempSecurity = new SecurityDomain();
tempSecurity.SecurityID = reader.GetInt32(0);
tempSecurity.SecurityCode = reader.GetString(1);
tempSecurity.SecurityAbbr = reader.GetString(2);
tempSecurity.CompanyCode = reader.GetString(3);
tempSecurity.CompanyAbbr = reader.GetString(4);
tempSecurity.ListingDate = reader.GetDateTime(5);
tempSecurity.ExchangeMarket = reader.GetString(6);
lst.Add(tempSecurity);
}
}, sql, prmExchange);
return(lst);
}
/**
* Check if current DatafeedResult contains "Keyword"
* @params: keyword: Search keyword
* @return: True: if it contains, False: if it doesn't contain
* */
public bool Find(string keyword)
{
if ((DatafeedID.Contains(keyword) && CheckStatus.Check_DatafeedID) ||
(Guid.Contains(keyword) && CheckStatus.Check_Guid) ||
(DatafeedName.Contains(keyword) && CheckStatus.Check_DatafeedName) ||
(SecurityUsername.Contains(keyword) && CheckStatus.Check_SecurityUsername) ||
(SecurityPassword.Contains(keyword) && CheckStatus.Check_SecurityPassword) ||
(TransportUsername.Contains(keyword) && CheckStatus.Check_TransportUsername) ||
(TransportPassword.Contains(keyword) && CheckStatus.Check_TransportPassword) ||
(TransportURL.Contains(keyword) && CheckStatus.Check_TransportURL) ||
(TransportInstance.Contains(keyword) && CheckStatus.Check_TransportInstance) ||
(SecurityDomain.Contains(keyword) && CheckStatus.Check_SecurityDomain) ||
(TransportDomain.Contains(keyword) && CheckStatus.Check_TransportDomain) ||
(ProxyOption.Contains(keyword) && CheckStatus.Check_ProxyOption) ||
(ProxyName.Contains(keyword) && CheckStatus.Check_ProxyName) ||
(ProxyPort.Contains(keyword) && CheckStatus.Check_ProxyPort) ||
(ProxyDomain.Contains(keyword) && CheckStatus.Check_ProxyDomain) ||
(ProxyUserName.Contains(keyword) && CheckStatus.Check_ProxyUserName) ||
(ProxyPassword.Contains(keyword) && CheckStatus.Check_ProxyPassword) ||
(TransportPath.Contains(keyword) && CheckStatus.Check_TransportPath) ||
(ConnectionString.Contains(keyword) && CheckStatus.Check_ConnectionString) ||
(SQLQueryUsername.Contains(keyword) && CheckStatus.Check_SQLQueryUsername) ||
(SQLQueryPassword.Contains(keyword) && CheckStatus.Check_SQLQueryPassword)
)
{
return(true);
}
return(false);
}
public static string GetTenantConnectionString(int tenantId)
{
try
{
return(_csMap.GetOrAdd(tenantId, (id) =>
{
EFDAL.Entity.TenantMaster item = null;
using (var context = new MultiTenantEntities(DatabaseHelper.MasterConnectionString))
{
item = context.TenantMaster.FirstOrDefault(x => x.TenantId == id);
if (item == null)
{
throw new Exception("Tenant not found");
}
}
var builder = new SqlConnectionStringBuilder(MasterConnectionString);
builder.UserID = item.DbUserName;
builder.Password = SecurityDomain.Decrypt(item.DbPassword);
builder.IntegratedSecurity = false;
return builder.ToString();
}));
}
catch (Exception ex)
{
Logger.LogError(ex);
return(null);
}
}
public override List <SecurityDomain> GetList()
{
List <SecurityDomain> securities = new List <SecurityDomain>();
SecurityDomain security;
string sql = "SELECT SecurityCode FROM Security";
dbContext.ExecuteDataReader(reader =>
{
while (reader.Read())
{
security = new SecurityDomain();
security.SecurityCode = reader.GetString(0);
securities.Add(security);
}
}, sql);
return(securities);
}
public static bool CreateTenant(string tenantName)
{
//Create a new tenant if the name does not exist
using (var context = new MultiTenantEntities(DatabaseHelper.MasterConnectionString))
{
//Check for tenant existence
if (context.TenantMaster.Any(x => x.TenantName == tenantName))
{
throw new Exception("The tenant already exists.");
}
//Create a SQL database user and store the creds for this tenant
var newUser = Utilities.GetRandomString();
var password = Utilities.GetRandomString();
var passwordCipher = SecurityDomain.Crypt(password);
var newItem = new TenantMaster
{
TenantName = tenantName,
DbUserName = newUser,
DbPassword = passwordCipher,
};
context.Add(newItem);
context.SaveChanges();
//Create database permissions
//Note: cannot use parameters as these are not data queries. They are DDL
var sb = new StringBuilder();
sb.AppendLine($"CREATE LOGIN [{newItem.DbUserName}] WITH PASSWORD=N'{password}'");
sb.AppendLine($"if not exists(select * from sys.sysusers where name = '{newItem.DbUserName}')");
sb.AppendLine($"CREATE USER [{newItem.DbUserName}] FOR LOGIN [{newItem.DbUserName}];");
//The "TenantRole" was created in a custom script in the SQL installer in the "6_UserDefinedFinalize\Always" folder
sb.AppendLine("GRANT DELETE TO [TenantRole];");
sb.AppendLine("GRANT INSERT TO [TenantRole];");
sb.AppendLine("GRANT SELECT TO [TenantRole];");
sb.AppendLine("GRANT UPDATE TO [TenantRole];");
sb.AppendLine($"ALTER ROLE [TenantRole] ADD MEMBER [{newItem.DbUserName}];");
DatabaseHelper.ExecuteSql(sb.ToString());
}
return(true);
}
/// <summary>
/// Creates a new LoaderContext object, with the specified settings.
/// </summary>
public LoaderContext(bool checkPolicyFile, ApplicationDomain applicationDomain, SecurityDomain securityDomain)
{
}
/**
* Replace DatafeedResult with keyword.
* @params: keyword: Search Keyword, replaceText: Text for Replace
* @return: Replace keyword count
* */
public int Replace(string keyword, string replaceText)
{
int replaceCount = 0;
if (DatafeedID.Contains(keyword) && CheckStatus.Check_DatafeedID)
{
DatafeedID = DatafeedID.Replace(keyword, replaceText);
replaceCount += 1;
}
if (Guid.Contains(keyword) && CheckStatus.Check_Guid)
{
Guid = Guid.Replace(keyword, replaceText);
replaceCount += 1;
}
if (DatafeedName.Contains(keyword) && CheckStatus.Check_DatafeedName)
{
DatafeedName = DatafeedName.Replace(keyword, replaceText);
replaceCount += 1;
}
if (SecurityUsername.Contains(keyword) && CheckStatus.Check_SecurityUsername)
{
SecurityUsername = SecurityUsername.Replace(keyword, replaceText);
replaceCount += 1;
}
if (SecurityPassword.Contains(keyword) && CheckStatus.Check_SecurityPassword)
{
SecurityPassword = SecurityPassword.Replace(keyword, replaceText);
replaceCount += 1;
}
if (TransportUsername.Contains(keyword) && CheckStatus.Check_TransportUsername)
{
TransportUsername = TransportUsername.Replace(keyword, replaceText);
replaceCount += 1;
}
if (TransportPassword.Contains(keyword) && CheckStatus.Check_TransportPassword)
{
TransportPassword = TransportPassword.Replace(keyword, replaceText);
replaceCount += 1;
}
if (TransportURL.Contains(keyword) && CheckStatus.Check_TransportURL)
{
TransportURL = TransportURL.Replace(keyword, replaceText);
replaceCount += 1;
}
if (TransportInstance.Contains(keyword) && CheckStatus.Check_TransportInstance)
{
TransportInstance = TransportInstance.Replace(keyword, replaceText);
replaceCount += 1;
}
if (SecurityDomain.Contains(keyword) && CheckStatus.Check_SecurityDomain)
{
SecurityDomain = SecurityDomain.Replace(keyword, replaceText);
replaceCount += 1;
}
if (TransportDomain.Contains(keyword) && CheckStatus.Check_TransportDomain)
{
TransportDomain = TransportDomain.Replace(keyword, replaceText);
replaceCount += 1;
}
if (ProxyOption.Contains(keyword) && CheckStatus.Check_ProxyOption)
{
ProxyOption = ProxyOption.Replace(keyword, replaceText);
replaceCount += 1;
}
if (ProxyName.Contains(keyword) && CheckStatus.Check_ProxyName)
{
ProxyName = ProxyName.Replace(keyword, replaceText);
replaceCount += 1;
}
if (ProxyPort.Contains(keyword) && CheckStatus.Check_ProxyPort)
{
ProxyPort = ProxyPort.Replace(keyword, replaceText);
replaceCount += 1;
}
if (ProxyDomain.Contains(keyword) && CheckStatus.Check_ProxyDomain)
{
ProxyDomain = ProxyDomain.Replace(keyword, replaceText);
replaceCount += 1;
}
if (ProxyUserName.Contains(keyword) && CheckStatus.Check_ProxyUserName)
{
ProxyUserName = ProxyUserName.Replace(keyword, replaceText);
replaceCount += 1;
}
if (ProxyPassword.Contains(keyword) && CheckStatus.Check_ProxyPassword)
{
ProxyPassword = ProxyPassword.Replace(keyword, replaceText);
replaceCount += 1;
}
if (TransportPath.Contains(keyword) && CheckStatus.Check_TransportPath)
{
TransportPath = TransportPath.Replace(keyword, replaceText);
replaceCount += 1;
}
if (ConnectionString.Contains(keyword) && CheckStatus.Check_ConnectionString)
{
ConnectionString = ConnectionString.Replace(keyword, replaceText);
replaceCount += 1;
}
if (SQLQueryUsername.Contains(keyword) && CheckStatus.Check_SQLQueryUsername)
{
SQLQueryUsername = SQLQueryUsername.Replace(keyword, replaceText);
replaceCount += 1;
}
if (SQLQueryPassword.Contains(keyword) && CheckStatus.Check_SQLQueryPassword)
{
SQLQueryPassword = SQLQueryPassword.Replace(keyword, replaceText);
replaceCount += 1;
}
return(replaceCount);
}
public abstract void InsertIntoDB(SecurityDomain security);
