2.9 Разработка хранимых процедур

В данном курсовом проекте в ходе разработки информационной системы были созданы хранимые процедуры на добавление, обновление и удаление данных для каждой из таблиц, а также хранимые процедуры для идентификации пользователя:

1. Таблица Кафедра (dbo.Department)

1.1. Удаление данных

Use Университет

Go

Create proc [dbo].[DepartmentDelete]

(@ID int,@re int output)

as

set @re=0

if exists (select * from Department

where ID_Dep=@ID)

begin

if (exists (select * from Teachers

where Department=@ID))

or (exists (select * from Speciality

where Department=@ID))

set @re= -1 /*Нельзя удалить, потому что на кафедре есть преподаватели или специальности*/

else

delete from Department

where ID_Dep=@ID

end

else set @re= -2 /*Нельзя удалить, потому что нет такой кафедры*/

1.2. Добавление данных

Use Университет

Go

Create proc [dbo].[DepartmentInsert]

(@ShortName nvarchar(10),

@FullName nvarchar(100),@re int output)

as

set @re=0

if not exists (select * from Department

where ShortName=@ShortName or

FullName=@FullName)

insert into Department(ShortName,FullName) values(@ShortName,@FullName)

else set @re= -1

1.3. Обновление данных

Use Университет

Go

Create [dbo].[DepartmentUpdate]

(@ID int, @ShortName nvarchar(10),@FullName nvarchar(100),@re int output)

as

set @re=0

if exists (select * from Department

where ID_Dep=@ID)

begin

if exists (select * from Department

where (ShortName=@ShortName or

FullName=@FullName) and @ID<>ID_Dep)

set @re= -2else

update Department

set ShortName=@ShortName, FullName=@FullName

where ID_Dep=@ID

end

else set @re= -1

Входные параметры, используемые в данных процедурах:

@id int - Номер кафедры;

@ShortName nvarchar(10) - Краткое название кафедры;

@FullName nvarchar(100) - Полное название кафедры;

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

2. Таблица Экзамен (dbo. Exam)

2.1. Удаление данных

Use Университет

Go

Create [dbo].[ExamDelete]

(@ID int, @re int output)

as

set @re=0

if exists(select * from Exam where ID_Exam=@ID)

delete from Exam where ID_Exam=@ID

else set @re=-1

2.2. Добавление данных

Use Университет

Go

Create proc [dbo].[ExamInsert]

(@FIO nvarchar(5),@Subject nvarchar(50),@Mark nvarchar(1), @Date datetime, @re int output)

as

set @re=0

declare @subj int

if exists(select * from Student where Nzach=@FIO)

if exists (select * from subject where [name]=@Subject)

if (@mark='3')or(@mark='4')or(@mark='5')or(@mark='2')

begin

select @subj=Nsubject from subject where [name]=@Subject

if not exists(select * from Exam where (Nzach=@FIO)and(Date=@Date))

insert into Exam (Nzach,Nsubject,mark,date)

values (@FIO,@subj,@mark,@date)

else set @re=-4

end

else set @re=-3

else set @re=-2

else set @re=-1

2.3. Обновление данных

Use Университет

Go

Create proc [dbo].[ExamUpdate]

(@ID int,@FIO nvarchar(5),@Subject nvarchar(50),@Mark int, @Date datetime, @re int output)

as

set @re=0

declare @subj int

if exists(select * from exam where ID_Exam=@ID)

if exists(select * from Student where Nzach=@FIO)

if exists (select * from subject where [name]=@Subject)

if (@mark='3')or(@mark='4')or(@mark='5')or(@mark='2')

begin

select @subj=Nsubject from subject where [name]=@Subject

if not exists(select * from Exam where (ID_Exam<>@ID) and(Nzach=@FIO)and(Date=@Date))

update Exam

set Nzach=@FIO,Nsubject=@subj,mark=@mark,date=@date

where ID_Exam=@ID

else set @re=-4

end

else set @re=-3

else set @re=-2

else set @re=-1

else set @re=-5

Входные параметры, используемые в данных процедурах:

@id int - Номер экзамена;

@FIO nvarchar(5) - Номер зачётной книжки студента;

@Subject nvarchar(50)-Название сдаваемого предмета;

@Mark int - Оценка за экзамен;

@Date datetime - Дата сдачи экзамена;

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

3. Таблица Факультет (dbo. Faculty)

3.1. Удаление данных

Use Университет

Go

Create proc [dbo].[FacultyDelete]

(@ID int,@re int output)

as

set @re=0

if exists (select * from Faculty

where ID_Faculty=@ID)

begin

if (exists (select * from Student

where Faculty=@ID))

