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();
}
}
