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"; } }