数据库修改

一、循环

declare @i int 
set @i = 1
while @i<=56
begin
insert into dbo.MachineStatus (MachineId) values (@i)
set @i = @i + 1
end
USE [HemoSystem]
GO

二、多行子查询

update [MachineStatus] set IsFault= 1 WHERE MachineId IN (SELECT Id FROM [View_Machine] WHERE [MachineBreakId] is not null)

三、添加字段

1. 数据库
 * alter table [HemoSystem].[dbo].[EquipmentFaultRepair] add [EquipmentId] [int] NULL
 * ALTER TABLE [HemoSystem].[dbo].[Machine] ADD MaintenanceStatus [int] default 0
 * ALTER TABLE [HemoSystem].[dbo].[EquipmentFaultRepair] ADD IsSuccess bit default 1
 * ALTER TABLE [HemoSystem].[dbo].[EquipmentFaultReport] ADD ReportHandlerId int,HandleDateTime datetime
2. VS
 * EquipmentFaultRepair类中添加EquipmentId
 * Machine类中添加MaintenanceStatus 
 * EquipmentFaultRepair类中添加IsSuccess 
 * EquipmentFaultReport类中添加ReportHandlerId int,HandleDateTime datetime

四、存储过程

（一）修改
添加插入字段：EquipmentId，更新MachineStatus中的IsRepair、IsSuccess
 ALTER
 PROC [dbo].[p_EquipmentFaultRepair_Insert]
@EquipmentId int,
@RepairPeople NVARCHAR(50),
@RepairDateTime DATETIME,
@MaintenanceItem NVARCHAR(50),
@IsSuccess bit
AS
        INSERT INTO EquipmentFaultRepair(EquipmentId,RepairPeople,RepairDateTime,MaintenanceItem,IsSuccess) VALUES(@EquipmentId,@RepairPeople,@RepairDateTime,@MaintenanceItem,@IsSuccess)
    UPDATE MachineStatus SET IsRepair = 1, IsSuccess = @IsSuccess WHERE MachineId = @EquipmentId 
    SELECT @@IDENTITY
（二）创建
CREATE
 PROC [dbo].[p_MachineStatus_Machine_Update]
@MachineId int,
@IsFault bit,
@IsReport bit,
@IsRepairing bit,
@IsRepair bit,
@IsSuccess bit
AS
begin

declare @result int = @IsFault*1000+@IsReport*100+@IsRepair*10+@IsSuccess;

    --1.更新设备状态表
    UPDATE MachineStatus SET IsFault = @IsFault, IsReport = @IsReport,IsRepairing = @IsRepairing, IsRepair = @IsRepair, IsSuccess = @IsSuccess WHERE MachineId = @MachineId 
    --2.更新设备表
    UPDATE Machine SET Machine.MaintenanceStatus = @result WHERE Id = @MachineId
end

数据库删除

DELETE [HemoSystem].[dbo].[EnumerationItem] where Id= 10
动静脉内瘘记录误删