Blood Donation is one of the important health-related services, which is used to save human life in emergencies. Especially in this article, I try to explain about databases, tables and stored procedures.
Create Database
Create Database BloodDonar The above sql code is used to create database.
Blood Donor Database Table
Table: DonarDetails
This is the table used to store blood donor information
create table donardetails(
ID int identity(1,1),
donarid nvarchar(12) primary key,
donarusername nvarchar(12),
donarpassword nvarchar(25),
donarfullname nvarchar(25),
donardob nvarchar(20),
donargender nchar(2),
donarstate nvarchar(25),
donarcity nvarchar(25),
donaremailid nvarchar(30),
donarphone nvarchar(15),
donarbloodgroup nchar(3),
status nchar(10)
)
In the above SQL code snippet, the donarid column is an nvarchar and it is not an auto-generated column. Blood donar database designed for beginners who just started their career in dotnet.
Please enhance the SQL code.
Stored Procedure: Insert Blood Donor
The proc_insertBloodDonarDetails is used to store newly blood donated guy's details in the database table. The advantage of storing the blood donor details we can track each blood donor individual.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_insertBloodDonarDetails] Script Date: 03/08/2016 20:50:47 ******/
Create procedure [dbo].[proc_insertBloodDonarDetails](
@donarusername varchar(30),
@donarpassword varchar(30),
@donarfullname varchar(50),
@donardateofbirth varchar(30),
@donargender varchar(20),
@donarstate varchar(25),
@donarcity varchar(25),
@donaremailaddress varchar(50),
@donarphonenumber varchar(30),
@donarBloodGroup varchar(30),
@donarstatus nchar(10)
)
As begin
Insert into donardetails(donarid, donarusername, donarpassword, donarfullname, donardob, donargender, donarstate, donarcity, donaremailid, donarphone,donarbloodgroup,status)
values (@donarid, @donarusername, @donarpassword, @donarfullname, @donardateofbirth, @donargender, @donarstate, @donarcity, @donaremailaddress, @donarphonenumber, @donarBloodGroup, @donarstatus)
End
Get All Blood Donar Details Stored Procedure Query
Using this proc_getALLDonarDetails we will get all blood donor details who are frequently donating blood.
Let me differentiate between active or inactive
- If any donor frequently donates blood or is in contact with the blood donation services we can call them an active donor.
- If any donor not donating blood or is not in contact or we are unable to contact those donors we call as inactive donors or we can update their status is inactive.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_getALLDonarDetails] Script Date: 03/08/2016 20:57:12 ******/
Create Procedure [dbo].[proc_getALLDonarDetails]
as begin
select * from donardetails where status='active'
end
Get Single Donor Details
The proc_getsingledonardetails stored procedure is used to fetch a particular donor's details based on a condition. The condition of this stored procedure is that we need to pass that donor username, and the account status should be active; otherwise, it will return blank.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_getsingledonardetails] Script Date: 03/08/2016 20:52:32 ******/
CREATE PROCEDURE [dbo].[proc_getsingledonardetails](
@donarusername nvarchar(25)
)
as begin
SELECT * FROM donardetails WHERE donarusername=@donarusername AND status='active'
END
Verify Blood Donor
The proc_getDonarDetails stored procedure is used to fetch particular donor records based on the donor username and password, and his account should also be active. If the record is fetched successfully, then the donor credential is verified.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_getDonarDetails] Script Date: 03/08/2016 20:53:52 ******/
Create Procedure [dbo].[proc_getDonarDetails](
@dusername nvarchar(25),
@donarpassword nvarchar(25)
)
as begin
select * from donardetails where donarusername=@dusername and donarpassword=@donarpassword and status='active'
end
Update Blood Donor
The proc_UpdateDonarDetails is used to modify the existing blood donor's information in the database donardetails table.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_UpdateDonarDetails] Script Date: 03/08/2016 21:01:56 ******/
Create Procedure [dbo].[proc_UpdateDonarDetails](
@did nvarchar(12),
@dusername nvarchar(25),
@dpassword nvarchar(25),
@dfullname nvarchar(25),
@ddob nvarchar(20),
@dgender nchar(2),
@demailid nvarchar(30),
@dphone nvarchar(15),
@dbloodgroup nchar(5)
)
as begin
update donardetails set donarusername=@dusername, donarpassword=@dpassword, donarfullname=@dfullname, donardob=@ddob, donargender=@dgender, donaremailid=@demailid, donarphone=@dphone, donarbloodgroup=@dbloodgroup where donarid=@did
end
Delete Blood Donor
The proc_proc_DeleteDonarDetails is used to delete the existing blood donor's information in the database donardetails table.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_DeleteDonarDetails] Script Date: 03/08/2016 21:00:51 ******/
Create Procedure [dbo].[proc_DeleteDonarDetails](
@did nvarchar(12)
)
as begin
Delete donardetails where donarid=@did
end
Filter Blood Donor Details
This proc_FilterBloodDonarDetails stored procedure is used to fetch the list of particular blood donor who belongs to the selected state and city.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_FilterBloodDonarDetails] Script Date: 03/08/2016 21:03:09 ******/
Create procedure [dbo].[proc_FilterBloodDonarDetails](
@state varchar(25),
@city varchar(25),
@bloodgroup nchar(3))
as begin
select * from donardetails where donarstate=@state and donarcity=@city and donarbloodgroup=@bloodgroup
end
To get more information regarding blood donar project source code like Business Object, Database & Stored Procedure, Database Logic and User Interface
0 Comments