/// <summary>
/// 查询数据库
/// </summary>
/// <param name="Sql">查询数据库SQL语句</param>
/// <param name="msg">出现异常时的返回字符串</param>
/// <returns>查询结果转化为DataTable类型</returns>
public static DataTable Query(string sql, out string msg)
{
try
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConnectionString;
SqlExtensions.QuickOpen(con, OpenDbTimeout);
//con.Open(); 该方法连接数据库超时太久
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.Text;
com.CommandText = sql;
com.CommandTimeout = con.ConnectionTimeout;//超时
SqlDataAdapter SqlDap = new SqlDataAdapter(com);
DataSet dataset = new DataSet();
SqlDap.Fill(dataset);
SqlDap.Dispose();
com.Dispose();
con.Close();
msg = string.Empty;
return(dataset.Tables[0]);
}
catch (Exception ex)
{
msg = ex.Message + "SQL:" + sql;
return(null);
}
}
/// <summary>
/// 写数据库
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="timeout">超时时间，单位：秒</param>
/// <param name="msg">出现异常时的返回字符串</param>
/// <returns>返回布尔类型表示操作是否成功</returns>
public static bool NonQuery(string sql, int timeout, out string msg)
{
try
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConnectionString;
SqlExtensions.QuickOpen(con, OpenDbTimeout);
//con.Open(); 该方法连接数据库超时太久
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.Text;
com.CommandText = sql;
com.CommandTimeout = timeout == -1 ? con.ConnectionTimeout : timeout;//超时
com.ExecuteNonQuery();
com.Dispose();
con.Close();
msg = string.Empty;
return(true);
}
catch (Exception ex)
{
msg = ex.Message + "SQL:" + sql;
return(false);
}
}
/// <summary>
/// 插入数据库并返回Id
/// </summary>
/// <param name="Sql">SQL语句</param>
/// <param name="msg">出现异常时的返回字符串</param>
/// <returns>返回新插入行Id</returns>
public static int Insert(string sql, out string msg)
{
try
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConnectionString;
SqlExtensions.QuickOpen(con, OpenDbTimeout);
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.Text;
com.CommandText = sql + " Select @@Identity";
com.CommandTimeout = con.ConnectionTimeout;//超时
int id = Convert.ToInt32(com.ExecuteScalar());
com.Dispose();
con.Close();
msg = string.Empty;
return(id);
}
catch (Exception ex)
{
msg = ex.Message + "SQL:" + sql;
return(-1);
}
}
public IEnumerable <object> GetAll(bool fireEvents = true)
{
var query = new Sql($"SELECT * FROM [{_collection.EntityType.GetTableName()}]");
bool hasWhere = false;
if (_collection.FilterExpression != null)
{
query.Where(_collection.EntityType, _collection.FilterExpression, SyntaxProvider);
hasWhere = true;
}
if (_collection.DeletedProperty != null)
{
var prefix = !hasWhere ? "WHERE" : "AND";
query.Append($" {prefix} {_collection.DeletedProperty.GetColumnName()} = 0 ");
}
if (_collection.SortProperty != null)
{
if (_collection.SortDirection == SortDirection.Ascending)
{
SqlExtensions.OrderBy(query, _collection.EntityType, _collection.SortProperty, SyntaxProvider);
}
else
{
SqlExtensions.OrderByDescending(query, _collection.EntityType, _collection.SortProperty, SyntaxProvider);
}
}
return(Db.Fetch(_collection.EntityType, query));
}
private async Task <bool> ExistsNameAsync(Guid parentId, string name)
{
using (var reader = await SqlExtensions.ExecuteReaderAsync(ConnectionString, "SELECT 1 FROM Item WHERE ParentId = @id AND Name=@name", new { id = parentId, name }, Logger).ConfigureAwait(false))
{
return(await reader.ReadAsync());
}
}
public async Task WriteAsync(SqlItem item, Stream stream)
{
if (item == null)
{
throw new ArgumentNullException(nameof(item));
}
if (item.Id == Guid.Empty)
{
throw new UnauthorizedAccessException();
}
Log("Item : " + item.Id + " '" + item.Name + " stream: " + stream);
if (stream == null)
{
await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, "UPDATE Item SET Data = NULL WHERE Id = @id", new { id = item.Id }, Logger).ConfigureAwait(false);
}
else
{
await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, "UPDATE Item SET Data = @data WHERE Id = @id", new { id = item.Id, data = stream }, Logger).ConfigureAwait(false);
}
if (!IsTempFile(item.Name))
{
AddEvent(item.Id, item.ParentId, WatcherChangeTypes.Changed);
}
}
public async Task <IFileSystemInfo> MoveToAsync(SqlItem item, Guid newParentId, MoveOptions options = null)
{
if (item == null)
{
throw new ArgumentNullException(nameof(item));
}
if (item.Id == Guid.Empty)
{
throw new UnauthorizedAccessException();
}
options ??= new MoveOptions();
if (options.Copy)
{
return(await CopyToAsync(item, newParentId).ConfigureAwait(false));
}
Log("New Parent: " + newParentId + " item: " + item.Id + " '" + item.Name + "'");
var oldParent = item.Parent;
var sql = "UPDATE Item SET ParentId = @parentId WHERE Id = @id";
await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, sql, new { id = item.Id, parentId = newParentId }, Logger).ConfigureAwait(false);
var newItem = await GetItemAsync(item.Id).ConfigureAwait(false);
if (oldParent != null)
{
AddEvent(oldParent.Id, oldParent.ParentId, WatcherChangeTypes.Changed);
}
AddEvent(newItem.ParentId, (newItem.Parent?.ParentId).GetValueOrDefault(), WatcherChangeTypes.Changed);
return(newItem);
}
public async Task <Stream> OpenReadAsync(SqlItem item)
{
if (item == null)
{
throw new ArgumentNullException(nameof(item));
}
if (item.Id == Guid.Empty)
{
throw new UnauthorizedAccessException();
}
using (var reader = await SqlExtensions.ExecuteReaderAsync(ConnectionString, "SELECT Data FROM Item WHERE Id = @id", new { id = item.Id }, Logger).ConfigureAwait(false))
{
if (!reader.Read())
{
return(null);
}
if (await reader.IsDBNullAsync(0))
{
return(null);
}
// it's a pitty GetFieldValueAsync<Stream> doesn't work... https://github.com/dotnet/runtime/issues/28596#issuecomment-484614943
return(reader.GetSqlBytes(0).Stream);
}
}
public DataSet ExecuteQuerySearchSchema(string connectionString, IList <FilterCriteria> filter, DataSetSchema schema, int currentPage,
int pageSize, string orderByColumn, string orderDirection)
{
var query = BuildSqlQuery(filter, schema, orderByColumn, orderDirection, currentPage, pageSize);
return(SqlExtensions.ExecuteQuery(connectionString, query.Sql, query.Params));
}
protected SqlConnection CreateConnection()
{
//Attempts to connect with the QuickOpen function which throws after 5000 ms if we didn't connect
//That way we can assume that it's safe to connect to the server and we won't get 30 s of loading
SqlExtensions.QuickOpen(new SqlConnection(_connectionString), 5000, "Anslutningen till databasen misslyckades.");
return(new SqlConnection(_connectionString));
}
public async Task <int> ClearOldChanges(DateTime startTime)
{
var sql = "DELETE FROM Change WHERE CreationTimeUtc < @startTime";
var count = await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, sql, new { startTime = startTime.ToUniversalTime() }, Logger).ConfigureAwait(false);
Log("Deleted:" + count);
return(count);
}
public PagedResult <object> GetPaged(int pageNumber, int pageSize, LambdaExpression whereClause, LambdaExpression orderBy, SortDirection orderDirection, bool fireEvents = true)
{
var query = new Sql($"SELECT * FROM [{_collection.EntityType.GetTableName()}]");
// Where
if (_collection.FilterExpression != null && whereClause != null)
{
var body = Expression.AndAlso(whereClause.Body, _collection.FilterExpression.Body);
whereClause = Expression.Lambda(body, whereClause.Parameters[0]);
}
else if (_collection.FilterExpression != null)
{
whereClause = _collection.FilterExpression;
}
if (whereClause != null)
{
query.Where(_collection.EntityType, whereClause, SyntaxProvider);
}
else
{
query.Where("1 = 1");
}
if (_collection.DeletedProperty != null)
{
query.Append($" AND ({_collection.DeletedProperty.GetColumnName()} = 0)");
}
// Order by
LambdaExpression orderByExp = orderBy ?? _collection.SortProperty;
if (orderByExp != null)
{
if (orderDirection == SortDirection.Ascending)
{
SqlExtensions.OrderBy(query, _collection.EntityType, orderByExp, SyntaxProvider);
}
else
{
SqlExtensions.OrderByDescending(query, _collection.EntityType, orderByExp, SyntaxProvider);
}
}
else
{
// There is a bug in the Db.Page code that effectively requires there
// to be an order by clause no matter what, so if one isn't provided
// we'lld just order by 1
query.Append(" ORDER BY 1 ");
}
var result = Db.Page(_collection.EntityType, pageNumber, pageSize, query);
return(new PagedResult <object>(result.TotalItems, pageNumber, pageSize)
{
Items = result.Items
});
}
public async Task <int> ClearOldTempFiles(DateTime startTime)
{
// these are files that were uploaded but never finished for some reason
var sql = "DELETE FROM Item WHERE Name LIKE @temp AND CreationTimeUtc < @startTime";
var count = await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, sql, new { temp = Core.Synchronization.ContentMover.DefaultTemporaryEntryMarker + "%", startTime = startTime.ToUniversalTime() }, Logger).ConfigureAwait(false);
Log("Deleted:" + count);
return(count);
}
public async IAsyncEnumerable <IFileSystemEvent> EnumerateChangesAsync(DateTime startTime)
{
using (var reader = await SqlExtensions.ExecuteReaderAsync(ConnectionString, "SELECT Id, ItemId, ParentId, Type, CreationTimeUtc, OldName FROM Change WHERE CreationTimeUtc > @startTime", new { startTime = startTime.ToUniversalTime() }, Logger).ConfigureAwait(false))
{
while (reader.Read())
{
yield return(new Event(reader));
}
}
}
public void Initialize(Action <WebFolderOptions> setupAction, IFileSystemEvents events, IDictionary <string, string> properties)
{
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
if (events == null)
{
throw new ArgumentNullException(nameof(events));
}
var cnx = properties.GetNullifiedValue(nameof(ConnectionString));
if (string.IsNullOrWhiteSpace(cnx))
{
throw new WebFolderException("0001: Configuration is missing parameter '" + nameof(ConnectionString) + "'.");
}
Events = events;
ConnectionString = cnx;
UniqueId = Conversions.ComputeGuidHash(ConnectionString);
setupAction?.Invoke(Options);
Task.Run(async() =>
{
// create tables
await SqlExtensions.CreateTableAsync(ConnectionString, "Item", "Id uniqueidentifier NOT NULL, ParentId uniqueidentifier NOT NULL, Name nvarchar(260) NOT NULL, LastAccessTimeUtc datetime2 NOT NULL, CreationTimeUtc datetime2 NOT NULL, LastWriteTimeUtc datetime2 NOT NULL, Attributes int NOT NULL, Data varbinary(max) CONSTRAINT PK_Item PRIMARY KEY NONCLUSTERED (Id)").ConfigureAwait(false);
await SqlExtensions.CreateTableAsync(ConnectionString, "Change", "Id uniqueidentifier NOT NULL, ItemId uniqueidentifier NOT NULL, ParentId uniqueidentifier NOT NULL, Type int NOT NULL, CreationTimeUtc datetime2 NOT NULL, OldName nvarchar(260) CONSTRAINT PK_Change PRIMARY KEY NONCLUSTERED (Id)").ConfigureAwait(false);
// add indices
await SqlExtensions.CreateIndexAsync(ConnectionString, "IX_Parent", "CREATE NONCLUSTERED INDEX IX_Parent ON Item(ParentId)").ConfigureAwait(false);
await SqlExtensions.CreateIndexAsync(ConnectionString, "IX_ParentIdName", "CREATE UNIQUE NONCLUSTERED INDEX IX_ParentIdName ON Item(ParentId, Name)").ConfigureAwait(false);
await SqlExtensions.CreateIndexAsync(ConnectionString, "IX_ChangeCreationTimeUtc", "CREATE CLUSTERED INDEX IX_ChangeCreationTimeUtc ON Change(CreationTimeUtc)").ConfigureAwait(false);
// ensure root exists
// we don't use ms
var now = DateTime.UtcNow.RemoveMilliseconds();
await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, "IF (NOT EXISTS(SELECT * FROM Item WHERE Id = '00000000-0000-0000-0000-000000000000')) INSERT INTO Item (Id, ParentId, Name, LastAccessTimeUtc, CreationTimeUtc, LastWriteTimeUtc, Attributes) VALUES ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '', @now, @now, @now, 16)", new { now }).ConfigureAwait(false);
// clear old changes
var max = Options.MaxChangesDays;
if (max >= 0)
{
var deleteStartTime = DateTime.Now.AddDays(-max);
await ClearOldChanges(max > 0 ? DateTime.Now.AddDays(-max) : DateTime.MaxValue).ConfigureAwait(false);
}
max = Options.MaxTempFilesDays;
if (max >= 0)
{
await ClearOldTempFiles(max > 0 ? DateTime.Now.AddDays(-max) : DateTime.MaxValue).ConfigureAwait(false);
}
}).Wait();
}
private async Task MergeNoUserData()
{
var bus = ServiceContainer.Resolve <MessageBus> ();
var log = ServiceContainer.Resolve <ILogger> ();
var client = ServiceContainer.Resolve <ITogglClient> ();
var store = ServiceContainer.Resolve <IDataStore> ();
var syncDuration = Stopwatch.StartNew();
bool hasErrors = false;
Exception ex = null;
try {
var serverData = await client.GetChanges(null).ConfigureAwait(false);
await store.ExecuteInTransactionAsync(ctx => {
var user = serverData.User.Import(ctx);
var workspace = serverData.Workspaces.ElementAt(0).Import(ctx);
var workspaceId = Tuple.Create("WorkspaceId", (object)workspace.Id);
var userId = Tuple.Create("UserId", (object)user.Id);
//Lift data from the local objects to server objects
SqlExtensions.UpdateTable <ProjectData> (ctx, workspaceId);
SqlExtensions.UpdateTable <ClientData> (ctx, workspaceId);
SqlExtensions.UpdateTable <TagData> (ctx, workspaceId);
SqlExtensions.UpdateTable <TimeEntryData> (ctx, workspaceId, userId);
SqlExtensions.UpdateTable <ProjectUserData> (ctx, userId);
// Delete old data.
SqlExtensions.DeleteTable <UserData> (ctx, Tuple.Create("Name", (object)"Workspace"));
SqlExtensions.DeleteTable <WorkspaceData> (ctx, Tuple.Create("Name", (object)"offlineUser"));
});
} catch (Exception e) {
if (e.IsNetworkFailure() || e is TaskCanceledException)
{
if (e.IsNetworkFailure())
{
ServiceContainer.Resolve <INetworkPresence> ().RegisterSyncWhenNetworkPresent();
}
}
else
{
}
hasErrors = true;
ex = e;
} finally {
syncDuration.Stop();
log.Info(Tag, "Sync finished in {0}ms{1}.", syncDuration.ElapsedMilliseconds, hasErrors ? " (with errors)" : String.Empty);
bus.Send(new SyncFinishedMessage(this, SyncMode.Full, hasErrors, ex));
}
}
private async Task <SqlItem> GetSqlItemAsync(Guid parentId, string name)
{
using (var reader = await SqlExtensions.ExecuteReaderAsync(ConnectionString, "SELECT Id, ParentId, Name, LastAccessTimeUtc, CreationTimeUtc, LastWriteTimeUtc, Attributes, DATALENGTH(Data) AS Length FROM Item WHERE ParentId = @id AND Name=@name", new { id = parentId, name }, Logger).ConfigureAwait(false))
{
if (reader.Read())
{
return(NewItem(reader));
}
return(null);
}
}
/// <summary>
/// Instantiates a new <see cref="Table"/>.
/// </summary>
public Table(string tableName, string schema, bool sanitize)
{
Guard.AgainstNullOrEmpty(tableName, nameof(tableName));
Guard.AgainstNullOrEmpty(schema, nameof(schema));
TableName = tableName;
Schema = schema;
if (sanitize)
{
TableName = SqlExtensions.Sanitize(TableName);
Schema = SqlExtensions.Sanitize(Schema);
}
FullTableName = $"{Schema}.{TableName}";
}
private async Task <SqlItem> CreateSqlItemAsync(SqlItem parentItem, string name, CreateOptions options = null)
{
if (parentItem == null)
{
throw new ArgumentNullException(nameof(parentItem));
}
if (name == null)
{
throw new ArgumentNullException(nameof(name));
}
Log("Parent: " + parentItem.Id + " '" + parentItem.Name + "' name: '" + name + "'");
options ??= new CreateOptions();
// if a race condition occurred, it may already exists
var item = await GetSqlItemAsync(parentItem.Id, name).ConfigureAwait(false);
if (item == null)
{
var id = Guid.NewGuid();
var parameters = new
{
id,
parentId = parentItem.Id,
name,
attributes = options.Attributes,
now = DateTime.UtcNow.RemoveMilliseconds()
};
await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, "INSERT INTO Item (Id, ParentId, Name, LastAccessTimeUtc, CreationTimeUtc, LastWriteTimeUtc, Attributes) VALUES (@id, @parentId, @name, @now, @now, @now, @attributes)", parameters, Logger).ConfigureAwait(false);
item = await GetSqlItemAsync(id).ConfigureAwait(false);
}
if (!item.IsFolder && options.InputStream != null)
{
await WriteAsync(item, options.InputStream).ConfigureAwait(false);
// refresh
item = await GetSqlItemAsync(item.Id).ConfigureAwait(false);
}
if (!IsTempFile(name))
{
AddEvent(item.Id, item.ParentId, WatcherChangeTypes.Created);
AddEvent(item.ParentId, (item.Parent?.ParentId).GetValueOrDefault(), WatcherChangeTypes.Changed);
}
return(item);
}
public async Task <bool> DeleteAsync(SqlItem item, DeleteOptions options = null)
{
if (item == null)
{
throw new ArgumentNullException(nameof(item));
}
if (item.Id == Guid.Empty)
{
throw new UnauthorizedAccessException();
}
Log("Item : " + item.Id + " '" + item.Name);
string sql;
if (!item.IsFolder)
{
sql = "DELETE FROM Item WHERE Id = @id";
}
else // deleting a folder is always recursive
{
sql = @"
WITH ItemHierarchy (Id)
AS
(
SELECT Id FROM Item WHERE Id = @id
UNION ALL
SELECT i.Id FROM Item i INNER JOIN ItemHierarchy h ON i.ParentId = h.Id AND i.ParentId <> '00000000-0000-0000-0000-000000000000'
)
DELETE Item FROM ItemHierarchy JOIN Item ON Item.Id = ItemHierarchy.Id";
}
var parent = item.Parent; // get parent before delete
var deleted = await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, sql, new { id = item.Id }, Logger).ConfigureAwait(false) != 0;
if (deleted)
{
if (!IsTempFile(item.Name))
{
AddEvent(item.Id, item.ParentId, WatcherChangeTypes.Deleted);
if (parent != null)
{
AddEvent(parent.Id, parent.ParentId, WatcherChangeTypes.Changed);
}
}
}
return(deleted);
}
// https://codehollow.com/2017/02/azure-functions-time-trigger-cron-cheat-sheet/
public static async Task RunAsync([TimerTrigger("0 */1 * * * *")] TimerInfo myTimer, ILogger log, ExecutionContext context)
{
config = Configuration.InitializeConfiguration(config, context);
KeyVaultExtensions.InitializeConfiguration(config);
keyVaultClient = KeyVaultExtensions.GetInstance();
var tableStorageAccountConnectionString = await KeyVaultExtensions.GetSecretAsync(Constants.KeyVaultConstants.AzureWebjobsStorageKey);
cloudTableClient = CloudTableClientExtensions.InitializeCloudTableClientAsync(cloudTableClient, tableStorageAccountConnectionString);
var queueConnectionString = await KeyVaultExtensions.GetSecretAsync(Constants.KeyVaultConstants.ServiceBusConnectionString);
queueClient = QueueClientExtensions.InitializeQueueClient(queueClient, queueConnectionString, "participants");
uint.TryParse(config["CascadiaGamersGroupId"], out var extraLifeGroupId);
log.LogInformation($"Getting gamers: {DateTime.Now}");
var donorDriveClient = new DonorDriveClient();
var gamers = new List <ParticipantDto>(); //AutoMapper.Mapper.Map<List<ParticipantDto>>(await donorDriveClient.GetTeamParticipantsAsync(extraLifeGroupId));
log.LogInformation($"Retrieved: {gamers.Count()} gamers.");
var participants = AutoMapper.Mapper.Map <List <ParticipantTableEntity> >(gamers);
await TableStorageExtensions <ParticipantTableEntity> .WriteToTableStorageAsync(cloudTableClient, participants,
"CascadiaGamersParticipants");
log.LogInformation($"Wrote to Table Storage values for: {gamers.Count()} participants.");
await QueueExtensions <ParticipantTableEntity> .WriteToQueueAsync(queueClient, participants,
"parcicipants");
log.LogInformation($"Wrote {gamers.Count()} participants to Service Bus to check for new donations.");
log.LogInformation($"Writing to Sql Server values for: {gamers.Count()} participants.");
var sqlConnectionString = await KeyVaultExtensions.GetSecretAsync(Constants.KeyVaultConstants.SqlConnectionString);
await SqlExtensions <ParticipantTableEntity> .WriteParticipantsToSqlAsync(participants, sqlConnectionString);
log.LogInformation($"Wrote to Sql Server values for: {gamers.Count()} participants.");
}
public static async Task <ImmutableList <Prize> > Run([HttpTrigger(AuthorizationLevel.Function, "get")]
HttpRequest req,
ILogger log,
ExecutionContext context)
{
log.LogInformation("GetWinners HTTP trigger function processed a request.");
config = Configuration.InitializeConfiguration(config, context);
KeyVaultExtensions.InitializeConfiguration(config);
keyVaultClient = KeyVaultExtensions.GetInstance();
var sqlConnectionString = await KeyVaultExtensions.GetSecretAsync(Constants.KeyVaultConstants.SqlConnectionString);
var prizes = await SqlExtensions <Prize> .GetWinnersFromSqlAsync(sqlConnectionString);
var builder = ImmutableList.CreateBuilder <Prize>();
builder.AddRange(prizes);
return(builder.ToImmutableList());
}
public DataSet ExecuteQueryDatasetDetailId(string connectionString, DataSetSchemaDefinition dataSetSchemaDefinition, int datasetdetailId)
{
var sql = String.Format("SELECT {0} FROM {1} WHERE DatasetDetailId = @datasetDetailId",
dataSetSchemaDefinition.ColumnSelectList,
dataSetSchemaDefinition.TableName);
var parameters = new List <SqlParameter>
{
new SqlParameter
{
ParameterName = "@datasetDetailId",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Input,
Value = datasetdetailId
}
};
var data = SqlExtensions.ExecuteQuery(connectionString, sql, parameters.ToArray());
return(data);
}
public PagedResult <object> GetPaged(int pageNumber, int pageSize, LambdaExpression whereClause, LambdaExpression orderBy, SortDirection orderDirection, bool fireEvents = true)
{
var query = new Sql($"SELECT * FROM {_collection.EntityType.GetTableName()}");
// Where
if (whereClause != null)
{
query.Where(_collection.EntityType, whereClause, SyntaxProvider);
}
else
{
query.Where(" 1 = 1");
}
if (_collection.DeletedProperty != null)
{
query.Append($" AND {_collection.DeletedProperty.GetColumnName()} = 0");
}
// Order by
LambdaExpression orderByExp = orderBy ?? _collection.SortProperty;
if (orderByExp != null)
{
if (orderDirection == SortDirection.Ascending)
{
SqlExtensions.OrderBy(query, _collection.EntityType, orderByExp, SyntaxProvider);
}
else
{
SqlExtensions.OrderByDescending(query, _collection.EntityType, orderByExp, SyntaxProvider);
}
}
var result = Db.Page(_collection.EntityType, pageNumber, pageSize, query);
return(new PagedResult <object>(result.TotalItems, pageNumber, pageSize)
{
Items = result.Items
});
}
public async IAsyncEnumerable <SqlItem> EnumerateAsync(SqlItem parentItem, EnumerateOptions options = null)
{
options ??= new EnumerateOptions();
string and;
if (options.IncludeFiles)
{
if (options.IncludeFolders)
{
and = null;
}
else
{
and = " AND (Attributes & " + (int)FileAttributes.Directory + ") = 0";
}
}
else
{
if (options.IncludeFolders)
{
and = " AND (Attributes & " + (int)FileAttributes.Directory + ") <> 0";
}
else
{
yield break;
}
}
if (!options.IncludeHidden)
{
and += " AND (Attributes & " + (int)FileAttributes.Hidden + ") = 0";
}
using (var reader = await SqlExtensions.ExecuteReaderAsync(ConnectionString, "SELECT Id, ParentId, Name, LastAccessTimeUtc, CreationTimeUtc, LastWriteTimeUtc, Attributes, DATALENGTH(Data) AS Length FROM Item WHERE ParentId = @id AND Id <> '00000000-0000-0000-0000-000000000000'" + and, new { id = parentItem.Id }, Logger).ConfigureAwait(false))
{
while (reader.Read())
{
yield return(NewItem(reader));
}
}
}
public void SendEvents()
{
var events = Interlocked.Exchange(ref _events, new ConcurrentDictionary <string, Event>());
Task.Run(async() =>
{
foreach (var evt in events.Values.OrderBy(v => v.CreationTimeUtc))
{
if (evt.OldName == null)
{
await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, "INSERT INTO Change (Id, ItemId, ParentId, Type, CreationTimeUtc) VALUES (@id, @itemId, @parentId, @type, @creationTimeUtc)", new { id = evt.Id, itemId = evt.ItemId, parentId = evt.ParentId, type = evt.Type, creationTimeUtc = evt.CreationTimeUtc }).ConfigureAwait(false);
}
else
{
await SqlExtensions.ExecuteNonQueryAsync(ConnectionString, "INSERT INTO Change (Id, ItemId, ParentId, Type, CreationTimeUtc, OldName) VALUES (@id, @itemId, @parentId, @type, @creationTimeUtc, @oldName)", new { id = evt.Id, itemId = evt.ItemId, parentId = evt.ParentId, type = evt.Type, creationTimeUtc = evt.CreationTimeUtc, oldName = evt.OldName }).ConfigureAwait(false);
}
Events.Change(evt);
}
});
}
public void UpdateCounters_Queued(string email, long timeNow, int howMany, IMyLog log)
{
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[LastMsgQueuedUtc]", SqlExtensions.SqlDateUtcNow().ToString());
cmd.AddInc("[NoOfQueuedMessages]", howMany);
cmd.AddDec("[RemainingMessages]", howMany);
cmd.Add("[AccountStatus]", String.Format("CASE WHEN {0}={1} THEN {2} ELSE {0} END",
"[AccountStatus]",
Data_AppUserFile.eUserStatus.verified_welcome_No_sent.ToIntString(),
Data_AppUserFile.eUserStatus.verified_welcome_queued.ToIntString()));
string strCmd = cmd.GetSql_Update(string.Format("WHERE [Email] like '{0}'", email));
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("StoreNew: *** unexpected RecordsAffected != 1");
}
}
}
public IEnumerable <object> GetAll(bool fireEvents = true)
{
var query = new Sql($"SELECT * FROM {_collection.EntityType.GetTableName()}");
if (_collection.DeletedProperty != null)
{
query.Append($"WHERE {_collection.DeletedProperty.GetColumnName()} = 0");
}
if (_collection.SortProperty != null)
{
if (_collection.SortDirection == SortDirection.Ascending)
{
SqlExtensions.OrderBy(query, _collection.EntityType, _collection.SortProperty, SyntaxProvider);
}
else
{
SqlExtensions.OrderByDescending(query, _collection.EntityType, _collection.SortProperty, SyntaxProvider);
}
}
return(Db.Fetch(_collection.EntityType, query));
}
public void Table_name_and_schema_should_be_quoted()
{
Assert.Equal("[MyEndpoint]", SqlExtensions.Sanitize("MyEndpoint"));
Assert.Equal("[MyEndpoint]]; SOME OTHER SQL;--]", SqlExtensions.Sanitize("MyEndpoint]; SOME OTHER SQL;--"));
}
public DataSet ExecuteQueryVisualiseSchema(VisualSchemaCriteria criteria)
{
var queryExecute = BuildVisualiseSqlQuery(criteria.Filters, criteria.Schema, criteria.ChartType, criteria.XAxisColumn, criteria.XAxisType, criteria.XAxisDateFormat, criteria.YAxisColumn, criteria.YAxisAggregate, criteria.PageNum, criteria.PageSize);
return(SqlExtensions.ExecuteQuery(criteria.DbConnectionString, queryExecute.Sql, queryExecute.Params));
}
