Olá a todos.
Usaremos LINQ no nosso projeto. Para as operações no banco de dados usaremos stored procedures. Tornando o nosso sistema mais seguro contra SQL Injections e com uma performace maior.
Fiz todas as stored procedures que serão usadas para manipular a tabela de usuário.
Para as outras tabalas (exceto a de Log), será feito dessa forma.
Stored Procedures:
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_add] Script Date: 06/16/2009 23:31:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_add;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Insere um registro na tabela de Usuario
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_add]
(
@Nome varchar(50),
@Login varchar(25),
@Senha varchar(32),
@Dica varchar(25),
@Tipo char(1),
@inserted_id int output
)
AS
SET NOCOUNT ON
insert into Usuario
(Nome, [Login], Senha, Dica, Tipo)
values
(@Nome, @Login, @Senha, @Dica, @Tipo)
set @inserted_id = SCOPE_IDENTITY()
SET ANSI_NULLS ON
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_alter] Script Date: 06/16/2009 23:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_alter;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Altera um registro na tabela de Usuario
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_alter]
(
@Id int,
@Nome varchar(50),
@Login varchar(25),
@Senha varchar(32),
@Dica varchar(25),
@Excluido bit,
@Tipo char(1)
)
AS
SET NOCOUNT ON
update Usuario
set
Nome = @Nome, [Login] = @Login, Senha = @Senha, Dica=@Dica, Excluido = @Excluido, Tipo = @Tipo
where
Id = @Id
SET ANSI_NULLS ON
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_autentica_administrador] Script Date: 06/16/2009 23:32:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_autentica_administrador;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Verifica se o usuário tem permissão de administrador
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_autentica_administrador](
@login varchar(50)
)
AS
SET NOCOUNT ON
SELECT *
FROM Usuario
WHERE [Login] = @login
AND ( Tipo = 'A')
AND (Excluido = 0)
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_autentica_orientador] Script Date: 06/16/2009 23:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_autentica_orientador;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Verifica se o usuário tem permissão de orientador
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_autentica_orientador](
@login varchar(50)
)
AS
SET NOCOUNT ON
SELECT *
FROM Usuario
WHERE [Login] = @login
AND ( Tipo = '0')
AND (Excluido = 0)
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_autentica_pesquisador] Script Date: 06/16/2009 23:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_autentica_pesquisador;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Verifica se o usuário tem permissão de pesquisador
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_autentica_pesquisador](
@login varchar(50)
)
AS
SET NOCOUNT ON
SELECT *
FROM Usuario
WHERE [Login] = @login
AND ( Tipo = 'P')
AND (Excluido = 0)
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_autentica_senha] Script Date: 06/16/2009 23:33:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_autentica_senha;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Verifica a senha de um usuário
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_autentica_senha](
@login varchar(25),
@senha varchar(32)
)
AS
SET NOCOUNT ON
SELECT *
FROM Usuario
WHERE [Login] = @login
AND Senha = @senha
AND (Excluido = 0)
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_delete] Script Date: 06/16/2009 23:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_delete;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Exclui um registro na tabela de Usuario
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_delete]
(
@Id int
)
AS
SET NOCOUNT ON
update Usuario
set
Excluido = 1,
[Login] = [Login]+'_deletado('+convert(varchar,getdate(),126)+')'
where
Id = @Id
SET ANSI_NULLS ON
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_select] Script Date: 06/16/2009 23:33:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_select;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Seleciona os registros na tabela de Usuario
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_select]
AS
SET NOCOUNT ON
select * from Usuario
where Excluido = 0
order by Nome;
SET ANSI_NULLS ON
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_select_by_id] Script Date: 06/16/2009 23:33:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_select_by_id;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Seleciona um registro na tabela de Usuario
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_select_by_id]
(
@Id int
)
AS
SET NOCOUNT ON
select * from Usuario
where
Id = @Id
AND (Excluido = 0)
order by Nome;
SET ANSI_NULLS ON
USE [GraphMatics]
GO
/****** Object: StoredProcedure [dbo].[sp_usuario_select_by_login] Script Date: 06/16/2009 23:33:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************
Name: sp_usuario_select_by_login;
Author: Felipe Oliveira Campos
Date Created: 2009-06-15
Description: Seleciona um registro na tabela de Usuario
*********************************************/
ALTER PROCEDURE [dbo].[sp_usuario_select_by_login]
(
@Login varchar(25)
)
AS
SET NOCOUNT ON
select * from Usuario
where
([Login] = @Login)
and (Excluido = 0)
order by Nome;
SET ANSI_NULLS ON
Qualquer dúvida ou sugestão entrem em contato comigo ou com o Gabriel.
Boa noite, abreços,
Felipe Campos
Nenhum comentário:
Postar um comentário