c# nhibernate fluent mysql

See more: MySQLStored Hi to all.

I have a proyect with c#+nhibernate+fluent+mysql

I don't understand why i have to map the tables in hbm xml file, when this information is in database, but this is other discussions.

My proyect contains this file hbm for store procedure

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="PigCsharp.Entities" assembly="PigCsharp">
  <sql-query name="getparametersbyparameter">
    <query-param name="iParameter" type="string" />
    <return class="mntparameters">
      <return-property column="mntparameters.ident" name="ident" />
      <return-property column="mntparameters.User" name="User" />
      <return-property column="mntparameters.Role" name="Role" />
      <return-property column="mntparameters.Parameter" name="Parameter" />
      <return-property column="mntparameters.value" name="value" />
      <return-property column="mntparameters.Module" name="Module" />
    </return>
    exec getparametersbyparameter   @iParameter = :iParameter
    <!--{ call getparametersbyparameter(:iParameter) }-->
  </sql-query>
</hibernate-mapping>

when I run my code in c#

string param = "getDefaultDatabase";
                var query = session.GetNamedQuery("getparametersbyparameter")
                .SetParameter<string>("iParameter", param).List<mntparameters>();

I have this exception

No se controló NHibernate.Exceptions.GenericADOException
  Message=could not execute query
[ exec getparametersbyparameter   @iParameter = ?p0 ]
  Name:iParameter - Value:getDefaultDatabase
[SQL: exec getparametersbyparameter   @iParameter = ?p0]
  Source=NHibernate
  SqlString=exec getparametersbyparameter   @iParameter = ?p0
  StackTrace:
       en NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:línea 1703
       en NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:línea 1601
       en NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:línea 1595
       en NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Custom\CustomLoader.cs:línea 272
       en NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:línea 2052
       en NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:línea 2035
       en NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl开发者_开发百科.cs:línea 2021
       en NHibernate.Impl.SqlQueryImpl.List[T]() en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SqlQueryImpl.cs:línea 163
       en PigCsharp.forms.FrmMDIExpedientacion.FrmMDIExpedientacion_Load(Object sender, EventArgs e) en C:\Code\PigCsharp\PigCsharp\forms\frmMDIExpedientacion.cs:línea 778
       en System.Windows.Forms.Form.OnLoad(EventArgs e)
       en System.Windows.Forms.Form.OnCreateControl()
       en System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       en System.Windows.Forms.Control.CreateControl()
       en System.Windows.Forms.Control.WmShowWindow(Message& m)
       en System.Windows.Forms.Control.WndProc(Message& m)
       en System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       en System.Windows.Forms.Form.WmShowWindow(Message& m)
       en System.Windows.Forms.Form.WndProc(Message& m)
       en System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       en System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       en System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       en System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, Int32 wParam, Int32 lParam)
       en System.Windows.Forms.Form.SetVisibleCore(Boolean value)
       en System.Windows.Forms.Control.set_Visible(Boolean value)
       en System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       en System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       en System.Windows.Forms.Application.Run(Form mainForm)
       en PigCsharp.forms.submain.Main() en C:\Code\PigCsharp\PigCsharp\main\submail.cs:línea 29
       en System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       en System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       en Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       en System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       en System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       en System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       en System.Threading.ThreadHelper.ThreadStart()
  InnerException: MySql.Data.MySqlClient.MySqlException
       Message=Fatal error encountered during command execution.
       Source=MySql.Data
       ErrorCode=-2147467259
       Number=0
       StackTrace:
            en MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
            en MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            en System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
            en NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:línea 247
            en NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:línea 1349
            en NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:línea 413
            en NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:línea 243
            en NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) en d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:línea 1694
       InnerException: MySql.Data.MySqlClient.MySqlException
            Message=Parameter '@iParameter' must be defined.
            Source=MySql.Data
            ErrorCode=-2147467259
            Number=0
            StackTrace:
                 en MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName)
                 en MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
                 en MySql.Data.MySqlClient.Statement.BindParameters()
                 en MySql.Data.MySqlClient.PreparableStatement.Execute()
                 en MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
            InnerException:

please can you help me.

Regards

First off, Marco's right; if you have Fluent, you should use classes derived from ClassMap instead of HBMs for the overwhelming majority of your domain mappings. However, specifying a named query is something Fluent doesn't do anyway, so the HBM is what you need.

As for why NHibernate needs this information when the schema is already known by the DB and discoverable by consumers, first, the process of using views like systables/sysobjects/syscolumns to produce a schema model programmatically can be time-consuming, and second, it's only half the equation; when specifying a mapping, you're telling NHibernate what PART of the schema you are interested in, and how each field or property of your domain is transformed into the schema field. It's not always an obvious conversion.

Anyway, to your problem. The error is basically stating that the parameter didn't make it into the SQL string; instead the placeholder was used, which the DB couldn't understand. I would take a good long look at how you're specifying the parameter, but unfortunately I don't see anything obviously wrong.

The mappings are used to create a connection between your POO's and your database.

Nhibernate needs it to translate any operation you make into the Query you need. Its a core pre-requisite to get the abstraction you want.

By the way, you can handle this on an easier way using Fluent Nhibernate

Here are some examples AutoMapper