custom stored procedure returning void isntead a dataset

rated by 0 users
This post has 0 verified answers | 22 Replies | 2 Followers

Suggest an Answer

132 Posts

Points 3,465

reguapo posted on 02-01-2010 2:14 PM

rated by 0 users

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: 35

All Replies

792 Posts

Points 22,230

SuperJeffe replied on 02-08-2010 1:43 PM

rated by 0 users

reguapo:

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 am not sure why it isn't. I never tried executing the GetPaged with the fmt_only option. You chould try it and see if it does the same thing.

You might try converting it to use temp tables instead of declare tables. You are just going to have to play with it some and see if you can see why it is doing that. Once the fmt_only returns a result, you should be good.

jeff

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

| Post Points: 35

132 Posts

Points 3,465

reguapo replied on 02-08-2010 1:50 PM

rated by 0 users

i tested it with getpaged methods and doesn't return nothing, the same as my stored procedure ??

| Post Points: 35

792 Posts

Points 22,230

SuperJeffe replied on 02-08-2010 2:42 PM

rated by 0 users

Nettiers doesn't call the stored procedures they generate to get the schema. It assumes since it generates them that they have the correct result.

It only uses the fmt_only with custom procs to learn the schema.

jeff

| Post Points: 35

132 Posts

Points 3,465

reguapo replied on 02-08-2010 2:51 PM

rated by 0 users

i am using sql server 2008, it has some consequences? because i was trying to get a result with this fmt_only on and i didn't have success

| Post Points: 35

792 Posts

Points 22,230

SuperJeffe replied on 02-08-2010 3:29 PM

rated by 0 users

What have you tried? Did you try using Temp tables instead of declare tables? No, there is no consequence in using Sql 2008. All ORM's have this problem even Linq to Sql has problems with procs like this.

As a hack you could do a big if statement at the top and if all the parameters are null, call the GetAll stored procedure for that table.

jeff

| Post Points: 35

132 Posts

Points 3,465

reguapo replied on 02-15-2010 1:42 PM

rated by 0 users

Suggested by blake05

well i tried this, but it is not dinamic sql, i get the correct result but without dinamic sql, it generates a dataset.

here is the stored procedure:

ALTER PROCEDURE [dbo].[usp_c_paciente_FindCiudadanoPaciente]

    @Nombre varchar (50) = null ,

    @PrimerApellido varchar (50) = null ,

    @SegundoApellido varchar (50) = null ,

    @Sufijo varchar (50) = null ,

    @Prefijo varchar (50) = null ,

    @Alias varchar (50) = null ,

    @NumeroIdentidad varchar (50) = null ,

    @IdRaza int   = null ,

    @IdSexo int   = null ,

    @IdColorOjos int   = null ,

    @LugarNacimiento varchar (50) = null ,

    @FechaNacimiento date   = null ,

    @Calle varchar (50) = null ,

    @Entre varchar (50) = null ,

    @Numero varchar (50) = null ,

    @Apto varchar (50) = null ,

    @IdAreaSalud int   = null ,

    @NombreMadre varchar (50) = null ,

    @NombrePadre varchar (50) = null ,

    @TelfCasa varchar (50) = null ,

    @TelfCelular varchar (50) = null ,

    @PageIndex int = 0,

    @PageSize int = 1000
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 = @PageSize - 1 + @PageLowerBound

        CREATE TABLE #PageIndex
        (
            IndexId int IDENTITY (0, 1) NOT NULL,
            --IndexId bigint,
            nombre varchar(50) NOT NULL,
            id_paciente uniqueidentifier NOT NULL,
            id_grupo_sanguineo int NULL,
            id_factor_sanguineo int NULL,
            nombre_trabajo varchar(50) NULL,
            telf_centro_trabajo varchar(50) NULL,
            email varchar(50) NULL,
            id_tipo_movimiento int NULL,
            fecha_movimiento date NULL
        )

        INSERT INTO #PageIndex (nombre, id_paciente, id_grupo_sanguineo, id_factor_sanguineo, nombre_trabajo,telf_centro_trabajo, email,id_tipo_movimiento,fecha_movimiento)

            SELECT
                --ROW_NUMBER() OVER (ORDER BY p.id_paciente) as RowIndex ,
                c.nombre,
                p.id_paciente,
                p.id_grupo_sanguineo ,
                p.id_factor_sanguineo ,
                p.nombre_centro_trabajo ,
                p.telf_centro_trabajo ,
                p.email ,
                p.id_tipo_movimiento ,
                p.fecha_movimiento
            FROM [c_paciente] p INNER JOIN [c_ciudadano] c ON p.id_paciente = c.id_ciudadano
            WHERE
                (c.[nombre] = @Nombre OR @Nombre IS NULL)
                AND (c.[primer_apellido] = @PrimerApellido OR @PrimerApellido IS NULL)
                AND (c.[segundo_apellido] = @SegundoApellido OR @SegundoApellido IS NULL)
                AND (c.[sufijo] = @Sufijo OR @Sufijo IS NULL)
                AND (c.[prefijo] = @Prefijo OR @Prefijo IS NULL)
                AND (c.[alias] = @Alias OR @Alias IS NULL)
                AND (c.[numero_identidad] = @NumeroIdentidad OR @NumeroIdentidad IS NULL)
                AND (c.[id_raza] = @IdRaza OR @IdRaza IS NULL)
                AND (c.[id_sexo] = @IdSexo OR @IdSexo IS NULL)
                AND (c.[id_color_ojos] = @IdColorOjos OR @IdColorOjos IS NULL)
                AND (c.[lugar_nacimiento] = @LugarNacimiento OR @LugarNacimiento IS NULL)
                AND (c.[fecha_nacimiento] = @FechaNacimiento OR @FechaNacimiento IS NULL)
                AND (c.[calle] = @Calle OR @Calle IS NULL)
                AND (c.[entre] = @Entre OR @Entre IS NULL)
                AND (c.[numero] = @Numero OR @Numero IS NULL)
                AND (c.[apto] = @Apto OR @Apto IS NULL)
                AND (c.[id_area_salud] = @IdAreaSalud OR @IdAreaSalud IS NULL)
                AND (c.[nombre_madre] = @NombreMadre OR @NombreMadre IS NULL)
                AND (c.[nombre_padre] = @NombrePadre OR @NombrePadre IS NULL)
                AND (c.[telf_casa] = @TelfCasa OR @TelfCasa IS NULL)
                AND (c.[telf_celular] = @TelfCelular OR @TelfCelular IS NULL)

        SELECT
            id_paciente,
            id_grupo_sanguineo,
            id_factor_sanguineo,
            nombre_trabajo,
            telf_centro_trabajo,
            email,
            id_tipo_movimiento,
            fecha_movimiento
        FROM
            #PageIndex
        WHERE
            #PageIndex.IndexId BETWEEN @PageLowerBound AND @PageUpperBound
        ORDER BY nombre

    SELECT COUNT(id_paciente) AS TotalRowCount from #PageIndex;




