Exemple #1
0
        /*
         * Use ObjectQuery(T) Class or ObjectSet<T> on the object context to access data using Entity SQL Builder methods.
         * Both methods requires an instance of type ObjectContext. As a result, Entity SQL Builder methods are not supported
         * on objects of type DbContext.
         */
        public static void RunESQLBuilderExample()
        {
            using (var context = new AdventureWorksEntities())
            {
                System.Console.WriteLine("\nUsing Entity SQL Builder");

                if (context.GetType() == typeof (ObjectContext))
                {
                    // An example of ESQL Builder using an ObjectSet<T> on the ObjectContext
                    // var order = context.CreateObjectSet<SalesOrderHeader>("SalesOrderHeader").Where("it.SalesOrderID = 43661");
                    // System.Console.WriteLine("\nSalesOrderID: {0} \nOrderDate: {1} \nDueDate: {2} \nShipDate: {3}", order.SalesOrderID, order.OrderDate, order.DueDate, order.ShipDate);

                    var objContext = new ObjectContext("name=AdventureWorksEntities");
                    var queryString =
                        @"SELECT VALUE salesOrders FROM AdventureWorksEntities.SalesOrderHeader AS salesOrders";
                    var salesQuery1 = new ObjectQuery<SalesOrderHeader>(queryString, objContext, MergeOption.NoTracking);
                    var salesQuery2 = salesQuery1.Where("it.SalesOrderID = 43661");

                    foreach (var order in salesQuery2)
                    {
                        System.Console.WriteLine("\nSalesOrderID: {0} \nOrderDate: {1} \nDueDate: {2} \nShipDate: {3}",
                                                 order.SalesOrderID, order.OrderDate, order.DueDate, order.ShipDate);
                    }
                }
                else
                {
                    System.Console.WriteLine("\nSorry! Context is not of type ObjectContext. ESQL Builder is not supported.");
                }

            }
        }
 public QueryLogFile(ObjectQuery objQuery)
 {
     _querytype = QueryType.ESQL;
     _ESQL = objQuery.CommandText;
     _StoreSQL = objQuery.ToTraceString();
     if (_ESQL != string.Empty) //if this was cast from LINQ, don\'t log  here
         CreateLogEntry();
 }
Exemple #3
0
        /// <summary>
        /// Gets or create the future context that is linked to the underlying <see cref="ObjectContext"/>.
        /// </summary>
        /// <param name="objectQuery">The query source to get the future context from.</param>
        /// <returns>An instance of <see cref="IFutureContext"/> to store waiting future queries.</returns>
        public IFutureContext GetOrCreate(ObjectQuery objectQuery)
        {
            if (objectQuery == null)
                throw new ArgumentNullException("objectQuery");

            var objectContext = objectQuery.Context;
            if (objectContext == null)
                throw new ArgumentException("The source ObjectContext can not be null.", "objectQuery");

            MaybeCleanup();

            int key = RuntimeHelpers.GetHashCode(objectContext);
            return _futureContext.GetOrAdd(key, k => new FutureContext(objectContext));
        }
Exemple #4
0
        static void Main(string[] args)
        {
            using (var e = new AdventureWorksEntities())
            {
                //http://msdn.microsoft.com/pt-br/library/vstudio/bb387145.aspx

                const String cmd = "SELECT VALUE C FROM AdventureWorksEntities.Contatos AS C WHERE C.ContactID <= 15";

                var contatos = new ObjectQuery<Contato>(cmd, e);

                foreach (var contato in contatos)
                {
                    Console.WriteLine(contato.Nome);
                }
            }

            Console.ReadKey();
        }
Exemple #5
0
        private static void MostrarMergeOption(MergeOption mo)
        {
            using (var e = new AdventureWorksEntities())
            {
                var s = new Stopwatch();

                s.Start();

                var contatos = new ObjectQuery<Contato>("SELECT VALUE C FROM AdventureWorksEntities.Contatos AS C", e, mo);

                foreach (var contato in contatos)
                {
                    contato.Nome = contato.Nome;
                }

                Console.WriteLine(s.ElapsedMilliseconds);
            }
        }
Exemple #6
0
 public void InitalizeTable(ObjectQuery<Person> objectQuery)
 {
     GetPersonTypes();
     GetPersonColumnTitles();
     foreach (Person item in objectQuery)
     {
         List<object> newList = new List<object>();
         for (int i = 0; i < item.Length; i++)
         {
             if (item[i] is byte[])
             {
                 newList.Add("File");
                 continue;
             }
             newList.Add(item[i]);
         }
         valuesList.Add(newList);
     }
 }
        static void Main()
        {
            using (NorthwindEntities northwindEntities = new NorthwindEntities())
            {
                string queryString =
                @"SELECT VALUE Customer FROM NorthwindEntities.Customers
                AS Customer WHERE Customer.City=='London'";

                var customerQuery = new ObjectQuery<Customer>(queryString, northwindEntities);

                Logger.PrintQueries(customerQuery);

                foreach (Customer customer in customerQuery)
                {
                    Console.WriteLine("Company Name: {0}\n Contact Name: {1}\n City: {2}\n{3}",
                                      customer.CompanyName, customer.ContactName, customer.City, Logger.SeparatorLine);
                }
            }
        }
Exemple #8
0
        static void Main(string[] args)
        {
            string queryString =
                 @"SELECT VALUE DISTINCT Customer
               FROM NorthwindEntities.Customers AS Customer
               INNER JOIN NorthwindEntities.Orders AS Order
               ON Customer.CustomerID=Order.CustomerID
               WHERE Year(Order.OrderDate) == 1997 &&
               Order.ShipCountry=='Canada'";

                var customerQuery = new ObjectQuery<Customer>(
            queryString, DAO.db);

                foreach (var c in customerQuery)
                {
                    Console.WriteLine(c.ContactName);
                    Console.WriteLine(c.Address);

                }
        }
        static void Main()
        {
            //4.Implement previous by using native SQL
            //query and executing it through the ObjectContext.

            string queryString =
            @"SELECT VALUE DISTINCT Customer
            FROM NorthwindEntities.Customers AS Customer
            INNER JOIN NorthwindEntities.Orders AS Order
            ON Customer.CustomerID=Order.CustomerID
            WHERE Year(Order.OrderDate) == 1997 &&
            Order.ShipCountry=='Canada'";

            var customerQuery = new ObjectQuery<Customer>(
            queryString, DAO.db);

            foreach (var q in customerQuery)
            {
                Console.WriteLine("The customer {0} have order made in 1997 and live in Canada.", q.ContactName);
            }
        }
 private void Form1_Load(object sender, EventArgs e)
 {
     gb_gmm_xid = gb.Gb_GMMSM_XID;
     gb_xid = gb.Gb_XID;
 }
        /// <summary>
        /// Get multi line query
        /// </summary>
        /// <param name="context">Vestalis context</param>
        /// <param name="formCollectionFiltered">Form collection</param>
        /// <param name="fieldName">Name of the field</param>
        /// <param name="tempQuery">Temporal query</param>
        /// <param name="query1Result">First query</param>
        /// <param name="isLikeSearch">Is or not a search with like statement</param>
        /// <returns>Object query of Guid</returns>
        private static ObjectQuery<Guid> GetMultiLineQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result, bool isLikeSearch)
        {
            string multilineValue = formCollectionFiltered[fieldName];
            string queryMultiText = string.Empty;

            if (isLikeSearch)
            {
                queryMultiText = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where  FormValue.IsDeleted = false AND FormValue.TextValue like '%"
                                        + multilineValue + "%'  and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";

            }
            else
            {
                queryMultiText = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where  FormValue.IsDeleted = false AND FormValue.TextValue= '"
                                        + multilineValue + "'  and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
            }

            ObjectQuery<Guid> objQueryMultiText = new ObjectQuery<Guid>(queryMultiText, context);

            if (tempQuery == null)
                tempQuery = query1Result.Intersect(objQueryMultiText);
            else
                tempQuery = tempQuery.Intersect(objQueryMultiText);
            return tempQuery;
        }
        /// <summary>
        /// Get integer query
        /// </summary>
        /// <param name="context">Vestalis context</param>
        /// <param name="formCollectionFiltered">Form collection</param>
        /// <param name="fieldName">Name of the field</param>
        /// <param name="tempQuery">Temporal query</param>
        /// <param name="query1Result">First query</param>
        /// <returns>Object query of Guid</returns>s
        private static ObjectQuery<Guid> GetIntegerQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result)
        {
            int intValue = int.Parse(formCollectionFiltered[fieldName]);

            string queryInteger = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.IntegerValue= "
                                  + intValue.ToString() + "  and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
            ObjectQuery<Guid> objQueryInteger = new ObjectQuery<Guid>(queryInteger, context);

            if (tempQuery == null)
                tempQuery = query1Result.Intersect(objQueryInteger);
            else
                tempQuery = tempQuery.Intersect(objQueryInteger);
            return tempQuery;
        }
        /// <summary>
        /// Get date range query
        /// </summary>
        /// <param name="context">Vestalis context</param>
        /// <param name="formCollectionFiltered">Form collection</param>
        /// <param name="tempQuery">Temporal query</param>
        /// <param name="query1Result">First query</param>
        /// <returns>Object query of Guid</returns>
        private static ObjectQuery<Guid> GetDateRangeQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result)
        {
            string[] OrderDatefromValue = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("from")).Value.Split(new char[] { '/', '-' }, StringSplitOptions.RemoveEmptyEntries);
            string[] OrderDateToValue = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("to")).Value.Split(new char[] { '/', '-' }, StringSplitOptions.RemoveEmptyEntries);
            string fieldName = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("to")).Key;
            fieldName = fieldName.Remove(fieldName.Length - 2, 2);

            DateTime OrderDatefrom = new DateTime(int.Parse(OrderDatefromValue[2]), int.Parse(OrderDatefromValue[1]), int.Parse(OrderDatefromValue[0]));
            DateTime OrderDateTo = new DateTime(int.Parse(OrderDateToValue[2]), int.Parse(OrderDateToValue[1]), int.Parse(OrderDateToValue[0]));

            OrderDatefrom = OrderDatefrom.Date;
            OrderDateTo = OrderDateTo.Date.AddDays(1).AddSeconds(-1);

            string queryDate = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false and FormValue.DateValue >= DATETIME '"
                               + OrderDatefrom.ToString("yyyy-MM-dd HH:mm:ss.fffffff") + "' and FormValue.DateValue <= DATETIME '" + OrderDateTo.ToString("yyyy-MM-dd HH:mm:ss.fffffff")
                               + "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
            ObjectQuery<Guid> objQueryDate = new ObjectQuery<Guid>(queryDate, context);

            if (tempQuery == null)
                tempQuery = query1Result.Intersect(objQueryDate);
            else
                tempQuery = tempQuery.Intersect(objQueryDate);
            return tempQuery;
        }
        /// <summary>
        /// Get catalogue query
        /// </summary>
        /// <param name="context">Vestalis context</param>
        /// <param name="formCollectionFiltered">Form collection</param>
        /// <param name="fieldName">Name of the field</param>
        /// <param name="tempQuery">Temporal query</param>
        /// <param name="query1Result">First query</param>
        /// <param name="isLikeSearch">Is or not a search with like statement</param>
        /// <returns>Object query of Guid</returns>
        private static ObjectQuery<Guid> GetCatalogueQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result, bool isLikeSearch)
        {
            Guid guidValue = Guid.Empty;
            string likeValue = string.Empty;
            string queryCatalogue = string.Empty;

            if (isLikeSearch)
            {
                likeValue = formCollectionFiltered[fieldName];
                queryCatalogue = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue "
                                 + " inner join VestalisEntities.CatalogueValues as CatalogueValue on FormValue.CatalogueValueId = CatalogueValue.CatalogueValueId"
                                 + " where FormValue.IsDeleted = false"
                                 + " and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL"
                                 + " and CatalogueValue.CatalogueValueData like '%" + likeValue + "%' and CatalogueValue.IsDeleted = false";
            }
            else
            {
                guidValue = new Guid(formCollectionFiltered[fieldName]);
                queryCatalogue = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.CatalogueValueId =Guid '"
                               + guidValue.ToString() + "'  and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
            }

            ObjectQuery<Guid> objQueryGuid = new ObjectQuery<Guid>(queryCatalogue, context);

            if (tempQuery == null)
                tempQuery = query1Result.Intersect(objQueryGuid);
            else
                tempQuery = tempQuery.Intersect(objQueryGuid);
            return tempQuery;
        }
        /// <summary>
        /// Get time query
        /// </summary>
        /// <param name="context">Vestalis context</param>
        /// <param name="formCollectionFiltered">Form collection</param>
        /// <param name="fieldName">Name of the field</param>
        /// <param name="tempQuery">Temporal query</param>
        /// <param name="query1Result">First query</param>
        /// <returns>Object query of Guid</returns>
        private static ObjectQuery<Guid> GetTimeQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result)
        {
            string timeValue = formCollectionFiltered[fieldName];

            string queryTime = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.TextValue= '"
                               + timeValue + "'  and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
            ObjectQuery<Guid> objQueryTime = new ObjectQuery<Guid>(queryTime, context);

            if (tempQuery == null)
                tempQuery = query1Result.Intersect(objQueryTime);
            else
                tempQuery = tempQuery.Intersect(objQueryTime);
            return tempQuery;
        }
        public static List<TRANSFORMACION> obtenerTransformacion(string nombreTransformada,
                                                          int codigoGrupoOrigen,
                                                          int codigoMensajeOrigen,
                                                          int codigoGrupoDestino,
                                                          int codigoMensajeDestino)
        {
            StringBuilder query = new StringBuilder("");
            query.Append("Select value T from TRANSFORMACION as T where " +
                "T.TRM_NOMBRE like '%" + nombreTransformada + "%'");

            if (codigoGrupoOrigen != -1)
            {
                query.Append(" and T.MENSAJE_ORIGEN.GRUPO_MENSAJE.GMJ_CODIGO =" + codigoGrupoOrigen);
            }

            if (codigoMensajeOrigen != -1)
            {
                query.Append(" and T.MENSAJE_ORIGEN.MEN_CODIGO =" + codigoMensajeOrigen);
            }

            if (codigoGrupoDestino != -1)
            {
                query.Append(" and T.MENSAJE_DESTINO.GRUPO_MENSAJE.GMJ_CODIGO =" + codigoGrupoDestino);
            }

            if (codigoMensajeDestino != -1)
            {
                query.Append(" and T.MENSAJE_ORIGEN.MEN_CODIGO =" + codigoMensajeDestino);
            }

            using (Switch contexto = new Switch())
            {
                contexto.TRANSFORMACION.MergeOption = MergeOption.NoTracking;
                ObjectQuery<TRANSFORMACION> Transformacion = new ObjectQuery<TRANSFORMACION>(query.ToString(), contexto);

                return Transformacion.Include("MENSAJE_ORIGEN").Include("MENSAJE_ORIGEN.GRUPO_MENSAJE")
                .Include("MENSAJE_DESTINO").Include("MENSAJE_DESTINO.GRUPO_MENSAJE")
                .ToList();
            }
        }
