public async Task <CustomActionInfo> GetCustomActionByAlias(string alias)
{
if (String.IsNullOrWhiteSpace(alias))
{
throw new ArgumentException(nameof(alias));
}
DbConnection connection = _customer.DatabaseType == DatabaseType.Postgres
? (DbConnection) new NpgsqlConnection(_customer.ConnectionString)
: new SqlConnection(_customer.ConnectionString);
using (connection)
{
await connection.OpenAsync();
var actionInfo = await connection.QuerySingleAsync <CustomActionInfo>($@"
SELECT {SqlQuerySyntaxHelper.Top(_customer.DatabaseType, "1")}
ba.CODE AS {nameof(CustomActionInfo.ActionCode)},
et.CODE AS {nameof(CustomActionInfo.EntityTypeCode)}
FROM dbo.CUSTOM_ACTION AS ca
INNER JOIN {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.BACKEND_ACTION AS ba ON ca.ACTION_ID = ba.ID
INNER JOIN {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.ENTITY_TYPE AS et ON ba.ENTITY_TYPE_ID = et.ID
WHERE ca.ALIAS = @{nameof(alias)} {SqlQuerySyntaxHelper.Limit(_customer.DatabaseType, "1")}",
new { alias });
return(actionInfo);
}
}
public ModelReader GetSchema()
{
var connector = GetDBConnector(_connection);
int siteId = connector.GetSiteId(_siteName);
bool replaceUrls;
using (var cmd = connector.CreateDbCommand($"SELECT {SqlQuerySyntaxHelper.Top(connector.DatabaseType, "1")} REPLACE_URLS FROM SITE WHERE SITE_ID = @siteId"))
{
cmd.Parameters.AddWithValue("@siteId", siteId);
replaceUrls = (bool)connector.GetRealScalarData(cmd);
}
var attributes = GetAttributes(connector, siteId);
var contents = GetContents(connector, siteId, attributes);
var model = new ModelReader();
model.Schema.DBType = connector.DatabaseType;
model.Schema.ReplaceUrls = replaceUrls;
model.Schema.SiteName = _siteName;
model.Attributes.AddRange(attributes);
model.Contents.AddRange(contents);
return(model);
}
private string GetRegionLinkIdQuery(int siteId)
{
string query =
$@"SELECT {SqlQuerySyntaxHelper.Top(_uow.DatabaseType, "1")} |QPAbstractItem.Regions| FROM |QPAbstractItem| WHERE |QPAbstractItem.Regions|
IS NOT NULL {SqlQuerySyntaxHelper.Limit(_uow.DatabaseType, "1")}";
return(_netNameQueryAnalyzer.PrepareQuery(query, siteId, false, true));
}
public async Task <DateTime?> GetMaxPublicationTime()
{
DbConnection connection = _customer.DatabaseType == DatabaseType.Postgres
? (DbConnection) new NpgsqlConnection(_customer.ConnectionString)
: new SqlConnection(_customer.ConnectionString);
using (connection)
{
await connection.OpenAsync();
DateTime?timestamp = await connection.QuerySingleAsync <DateTime?>($@"
SELECT {SqlQuerySyntaxHelper.Top(_customer.DatabaseType, "1")}
Updated
FROM {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.Products
ORDER BY Updated DESC {SqlQuerySyntaxHelper.Limit(_customer.DatabaseType, "1")}");
return(timestamp);
}
}
public CultureInfo GetCurrentUserCulture()
{
int userId = _userProvider.GetUserId();
DbConnection connection = _customer.DatabaseType == DatabaseType.Postgres
? (DbConnection) new NpgsqlConnection(_customer.ConnectionString)
: new SqlConnection(_customer.ConnectionString);
using (connection)
{
connection.Open();
int?culture = connection.QueryFirstOrDefault <int?>($@"
SELECT {SqlQuerySyntaxHelper.Top(_customer.DatabaseType, "1")} l.LOCALE
FROM {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.LANGUAGES AS l
INNER JOIN {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.USERS AS u ON l.LANGUAGE_ID = u.LANGUAGE_ID
WHERE u.USER_ID = @{nameof(userId)} {SqlQuerySyntaxHelper.Limit(_customer.DatabaseType, "1")}",
new { userId });
return(culture != null?CultureInfo.GetCultureInfo(culture.Value) : null);
}
}
private string GetActionCodeInternal(string name)
{
string sql = $@"select {SqlQuerySyntaxHelper.Top(_customer.DatabaseType, "1")} CODE, NAME, ID
FROM backend_action
WHERE name like @name {SqlQuerySyntaxHelper.Limit(_customer.DatabaseType, "1")}" ;
if (_customer.DatabaseType == DatabaseType.SqlServer)
{
SqlConnection connection = new SqlConnection(_customer.ConnectionString);
using (connection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var codes = new List <string>();
using (var cmd = new SqlCommand(sql, connection))
{
var idParameter = new SqlParameter();
idParameter.ParameterName = "@name"; // Defining Name
idParameter.SqlDbType = SqlDbType.NVarChar; // Defining DataType
idParameter.Direction = ParameterDirection.Input; // Setting the direction
idParameter.Value = name;
cmd.Parameters.Add(idParameter);
using (SqlDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
codes.Add(rd.GetString(0));
}
rd.Close();
}
}
return(codes.FirstOrDefault());
}
}
else
{
NpgsqlConnection connection = new NpgsqlConnection(_customer.ConnectionString);
using (connection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var codes = new List <string>();
using (var cmd = new NpgsqlCommand(sql, connection))
{
var idParameter = new NpgsqlParameter();
idParameter.ParameterName = "@name"; // Defining Name
idParameter.NpgsqlDbType = NpgsqlDbType.Text; // Defining DataType
idParameter.Direction = ParameterDirection.Input; // Setting the direction
idParameter.Value = name;
cmd.Parameters.Add(idParameter);
using (NpgsqlDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
codes.Add(rd.GetString(0));
}
rd.Close();
}
}
return(codes.FirstOrDefault());
}
}
}