set @re= -1else

delete from Faculty

where ID_Faculty=@ID

end

else set @re= -2

3.2. Добавление данных

Use Университет

Go

Create roc [dbo].[FacultyInsert]

(@ShortName nvarchar(10),@FullName nvarchar(100),@re int output)

as

set @re=0

if not exists (select * from Faculty

where ShortName=@ShortName or

FullName=@FullName)

insert into Faculty(ShortName,FullName) values(@ShortName,@FullName)

else set @re= -1

3.3. Обновление данных

Use Университет

Go

Create proc [dbo].[FacultyUpdate]

(@ID int, @ShortName nvarchar(10),@FullName nvarchar(100),@re int output)

as

set @re=0

if exists (select * from Faculty

where ID_Faculty=@ID)

begin

if exists (select * from Faculty

where (ShortName=@ShortName or

FullName=@FullName) and ID_Faculty<>@ID)

set @re= -2

else

update Faculty

set ShortName=@ShortName, FullName=@FullName

where ID_Faculty=@ID

end

else set @re= -1

Входные параметры, используемые в данных процедурах:

@id int - Номер факультета;

@ShortName nvarchar(10) - Краткое название факультета;

@FullName nvarchar(100) - Полное название факультета;

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

4. Таблица Группа (dbo. Group)

4.1. Удаление данных

Use Университет

Go

Create proc [dbo].[GroupDelete]

(@ID int,@re int output)

as

set @re=0

if exists (select * from [group] where ID_Group=@ID)

if not exists (Select * from student where [Group]=@ID)

delete from [Group]

where ID_Group=@ID

else set @re= -1

else set @re= -2

4.2. Добавление данных

Use Университет

Go

Create proc [dbo].[GroupInsert]

(@number nvarchar(4),@speciality nvarchar(6),@re int output)

as

set @re=0

if not exists (Select * from [Group] where Number=@number)

if exists (select * from speciality where nspec=@speciality)

begin

declare @p int

select @p=ID_Spec From Speciality where nspec=@speciality

insert into [Group](number,speciality)

values(@number,@p)

end

else set @re= -2

else set @re= -1

4.3. Обновление данных

Use Университет

Go

Create proc [dbo].[GroupUpdate]

(@ID int, @Number nvarchar(4),@speciality nvarchar(6),@re int output)

as

set @re=0

if exists (select * from [group]

where ID_Group=@ID)

if exists (select * from [group]

where Number=@Number and ID_Group<>@ID)

set @re= -2

else

if exists(select * from speciality where nspec=@speciality)

begin

declare @p int

select @p=ID_Spec from speciality where nspec=@speciality

update [group]

set Number=@Number, Speciality=@p

where ID_Group=@ID

end

else set @re=-3

else set @re= -1

Входные параметры, используемые в данных процедурах:

@id int - ID группы;

@Number nvarchar(4)-Номер группы;

@speciality nvarchar(6) -Номер специальности;

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

5. Таблица Стипендия (dbo. Grant)

5.1. Удаление данных

Use Университет

Go

Create proc [dbo].[GrantDelete]

(@ID int,@re int output)

as

set @re=0

if exists (Select * from [Grant]

where ID_Grant=@ID)

delete from [Grant]

where ID_Grant=@ID

else set @re= -1

5.2. Добавление данных

Use Университет

Go

Create proc [dbo].[GrantInsert]

(@Student nvarchar(5),@volume money,@date datetime,@re int output)

as

if exists (Select * from Student

where Nzach=@Student)

BEGIN

set @re=0

insert into [Grant](Student,GrantSize,date)

values (@Student,@volume,@date)

END

else set @re= -1

5.3. Обновление данных

Use Университет

Go

Create proc [dbo].[GrantUpdate]

(@ID int,@Student nvarchar(5),@volume money,@date datetime,@re int output)

as

if exists (Select * from [Grant]

where ID_Grant=@ID)

if exists (Select * from Student

where Nzach=@Student)

begin

set @re=0

update [Grant]

set student=@Student,GrantSize=@volume,date=@date

where ID_Grant=@ID

end

else set @re= -2

else set @re= -1

Входные параметры, используемые в данных процедурах:

@id int - ID стипендии;

@Student nvarchar(5)-Номер зачётной книжки студента;

@volume money - Объём стипендии;

@date datetime - Дата начисления стипендии.

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

6. Таблица Учёная степень (dbo. SDegree)

6.1. Удаление данных

Use Университет

Go

Create proc [dbo].[SDegreeDelete]

(@ID int, @re int output)

as

if exists (Select * from SDegree

where ID_UchStep=@ID)

begin

set @re=0

