Getting unique identifier primary key from SQL Server on INSERT

ServiceStack.OrmLite SQL Server
1

I have a table that has an Id field which is a primary key and is a uniqueidentifier type.

The type class is decorated with the following attributes

[PrimaryKey]
[Default(typeof(Guid), "newid()")]
public Guid Id { get; set; }

How can I get the ID of newly created record?

Insert only returns the last “AutoIncremented” ID as a long and Save just returns a boolean dependant on whether it’s created.

I need the ID to be generated by the database, so can’t create the GUID in code.

2

For Guid primary keys the client usually sets it, there’s no built in way in OrmLite to fetch a RDBMS generated Guid which is specific to SQL Server 2005+ and required non-standard SQL which you’d need to drop down to use Custom SQL to access.

To use OrmLite to generate the Insert you’ll need to manually modify the generated SQL and read the Scalar OUTPUT with something like:

var obj = new Table { ... };

using (var db = OpenDbConnection())
using (var cmd = db.CreateCommand())
{
    cmd.GetDialectProvider().PrepareParameterizedInsertStatement<Table>(cmd,
        insertFields: OrmLiteUtils.GetNonDefaultValueInsertFields(obj));

    cmd.CommandText = cmd.CommandText.Replace("VALUES", "OUTPUT inserted.Id VALUES");

    cmd.GetDialectProvider().SetParameterValues<Table>(cmd, obj);

    var insertedId = (Guid)cmd.ExecuteScalar();
}
1 Like
3

Perfect, works a treat. thanks for the quick reply. :thumbsup: