public string GetData(int BindingID, string[] parameters, bool singleRow = false) { checkCookieAndLogin(); XDocument doc = new XDocument(); switch (BindingID) { case -1: // page break; case -2: // static image break; default: using (var unit = GetUnitOfWork()) { // get the query WebToPrintBinding wtpb = unit.Scope.Repository <WebToPrintBinding>().GetSingle(c => c.BindingID == BindingID); string sql = ""; if (wtpb != null) { sql = wtpb.Query; using (SqlConnection conn = new SqlConnection(Environments.Current.Connection))//context.Connection.ConnectionString)) { conn.Open(); SqlCommand comm = new SqlCommand(sql, conn); int i = 0; foreach (WebToPrintBindingField wtpbf in wtpb.WebToPrintBindingFields.Where(c => c.Type % 2 == 0)) { comm.Parameters.Add(new SqlParameter("@" + wtpbf.FieldID.ToString(), parameters[i])); i++; } SqlDataReader reader = comm.ExecuteReader(); XElement root = new XElement("root"); while (reader.Read()) { root.Add(new XElement("row", from WebToPrintBindingField wtpbf in wtpb.WebToPrintBindingFields where wtpbf.Type % 2 == 1 select new XElement("Value", new XAttribute("Name", wtpbf.Name), reader.GetSqlValue(reader.GetOrdinal(wtpbf.FieldID.ToString()))))); if (singleRow) { break;//dance } } doc.Add(root); conn.Close(); } } } break; } return(doc.ToString()); }
public ActionResult EditBinding(int id) { WebToPrintBinding b = this.GetObject <WebToPrintBinding>(c => c.BindingID == id); return(Json(new { success = true, data = new { b.BindingID, b.Name, b.QueryText } })); }
private void CheckForFields(IServiceUnitOfWork unit, WebToPrintBinding wtpb) { try { if (wtpb.WebToPrintBindingFields == null) { wtpb.WebToPrintBindingFields = new List <WebToPrintBindingField>(); } if (wtpb.WebToPrintBindingFields.Count != 0) { unit.Service <WebToPrintBindingField>().Delete(wtpb.WebToPrintBindingFields); wtpb.WebToPrintBindingFields = new List <WebToPrintBindingField>(); unit.Save(); } string query = wtpb.QueryText; wtpb.Query = ""; if (query.ToLower().Contains("select") && query.ToLower().Contains("from")) { int fromindex = query.ToLower().LastIndexOf("from"); int offset = 7; if (query.ToLower().Contains("distinct")) { offset += 9; } List <string> selects = new List <string>(); string subString = query.Substring(offset, fromindex - offset); int bracketcount = 0; int lastIndex = 0; for (int i = 0; i < subString.Length; i++) { if (subString[i] == '(') { bracketcount++; } if (subString[i] == ')') { bracketcount--; } if (subString[i] == ',' && bracketcount == 0) { selects.Add(subString.Substring(lastIndex, i - lastIndex).Trim()); lastIndex = i + 1; } } selects.Add(subString.Substring(lastIndex, subString.Length - lastIndex).Trim()); string[] selectwords = selects.ToArray(); string newquery = "select "; if (query.ToLower().Contains("distinct")) { newquery += "distinct "; } for (int i = 0; i < selectwords.Length; i++) { string word = selectwords[i].Trim(); string name = word; int asIndex = word.ToLower().LastIndexOf("as"); if (asIndex >= 0) { name = word.Substring((asIndex + 3), word.Length - (asIndex + 3)); } WebToPrintBindingField wtpbf = new WebToPrintBindingField() { Name = name, WebToPrintBinding = wtpb, Options = 0, Type = (byte)BindingFieldType.Unknown + 1 }; unit.Service <WebToPrintBindingField>().Create(wtpbf); unit.Save(); if (asIndex >= 0) { newquery += word.Substring(0, asIndex - 1) + " as '" + wtpbf.FieldID + "'"; } else { newquery += word + " as '" + wtpbf.FieldID + "'"; } if (i < selectwords.Length - 1) { newquery += ", "; } } if (query.ToLower().Contains("where")) { int whereindex = query.ToLower().LastIndexOf("where") + 5; newquery += " " + query.Substring(fromindex, whereindex - 5 - (fromindex)); newquery += "where"; string[] wherewords = query.Substring(whereindex, query.Length - whereindex).Split('='); for (int i = 0; i < wherewords.Length - 1; i++) { string[] values = wherewords[i].Trim().Split(' '); string[] values2 = wherewords[i + 1].Trim().Split(' '); WebToPrintBindingField wtpbf = new WebToPrintBindingField() { Name = values2[0], Type = (byte)BindingFieldType.Unknown }; wtpb.WebToPrintBindingFields.Add(wtpbf); unit.Save(); newquery += " " + values[values.Length - 1] + "=@" + wtpbf.FieldID; if (values2.Length > 1) { newquery += " " + values2[1]; } } } else { newquery += " " + query.Substring(fromindex, query.Length - (fromindex)); } wtpb.Query = newquery; unit.Save(); } else { Exception e = new Exception("The query is invalid"); throw e; } } catch (Exception e) { throw e; } }
/// <summary> /// Updates all the objects currently registered with the querybuilder /// </summary> public void Execute(IUnitOfWork work) { using (SqlConnection conn = new SqlConnection(Environments.Current.Connection)) { conn.Open(); foreach (int bindingID in Bindings.Keys) { if (bindingID > 0) { // fetch the binding WebToPrintBinding wtpb = work.Scope.Repository <WebToPrintBinding>().GetSingle(c => c.BindingID == bindingID); if (wtpb == null) { throw new Exception("Unknown binding attached to component"); } string sql = wtpb.Query; SqlCommand comm = new SqlCommand(sql, conn); Bindings[bindingID].ForEach(c => { foreach (WebToPrintBindingField wtpbf in wtpb.WebToPrintBindingFields.Where(f => f.Type % 2 == 0)) { comm.Parameters.Add(new SqlParameter("@" + wtpbf.FieldID.ToString(), c.Inputs[wtpbf.Name])); } SqlDataReader reader = comm.ExecuteReader(); if (reader.Read()) { Dictionary <string, string> bindingValues = new Dictionary <string, string>(); foreach (var field in wtpb.WebToPrintBindingFields) { if (field.Type % 2 == 1) { bindingValues.Add(field.Name, reader.GetSqlValue(reader.GetOrdinal(field.FieldID.ToString())).ToString()); } if ((field.Options & (int)BindingFieldOptions.IndexValue) == (int)BindingFieldOptions.IndexValue) { // is databinding source, set all components bound to this output to be used in the indexbuilder foreach (var comp in c.Children) { if (comp.DataBindingSource == field.Name) { comp.IsIndexComponent = true; } } } } c.SetData(bindingValues); } try { reader.Close(); } catch (Exception e) { } comm.Parameters.Clear(); }); } } conn.Close(); } }