1.UserInfo
CREATE TABLE [dbo].[userInfo](
[userId] [bigint] IDENTITY(1,1) NOT NULL,
[email] [nvarchar](30) NOT NULL,
[password] [nvarchar](20) NOT NULL,
[firstName] [nvarchar](50) NULL,
[lastName] [nvarchar](50) NULL,
[activationKey] [nvarchar](800) NULL,
[active] [bit] NULL,
[createdOn] [datetime] NULL,
[modifiedOn] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
2.FileInfo
CREATE TABLE [dbo].[fileInfo](
[fileId] [bigint] IDENTITY(1,1) NOT NULL,
[userId] [bigint] NULL,
[fileName] [nvarchar](50) NOT NULL,
[fileType] [nvarchar](50) NOT NULL,
[fileSize] [nvarchar](100) NOT NULL,
[filePath] [nvarchar](200) NOT NULL,
[createdOn] [datetime] NULL,
[statusId] [bigint] NULL,
[lastModifiedOn] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[fileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[fileInfo] WITH CHECK ADD FOREIGN KEY([statusId])
REFERENCES [dbo].[status] ([statusId])
GO
ALTER TABLE [dbo].[fileInfo] WITH CHECK ADD FOREIGN KEY([statusId])
REFERENCES [dbo].[status] ([statusId])
GO
ALTER TABLE [dbo].[fileInfo] WITH CHECK ADD FOREIGN KEY([statusId])
REFERENCES [dbo].[status] ([statusId])
GO
ALTER TABLE [dbo].[fileInfo] WITH CHECK ADD FOREIGN KEY([userId])
REFERENCES [dbo].[userInfo] ([userId])
GO
ALTER TABLE [dbo].[fileInfo] WITH CHECK ADD FOREIGN KEY([userId])
REFERENCES [dbo].[userInfo] ([userId])
GO
ALTER TABLE [dbo].[fileInfo] WITH CHECK ADD FOREIGN KEY([userId])
REFERENCES [dbo].[userInfo] ([userId])
GO
I need if userid 1 is upload file to save value in db in fileinfo by userid and all deatils to store in db
fileId userId fileName fileType fileSize filePath createdOn statusId lastModifiedOn
8 2 Asp notes.docx .docx 13539 2013-04-04-05-52\ 2013-04-04 17:52:20.173 6 NULL
9 2 Asp.net.docx .docx 14041 2013-04-04-05-52\ 2013-04-04 17:52:20.647 6 NULL
I Implement in stored proc it's stored Proc.It's not working.
create proc Insert_fileinfo @userId bigint,@FileName nvarchar(30), @FileType nvarchar(30),@fileSize nvarchar(30),@filePath nvarchar(30),@statusId int
as
insert into fileInfo(userId,fileName,fileType,fileSize,filePath,statusId,createdOn)
values(
(select userID from fileInfo where
fileName=@FileName and fileType=@FileType and fileSize=@fileSize and filePath=@filePath and statusId=3 and createdOn=CURRENT_TIMESTAMP and userId=
(select userId from userInfo where userId=@userId)))
Thanks Friends...