END

and here is the generated code:

public override DataSet FindCiudadanoPaciente(TransactionManager transactionManager, int start, int pageLength , System.String nombre, System.String primerApellido, System.String segundoApellido, System.String sufijo, System.String prefijo, System.String alias, System.String numeroIdentidad, System.Int32? idRaza, System.Int32? idSexo, System.Int32? idColorOjos, System.String lugarNacimiento, System.DateTime? fechaNacimiento, System.String calle, System.String entre, System.String numero, System.String apto, System.Int32? idAreaSalud, System.String nombreMadre, System.String nombrePadre, System.String telfCasa, System.String telfCelular, 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, "@Nombre", DbType.AnsiString, nombre );
            database.AddInParameter(commandWrapper, "@PrimerApellido", DbType.AnsiString, primerApellido );
            database.AddInParameter(commandWrapper, "@SegundoApellido", DbType.AnsiString, segundoApellido );
            database.AddInParameter(commandWrapper, "@Sufijo", DbType.AnsiString, sufijo );
            database.AddInParameter(commandWrapper, "@Prefijo", DbType.AnsiString, prefijo );
            database.AddInParameter(commandWrapper, "@Alias", DbType.AnsiString, alias );
            database.AddInParameter(commandWrapper, "@NumeroIdentidad", DbType.AnsiString, numeroIdentidad );
            database.AddInParameter(commandWrapper, "@IdRaza", DbType.Int32, idRaza );
            database.AddInParameter(commandWrapper, "@IdSexo", DbType.Int32, idSexo );
            database.AddInParameter(commandWrapper, "@IdColorOjos", DbType.Int32, idColorOjos );
            database.AddInParameter(commandWrapper, "@LugarNacimiento", DbType.AnsiString, lugarNacimiento );
            database.AddInParameter(commandWrapper, "@FechaNacimiento", DbType.Date, fechaNacimiento );
            database.AddInParameter(commandWrapper, "@Calle", DbType.AnsiString, calle );
            database.AddInParameter(commandWrapper, "@Entre", DbType.AnsiString, entre );
            database.AddInParameter(commandWrapper, "@Numero", DbType.AnsiString, numero );
            database.AddInParameter(commandWrapper, "@Apto", DbType.AnsiString, apto );
            database.AddInParameter(commandWrapper, "@IdAreaSalud", DbType.Int32, idAreaSalud );
            database.AddInParameter(commandWrapper, "@NombreMadre", DbType.AnsiString, nombreMadre );
            database.AddInParameter(commandWrapper, "@NombrePadre", DbType.AnsiString, nombrePadre );
            database.AddInParameter(commandWrapper, "@TelfCasa", DbType.AnsiString, telfCasa );
            database.AddInParameter(commandWrapper, "@TelfCelular", DbType.AnsiString, telfCelular );
            database.AddInParameter(commandWrapper, "@PageIndex", DbType.Int32, pageIndex );
            database.AddInParameter(commandWrapper, "@PageSize", DbType.Int32, pageSize );


            DataSet ds = null;

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

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

            //Provider Data Requested Command Event
            OnDataRequested(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));




            return ds;
        }

| Post Points: 35

792 Posts

Points 22,230

SuperJeffe replied on 02-16-2010 9:03 AM

rated by 0 users

It is returning a dataset because it has multiple results.

jeff

| Post Points: 35

132 Posts

Points 3,465

reguapo replied on 02-22-2010 11:52 AM

rated by 0 users

yes jeff i understand this part, but my point is, why it doesnt work with dynamic sql? are any explanation for that?

| Post Points: 5

Previous | Next

Page 2 of 2 (23 items) < Previous 1 2 | RSS

custom stored procedure returning void isntead a dataset

rated by 0 users This post has 0 verified answers | 22 Replies | 2 Followers

All Replies

rated by 0 users
This post has 0 verified answers | 22 Replies | 2 Followers