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 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 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 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);
}
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);
}
