Accessing Stored Procedure Output Parameter in DotNetNuke DAL (C#)

From the previous post Adding New Record Inside DataGrid using DotNetNuke 4 DAL (C#) we will modify the DataGrid’s Insert funtion to return a notification message or an error from the stored procedure.

dnn_Insert stored procedure:

CREATE PROCEDURE [dbo].[dnn_Insert]
@ModuleId int,
@LastName nvarchar(20),
@FirstName nvarchar(10),
@HireDate datetime,
@ERR nvarchar(50) output
AS

SELECT UPPER(LastName), UPPER(FirstName), HireDate from employees WHERE LastName = UPPER(@LastName) AND FirstName = UPPER(@FirstName)

IF @@rowcount = 0
BEGIN
INSERT INTO employees ( LastName, FirstName, HireDate) VALUES ( @LastName, @FirstName, @HireDate)
SET @ERR = 'Record has been saved.'
END

ELSE
SET @ERR = 'The name ' + @FirstName + ' ' + @LastName + ' already exist on database.'

This procedure will check the database if there is already an employee with the name you are trying to add.

Now on the SqlDataprovider.cs, we will modify the dnn_Insert function to return string.

public override string dnn_Insert(int ModuleId, string LastName, string FirstName, DateTime HireDate)
{
SqlParameter[] param = new SqlParameter[5];
param[0] = new SqlParameter("@ModuleId", ModuleId);
param[1] = new SqlParameter("@LastName", LastName);
param[2] = new SqlParameter("@FirstName", FirstName);
param[3] = new SqlParameter("@HireDate", HireDate);
param[4] = new SqlParameter("@ERR", SqlDbType.NVarChar,50);
param[4].Direction = ParameterDirection.Output;

SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, GetFullyQualifiedName("dnn_Insert"), param);
return param[4].Value.ToString();
}

Since on our stored procedure, we declared an additional output parameter, we will have to include this parameter when executing the stored procedure and declare this parameter as output. The SqlHelper.ExecuteNonQuery accepts an SqlParameter array as argument. So instead of passing the object directly to SqlHelper like what we did before, we created an SqlParameter array and assigned the object parameters including the additional output parameter @ERR. And then return the value of SqlParameter array on index 4 as string containing the string message from our stored procedure.

Once you modified the SqlDataprovider dnn_Insert function to return string, you will also have to modify all its instances to also return string (DataProvider.cs and dnnController.cs) before compiling.

So now on the DNN module when you call the Insert function, you will have to assign a string variable to catch the returned string and assign this string to a hidden label to display the message.

string notify = dnnController.simple_Insert(info);
msg.Text = notify;