Databases
StackOverflow2013
Postsdbo
POHow to convert a procedure into create criteria with Nhibernate?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to convert a procedure into create criteria with Nhibernate?
    primarykey
    data
    text
    <p>I have a stored procedure that I have to convert it into Nhibernate create criteria.I have done all the mapping related to procedure in <code>.hbm</code> file. But when I debug the code, no data is retrieved. But when I give same values in that procedure in SQL server, it retrieve the data.</p> <p>I have following stored procedure</p> <pre><code>CREATE PROCEDURE ak_ReportData_GLActivityD_BeginningBalance @CompanyID uniqueidentifier, @PropertyID uniqueidentifier = NULL, @StartAccount float, @EndAccount float, @StartDate datetime AS SET NOCOUNT ON DECLARE @dtZeroDate datetime DECLARE @dtFiscalYearStart datetime -- Set constants SELECT @dtZeroDate = CONVERT(DATETIME, '1-Jan-1900') -- If companyID is not supplied get it from PropertyID IF @CompanyID IS NULL SELECT @CompanyID = fCompanyID FROM tSCProperty WHERE fPropertyID = @PropertyID --Get start date of the fiscal year containing the passed @StartDate SELECT @dtFiscalYearStart = fBeginDate FROM tSCFiscalPeriod WHERE fFiscalYear = (SELECT fFiscalYear FROM tSCFiscalPeriod WHERE (@StartDate BETWEEN fBeginDate AND fEndDate) AND fCompanyID = @CompanyID) AND fPeriod = 1 AND fCompanyID = @CompanyID -- Calculate beginning balances SELECT la.fAccount, SUM(ps.fDebitAmount - ps.fCreditAmount) AS BeginBalance FROM tSCLedgerAccount la INNER JOIN tGLPostSummary ps ON (la.fAccount = ps.fAccount AND la.fCompanyID = ps.fCompanyID) WHERE la.fCompanyID = @CompanyID AND (la.fPropertyID = @PropertyID OR @PropertyID IS NULL) AND la.fAccount BETWEEN @StartAccount AND @EndAccount AND ps.fPostDate &gt;= (CASE la.fType WHEN 'Liability' THEN @dtZeroDate WHEN 'Equity' THEN @dtZeroDate WHEN 'Asset' THEN @dtZeroDate ELSE @dtFiscalYearStart END) AND ps.fPostDate &lt; @StartDate GROUP BY la.fAccount </code></pre> <p>Following are the .hbm files</p> <p>First one is</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt; &lt;hibernate-mapping assembly="BlackOpsP2.Core" namespace="BlackOpsP2.Core.Domain.ARModule" xmlns="urn:nhibernate-mapping-2.2"&gt; &lt;class name="tGLPostSummary" table="tGLPostSummary" lazy="true" &gt; &lt;id name="fCompanyID"&gt; &lt;generator class="guid" /&gt; &lt;/id&gt; &lt;property name="fAccount"&gt; &lt;/property&gt; &lt;property name="fPostDate"&gt; &lt;/property&gt; &lt;property name="fCreditAmount"&gt; &lt;/property&gt; &lt;property name="fDebitAmount"&gt; &lt;/property&gt; &lt;property name="fDateModified"&gt; &lt;/property&gt; &lt;many-to-one name="LedgerAccount" class="tSCLedgerAccount" insert="false" update="false"&gt; &lt;column name="fCompanyID" /&gt; &lt;column name="fAccount" /&gt; &lt;/many-to-one&gt; &lt;/class&gt; &lt;/hibernate-mapping&gt; </code></pre> <p>Second One is</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt; &lt;hibernate-mapping assembly="BlackOpsP2.Core" namespace="BlackOpsP2.Core.Domain.ARModule" xmlns="urn:nhibernate-mapping-2.2"&gt; &lt;class name="tSCLedgerAccount" table="tSCLedgerAccount" lazy="true" &gt; &lt;composite-id&gt; &lt;key-property name="fCompanyID"&gt;&lt;/key-property&gt; &lt;key-property name="fAccount"&gt;&lt;/key-property&gt; &lt;/composite-id&gt; &lt;version name="fTimestamp" generated="always" unsaved-value="null" type="BinaryBlob"&gt; &lt;column name="fTimestamp" not-null="false" sql-type="timestamp"/&gt; &lt;/version&gt; &lt;property name="fInactive"&gt; &lt;/property&gt; &lt;property name="fPropertyID"&gt; &lt;/property&gt; &lt;property name="fProjectID"&gt;&lt;/property&gt; &lt;property name="fCapital"&gt; &lt;/property&gt; &lt;property name="fName"&gt; &lt;/property&gt; &lt;property name="fType"&gt; &lt;/property&gt; &lt;property name="fDescription"&gt; &lt;/property&gt; &lt;property name="fDepLife"&gt; &lt;/property&gt; &lt;bag name="PostSummary" inverse="true"&gt; &lt;key&gt; &lt;column name="fCompanyID"/&gt; &lt;column name="fAccount"/&gt; &lt;/key&gt; &lt;one-to-many class="tGLPostSummary"/&gt; &lt;/bag&gt; &lt;/class&gt; &lt;/hibernate-mapping&gt; </code></pre> <p>and in C# I have written this create criteria query.</p> <pre><code> var BeginingBalance= Session.CreateCriteria&lt;tSCLedgerAccount&gt;("Ledger") .CreateCriteria("Ledger.PostSummary", "Summary", NHibernate.SqlCommand.JoinType.InnerJoin) .Add(Expression.And(Expression.Eq("Ledger.fCompanyID", GlobalMember.PrimaryFilter.CompanyId), Expression.Eq("Ledger.fPropertyID", GlobalMember.PrimaryFilter.PropertyId))) .Add(Expression.Eq("Summary.fDebitAmount", StartAccount)) .Add(Expression.Eq("Summary.fCreditAmount", EndAccount)) .Add(Expression.Eq("Summary.fPostDate", Convert.ToDateTime("2012-01-01"))) .SetProjection(Projections.ProjectionList() .Add(Projections.GroupProperty("Summary.fAccount"), "fAccount") .Add(Projections.Property("Summary.fAccount"), "fAccount")) //.Add(Projections.Property("Summary.fDebitAmount"), "fDebitAmount") //.Add(Projections.Property("Summary.fCreditAmount"), "fCreditAmount")) .SetResultTransformer(Transformers.AliasToBean(typeof(tSCLedgerAccount))) .List&lt;tSCLedgerAccount&gt;(); return BeginingBalance; </code></pre> <p>I am using C# as a language and NHibernate.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShalfer
    1. USAmit Kumar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POHow to convert a procedure into create criteria with Nhibernate?
      1. USAmit Kumar
      1. VTFavorite
    1. COPlease, show us more, what is in the `hbm` file? How does your C# call looks like?... *(NOTE: if you have to map Stored procedures...do it... but NHibernate has a real power elsewhere)*
      singulars
      1. POHow to convert a procedure into create criteria with Nhibernate?
      1. USRadim Köhler
    2. COI have updated with .hbm files.
      singulars
      1. POHow to convert a procedure into create criteria with Nhibernate?
      1. USAmit Kumar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload