예제 #1
0
        /// <summary>
        /// Execute query on the database and fill DataTable with the result.
        /// Connection and transaction information will be appended to the provided command.
        /// </summary>
        /// <param name="query">ADO.NET driver</param>
        /// <param name="command">command to execute</param>
        /// <param name="parameters">sql additional parameters</param>
        /// <returns>populated table</returns>
        public static DataTable Fill(
            this IDatabaseQuery query,
            IDbCommand command)
        {
            Contract.Requires(query != null);
            Contract.Requires(command != null);

            var ds = new DataSet();

            query.Fill(command, ds);
            return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
        }
예제 #2
0
        /// <summary>
        /// Execute query on the database and fill DataTable with the result.
        /// Connection and transaction information will be appended to the provided command.
        /// </summary>
        /// <param name="query">ADO.NET driver</param>
        /// <param name="sql">sql to execute</param>
        /// <param name="parameters">sql additional parameters</param>
        /// <returns>populated table</returns>
        public static DataTable Fill(
            this IDatabaseQuery query,
            string sql,
            params object[] parameters)
        {
            Contract.Requires(query != null);
            Contract.Requires(!string.IsNullOrWhiteSpace(sql));

            var com = query.CreateCommand(sql, parameters);
            var ds  = new DataSet();

            query.Fill(com, ds);
            return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
        }
예제 #3
0
        public DataTable Analyze(
            IEnumerable <string> dimensions,
            IEnumerable <string> facts,
            IEnumerable <KeyValuePair <string, bool> > order,
            ISpecification <TSource> filter,
            int?limit,
            int?offset)
        {
            var usedDimensions = new List <string>();
            var usedFacts      = new List <string>();
            var customOrder    = new List <KeyValuePair <string, bool> >();

            if (dimensions != null)
            {
                usedDimensions.AddRange(dimensions);
            }
            if (facts != null)
            {
                usedFacts.AddRange(facts);
            }
            if (order != null)
            {
                foreach (var o in order)
                {
                    if (o.Key != null)
                    {
                        customOrder.Add(new KeyValuePair <string, bool>(o.Key, o.Value));
                    }
                }
            }

            ValidateInput(usedDimensions, usedFacts, customOrder.Select(it => it.Key));

            var sb    = new StringBuilder();
            var alias = filter != null?filter.IsSatisfied.Parameters.First().Name : "it";

            if (offset != null)
            {
                sb.Append("SELECT ");
                sb.Append(string.Join(", ", usedDimensions.UnionAll(usedFacts).Select(it => "\"" + it + "\"")));
                sb.AppendLine(" FROM (");
            }
            if (limit != null || offset != null)
            {
                sb.Append("SELECT /*+ FIRST_ROWS(n) */ ");
                sb.Append(string.Join(", ", usedDimensions.UnionAll(usedFacts).Select(it => "\"" + it + "\"")));
                if (offset != null)
                {
                    sb.Append(", RowNum rn$");
                }
                sb.AppendLine(" FROM (");
            }
            sb.Append("SELECT ");
            foreach (var d in usedDimensions)
            {
                sb.AppendFormat("{0} AS \"{1}\", ", CubeDimensions[d](alias), d);
            }
            foreach (var f in usedFacts)
            {
                sb.AppendFormat("{0} AS \"{1}\", ", CubeFacts[f](alias), f);
            }
            sb.Length -= 2;
            sb.AppendLine();
            sb.AppendFormat("FROM {0} \"{1}\"", Source, alias);
            sb.AppendLine();

            var command = DatabaseQuery.NewCommand();

            if (filter != null)
            {
                var cf = Locator.Resolve <IOracleConverterFactory>();
                var ep = Locator.Resolve <IExtensibilityProvider>();
                var qp =
                    new MainQueryParts(
                        Locator,
                        cf,
                        ep.ResolvePlugins <IQuerySimplification>(),
                        ep.ResolvePlugins <IExpressionMatcher>(),
                        ep.ResolvePlugins <IMemberMatcher>(),
                        new IProjectionMatcher[0]);
                var linq   = new Queryable <TSource>(new QueryExecutor(DatabaseQuery, Locator, cf, ep)).Filter(filter);
                var parser = QueryParser.CreateDefault();
                var model  = parser.GetParsedQuery(linq.Expression);
                if (model.BodyClauses.Count > 0)
                {
                    sb.AppendLine("WHERE");
                    for (int i = 0; i < model.BodyClauses.Count; i++)
                    {
                        var wc = model.BodyClauses[i] as WhereClause;
                        sb.Append("	");
                        if (i > 0)
                        {
                            sb.Append("AND ");
                        }
                        sb.Append(qp.GetSqlExpression(wc.Predicate));
                    }
                }
                foreach (var p in qp.Parameters.Parameters)
                {
                    command.Parameters.Add(p);
                }
            }
            sb.AppendLine();
            if (usedDimensions.Count > 0)
            {
                sb.Append("GROUP BY ");
                sb.AppendLine(string.Join(", ", usedDimensions.Select(it => CubeDimensions[it](alias))));
            }
            if (customOrder.Count > 0)
            {
                sb.Append("ORDER BY ");
                sb.AppendLine(string.Join(", ", customOrder.Select(it => "\"{0}\" {1}".With(it.Key, it.Value ? string.Empty : "DESC"))));
            }
            if (limit != null || offset != null)
            {
                sb.AppendLine(") sq$");
                if (limit != null)
                {
                    sb.Append("WHERE RowNum <= ");
                    sb.Append(limit.Value + (offset != null ? offset.Value : 0));
                }
            }
            if (offset != null)
            {
                sb.AppendLine(") sq$");
                sb.Append("WHERE sq$.rn$ > ");
                sb.Append(offset.Value);
            }
            command.CommandText = sb.ToString();
            return(DatabaseQuery.Fill(command));
        }
