Criei uma procedure para popular uma grid view:
create procedure [dbo].[spc_listaafiliadosadmin]
(
@nome varchar(100),
@login varchar(100),
@cpf varchar(100)
)
as
begin
if(@nome is not null)
begin
select
a.idafiliado,
u.login, u.nome NomeUsuario, upai.login loginpai, u.email,u.cpfcnpj,
'Sempre exclusivo - Grupo de Consumo' as NomeClube
,dbo.getsaldo(a.idafiliado) as saldo
from usuario u
inner join afiliado a on a.idusuario = u.idusuario
inner join afiliado apai on apai.idafiliado = a.idafiliadopai
inner join usuario upai on upai.idusuario = apai.idusuario
where u.ativo = 1 and u.nome like @nome
end
else
if (@login is not null)
begin
select
a.idafiliado,
u.login, u.nome NomeUsuario, upai.login loginpai, u.email,u.cpfcnpj,
'Sempre exclusivo - Grupo de Consumo' as NomeClube
,dbo.getsaldo(a.idafiliado) as saldo
from usuario u
inner join afiliado a on a.idusuario = u.idusuario
inner join afiliado apai on apai.idafiliado = a.idafiliadopai
inner join usuario upai on upai.idusuario = apai.idusuario
where u.ativo = 1 and u.login like @login
end
else
if (@cpf is not null )
begin
select
a.idafiliado,
u.login, u.nome NomeUsuario, upai.login loginpai, u.email,u.cpfcnpj,
'Sempre exclusivo - Grupo de Consumo' as NomeClube
,dbo.getsaldo(a.idafiliado) as saldo
from usuario u
inner join afiliado a on a.idusuario = u.idusuario
inner join afiliado apai on apai.idafiliado = a.idafiliadopai
inner join usuario upai on upai.idusuario = apai.idusuario
where u.ativo = 1 and u.cpfcnpj = @cpf
end
end
GO
In the code the procedure is called as follows:
if (UsuarioUtil.IsAdmin())
{
//UsuarioUtil uUtil = new UsuarioUtil();
string nome = txtNome.Text;
string login = TxtLogin.Text;
string cpf = TxtCpf.Text;
gdvAfiliados.DataSource = _DB.spc_listaafiliadosadmin(nome,login,cpf).ExecuteDataTable(); //_DB.vw_Afiliado.Where(a => a.ativo).ToList(); // uUtil.getAfils("");
gdvAfiliados.DataBind();
gdvAfiliados.HeaderRow.TableSection = TableRowSection.TableHeader;
ScriptManager.RegisterStartupScript(Page, typeof(Page), "", "SetDataTable();", true);
}
But when executed it gives this error
Procedure or function spc_listaafiliadosadmin has too many arguments specified.
Procedure method:
public SPClass spc_listaafiliadosadmin(string nome,string login,string cpf)
{
SPClass c = new SPClass(this);
DbCommand cmd = c.context.Database.Connection.CreateCommand();
cmd.CommandText = "spc_listaafiliadosadmin";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 600000;
if (nome != null)
cmd.Parameters.Add(new SqlParameter("@nome",nome));
if (login != null)
cmd.Parameters.Add(new SqlParameter("@login",login));
if (cpf != null)
cmd.Parameters.Add(new SqlParameter("@cpf",cpf));
c.command = cmd;
return c;
}