Set deadlock priority with NHibernate

Sometimes you would want to explicitly set dead lock priority as part of a transaction which is not possible if you are using NHibernate as of now. In such cases you can define a custom Interceptor which does the trick. Here is a small snippet of code that can help you achieve that:

public class DeadLockPriorityInterceptor : EmptyInterceptor
{
 private ISession session;
 private string priority;

 public DeadLockPriorityInterceptor(string Priority)
 {
  priority = Priority;
 }

 public override void AfterTransactionBegin(ITransaction tx)
 {
  using (var command = session.Connection.CreateCommand())
  {
   session.Transaction.Enlist(command);
   string sql = string.Format("SET DEADLOCK_PRIORITY {0}", priority);
   // Create a SQL Command
   //System.Data.IDbCommand command = session.Connection.CreateCommand();
   // Set the query you're going to run
   command.CommandText = sql;
   // Run the query
   command.ExecuteNonQuery();
  }
 }

 public override void SetSession(ISession sessionLocal)
 {
  session = sessionLocal;
 }
}

Now your interceptor is ready. So if you would like to set your deadlock priority to High (ie.. SET DEADLOCK_PRIORITY HIGH), then just use following single line of code shown below:

ISession session = sessionFactory.OpenSession(new DeadLockPriorityInterceptor("HIGH"));
session.BeginTransaction()

This is how it works

1. sessionFactory.OpenSession will pass the interceptor to session.
2. OpenSession() verifies if there are any interceptors defined in this case it will be "DeadLockPriorityInterceptor".
3. Executes SetSession() as it is overridden by an interceptor.
4. session.BeginTransaction() will start the transaction.
5. BeginTransaction() verifies if an interceptor is defined by OpenSession().
6. Executes AfterTransactionBegin() as it is overridden by an interceptor.

That's it. NHibernate offers various interceptor points which will become quite handy at times.

“Simplicity, carried to the extreme, becomes elegance.” – Jon Franklin

Continue Reading

CompositeId() throws exception - Could not compile the mapping document: (XmlDocument)

After defining a map for composite key

 CompositeId()
                .KeyProperty(x => x.title)
                .KeyProperty(x => x.label);

The following exception is raised when trying to create a composite key

CreateSessionFactory() - Inner exception: An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.
..
CreateSessionFactory() - Inner exception: Could not compile the mapping document: (XmlDocument)
CreateSessionFactory() - Inner exception: composite-id class must override Equals(): CSharpTechies.Data.Entities.post

This means "post" entity definition should override at least 2 members GetHashCode() and Equals(object obj) Now here is how code snippet looks

public override int GetHashCode()
{
 int hashCode = 0;
 hashCode = hashCode ^ title.GetHashCode() ^ label.GetHashCode();
 return hashCode;
}

public override bool Equals(object obj)
{
 var toCompare = obj as post;
 if (toCompare == null)
 {
  return false;
 }
 return (this.GetHashCode() != toCompare.GetHashCode());
} 

Remember if the entity "post" is referenced elsewhere (ie.. foreign key) then you might get some more exceptions

CreateSessionFactory() - caught EXCEPTION: An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.
..
CreateSessionFactory() - Inner exception: Foreign key (FK36342426872A80CB:AuthorPosts [Post_id])) must have same number of columns as the referenced primary key (Post[title, label])

So update reference mapping as

  References(x => x.Post)
                .Columns(new string[] { "title", "label" })
                .Not.Update()
                .Not.Insert();

or one-to-many mapping as

 HasMany(x => x.Posts)
                .KeyColumns.Add("title")
                .KeyColumns.Add("label")
                .Not.LazyLoad()
                .Inverse();

“We have to stop optimizing for programmers and start optimizing for users.” – Jeff Atwood

Continue Reading

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Have encountered with an sql error after running following sql:

INSERT INTO [Member](FirstName, LastName, CreationDate, LastActivityDate) 
VALUES ('Saif', 'Ikram' , '13/04/2013 22:15:44', '01/01/1753 00:00:00')
go

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value". Actually there is nothing wrong with sql. Now run following sql to see if this works

set language british
go
INSERT INTO [Member](FirstName, LastName, CreationDate, LastActivityDate) 
VALUES ('Saif', 'Ikram' , '13/04/2013 22:15:44', '01/01/1753 00:00:00')
go

Now surprisingly this works, only addition is assigning language to "British". As I am using NHibernate this still is not acceptable. So I have updated my default language

USE CSharpTechies;
GO
EXEC sp_configure 'default language', 1033 ; 
GO
RECONFIGURE ;
GO

1033 for "British English" as described in msdn sys.syslanguages. But this also failed. The reason was the user that sql is run as. So update the language setting for user

EXEC sp_defaultlanguage 'saif', 'british'

sql now executes without any error

Education makes machines which act like men and produces men who act like machines. ~Erich Fromm

Continue Reading

Consuming return value from stored procedure using NHibernate without mapping

After haggling around I have found a solution that works. First I have created a stored procedure as follows:

CREATE PROCEDURE GETTOTALINCOME
AS 
DECLARE
     @TOTALINCOMECOUNT MONEY;
BEGIN
     SELECT @TOTALINCOMECOUNT = SUM(INVAMOUNT) FROM INVOICE
     RETURN @TOTALINCOMECOUNT;
END;

This is a simple stored procedure that just returns a value. Now lets look at NHibernate code block that can execute the stored procedure and read the return value.

using (var session = OpenSession())
{
     var query = session.CreateSQLQuery("EXEC GETTOTALINCOME");
     object result = query.UniqueResult();
     retVal = Convert.ToInt64(result);
}

I am getting exceptions so I started debugging it and have found "result" is null. I have looked at some articles of using stored procedure, but I do not want to define mapping either FulentNHibernate mapped classes or .hbm configuration.

I have looked again into stored procedure definition, NHibernate can read the values that are returned by a "SELECT" statement but not "RETURN". So I have changed my stored procedure as follows:

CREATE PROCEDURE GETTOTALINCOME
AS 
DECLARE
     @TOTALINCOMECOUNT MONEY;
BEGIN
     SELECT @TOTALINCOMECOUNT = SUM(INVAMOUNT) FROM INVOICE
     SELECT @TOTALINCOMECOUNT;
END;

Now this works.

“We have to stop optimizing for programmers and start optimizing for users.” – Jeff Atwood

Continue Reading