public void UpdateTemplateControls(TemplateControlCollection templateControls)
{
const string updateQuery = @"
UPDATE
[Cerberus.TemplateEngine.TemplateControl]
SET
Content = @Content{0},
FriendlyName = @FriendlyName{0},
VisualProperties = @VisualProperties{0},
Class = @Class{0}
WHERE
TemplateControlId = @TemplateControlId{0};" ;
var command = SqlDbAccess.CreateTextCommand();
var sb = new StringBuilder();
var counter = 0;
foreach (var templateControl in templateControls)
{
sb.AppendFormat(updateQuery, counter);
SqlDbAccess.AddParameter(command, string.Format("@Content{0}", counter), SqlDbType.NVarChar, templateControl.Content);
SqlDbAccess.AddParameter(command, string.Format("@FriendlyName{0}", counter), SqlDbType.NVarChar, templateControl.FriendlyName, 50);
SqlDbAccess.AddParameter(command, string.Format("@VisualProperties{0}", counter), SqlDbType.NVarChar, templateControl.VisualProperties);
SqlDbAccess.AddParameter(command, string.Format("@TemplateControlId{0}", counter), SqlDbType.Int, templateControl.Id);
SqlDbAccess.AddParameter(command, string.Format("@Class{0}", counter), SqlDbType.NVarChar, templateControl.Class);
counter++;
}
command.CommandText = sb.ToString();
SqlDbAccess.ExecuteNonQuery(command);
}
public TemplateControlCollection GetControls(int templateId, int documentId, int documentTypeId)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
SELECT
TC.TemplateControlId,
TC.FriendlyName,
IsNull(TCC.Content, TC.Content) AS Content,
TC.Content AS DefaultContent,
TC.VisualProperties,
TC.CreationGUID,
TC.Class,
CP.Name as ControlName,
CP.ControlType,
CP.Category
FROM
[Cerberus.TemplateEngine.TemplateControl] TC
JOIN [Cerberus.TemplateEngine.ControlPlugin] CP ON TC.ControlPluginId = CP.ControlPluginId
LEFT JOIN [Cerberus.TemplateEngine.TemplateControlContent] TCC ON TCC.TemplateControlId = TC.TemplateControlId
AND TCC.DocumentId=@DocumentId
AND TCC.DocumentTypeId=@DocumentTypeId
WHERE
TemplateId = @TemplateId" ;
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, templateId);
SqlDbAccess.AddParameter(command, "@DocumentId", SqlDbType.Int, documentId);
SqlDbAccess.AddParameter(command, "@DocumentTypeId", SqlDbType.Int, documentTypeId);
return(TemplateControlCollection.CreateFromData(SqlDbAccess.ExecuteSelect(command)));
}
public void UpdateResolutions(ResolutionCollection resolutions)
{
const string updateQuery = @"
UPDATE
[Cerberus.TemplateEngine.Resolution]
SET
Width = @Width{0}
WHERE
ResolutionId = @ResolutionId{0}" ;
var command = SqlDbAccess.CreateTextCommand();
var sb = new StringBuilder();
var counter = 0;
foreach (var resolution in resolutions)
{
sb.AppendFormat(updateQuery, counter);
SqlDbAccess.AddParameter(command, string.Format("@ResolutionId{0}", counter), SqlDbType.Int, resolution.Id);
SqlDbAccess.AddParameter(command, string.Format("@Width{0}", counter), SqlDbType.Int, resolution.ResolutionValue);
this.UpdateResolutionValues(resolution.Id, resolution.TemplateControlVisualProperties);
counter++;
}
command.CommandText = sb.ToString();
SqlDbAccess.ExecuteNonQuery(command);
}
public void AddResolutions(int templateId, ResolutionCollection resolutions)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
INSERT INTO
[Cerberus.TemplateEngine.Resolution]
(
TemplateId,
Width
)
VALUES
(
@TemplateId,
@Width
);
SELECT CAST(SCOPE_IDENTITY() AS INT);" ;
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, templateId);
var widthParameter = SqlDbAccess.AddParameter(command, "@Width", SqlDbType.Int, 0);
foreach (var resolution in resolutions)
{
widthParameter.Value = resolution.ResolutionValue;
resolution.Id = SqlDbAccess.ExecuteScalar <int>(command);
this.UpdateResolutionValues(resolution.Id, resolution.TemplateControlVisualProperties);
}
}
public void ResetTemplateControlCreationGUIDs(int templateId)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = "UPDATE [Cerberus.TemplateEngine.TemplateControl] SET CreationGUID=NULL WHERE TemplateId = @TemplateId";
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, templateId);
SqlDbAccess.ExecuteNonQuery(command);
}
public void UpdateControlContent(int documentId, int documentTypeId, int templateId, TemplateControlCollection templateControls)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
DELETE
[Cerberus.TemplateEngine.TemplateControlContent]
FROM
[Cerberus.TemplateEngine.TemplateControlContent] TCC
JOIN [Cerberus.TemplateEngine.TemplateControl] TC ON TC.TemplateControlId=TCC.TemplateControlId
AND TCC.DocumentId=@DocumentId
AND TCC.DocumentTypeId=@DocumentTypeId" ;
SqlDbAccess.AddParameter(command, "@DocumentId", SqlDbType.Int, documentId);
SqlDbAccess.AddParameter(command, "@DocumentTypeId", SqlDbType.Int, documentTypeId);
SqlDbAccess.ExecuteNonQuery(command);
command.CommandText = string.Format(@"
INSERT INTO [Cerberus.TemplateEngine.TemplateControlContent]
(
DocumentId,
DocumentTypeId,
TemplateControlId,
Content
)
VALUES
(
{0},
{1},
@TemplateControlId,
@Content
);" ,
documentId,
documentTypeId);
var templateControlIdParameter = SqlDbAccess.AddParameter(command, "@TemplateControlId", SqlDbType.Int, 0);
var contentParameter = SqlDbAccess.AddParameter(command, "@Content", SqlDbType.NText, string.Empty);
foreach (var templateControl in templateControls)
{
templateControlIdParameter.Value = templateControl.Id;
contentParameter.Value = templateControl.Content;
SqlDbAccess.ExecuteNonQuery(command);
}
command.CommandText = @"
DELETE
[Cerberus.TemplateEngine.TemplateControlContent]
FROM
[Cerberus.TemplateEngine.TemplateControlContent] TCC
JOIN [Cerberus.TemplateEngine.TemplateControl] TC ON TC.TemplateControlId=TCC.TemplateControlId
AND TCC.DocumentId=@DocumentId
AND TCC.DocumentTypeId=@DocumentTypeId
AND TC.Content = TCC.Content" ;
SqlDbAccess.ExecuteNonQuery(command);
}
public bool RemoveTemplate(int templateId)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
DELETE FROM
[Cerberus.TemplateEngine.Template]
WHERE
TemplateId = @TemplateId" ;
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, templateId);
return(SqlDbAccess.ExecuteNonQuery(command) > 0);
}
/// <summary>
///
/// </summary>
public void AddTemplateControls(int templateId, TemplateControlCollection templateControls)
{
const string insertQuery = @"
INSERT INTO
[Cerberus.TemplateEngine.TemplateControl]
(
ControlPluginId,
TemplateId,
FriendlyName,
Content,
VisualProperties,
CreationGUID,
Class
)
VALUES
(
(SELECT ControlPluginId FROM [Cerberus.TemplateEngine.ControlPlugin] WHERE ControlType=@ControlType{0}),
@TemplateId,
@FriendlyName{0},
@Content{0},
@VisualProperties{0},
@CreationGUID{0},
@Class{0}
);" ;
var command = SqlDbAccess.CreateTextCommand();
var sb = new StringBuilder();
var counter = 0;
foreach (var templateControl in templateControls)
{
sb.AppendFormat(insertQuery, counter);
SqlDbAccess.AddParameter(command, string.Format("@ControlType{0}", counter), SqlDbType.NVarChar, templateControl.ControlType);
SqlDbAccess.AddParameter(command, string.Format("@FriendlyName{0}", counter), SqlDbType.NVarChar, templateControl.FriendlyName, 50);
SqlDbAccess.AddParameter(command, string.Format("@Content{0}", counter), SqlDbType.NText, templateControl.Content);
SqlDbAccess.AddParameter(command, string.Format("@VisualProperties{0}", counter), SqlDbType.NVarChar, templateControl.VisualProperties);
SqlDbAccess.AddParameter(command, string.Format("@CreationGUID{0}", counter), SqlDbType.NVarChar, templateControl.CreationGUID);
SqlDbAccess.AddParameter(command, string.Format("@Class{0}", counter), SqlDbType.NVarChar, templateControl.Class);
counter++;
}
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, templateId);
command.CommandText = sb.ToString();
SqlDbAccess.ExecuteNonQuery(command);
}
public bool RemoveTemplateControlContent(int documentId, int documentTypeId)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
DELETE FROM
[Cerberus.TemplateEngine.TemplateControlContent]
WHERE
DocumentId=@DocumentId
AND DocumentTypeId=@DocumentTypeId" ;
SqlDbAccess.AddParameter(command, "@DocumentId", SqlDbType.Int, documentId);
SqlDbAccess.AddParameter(command, "@DocumentTypeId", SqlDbType.Int, documentTypeId);
return(SqlDbAccess.ExecuteNonQuery(command) > 0);
}
public void RemoveTemplateControlsNotInCollection(int templateId, IEnumerable <int> templateControlIds)
{
var templateControlIdsAsString = templateControlIds.Count() > 0 ? string.Join(",", templateControlIds) : "-1";
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = string.Format(@"
DELETE FROM
[Cerberus.TemplateEngine.TemplateControl]
WHERE
TemplateId = @TemplateId
AND TemplateControlId NOT IN ({0})" ,
templateControlIdsAsString);
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, templateId);
SqlDbAccess.ExecuteSelect(command);
}
public int AddTemplate(Template template)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
INSERT INTO
[Cerberus.TemplateEngine.Template]
(
Name,
VisualProperties,
CreatedByUserId,
CreatedDate,
LastModifiedDate
)
VALUES
(
@Name,
@VisualProperties,
@CreatedByUserId,
GETDATE(),
GETDATE()
);
DECLARE @IDENTITY INT;
SELECT @IDENTITY=CAST(SCOPE_IDENTITY() AS INT);
INSERT INTO
[Cerberus.TemplateEngine.Resolution]
(
TemplateId
)
VALUES
(
@IDENTITY
);
SELECT @IDENTITY;" ;
SqlDbAccess.AddParameter(command, "@Name", SqlDbType.NVarChar, template.Name);
SqlDbAccess.AddParameter(command, "@CreatedByUserId", SqlDbType.Int, template.CreatedByUserId);
SqlDbAccess.AddParameter(command, "@VisualProperties", SqlDbType.NVarChar, template.VisualProperties);
return(SqlDbAccess.ExecuteScalar <int>(command));
}
public bool UpdateTemplate(Template template)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
UPDATE
[Cerberus.TemplateEngine.Template]
SET
Name = @Name,
VisualProperties = @VisualProperties,
LastModifiedDate = GETDATE()
WHERE
TemplateId = @TemplateId" ;
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, template.Id);
SqlDbAccess.AddParameter(command, "@Name", SqlDbType.NVarChar, template.Name);
SqlDbAccess.AddParameter(command, "@VisualProperties", SqlDbType.NVarChar, template.VisualProperties);
return(SqlDbAccess.ExecuteNonQuery(command) > 0);
}
public Template GetTemplate(int templateId)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
SELECT
TemplateId,
Name,
CreatedByUserId,
CreatedDate,
LastModifiedDate,
VisualProperties
FROM
[Cerberus.TemplateEngine.Template]
WHERE
TemplateId = @TemplateId" ;
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, templateId);
return(Template.CreateFromData(SqlDbAccess.ExecuteSelect(command).Rows[0]));
}
public ResolutionCollection GetResolutions(int templateId)
{
var command = SqlDbAccess.CreateTextCommand();
command.CommandText = @"
SELECT
R.ResolutionId,
R.Width,
TCR.TemplateControlId,
TCR.VisualProperties
FROM
[Cerberus.TemplateEngine.Resolution] R
LEFT JOIN [Cerberus.TemplateEngine.TemplateControlResolution] TCR ON R.ResolutionId=TCR.ResolutionId
WHERE
R.TemplateId = @TemplateId
ORDER BY
R.Width ASC" ;
SqlDbAccess.AddParameter(command, "@TemplateId", SqlDbType.Int, templateId);
return(ResolutionCollection.CreateFromData(SqlDbAccess.ExecuteSelect(command)));
}
public TemplateCollection GetTemplates(TemplateSearchParameters searchParameters)
{
var command = SqlDbAccess.CreateTextCommand();
var sb = new StringBuilder(@"
SELECT
TemplateId,
Name,
CreatedByUserId,
CreatedDate,
LastModifiedDate,
VisualProperties
FROM
[Cerberus.TemplateEngine.Template] " );
if (searchParameters.CreatedByUserId > 0)
{
sb.AppendLine(" WHERE CreatedByUserId = @CreatedByUserId ");
SqlDbAccess.AddParameter(command, "@CreatedByUserId", SqlDbType.Int, searchParameters.CreatedByUserId);
}
command.CommandText = sb.ToString();
return(TemplateCollection.CreateDataObjectCollection(SqlDbAccess.ExecuteSelect(command)));
}
private void UpdateResolutionValues(int resolutionId, Dictionary <int, string> resolutionValues)
{
const string insertForNewTemplateControlQuery = @"INSERT INTO @tmp (CreationGUID, ResolutionId, VisualProperties) VALUES (@CreationGUID{0}, @ResolutionId, @VisualProperties{0});";
const string insertForExistingTemplateControlQuery = @"
INSERT INTO
[Cerberus.TemplateEngine.TemplateControlResolution]
(
ResolutionId,
TemplateControlId,
VisualProperties
)
VALUES
(
@ResolutionId,
@TemplateControlId{0},
@VisualProperties{0}
)" ;
var command = SqlDbAccess.CreateTextCommand();
var sb = new StringBuilder(@"
DECLARE @tmp TABLE
(
CreationGUID NVARCHAR(10),
ResolutionId INT,
VisualProperties NVARCHAR(1024)
);
DELETE FROM [Cerberus.TemplateEngine.TemplateControlResolution]
WHERE
ResolutionId = @ResolutionId;
" );
var counter = 0;
foreach (var resolutionValue in resolutionValues)
{
if (resolutionValue.Key <= 0)
{
sb.AppendFormat(insertForNewTemplateControlQuery, counter);
SqlDbAccess.AddParameter(command, string.Format("@CreationGUID{0}", counter), SqlDbType.NVarChar, resolutionValue.Key.ToString());
}
else
{
sb.AppendFormat(insertForExistingTemplateControlQuery, counter);
SqlDbAccess.AddParameter(command, string.Format("@TemplateControlId{0}", counter), SqlDbType.Int, resolutionValue.Key);
}
SqlDbAccess.AddParameter(command, string.Format("@VisualProperties{0}", counter), SqlDbType.NVarChar, resolutionValue.Value);
counter++;
}
SqlDbAccess.AddParameter(command, "@ResolutionId", SqlDbType.Int, resolutionId);
sb.AppendFormat(@"
INSERT INTO
[Cerberus.TemplateEngine.TemplateControlResolution]
(
TemplateControlId,
ResolutionId,
VisualProperties
)
SELECT
TC.TemplateControlId,
@ResolutionId,
T.VisualProperties
FROM
[Cerberus.TemplateEngine.TemplateControl] TC
JOIN @tmp T ON TC.CreationGUID = T.CreationGUID;" );
command.CommandText = sb.ToString();
SqlDbAccess.ExecuteNonQuery(command);
}
