Database of Society Management System Project

Database of Society Management System Project – ASP.Net C#

In this asp.net c# project post we will discuss about database of society management system project. In our previous post we have already given project source code download link and in this post we give you full database of society system project. First, download project from previous post and create database manually as shows in below post. We have provide this free project for students, only for educational purpose.

meeraacademy.com
Database of Society Management System Project

Society Management System Project SQL database Tables.

Here, we provide all sql tables and stored procedure for Society Management Project.

TableName : ComplainMst
Table stores Complain detail of Society members.
Columns :
CID
int  – Complain ID [Primary_Key and Auto_increments]
UID int – User ID, who makes complain
Subject nvarchar(50) – Subject of complain
Complain nvarchar(500) – Complain message
Reply nvarchar(500) – Answer/solution of complain
Status nvarchar(50) – Status of complain solved/pending
EntryDate datetime – Complain entry date
TableName : HouseMst
Table stores House detail of members.
Columns :
HID
int  – House ID [Primary_Key and Auto_increments]
BlockNo int
Type nvarchar(50) – Type of House (1bhk, 2bhk, etc..)
Detail nvarchar(50)
SName nvarchar(50) – Society Name
Image nvarchar(500) – Picture of House
EntryDate datetime – House entry date
TableName : MessageMSt
Table stores Message detail of society members.
Columns :
MID
int  – Message ID [Primary_Key and Auto_increments]
FromName nvarchar(50) – Sender
ToName nvarchar(50) – Receiver
Subject nvarchar(50)
Message nvarchar(500)
EntryDate datetime – Message entry date
TableName : RentMst
Table stores house rent information of members.
Columns :
RID
int  – Rent ID [Primary_Key and Auto_increments]
HID int – House ID
SName nvarchar(50) – Society Name
UID int – Owner ID of house
Rent float – Rent Price
EntryDate datetime
TableName : SellMst
Table stores house sell detail of members.
Columns :
SID
int  – Sell ID [Primary_Key and Auto_increments]
HID int – House ID
SName nvarchar(50) – Society Name
UID int – Owner ID of house
Sell float – Sell Price
EDate datetime – Complain entry date
TableName : SocietyMst
Table stores society information.
Columns :
SID
int  – Society ID [Primary_Key and Auto_increments]
SName nvarchar(50) – Society Name
Address nvarchar(50)
City nvarchar(50)
Pincode nvarchar(50)
NoHouse int – Total no of house in society
Image nvarchar(600) – Picture of society
EntryDate datetime – Society entry date
TableName : UserMst
Table stores information of members.
Columns :
UID
int  – Member ID [Primary_Key and Auto_increments]
FName nvarchar(50) – User First Name
LName nvarchar(50) – User Last Name
Email nvarchar(50)
Mobile nvarchar(50)
Bdate datetime – Birth Date
HouseID int
SocietyName nvarchar(50)
Member int – Total member in family
UserName nvarchar(50) – Username for log in to system
Password nvarchar(50) – Password for log in to system
Image nvarchar(700) – Picture of User
EntryDate datetime
meeraacademy.com
SQL Table of Society Management ASP.Net Project.

Now, After create database tables then create stored procedure from below.
Here, we have given all stored procedure of society management system project.

Step to create database

Step 1 : Create New Database HHelp in SQL Server
Step 2 : Create all tables from above
Step 3 : Create stored procedure from below
Step 4 : Open New Query Windows –> Copy Stored Procedure Code –>  Past code –> Execute query –> Done
Step 5 : Run project
Step 6 : Admin side username = “admin” and password= “admin”

SQL Stored Procedures

