csharp: Procedure with DAO(Data Access Object) and DAL(Data Access Layer)

sql script code:

CREATE TABLE DuCardType(	CardTypeId INT IDENTITY(1,1) PRIMARY KEY,	CardTypeName NVARCHAR(100) NOT NULL,     --卡类名称	CardTypeColor NVARCHAR(50) NOT NULL		   --卡颜色(或样本))GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Insert_DuCardType')DROP PROCEDURE proc_Insert_DuCardTypeGOCREATE PROCEDURE proc_Insert_DuCardType(	@CardTypeName NVarChar(200),	@CardTypeColor NVarChar(100)) AS IF NOT EXISTS (SELECT * FROM DuCardType WHERE [CardTypeName]=@CardTypeName)BEGININSERT INTO DuCardType(	[CardTypeName] ,	[CardTypeColor])	VALUES(	@CardTypeName ,	@CardTypeColor)ENDGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Insert_DuCardTypeOutput')DROP PROCEDURE proc_Insert_DuCardTypeOutputGOCREATE PROCEDURE proc_Insert_DuCardTypeOutput(	@CardTypeName NVarChar(200),	@CardTypeColor NVarChar(100), 	@CardTypeId int output)ASIF NOT EXISTS (SELECT * FROM DuCardType WHERE [CardTypeName]=@CardTypeName)BEGININSERT INTO DuCardType(	[CardTypeName] ,	[CardTypeColor])	VALUES(	@CardTypeName ,	@CardTypeColor)select @CardTypeId=@@IDENTITYENDGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Update_DuCardType')DROP PROCEDURE proc_Update_DuCardTypeGOCREATE PROCEDURE proc_Update_DuCardType(	@CardTypeId Int,	@CardTypeName NVarChar(200),	@CardTypeColor NVarChar(100))ASIF NOT EXISTS (SELECT * FROM DuCardType WHERE [CardTypeName]=@CardTypeName)BEGINUPDATE DuCardType	SET		[CardTypeName]=@CardTypeName ,		[CardTypeColor]=@CardTypeColor	where		[CardTypeId]=@CardTypeIdENDGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Update_DuCardTypeField')DROP PROCEDURE proc_Update_DuCardTypeFieldGOCREATE PROCEDURE proc_Update_DuCardTypeField(	@CardTypeId Int,	@FieldValue NVARCHAR(1500))ASDECLARE @sql NVARCHAR(4000)SET @sql='update DuCardType set '+@FieldValue+' WHERE CardTypeId ='+ @CardTypeIdEXEC(@sql)GOIF EXISTS (select * from sysobjects where [name] = 'proc_Delete_DuCardType')DROP PROCEDURE proc_Delete_DuCardTypeGOCREATE PROCEDURE proc_Delete_DuCardType(	@CardTypeId Int)asDELETE	DuCardType	WHERE		CardTypeId = @CardTypeIdGOIF EXISTS (select * from sysobjects where [name] = 'proc_Delete_DuCardTypeId')DROP PROCEDURE proc_Delete_DuCardTypeIdGOCREATE PROCEDURE proc_Delete_DuCardTypeId(	@CardTypeId varchar(2000))asDECLARE @strsql varchar(3000)SET @strsql='DELETE DuCardType WHERE CardTypeId in('+@CardTypeId+')'EXEC(@strsql)GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardType')DROP PROCEDURE proc_Select_DuCardTypeGOCREATE PROCEDURE proc_Select_DuCardType(	@CardTypeId Int)ASSELECT * FROM DuCardType WHERE CardTypeId = @CardTypeIdGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeExists')DROP PROCEDURE proc_Select_DuCardTypeExistsGOCREATE PROCEDURE proc_Select_DuCardTypeExists(	@CardTypeId Int)ASSELECT count(1) as H FROM DuCardType WHERE CardTypeId = @CardTypeIdGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeCount')DROP PROCEDURE proc_Select_DuCardTypeCountGOCREATE PROCEDURE proc_Select_DuCardTypeCount(	@where NVARCHAR(1000))ASDECLARE @sql NVARCHAR(4000)SET @sql='select count(*) as H from DuCardType 'IF @where<>''	SET @sql=@sql+@whereEXEC(@sql)GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeTitle')DROP PROCEDURE proc_Select_DuCardTypeTitleGOCREATE PROCEDURE proc_Select_DuCardTypeTitle(	@FieldName NVARCHAR(100),	@CardTypeId Int)ASDECLARE @sql NVARCHAR(4000)SET @sql='select top 1 '+@FieldName+' from DuCardType WHERE CardTypeId ='+ CAST(@CardTypeId AS VARCHAR(50))EXEC(@sql)GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeFuzzySearch')DROP PROCEDURE proc_Select_DuCardTypeFuzzySearchGOCREATE PROCEDURE proc_Select_DuCardTypeFuzzySearch(	@FieldList NVARCHAR(1000),	@where NVARCHAR(2000))ASDECLARE @sql NVARCHAR(4000)IF(@where<>'')	SET @where=' WHERE '+@where IF(@FieldList='')	SET @FieldList=' * ' SET @sql='select '+@FieldList+' from DuCardType '+@whereEXEC(@sql)GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeAll')DROP PROCEDURE proc_Select_DuCardTypeAllGOCREATE PROCEDURE proc_Select_DuCardTypeAllASSELECT * FROM DuCardTypeGO

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：csharp: Procedure with DAO(Data Access Object) and DAL(Data Access Layer)

关键词：access