public virtual IList<object> GetList(SqlListGenerator generator) { HashSet<object> set = new HashSet<object>(); using (var conn = CreateConnection()) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandTimeout = CommandTimeout; generator.UpdateCommand(cmd); using (var reader = cmd.ExecuteReader()) { int idx = -1; try { idx = reader.GetOrdinal("ID"); } catch { } while (reader.Read()) { // assumes either 1 or 2 columns. If 2, one will be ID column var item = reader.GetValue(idx != -1 ? 1 - idx : 0); if (!set.Contains(item)) set.Add(item); } } } } return new List<object>(set); }
/// <summary> /// Execute all SQL statements and ensure the given configuration is valid. /// </summary> /// <param name="config"></param> public virtual void Validate(bool onlyErrors = false) { _conn.Open(); var broken = new List<ISubject>(); // ensure all the source data from the subjects can be executed as SQL foreach (var subject in _configuration) { // check subject and all non-related fields first var fields = subject.FindAll<IField>(f => !(f is IRelationField)) .ToList<IField>() .ConvertAll<IFieldPath>(f => FieldPath.FromDefault(f)); var generator = new ExposedSqlGenerator(_configuration); generator .Target(subject) .Column(fields); try { int rows = Execute(generator); if (!onlyErrors) Console.WriteLine("Subject '" + subject.DisplayName + "' succeeded (" + rows + " rows affected)"); } catch (Exception ex) { Console.WriteLine("Subject '" + subject.DisplayName + "' failed when executing with all non-RelationFields: " + ex.Message + "\n\t" + generator.GenerateSql()); broken.Add(subject); continue; } // now check all RelationFields individually for validity foreach (var rf in subject.FindAll<IField>(f => f is IRelationField)) { var path = FieldPath.FromDefault(rf); var bf = path.Find(f2 => broken.Contains(f2.Subject)); if (bf != null) { Console.WriteLine("Cannot check relation field for " + path.Description + " as " + bf.Subject + " SQL is invalid."); continue; } var generator2 = new ExposedSqlGenerator(_configuration); generator2 .Target(subject) .Column(path); try { int rows = Execute(generator2); if (!onlyErrors) Console.WriteLine("RelationField '" + rf.DisplayName + "' succeeded."); } catch (Exception ex) { Console.WriteLine("RelationField '" + rf.DisplayName + "' failed. " + ex.Message + "\n\t" + generator2.GenerateSql()); } } // for all fields, ensure list data can be retrieved foreach (var f in subject) { var path = FieldPath.FromDefault(f); if (path.Last.List != null && !String.IsNullOrEmpty(path.Last.List.Source)) { var bf = path.Find(f2 => broken.Contains(f2.Subject)); if (bf != null) { Console.WriteLine("Cannot check list data for " + path.Description + " as " + bf.Subject + " SQL is invalid."); continue; } var gen = new SqlListGenerator(_configuration).Path(path); if (Regex.IsMatch(path.Last.List.Source, @"^select.*[`'\[\s]id", RegexOptions.IgnoreCase)) gen.IdColumn("ID") .ValueColumn("Value"); try { int rows = Execute(gen); if (!onlyErrors) Console.WriteLine("List data for " + path.Description + " succeeded (" + rows + " rows affected)."); } catch (Exception ex) { Console.WriteLine("List data for " + path.Description + " failed. " + ex.Message + "\n\t"); } } } } // ensure all path SQL statements execute successfully foreach (var from in _configuration) { foreach (var to in _configuration) { if (broken.Contains(from)) { Console.WriteLine("Cannot check matrix query [" + from.DisplayName + " to " + to.DisplayName + "] because " + from.DisplayName + " SQL is invalid."); continue; } if (broken.Contains(to)) { Console.WriteLine("Cannot check matrix query [" + from.DisplayName + " to " + to.DisplayName + "] because " + to.DisplayName + " SQL is invalid."); continue; } var matrix = _configuration[from, to]; if (String.IsNullOrWhiteSpace(matrix.Query)) { if (!onlyErrors) Console.WriteLine("No relationship between " + from.DisplayName + " to " + to.DisplayName + "."); continue; } // try the vanilla SQL first try { int rows = Execute(matrix.Query); if (!onlyErrors) Console.WriteLine("Matrix query for [" + from.DisplayName + " to " + to.DisplayName + "] succeeded (" + rows + " rows affected)."); } catch (Exception ex) { Console.WriteLine("Matrix query for [" + from.DisplayName + " to " + to.DisplayName + "] failed. " + ex.Message + "\n\t" + matrix.Query); continue; } // now try a full query traversing the two subjects // TODO: is this correct? are we asking for Target=from? somethings not right here... var generator = new ExposedSqlGenerator(_configuration); generator .Target(from) .Column(FieldPath.FromDefault(to.IdField)); try { int rows = Execute(generator); if (!onlyErrors) Console.WriteLine("Generated SQL for " + from.DisplayName + " to " + to.DisplayName + " succeeded (" + rows + " rows affected)."); } catch (Exception ex) { Console.WriteLine("Generated SQL for " + from.DisplayName + " to " + to.DisplayName + " failed. " + ex.Message + "\n\t" + generator.GenerateSql()); } } } }
private int Execute(SqlListGenerator generator) { using (var cmd = _conn.CreateCommand()) { generator.UpdateCommand(cmd); System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)cmd); //we are now tieing ourselves to SQL System.Data.DataTable tbl = new System.Data.DataTable(); adapter.Fill(tbl); return tbl.Rows.Count; } }