void _Query(ProjectFlx.Schema.SchemaQueryType query) { if(query.paging == null) query.paging = new ProjectFlx.Schema.paging(); ProjectFlx.Schema.results rslts; _Projresults.results.Add(rslts = new ProjectFlx.Schema.results()); rslts.schema.Add(query); rslts.name = query.name; SqlCommand cmd = new SqlCommand(); if (_database.State != ConnectionState.Open) { _database.InitializeConnection(); _database.Open(); } cmd.Connection = _database.Connection; switch (query.command.type) { case ProjectFlx.Schema.commandType.StoredProcedure: cmd.CommandText = ProjectFlx.Schema.Helper.FlattenList(query.command.name.Text); cmd.CommandType = System.Data.CommandType.StoredProcedure; break; case ProjectFlx.Schema.commandType.Select: cmd.CommandText = ProjectFlx.Schema.Helper.FlattenList(query.command.text.Text); cmd.CommandType = System.Data.CommandType.Text; break; } foreach (ProjectFlx.Schema.parameter parm in query.parameters.parameter) { // short circuit if (query.command.type == Schema.commandType.Select) { string replace = String.Format("[{0}]", parm.name); cmd.CommandText = cmd.CommandText.Replace(replace, ProjectFlx.Schema.Helper.FlattenList(parm.Text)); continue; } // guarantee that we setup variable for out param types bool isoutparm = parm.inout == ProjectFlx.Schema.inoutType.inout || parm.inout == ProjectFlx.Schema.inoutType.@out; // assume null parameter value if collection is length of 0 // see _fillParmsWeb for implementation details on // passing null and empty strings if (parm.Text.Count == 0 && !isoutparm) continue; var value = ProjectFlx.Schema.Helper.FlattenList(parm.Text); if (value != null || isoutparm) { SqlParameter inoutparm; if (parm.type == ProjectFlx.Schema.fieldType.date) { string dtValue = null; if (value.EndsWith(" GMT")) dtValue = value.Substring(0, value.LastIndexOf(" GMT")); if (value.EndsWith(" UTC")) dtValue = value.Substring(0, value.LastIndexOf(" UTC")); if (dtValue == null) dtValue = value; var dt = DateTime.Parse("1970-1-1 01:01:01"); DateTime.TryParse(dtValue, out dt); if (dt.ToString("d").Equals("1/1/1970") && dt.ToString("t").Equals("1:1 AM")) throw new Exception("Could not parse date: " + value); inoutparm = cmd.Parameters.AddWithValue(parm.name, dt); } else if(parm.type == Schema.fieldType.json) { inoutparm = cmd.Parameters.AddWithValue(parm.name, value); } else inoutparm = cmd.Parameters.AddWithValue(parm.name, value); switch(parm.inout) { case ProjectFlx.Schema.inoutType.inout: inoutparm.Direction = ParameterDirection.InputOutput; inoutparm.DbType = getDBTypeForSchemaParm(parm); break; case ProjectFlx.Schema.inoutType.@out: inoutparm.Direction = ParameterDirection.Output; inoutparm.DbType = getDBTypeForSchemaParm(parm); break; } // enforce size for inout params (text only) if (parm.type == ProjectFlx.Schema.fieldType.text && isoutparm) { if (parm.size > 0) inoutparm.Size = parm.size; else throw new Exception(String.Format("Expecting parameter size for parameter {0} in query {1}", parm.name, query.name)); } // validate json text type if (parm.type == Schema.fieldType.json) { try { var jsonObj = Newtonsoft.Json.Linq.JObject.Parse(Schema.Helper.FlattenList(parm.Text)); } catch (Exception handled) { throw new Exception("Invalid Json for Parameter: " + parm.name, handled); } } } } int result = 0; switch (query.command.action) { case ProjectFlx.Schema.actionType.NonQuery: result = cmd.ExecuteNonQuery(); // populate output parameter values foreach(SqlParameter parm in cmd.Parameters) { if (parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Output) { ProjectFlx.Schema.parameter outboundParm = query.parameters.parameter.Find(delegate(ProjectFlx.Schema.parameter g) { return g.name == parm.ParameterName; }); if (outboundParm != null) { outboundParm.Text = new List<string>(); outboundParm.Text.Add(Convert.ToString(parm.Value)); } } } // set sorm sort of result break; case ProjectFlx.Schema.actionType.Result: using (SqlDataReader reader = cmd.ExecuteReader()) { readerToResult(reader, rslts.result, query.fields, query.paging); // include sub results (StoredProcedure returns more than one Result Set) while (reader.NextResult()) { if (query.subquery != null) { _Projresults.results.Add(rslts = new ProjectFlx.Schema.results()); readerToResult(reader, rslts.result, query.subquery.fields, query.paging); } } } break; case ProjectFlx.Schema.actionType.Scalar: Object objresult = cmd.ExecuteScalar(); var r = new ProjectFlx.Schema.result(); var i = new ProjectFlx.Schema.row(); XmlDocument xm = new XmlDocument(); XmlAttribute att = xm.CreateAttribute("Scalar"); att.Value = objresult.ToString(); i.AnyAttr.Add(att); r.row.Add(i); _Projresults.results[0].result = r; break; } }
private void readerToResult(SqlDataReader reader, ProjectFlx.Schema.result Result, List<ProjectFlx.Schema.field> Fields, ProjectFlx.Schema.paging Paging) { int currentpage = Paging.pages.current == 0 ? 1 : Paging.pages.current; // TODO: fill available pages to dropdown switch (Paging.direction) { case ProjectFlx.Schema.pagingDirectionType.next: currentpage++; break; case ProjectFlx.Schema.pagingDirectionType.previous: currentpage--; break; case ProjectFlx.Schema.pagingDirectionType.top: currentpage = 1; break; case ProjectFlx.Schema.pagingDirectionType.last: currentpage = 99; // TODO: last page break; } if (currentpage < 1) currentpage = 1; Paging.pages.current = currentpage; int resultfrom = ((currentpage * Paging.limit) - Paging.limit) + 1; int resultto = resultfrom + Paging.limit; // keep copy of previous page results incase resultsfrom exceeds total pages // return last know page ProjectFlx.Schema.result prevresult = new ProjectFlx.Schema.result(); int readercount = 0; while (reader.Read()) { // enforce paging results readercount++; if ((readercount >= resultfrom && readercount < (resultto)) || Paging.limit.Equals(-1)) { ProjectFlx.Schema.row r; Result.row.Add(r = new ProjectFlx.Schema.row()); XmlDocument xm = new XmlDocument(); List<XmlElement> innerNodes = new List<XmlElement>(); foreach (ProjectFlx.Schema.field f in Fields) { XmlAttribute att = xm.CreateAttribute(f.name); if (f.type == Schema.fieldType.json || f.type == Schema.fieldType.tryjson) { string json = reader[f.name].ToString().TrimEnd(); try { if (String.IsNullOrEmpty(json)) json = "{}"; else Newtonsoft.Json.Linq.JObject.Parse(json); string jsonDetails = String.Format("{{\"{0}\":{1}}}", f.name, json); var innerXml = Newtonsoft.Json.JsonConvert.DeserializeXmlNode(jsonDetails); innerNodes.Add((XmlElement)innerXml.DocumentElement); } catch { if (f.type == Schema.fieldType.tryjson) { json = reader[f.name].ToString().TrimEnd(); } else { json = "{\"error\":\"" + "Invalid Json Object field: " + f.name + "\"}"; } } att.Value = json; } else { try { string val = null; if (f.encode.HasValue()) { val = reader[f.encode].ToString(); val = System.Web.HttpUtility.UrlEncode(val.TrimEnd()).Replace("+", "%20"); } else if (f.regx_field.HasValue()) { val = reader[f.regx_field].ToString(); val = Regex.Replace(val,Schema.Helper.FlattenList(f.regx), f.regx_replace); } else val = reader[f.name].ToString(); if (val != null) att.Value = val; } catch (IndexOutOfRangeException handled) { throw new ProjectFlx.Exceptions.ProjectException(new Exceptions.ProjectExceptionArgs("A critical error has occured. Please notify MSO. We are sorry for the inconvenience.", "ProjectFlx.DB.SchemaBased.DatabaseQuery", "readerToResult(SqlDataReader, ProjectFlx.Schema.result, List<ProjectFlx.Schema.field>, ProjectFlx.Schema.paging", "att.Value = System.Web.HttpUtility.UrlEncode(reader[f.encode].ToString().TrimEnd()).Replace(" + ", \"%20\");", Exceptions.SeverityLevel.Critical, Exceptions.LogLevel.Event), handled); } } r.AnyAttr.Add(att); } // append inner nodes (usually from json field conversion) for (int i = 0; i < innerNodes.Count; i++) r.Any.Add(innerNodes[i]); } // save previous page results incase resultfrom > readercount, then return last page results int prevresultfrom = resultfrom - Paging.limit; int prevresultto = resultto - Paging.limit; if (prevresultfrom > 0) if ((readercount >= prevresultfrom && readercount < (prevresultto)) && Paging.limit > 0) { ProjectFlx.Schema.row r; prevresult.row.Add(r = new ProjectFlx.Schema.row()); XmlDocument xm = new XmlDocument(); foreach (ProjectFlx.Schema.field f in Fields) { XmlAttribute att = xm.CreateAttribute(f.name); if (f.encode != null) att.Value = System.Web.HttpUtility.UrlEncode(reader[f.encode].ToString().TrimEnd()); else att.Value = reader[f.name].ToString().TrimEnd(); r.AnyAttr.Add(att); } } } // check to see if we are trying to read records beyond recordset and // return last page results if we are if (resultfrom > readercount && Paging.limit != -1) { currentpage--; if (currentpage > 0) Paging.pages.current = currentpage; // copy previous results to Result ProjectFlx.Schema.row r; foreach (ProjectFlx.Schema.row r2 in prevresult.row) { Result.row.Add(r = new ProjectFlx.Schema.row()); foreach (XmlAttribute att in r2.AnyAttr) r.AnyAttr.Add(att); } } // get pages available for reader int pagescount = (readercount / Paging.limit) + 1; Paging.pages.totalpages = pagescount; Paging.pages.totalrecords = readercount; for (int x = 1; x <= pagescount; x++) { ProjectFlx.Schema.page pg = new ProjectFlx.Schema.page(); pg.value = x; pg.from = ((Paging.limit * x) - Paging.limit) + 1; // upper limit page to value is difference of total reader count if (x.Equals(pagescount)) pg.to = readercount; else pg.to = (Paging.limit * x); Paging.pages.page.Add(pg); } }