delete from SDegree

where ID_UchStep=@ID

end

else set @re= -1

6.2. Добавление данных

Use Университет

Go

Create proc [dbo].[SDegreeInsert]

(@Teachers int,@sdegree nvarchar(50),@date datetime,@re int output)

as

if exists (Select * from Teachers

where Nprep=@Teachers)

BEGIN

insert into SDegree(Teachers,SDegree,date)

values (@Teachers,@sdegree,@date)

set @re=0

END

else set @re= -1

6.3. Обновление данных

Use Университет

Go

Create proc [dbo].[SDegreeUpdate]

(@ID int,@Teachers int,@SDegree nvarchar(50),@date datetime,@re int output)

as

if exists (Select * from SDegree

where ID_UchStep=@ID)

if exists (Select * from Teachers

where Nprep=@Teachers)

begin

set @re=0

update SDegree

set Teachers=@Teachers,SDegree=@SDegree,date=@date

where ID_UchStep=@ID

end

else set @re= -2

else set @re= -1

Входные параметры, используемые в данных процедурах:

@id int - ID учёной степени;

@Teachers int - Номер преподавателя;

@SDegree nvarchar(50)-Наименование учёной степени;

@date datetime - Дата получения учёной степени.

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

7. Таблица Специальность (dbo. Speciality)

7.1. Удаление данных

Use Университет

Go

Create proc [dbo].[SpecialityDelete]

(@ID int,@re int output)

as

set @re=0

if exists (select * from Speciality

where ID_Spec=@ID)

begin

if exists (select * from [Group]

where Speciality=@ID)

set @re= -1

else

delete from Speciality

where ID_Spec=@ID

end

else set @re= -2

7.2. Добавление данных

Use Университет

Go

Create proc [dbo].[SpecialityInsert]

(@Nspec nvarchar(6),@Name nvarchar(100),@Department nvarchar(10),@StudyCost money,@re int output)

as

set @re=0

if not exists (select * from Speciality

where Nspec=@Nspec or

[Name]=@Name)

if exists (Select * from Department

where ShortName=@Department)

begin

declare @p int

select @p=ID_Dep from Department where ShortName=@Department

insert into Speciality(Nspec,[Name],Department,StudyCost)

values (@Nspec,@Name,@p,@StudyCost)

end

else set @re= -2

else set @re= -1

7.3. Обновление данных

Use Университет

Go

Create proc [dbo].[SpecialityUpdate]

(@ID int,@Nspec nvarchar(6),@Name nvarchar(100),@Department nvarchar(10),@StudyCost money,@re int output)

as

set @re=0

if not exists (select * from Speciality

where (Nspec=@Nspec or

[Name]=@Name) and ID_Spec<>@ID)

begin

if exists (select * from Department where ShortName=@Department)

begin

declare @p int

select @p=ID_Dep from Department where ShortName=@Department

Update Speciality

set Nspec=@Nspec,[Name]=@Name,Department=@p,StudyCost=@StudyCost

where ID_Spec=@ID

end

else set @re=-1

end

else set @re= -2

Входные параметры, используемые в данных процедурах:

@id int -номер специальности;

@Nspec nvarchar(6) - код специальности;

@Name nvarchar(100) - название специальности;

@Department nvarchar(10) - краткое название выпускающей кафедры;

@StudyCost money - стоимость обучения на специальности

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

8. Таблица Студент (dbo. Student)

8.1. Удаление данных

Use Университет

Go

Create proc [dbo].[StudentDelete]

(@Nzach nvarchar(5),@re int output)

as

set @re=0

if exists (select * from student where Nzach=@Nzach)

begin

delete from Exam where Nzach=@Nzach

delete from [Grant] where Student=@Nzach

delete from Student where Nzach=@Nzach

end

else set @re=-1

8.2. Добавление данных

Use Университет

Go

Create proc [dbo].[StudentInsert]

(@Nzach nvarchar(5),@FIO nvarchar(50),@group nvarchar(4),@faculty nvarchar(10),@fo nvarchar(10),@factory nvarchar(100),@ispay bit,@re int Output)

as

set @re=0

if not exists (select * from student where Nzach=@Nzach)

if exists(select * from [Group] where Number=@group)

if exists (select * from Faculty where ShortName=@faculty)

begin

declare @a int,@b int

select @a=ID_Group from [Group] where Number=@group

select @b=ID_Faculty from Faculty where ShortName=@faculty

insert into Student

values(@Nzach,@FIO,@b,@a,@fo,@factory,@ispay)

end

else set @re=-3

else set @re=-2

else set @re=-1

8.3. Обновление данных

Use Университет

Go

