时间戳

首页 > 解决方案 > c# SnapObjects.Data 使用 SqlSelect 属性从模型中检索数据

c# - c# SnapObjects.Data 使用 SqlSelect 属性从模型中检索数据

问题描述

如何使用 SqlSelect 属性从我的 SQL 模型中检索数据？？？现在我这样做；

[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; }
}

在我的服务中，我想加载这个自定义 sqlSelect；

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;
}

我也用 ParamValue 尝试过；

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;
}

和我的呼唤；

var myclass = await RetrieveOneAsync(1, "test", DateTime.Now, "mycustomselect", default);

每次我没有得到正确的行。我认为我的参数有一些问题。也许 DateTime 值会导致一些问题。

我已经阅读了如何获取 SQLSelect（这有效）： GetQueryBuilder SqlSelectAttribute

但我没有从表中得到正确的行。也许我必须使用不同的功能。 SelectOneAsync

标签： c#sql

解决方案

好的，我解决了。但是我必须手动添加 where 子句而不是使用参数。

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;
}

推荐阅读