Drizin / Drizin/InterpolatedSql
ExplicitParameters are lost in InterpolatedSql.SqlBuilders.QueryBuilder in Build() method on line 133
- 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.