Hi all,
I need to count records in a module. I wrote a stored procedure:
CREATE PROCEDURE dbo.dnn_CountOutdatedRecords @ModuleID int, @StartDate datetime AS BEGIN SET NOCOUNT ON SELECT COUNT(*) FROM dbo.dnn_Records WHERE ModuleID = @ModuleID AND StartDate <= @StartDate END
In the controller I wrote the following code:
public int OutdatedRecordsCount(int moduleID, DateTime startDate) { int outdatedRecordsCount; using (IDataContext ctx = DataContext.Instance()) { outdatedRecordsCount = ctx.ExecuteScalar(System.Data.CommandType.StoredProcedure, "{databaseOwner}{objectQualifier}CountOutdatedRecords", new object[] { moduleID, startDate }); } return outdatedRecordsCount; }
This always returns 0 (zero), even if I get a number selected when I execute the stored procedure. I tried with an OUTPUT parameter:
CREATE PROCEDURE dbo.dnn_CountOutdatedRecords @ModuleID int, @StartDate datetime, @OutdatedRecordsCount OUTPUT AS BEGIN SET NOCOUNT ON SELECT @OutdatedRecordsCount = COUNT(*) FROM dbo.dnn_Records WHERE ModuleID = @ModuleID AND StartDate <= @StartDate RETURN @OutdatedRecordsCount END
And in the controller:
public int OutdatedRecordsCount(int moduleID, DateTime startDate) { int outdatedRecordsCount = 0; using (IDataContext ctx = DataContext.Instance()) { ctx.ExecuteScalar(System.Data.CommandType.StoredProcedure, "{databaseOwner}{objectQualifier}CountOutdatedRecords", new object[] { moduleID, startDate, outdatedRecordsCount }); } return outdatedRecordsCount; }
This fired an exception, "Null object cannot be converted to a value type'. I changed the controller to a text command:
public int OutdatedRecordsCount(int moduleID, DateTime startDate) { int outdatedRecordsCount; using (IDataContext ctx = DataContext.Instance()) { outdatedRecordsCount = ctx.ExecuteScalar(System.Data.CommandType.Text, "SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}Records WHERE ModuleID = @0 AND StartDate <= @1", new object[] { moduleID, startDate }); } return outdatedRecordsCount; }
This also returns 0 (zero).
Any idea how to get this done right?
Happy DNNing! Michael
Michael TobischDNN★MVP
Solved it.
Instead of the stored procedure I created a function:
CREATE FUNCTION dbo.dnn_f_CountOutdatedRecords ( @ModuleID int, @StartDate datetime ) RETURNS int AS BEGIN DECLARE @OutdatedRecordsCount int SELECT @OutdatedRecordsCount = COUNT(*) FROM dbo.dnn_Records WHERE ModuleID = @ModuleID AND StartDate <= @StartDate RETURN @OutdatedRecordsCount END GO
In the controller I use the follwing code:
public static int OutdatedRecordsCount(int moduleID, DateTime startDate) { int outdatedRecordsCount = 0; using (IDataContext ctx = DataContext.Instance()) { outdatedRecordsCount = ctx.ExecuteScalar(System.Data.CommandType.Text, "SELECT {databaseOwner}{objectQualifier}f_CountOutdatedRecords(@0, @1)", new object[] { moduleID, startDate }); } return outdatedRecordsCount; }
Eh, voila!
If I wanted to get a single value from a stored procedure, this is how I'd do it:
CREATE OR ALTER PROCEDURE CountOutdatedRecords ( @ModuleId int, @StartDate datetime ) AS BEGIN SELECT @moduleId + DATEDIFF(ns, GETUTCDATE(), @StartDate) END GO
From the SQL, just SELECT the value you want.
SELECT
protected int GetValue() { using (IDataContext ctx = DataContext.Instance()) { return ctx.ExecuteScalar<int>( System.Data.CommandType.StoredProcedure, "{databaseOwner}{objectQualifier}CountOutdatedRecords", new object[] { ModuleConfiguration.ModuleID, DateTime.Now }); } }
Using ExecuteScalar will get that single value from the SELECT result set.
ExecuteScalar
If you need to use an OUTPUT parameter, this will work:
OUTPUT
CREATE OR ALTER PROCEDURE CountOutdatedRecords ( @ModuleId int, @StartDate datetime, @OutdatedRecordsCount int OUTPUT ) AS BEGIN SELECT @moduleId + DATEDIFF(ns, GETUTCDATE(), @StartDate); SET @OutdatedRecordsCount = 123; END GO
protected int GetOutputValue() { using (IDataContext ctx = DataContext.Instance()) { var count = new SqlParameter("@OutdatedRecordsCount", SqlDbType.Int) { Direction = ParameterDirection.Output }; ctx.Execute( CommandType.StoredProcedure, "{databaseOwner}{objectQualifier}CountOutdatedRecords", new SqlParameter("@ModuleId", ModuleConfiguration.ModuleID), new SqlParameter("@StartDate", DateTime.Now), count); return (int)theResult.Value; } }
Hope it helps!
These Forums are dedicated to the discussion of DNN Platform.
For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:
Awesome! Simply post in the forums using the link below and we'll get you started.