Artificial Field or Anonymous Field in agregate query

7 posts, 0 answers
  1. Olivier
    Olivier avatar
    146 posts
    Member since:
    Nov 2010

    Posted 04 Feb 2015 Link to this post

    Hello

    I don't get success with my code to execute a Sql query with agregate Field.

    Have you an idea ? please.  
    EntitiesModel DbContext = new EntitiesModel();
              // Execute the statement and return a new reader
              int[] nTab          =  new int[] {0, 0, 0 };
              string cSql = "";
     
              cSql += "Select 1 as man_id, ";
              cSql += "ISNULL (SUM(CASE tmanet_id WHEN 1 THEN 1 else 0 END ),0) as Enattente, ";
              cSql += "ISNULL (SUM(CASE tmanet_id WHEN 4 THEN 1 else 0 END ),0) as NonValide, ";
              cSql += "ISNULL (SUM(CASE tmanet_id WHEN 3 THEN 1 else 0 END ),0) as Valide from mand ";
              cSql += "Where user_id=" + nUs_id;
               
              mandat oMan = DbContext.ExecuteQuery<mand>(cSql).FirstOrDefault();
     
              nTab[0] = oMan.FieldValue<int>("Enattente");
              nTab[1] = oMan.FieldValue<int>("NonValide");
              nTab[2] = oMan.FieldValue<int>("Valide");


    And after help me so for the primary key, i put a wrong cause they ask me one but the query become false with the group by.
    i don't want use primary in this query.

    thanks
    Oliver,


  2. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 06 Feb 2015 Link to this post

    Hi Oliver,

    The FieldValue<T> method is part of our Artificial API. It basically gets the value of a specified artificial field. In the provided code sample the oMan variable is of type mandat, which is a hard coded type. To achieve the result you need, I would suggest to you to try the following:

    EntitiesModel DbContext = new EntitiesModel();
    // Execute the statement and return a new reader
    int[] nTab          =  new int[] {0, 0, 0 };
    string cSql = "";
      
    cSql += "Select 1 as man_id, ";
    cSql += "ISNULL (SUM(CASE tmanet_id WHEN 1 THEN 1 else 0 END ),0) as Enattente, ";
    cSql += "ISNULL (SUM(CASE tmanet_id WHEN 4 THEN 1 else 0 END ),0) as NonValide, ";
    cSql += "ISNULL (SUM(CASE tmanet_id WHEN 3 THEN 1 else 0 END ),0) as Valide from mand ";
    cSql += "Where user_id=" + nUs_id;
                
    mandat oMan = DbContext.ExecuteQuery<mand>(cSql).FirstOrDefault();
     
    nTab[0] = oMan.Enattente;
    nTab[1] = oMan.NonValide;
    nTab[2] = oMan.Valide;

    I hope this helps. Do let us know, if you need further information or assistance.


    Regards,
    Doroteya
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Olivier
    Olivier avatar
    146 posts
    Member since:
    Nov 2010

    Posted 06 Feb 2015 in reply to Doroteya Link to this post

    Doroteya,

    i don't have a Property which name oMan.Enattente , oMan.NonValide , oMan.Valide

    How can i get a field which come from agregate or alias name with telerik data access ?

    Do you understand my problem  ?
    i choose type mandat cause , it's the table which i have calculated query so the field don't exist in the table.

    thanks
    Olivier
  5. Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 11 Feb 2015 Link to this post

    Hello Olivier,

    As I understand from your last replay, you are in case where you are executing SQL queries and materialize the result to NonPersistent Type. In this scenario you would need to define a custom CLR type that can hold the result. Otherwise it will try to populate the properties of the specified type you passed and for all other columns which are not defined will be ignored, in your case Enattente, NonValide, Valide. For more information please refer to this article.

    If any other questions arise, do not hesitate to contact us again.

    Regards,
    Boris Georgiev
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  6. Olivier
    Olivier avatar
    146 posts
    Member since:
    Nov 2010

    Posted 11 Feb 2015 in reply to Boris Georgiev Link to this post

    Hello

    I understand, in my old business object library i had a easy to way like this
    can you ask , if we can add this feature in the future ?

    using (EntitiesModel dbContext = new EntitiesModel())
    {
       string sqlQueryString = "select * from Categories";
       IEnumerable<Category> categories = dbContext.ExecuteQuery<Category>(sqlQueryString);              
    }
     
    Response.Write(categories["MyCustomField"].ToString());
    Response.Write(categories["MyCustomField2"].ToString());
     
    // Can you accept this write ?


    thanks
    Olivier
  7. Olivier
    Olivier avatar
    146 posts
    Member since:
    Nov 2010

    Posted 12 Feb 2015 Link to this post

    Hello 

    i find a way, not a great solution, but a good idea !

    I can export a Sql Statement to Datatable with telerik data access

    public DataTable GetFillData(string cSql) {
            DataTable oDt = new DataTable();
     
            if ( ! string.IsNullOrEmpty(cSql)) {
                using ( EntitiesModel dbContext = new EntitiesModel() ) {
                    using ( IDbConnection connection = dbContext.Connection ) {
                        // 2. Create a new command.
                        using ( OACommand command = this.Connection.CreateCommand()) {
                                // 3. Set the command text.
                                command.CommandText = cSql;
                                using (OADataReader executeReader = command.ExecuteReader()) {
                                    oDt.Load(executeReader);
                                }
                                command.Parameters.Clear();
                                command.Dispose();
                            }
                        }
                        dbContext.Dispose();
                }
            } // End If Not Empty cSql
     
            return oDt;
        } // End GetFillData


    to get a Value :

    DataTable oTable = This.DbContext.GetFillData("select * from customer")
     
    oTable.Rows[0]["MyCustomField"].ToString()
     
     
    // With this write you can get a Field From Variable !


    Olivier
  8. Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 16 Feb 2015 Link to this post

    Hello Olivier,

    I am glad to see that you have found a solution. Using System.Data.DataTable class to populate the result from a custom query is a valid approach, if you don't want to create a Class only for this query.

    If any other question arise do not hesitate to contact us again.

    Regards,
    Boris Georgiev
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner