/// <summary>
/// Inserts the integration service creation settings.
/// </summary>
/// <param name="dto">The dto.</param>
/// <exception cref="System.ArgumentNullException">dto</exception>
/// <exception cref="System.Data.DBConcurrencyException"></exception>
public void InsertIntegrationServiceCreationSettings(IntegrationServiceCreationSettingsDto dto)
{
const string CommandText = @"
INSERT INTO [dbo].[IntegrationServiceCreationSettings]
(
[ServiceId]
,[LastModifiedOn]
,[Namespace]
)
VALUES
(
@serviceId
,GETDATE()
,@namespace
);
SET @id = SCOPE_IDENTITY();";
if (dto == null)
throw new ArgumentNullException("dto");
using (var cmd = new SqlCommand(CommandText))
{
var idParam = cmd.Parameters.Add("@id", SqlDbType.Int);
idParam.Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@serviceId", dto.ServiceId);
cmd.Parameters.AddWithValue("@namespace", dto.Namespace);
var rowsAffected = Database.Execute(cmd);
if (rowsAffected == 0)
throw new DBConcurrencyException(ConcurencyException);
dto.Id = (int)idParam.Value;
}
}
/// <summary>
/// Updates the integration service creation settings.
/// </summary>
/// <param name="dto">The dto.</param>
/// <exception cref="System.ArgumentNullException">dto</exception>
/// <exception cref="System.Data.DBConcurrencyException"></exception>
public void UpdateIntegrationServiceCreationSettings(IntegrationServiceCreationSettingsDto dto)
{
const string CommandText = @"
UPDATE [dbo].[IntegrationServiceCreationSettings]
SET
[ServiceId] = @serviceId
,[LastModifiedOn] = GETDATE()
,[Namespace] = @namespace
WHERE [Id] = @id;";
if (dto == null)
throw new ArgumentNullException("dto");
using (var cmd = new SqlCommand(CommandText))
{
cmd.Parameters.AddWithValue("@id", dto.Id);
cmd.Parameters.AddWithValue("@serviceId", dto.ServiceId);
cmd.Parameters.AddWithValue("@namespace", dto.Namespace);
var rowsAffected = Database.Execute(cmd);
if (rowsAffected == 0)
throw new DBConcurrencyException(ConcurencyException);
}
}
/// <summary>
/// Reads the integration service exposed type fields.
/// </summary>
/// <param name="settingsDto">The settings dto.</param>
/// <param name="reader">The reader.</param>
private static void ReadIntegrationServiceExposedTypeFields(IntegrationServiceCreationSettingsDto settingsDto, IDataReader reader)
{
reader.NextResult();
int? typeId = null;
IntegrationServiceExposedTypeDto type = null;
while (reader.Read())
{
var fieldDto = new IntegrationServiceExposedTypeFieldDto
{
Id = reader.GetInt32(0),
TypeId = reader.GetInt32(1),
Guid = reader.GetGuid(2),
Name = reader.GetString(3),
Namespace = reader.GetString(4),
TypeGuid = reader.GetGuid(5),
AllowMultiple = reader.GetBoolean(6),
SerializeAsSequence = reader.GetBoolean(7),
IsNullable = reader.GetBoolean(8)
};
if (fieldDto.TypeId != typeId)
{
type = settingsDto.Types.First(t => t.Id == fieldDto.TypeId);
typeId = fieldDto.TypeId;
}
type.Fields.Add(fieldDto);
}
}
/// <summary>
/// Reads the integration service exposed types.
/// </summary>
/// <param name="settingsDto">The settings dto.</param>
/// <param name="reader">The reader.</param>
private static void ReadIntegrationServiceExposedTypes(IntegrationServiceCreationSettingsDto settingsDto, IDataReader reader)
{
reader.NextResult();
while (reader.Read())
{
var dto = new IntegrationServiceExposedTypeDto
{
Id = reader.GetInt32(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
Namespace = reader.GetString(3)
};
settingsDto.Types.Add(dto);
}
ReadIntegrationServiceExposedTypeFields(settingsDto, reader);
}
/// <summary>
/// Reads the integration service method parameters.
/// </summary>
/// <param name="settingsDto">The settings dto.</param>
/// <param name="reader">The reader.</param>
private static void ReadIntegrationServiceMethodParameters(IntegrationServiceCreationSettingsDto settingsDto, IDataReader reader)
{
reader.NextResult();
int? methodId = null;
IntegrationServiceMethodDto method = null;
while (reader.Read())
{
var parameterDto = new IntegrationServiceMethodParameterDto
{
Id = reader.GetInt32(0),
MethodId = reader.GetInt32(1),
Guid = reader.GetGuid(2),
Name = reader.GetString(3),
Namespace = reader.GetString(4),
TypeGuid = reader.GetGuid(5),
AllowMultiple = reader.GetBoolean(6),
SerializeAsSequence = reader.GetBoolean(7),
IsNullable = reader.GetBoolean(8)
};
if (parameterDto.MethodId != methodId)
{
method = settingsDto.Methods.First(m => m.Id == parameterDto.MethodId);
methodId = parameterDto.MethodId;
}
method.InputParameters.Add(parameterDto);
}
}
/// <summary>
/// Reads the integration service methods.
/// </summary>
/// <param name="settingsDto">The settings dto.</param>
/// <param name="reader">The reader.</param>
private static void ReadIntegrationServiceMethods(IntegrationServiceCreationSettingsDto settingsDto, SafeDataReader reader)
{
reader.NextResult();
while (reader.Read())
{
var dto = new IntegrationServiceMethodDto
{
Id = reader.GetInt32(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
Username = reader.GetString(3),
Password = reader.GetString(4),
CanInsert = reader.GetBoolean(5),
CanUpdate = reader.GetBoolean(6),
CanInsertOrUpdate = reader.GetBoolean(7),
CanRemove = reader.GetBoolean(8),
EndpointMapping = reader.GetString(9),
Namespace = reader.GetString(10),
CanSelect = reader.GetBoolean("CanSelect"),
ResultTypeGuid = reader.GetNullableGuid("ResultTypeGuid"),
ResultMapping = reader.GetString("ResultMapping"),
ReturnMode = reader.GetEnum("ReturnMode", IntegrationServiceMethodReturnMode.All),
GenerateIndexes = reader.GetBoolean("GenerateIndexes")
};
settingsDto.Methods.Add(dto);
}
ReadIntegrationServiceMethodParameters(settingsDto, reader);
}
/// <summary>
/// Fetches the integration service creation settings.
/// </summary>
/// <param name="serviceId">The service identifier.</param>
/// <returns>IntegrationServiceCreationSettingsDto.</returns>
public IntegrationServiceCreationSettingsDto FetchIntegrationServiceCreationSettings(int serviceId)
{
const string CommandText = @"
DECLARE @settingsId INT;
SELECT @settingsId = Id
FROM [dbo].[IntegrationServiceCreationSettings]
WHERE [ServiceId] = @serviceId;
SELECT
[Id]
,[Namespace]
FROM
[dbo].[IntegrationServiceCreationSettings]
WHERE [Id] = @settingsId;
-- Service Methods
DECLARE @Methods TABLE
(
[Id] INT
,[Guid] UNIQUEIDENTIFIER
,[Name] NVARCHAR(200)
,[Username] NVARCHAR(200)
,[Password] NVARCHAR(200)
,[CanInsert] BIT
,[CanUpdate] BIT
,[CanInsertOrUpdate] BIT
,[CanRemove] BIT
,[EndpointMapping] NVARCHAR(MAX)
,[Namespace] NVARCHAR(400)
,[CanSelect] BIT
,[ResultTypeGuid] UNIQUEIDENTIFIER
,[ResultMapping] NVARCHAR(MAX)
,[ReturnMode] NVARCHAR(50)
,[GenerateIndexes] BIT
)
INSERT INTO @Methods
SELECT
[Id]
,[Guid]
,[Name]
,[Username]
,[Password]
,[CanInsert]
,[CanUpdate]
,[CanInsertOrUpdate]
,[CanRemove]
,[EndpointMapping]
,[Namespace]
,[CanSelect]
,[ResultTypeGuid]
,[ResultMapping]
,[ReturnMode]
,[GenerateIndexes]
FROM
[dbo].[IntegrationServiceMethods]
WHERE [SettingsId] = @settingsId;
SELECT * FROM @Methods;
-- Service Method Parameters
SELECT
mp.[Id]
,mp.[MethodId]
,mp.[Guid]
,mp.[Name]
,mp.[Namespace]
,mp.[TypeGuid]
,mp.[AllowMultiple]
,mp.[SerializeAsSequence]
,mp.[IsNullable]
FROM
[dbo].[IntegrationServiceMethodParameters] mp
INNER JOIN @Methods m ON m.[Id] = mp.[MethodId]
ORDER BY mp.[MethodId];
-- Service Types
DECLARE @Types TABLE
(
[Id] INT
,[Guid] UNIQUEIDENTIFIER
,[Name] NVARCHAR(200)
,[Namespace] NVARCHAR(400)
)
INSERT INTO @Types
SELECT
[Id]
,[Guid]
,[Name]
,[Namespace]
FROM
[dbo].[IntegrationServiceExposedTypes]
WHERE [SettingsId] = @settingsId;
SELECT * FROM @Types;
-- Service Type Fields
SELECT
tf.[Id]
,tf.[TypeId]
,tf.[Guid]
,tf.[Name]
,tf.[Namespace]
,tf.[TypeGuid]
,tf.[AllowMultiple]
,tf.[SerializeAsSequence]
,tf.[IsNullable]
FROM
[dbo].[IntegrationServiceExposedTypeFields] tf
INNER JOIN @Types t ON t.[Id] = tf.[TypeId]
ORDER BY tf.[TypeId];";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
using (var cmd = new SqlCommand(CommandText, ctx.Connection))
{
cmd.Parameters.AddWithValue("@serviceId", serviceId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
var result = new IntegrationServiceCreationSettingsDto { Id = reader.GetInt32(0), Namespace = reader.GetString(1) };
ReadIntegrationServiceMethods(result, reader);
ReadIntegrationServiceExposedTypes(result, reader);
return result;
}
}
}
}
return null;
}