public IActionResult BaseViewManagerSQL(string id)
{
ViewBag.Id = id;
namespaceId = id;
BaseViewSQLModel model = new BaseViewSQLModel();
DataTable dt = SynapseHelpers.GetBaseviewDTByID(id);
model.SQL = dt.Rows[0]["baseviewsqlstatement"].ToString();
model.Summary = SynapseHelpers.GetBaseViewNameAndNamespaceFromID(id);
model.NamespaceId = SynapseHelpers.GetBaseviewNameSpaceIDFromBaseViewID(id);
model.NextOrdinalPosition = SynapseHelpers.GetNextOrdinalPositionFromID(id);
model.ViewName = SynapseHelpers.GetBaseviewNameFromID(id);
model.NamespaceName = SynapseHelpers.GetBaseviewNameSpaceNameFromBaseViewID(id);
model.BaseViewComments = SynapseHelpers.GetBaseviewCommentsFromBaseViewID(id);
return(View(model));
}
public IActionResult BaseViewManagerSQLValidate(BaseViewSQLModel model, string command)
{
if (command == "Re-Validate")
{
string tmpViewName = Guid.NewGuid().ToString("N");
//Check if we can create a temporary view using the supplied SQL Statement
string sqlValidate = "CREATE VIEW baseviewtemp." + model.NamespaceName + "_" + tmpViewName + " AS " + model.SQL + ";";
var paramListvalidate = new List <KeyValuePair <string, string> >()
{
};
try
{
DataServices.executeSQLStatement(sqlValidate, paramListvalidate);
}
catch (Exception ex)
{
return(Json("Error creating view: " + ex.Message));
}
return(Json(command));
}
string sqlDrop = @"SELECT listsettings.dropbaseview(
@p_baseview_id,
@p_baseviewname
)";
var paramListDrop = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("p_baseview_id", namespaceId),
new KeyValuePair <string, string>("p_baseviewname", model.Summary)
};
DataServices.ExcecuteNonQueryFromSQL(sqlDrop, paramListDrop);
//Recreate the baseview
//string tmpViewName = Guid.NewGuid().ToString("N");
//Check if we can create a temporary view using the supplied SQL Statement
string sqlCreate = "CREATE VIEW baseviewcore." + model.NamespaceName + "_" + model.ViewName + " AS " + model.SQL + ";";
var paramListCreate = new List <KeyValuePair <string, string> >()
{
};
try
{
DataServices.executeSQLStatement(sqlCreate, paramListCreate);
}
catch (Exception ex)
{
return(Json("Error creating view: " + ex.Message));
}
string baseviewid = namespaceId;
string sql = "SELECT listsettings.recreatebaseview(@p_baseviewnamespaceid, @p_baseviewnamespace, @p_baseviewname, @p_baseviewdescription, @p_baseviewsqlstatement, @p_username, @baseviewid);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("p_baseviewnamespaceid", model.NamespaceId),
new KeyValuePair <string, string>("p_baseviewnamespace", model.NamespaceName),
new KeyValuePair <string, string>("p_baseviewname", model.ViewName),
new KeyValuePair <string, string>("p_baseviewdescription", model.BaseViewComments == null ? "" : model.BaseViewComments),
new KeyValuePair <string, string>("p_baseviewsqlstatement", model.SQL),
new KeyValuePair <string, string>("p_username", HttpContext.Session.GetString(SynapseSession.FullName)),
new KeyValuePair <string, string>("baseviewid", baseviewid),
};
DataSet ds = new DataSet();
try
{
ds = DataServices.DataSetFromSQL(sql, paramList);
}
catch (Exception ex)
{
return(Json("Error creating view: " + ex.Message));
}
DataTable dt = ds.Tables[0];
string newGuid = dt.Rows[0][0].ToString();
return(Json(command + "|" + newGuid));
}