Create proc [dbo].[StudentUpdate]

(@Nzach nvarchar(5),@FIO nvarchar(50),@group nvarchar(4),@faculty nvarchar(10),@fo nvarchar(10),@factory nvarchar(100),@ispay bit,@re int Output)

as

set @re=0

if exists(select * from [Group] where Number=@group)

if exists (select * from Faculty where ShortName=@faculty)

begin

declare @a int,@b int

select @a=ID_Group from [Group] where Number=@group

select @b=ID_Faculty from Faculty where ShortName=@faculty

update Student

set FIO=@FIO,Faculty=@b,[Group]=@a,StudyForm=@fo,Factory=@factory,IsPay=@ispay

where Nzach=@Nzach

end

else set @re=-3

else set @re=-2

Входные параметры, используемые в данных процедурах:

@Nzach nvarchar(5)- номер зачётной книжки;

@FIO nvarchar(50) - ФИО студента;

@group nvarchar(4) - номер группы студента;

@faculty nvarchar(10) - краткое навание факультета студента;

@fo nvarchar(10) - форма обучения;

@factory nvarchar(100) - предприятие, оплачивающее обучение (для студентов по целевому набору)

@ispay bit - платит ли студент за обучение

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

9. Таблица Предмет (dbo. Subject)

9.1. Удаление данных

Use Университет

Go

Create proc [dbo].[SubjectDelete]

(@Nsubject int,@re int output)

as

set @re=0

if exists (select * from subject

where Nsubject=@Nsubject)

begin

if (exists (select * from Teach

where Subject=@Nsubject))

or (exists (select * from Exam

where Nsubject=@Nsubject))

set @re= -1else

delete from subject

where Nsubject=@Nsubject

end

else set @re= -2

9.2. Добавление данных

Use Университет

Go

Create proc [dbo].[SubjectInsert]

(@Name nvarchar(50),@re int output)

as

set @re=0

if not exists (select * from Subject

where [Name]=@Name)

insert into Subject ([Name]) values(@Name)

else set @re= -1

9.3. Обновление данных

Use Университет

Go

Create proc [dbo].[SubjectUpdate]

(@ID int,@name nvarchar(50), @re int output)

as

Set @re=0

if not exists (select * from Subject where [name]=@name)

update Subject

set [Name]=@name

where Nsubject=@ID

else set @re=-1

Входные параметры, используемые в данных процедурах:

@ID int - Номер предмета;

@name nvarchar(50) - Название предмета.

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

10. Таблица Преподает (dbo. Teach)

10.1. Удаление данных

Use Университет

Go

Create proc [dbo].[TeachDelete]

(@ID int, @re int output)

as

set @re=0

if exists(select * from teach where ID_Teach=@ID)

delete from teach where ID_Teach=@ID

else set @re=-1

10.2. Добавление данных

Use Университет

Go

Create proc [dbo].[TeachInsert]

(@prep char(50),@Subject char(50),@date datetime, @re int output)

as

set @re=0

if exists(select * from teachers where FIO=@prep)

if exists(select * from subject where [Name]=@Subject)

begin

declare @a int,@b int

select @a=Nsubject from subject where [Name]=@Subject

select @b=Nprep from teachers where FIO=@prep

if not exists (select * from Teach where (subject=@a) and (Teachers=@b) and (date=@date))

insert into teach (Subject,Teachers,date)

values (@a,@b,@date)

else set @re=-2

end

else set @re=-3

else set @re=-1

10.3. Обновление данных

Use Университет

Go

Create proc [dbo].[TeachUpdate]

(@ID int,@prep char(50),@Subject char(50),@date datetime, @re int output)

as

set @re=0

if exists(select * from teach where ID_Teach=@ID)

if exists(select * from teachers where FIO=@prep)

if exists(select * from subject where [Name]=@Subject)

begin

declare @a int,@b int

select @a=Nsubject from subject where [Name]=@Subject

select @b=Nprep from teachers where FIO=@prep

if not exists (select * from Teach where (subject=@a)and(Teachers=@b)and(date=@date))

update teach

set Subject=@a,Teachers=@b,date=@date

where ID_Teach=@ID

else set @re=-4

end

else set @re=-3

else set @re=-1

else set @re=-2

Входные параметры, используемые в данных процедурах:

@ID int - Номерзаписи о преподавании;

@prep char(50) - ФИО преподавателя;

@Subject char(50) - название предмета;

@date datetime - Дата.

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

11. Таблица Преподаватель (dbo. Teachers)

11.1. Удаление данных

Use Университет

Go

Create proc [dbo].[TeachersDelete]

(@ID int, @re int output)

as

set @re=0

