tazarvmmr
سه شنبه 15 آذر 1390, 14:36 عصر
من سعی میکنم یه جواب درست بدم به این سوالت
فرض کنیم این جدول رو داریم:
CREATE TABLE [dbo].[UserMessages](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SenderUserCode] [int] NULL,
[DestUserCode] [int] NULL,
[SenderSectionCode] [int] NULL,
[DestSectionCode] [int] NULL,
[MessageBody] [ntext] NULL,
[SendDate] [nvarchar](10) NULL,
[SendTime] [nvarchar](5) NULL,
[ReadDate] [nvarchar](10) NULL,
[ReadTime] [nvarchar](5) NULL,
[Priority] [int] NULL,
[LogoId] [int] NULL,
[ReadAlertNeeded] [bit] NULL CONSTRAINT [DF_UserMessages_ReadAlertNeeded] DEFAULT ((0)),
[ReplyToMessageId] [int] NULL,
[FileAttached] [bit] NULL CONSTRAINT [DF_UserMessages_FileAttached] DEFAULT ((0)),
[Deleted] [bit] NULL CONSTRAINT [DF_UserMessages_Deleted] DEFAULT ((0)),
[DelDate] [nvarchar](10) NULL,
[DelTime] [nvarchar](5) NULL,
[IsNew] [bit] NULL CONSTRAINT [DF_UserMessages_IsNew] DEFAULT ((1)),
CONSTRAINT [PK_UserMessages] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
حالا برای این جدول چندتا sp مینویسم که دقیقا متوجه بشی:
این پراسیجر یک ردیف جدید به این جدول اضافه میکنه و شماره ردیف اونو برمیگردونه(منظورم مقدار فیلد ID هست که برمیگردونه)
Create Procedure [dbo].[sp_InsertUserMessage]
@SenderUserCode int,
@DestUserCode int,
@SenderSectionCode int,
@DestSectionCode int,
@MessageBody ntext,
@SendDate nvarchar(50),
@SendTime nvarchar(5),
@Priority int,
@LogoId int,
@ReadAlertNeeded bit,
@ReplyToMessageId int,
@FileAttached bit
As
insert into UserMessages
(
SenderUserCode,
DestUserCode,
SenderSectionCode,
DestSectionCode,
MessageBody,
SendDate,
SendTime,
Priority,
LogoId,
ReadAlertNeeded,
ReplyToMessageId,
FileAttached
)
values
( @SenderUserCode,
@DestUserCode,
@SenderSectionCode,
@DestSectionCode,
@MessageBody,
@SendDate,
@SendTime,
@Priority,
@LogoId,
@ReadAlertNeeded,
@ReplyToMessageId,
@FileAttached
)
return @@Identity
این یکی بر اساس مقداری که به Dirction داده شده یا کلیه ردیفهایی که مقدار DestUserCode مشخصی دارند رو بر میگردونه یا کلیه ردیفهایی که مقدار SourceUserCode مشخصی دارند رو برمیگردونه، این مقدار هم در پارامتر UserCode به پراسیجر داده شده و در انتها تعداد ردیفهایی که برگردونده میشه رو هم برمیگردونه:
Create Procedure [dbo].[sp_GetThisUserMessages]
@UserCode int = -1,
@Direction int = 1
As
SELECT
UserMessages.Id AS MessageId,
UserMessages.MessageBody,
UserMessages.SendDate,
UserMessages.SendTime,
ISNULL(UserMessages.ReadDate, '') AS ReadDate,
ISNULL(UserMessages.ReadTime, '') AS ReadTime,
UserMessages.Priority,
UserMessages.FileAttached,
UserMessages.ReadAlertNeeded,
UserMessages.DestSectionCode,
UserMessages.DestUserCode as DestUserCode,
UserMessages.SenderSectionCode,
UserMessages.SenderUserCode as SenderUserCode,
UserMessages.Deleted,
ISNULL(UserMessages.DelDate, '') AS DelDate,
ISNULL(UserMessages.DelTime, '') AS DelTime,
Sexes_1.ShortCaption + N' ' + Users.UserName AS SenderUserFullName,
Sexes.ShortCaption + N' ' + Users_1.UserName AS DestUserFullName,
UserMessages.IsNew,
UserMessages.ReplyToMessageId
FROM
Sexes AS Sexes_1 RIGHT OUTER JOIN
Users LEFT OUTER JOIN
Sections AS Sections_1 ON Users.UserGroupCode = Sections_1.Id RIGHT OUTER JOIN
UserMessages LEFT OUTER JOIN
Users AS Users_1 ON UserMessages.DestUserCode = Users_1.Id LEFT OUTER JOIN
Sections ON Users_1.UserGroupCode = Sections.Id LEFT OUTER JOIN
Sexes ON Users_1.UserSexId = Sexes.SexId ON Users.Id = UserMessages.SenderUserCode ON Sexes_1.SexId = Users.UserSexId
WHERE
(DestUserCode = Case When @Direction = 1 then @UserCode
When @Direction = 2 then DestUserCode End) and
(SenderUserCode = Case When @Direction = 1 then SenderUserCode
When @Direction = 2 then @UserCode End) and
(UserMessages.Deleted = Case When @Direction = 1 then 0
When @Direction = 2 then UserMessages.Deleted End)
Order by SendDate DESC, SendTime DESC
Return @@Rowcount
این پراسیجر شماره یک ردیف رو میگیره و ساعت و تاریخ خوانده شدن آنرا درج میکنه و اون ردیف رو از حالت جدید خارج میکنه:
Create Procedure [dbo].[sp_MakeThisUserMessagesIsRead]
@MessageId int,
@ReadDate nvarchar(10),
@ReadTime nvarchar(5)
As
update UserMessages
Set ReadDate = @ReadDate, ReadTime = @ReadTime, IsNew = 0
Where Id = @MessageId
اگر بخوای این جئاول و پراسیجرها رو توی بانک خودت درست کنی و ازشون استفاده کنی، به جدولهای زیر هم نیاز داری:
CREATE TABLE [dbo].[Sections](
[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[ViewPublicProjects] [bit] NOT NULL CONSTRAINT [DF_Sections_ViewPublicProjects] DEFAULT ((0)),
[LET] [smalldatetime] NULL CONSTRAINT [DF_Sections_LET] DEFAULT (CONVERT([smalldatetime],getdate(),(0))),
[LES] [tinyint] NULL CONSTRAINT [DF_Sections_LES] DEFAULT ((1)),
CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Sexes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SexCaption] [nvarchar](15) NULL,
[SexId] [int] NULL,
[ShortCaption] [nvarchar](5) NULL,
CONSTRAINT [PK_Sexes] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](100) NOT NULL,
[UserRights] [nvarchar](300) NULL CONSTRAINT [DF_Users_UserRights] DEFAULT ((1)),
[UserIsActive] [bit] NOT NULL CONSTRAINT [DF_Users_UserIsActive] DEFAULT ((0)),
[UserGroupCode] [int] NOT NULL,
[UserCurrentPosition] [nvarchar](400) NULL CONSTRAINT [DF_Table_1_CurrentPosition] DEFAULT (N'هنوز وارد نشده'),
[UserGroupAdmin] [bit] NULL,
[UserLegalDiscount] [int] NOT NULL CONSTRAINT [DF_Table_1_LegalDiscount] DEFAULT ((0)),
[UserTel] [nvarchar](50) NULL CONSTRAINT [DF_Table_1_STel] DEFAULT (N'UnDEf'),
[UserPositionTime] [nvarchar](50) NULL,
[UserLetterLevel] [int] NULL CONSTRAINT [DF_Table_1_LetterLevel] DEFAULT ((-1)),
[UserAddr] [ntext] NULL CONSTRAINT [DF_Table_1_Addr] DEFAULT ('???????'),
[UserSexId] [int] NULL CONSTRAINT [DF_Table_1_Sex] DEFAULT ((0)),
[UserBirthDate] [nvarchar](10) NULL,
[UserIDCardNo] [nvarchar](50) NULL CONSTRAINT [DF_Table_1_IDNo] DEFAULT (N'نامعلوم'),
[UserEducationID] [int] NULL CONSTRAINT [DF_Table_1_EducationID] DEFAULT ((-1)),
[UserContactTel] [nvarchar](50) NULL CONSTRAINT [DF_Users_UserContactTel] DEFAULT (N'نامعلوم'),
[UserMobile] [nvarchar](50) NULL,
[UserParafLevel] [int] NOT NULL CONSTRAINT [DF_Table_1_ParafLevel] DEFAULT ((-1)),
[UserPassword] [nvarchar](50) NOT NULL,
[UserIsAdmin] [bit] NOT NULL CONSTRAINT [DF_Users_UserIsAdmin] DEFAULT ((0)),
[UserEmailAddr] [nvarchar](200) NOT NULL CONSTRAINT [DF_Users_UserEmailAddr] DEFAULT (N'UnKnown'),
[UserInvoiceSettingId] [int] NULL,
[LET] [smalldatetime] NULL CONSTRAINT [DF_Users_LET] DEFAULT (CONVERT([smalldatetime],getdate(),(0))),
[LES] [tinyint] NULL CONSTRAINT [DF_Users_LES] DEFAULT ((1)),
[LangId] [int] NULL CONSTRAINT [DF_Users_LangId] DEFAULT ((1)),
[UserIP] [varchar](20) NULL,
[Remember] [bit] NULL,
[T0] [nvarchar](200) NULL,
[T1] [nvarchar](200) NULL,
[T2] [nvarchar](200) NULL,
[T3] [nvarchar](200) NULL,
[T4] [nvarchar](200) NULL,
[T5] [nvarchar](200) NULL,
[T6] [nvarchar](200) NULL,
[T7] [nvarchar](200) NULL,
[T8] [nvarchar](200) NULL,
[T9] [nvarchar](200) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
امیدوارم کمکی کرده باشه
vBulletin® v4.2.5, Copyright ©2000-1403, Jelsoft Enterprises Ltd.