电话管理系统,可以对电话进行维护（也可导出EXCEL表）以及调用SQL的基础类库

CREATE TABLE [dbo].[tblDepartment]( [DepartmentID] [int] PRIMARY KEY NOT NULL, [Description] [nvarchar](200) NULL ) GO CREATE TABLE [dbo].[tblRole]( [RoleID] [int] PRIMARY KEY NOT NULL, [Description] [nvarchar](200) NULL, ) GO CREATE TABLE [dbo].[tblAccount]( [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [UserName] [nvarchar](50) NOT NULL, [RealName] [nvarchar](50) NULL, [PassWord] [nvarchar](50) NOT NULL, [Email] [nvarchar](50) NULL, [OfficeTelephoneNo] [nvarchar](50) NULL, [MobileTelephoneNo] [nvarchar](50) NULL, [ShortPhoneNo] [nvarchar](50) NULL, [DepartmentID] [int] NULL, [RoleID] [int] NULL, ) GO -- ============================================= -- Author: -- Create date: 2008-06-16 -- Description: get account information -- ============================================= CREATE PROCEDURE [dbo].[GetAccountInfo] AS BEGIN SELECT a.ID, a.UserName, a.RealName, a.PassWord, a.Email, a.OfficeTelephoneNo, a.MobileTelephoneNo, a.ShortPhoneNo, a.DepartmentID, (SELECT Description FROM dbo.tblDepartment WHERE DepartmentID=a.DepartmentID)AS Department, a.RoleID, (SELECT Description FROM dbo.tblRole WHERE RoleID=a.RoleID)AS Role FROM dbo.tblAccount a END -- ============================================= -- Author: -- Create date: 2008-06-16 -- Description: export information export to excel -- ============================================= CREATE PROCEDURE [dbo].[ExportAccountInfo] ( @UserName NVARCHAR(50), @Email NVARCHAR(50), @OfficeTelephoneNo NVARCHAR(50) , @MobileTelephoneNo NVARCHAR(50), @ShortPhoneNo NVARCHAR(50), @DepartmentID INT ) AS BEGIN SET @UserName=''%''+@UserName+''%'' SET @Email=''%''+@Email+''%'' SET @OfficeTelephoneNo=''%''+@OfficeTelephoneNo+''%'' SET @MobileTelephoneNo=''%''+@MobileTelephoneNo+''%'' SET @ShortPhoneNo=''%''+@ShortPhoneNo+''%'' IF(@DepartmentID=-1) BEGIN SELECT a.ID, a.UserName, a.RealName, a.Email, a.OfficeTelephoneNo, a.MobileTelephoneNo, a.ShortPhoneNo, (SELECT Description FROM dbo.tblDepartment WHERE DepartmentID=a.DepartmentID)AS Department, (SELECT Description FROM dbo.tblRole WHERE RoleID=a.RoleID)AS Role FROM dbo.tblAccount a WHERE a.UserName LIKE @UserName AND a.Email LIKE @Email AND a.OfficeTelephoneNo LIKE @OfficeTelephoneNo AND a.MobileTelephoneNo LIKE @MobileTelephoneNo AND a.ShortPhoneNo LIKE @ShortPhoneNo END ELSE BEGIN SELECT a.ID, a.UserName, a.RealName, a.Email, a.OfficeTelephoneNo, a.MobileTelephoneNo, a.ShortPhoneNo, (SELECT Description FROM dbo.tblDepartment WHERE DepartmentID=a.DepartmentID)AS Department, (SELECT Description FROM dbo.tblRole WHERE RoleID=a.RoleID)AS Role FROM dbo.tblAccount a WHERE a.UserName LIKE @UserName AND a.Email LIKE @Email AND a.OfficeTelephoneNo LIKE @OfficeTelephoneNo AND a.MobileTelephoneNo LIKE @MobileTelephoneNo AND a.ShortPhoneNo LIKE @ShortPhoneNo AND a.DepartmentID=@DepartmentID END END -- ============================================= -- Author: -- Create date: 2008-06-16 -- Description: search account information by uername,emial.... -- ============================================= CREATE PROCEDURE [dbo].[SearchAccountInfo] ( @UserName NVARCHAR(50), @Email NVARCHAR(50), @OfficeTelephoneNo NVARCHAR(50) , @MobileTelephoneNo NVARCHAR(50), @ShortPhoneNo NVARCHAR(50), @DepartmentID INT ) AS BEGIN SET @UserName=''%''+@UserName+''%'' SET @Email=''%''+@Email+''%'' SET @OfficeTelephoneNo=''%''+@OfficeTelephoneNo+''%'' SET @MobileTelephoneNo=''%''+@MobileTelephoneNo+''%'' SET @ShortPhoneNo=''%''+@ShortPhoneNo+''%'' IF(@DepartmentID=-1) BEGIN SELECT a.ID, a.UserName, a.RealName, a.PassWord, a.Email, a.OfficeTeleph