Welcome to the CodeSmith Community!

Re: custom stored procedure returning void isntead a dataset

.netTiers

A description has not yet been added to this group.

custom stored procedure returning void isntead a dataset

Answered (Verified) This question is answered

i know this is a very discussed topic, but i read  a lot of that and still without and answer.

i have my sp named usp_c_paciente_FindCiudadanoPaciente, and here is the sp:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_c_paciente_FindCiudadanoPaciente]
    -- Add the parameters for the stored procedure here
    @WhereClause varchar (2000),
    @OrderBy varchar (2000),
    @PageIndex int,
    @PageSize int 
   
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    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

  
END

GO

and here is the method stub generated in the SqlCPacienteProviderBase.generated.cs file:

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;
        }

 

i just tried to do and paged avanced search doing like nettiers do, the only diferences is the JOIN, what i have to do to generated a method thats returns a DASET or a TLIST(better a TLIST)

  • Post Points: 65
All Replies
  • 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

    ----------------------------------------------------------------------
     Member of the .NetTiers team | Visit http://www.nettiers.com
    ----------------------------------------------------------------------

  • i will try with that and get u back, thanks

  • 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?

  • 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

    ----------------------------------------------------------------------
     Member of the .NetTiers team | Visit http://www.nettiers.com
    ----------------------------------------------------------------------

  • 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

  • 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.

  • any help with this?

  • 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

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • 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

    ----------------------------------------------------------------------
     Member of the .NetTiers team | Visit http://www.nettiers.com
    ----------------------------------------------------------------------

  • 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.

     

    ??

  • 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?

  • Hello,
    Try.
    SET FMTONLY ON;
    GO
    Exec Procname.
    GO
    SET FMTONLY OFF;
    Thanks
    Blake Niemyjski

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • 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?

  • 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

Page 1 of 2 (26 items) 12