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 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);
}
internal static TemplateControlCollection CreateFromData(DataTable data)
{
var result = new TemplateControlCollection();
foreach (DataRow row in data.Rows)
{
result.Add(TemplateControl.CreateFromData(row));
}
return(result);
}
/// <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 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);
}