Exemple #17
0
        //生成菜单项数据
        private List<MenuDataItem> GetMenuData(ObjectQuery DataSource)
        {
            List<MenuDataItem> menuData = new List<MenuDataItem>();

            int PrevGroupIndex = default(int);
            int CurrGroupIndex = default(int);
            Guid? PrevParent = default(Guid);
            Guid? CurrParent = default(Guid);

            foreach (View_Menu_Permission_Role item in DataSource)
            {

                CurrGroupIndex = item.GroupBy == null ? 0 : (int)item.GroupBy;
                CurrParent = item.ParentNode;

                if (CurrGroupIndex != PrevGroupIndex
                    && CurrParent == PrevParent)
                {
                    menuData.Add(new MenuDataItem(Guid.NewGuid(), item.ParentNode, "", "", "True"));
                }

                menuData.Add(new MenuDataItem(item.MenuID, item.ParentNode, item.MenuName, item.URL, "False"));

                PrevGroupIndex = item.GroupBy == null ? 0 : (int)item.GroupBy;
                PrevParent = item.ParentNode;
            }

            return menuData;
        }
        /// <summary>
        /// This method get dinamically the result according of the filters
        /// </summary>
        /// <param name="context">Database context</param>
        /// <param name="inspectionReportId">if of inspection report item</param>
        /// <param name="parameters">Parameters</param>
        /// <returns></returns>
        private static List<Guid?> GetInspectionReportsFiltered(VestalisEntities context, Guid inspectionReportId, ParameterSearchInspectionReport parameters)
        {
            List<Guid?> result = new List<Guid?>();
            string query1 = string.Empty;
            //filter FormCollection object to get a dictionary only with the key that have a value
            Dictionary<string, string> formCollectionFiltered = parameters.Collection.ToFilledDictionary();

            //get the types of the filters
            var queryServiceOrder = (from formValue in context.FormValues
                                     join inspectionReportItem in context.InspectionReportItems on formValue.InspectionReportItemId equals inspectionReportItem.InspectionReportItemId
                                     where formCollectionFiltered.Keys.Contains(formValue.FieldName) && inspectionReportItem.InspectionReportId == inspectionReportId
                                     select new { FieldName = formValue.FieldName, TypeField = formValue.FieldType }).Distinct();

            //this query filter the result by inspection report
            if (parameters.RolesForUser.Contains("Client"))
            {
                query1 = "select VALUE DISTINCT inspectionReportItem.InspectionReportItemId from VestalisEntities.InspectionReportItems as inspectionReportItem" +
            " inner join  VestalisEntities.ApprovalItems as approvalItem on inspectionReportItem.InspectionReportItemId = approvalItem.InspectionReportItemId" +
            " inner join VestalisEntities.InspectionReports as inspectionReport on inspectionReportItem.InspectionReportId = inspectionReport.InspectionReportId" +
            " inner join VestalisEntities.ServiceOrders as serviceOrder on inspectionReport.ServiceOrderId = serviceOrder.ServiceOrderId" +
            " where inspectionReportItem.IsDeleted = false AND inspectionReportItem.InspectionReportId = GUID '" + inspectionReportId.ToString() + "'" +
            " and ApprovalItem.IsDeleted = false and ApprovalItem.ApprovalStatus = 3 and ApprovalItem.CanPublish = true" +
            " and serviceOrder.BusinessApplicationId = GUID '" + parameters.BusinessApplicationId.ToString() + "'";
            }
            else
            {
                string roles = string.Empty;
                foreach (string item in parameters.RolesForUser)
                {
                    if (item == parameters.RolesForUser.Last())
                        roles += "ApprovalItem.RoleName = '" + item + "'";
                    else
                        roles += "ApprovalItem.RoleName = '" + item + "' or ";
                }
                query1 = "select VALUE inspectionReportItem.InspectionReportItemId from VestalisEntities.InspectionReportItems as inspectionReportItem" +
            " inner join  VestalisEntities.ApprovalItems as approvalItem on inspectionReportItem.InspectionReportItemId = approvalItem.InspectionReportItemId" +
            " inner join VestalisEntities.InspectionReports as inspectionReport on inspectionReportItem.InspectionReportId = inspectionReport.InspectionReportId" +
            " inner join VestalisEntities.ServiceOrders as serviceOrder on inspectionReport.ServiceOrderId = serviceOrder.ServiceOrderId" +
            " where inspectionReportItem.IsDeleted = false AND inspectionReportItem.InspectionReportId = GUID '" + inspectionReportId.ToString() + "'" +
            " and ApprovalItem.IsDeleted = false and (" + roles + ") and (ApprovalItem.ApprovalStatus = 3 or ApprovalItem.ApprovalStatus = 2)" +
            " and serviceOrder.BusinessApplicationId = GUID '" + parameters.BusinessApplicationId.ToString() + "'";
            }

            ObjectQuery<Guid> query1Result = new ObjectQuery<Guid>(query1, context);

            ObjectQuery<Guid> tempQuery = null;

            //for each result of filters, the systems perform a query to filter the corresponding value, but this iteration is not valid when in the filters exist a date range
            foreach (var item in queryServiceOrder)
            {
                string fieldName = item.FieldName;
                switch (item.TypeField)
                {
                    case (int)FieldType.Boolean:
                        tempQuery = GetBooleanQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
                        break;
                    case (int)FieldType.Catalogue:
                        tempQuery = GetCatalogueQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName));
                        break;
                    case (int)FieldType.Decimal:
                        tempQuery = GetDecimalQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
                        break;
                    case (int)FieldType.Integer:
                        tempQuery = GetIntegerQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
                        break;
                    case (int)FieldType.MultipleTextLine:
                        tempQuery = GetMultiLineQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName));
                        break;
                    case (int)FieldType.RegularExpressionText:
                        tempQuery = GetRegularExpressionQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName));
                        break;
                    case (int)FieldType.SingleTextLine:
                        tempQuery = GetSingleTextQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName));
                        break;
                    case (int)FieldType.Time:
                        tempQuery = GetTimeQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
                        break;
                    default:
                        break;
                }
            }

            //if in the filter exist a date range, the system will perform the query for filter the results
            if (formCollectionFiltered.Keys.Any(key => key.EndsWith("to") || key.EndsWith("from")))
            {
                tempQuery = GetDateRangeQuery(context, formCollectionFiltered, tempQuery, query1Result);
            }

            //retreive the results of the dynamic query
            if (tempQuery != null)
            {
                var tempResult = tempQuery.ToList();

                if (tempResult.Count > 0)
                    result = tempResult.Cast<Guid?>().ToList();
            }

            return result;
        }
        /// <summary>
        /// Get decimal query
        /// </summary>
        /// <param name="context">Vestalis context</param>
        /// <param name="formCollectionFiltered">Form collection</param>
        /// <param name="fieldName">Name of the field</param>
        /// <param name="tempQuery">Temporal query</param>
        /// <param name="query1Result">First query</param>
        /// <returns>Object query of Guid</returns>
        private static ObjectQuery<Guid> GetDecimalQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result)
        {
            decimal decimalValue = decimal.Parse(formCollectionFiltered[fieldName]);

            string queryDecimal = "select VALUE FormValue.InspectionReportItemId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.DecimalValue= "
                                  + decimalValue.ToString() + "  and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NOT NULL";
            ObjectQuery<Guid> objQueryDecimal = new ObjectQuery<Guid>(queryDecimal, context);

            if (tempQuery == null)
                tempQuery = query1Result.Intersect(objQueryDecimal);
            else
                tempQuery = tempQuery.Intersect(objQueryDecimal);
            return tempQuery;
        }
