How to add ID before using NHibernate

CREATE TABLE Cats(ID int, Name varchar( 25))

public class CatMap: ClassMap
{
 public CatMap()
 {
 Id(m=>m.ID).GeneratedBy.Increment();
 Map(m=>.Name);
 }
 }

using System;
using System.Collections .Generic;
using System.Data;
using NHibernate.Dialect;
using NHibernate.Engine;
using NHibernate.Id;
using NHibernate.Persister.Entity; 
using NHibernate.Type;

namespace YourCompany.Stuff
{
 public class IdGenerator: IIdentifierGenerator, IConfigurable
 {
 private string _tableName; 
 // The "select max(id) ..." query will go into this proc:
 private const string DefaultProcedureName = "dbo.getId";

 public string ProcedureName {get; protected set; }
 public string TableNameParameter {get; protected set; }
 public string OutputParameter {get; protected set; }

 public IdGenerator()
 {
 ProcedureName = DefaultProcedureName;
 TableNameParameter = "@tableName";
 OutputParameter = "@newID";
 }

 public object Generate(ISessionImplementor session, object obj)
 {
 int newId;
 using (var command = session.Connection.CreateCommand())
 {
 var tableName = GetTableName(session, obj.GetType());

 command.CommandType = CommandType.StoredProcedure;
 command.CommandText = ProcedureName;

 // Set input parameters
 var parm = command.CreateParameter();
 parm.Value = tableName;
 parm. ParameterName = TableNameParameter;
 parm.DbType = DbType.String;

 command.Parameters.Add(parm);

 // Set output parameter
 var outputParameter = command.CreateParameter();
 outputParameter.Direction = ParameterDirection.Outpu t;
 outputParameter.ParameterName = OutputParameter;
 outputParameter.DbType = DbType.Int32;

 command.Parameters.Add(outputParameter);

 / / Execute the stored procedure
 command.ExecuteNonQuery();

 var id = (IDbDataParameter)command.Parameters[OutputParameter];

 newId = int.Parse( id.Value.ToString());

 if (newId <1)
 throw new InvalidOperationException(
 string.Format("Could not retrieve a new ID with proc {0 } for table {1}",
 ProcedureName,
 tableName));
 }

 return newId;
 }

 public void Configure(IType type, IDictionary parms, Dialect dialect)
 {
 _table Name = parms["TableName"];
 }

 private string GetTableName(ISessionImplementor session, Type objectType)
 {
 if (string.IsNullOrEmpty(_tableName)) 
 {
 //Not set by configuration, default to the mapped table of the actual type from runtime object:
 var persister = (IJoinable)session.Factory.GetClassMetadata(objectType);

 var qualifiedTableName = persister.TableName.Split('.');
 _tableName = qualifiedTableName[qualifiedTableName.GetUpperBound(0)]; //Get last string
 }
 
 return _tableName;
 }
 }
}

It seems that NH only gets MAX(ID) once, in the first When inserting once and then storing the value internally, this will cause some problems when other processes insert data. Then I don’t have the actual ID and throw a duplicate key exception.

Let’s imagine we have Table Cats

CREATE TABLE Cats(ID int, Name varchar(25))

Then we use FluentNhibernate to complete the corresponding mapping

public class CatMap: ClassMap
{
 public CatMap()
 {
 Id(m=>m.ID).GeneratedBy.Increment();
 Map(m=>.Name);
 }
}

What I want to achieve is to use SELECT MAX(ID) FROM Cats to insert my Cat record before any insertion, and its ID is generated by NHibernate. After any dosnt work is submitted, Session.Flush is executed I did some investigations using SQL Server profiler, this sql stetement is executed only once (at the first insert)-other inserts do not force the retrieval of the actual MAX(ID). I know that other algorithms like HiLo are better, But I cannot replace it.

As you know, the NHibernate Increment id generator is not suitable for multi-user environments. You stated that using the HiLo generator is not an option , So you can choose the following options:

>Use the Native generator and change the id column to use the identification mechanism supported by the database
>Use the Assigned generator and write code to determine the next valid ID
>Create a custom generator in which you can implement the IIdentifierGenerator interface to perform the required operations

Below is the sample code of the custom generator, which uses a general proc to get the Set the ID of the table. The main problem with this method is that you have to wrap the code into something similar to the Unit of Work mode to ensure that the’select max(id)…’ and insert are covered by the same database transaction. The IIdentifierGenerator link has XML To map you need to connect this custom generator.

using System;
using System.Collections.Generic;
using System.Data;
using NHibernate.Dialect;
using NHibernate.Engine;
using NHibernate.Id;
using NHibernate.Persister.Entity;
using NHibernate.Type;

namespace YourCompany.Stuf f
{
 public class IdGenerator: IIdentifierGenerator, IConfigurable
 {
 private string _tableName;
 // The "select max(id) ..." query will go into this proc:
 private const string DefaultProcedureName = "dbo.getId";

 public string ProcedureName {get; protected set; }
 public string TableNameParameter {get; protected set; }
 public string OutputParameter {get; protected set; }

 public IdGenerator()
 {
 ProcedureName = DefaultProcedureName;
 TableNameParameter = "@tableName" ;
 OutputParameter = "@newID";
 }

 public object Generate(ISessionImplementor session, object obj)
 {
 int newId;
 using (var command = session.Connection.CreateCommand())
 {
 var tableName = GetTableName(session, obj.GetType());

 command.CommandType = CommandType.StoredProcedure;
 command.CommandText = ProcedureName;

 // Set input parameters
 var parm = command.CreateParameter(); 
 parm.Value = tableName;
 parm.ParameterName = TableNameParameter;
 parm.DbType = DbType.String;

 command.Parameters.Add(parm);< br />
 // Set output parameter
 var outputParameter = command.CreateParameter();
 outputParameter.Direction = ParameterDirection.Output;
 outputParameter.ParameterName = OutputParameter;
 outputParameter.DbType = DbType.Int32;

 command.Parameters.Add(outputParameter);

 // Execute the stored procedure
 command.ExecuteNonQuery(); 

 var id = (IDbD ataParameter)command.Parameters[OutputParameter];

 newId = int.Parse(id.Value.ToString());

 if (newId <1)
 throw new InvalidOperationException(
 string.Format("Could not retrieve a new ID with proc {0} for table {1}",
 ProcedureName,
 tableName));
} 

 return newId;
 }

 public void Configure(IType type, IDictionary parms, Dialect dialect)
 {
 _tableName = parms["TableName"];
 }

 private string GetTableName(ISessionImplementor session, Type objectType)
 {
 if (string.IsNullOrEmpty(_tableName) )
 {
 //Not set by configuration, default to the mapped table of the actual type from runtime object:
 var persister = (IJoinable)session.Factory.GetClassMetadata(objectType);

 var qualifiedTableName = persister.TableName.Split('.');
 _tableName = qualifiedTableName[qualifiedTableName.GetUpperBound(0 )]; //Get last string
 }

 return _tableName;
 }
 }
}