custom stored procedure returning void isntead a dataset

Reply

SuperJeffe replied on Mon, Feb 1 2010 4:20 PM

rated by 0 users

You need to make sure that when the stored procedure is executed with Null for each parameter, that it returns a result set. 

Nettiers does a mock execute on the stored procedure passing in all nulls as parameters to get the Result schema.

jeff

Reply

reguapo replied on Tue, Feb 2 2010 12:56 PM

rated by 0 users

i will try with that and get u back, thanks

Reply

reguapo replied on Wed, Feb 3 2010 8:19 AM

rated by 0 users

well i tested the sp with all the parameters null and it returns all the records of the db, so it is fine like this,but still getting the methods with void, here is the new sp, and the query:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_c_paciente_FindCiudadanoPaciente]
    -- Add the parameters for the stored procedure here
    @WhereClause varchar (2000) = NULL,
    @OrderBy varchar (2000) = NULL,
    @PageIndex int = NULL,
    @PageSize int = NULL 
   
AS
BEGIN
    IF @PageIndex IS NULL
    SET @PageIndex = 0
   
    IF @PageSize IS NULL
    SET @PageSize = 400
   
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
   
    -- Set the page bounds
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize
   
    IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
    BEGIN
        -- default order by id_ciudadano
        SET @OrderBy = '[c_paciente].id_paciente'
    END

    -- SQL Server 2005 Paging
    DECLARE @SQL AS nvarchar(MAX)
    SET @SQL = 'WITH PageIndex AS ('
    SET @SQL = @SQL + ' SELECT'
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
    SET @SQL = @SQL + ' ,[c_paciente].id_paciente'
    SET @SQL = @SQL + ' ,[c_paciente].id_grupo_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].id_factor_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].nombre_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].telf_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].email'
    SET @SQL = @SQL + ' ,[c_paciente].id_tipo_movimiento'
    SET @SQL = @SQL + ' ,[c_paciente].fecha_movimiento'
    SET @SQL = @SQL + ' FROM [c_paciente] INNER JOIN [c_ciudadano] ON [c_paciente].id_paciente = [c_ciudadano].id_ciudadano '
    IF (@WhereClause IS NOT NULL AND LEN(@WhereClause) > 0)
    BEGIN
        SET @SQL = @SQL + ' WHERE ' + @WhereClause
    END
    SET @SQL = @SQL + ' ) SELECT'
    SET @SQL = @SQL + ' id_paciente,'
    SET @SQL = @SQL + ' id_grupo_sanguineo,'
    SET @SQL = @SQL + ' id_factor_sanguineo,'
    SET @SQL = @SQL + ' nombre_centro_trabajo,'
    SET @SQL = @SQL + ' telf_centro_trabajo,'
    SET @SQL = @SQL + ' email,'
    SET @SQL = @SQL + ' id_tipo_movimiento,'
    SET @SQL = @SQL + ' fecha_movimiento'
    SET @SQL = @SQL + ' FROM PageIndex'
    SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ORDER BY ' + 'id_paciente'
    EXEC sp_executesql @SQL
   
    -- get row count
    SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
    SET @SQL = @SQL + ' FROM [c_paciente] INNER JOIN [c_ciudadano] ON [c_paciente].id_paciente = [c_ciudadano].id_ciudadano '
    IF (@WhereClause IS NOT NULL AND LEN(@WhereClause) > 0)
    BEGIN
        SET @SQL = @SQL + ' WHERE ' + @WhereClause
    END
    EXEC sp_executesql @SQL
    PRINT @SQL
  
END

the execution of the sp:

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_c_paciente_FindCiudadanoPaciente]
        @WhereClause = NULL,
        @OrderBy = NULL,
        @PageIndex = NULL,
        @PageSize = NULL

SELECT    'Return Value' = @return_value

GO

and the results:
(2 row(s) affected)

(1 row(s) affected)
SELECT COUNT(*) AS TotalRowCount FROM [c_paciente] INNER JOIN [c_ciudadano] ON [c_paciente].id_paciente = [c_ciudadano].id_ciudadano

(1 row(s) affected)

it returned all the records in the table c_paciente

so this is not the problem, any idea?

Reply

SuperJeffe replied on Fri, Feb 5 2010 10:51 AM

rated by 0 users

So, in custom procs, by default, if there is more than one result set, it returns a dataset.

You have 2 results in this stored procedure, the SELECT COUNT(*).  This is a waisted statement.  It isn't used in Nettiers at all.

Two solutions:

1.  Get rid of the Select Count

2.  There is an option in 07.  Crud - Advanced that says AllowCustomProcMultipleResults.  Set that to try and it will ignore all other result sets other than the first.

jeff

Reply

reguapo replied on Fri, Feb 5 2010 11:29 AM

rated by 0 users

i tried the second option and it didn't work, continues generating the void method, let me try with the first option, but i was seeing the  nettiers sp, and all getpaged have this way.

trying first solution ....... and get back

Reply

reguapo replied on Fri, Feb 5 2010 12:09 PM

rated by 0 users

hey i did the first but the same, here is the new sp:

ALTER PROCEDURE [dbo].[usp_c_paciente_FindCiudadanoPaciente]
    -- Add the parameters for the stored procedure here
    @WhereClause varchar (2000) = NULL,
    @OrderBy varchar (2000) = NULL,
    @PageIndex int = NULL,
    @PageSize int = NULL 
   
AS
BEGIN
    IF @PageIndex IS NULL
    SET @PageIndex = 0
   
    IF @PageSize IS NULL
    SET @PageSize = 400
   
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
   
    -- Set the page bounds
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize
   
    IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
    BEGIN
        -- default order by id_ciudadano
        SET @OrderBy = '[id_paciente]'
    END

    -- SQL Server 2005 Paging
    DECLARE @SQL AS nvarchar(MAX)
    SET @SQL = 'WITH PageIndex AS ('
    SET @SQL = @SQL + ' SELECT'
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
    SET @SQL = @SQL + ' ,[c_paciente].id_paciente'
    SET @SQL = @SQL + ' ,[c_paciente].id_grupo_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].id_factor_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].nombre_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].telf_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].email'
    SET @SQL = @SQL + ' ,[c_paciente].id_tipo_movimiento'
    SET @SQL = @SQL + ' ,[c_paciente].fecha_movimiento'
    SET @SQL = @SQL + ' FROM [c_paciente] INNER JOIN [c_ciudadano] ON [c_paciente].id_paciente = [c_ciudadano].id_ciudadano '
    IF (@WhereClause IS NOT NULL AND LEN(@WhereClause) > 0)
    BEGIN
        SET @SQL = @SQL + ' WHERE ' + @WhereClause
    END
    SET @SQL = @SQL + ' GROUP BY '
    SET @SQL = @SQL + '  [c_paciente].id_paciente'
    SET @SQL = @SQL + ' ,[c_paciente].id_grupo_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].id_factor_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].nombre_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].telf_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].email'
    SET @SQL = @SQL + ' ,[c_paciente].id_tipo_movimiento'
    SET @SQL = @SQL + ' ,[c_paciente].fecha_movimiento'
   
    SET @SQL = @SQL + ' ) SELECT'
    SET @SQL = @SQL + ' id_paciente,'
    SET @SQL = @SQL + ' id_grupo_sanguineo,'
    SET @SQL = @SQL + ' id_factor_sanguineo,'
    SET @SQL = @SQL + ' nombre_centro_trabajo,'
    SET @SQL = @SQL + ' telf_centro_trabajo,'
    SET @SQL = @SQL + ' email,'
    SET @SQL = @SQL + ' id_tipo_movimiento,'
    SET @SQL = @SQL + ' fecha_movimiento'
    SET @SQL = @SQL + ' FROM PageIndex'
    SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
    EXEC sp_executesql @SQL
   
END

i tested it with all the parameters in null and it returns fine. any help.

Reply

reguapo replied on Mon, Feb 8 2010 11:16 AM

rated by 0 users

any help with this?

Reply

blake05 replied on Mon, Feb 8 2010 11:46 AM

rated by 0 users

Hello,

Can you post a snippet of what it generates for this as well as what your csp options are for ignore extra result sets and your custom stored procedures pattern.

Thanks

-Blake Niemyjski

Reply

SuperJeffe replied on Mon, Feb 8 2010 11:59 AM

rated by 0 users

Can you try executing the proc like this:

set fmt_only on
go

EXEC proc NULL, NULL, NULL

set fmt_only off
go

Try that and see if you get a result set.

jeff

Reply

reguapo replied on Mon, Feb 8 2010 12:24 PM

rated by 0 users

when i tried to execute this:

USE [Gerisoft(09-12-21)]
GO
set fmt_only on
go

EXEC [dbo].[usp_c_paciente_FindCiudadanoPaciente] NULL, NULL, NULL, NULL

set fmt_only off
go

i recieved the following error:

Msg 195, Level 15, State 5, Line 1
'fmt_only' is not a recognized SET option.

Msg 195, Level 15, State 5, Line 4
'fmt_only' is not a recognized SET option.

??

Reply

reguapo replied on Mon, Feb 8 2010 12:34 PM

rated by 0 users

nettiers stuff.zip

there are all my options of generation, there is the csp, the database script and the mapping file, and here is the generated code:

#region usp_c_paciente_FindCiudadanoPaciente
                   
        /// <summary>
        ///    This method wraps the 'usp_c_paciente_FindCiudadanoPaciente' stored procedure.
        /// </summary>   
        /// <param name="whereClause"> A <c>System.String</c> instance.</param>
        /// <param name="orderBy"> A <c>System.String</c> instance.</param>
        /// <param name="pageIndex"> A <c>System.Int32?</c> instance.</param>
        /// <param name="pageSize"> A <c>System.Int32?</c> instance.</param>
        /// <param name="start">Row number at which to start reading.</param>
        /// <param name="pageLength">Number of rows to return.</param>
        /// <param name="transactionManager"><see cref="TransactionManager"/> object.</param>
        /// <remark>This method is generated from a stored procedure.</remark>
        public override void FindCiudadanoPaciente(TransactionManager transactionManager, int start, int pageLength , System.String whereClause, System.String orderBy, System.Int32? pageIndex, System.Int32? pageSize)
        {
            SqlDatabase database = new SqlDatabase(this._connectionString);
            DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.usp_c_paciente_FindCiudadanoPaciente", true);
           
            database.AddInParameter(commandWrapper, "@WhereClause", DbType.AnsiString,  whereClause );
            database.AddInParameter(commandWrapper, "@OrderBy", DbType.AnsiString,  orderBy );
            database.AddInParameter(commandWrapper, "@PageIndex", DbType.Int32,  pageIndex );
            database.AddInParameter(commandWrapper, "@PageSize", DbType.Int32,  pageSize );
   
           
            //Provider Data Requesting Command Event
            OnDataRequesting(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));

            if (transactionManager != null)
            {   
                Utility.ExecuteNonQuery(transactionManager, commandWrapper );
            }
            else
            {
                Utility.ExecuteNonQuery(database, commandWrapper);
            }
                       
            //Provider Data Requested Command Event
            OnDataRequested(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));


               
                return;
        }
        #endregion
        #endregion

any help with that?

Reply

blake05 replied on Mon, Feb 8 2010 12:49 PM

rated by 0 users

Hello,

Try.

SET FMTONLY ON;
GO
Exec Procname.
GO
SET FMTONLY OFF;

Thanks

Blake Niemyjski

Reply

reguapo replied on Mon, Feb 8 2010 12:54 PM

rated by 0 users

yep its execute fine but without results, maybe for this reason are generating a void method, but why? it is the same of all the getpaged methods, even more simple, any quick solution for that? can u reproduce it?

Reply

reguapo replied on Mon, Feb 8 2010 12:57 PM

rated by 0 users

i did this in my code and it works fine without problem, to continues with my work, and following the same line of the nettiers generation:

public override TList<Paciente> FindCiudadanoPaciente(TransactionManager transactionManager, System.String whereClause, System.String orderBy, int start, int pageLength)
        {
            SqlDatabase database = new SqlDatabase(this._connectionString);
            DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.usp_c_paciente_FindCiudadanoPaciente", true);

            database.AddInParameter(commandWrapper, "@WhereClause", DbType.AnsiString, whereClause);
            database.AddInParameter(commandWrapper, "@OrderBy", DbType.AnsiString, orderBy);
            database.AddInParameter(commandWrapper, "@PageIndex", DbType.Int32, start);
            database.AddInParameter(commandWrapper, "@PageSize", DbType.Int32, pageLength);

            IDataReader reader = null;

            //Create Collection
            TList<Paciente> rows = new TList<Paciente>();

            try
            {
                //Provider Data Requesting Command Event
                OnDataRequesting(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));

                if (transactionManager != null)
                {
                    reader = Utility.ExecuteReader(transactionManager, commandWrapper);
                }
                else
                {
                    reader = Utility.ExecuteReader(database, commandWrapper);
                }

                Fill(reader, rows, start, pageLength);
               
                //Provider Data Requested Command Event
                OnDataRequested(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                commandWrapper = null;
            }


            return rows;
        }

it is something like i was  expecting from the generation