Exemple #20
0
        /// <summary>
        /// Get import file headers for profiling
        /// </summary>
        private void GetImportFileHeaders(int ImportFileId)
        {
            m_objImportFileHeaders = null;
            m_objImportFileHeaders = DataImportUtility.GetImportFileColumns(ImportFileId, true);

            this.ClearProfilingHeaderDropdown();
            riImportFileHeader.DataSource = m_objImportFileHeaders.Execute(MergeOption.AppendOnly);
            riImportFileHeader.DisplayMember = "column_name";
            riImportFileHeader.ValueMember = "id";
            riImportFileHeader.Columns.Add(new LookUpColumnInfo("column_name"));
            riImportFileHeader.ShowHeader = false;
            riImportFileHeader.ShowFooter = false;
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            //mpos_gb_gz0511Entities mpos_gb = new mpos_gb_gz0511Entities();
            GuangZhou_GnEntities gn = new GuangZhou_GnEntities();
            Guangzhou_GbEntities gb = new Guangzhou_GbEntities();
            gb.CommandTimeout = 0;

            sqlserver_mpos_gb_gz = gn.mpos_gb_gz;
            gb_dns_syn = gb.Gb_DNS_Syn;
            gb_pdp_fin = gn.Gb_PDP_Fin;
            gb_cell_syn = gn.Gb_Cell_Syn;
            gb_cell_repeat = gn.Gb_Cell_Repeat;
            mytcpsession = gb.myTcpSession;

            //gb_gz = mpos_gb.gb_gz_tousu;
        }
        /// <summary>
        /// This method get dinamically the result according of the filters
        /// </summary>
        /// <param name="context">Database context</param>
        /// <param name="parameters">Parameters</param>
        /// <returns></returns>
        private static List<Guid?> GetServiceOrdersFiltered(VestalisEntities context, ParameterSearchServicerOrder parameters)
        {
            List<Guid?> result = new List<Guid?>();

            //filter FormCollection object to get a dictionary only with the key that have a value
            Dictionary<string, string> formCollectionFiltered = parameters.FormCollection.ToFilledDictionary();

            //get the types of the filters
            var queryServiceOrder = context.FormValues.Where(data => formCollectionFiltered.Keys.Contains(data.FieldName))
                .Select(data => new { FieldName = data.FieldName, TypeField = data.FieldType }).Distinct();

            //this query filter the result by business application
            string query1 = "select VALUE ServiceOrder.ServiceOrderId from VestalisEntities.ServiceOrders as ServiceOrder where ServiceOrder.IsDeleted = false AND ServiceOrder.BusinessApplicationId = GUID '" + parameters.BusinessApplicationId.ToString() + "'";
            ObjectQuery<Guid> query1Result = new ObjectQuery<Guid>(query1, context);

            ObjectQuery<Guid> tempQuery = null;

            //for each result of filters, the systems perform a query to filter the corresponding value, but this iteration is not valid when in the filters exist a date range
            foreach (var item in queryServiceOrder)
            {
                string fieldName = item.FieldName;
                switch (item.TypeField)
                {
                    case (int)FieldType.Boolean:
                        tempQuery = GetBooleanQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
                        break;
                    case (int)FieldType.Catalogue:
                        tempQuery = GetCatalogueQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName));
                        break;
                    case (int)FieldType.Decimal:
                        tempQuery = GetDecimalQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
                        break;
                    case (int)FieldType.Integer:
                        tempQuery = GetIntegerQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
                        break;
                    case (int)FieldType.MultipleTextLine:
                        tempQuery = GetMultiLineQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName));
                        break;
                    case (int)FieldType.RegularExpressionText:
                        tempQuery = GetRegularExpressionQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName));
                        break;
                    case (int)FieldType.SingleTextLine:
                        tempQuery = GetSingleTextQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName));
                        break;
                    case (int)FieldType.Time:
                        tempQuery = GetTimeQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
                        break;
                    default:
                        break;
                }
            }

            //if in the filter exist a date range, the system will perform the query for filter the results
            if (formCollectionFiltered.Any(keyPair => keyPair.Key.EndsWith("from")) && formCollectionFiltered.Any(keyPair => keyPair.Key.EndsWith("to")))
            {
                tempQuery = GetDateRangeQuery(context, formCollectionFiltered, tempQuery, query1Result);
            }

            //retreive the results of the dynamic query
            if (tempQuery != null)
            {
                var tempResult = tempQuery.ToList();

                if (tempResult.Count > 0)
                    result = tempResult.Cast<Guid?>().ToList();
            }

            return result;
        }
 /// <summary>
 /// Execute the sql statement that comes from autocomplete field
 /// </summary>
 /// <param name="command">Command to execute</param>
 /// <returns>List of strings</returns>
 public static List<string> GetAutoCompleteItemSource(string command)
 {
     using (VestalisEntities context = new VestalisEntities())
     {
         //The system creates the object that will be executed for getting the result.
         ObjectQuery<string> result = new ObjectQuery<string>(command, context);
         //The system performs the execution of the query.
         return result.ToList();
     }
 }
Exemple #24
0
        static void Main(string[] args)
        {
            using (var context = new SampleModel1Container())
            {
                TryGenerateSampleData(context);

                IList<Project> list1 = new List<Project>();
                IList<Project> list2 = new List<Project>()
                {
                    new Project()
                    {
                        Id = 1
                    }
                };

                // Must read the data from db then using join
                var total = context.Projects.AsEnumerable().Join(list2, p => p.Id, p => p.Id, (x, y) => new { X = x, Y = y });

                var z = list1.Join(list2, p => p.Id, p => p.Id, (p1, p2) => new { });

                var q = new ObjectQuery<Project>("SELECT VALUE p FROM Projects as p WHERE p.Id > @projectId",
                    ((IObjectContextAdapter)context).ObjectContext);
                var r = q.Select("it", new ObjectParameter("projectId", 0)).ToList();

                // Create the query
                var queryString = "SELECT VALUE p FROM Projects as p WHERE p.Id > @projectId";
                var objectQuery = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Project>(queryString, new ObjectParameter("projectId", 0));

                // Get all projects satified the query
                var projects = objectQuery.ToList();

                // Select only first item
                var top1 = objectQuery.Top("1").FirstOrDefault();

                // Skip and Top, it's different than Top and Skip
                var top2 = objectQuery.Skip("it.Name DESC", "0").Top("1").ToList();

                // Get two fields
                var records = objectQuery.Select("it.Id, it.Name").Skip("it.Name", "2").Top("2");
                foreach (var record in records)
                {
                    Console.WriteLine("Id / Name: {0} / {1}", record["Id"], record["Name"]);
                }

                //
                var results =
                    context.Projects.Where(p => p.Id > 0)
                        .Select(x => new { x.Id, x.Name })
                        .OrderByDescending(x => x.Name)
                        .Skip(2)
                        .Take(2)
                        .ToList();

                var employees = context.Employees.ToList();
            }

            Console.ReadLine();

            //EntityConnection con = new EntityConnection();

            //SampleModel1Container c = new SampleModel1Container();
            //c.Database.Connection.BeginTransaction();

            //EntityCommand ec = new EntityCommand();
            //ec.ExecuteScalarAsync();

            //SqlCommand sc = new SqlCommand();
            //sc.ExecuteReaderAsync();

            //System.Data.OleDb.OleDbCommand oc = new OleDbCommand();
            //oc.ExecuteReaderAsync();
        }