Artificial Field or Anonymous Field in agregate query

[Olivier]

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,

[Doroteya]

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

 

 

[Olivier]

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

[Boris Georgiev]

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

 

 

[Olivier]

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

[Olivier]

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

[Boris Georgiev]

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