Using GUID with SQL Server and NHibernate.

Question

I'm running NHibernate and SQL Server CE I am trying to use GUIDs as my ID column. This is the code I already have:

Mapping:

  <class name="DatabaseType" table="DBMON_DATABASE_TYPE">
    <id name="Id" column="DATABASE_TYPE_ID">
      <generator class="guid" />
    </id>
    <property name="DispName" />
  </class>

And this is the create statement it creates:

create table DBMON_DATABASE_TYPE (
    DATABASE_TYPE_ID BIGINT not null,
   DispName NVARCHAR(255) null,
   primary key (DATABASE_TYPE_ID)
)

And this is the kind of insert statement I want to be able to run on it:

Insert into DBMON_DATABASE_TYPE (DATABASE_TYPE_ID,DISPNAME) values ('f5c7181e-e117-4a98-bc06-733638a3a264','DOC')

And this is the error I get when I try that:

Major Error 0x80040E14, Minor Error 26306
> Insert into DBMON_DATABASE_TYPE (DATABASE_TYPE_ID,DISPNAME) values ('f5c7181e-e117-4a98-bc06-733638a3a264','DOC')
Data conversion failed. [ OLE DB status value (if known) = 2 ]

Once again my goal is to be able to use GUIDs as the ID column of my table, they don't even need to be auto generated, I can generate them manually in the Save/SaveOrUpdate methods of NHibernate. If there is any other information you need to know please let me know!

Answer 1

instead of

DATABASE_TYPE_ID BIGINT not null,

it needs to be

DATABASE_TYPE_ID UNIQUEIDENTIFIER not null,

I would strongly advice you to use NEWSEQUENTIALID() as a default instead since you are using it as a clustered index because it won't cause page splits and thus fragmentation, see here how to use it http://msdn.microsoft.com/en-us/library/ms189786.aspx

Answer 2

In your mapping you need to identify that you want NHibernate to create a GUID in the database schema (it looks like you are generating your schema from the nhibernate mapping). Off the top of my head the following should work:

<id name="Id" column="DATABASE_TYPE_ID" type="Guid">
  <generator class="guid" />
</id>

Answer 3

Dare I recommend using GuidComb's instead?