예제 #1
0
        private bool _sqlIsDirty;                  // SQL needs to be regenerated

        #endregion Members

        //----------------------------------------------------------------

        #region Constructor

        /// <summary>
        /// Initializes a new instance of a <see cref="QueryBuilder"/>.
        /// </summary>
        /// <param name="schema"><see cref="OleDbSchema"/> used by the query builder.</param>
        public QueryBuilder(OleDbSchema schema)
        {
            _sql                      = null;
            _schema                   = schema;
            _queryFields              = new QueryFieldCollection();
            _queryFields.ListChanged += _queryFields_ListChanged;
        }
예제 #2
0
        // build the SELECT clause
        private string BuildSelectClause()
        {
            var sb = new StringBuilder();

            foreach (QueryField field in QueryFields)
            {
                if (field.Output)
                {
                    // add separator
                    if (sb.Length > 0)
                    {
                        sb.Append(",\r\n\t");
                    }

                    // add field expression ("table.column" or "colexpr")
                    string item = field.GetFullName(GroupBy);
                    sb.Append(item);

                    // add alias (use brackets to contain spaces, reserved words, etc)
                    if (!string.IsNullOrEmpty(field.Alias))
                    {
                        sb.AppendFormat(" AS {0}", OleDbSchema.BracketName(field.Alias));
                    }
                }
            }
            return(sb.ToString());
        }
예제 #3
0
        /// <summary>
        /// Gets the full field name, including the parent table name and brackets.
        /// </summary>
        /// <returns>The full field name.</returns>
        public string GetFullName()
        {
            // return table.column string
            if (Column == "*" || _table.Columns.Contains(Column))
            {
                return(string.Format("{0}.{1}",
                                     OleDbSchema.GetFullTableName(_table),
                                     OleDbSchema.BracketName(Column)));
            }

            // column is not part of a table (e.g. expression)
            return(OleDbSchema.BracketName(Column));
        }
예제 #4
0
 public static Type GetAltaxoColumnType(Type oledbType)
 {
     if (OleDbSchema.IsNumeric(oledbType))
     {
         return(typeof(Altaxo.Data.DoubleColumn));
     }
     else if (OleDbSchema.IsDateTime(oledbType))
     {
         return(typeof(Altaxo.Data.DateTimeColumn));
     }
     else
     {
         return(typeof(Altaxo.Data.TextColumn));
     }
 }
예제 #5
0
        /// <summary>
        /// Gets a <see cref="OleDbSchema"/> that contains the schema for a
        /// given connection string.
        /// </summary>
        /// <param name="connString">OleDb connection string used to
        /// initialize the new <see cref="OleDbSchema"/>.</param>
        /// <returns>
        /// A new <see cref="OleDbSchema"/> containing the schema for the
        /// given <paramref name="connString"/> or null if the connection
        /// string is invalid.
        /// </returns>
        public static OleDbSchema GetSchema(string connString)
        {
            // trivial test
            connString = OleDbConnString.TranslateConnectionString(connString);
            if (string.IsNullOrEmpty(connString) ||
                connString.IndexOf("Provider=", StringComparison.OrdinalIgnoreCase) < 0)
            {
                return(null);
            }

            // connString looks OK, try getting the schema
            try
            {
                var ds = new OleDbSchema
                {
                    ConnectionString = connString
                };
                return(ds);
            }
            catch
            {
                return(null);
            }
        }
예제 #6
0
        // build the FROM clause
        private string BuildFromClause()
        {
            // build list of tables in query
            var tables = new List <DataTable>();

            foreach (QueryField field in QueryFields)
            {
                string    tableName = field.Table;
                DataTable table     = _schema.Tables[tableName];
                if (table != null && !tables.Contains(table))
                {
                    tables.Add(table);
                }
            }

            // save table count so caller can check this
            _tableCount = tables.Count;

            // build list of joined tables so each table is related to the next one
            var  qTables = new List <DataTable>();
            bool done    = false;

            while (qTables.Count < tables.Count && !done)
            {
                done = true;
                foreach (DataTable dt in tables)
                {
                    bool inserted = InsertRelatedTable(dt, qTables);
                    if (inserted)
                    {
                        done = false;
                    }
                }
            }

            // build join list
            var qJoins = new List <string>();

            for (int index = 0; index < qTables.Count - 1; index++)
            {
                // get relation
                var          dt1 = qTables[index];
                var          dt2 = qTables[index + 1];
                DataRelation dr  = GetRelation(dt1, dt2);

                // build join statement
                qJoins.Add(string.Format("{0}.{1} = {2}.{3}",
                                         OleDbSchema.GetFullTableName(dr.ParentTable),
                                         dr.ParentColumns[0].ColumnName,
                                         OleDbSchema.GetFullTableName(dr.ChildTable),
                                         dr.ChildColumns[0].ColumnName));
            }

            // build from statement
            var sb = new StringBuilder();

            for (int i = 0; i < qTables.Count - 1; i++)
            {
                var dt = qTables[i] as DataTable;
                if (sb.Length > 0)
                {
                    sb.Append("\r\n\t");
                }
                sb.AppendFormat("({0} INNER JOIN", OleDbSchema.GetFullTableName(dt));
            }
            sb.AppendFormat(" {0}", OleDbSchema.GetFullTableName(qTables[qTables.Count - 1]));
            for (int i = qJoins.Count - 1; i >= 0; i--)
            {
                string join = qJoins[i] as string;
                sb.AppendFormat("\r\n\tON {0})", join);
            }

            // not all tables joined? probably not what the user wants...
            _missingJoins = qTables.Count < tables.Count;

            // add tables that couldn't be joined
            if (_missingJoins)
            {
                foreach (DataTable dt in tables)
                {
                    if (!qTables.Contains(dt))
                    {
                        sb.AppendFormat(", {0}", OleDbSchema.GetFullTableName(dt));
                        qTables.Add(dt);
                    }
                }
            }

            // done
            return(sb.ToString());
        }