This question is locked. New answers and comments are not allowed.
Hello
I don't get success with my code to execute a Sql query with agregate Field.
Have you an idea ? please.
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,
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,
6 Answers, 1 is accepted
0
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:
I hope this helps. Do let us know, if you need further information or assistance.
Regards,
Doroteya
Telerik
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.
0
Olivier
Top achievements
Rank 2
answered on 06 Feb 2015, 08:18 AM
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
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
0
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
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.
0
Olivier
Top achievements
Rank 2
answered on 11 Feb 2015, 09:11 AM
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 ?
thanks
Olivier
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
0
Olivier
Top achievements
Rank 2
answered on 12 Feb 2015, 02:29 PM
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
to get a Value :
Olivier
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
0
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
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.