/// <summary>
/// Insert a new record about a server.
/// </summary>
/// <param name="model">The server.</param>
/// <returns>The server's identifier that has been inserted.</returns>
public Int64 InsertServer(ServerModel model)
{
logger.WriteTrace("Inserting {0} into the database...", model);
if (model.Id != 0)
{
throw new ArgumentException("Model's identifier should be 0. Now it is " + model.Id, "model");
}
logger.WriteTrace("Converting {0} into the entity...", model);
ServerEntity entity = serverConverter.Convert(model);
logger.WriteTrace("{0} has been converted. Generating sql command for {1}...", model, entity);
string sqlCommand = string.Format(insertStatement, sqlSettings.WiseQueueDefaultSchema, serverTableName, entity.Name, entity.Description, entity.HeartbeatLifetime);
logger.WriteTrace("The SqlCommand has been generated. Result: {0}", sqlCommand);
logger.WriteTrace("Executing sql command...");
using (IDbConnection connection = connectionFactory.CreateConnection())
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sqlCommand;
Int64 result = (Int64)command.ExecuteScalar();
logger.WriteTrace("The command has been executed. Entity identifier is {0}", result);
return(result);
}
}
}
public async Task AddAttachmentAsync(Attachment attachment)
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
var sqlQuery = @"
INSERT INTO Attachments(Id, ContentType, Created, FileName, MessageId, Size)
VALUES (@Id, @ContentType, @Created, @FileName, @MessageId, @Size);";
await connection.ExecuteScalarAsync(sqlQuery, attachment);
}
}
public async Task AddNotificationAsync(Notification notification)
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
var sqlQuery = @"
INSERT INTO Notifications(Id, IsRead, MessageId, ChannelId, MemberId)
VALUES (@Id, @IsRead, @MessageId, @ChannelId, @MemberId);";
await connection.ExecuteScalarAsync(sqlQuery, notification);
}
}
public async Task AddSettingsAsync(Settings settings)
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
var sqlQuery = @"
INSERT INTO Settings(Id, RawSettings, ChannelId)
VALUES (@Id, @RawSettings, @ChannelId);";
await connection.ExecuteScalarAsync(sqlQuery, settings);
}
}
/// <summary>
/// Get queue by its name.
/// </summary>
/// <param name="queueName">The queue name.</param>
/// <returns>The <see cref="QueueModel"/> instance.</returns>
public QueueModel GetQueueByName(string queueName)
{
string sqlCommand = string.Format("SELECT * FROM {0}.{1} WHERE [Name] = '{2}'", sqlSettings.WiseQueueDefaultSchema, queueTableName, queueName);
logger.WriteTrace("Executing sql command...");
using (IDbConnection connection = connectionFactory.CreateConnection())
{
using (IDbCommand command = connectionFactory.CreateCommand(connection))
{
command.CommandText = sqlCommand;
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
QueueEntity queueEntity = new QueueEntity
{
Id = (Int64)rdr["Id"],
Name = (string)rdr["Name"],
Description = (string)rdr["Description"]
};
QueueModel queueModel = queueConverter.Convert(queueEntity);
logger.WriteTrace("The command has been executed. Result = {0}", queueModel);
return(queueModel);
}
}
}
}
return(null); //TODO: It is not a good idea. We should return something like NullTaskEntity.
}
public async Task <List <Products> > GetProductList()
{
try
{
List <Products> productList = new List <Products>();
using (IDbConnection connection = _sqlConnectionFactory.CreateConnection())
{
productList = (await connection.QueryAsync <Products>(@"select * from Products;")).ToList();
}
return(productList);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <List <Employee> > GetEmployeeList()
{
try
{
List <Employee> employeeList = new List <Employee>();
using (IDbConnection connection = _sqlConnectionFactory.CreateConnection())
{
employeeList = (await connection.QueryAsync <Employee>(@"select * from Employee;")).ToList();
}
return(employeeList);
}
catch (Exception ex)
{
throw ex;
}
}
private SqlConnection NewOpenConnection()
{
IDbConnection connection = _sqlConnectionFactory.CreateConnection();
connection.Open();
return((SqlConnection)connection);
}
private IDbConnection NewOpenConnection()
{
IDbConnection connection = _connectionFactory.CreateConnection();
_openConnectionPolicy.Execute(() => connection.Open());
return(connection);
}
public async Task AddChannelMemberAsync(ChannelMembers channelMember)
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
var sqlQuery = @"
INSERT INTO ChannelMembers(ChannelId, MemberId, LastReadMessageId, IsMuted)
VALUES (@ChannelId, @MemberId, @LastReadMessageId, @IsMuted)";
await connection.ExecuteScalarAsync(sqlQuery, channelMember);
}
}
public void Insert(ExecutionError executionError)
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
connection.Execute(InsertSql, new
{
ReferenceType = executionError.ReferenceType.ToString(),
ReferenceId = executionError.ReferenceId,
Message = executionError.Message,
Active = executionError.Valid
});
}
}
public async Task <List <Client> > GetAllClientsAsync()
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
var sqlQuery = @"
SELECT Id, ClientConnectionId, LastActivity, LastClientActivity, Name, UserAgent, MemberId
FROM Clients";
var data = (await connection.QueryAsync <Client>(sqlQuery)).ToList();
return(data);
}
}
public void Insert(Player player)
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
connection.Execute(InsertSql, new
{
ApiId = player.ApiId,
Name = player.Name,
DateOfBirth = player.DateOfBirth,
Nationality = player.Nationality,
Position = player.Position
});
}
}
/// <summary>
/// Insert a task into the storage.
/// </summary>
/// <param name="taskModel">The <see cref="TaskModel"/> instance.</param>
/// <returns>The task's identifier.</returns>
/// <exception cref="ArgumentException">Task's identifier should be 0.</exception>
public Int64 InsertTask(TaskModel taskModel)
{
logger.WriteTrace("Inserting {0} into the database...", taskModel);
if (taskModel.Id != 0)
{
throw new ArgumentException("Task's identifier should be 0. Now it is " + taskModel.Id, "taskModel");
}
logger.WriteTrace("Converting {0} into the TaskEntity...", taskModel);
TaskEntity taskEntity = taskConverter.Convert(taskModel);
logger.WriteTrace("{0} has been converted. Generating sql command for {1}...", taskModel, taskEntity);
string instanceType = taskEntity.InstanceType;
string method = taskEntity.Method;
string parametersTypes = taskEntity.ParametersTypes;
string arguments = taskEntity.Arguments;
Int64 queueId = taskEntity.QueueId;
string sqlCommand = string.Format(insertStatement, sqlSettings.WiseQueueDefaultSchema, taskTableName, (short)taskEntity.TaskState, instanceType, method, parametersTypes, arguments, queueId);
logger.WriteTrace("The SqlCommand has been generated. Result: {0}", sqlCommand);
logger.WriteTrace("Executing sql command...");
using (IDbConnection connection = connectionFactory.CreateConnection())
{
using (IDbCommand command = connectionFactory.CreateCommand(connection))
{
command.CommandText = sqlCommand;
Int64 taskId = (Int64)command.ExecuteScalar();
logger.WriteTrace("The command has been executed. TaskId = {0}", taskId);
return(taskId);
}
}
}
public int Create(string name, DateTime dateOfBirth)
{
using (var connection = _connectionFactory.CreateConnection())
{
var customer = new Customer
{
DateOfBirth = dateOfBirth,
Name = name
};
customer.CustomerId = Spry.InsertInto <Customer>(CUSTOMER_TABLE)
.Value(_ => customer.Name)
.Value(_ => customer.DateOfBirth)
.OutputIdentity()
.ExecuteScalar <int>(connection);
return(customer.CustomerId);
}
}
public void Insert(Match match)
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
connection.Execute(InsertSql, new
{
ApiId = match.ApiId,
PlayerApiId = match.PlayerApiId,
Date = match.Date,
HomeTeam = match.HomeTeam,
AwayTeam = match.AwayTeam,
HomeTeamScore = match.HomeTeamScore,
AwayTeamScore = match.AwayTeamScore,
Winner = match.Winner,
CompetitionWinner = match.CompetitionWinner
});
}
}
public async Task <List <Message> > GetAllChannelMessagesAsync(Guid channelId)
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
var sqlQuery = @"
SELECT *
FROM Messages m
INNER JOIN Members me ON m.OwnerId = me.Id
WHERE m.ChannelId = @channelId
ORDER BY m.Created DESC";
var data = (await connection.QueryAsync <Message, Member, Message>(
sqlQuery,
(message, member) =>
{
message.Owner = member;
return(message);
},
new { channelId }))
.Distinct()
.ToList();
return(data);
}
}
public CatalogDBContext(ISqlConnectionFactory provider)
: base(provider.CreateConnection(), true)
{
}
/// <summary>
/// Get available task from the storage.
/// </summary>
/// <param name="specification">The <see cref="TaskRequestSpecification"/> instance.</param>
/// <param name="taskModels">List of <see cref="WiseQueue.Core.Common.Models.Tasks.TaskStateModel"/> instances if it has been found</param>
/// <returns>True if the list of TaskModel instances has been populated. Otherwise, false.</returns>
public bool TryGetAvailableTask(TaskRequestSpecification specification, out List <TaskModel> taskModels)
{
if (specification == null)
{
throw new ArgumentNullException("specification");
}
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("Declare @TempTable table ([Id] [bigint], ");
stringBuilder.Append("[QueueId] [bigint], ");
stringBuilder.Append("[ServerId] [bigint] NULL, ");
stringBuilder.Append("[State] [smallint], ");
stringBuilder.Append("[CompletedAt] [datetime] NULL, ");
stringBuilder.Append("[ExecuteAt] [datetime] NOT NULL, ");
stringBuilder.Append("[RepeatCrashCount] [int] NOT NULL, ");
stringBuilder.Append("[InstanceType] [nvarchar](4000), ");
stringBuilder.Append("[Method] [nvarchar](4000), ");
stringBuilder.Append("[ParametersTypes] [nvarchar](4000), ");
stringBuilder.AppendLine("[Arguments] [nvarchar](4000)); ");
stringBuilder.AppendFormat("UPDATE TOP ({0}) {1}.{2} ", specification.MaxTasks, sqlSettings.WiseQueueDefaultSchema, taskTableName);
stringBuilder.AppendFormat("SET State = {0}, ", (short)TaskStates.Pending);
stringBuilder.AppendFormat("ServerId = {0} ", specification.ServerId);
//stringBuilder.Append("RepeatCount = RepeatCount - 1 ");
stringBuilder.Append("OUTPUT inserted.* INTO @TempTable ");
stringBuilder.AppendFormat("Where (State = {0} ", (short)TaskStates.New);
stringBuilder.AppendFormat("OR ( (State = {0} OR State = {1}) AND [ServerId] IS NULL)) ", (short)TaskStates.Pending, (short)TaskStates.Running);
stringBuilder.AppendFormat("AND (QueueId = {0}) AND ([ExecuteAt] <= GETUTCDATE()) AND [RepeatCrashCount] > 0;", specification.QueueId);
stringBuilder.AppendLine();
stringBuilder.AppendLine("SELECT * FROM @TempTable");
using (IDbConnection connection = connectionFactory.CreateConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
{
using (IDbCommand command = connectionFactory.CreateCommand(connection, transaction))
{
command.CommandText = stringBuilder.ToString();
using (IDataReader rdr = command.ExecuteReader())
{
taskModels = new List <TaskModel>();
while (rdr.Read())
{
Int64 id = (Int64)rdr["Id"];
Int64 queueId = (Int64)rdr["QueueId"];
Int64 serverId = (Int64)rdr["ServerId"];
TaskStates taskState = (TaskStates)(short)rdr["State"];
int repeatCrashCount = (int)rdr["RepeatCrashCount"];
string typeDetails = (string)rdr["InstanceType"];
string methodDetails = (string)rdr["Method"];
string parameterDetails = (string)rdr["ParametersTypes"];
string argumentDetails = (string)rdr["Arguments"];
TaskEntity taskEntity = new TaskEntity
{
Id = id,
QueueId = queueId,
ServerId = serverId,
TaskState = taskState,
InstanceType = typeDetails,
Method = methodDetails,
ParametersTypes = parameterDetails,
Arguments = argumentDetails,
RepeatCrashCount = repeatCrashCount
};
TaskModel taskModel = taskConverter.Convert(taskEntity);
taskModels.Add(taskModel);
}
}
}
if (taskModels.Count > 0)
{
transaction.Commit();
return(true);
}
}
return(false);
}
}
public async Task <List <Member> > GetAllMembersAsync()
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
var sqlQuery = @"
SELECT Id, Email, IsAfk, IsBanned, LastActivity, LastNudged, Name, PhotoName, Role, SaasUserId, Status
FROM Members";
var data = (await connection.QueryAsync <Member>(sqlQuery)).ToList();
return(data);
}
}
public async Task <List <Channel> > GetAllChannelsAsync()
{
using (var connection = _sqlConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
var sqlQuery = @"
SELECT Id, Created, IsClosed, CreatorId, MembersCount, Updated, Name, Type, Description, WelcomeMessage, PhotoUrl
FROM Channels";
var data = (await connection.QueryAsync <Channel>(sqlQuery)).ToList();
return(data);
}
}
