public NHibernateUnitOfWork(IStore store, IDbConnectionAccessor accessor)
{
var conn = accessor.CreateConnection();
_session = NHibernateManager.Instance(store, conn.ConnectionString)
.WithOptions()
.Connection(accessor.CreateConnection())
.OpenSession();
conn.Close();
}
public async Task <IEnumerable <IndexingTask> > GetIndexingTasksAsync(int afterTaskId, int count)
{
using (var connection = _dbConnectionAccessor.CreateConnection())
{
await connection.OpenAsync();
try
{
var dialect = SqlDialectFactory.For(connection);
var sqlBuilder = dialect.CreateBuilder(_tablePrefix);
sqlBuilder.Select();
sqlBuilder.Table(nameof(IndexingTask));
sqlBuilder.Selector("*");
if (count > 0)
{
sqlBuilder.Take(count.ToString());
}
sqlBuilder.WhereAlso($"{dialect.QuoteForColumnName("Id")} > @Id");
return(await connection.QueryAsync <IndexingTask>(sqlBuilder.ToSqlString(), new { Id = afterTaskId }));
}
catch (Exception e)
{
Logger.LogError(e, "An error occurred while reading indexing tasks");
throw;
}
}
}
public void SendDataToPgl(ProductMainModel model)
{
using (var connection = _dbAccessor.CreateConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var dialect = SqlDialectFactory.For(connection);
var customTable = dialect.QuoteForTableName($"{_tablePrefix}_Product");
var documentId = dialect.QuoteForColumnName($"DocumentId");
var productName = dialect.QuoteForColumnName($"ProductName");
var productPrice = dialect.QuoteForColumnName($"ProductPrice");
connection.Execute($"INSERT INTO {customTable} ({documentId},{productName},{productPrice}) VALUES(1,@ProductName,@ProductPrice)", model);
//var amodel = connection.Execute(selectCommand);
// If an exception occurs the transaction is disposed and rollbacked
transaction.Commit();
}
}
}
protected OrchardRepositoryDapper(IDbConnectionAccessor dbConnectionAccessor, IStore store, ILogger <TEntity> logger, ShellSettings shellSettings) :
base(dbConnectionAccessor.CreateConnection(), new SqlGenerator <TEntity>(GetSqlProvider(shellSettings), true))
{
DbConnectionAccessor = dbConnectionAccessor;
Store = store;
Logger = logger;
TablePrefix = shellSettings["TablePrefix"];
if (!String.IsNullOrEmpty(TablePrefix))
{
TablePrefix += '_';
}
}
public async Task ExecuteAsync(RecipeExecutionContext context)
{
if (!string.Equals(context.Name, "DirectSQL", StringComparison.OrdinalIgnoreCase))
{
return;
}
var siteSettings = await _siteService.LoadSiteSettingsAsync();
var environSettings = new JArray(context.Environment);
siteSettings.Properties["DatabaseProvider"] = environSettings["DatabaseProvider"]?.ToString();
siteSettings.Properties["DatabaseConnectionString"] = environSettings["DatabaseConnectionString"]?.ToString();
siteSettings.Properties["DatabaseTablePrefix"] = environSettings["DatabaseTablePrefix"]?.ToString();
await _siteService.UpdateSiteSettingsAsync(siteSettings);
var model = context.Step.ToObject <DirectSqlModel>();
if (model != null)
{
foreach (var jToken in model.Statements)
{
var token = (JObject)jToken;
await using (var connection = _dbAccessor.CreateConnection())
{
await connection.OpenAsync();
await using (var transaction = await connection.BeginTransactionAsync())
{
await connection.ExecuteAsync(token["Script"]?.ToString(), null, transaction, 30, CommandType.Text);
await transaction.CommitAsync();
}
await connection.CloseAsync();
}
}
}
}
public async Task <IQueryResults> ExecuteQueryAsync(Query query, IDictionary <string, object> parameters)
{
var sqlQuery = query as SqlQuery;
var sqlQueryResults = new SQLQueryResults();
var tokenizedQuery = await _liquidTemplateManager.RenderStringAsync(sqlQuery.Template, NullEncoder.Default,
parameters.Select(x => new KeyValuePair <string, FluidValue>(x.Key, FluidValue.Create(x.Value, _templateOptions))));
var connection = _dbConnectionAccessor.CreateConnection();
var dialect = _session.Store.Configuration.SqlDialect;
if (!SqlParser.TryParse(tokenizedQuery, dialect, _session.Store.Configuration.TablePrefix, parameters, out var rawQuery, out var messages))
{
sqlQueryResults.Items = new object[0];
connection.Dispose();
return(sqlQueryResults);
}
if (sqlQuery.ReturnDocuments)
{
IEnumerable <int> documentIds;
using (connection)
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction(_session.Store.Configuration.IsolationLevel))
{
documentIds = await connection.QueryAsync <int>(rawQuery, parameters, transaction);
}
}
sqlQueryResults.Items = await _session.GetAsync <ContentItem>(documentIds.ToArray());
return(sqlQueryResults);
}
else
{
IEnumerable <dynamic> queryResults;
using (connection)
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction(_session.Store.Configuration.IsolationLevel))
{
queryResults = await connection.QueryAsync(rawQuery, parameters, transaction);
}
}
var results = new List <JObject>();
foreach (var document in queryResults)
{
results.Add(JObject.FromObject(document));
}
sqlQueryResults.Items = results;
return(sqlQueryResults);
}
}
/// <summary>
/// This method transfers module defined parameters, maps, connections, and actions into a process.
/// This is used to consolidate such things for easier maintenance.
/// </summary>
/// <param name="process">the transformalize process</param>
public void ApplyCommonSettings(Process process)
{
// common parameters
for (int i = 0; i < process.Parameters.Count; i++)
{
var parameter = process.Parameters[i];
if (Parameters.ContainsKey(parameter.Name) && string.IsNullOrEmpty(parameter.Value) && parameter.T == string.Empty && !parameter.Transforms.Any() && parameter.V == string.Empty && !parameter.Validators.Any())
{
process.Parameters[0] = Parameters[parameter.Name];
}
}
// common maps
for (int i = 0; i < process.Maps.Count; i++)
{
var map = process.Maps[i];
if (Maps.ContainsKey(map.Name) && !map.Items.Any() && map.Query == string.Empty)
{
process.Maps[i] = Maps[map.Name];
}
}
// common actions
for (int i = 0; i < process.Actions.Count; i++)
{
var action = process.Actions[i];
if (action.Name != null && Actions.ContainsKey(action.Name) && action.Type == "internal")
{
var key = action.Key;
process.Actions[i] = Actions[action.Name];
process.Actions[i].Key = key;
}
}
// common connections
for (int i = 0; i < process.Connections.Count; i++)
{
var connection = process.Connections[i];
if (connection.Provider == Transformalize.Constants.DefaultSetting)
{
if (Connections.ContainsKey(connection.Name))
{
var key = connection.Key;
var table = connection.Table;
process.Connections[i] = Connections[connection.Name];
process.Connections[i].Key = key;
process.Connections[i].Table = table;
}
else if (connection.Name == "orchard")
{
using (var cn = _dbConnectionAccessor.CreateConnection()) {
connection.ConnectionString = cn.ConnectionString;
connection.Provider = _store.Dialect.Name.ToLower();
}
}
}
}
// common fields
if (Fields.Any() && process.Entities.Any())
{
for (int x = 0; x < process.Entities.Count; x++)
{
var entity = process.Entities[x];
for (int y = 0; y < entity.Fields.Count; y++)
{
var field = process.Entities[x].Fields[y];
if (Fields.ContainsKey(field.Name) && !field.System && !field.Input && !field.Transforms.Any() && !field.Validators.Any())
{
var index = field.Index;
var masterIndex = field.MasterIndex;
process.Entities[x].Fields[y] = Fields[field.Name];
process.Entities[x].Fields[y].Index = index;
process.Entities[x].Fields[y].MasterIndex = masterIndex;
}
}
for (int y = 0; y < entity.CalculatedFields.Count; y++)
{
var field = process.Entities[x].CalculatedFields[y];
if (Fields.ContainsKey(field.Name) && !field.System && !field.Input && !field.Transforms.Any() && !field.Validators.Any())
{
var index = field.Index;
var masterIndex = field.MasterIndex;
process.Entities[x].CalculatedFields[y] = Fields[field.Name];
process.Entities[x].CalculatedFields[y].Index = index;
process.Entities[x].CalculatedFields[y].MasterIndex = masterIndex;
}
}
}
}
}