Store procedure for Storing Data in Database.

ALTER PROCEDURE [dbo].[GetAllNodes]
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder
Select * from dbo.MailSubFolder
END

ALTER PROCEDURE [dbo].[GetAllRouteFolder]
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder

END

ALTER PROCEDURE [dbo].[GetAllSubFolders]
@RouteFolderId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailSubFolder
END

ALTER PROCEDURE [dbo].[GetMailDetailByRouteFolderID]
@RouteFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.MailMaster where RouteFolderId=@RouteFolderId
end
else
begin
Select * from dbo.MailMaster where RouteFolderId=@RouteFolderId and (MailFrom like '%'+ @Filter + '%' or MailTo like '%'+ @Filter + '%' or MailCc like '%'+ @Filter + '%' or MailSubject like '%' + @Filter + '%' or MailBody like '%'+ @Filter + '%' or MailBodyPlain like '%'+ @Filter + '%')
end
END

ALTER PROCEDURE [dbo].[GetMailDetailBysubFolderID]
@SubFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.MailMaster where SubFolderId=@SubFolderId
end
else
begin
Select * from dbo.MailMaster where SubFolderId=@SubFolderId and (MailFrom like '%'+ @Filter + '%' or MailTo like '%'+ @Filter + '%' or MailCc like '%'+ @Filter + '%' or MailSubject like '%' + @Filter + '%' or MailBody like '%'+ @Filter + '%' or MailBodyPlain like '%'+ @Filter + '%' )
end
END


ALTER PROCEDURE [dbo].[GetTopFolderbySubFolderId]
@SubFolderId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select MailSubFolder.*,MailRoutefolder.Name as RouteFolderName from
dbo.MailSubFolder left join dbo.MailRoutefolder on MailSubFolder.RouteFolderID=MailRoutefolder.ID where MailSubFolder.ID = @SubFolderId


END



ALTER PROCEDURE [dbo].[IsRouteFolderExists]
@RouteFolderName varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder where Name=@RouteFolderName

END


ALTER PROCEDURE [dbo].[IsSubFolderExists]
@SubFolderName varchar(200),
@RouteFolderID int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailSubFolder where [Name]=@SubFolderName and RouteFolderID=@RouteFolderID

END


ALTER PROCEDURE [dbo].[SaveMail]
@RouteFolderID int,
@SubFolderId int,
@MailFrom varchar(50),
@MailTo varchar(MAX),
@MailCc varchar(MAX),
@MailBcc varchar(MAX),
@MailSubject varchar(MAX),
@MailBody varchar(MAX),
@MailDate datetime,
@MailSize int,
@HasAttechment bit,
@MailBodyPlain nvarchar(MAX)

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[MailMaster]
([RouteFolderId]
,[SubFolderId]
,[MailFrom]
,[MailTo]
,[MailCc]
,[MailBcc]
,[MailSubject]
,[MailBody]
,[MailDate]
,[MailSize]
,[HasAttechment]
,[MailBodyPlain])
OUTPUT INSERTED.ID
VALUES
(@RouteFolderID,@SubFolderId,@MailFrom,@MailTo,@MailCc,@MailBcc,@MailSubject,@MailBody,@MailDate,@MailSize,@HasAttechment,@MailBodyPlain)

END

ALTER PROCEDURE [dbo].[SaveMailRouteFolder]
@Name varchar(250)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT into [dbo].[MailRoutefolder]
([Name])OUTPUT INSERTED.ID
VALUES
(@Name)

END


ALTER PROCEDURE [dbo].[SaveMailSubFolder]
@Name varchar(50),
@RouteFolderID int,
@Lavel int,
@ParentId int,
@HasChildNode bit
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[MailSubFolder]
([Name]
,[RouteFolderID]
,[Lavel]
,[ParentId]
,[HasChildNode])
OUTPUT INSERTED.ID
VALUES
(@Name,@RouteFolderID,@Lavel,@ParentId,@HasChildNode)

END