CODEWITHSUNDEEP
c#sql-server-2008asp.net-mvc-5dapperdapper-extensions
Bagzli
Bagzli

Reputation: 6569

Insert Guid and retrieve value with dapper

I am trying to insert a record into my database and retrieve the GUID it just added in.

Let's say if I have a table with 3 columns, GUID, FirstName, LastName. I need to insert a new record and then get back the GUID that was just generated. The problem is that first and last name are duplicated, often. I am not quite sure how to accomplish

Here is what I tried, I know the below won't work as I am not really telling it which column to select back and I'm not sure how to tell it:

var query = @"INSERT INTO MyTable(GUID, FirstName, LastName)
                  SELECT
                      @GUID, @FirstName, @LastName);

using (var oConn = CreateConnection())
{
    var test = oConn.Query<string>(query, new
            {
                GUID = Guid.NewGuid(), 
                "John", 
                "Doe"
            }).Single();
}

The error that I get is

Sequence contains no elements

Upvotes: 2

Views: 7423

Answers (2)

Sarfaraz Farooqui
Sarfaraz Farooqui

Reputation: 81

Dapper Contrib needs a Auto Generated ID, It cannot be a GUID and you cannot pass a pre generated Guid

Upvotes: 0

Shyju
Shyju

Reputation: 218722

If you want only the Guid which you inserted, Why not store it in a local variable in your code and use that as needed ?

I also see some errors in your code. The below code is corrected and should work. 

var guid = Guid.NewGuid();
var query = @"INSERT INTO
              MyTable (GUID, FirstName, LastName) values ( @GUID, @FirstName,@LastName);";
using (var conn =  CreateConnection())
{
    conn.Execute(query, new {  @GUID = guid, @FirstName= "John", @LastName= "Scott" });
}
// You can use the value in guid variable now. It will be Id you inserted just now

Upvotes: 3

Related Questions