예제 #4
0
        public string[] Persist(IEnumerable <global::Test.XYZ> insert, IEnumerable <KeyValuePair <global::Test.XYZ, global::Test.XYZ> > update, IEnumerable <global::Test.XYZ> delete)
        {
            var insertedData = insert != null?insert.ToArray() : new global::Test.XYZ[0];

            var updatedData = update != null?update.ToList() : new List <KeyValuePair <global::Test.XYZ, global::Test.XYZ> >();

            var deletedData = delete != null?delete.ToArray() : new global::Test.XYZ[0];

            if (insertedData.Length == 0 && updatedData.Count == 0 && deletedData.Length == 0)
            {
                return(new string[0]);
            }

            if (updatedData.Count > 0 && updatedData.Any(it => it.Key == null))
            {
                //TODO fetch only null values
                var oldValues = Find(updatedData.Select(it => it.Value.URI)).ToDictionary(it => it.URI);
                if (oldValues.Count != updatedData.Count)
                {
                    throw new ArgumentException("Can't find update value. Requested: {0}, found: {1}. Missing URI: {2}".With(
                                                    updatedData.Count,
                                                    oldValues.Count,
                                                    string.Join(", ", updatedData.Select(it => it.Value.URI).Except(oldValues.Keys))));
                }
                global::Test.XYZ _val;
                for (int i = 0; i < updatedData.Count; i++)
                {
                    _val           = updatedData[i].Value;
                    updatedData[i] = new KeyValuePair <global::Test.XYZ, global::Test.XYZ>(oldValues[_val.URI], _val);
                }
            }

            updatedData.RemoveAll(kv => kv.Key.Equals(kv.Value));

            var cms = Common.Utility.UseThreadLocalStream();


            DatabaseQuery.Fill <global::Test.XYZ, int>(insertedData, @"nextval('""Test"".""XYZ_ID_seq""'::regclass)::int", (it, seq) => it.ID = seq);

            for (int i = 0; i < insertedData.Length; i++)
            {
                insertedData[i].__InternalPrepareInsert();
            }
            foreach (var it in updatedData)
            {
                it.Value.__InternalPrepareUpdate();
            }
            for (int i = 0; i < deletedData.Length; i++)
            {
                deletedData[i].__InternalPrepareDelete();
            }

            for (int i = 0; i < insertedData.Length; i++)
            {
                var it = insertedData[i];

                it.URI = _DatabaseCommon.FactoryTest_XYZ.XYZConverter.BuildURI(cms.CharBuffer, it.ID);
            }
            foreach (var kv in updatedData)
            {
                kv.Value.URI = _DatabaseCommon.FactoryTest_XYZ.XYZConverter.BuildURI(cms.CharBuffer, kv.Value.ID);
            }


            _InternalDoPersist(cms, insertedData, updatedData, deletedData);
            var resultURI = new string[insertedData.Length];

            for (int i = 0; i < resultURI.Length; i++)
            {
                resultURI[i] = insertedData[i].URI;
            }


            if (DatabaseQuery.InTransaction)
            {
                if (insertedData.Length > 0)
                {
                    NotifyQueue.Enqueue(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Insert, resultURI));
                }
                if (updatedData.Count > 0)
                {
                    NotifyQueue.Enqueue(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Update, updatedData.Select(it => it.Key.URI).ToArray()));
                    if (updatedData.Any(kv => kv.Key.URI != kv.Value.URI))
                    {
                        NotifyQueue.Enqueue(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Change, updatedData.Where(kv => kv.Key.URI != kv.Value.URI).Select(it => it.Value.URI).ToArray()));
                    }
                }
                if (deletedData.Length > 0)
                {
                    NotifyQueue.Enqueue(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Delete, deletedData.Select(it => it.URI).ToArray()));
                }
            }
            else
            {
                if (insertedData.Length > 0)
                {
                    Notifications.Notify(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Insert, resultURI));
                }
                if (updatedData.Count > 0)
                {
                    Notifications.Notify(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Update, updatedData.Select(it => it.Key.URI).ToArray()));
                    if (updatedData.Any(kv => kv.Key.URI != kv.Value.URI))
                    {
                        Notifications.Notify(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Change, updatedData.Where(kv => kv.Key.URI != kv.Value.URI).Select(it => it.Value.URI).ToArray()));
                    }
                }
                if (deletedData.Length > 0)
                {
                    Notifications.Notify(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Delete, deletedData.Select(it => it.URI).ToArray()));
                }
            }
            DataCache.Invalidate(updatedData.Select(it => it.Key.URI).Union(deletedData.Select(it => it.URI)));

            return(resultURI);
        }