so there is my temporarily solution, until stored procedures will be native supported by NH. This is of course not ideal, but it meet all my requirements:
In my class i have created new interface and implemented it:
Code:
public interface IStoredProcSupport
{
IDbCommand CreateStoredProcCommand(string procedureName, IDbConnection connection);
IDataParameter CreateStoredProcParameter(string name, int size, ParameterDirection direction, object value);
}
public class SqlClientDriver : NHibernate.Driver.SqlClientDriver, IStoredProcSupport
{
public SqlClientDriver() : base()
{
}
public IDbCommand CreateStoredProcCommand(string procedureName, IDbConnection connection)
{
IDbCommand cmd = base.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = procedureName;
cmd.Connection = connection;
return cmd;
}
public IDataParameter CreateStoredProcParameter(string name, int size, ParameterDirection direction, object value)
{
SqlParameter param = new SqlParameter();
param.ParameterName = base.FormatNameForParameter(name);
param.Size = size;
param.Direction = direction;
param.Value = value;
return param;
}
}
in app.config i must change driver:
Code:
<nhibernate>
...
<!--<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />-->
<add key="hibernate.connection.driver_class" value="Msmis.Nhb.SqlClientDriver, Msmis.Nhb" />
...
</nhibernate>
in my DAL layer:
Code:
protected virtual IDbCommand CreateStoredProcCommand(string procName)
{
CheckForIStoredProcSupport();
return ((IStoredProcSupport)m_session.SessionFactory.ConnectionProvider.Driver).CreateStoredProcCommand(procName, m_session.Connection);
}
protected virtual IDataParameter CreateStoredProcParameter(string name, int size, ParameterDirection direction, object value)
{
CheckForIStoredProcSupport();
return ((IStoredProcSupport)m_session.SessionFactory.ConnectionProvider.Driver).CreateStoredProcParameter(name, size, direction, value);
}
private void CheckForIStoredProcSupport()
{
NHibernate.Driver.IDriver driver = m_session.SessionFactory.ConnectionProvider.Driver;
if (!(driver is IStoredProcSupport))
throw new NotSupportedException("Used database driver '" + driver.GetType().ToString() + "' does not implement interface 'Msmis.Nhb.IStoredProcSupport' and therefore don't support stored procedures.");
}
and of course there is a fragment stored procedure wrapper:
Code:
public bool IsInRole(string userName, string roleName)
{
IDbCommand cmd = CreateStoredProcCommand("sp_SEC_IsInRole");
cmd.Parameters.Add(CreateStoredProcParameter("UserName", 30, ParameterDirection.Input, userName));
cmd.Parameters.Add(CreateStoredProcParameter("RoleName", 30, ParameterDirection.Input, roleName));
IDataParameter output = CreateStoredProcParameter("Result", 0, ParameterDirection.Output, (int)0);
cmd.Parameters.Add(output);
cmd.ExecuteNonQuery();
return Convert.ToBoolean((int)output.Value);
}
This is just only for MsSql..., but could be created for any of the existing driver.
Maybe this code fragments will be usefull for somebody who has the same problem.
Login
FAQ
Search