protected void Page_Load(object sender, EventArgs e)
{
try
{
//TODO: check for all field validation
if (Request["sql"] != null)
{
string guid = Guid.NewGuid().ToString();
using (DataClasses1DataContext dc = new DataClasses1DataContext())
{
Query q = new Query();
q.GUID = guid;
q.catalog = Request["catalog"];
q.createdby = User.Identity.Name;
q.createdon = DateTime.Now;
q.enabled = true;
q.server = Request["server"];
q.sql = Request["sql"];
q.username = Request["username"];
q.password = Request["password"];
q.url = "/report.aspx?guid=" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(guid));
dc.Queries.InsertOnSubmit(q);
dc.SubmitChanges();
}
Response.Write(Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(guid)));
}
}catch (Exception ex){
Response.Write("Trouble");
}
}
public static string GenerateQuery(string guid, out string dataout, out string columnsout)
{
Query q = null;
using (DataClasses1DataContext dc = new DataClasses1DataContext())
{
var query = (from c in dc.Queries
where c.GUID == guid
select c).SingleOrDefault();
q = query;
}
string connectionString =
"Data Source=" + q.server + ";Initial Catalog=" + q.catalog + ";Persist Security Info=True;User ID="+q.username+";Password="******"{");
for (var x = 0; x < reader.FieldCount; x++)
{
if (columns[x].cell == "number")
{
if (Convert.IsDBNull(reader[x]) == false)
{
row.Append("\"" + columns[x].name + "\"" + ":" + reader[x] + "");
}
else row.Append("\"" + columns[x].name + "\"" + ":" + "null" + "");
}
else if ((columns[x].cell == "date") || (columns[x].cell == "datetime"))
{
if (Convert.IsDBNull(reader[x]) == false)
{
row.Append("\"" + columns[x].name + "\"" + ":\"" + ((DateTime)reader[x]).ToString("yyyy-MM-dd") + "\"");
}
else row.Append("\"" + columns[x].name + "\"" + ":\"" + "" + "\"");
}
else
{
if (Convert.IsDBNull(reader[x]) == false)
{
//row = row + "\"" + columns[x].name + "\"" + ":\"" + reader[x] + "\"";
row.Append("\"" + columns[x].name + "\"" + ":\"" + Convert.ToString(reader[x]).Replace("\"", "\\\"") + "\"");
}
else
{
row.Append("\"" + columns[x].name + "\"" + ":\"" + (reader[x]) + "\"");
}
}
if (x != reader.FieldCount - 1)
row.Append( ",");
}
row.Append("}");
result.Add(row.ToString());
}
}
reader.Close();
connection.Close();
}
catch (Exception ex)
{
dataout = "";
columnsout = "";
return "Trouble";
}
//output
StringBuilder resOut = new StringBuilder("[");
for (int i = 0; i < result.Count; i++)
{
if (i == result.Count - 1)
{
resOut.Append(result[i]);
}
else
resOut.Append(result[i] + ",");
}
resOut.Append("]");
dataout = resOut.ToString();
columnsout = JSON.ToJSON(columns);
return "OK";
}
}