/****** Object:  StoredProcedure [dbo].[USERMST_UPDATE]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_UPDATE]
@FNAME AS NVARCHAR(256),
@LNAME AS NVARCHAR(256),
@EMAIL AS NVARCHAR(256),
@MOBILE AS NVARCHAR(256),
@BDATE AS DATETIME,
@UID AS INT

AS
BEGIN

UPDATE UserMst SET FNAME=@FNAME,LNAME=@LNAME,EMAIL=@EMAIL,MOBILE=@MOBILE,BDATE=@BDATE WHERE UID=@UID
END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_SELECT_BY_UNAME]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_SELECT_BY_UNAME]
 
@UNAME AS NVARCHAR(256)
AS
BEGIN
SELECT * FROM  USERMST WHERE Username=@UNAME
END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_SELECT_by_uid]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_SELECT_by_uid]
@UID AS INT

AS
BEGIN

SELECT * FROM UserMst WHERE UID=@UID

END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_SELECT_by_SNAME]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[USERMST_SELECT_by_SNAME]
@sname as nvarchar(256)

AS
BEGIN

SELECT * FROM UserMst WHERE SocietyName=@sname

END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_SELECT_BY_LOGIN]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_SELECT_BY_LOGIN]
@UNAME AS NVARCHAR(256),
@UPASS AS NVARCHAR(256)

AS
BEGIN

SELECT * FROM UserMst WHERE Username=@UNAME AND Password=@UPASS

END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_SELECT_by_HID]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[USERMST_SELECT_by_HID]
@hid AS INT

AS
BEGIN

SELECT * FROM UserMst WHERE HouseID=@hid

END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_SELECT_BY_EMAIL_AND_mobileno]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_SELECT_BY_EMAIL_AND_mobileno]
 
@EMAIL AS NVARCHAR(256),
@MOBILE AS NVARCHAR(256)
AS
BEGIN
SELECT * FROM  USERMST WHERE Email=@EMAIL AND Mobile=@MOBILE
END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_SELECT_by_email]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_SELECT_by_email]
@EMAIL  AS NVARCHAR(256)

AS
BEGIN

SELECT * FROM UserMst WHERE Email=@EMAIL

END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_SELECT]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_SELECT]


AS
BEGIN

SELECT * FROM UserMst

END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_INSERT]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_INSERT]
@FNAME AS NVARCHAR(256),
@LNAME AS NVARCHAR(256),
@EMAIL AS NVARCHAR(256),
@MOBILE AS NVARCHAR(256),
@BDATE AS DATETIME,
@HID AS INT,
@Sname AS nvarchar(256),
@MEMBER AS INT,
@UNAME  AS NVARCHAR(256),
@PASS AS NVARCHAR(256),
@img as nvarchar(700)

AS
BEGIN

insert into UserMst values (@FNAME,@LNAME,@EMAIL,@MOBILE,@BDATE,@HID,@Sname,@MEMBER,@UNAME,@PASS,@img,GETDATE())
END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_DELETE]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_DELETE]
@UID AS INT

AS
BEGIN

DELETE  FROM UserMst WHERE UID=@UID

END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_CHANGE_PICTURE]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_CHANGE_PICTURE]
 @UID AS INT,
@IMG AS NVARCHAR(500)
AS
BEGIN
UPDATE USERMST SET Image=@IMG WHERE UID=@UID
END
GO
/****** Object:  StoredProcedure [dbo].[USERMST_CHANGE_PASSWORD]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USERMST_CHANGE_PASSWORD]
 @UID AS INT,
@PASS AS NVARCHAR(256)
AS
BEGIN
UPDATE USERMST SET PASSWORD=@PASS WHERE UID=@UID
END
GO
/****** Object:  StoredProcedure [dbo].[SOCIETY_Update]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SOCIETY_Update]
@sid as int,
@SNAME AS NVARCHAR(256),
@ADD AS NVARCHAR(256),
@CITY AS NVARCHAR(256),
@PIN AS NVARCHAR(256),
@NOOFHOUSE AS INT
AS
BEGIN

UPDATE  SocietyMst SET SNAME=@SNAME,ADDRESS=@ADD,CITY=@CITY,PINCODE=@PIN,NOHOUSE=@NOOFHOUSE,ENTRYDATE=GETDATE() WHERE SID=@SID

END
GO
/****** Object:  StoredProcedure [dbo].[SOCIETY_SELECT_TOP10]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SOCIETY_SELECT_TOP10]


AS
BEGIN

SELECT  top 7 *  FROM SocietyMst order by NEWID()

END
GO
/****** Object:  StoredProcedure [dbo].[SOCIETY_SELECT_by_SID]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SOCIETY_SELECT_by_SID]

@SID AS INT
AS
BEGIN

SELECT *  FROM SocietyMst WHERE SID=@SID

END
GO
/****** Object:  StoredProcedure [dbo].[SOCIETY_SELECT]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SOCIETY_SELECT]


AS
BEGIN

SELECT *  FROM SocietyMst

END
GO
/****** Object:  StoredProcedure [dbo].[SOCIETY_INNSERT]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SOCIETY_INNSERT]

@SNAME AS NVARCHAR(256),
@ADD AS NVARCHAR(256),
@CITY AS NVARCHAR(256),
@PIN AS NVARCHAR(256),
@NOOFHOUSE AS INT,
@img as nvarchar(700)
AS
BEGIN

INSERT INTO SocietyMst VALUES(@SNAME,@ADD,@CITY,@PIN,@NOOFHOUSE,@img,GETDATE())

END
GO
/****** Object:  StoredProcedure [dbo].[SOCIETY_DELETE]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SOCIETY_DELETE]
@sid as inT
AS
BEGIN

DELETE FROM  SocietyMst WHERE SID=@SID

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_UPDATE_SELL]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SELL_UPDATE_SELL]
@SID AS INT,
@SELL AS FLOAT
AS
BEGIN

UPDATE SELLMST SET SELL=@SELL  WHERE SID=@SID

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_SELECT_FOR_USER_SEARCH]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SELL_SELECT_FOR_USER_SEARCH]
@sname as nvarchar(256)
AS
BEGIN

--SELECT u.FName+ ' ' + u.LName as username, u.Mobile as mobile, s.HID as hid,s.Sname as sname, s.rent as sell, s.UID as uid FROM  RentMst as s,  UserMst as u where s.UID=u.UID 
 
 SELECT h.*,R.* FROM HouseMst AS h, SellMst AS r WHERE H.HID=R.HID and r.Sname=@sname

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_SELECT_FOR_USER]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SELL_SELECT_FOR_USER]
AS
BEGIN

--SELECT u.FName+ ' ' + u.LName as username, u.Mobile as mobile, s.HID as hid,s.Sname as sname, s.rent as sell, s.UID as uid FROM  RentMst as s,  UserMst as u where s.UID=u.UID 
 
 SELECT h.*,R.* FROM HouseMst AS h, SellMst AS r WHERE H.HID=R.HID

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_SELECT_FOR_ADMIN]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SELL_SELECT_FOR_ADMIN]
AS
BEGIN

SELECT u.FName+ ' ' + u.LName as username, u.Mobile as mobile, s.HID as hid,s.Sname as sname, s.Sell as sell, s.UID as uid FROM  SELLMST as s,  UserMst as u where s.UID=u.UID 
 

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_SELECT_BY_UID]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SELL_SELECT_BY_UID]
@UID as inT
AS
BEGIN

SELECT * FROM  SELLMst WHERE uID=@UID 

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_SELECT_BY_SID]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SELL_SELECT_BY_SID]
@SID as inT
AS
BEGIN

SELECT * FROM  SELLMst WHERE SID=@SID 

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_SELECT]    Script Date: 08/25/2019 11:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SELL_SELECT]
AS
BEGIN

SELECT * FROM  SELLMST 

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_INSERT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SELL_INSERT]
@HID AS INT,
@Sname as nvarchar(256),
@Uid as int,
 @SELL AS FLOAT
AS
BEGIN

INSERT INTO SELLMST VALUES(@HID,@Sname,@Uid,@SELL,GETDATE()) 

END
GO
/****** Object:  StoredProcedure [dbo].[SELL_DELETE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SELL_DELETE]
@SID as inT
AS
BEGIN

DELETE FROM  SELLMST WHERE SID=@SID 

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_UPDATE_RENT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RENT_UPDATE_RENT]
@RID AS INT,
@RENT AS FLOAT
AS
BEGIN

UPDATE RentMst SET Rent=@RENT  WHERE RID=@RID

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_SELECT_FOR_USER_SEARCH]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[RENT_SELECT_FOR_USER_SEARCH]
@sname as nvarchar(256)
AS
BEGIN

--SELECT u.FName+ ' ' + u.LName as username, u.Mobile as mobile, s.HID as hid,s.Sname as sname, s.rent as sell, s.UID as uid FROM  RentMst as s,  UserMst as u where s.UID=u.UID 
 
 SELECT h.*,R.* FROM HouseMst AS h, RentMst AS r WHERE H.HID=R.HID and r.Sname=@sname

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_SELECT_FOR_USER]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[RENT_SELECT_FOR_USER]
AS
BEGIN

--SELECT u.FName+ ' ' + u.LName as username, u.Mobile as mobile, s.HID as hid,s.Sname as sname, s.rent as sell, s.UID as uid FROM  RentMst as s,  UserMst as u where s.UID=u.UID 
 
 SELECT h.*,R.* FROM HouseMst AS h, RentMst AS r WHERE H.HID=R.HID

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_SELECT_FOR_ADMIN]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[RENT_SELECT_FOR_ADMIN]
AS
BEGIN

SELECT u.FName+ ' ' + u.LName as username, u.Mobile as mobile, s.HID as hid,s.Sname as sname, s.rent as sell, s.UID as uid FROM  RentMst as s,  UserMst as u where s.UID=u.UID 
 

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_SELECT_BY_UID]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RENT_SELECT_BY_UID]
@UID as inT
AS
BEGIN

SELECT * FROM  RentMst WHERE UID=@UID 

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_SELECT_BY_RID]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RENT_SELECT_BY_RID]
@RID as inT
AS
BEGIN

SELECT * FROM  RentMst WHERE RID=@RID 

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_SELECT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RENT_SELECT]
AS
BEGIN

SELECT * FROM  RentMst 

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_INSERT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RENT_INSERT]
@HID AS INT,
@Sname as nvarchar(256),
@Uid AS int,
 
@RENT AS FLOAT
AS
BEGIN

INSERT INTO RentMst VALUES(@HID,@Sname,@Uid ,@RENT,GETDATE()) 

END
GO
/****** Object:  StoredProcedure [dbo].[RENT_DELETE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RENT_DELETE]
@RID as inT
AS
BEGIN

DELETE FROM  RentMst WHERE RID=@RID 

END
GO
/****** Object:  StoredProcedure [dbo].[MESSAGE_SELECT_BY_TNAME]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MESSAGE_SELECT_BY_TNAME]
@TNAME  AS NVARCHAR(256)
AS
BEGIN

SELECT * FROM MessageMSt WHERE TONAME=@TNAME

END
GO
/****** Object:  StoredProcedure [dbo].[MESSAGE_SELECT_BY_MID]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MESSAGE_SELECT_BY_MID]
@MID AS INT
AS
BEGIN

SELECT * FROM MessageMSt WHERE MID=@MID
END
GO
/****** Object:  StoredProcedure [dbo].[MESSAGE_SELECT_BY_FNAME]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MESSAGE_SELECT_BY_FNAME]
@FNAME  AS NVARCHAR(256)
AS
BEGIN

SELECT * FROM MessageMSt WHERE FromName=@FNAME
END
GO
/****** Object:  StoredProcedure [dbo].[MESSAGE_SELECT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MESSAGE_SELECT]

AS
BEGIN

SELECT * FROM MessageMSt
END
GO
/****** Object:  StoredProcedure [dbo].[MESSAGE_INSERT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MESSAGE_INSERT]
@FNAME  AS NVARCHAR(256),
@TNAME AS NVARCHAR(256),
@SUB AS NVARCHAR(256),
@MSG AS NVARCHAR(256)
AS
BEGIN

INSERT INTO MessageMSt VALUES(@FNAME,@TNAME,@SUB,@MSG,GETDATE())
END
GO
/****** Object:  StoredProcedure [dbo].[MESSAGE_DELETE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MESSAGE_DELETE]
@MID AS INT
AS
BEGIN

DELETE FROM MessageMSt WHERE MID=@MID
END
GO
/****** Object:  StoredProcedure [dbo].[COMPLAIN_SOLVED]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[COMPLAIN_SOLVED]

@cid as int,
@reply as nvarchar(500)
AS
BEGIN

update ComplainMst set Reply=@reply,Status='Solved', EntryDate=GETDATE() where CID=@cid
END
GO
/****** Object:  StoredProcedure [dbo].[COMPLAIN_SELECT_for_ADMIN]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COMPLAIN_SELECT_for_ADMIN]

@status as nvarchar(200)
AS
BEGIN

SELECT u.UID as uid, u.FName +' '+u.LName as username,u.SocietyName as societyname,c.* FROM ComplainMst as c, UserMst as u where c.UID=u.UID and c.Status=@status


END
GO
/****** Object:  StoredProcedure [dbo].[COMPLAIN_select_by_uid]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COMPLAIN_select_by_uid]

@UID AS INT

AS
BEGIN

select * from   ComplainMst  WHERE UID=@UID
END
GO
/****** Object:  StoredProcedure [dbo].[COMPLAIN_SELECT_BY_CID]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COMPLAIN_SELECT_BY_CID]

@CID AS INT
AS
BEGIN

SELECT * FROM ComplainMst WHERE  CID=@CID
END
GO
/****** Object:  StoredProcedure [dbo].[COMPLAIN_SELECT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COMPLAIN_SELECT]


AS
BEGIN

SELECT * FROM ComplainMst
END
GO
/****** Object:  StoredProcedure [dbo].[COMPLAIN_INSERT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COMPLAIN_INSERT]

@UID AS INT,
@sub AS NVARCHAR(256),
@comlain AS NVARCHAR(256),
@reply AS NVARCHAR(256)
AS
BEGIN

insert into ComplainMst values(@UID,@sub,@comlain,@reply,'Pending',GETDATE())
END
GO
/****** Object:  StoredProcedure [dbo].[COMPLAIN_DELETE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COMPLAIN_DELETE]

@CID AS INT
AS
BEGIN

DELETE FROM ComplainMst WHERE  CID=@CID
END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_UPDATE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOUSE_UPDATE]
@HID AS INT,
@BLOCKNO AS INT,
@TYPE AS NVARCHAR(256),
@DETAIL AS NVARCHAR(256),
@SID AS INT

AS
BEGIN

UPDATE HouseMst SET BLOCKNO= @BLOCKNO,TYPE=@TYPE,DETAIL=@DETAIL,SID=@SID,EntyDate=GETDATE() WHERE HID=@HID

END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_SELECT_FOR_VIEWMORE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOUSE_SELECT_FOR_VIEWMORE]
@uid as int
AS
BEGIN

--SELECT u.FName+ ' ' + u.LName as username, u.Mobile as mobile, s.HID as hid,s.Sname as sname, s.rent as sell, s.UID as uid FROM  RentMst as s,  UserMst as u where s.UID=u.UID 
 
-- SELECT * FROM HouseMst
 --select * from UserMst
 --select * from SocietyMst 
 
select u.FName +' ' +u.LName as username,u.Email as email, u.Mobile as mobile,u.Image as img, h.*,s.Address as address, s.City as city, s.Pincode as pincode  from UserMst as u, HouseMst as h, SocietyMst as s where u.HouseID=h.HID and h.SName=s.SName and u.UID=@uid
END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_SELECT_BY_SNAME_FOR_NEW_ADD_HOUSE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[HOUSE_SELECT_BY_SNAME_FOR_NEW_ADD_HOUSE]
@sname as nvarchar(256)

AS
BEGIN

SELECT * FROM HouseMst WHERE SName=@sname and blockno not in(select HouseID from UserMst where  SocietyName=@sname)

END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_SELECT_BY_SNAME_and_BLOCKNO]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[HOUSE_SELECT_BY_SNAME_and_BLOCKNO]
@sname as nvarchar(256),
@bno as int
AS
BEGIN

SELECT * FROM HouseMst WHERE SName=@sname  and BlockNo=@bno 

END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_SELECT_BY_SNAME]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOUSE_SELECT_BY_SNAME]
@sname as nvarchar(256)

AS
BEGIN

SELECT * FROM HouseMst WHERE SName=@sname  

END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_SELECT_BY_HID]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOUSE_SELECT_BY_HID]
@HID AS INT

AS
BEGIN

SELECT * FROM HouseMst WHERE HID=@HID 

END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_SELECT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOUSE_SELECT]


AS
BEGIN

SELECT * FROM HouseMst

END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_INSERT]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOUSE_INSERT]
@BLOCKNO AS INT,
@TYPE AS NVARCHAR(256),
@DETAIL AS NVARCHAR(256),
@Sname AS NVARCHAR(256),
@img as nvarchar(500)

AS
BEGIN

INSERT INTO HouseMst VALUES(@BLOCKNO,@TYPE,@DETAIL,@Sname,@img,GETDATE())

END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_DELETE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOUSE_DELETE]
@HID AS INT

AS
BEGIN

DELETE FROM HouseMst WHERE HID=@HID 

END
GO
/****** Object:  StoredProcedure [dbo].[HOUSE_CHANGE_IMAGE]    Script Date: 08/25/2019 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOUSE_CHANGE_IMAGE]

@HID AS INT,
@IMG AS NVARCHAR(500)
AS
BEGIN

UPDATE  HouseMst SET Image=@IMG WHERE HID=@HID

END
GO

Subscribe us

If you liked this c# post, then please subscribe to our YouTube Channel for more Project video tutorials.


Download and Run Project Demo :

Learn how to download and run c# .net project with source code. Project Demo

We hope that this asp.net project database tutorial helped you to understand and run Society Management System Project.

Leave a Reply

Your email address will not be published. Required fields are marked *