private void SavePage(Page page, bool local)
{
LoggerHelper.Info("Start");
try
{
StringBuilder query = new StringBuilder();
query.Append("DECLARE @PAGE_ID AS INT\n");
query.Append("DECLARE @TAB_ID AS INT\n");
query.Append("DECLARE @FIELD_ID AS INT\n");
query.Append("DECLARE @FILTER_ID AS INT\n");
query.Append("\n");
IDictionary <string, string> defaultVals = new Dictionary <string, string>();
defaultVals["UpdatedDate"] = "GETDATE()";
defaultVals["PageId"] = "@PAGE_ID";
defaultVals["TabId"] = "@TAB_ID";
defaultVals["FieldId"] = "@FIELD_ID";
if (String.IsNullOrEmpty(page.PageId)) //New Page
{
if (local)
{
query.Append("INSERT INTO [Page]([Name],[Title],[TableName],[UpdatedBy],[UpdatedDate],[ConnName]) VALUES(");
query.Append("'").Append(page.Name).Append("','").Append(page.Title).Append("','").Append(page.TableName).Append("','");
query.Append(page.UpdatedBy).Append("',GETDATE(),'").Append(page.ConnName).Append("')").Append('\n');
}
else
{
query.Append(GetQueryBuilder().BuildInsertQuery(page, "PageId", defaultVals)).Append('\n');
}
query.Append("SET @PAGE_ID = scope_identity()\n");
}
else
{
if (local)
{
query.Append("UPDATE [Page] SET [Name] = '").Append(page.Name).Append("', [Title] = '").Append(page.Title);
query.Append("', [TableName] = '").Append(page.TableName).Append("', [UpdatedBy] = '").Append(page.UpdatedBy);
query.Append("', [UpdatedDate] = GETDATE(), [ConnName] = '").Append(page.ConnName).Append("' ");
query.Append("WHERE PageId = ").Append(page.PageId).Append('\n');
}
else
{
query.Append(GetQueryBuilder().BuildUpdateQuery(page, "PageId", defaultVals)).Append('\n');
}
query.Append("SET @PAGE_ID = ").Append(page.PageId).Append("\n");
query.Append("DELETE FROM PageGridColumn where PageId = @PAGE_ID\n");
query.Append("DELETE FROM [PageFilterField] WHERE [FilterId] IN (SELECT [FilterId] FROM PageFilter WHERE [PageId] = @PAGE_ID)\n");
query.Append("DELETE FROM [PageFilter] WHERE [PageId] = @PAGE_ID\n");
}
foreach (PageTab tab in page.Tabs)
{
tab.UpdatedBy = page.UpdatedBy;
if (!"1".Equals(tab.UpdatedDate))
{
if (String.IsNullOrEmpty(tab.TabId))
{
query.Append(GetQueryBuilder().BuildInsertQuery(tab, "TabId", defaultVals)).Append("\n");
query.Append("SET @TAB_ID = scope_identity()\n");
}
else
{
query.Append(GetQueryBuilder().BuildUpdateQuery(tab, "TabId", defaultVals)).Append("\n");
query.Append("SET @TAB_ID = ").Append(tab.TabId).Append("\n");
}
}
foreach (PageField field in tab.Fields)
{
field.UpdatedBy = page.UpdatedBy;
if (!String.IsNullOrEmpty(field.FieldId) && "1".Equals(field.UpdatedDate))
{
query.Append("DELETE FROM PageField where FieldId = ").Append(field.FieldId);
}
else if (String.IsNullOrEmpty(field.FieldId))
{
query.Append(GetQueryBuilder().BuildInsertQuery(field, "FieldId", defaultVals)).Append('\n');
if (field.ColumnInfo != null && !"1".Equals(field.ColumnInfo.UpdatedDate))
{
query.Append("SET @FIELD_ID = scope_identity()\n");
}
}
else
{
query.Append(GetQueryBuilder().BuildUpdateQuery(field, "FieldId", defaultVals)).Append('\n');
if (field.ColumnInfo != null && !"1".Equals(field.ColumnInfo.UpdatedDate))
{
query.Append("SET @FIELD_ID = ").Append(field.FieldId).Append("\n");
}
}
if (field.ColumnInfo != null && !"1".Equals(field.ColumnInfo.UpdatedDate))
{
PageGridColumn column = field.ColumnInfo;
column.UpdatedBy = page.UpdatedBy;
query.Append(GetQueryBuilder().BuildInsertQuery(column, "ColumnId", defaultVals)).Append('\n');
}
}
if (!String.IsNullOrEmpty(tab.TabId) && "1".Equals(tab.UpdatedDate))
{
query.Append("DELETE FROM PageTab where TabId = ").Append(tab.TabId).Append('\n');
}
}
if (page.Filter != null)
{
query.Append(GetQueryBuilder().BuildInsertQuery(page.Filter, "FilterId", defaultVals)).Append("\n");
query.Append("SET @FILTER_ID = scope_identity()\n");
defaultVals.Remove("FieldId");
defaultVals["FilterId"] = "@FILTER_ID";
foreach (PageFilterField field in page.Filter.Fields)
{
field.UpdatedBy = page.UpdatedBy;
query.Append(GetQueryBuilder().BuildInsertQuery(field, "FilterFieldId", defaultVals)).Append('\n');
}
}
LoggerHelper.Debug(query.ToString());
IList <String> queries = new List <string>();
queries.Add(query.ToString());
int id = GetQueryRunner().ExecuteTransaction(GetConnection(), queries, String.IsNullOrEmpty(page.PageId));
if (String.IsNullOrEmpty(page.PageId))
{
page.PageId = id.ToString();
LoggerHelper.Debug("pageid is null getting new id from db" + page.PageId);
}
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to save/update page.", e);
}
finally
{
LoggerHelper.Info("End");
}
}
/// <summary>
/// Convert the whole ResultSet into a Page objects.
/// </summary>
/// <param name="rs">The ResultSet to process.</param>
/// <returns>The Initialize Page objects</returns>
public T Handle(DbDataReader rs)
{
T result = default(T);
Page page = null;
bool firstTime = true;
PageTab prevTab = null;
while (rs.Read())
{
if (firstTime)
{
page = processor.ToBean <Page>(rs, typeof(Page));
firstTime = false;
}
string tabId = rs["TabId"].ToString();
if (!String.IsNullOrEmpty(tabId))
{
PageTab currentTab = prevTab;
if (prevTab == null || !tabId.Equals(prevTab.TabId))
{
currentTab = processor.ToBean <PageTab>(rs, typeof(PageTab));
page.Tabs.Add(currentTab);
}
string fieldId = rs["FieldId"].ToString();
if (!String.IsNullOrEmpty(fieldId))
{
PageField field = processor.ToBean <PageField>(rs, typeof(PageField));
string columnId = rs["ColumnId"].ToString();
if (!String.IsNullOrEmpty(columnId))
{
PageGridColumn column = processor.ToBean <PageGridColumn>(rs, typeof(PageGridColumn));
if (String.IsNullOrEmpty(column.ColumnName))
{
column.ColumnName = field.FieldName;
}
if (String.IsNullOrEmpty(column.ColumnLabel))
{
column.ColumnLabel = field.Label;
}
page.GridFields.Add(column);
}
currentTab.Fields.Add(field);
prevTab = currentTab;
}
}
}
result = (T)Convert.ChangeType(page, type);
return(result);
}