ASP.NET Project Tutorial Blood Bank Database Table

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)
)

create blood bank details database table

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)
)


blood donar request post status table

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

Post a Comment

0 Comments