Drizin / Drizin/InterpolatedSql

ExplicitParameters are lost in InterpolatedSql.SqlBuilders.QueryBuilder in Build() method on line 133

Open
#21 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
279
Forks
15
PR merge metrics
No merged PRs in 30d

Description

I have an Oracle insert statement where I would like to return the inserted id.
For this i can use ParameterDirection.ReturnValue

However, I have found that it is not possible to define a ReturnValue in the interpolated string, and it is not possible to define it as an Explicit Parameter either without modifications to InterpolatedSql.Dapper.SqlBuilders.QueryBuilder

ideally i would do something like this:

```
var sql = $@"insert into users (name, surname) values ({name},{surname}) returning id into out_id";
var outParam = "out_id";
var q = sqlconnection.QueryBuilder(sql);
q.AddParameter(outParam, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
q.Execute();
//get the out param
```

but unfortunately the explicitly defined ReturnValue gets lost inside the the Build() method inside Execute().

Specifically line 133 of [QueryBuilder.cs](https://github.com/Drizin/InterpolatedSql/blob/main/src/InterpolatedSql/SqlBuilders/QueryBuilder/QueryBuilder%7BU%2CRB%2CR%7D.cs):
`combinedQuery = _combinedBuilderFactory2(Options, new StringBuilder(Format), SqlParameters.ToList());`

I have found that the following hack is sufficient:

```
public class CustomQueryBuilder : InterpolatedSql.Dapper.SqlBuilders.QueryBuilder
{
public CustomQueryBuilder(IDbConnection connection) : base(connection)
{
}

public override IDapperSqlCommand Build()
{

IDapperSqlBuilder combinedQuery;
if (_combinedBuilderFactory1 == null || _combinedBuilderFactory2 == null)
return null!; // initializing

// An initial template may or may not have been provided
if (base.IsEmpty)
combinedQuery = _combinedBuilderFactory1(Options);
else
{
combinedQuery = _combinedBuilderFactory2(Options, new StringBuilder(Format), SqlParameters.ToList());
//start customisation of the Build() method
foreach (var p in ExplicitParameters)
{
combinedQuery.AddParameter(p);
}
//end customisation of the Build() method
}

//the rest of this method has been omitted from here for brevity.
}
}
```

if i call it as follows then the insert id is returned by dapper correctly:

```
var sql = $@"insert into users (name, surname) values ({name},{surname}) returning id into out_id";
var outParam = "out_id";
var q = new CustomQueryBuilder(sqlconnection);
q.AppendFormattableString(sql);
q.AddParameter(outParam, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
var r = q.Build();
q.DbConnection.Execute(sql: r.Sql, param: ParametersDictionary.LoadFrom(r));
var result = r.DapperParameters.Get(outParam);
```

I would be interested to know if there are any supported ways of achieving the same, or perhaps support for returning id into out_id could be added?

Many Thanks

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.