1. Tony Meißner
  2. SnapObjects
  3. Friday, 25 June 2021 10:05 AM UTC

 

How can i retrieve Data from Model using the sqlselect attribute and paramvalues?

I already ask a stackoverflow question;

https://stackoverflow.com/questions/68129138

 

How can I retrieve data from my SQL-Model using the SqlSelect attribute??? For now i do this;

```c#
[SqlParameter(name: "first", dataType: typeof(int))]
[SqlParameter(name: "second", dataType: typeof(string))]
[SqlParameter(name: "third", dataType: typeof(DateTime))]
[FromTable("mytable", Schema = "DBA")]
[SqlSelect("mycustomselect", RawSelect = "someproperty, otherproperty")]
public class MyClass
{
[Key]
[SqlColumn("first")]
public int First { get; set; }
[Key]
[SqlColumn("second")]
public string Second { get; set; }
[Key]
[SqlColumn("third")]
public DateTime Third { get; set; }
...
[SqlColumn("someproperty")]
public int Someproperty { get; set; }
[SqlColumn("otherproperty")]
public int Otherproperty { get; set; }
}
```
in my service I want to load this custom sqlSelect;
```c#
public async Task<MyClass> RetrieveOneAsync(int first, string second, DateTime third, string sqlselect, CancellationToken cancellationToken)
{
Myclass myclass;

var builder = ModelSqlBuilder.GetBuilder<Myclass>(_dataContext)
.GetQueryBuilder(sqlselect);

myclass = await _dataContext.SqlExecutor
.SelectOneAsync<Myclass>(
builder,
new object[]
{
first,
second,
third
},
cancellationToken);

return myclass;
}
```
i also tried it with a ParamValue;
```c#
public async Task<MyClass> RetrieveOneAsync(int first, string second, DateTime third, string sqlselect, CancellationToken cancellationToken)
{
Myclass myclass;

ParamValue firstParam = ParamValue.Input<int>("first", first);
ParamValue secondParam = ParamValue.Input<string>("second", second);
ParamValue thirdParam = ParamValue.Input<DateTime>("third", third);

var builder = ModelSqlBuilder.GetBuilder<Myclass>(_dataContext)
.GetQueryBuilder(sqlselect);

myclass = await _dataContext.SqlExecutor
.SelectOneAsync<Myclass>(
builder,
new object[]
{
firstParam,
secondParam,
thirdParam
},
cancellationToken);

return myclass;
}
```
and my call;
```c#
var myclass = await RetrieveOneAsync(1, "test", DateTime.Now, "mycustomselect", default);
```
Everytime i didnt get the right row. I think there are some problems with my parameters. Maybe the DateTime value cause some problems.

 

I already read how to get the SQLSelect (this works):
[GetQueryBuilder](https://docs.appeon.com/snapobjects/3.0/api_reference/SnapObjects.Data/ModelSqlBuilder/Method/GetQueryBuilder.html)
[SqlSelectAttribute](https://docs.appeon.com/snapobjects/3.0/api_reference/SnapObjects.Data/ModelAttribute/Class/SqlSelectAttribute/SqlSelectAttribute.html)

but i dont get the right row from the table. Maybe i have to use a different function.
[SelectOneAsync](https://docs.appeon.com/snapobjects/3.0/api_reference/SnapObjects.Data/SqlExecutor/ISqlExecutor/Method/SelectOneAsync.html)

 

Accepted Answer
David Xiong @Appeon Accepted Answer Pending Moderation
  1. Monday, 28 June 2021 08:18 AM UTC
  2. SnapObjects
  3. # Permalink

Hi Tony,

After defining the parameter using SqlParamenter attribute in the model, you also need to use the SqlWhere attribute to specify the retrieve argument for retrieving data. For example:

[SqlParameter(name: "first", dataType: typeof(int))]
[SqlParameter(name: "second", dataType: typeof(string))]
[SqlParameter(name: "third", dataType: typeof(DateTime))]
[FromTable("mytable", Schema = "DBA")]
[SqlSelect("mycustomselect", RawSelect = "someproperty, otherproperty")]

[SqlWhere("First = :first")]
[SqlAndWhere ("second = :second")]
[SqlAndWhere ("third = :third")]
public class MyClass
{
[Key]
[SqlColumn("first")]
public int First { get; set; }
[Key]
[SqlColumn("second")]
public string Second { get; set; }
[Key]
[SqlColumn("third")]
public DateTime Third { get; set; }
...
[SqlColumn("someproperty")]
public int Someproperty { get; set; }
[SqlColumn("otherproperty")]
public int Otherproperty { get; set; }
}

You can refer to the following link for more details:

      https://docs.appeon.com/snapobjects/3.0/api_reference/SnapObjects.Data/ModelAttribute/Class/SqlParameterAttribute/SqlParameterAttribute.html

https://docs.appeon.com/snapobjects/3.0/api_reference/SnapObjects.Data/ModelAttribute/Class/SqlWhereAttribute/SqlWhereAttribute.html

https://docs.appeon.com/snapobjects/3.0/api_reference/SnapObjects.Data/ModelAttribute/Class/SqlAndWhereAttribute/SqlAndWhereAttribute.html

 

Thanks & Regards,

David

Comment
  1. Tony Meißner
  2. Monday, 28 June 2021 08:55 AM UTC
very nice, works perfectly! thx
  1. Helpful
There are no comments made yet.
Francisco Martinez @Appeon Accepted Answer Pending Moderation
  1. Friday, 25 June 2021 13:10 PM UTC
  2. SnapObjects
  3. # 1

Hi Tony,

I'm glad you already found a solution to your problem! It would be great if you could also post the solution in here to help the community find the answer faster

Regards,
Francisco

Comment
There are no comments made yet.
Tony Meißner Accepted Answer Pending Moderation
  1. Friday, 25 June 2021 13:21 PM UTC
  2. SnapObjects
  3. # 2

sure;

 

ok i solved it. However i have to manually add the where clause instead of use the params.

```c#
public async Task<MyClass> RetrieveOneAsync(int first, string second, DateTime third, string sqlselect, CancellationToken cancellationToken)
{
Myclass myclass;

var builder = ModelSqlBuilder.GetBuilder<Myclass>(_dataContext)
.GetQueryBuilder(sqlselect);

builder
.WhereValue("first", first)
.AndWhereValue("second", second)
.AndWhereValue("third", third);

myclass = await _dataContext.SqlExecutor.SelectOneAsync<MyClass>(
builder.ToSqlString(_dataContext));

return myclass;
}
```

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.