1. Tony Meißner
  2. SnapObjects
  3. Monday, 28 June 2021 14:07 PM UTC

is it possible to load a sqlselect from a model without params?

 

I have this structure:

[SqlParameter(name: "first", dataType: typeof(int))]
[SqlParameter(name: "second", dataType: typeof(int))]
[FromTable("mytable", Schema = "DBA")]

[SqlWhere("first = :first")]
[SqlAndWhere ("second = :second")]
public class MyClass
{
[Key]
[SqlColumn("first")]
public int First { get; set; }
[Key]
[SqlColumn("second")]
public string Second { get; set; }
...
}

 

and my service:

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

builder.RemoveWhere();

List<MyClass> result = (await _dataContext.SqlExecutor.SelectAsync<MyClass>(
builder,
new object[] { },
default)).ToList();

 

but it shows me:

Missing parameter value(s).

How can i dynmacilly remove the params?

David Xiong @Appeon Accepted Answer Pending Moderation
  1. Monday, 5 July 2021 10:02 AM UTC
  2. SnapObjects
  3. # 1

Hi Tony,

You will be able to delete the parameters using ISqlQueryBuilder’s RemoveWhere method in the next release. For the time being, please use the workaround you posted in the other reply.

Thanks & Regards,
David

Comment
  1. Tony Meißner
  2. Monday, 5 July 2021 10:20 AM UTC
very nice! thanks!
  1. Helpful
There are no comments made yet.
David Xiong @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 30 June 2021 10:39 AM UTC
  2. SnapObjects
  3. # 2

Hi Tony,

 

You can use SqlModelMapper’s RemoveWhere function to see if it resolves your issue. For example, you can write your service like this:

                

var builder = _dataContext.SqlModelMapper.GetQueryBuilder<MyClass>();
            
builder.RemoveWhere();
             

List<MyClass>  result = (await builder.LoadAsync()).ToList();

 

You can refer to the following link for more details:

https://docs.appeon.com/snapobjects/3.0/api_reference/SnapObjects.Data/SqlModelMapper/IQueryBuilder/Method/RemoveWhere.html

 

Regards,

David  

Comment
  1. Tony Meißner
  2. Wednesday, 30 June 2021 12:41 PM UTC
i dont want to laod all the columns, because of that i have to use .GetQueryBuilder(...);
  1. Helpful
  1. Tony Meißner
  2. Wednesday, 30 June 2021 12:49 PM UTC
.RemoveWhere() does not work. however, the builder has no where clause aynmore but the SqlParameter is still there - but i cant see it.
  1. Helpful
There are no comments made yet.
Tony Meißner Accepted Answer Pending Moderation
  1. Wednesday, 30 June 2021 09:00 AM UTC
  2. SnapObjects
  3. # 3

I have no idea how to remove the params of a ISqlQueryBuilder. The ISqlQueryBuilder.UsedParameters gives me an IReadOnlyDictionary<string, ISqlParameter> - its read only and I can't modify it.

so i coded a little workaround, its not perfect, but its works.

my service;

List<MyClass> myClasses = new List<MyClass>();

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

builder.RemoveWhere();

var dynamicModels = (await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(
builder.ToSqlString(_dataContext),
new object[] { },
default)).ToList();

foreach (var dynamicModel in dynamicModels)
myClasses .Add(new MyClass(dynamicModel));

return myClasses;

 

and my new constructor;

public MyClass(DynamicModel dynamicModel)
{
var props = typeof(MyClass).GetProperties();

foreach (var paramprop in dynamicModel?.Properties)
{
var prop = props.First(x => x.GetCustomAttribute<SqlColumnAttribute>().Column == paramprop.Name);
prop.SetValue(this, dynamicModel.GetValue(prop.GetCustomAttribute<SqlColumnAttribute>().Column));
}
}

 
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.