/// <summary>
/// Updates the integration service scheduled call.
/// </summary>
/// <param name="dto">The dto.</param>
/// <exception cref="System.ArgumentNullException">dto</exception>
/// <exception cref="System.Data.DBConcurrencyException"></exception>
public void UpdateIntegrationServiceScheduledCall(IntegrationServiceUrlScheduledCallDto dto)
{
const string CommandText = @"
UPDATE [dbo].[IntegrationServiceScheduledCalls]
SET
[LastModifiedOn] = GETDATE()
,[IntegrationServiceGuid] = @integrationServiceGuid
,[ProcessName] = @processName
,[ItemId] = @itemId
,[CallCount] = @callCount
,[Status] = @status
WHERE [Id] = @id;
UPDATE [dbo].[IntegrationServiceUrlScheduledCalls]
SET
[Url] = @url
,[Data] = @data
,[HttpMethod] = @httpMethod
WHERE [ScheduledCallId] = @id;";
if (dto == null)
throw new ArgumentNullException("dto");
// Don't enlist this connection in current TransationScope.
// When this method is called from dynamic assemblies, it will fail if MS DTC is not enabled.
var csb = new SqlConnectionStringBuilder(Database.VeyronMeta) { Enlist = false };
using (var cn = new SqlConnection(csb.ConnectionString))
{
cn.Open();
using (var cmd = new SqlCommand(CommandText, cn))
{
cmd.Parameters.AddWithValue("@id", dto.Id);
cmd.Parameters.AddWithValue("@integrationServiceGuid", dto.IntegrationServiceGuid);
cmd.Parameters.AddWithValue("@processName", dto.ProcessName);
cmd.Parameters.AddWithValue("@itemId", dto.ItemId);
cmd.Parameters.AddWithValue("@callCount", dto.CallCount);
cmd.Parameters.AddWithValue("@status", dto.Status.ToString());
cmd.Parameters.AddWithValue("@url", AdoHelper.NullCheck(dto.Url));
cmd.Parameters.AddWithValue("@data", AdoHelper.NullCheck(dto.Data));
cmd.Parameters.AddWithValue("@httpMethod", dto.HttpMethod.ToString());
var rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
throw new DBConcurrencyException(ConcurencyException);
}
}
}
/// <summary>
/// Gets the integration service scheduled calls.
/// </summary>
/// <returns>IEnumerable{IntegrationServiceScheduledCallDto}.</returns>
public IEnumerable<IntegrationServiceScheduledCallDto> GetIntegrationServiceScheduledCalls()
{
const string CommandText = @"
SELECT
sc.[Id]
,sc.[CreationDate]
,sc.[IntegrationServiceGuid]
,sc.[ProcessName]
,sc.[ItemId]
,sc.[CallCount]
,sc.[Status]
,wm.[ServiceDescriptionId]
,wm.[ContractTypeName]
,wm.[MethodName]
,wm.[Data]
,wm.[Options]
FROM
[dbo].[IntegrationServiceScheduledCalls] sc
INNER JOIN [dbo].[IntegrationServiceWebMethodScheduledCalls] wm ON wm.[ScheduledCallId] = sc.[Id]
WHERE sc.[Status] = 'Scheduled';
SELECT
sc.[Id]
,sc.[CreationDate]
,sc.[IntegrationServiceGuid]
,sc.[ProcessName]
,sc.[ItemId]
,sc.[CallCount]
,sc.[Status]
,url.[Url]
,url.[Data]
,url.[HttpMethod]
FROM
[dbo].[IntegrationServiceScheduledCalls] sc
INNER JOIN [dbo].[IntegrationServiceUrlScheduledCalls] url ON url.[ScheduledCallId] = sc.[Id]
WHERE sc.[Status] = 'Scheduled';";
var result = new List<IntegrationServiceScheduledCallDto>();
// Don't enlist this connection in current TransationScope.
// When this method is called from dynamic assemblies, it will fail if MS DTC is not enabled.
var csb = new SqlConnectionStringBuilder(Database.VeyronMeta) { Enlist = false };
using (var cn = new SqlConnection(csb.ConnectionString))
{
cn.Open();
using (var cmd = new SqlCommand(CommandText, cn))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new IntegrationServiceWebMethodScheduledCallDto
{
Id = reader.GetInt32(0),
CreationDate = reader.GetDateTime(1),
IntegrationServiceGuid = reader.GetGuid(2),
ProcessName = reader.GetString(3),
ItemId = reader.GetInt32(4),
CallCount = reader.GetInt32(5),
Status =
(ServiceCallStatus)
Enum.Parse(
typeof(ServiceCallStatus), reader.GetString(6), true),
ServiceDescriptionId = reader.GetInt32(7),
ContractTypeName = reader.GetString(8),
MethodName = reader.GetString(9),
Data = reader.GetString(10),
Options = !reader.IsDBNull(11) ? reader.GetString(11) : null
};
result.Add(dto);
}
reader.NextResult();
while (reader.Read())
{
var dto = new IntegrationServiceUrlScheduledCallDto
{
Id = reader.GetInt32(0),
CreationDate = reader.GetDateTime(1),
IntegrationServiceGuid = reader.GetGuid(2),
ProcessName = reader.GetString(3),
ItemId = reader.GetInt32(4),
CallCount = reader.GetInt32(5),
Status =
(ServiceCallStatus)
Enum.Parse(typeof(ServiceCallStatus), reader.GetString(6), true),
Url = reader.GetString(7),
Data = reader.GetString(8),
HttpMethod =
(UrlServiceCallMethod)
Enum.Parse(
typeof(UrlServiceCallMethod), reader.GetString(9), true)
};
result.Add(dto);
}
}
}
}
return result;
}
/// <summary>
/// Inserts the integration service scheduled call.
/// </summary>
/// <param name="dto">The dto.</param>
/// <exception cref="System.ArgumentNullException">dto</exception>
public void InsertIntegrationServiceScheduledCall(IntegrationServiceUrlScheduledCallDto dto)
{
const string CommandText = @"
INSERT INTO [dbo].[IntegrationServiceScheduledCalls]
(
[LastModifiedOn]
,[CreationDate]
,[IntegrationServiceGuid]
,[ProcessName]
,[ItemId]
,[CallCount]
,[Status]
)
VALUES
(
GETDATE()
,GETDATE()
,@integrationServiceGuid
,@processName
,@itemId
,@callCount
,@status
);
SET @id = SCOPE_IDENTITY();
INSERT INTO [dbo].[IntegrationServiceUrlScheduledCalls]
(
[ScheduledCallId]
,[Url]
,[Data]
,[HttpMethod]
)
VALUES
(
@id
,@url
,@data
,@httpMethod
);";
if (dto == null)
throw new ArgumentNullException("dto");
// Don't enlist this connection in current TransationScope.
// When this method is called from dynamic assemblies, it will fail if MS DTC is not enabled.
var csb = new SqlConnectionStringBuilder(Database.VeyronMeta) { Enlist = false };
using (var cn = new SqlConnection(csb.ConnectionString))
{
cn.Open();
using (var cmd = new SqlCommand(CommandText, cn))
{
var idParam = cmd.Parameters.Add("@id", SqlDbType.Int);
idParam.Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@integrationServiceGuid", dto.IntegrationServiceGuid);
cmd.Parameters.AddWithValue("@processName", dto.ProcessName);
cmd.Parameters.AddWithValue("@itemId", dto.ItemId);
cmd.Parameters.AddWithValue("@callCount", dto.CallCount);
cmd.Parameters.AddWithValue("@status", dto.Status.ToString());
cmd.Parameters.AddWithValue("@url", AdoHelper.NullCheck(dto.Url));
cmd.Parameters.AddWithValue("@data", AdoHelper.NullCheck(dto.Data));
cmd.Parameters.AddWithValue("@httpMethod", dto.HttpMethod.ToString());
cmd.ExecuteNonQuery();
dto.Id = (int)idParam.Value;
}
}
}
