My NH Query
exec sp_executesql N'IF @p0 = 1 SET @p1 = DATEADD(d, 1, @p1)
SELECT b.fInvoiceID,
b.fItemID,
b.fAccount,
e.fName AccountName,
b.fDescription,
b.fAmount,
c.fCode CTCode,
f.fDescription CTCodeName,
c.fDescription CTDescription,
c.fAmount CTAmount
FROM tAPInvoice a
INNER JOIN tAPInvoiceDetail b ON a.fInvoiceID = b.fInvoiceID
INNER JOIN tSCLedgerAccount e ON b.fAccount = e.fAccount AND a.fCompanyID = e.fCompanyID
LEFT JOIN tCTActual c ON b.fItemID = c.fAPItemID
LEFT JOIN tCTCode f ON c.fCode = f.fCode AND c.fProjectID = f.fProjectID
LEFT JOIN tAPPayment p ON a.fInvoiceID = p.fInvoiceID
LEFT JOIN tBACheck d ON p.fCheckID = d.fCheckID
INNER JOIN tAPVendor v ON v.fVendorID = a.fVendorID
WHERE
((@p3 = ''Fully Paid'' AND a.fPaid = 1 AND a.fVoid = 0 ) OR
(@p3 = ''Part Paid'' AND a.fPartPaid = 1 AND a.fVoid = 0 ) OR
(@p3 = ''Payment Made'' AND a.fPartPaid = 1 AND a.fVoid = 0 ) OR
(@p3 = ''Payment Made'' AND a.fPaid = 1 AND a.fVoid = 0 ) OR
(@p3 = ''Pending'' AND a.fPaid = 0 AND a.fPartPaid = 0 AND a.fVoid = 0 AND a.fPosted = 0 ) OR
(@p3 = ''Posted'' AND a.fPosted = 1 ) OR
(@p3 = ''Unpaid'' AND a.fPaid = 0 AND a.fPartPaid = 0 AND a.fVoid = 0 ) OR
(@p3 = ''Voided'' AND a.fVoid = 1 ) OR
(@p3 = ''All'' ))
AND (@p12 = p.fPaymentType OR @p12 = -1)
AND (CASE WHEN @p0 = 1
THEN a.fEntryDate
ELSE CASE @p3
WHEN ''Fully Paid'' THEN d.fDate
WHEN ''Part Paid'' THEN d.fDate
WHEN ''Payment Made'' THEN d.fDate
WHEN ''Voided'' THEN a.fVoidDate
ELSE a.fPostDate
END
END BETWEEN @p16 AND @p1)
AND (a.fCompanyID = @p18 OR @p18 IS NULL)
AND (a.fPropertyID =@p20 OR @p20 IS NULL)
AND (a.fVendorID = @p22 OR @p22 IS NULL)
AND (a.fUser = @p24 OR @p25 IS NULL)
AND (v.fActive = @p26 OR @p26 IS NULL)
ORDER BY a.fInvoiceNo ASC, b.fItemID, c.fCTID ',N'@p0 bit,@p1 datetime,@p3 nvarchar(4000),@p12 int,@p16 datetime,@p18 nvarchar(4000),@p20 nvarchar(4000),@p22 nvarchar(4000),@p24 nvarchar(4000),@p25 nvarchar(4000),@p26 bit',@p0=0,@p1='2012-10-31 20:35:23',@p3=N'Fully Paid',@p12=-1,@p16='2012-07-01 20:35:23',@p18=NULL,@p20=NULL,@p22=NULL,@p24=NULL,@p25=NULL,@p26=1
Below is my c# code
sqlQuery =
session.CreateSQLQuery(finalQuery).
SetGuid("CompanyID", Company.FCompanyID).
SetString("PropertyID", null).
SetString("VendorID", null).
SetDateTime("StartDate", StartDate).
SetDateTime("EndDate", EndDate).
SetString("Username", User).
SetInt32("PaymentMethod", PaymentMethod).
SetString("InvoiceType", invoicetype).
SetString("UserName", User).
SetBoolean("UseEntryDate", UserEntryDate).
SetParameter("Active", Active);
after this i am using SetResultTransformer method
var result = sqlQuery.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<ReportData_APBI_Detail>()).List();
above line with SetResultTransformer() method throw an exception message
{"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}
even i run this query on Sql Server which give me output with in 48 sec.
