public void DeleteDataSet(string datasetname) { var db = new PostgreSQLDBInterface(new Settings().warehousedb); db.Execute(" drop materialized view \"" + datasetname + "\""); }
public string CreateDataSet() { try { var data = Request.Form["datasetdata"]; var moduleid = Request.Form["module"].toInt(); var datasetobject = JsonConvert.DeserializeObject <List <DatasetObject> >(data); bool isduplicateTable = datasetobject.GroupBy(d => d.TableName).Count() < datasetobject.Count; if (isduplicateTable) { return("1"); } StringBuilder sb = new StringBuilder(); StringBuilder sb2 = new StringBuilder(); int tcounter = 0; int ccounter = 0; string[] tags = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" }; foreach (DatasetObject tbitem in datasetobject) { if (tcounter == 0) //handling the first table { sb2.Append(" from \"" + tbitem.TableName + "\" " + tags[0]); } else { var pretag = tags[tcounter - 1]; //setting previous table values var pretable = datasetobject[tcounter - 1]; sb2.Append(" inner join \"" + tbitem.TableName + "\" " + tags[tcounter] + " on (" + pretag + ".\"" + pretable.NxtTable + "\"=" + tags[tcounter] + ".\"" + tbitem.PreTable + "\") "); } ccounter = 0; if (tbitem.Columns.isNull()) { sb.Append(tags[tcounter] + ".*"); } else { foreach (string column in tbitem.Columns) { ccounter++; sb.Append(tags[tcounter] + ".\"" + column + "\""); if (ccounter < tbitem.Columns.Length) { sb.Append(","); } } } tcounter++; if (tcounter < datasetobject.Count) { sb.Append(","); } } string cols = sb.ToString(); string tabs = sb2.ToString(); var db = new InlaksBIContext(); var script = "CREATE MATERIALIZED VIEW public." + datasetobject[0].DataSetName + " AS Select "; var dataset = new DataSetDetail(); dataset.Script = script + cols + tabs; dataset.DataSetName = datasetobject[0].DataSetName; DBInterface dbinterface = new PostgreSQLDBInterface(new Settings().warehousedb); int resp = dbinterface.Execute(dataset.Script); var sql = "select * from \"" + dataset.DataSetName + "\" LIMIT 1"; var dt = dbinterface.getData(sql); dataset.Module = db.Modules.Where(m => m.ModuleID == moduleid).FirstOrDefault(); db.DataSets.Add(dataset); db.SaveChanges(); if (dt.Rows.Count > 0) { return("0"); } else { return("DataSet Created sucessfully but has no data"); } } catch (Exception e) { return(e.Message); } }