if exists (select * from Teachers where Nprep=@ID)

if not exists (select * from Teach where Teachers=@ID)

begin

delete from SDegree where Teachers=@ID

delete from salary where Teachers=@ID

delete from Teachers where Nprep=@ID

end

else set @re=-2

else set @re=-1

11.2. Добавление данных

Use Университет

Go

Create proc [dbo].[TeachersInsert]

(@FIO nvarchar(50),@Department nvarchar(10),@Dolznost nvarchar(50),@DatePrin datetime, @re int output)

as

set @re=0

if exists(select * from Department where ShortName=@Department)

begin

declare @p int

select @p=ID_Dep from Department where ShortName=@Department

insert into teachers (FIO,Department,Dolznost,DatePrin)

values (@FIO,@p,@Dolznost,@DatePrin)

end

else set @re=-1

11.3. Обновление данных

Use Университет

Go

Create proc [dbo].[TeachersUpdate]

(@ID int,@FIO nvarchar(50),@Department nvarchar(10),@Dolznost nvarchar(50),@DatePrin datetime, @re int output)

as

set @re=0

if exists(select * from teachers where Nprep=@ID)

if exists(select * from Department where ShortName=@Department)

begin

declare @p int

select @p=ID_Dep from Department where ShortName=@Department

update teachers

set FIO=@FIO,Department=@p,Dolznost=@Dolznost,DatePrin=@DatePrin

where Nprep=@ID

end

else set @re=-1

else set @re=-2

Входные параметры, используемые в данных процедурах:

@ID int - Номер преподавателя;

@FIO nvarchar(50)- ФИО преподавателя;

@Department nvarchar(10) - краткое название кафедры, где преподаватель работает

@Dolznost nvarchar(50) - должность, занимаема преподавателем

@DatePrin datetime - дата принятия на работу

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

12. Таблица Зарплата (dbo. Salary)

12.1. Удаление данных

Use Университет

Go

Create proc [dbo].[SalaryDelete]

(@ID int,@re int output)

as

if exists (Select * from salary

where ID_Zarplata=@ID)

begin

delete from salary

where ID_Zarplata=@ID

set @re=0

end

else set @re= -1

12.2. Добавление данных

Use Университет

Go

Create proc [dbo].[SalaryInsert]

(@Teachers int,@volume money,@date datetime,@re int output)

as

if exists (Select * from Teachers

where Nprep=@Teachers)

BEGIN

set @re=0

insert into Salary(Teachers,Volume,date)

values (@Teachers,@volume,@date)

END

else set @re= -1

12.3. Обновление данных

Use Университет

Go

Create proc [dbo].[SalaryUpdate]

(@ID int,@Teachers int,@volume money,@date datetime,@re int output)

as

if exists (Select * from salary

where ID_Zarplata=@ID)

if exists (Select * from Teachers

where Nprep=@Teachers)

begin

set @re=0

update Salary

set Teachers=@Teachers,volume=@volume,date=@date

where ID_Zarplata=@ID

end

else set @re= -2

else set @re= -1

Входные параметры, используемые в данных процедурах:

@ID int - Номер записи о зарплате;

@Teachers int - Номер преподавателя

@volume money - размер зарплаты

@date datetime - дата начисления

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

13. Идентификация пользователей

13.1. Идентификация студента

Create proc [dbo].[StuPass]

(@pass nvarchar(5),@re int output,@FIO nvarchar(50)output)

AS

set @re=0

if exists (select * from student where Nzach=@pass)

select @FIO=FIO from Student where Nzach=@pass

else set @re=-1

13.2. Идентификация преподавателя

Create proc [dbo].[TeachPass]

(@pass int,@re int output,@FIO nvarchar(50) output)

AS

set @re=0

if exists (select * from teachers where Nprep=@pass)

select @FIO = FIO from TEACHERS where Nprep=@pass

else set @re=-1

Входные параметры, используемые в данных процедурах:

@pass nvarchar(5)/ @pass int - пароль пользователя (соответственно номер зачётной книжки и номер преподавателя)

Выходные параметры, используемые в данных процедурах:

@Re int - Вспомогательная переменная, позволяющая определить успешность или не успешность выполнения операции.

@FIO nvarchar(50) - ФИО пользователя

3. РАЗРАБОТКА КЛИЕНТСКОЙ ЧАСТИ ИНФОРМАЦОННОЙ СИСТЕМЫ

3.1 Организация взаимодействия клиентской программы с БД

Для доступа к базе данных использовалась технология ADO.NET. Экземпляры компонентов доступа к БД, применённые в данном проекте, приведены в таблице 1.

Таблица 1

3.2 Разработка форм