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.
Blood Bank Database Table
The blood bank donation services center details are stored in this blood bank details database table.
create table bloodbankdetails(
Id int identity,
bbid nvarchar(10) primary key,
bbname nvarchar(160),
bbaddress nvarchar(260),
bblandmark nvarchar(150),
bbphone1 nvarchar(15),
bbphone2 nvarchar(15),
bbemail nvarchar(30),
bbstate nvarchar(25),
bbcity nvarchar(25),
userid nvarchar(12),
bbstatus nchar(10)
)
Insert Blood Bank
The proc_InsertBloodBankDetails stored procedure is used to add blood bank service organization details into the blood bankdetails table.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_InsertBloodBankDetails] Script Date: 03/08/2016 21:23:22 ******/
Create procedure [dbo].[proc_InsertBloodBankDetails](
@bbid varchar(10),
@bbname varchar(160),
@bbaddress varchar(250),
@bblandmark varchar(150),
@bbphone1 varchar(15),
@bbphone2 varchar(15),
@bbemail varchar(30),
@bbstate varchar(15),
@bbcity varchar(15),
@userid varchar(12),
@bbstatus nchar(10)
)
as begin
Insert into bloodbankdetails(bbid, bbname, bbaddress, bblandmark, bbphone1, bbphone2, bbemail, bbstate, bbcity, userid, bbstatus) values(@bbid, @bbname, @bbaddress,@bblandmark, @bbphone1, @bbphone2, @bbemail, @bbstate, @bbcity, @userid, @bbstatus)
end
Get Single Blood Bank Details
Using this stored procedure, i.e., proc_singlebloodbankdetails, to fetch particular blood bank details based on blood bank ID, irrespective of status,. means fetching both active and inactive blood bank details.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_singlebloodbankdetails] Script Date: 03/08/2016 21:24:39 ******/
Create procedure [dbo].[proc_singlebloodbankdetails](
@bbid varchar(10)
)
as begin
select * from bloodbankdetails where bbid=@bbid
end
Get All Blood Bank Details
To get all blood bank details based on whose blood bank status is active, we use the below-stored procedure, i.e., proc_getAllBloodBankData.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_getAllBloodBankData] Script Date: 03/08/2016 21:26:47 ******/
Create procedure [dbo].[proc_getAllBloodBankData]
as begin
select * from bloodbankdetails where bbstatus='active'
end
Filter Blood Bank Details by State and City
Fetching the list of blood bank details from the blood bank details database table based on state and city.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_getBloodbank_StateCity] Script Date: 03/08/2016 21:27:30 ******/
Create procedure [dbo].[proc_getBloodbank_StateCity](
@bbstate varchar(25),
@bbcity varchar(25)
)
as begin
select * from bloodbankdetails where bbstate=@bbstate and bbcity=@bbcity
end
Post Status Database Table
This table is used to post blood donor requirements on the website or send an SMS or email. To maintain this requirement, this table is used, and the name of this table is PostStatus. Once you find your requirement, i.e., blood donor contact or donated blood, this post status will be closed or inactive.
create table PostStatus(
ID int identity(1,1),
donarid nvarchar(12) references donardetails(donarid),
statuspostid nvarchar(10) primary key,
statusmessage nvarchar(160),
statuspostedtime nvarchar(30),
nooftimepostedited nchar(2)
)
Insert Post Status
This proc_InsertStatusData stored procedure is used to add a new post requirement to the database table.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_InsertStatusData] Script Date: 03/08/2016 21:39:57 ******/
Create procedure [dbo].[proc_InsertStatusData](
@donarid nvarchar(12),
@statusid nvarchar(10),
@statusmessage nvarchar(160),
@poststatustime date,
@nooftimestatusedited nchar(2)
)
as begin
Insert into PostStatus(donarid, statuspostid, statusmessage, statuspostedtime, nooftimepostedited) values(@donarid, @statusid, @statusmessage, @poststatustime, @nooftimestatusedited)
end
Get All Post Status
To fetch all the post status information regarding a particular donor, we can use this stored procedure, i.e., proc_getAllUserStatus.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_getAllUserStatus] Script Date: 03/08/2016 21:40:20 ******/
Create procedure [dbo].[proc_getAllUserStatus](
@donarid nvarchar(12)
)
as begin
select * from PostStatus where donarid=@donarid
end
Count Number of Post Status
Using this proc_NoOfStatusPosted stored procedure, we can get the total number of posts till now posted or stored in the database.
USE [BloodDonar]
GO
/****** Object: StoredProcedure [dbo].[proc_NoOfStatusPosted] Script Date: 03/08/2016 21:40:52 ******/
Create procedure [dbo].[proc_NoOfStatusPosted]
as begin
select count(*) from PostStatus
end
Download Source Code
Download the complete source code for the Blood Donor Project from the provided link and explore its functionalities.
To get more information regarding blood donar project source code like Business Object, Database & Stored Procedure, Database Logic and User Interface
0 Comments