Support for ExecuteScalar

[GoogleCodeExporter]

I would appreciate support for ExecuteScalar.
This makes it easy to to do inserts and get the generated row id as a result.

I've implemented this based on the ExecuteCommand implementation and it works 
for me so far.

Original issue reported on code.google.com by marnixva...@gmail.com on 9 May 2011 at 10:18

Attachments:

• ExecuteScalar.cs

[GoogleCodeExporter]

I kind of dislike executescalar it is so inflexible. 

This gives you pretty much what you want. 

var id = Query<int>("insert table values(@a) select SCOPE_IDENTITY()", 
new{a}).First()

Original comment by sam.saff...@gmail.com on 15 May 2011 at 11:45

[GoogleCodeExporter]

What about having a QueryOne that just wraps Query and does the First for you? 
It will make code look a little cleaner.

Original comment by james.ne...@gmail.com on 19 May 2011 at 11:39

[GoogleCodeExporter]

Scope_Identity() returns decimal.

Original comment by mcnamara...@gmail.com on 21 May 2011 at 10:01

[GoogleCodeExporter]

mcnamara thanks for the correction 

James, we are trying for a minimal yet complete API with dapper. Adding a 
queryone feels wrong to me, also what are the semantics, is it First? is it 
FirstOrDefault? is it First And enforce 1 result? 

Original comment by sam.saff...@gmail.com on 23 May 2011 at 12:35

[GoogleCodeExporter]

FirstOrDefault if the result is nullable, or First and an exception thrown if 
the result is not nullable makes sense to me.

Querying a single result is a pretty common operation and I'd expect a method 
for it if I was the average user. Calling a method that returns a list and then 
having to use LINQ to filter it down to one result isn't helping users fall 
into the pit of success.

Original comment by james.ne...@gmail.com on 27 May 2011 at 3:24

[GoogleCodeExporter]

Actually, I think Single for non-nullable and SingleOrDefault for nullable 
would be more semantically correct. We're requesting a single value after all.

Anyway, since the SqlMapper class is now partial it's really easy to add an 
ExecuteScalar or QueryOne implementation yourself without modifying the 
original source code. 
That makes me much more sympathetic to Sam's argument of keeping a minimal API. 

Maybe this feature can be added to the Contrib project?

Original comment by marnixva...@gmail.com on 31 May 2011 at 8:48

[GoogleCodeExporter]

marnix, james 

I am declining this for dapper, open to having this in dapper contrib, with the 
correct semantics ... if we can figure them out 

if you do a QueryOne and get 2 results, should you exception? if not then it 
should really be called First ... 

Original comment by sam.saff...@gmail.com on 1 Jun 2011 at 9:19

• Changed state: DapperContribProposed

[GoogleCodeExporter]

The easiest method I have found for returning the generated row id is as 
follows:

                var p = new DynamicParameters();

                p.Add("@ServerIP", "192.168.100.200");
                p.Add("@PartName", "Dapper");
                p.Add("@Config_Data_Modified", false);
                p.Add("@LatestRecord", false);
                p.Add("@IsRunning", true);
                p.Add("@Stopped", false);
                p.Add("@SessionID", dbType: DbType.Int32, direction: ParameterDirection.Output);

                connection.Execute(
                    "DECLARE @TmpTable TABLE (ID INT) " +
                    "INSERT Session(ServerIP, PartName, Config_Data_Modified, LatestRecord, IsRunning, Stopped) " +
                    "OUTPUT Inserted.SessionID INTO @TmpTable " +
                    "VALUES (@ServerIP, @PartName, @Config_Data_Modified, @LatestRecord, @IsRunning, @Stopped)" +
                    "SELECT @SessionID = ID FROM @TmpTable", p);

                var sessionID = p.Get<Int32>("@SessionID");

Original comment by david....@gmail.com on 23 Jul 2011 at 3:40

[GoogleCodeExporter]

SCOPE_IDENTITY() would be easier, and you can create the dynamic param from an 
anon object to set up the 1-way params, then add the 2-way/out.

Re scalar - First() is simplest

Original comment by marc.gravell on 23 Jul 2011 at 3:34

[GoogleCodeExporter]

var id = Query<int>("insert table values(@a) select SCOPE_IDENTITY()", 
new{a}).First()

Seems to perfectly do the job and enforce that we actually get a result. I 
personally see no need for ExecuteScalar, but it would not cost too much to add 
it if there was a relevant use case we did not think of.

Original comment by trader...@gmail.com on 29 Sep 2011 at 9:01

[GoogleCodeExporter]

Guy, don't forget to cast. Both "SELECT @@IDENTITY" and "SELECT 
SCOPE_IDENTITY()" return decimal.

int id = (int)Query<decimal>("insert blha select @@idenitty").First();

Original comment by jitbit on 3 Oct 2011 at 9:47

[GoogleCodeExporter]

Yes, and I received a long for other queries.

Original comment by trader...@gmail.com on 4 Oct 2011 at 2:45

[GoogleCodeExporter]

Implemented as ExecuteWithIdentity in Dapper.Microsoft.Sql

Original comment by ddaniele...@gmail.com on 1 Nov 2013 at 7:24

[GoogleCodeExporter]

With async and await keyword it is now a good idea to have ExecuteScalar and 
ExecuteScalarAsync. The work around doesn't really work for me if I want to 
return Task<int> without awaiting.

Right now I would have to do the following:

public async Task<int> Insert()
{
   var sql = "INSERT ...; SELECT CAST(scope_identity() as int);";
   var q = await _connection.QueryAsync<int>(sql);
   return q.SingleOrDefault();
}

But if I don't want to await, I now have to expose it as 

public Task<IEnumerable<int>> Insert()
{
   var sql = "INSERT ...; SELECT CAST(scope_identity() as int);";
   var q = _connection.QueryAsync<int>(sql);
   return q;
}

and let the caller do the SingleOrDefault(), which is not very clean. Unless 
you can see a problem with this implementation and have a better suggestion

Original comment by jstaw...@gmail.com on 18 Jul 2014 at 7:04

[GoogleCodeExporter]

Added

Original comment by marc.gravell on 5 Aug 2014 at 3:09

• Changed state: Fixed