数据字典解决方案和存储过程设计

开发企业应用时，不使用ORM工具框架时，自行建立一个数据字典表，用于设置标题、控件、显示格式等设计，也是一个可行的办法。

一、前端编辑界面（桌面）

这是内容编辑界面。

二、数据字典表

X9_SJZD字段列表：

ID      	int     	    4	键号          	TextEdit	        
TNAME   	varchar 	   20	数据表名        	TextEdit	        
ZDBB    	varchar 	   20	字典版本        	TextEdit	        
FXH     	int     	    4	序号          	TextEdit	        
FNAME   	varchar 	   20	字段名称        	TextEdit	        
FTYPE   	varchar 	   50	类型          	TextEdit	        
FLEN    	int     	    4	长度          	TextEdit	        
FPREC   	int     	    4	有效数字        	TextEdit	        
FSCALE  	int     	    4	小数位数        	TextEdit	        
FMEMO   	nvarchar	   60	标题          	TextEdit	        
FZMC    	nvarchar	   20	分组名称        	TextEdit	        
SRLX    	nvarchar	   20	输入类型        	TextEdit	        
BYXM    	bit     	    1	必要项目        	CheckEdit	        
HSXM    	bit     	    1	整行项目        	CheckEdit	        
ALIGNMENT	int     	    4	对齐          	TextEdit	        
EDIT    	varchar 	   50	控件          	TextEdit	        
FORMATTYPE	varchar 	   50	格式类型        	TextEdit	        
FORMATSTR	varchar 	  100	格式字符        	TextEdit	        
MASKTYPE	varchar 	   50	掩码类型        	TextEdit	        
MASKSTR 	varchar 	  100	掩码字符        	TextEdit	        
SUMTYPE 	varchar 	   20	摘要类型        	TextEdit	        
SUMFMT  	varchar 	   30	摘要格式        	TextEdit	        
SEARCH  	nvarchar	  400	上网搜索        	TextEdit	        
TOOLTIP 	nvarchar	  240	操作提示        	TextEdit	        

三、当前端访问某一表时，执行初始过程代码(MS SQLSERVER)

CREATE PROCEDURE [dbo].[X9_ZRSJZD] 
   (@ZDBB VARCHAR(20),    
	@TBMC VARCHAR(20),    
	@SFGX INT)  
AS 
BEGIN TRY   
	SET NOCOUNT ON; 
	IF @SFGX=1
		BEGIN    
			--插入新行
			INSERT INTO X9_SJZD  (TNAME,ZDBB, FNAME, FXH, FTYPE,FLEN,FPREC,FSCALE)
				SELECT    OBJECT_NAME(OBJECT_ID) , @ZDBB AS ZDBB, NAME, COLUMN_ID, TYPE_NAME(SYSTEM_TYPE_ID) ,
						MAX_LENGTH ,PRECISION,SCALE
					FROM   SYS.COLUMNS WHERE  OBJECT_NAME(OBJECT_ID)=@TBMC AND NAME<>'X9STAMP' 
						AND ((OBJECT_NAME(OBJECT_ID)+@ZDBB+NAME) NOT IN  (SELECT TNAME+ZDBB+FNAME AS ZDHH 
						FROM  X9_SJZD AS X9_SJZD_1));
			--删除多余行
			DELETE FROM X9_SJZD WHERE TNAME=@TBMC AND FTYPE='BINARY';
			DELETE FROM X9_SJZD  WHERE TNAME=@TBMC AND  ((FNAME) NOT IN  
				(SELECT  NAME AS FNAME	FROM  SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID)=@TBMC));
		END
	IF @SFGX=2
		BEGIN    
			--删除所有行
			DELETE FROM X9_SJZD  WHERE TNAME=@TBMC AND ZDBB=@ZDBB;
			--插入所有行
			INSERT INTO X9_SJZD  (TNAME,ZDBB, FNAME, FXH, FTYPE,FLEN,FPREC,FSCALE)
				SELECT    OBJECT_NAME(OBJECT_ID) , @ZDBB AS ZDBB, NAME, COLUMN_ID, 
					TYPE_NAME(SYSTEM_TYPE_ID) ,MAX_LENGTH ,PRECISION,SCALE
					FROM   SYS.COLUMNS WHERE  OBJECT_NAME(OBJECT_ID)=@TBMC AND NAME<>'X9STAMP' 
						AND ((OBJECT_NAME(OBJECT_ID)+@ZDBB+NAME) NOT IN  
						(SELECT TNAME+ZDBB+FNAME AS ZDHH FROM  X9_SJZD AS X9_SJZD_1));
		END
	--更新相关信息   
	UPDATE X9_SJZD SET  FXH = SYS.COLUMNS.COLUMN_ID,  FTYPE = TYPE_NAME(SYSTEM_TYPE_ID),FLEN=MAX_LENGTH,FPREC=PRECISION,FSCALE=SCALE
		FROM X9_SJZD INNER JOIN      SYS.COLUMNS ON X9_SJZD.TNAME = OBJECT_NAME(SYS.COLUMNS.OBJECT_ID) AND 
			X9_SJZD.FNAME = SYS.COLUMNS.NAME   
		WHERE  TNAME =@TBMC AND ZDBB=@ZDBB ;   
	--设置缺省   
	UPDATE X9_SJZD SET FORMATTYPE=(CASE UPPER(FTYPE)  WHEN 'DECIMAL' THEN 'Numeric' WHEN 'NUMERIC' THEN 'Numeric'  
			WHEN 'DATETIME' THEN 'DateTime'  ELSE '' END), FORMATSTR =(CASE FTYPE  WHEN 'DECIMAL' THEN 'N2' 
			WHEN 'NUMERIC' THEN '0.'+REPLICATE ('0',FSCALE)   WHEN 'DATETIME' THEN 'YYYY-MM-DD'   ELSE '' END)  
		WHERE TNAME =@TBMC AND ZDBB=@ZDBB AND FORMATSTR =''  ;   
	UPDATE X9_SJZD SET SRLX=(CASE UPPER(FTYPE)  
			WHEN 'CHAR' THEN '文本'  WHEN 'NCHAR' THEN '文本'  WHEN 'TEXT' THEN '文本'  
			WHEN 'NTEXT' THEN '文本'   WHEN 'VARCHAR' THEN '文本'  WHEN 'NVARCHAR' THEN '文本'  
			WHEN 'INT' THEN '整型'  WHEN 'SMALLINT' THEN '整型'      WHEN 'BIGINT' THEN '整型'      
			WHEN 'DECIMAL' THEN '数值'    WHEN 'NUMERIC' THEN '数值'   WHEN 'FLOAT' THEN '数值'  WHEN 'REAL' THEN '数值'      
			WHEN 'DATE' THEN '日期时间'   	WHEN 'DATETIME' THEN '日期时间' WHEN 'DATETIME2' THEN '日期时间'  
			WHEN 'SMALLDATETIME' THEN '日期时间'      WHEN 'DATETIMEOFSET' THEN '日期时间'  WHEN 'TIME' THEN '日期时间'  
			WHEN 'BIT' THEN '逻辑'   
			WHEN 'BINARY'  THEN '对象'  WHEN 'VARBINARY'  THEN '对象'  WHEN 'IMAGE' THEN '对象'      
				ELSE '' END)    
		WHERE  X9_SJZD.TNAME =@TBMC AND X9_SJZD.ZDBB=@ZDBB;   
	UPDATE X9_SJZD SET ALIGNMENT =5 WHERE (FNAME='ID' OR FNAME='OID' OR FNAME='MID' OR FNAME='MHH' OR EDIT='CheckEdit' ) 
			AND TNAME =@TBMC AND ZDBB=@ZDBB;   
	--更新为近似值 
	DECLARE @GXID INT;
	DECLARE @ZDID INT,@ZDFNAME VARCHAR(20),@ZDFTYPE VARCHAR(20),@ZDFLEN INT;
	DECLARE SJZD_cursor CURSOR LOCAL FOR
		SELECT ID,FNAME,FTYPE,FLEN FROM X9_SJZD 
		WHERE TNAME =@TBMC AND ZDBB=@ZDBB AND (FMEMO='' OR EDIT='');
	OPEN SJZD_cursor;
	FETCH FROM SJZD_cursor
		INTO @ZDID,@ZDFNAME,@ZDFTYPE,@ZDFLEN;
	WHILE @@FETCH_STATUS=0
		BEGIN
			SET @GXID=0;
			SELECT TOP 1 @GXID=ID FROM X9_SJZD 
				WHERE TNAME=@TBMC AND FNAME=@ZDFNAME AND FTYPE=@ZDFTYPE AND 
					FLEN=@ZDFLEN AND FMEMO<>'' AND EDIT<>'' ORDER BY ID DESC;
			IF @GXID=0
				BEGIN
					SELECT TOP 1 @GXID=ID FROM X9_SJZD 
						WHERE FNAME=@ZDFNAME AND FTYPE=@ZDFTYPE AND 
							FLEN=@ZDFLEN AND FMEMO<>'' AND EDIT<>'' ORDER BY ID DESC;
				END
			IF @GXID<>0
				BEGIN
					UPDATE X9_SJZD SET FMEMO = ZD1.FMEMO, FZMC = ZD1.FZMC, SRLX = ZD1.SRLX,BYXM = ZD1.BYXM, 
						HSXM=ZD1.HSXM,ALIGNMENT=ZD1.ALIGNMENT, EDIT = ZD1.EDIT,MASKTYPE  = ZD1.MASKTYPE ,
						MASKSTR  = ZD1.MASKSTR , FORMATTYPE = ZD1.FORMATTYPE,FORMATSTR = ZD1.FORMATSTR,
						SUMTYPE = ZD1.SUMTYPE,SUMFMT = ZD1.SUMFMT,SEARCH = ZD1.SEARCH,TOOLTIP = ZD1.TOOLTIP    
					FROM X9_SJZD INNER JOIN  X9_SJZD AS ZD1 ON ZD1.ID=@GXID 
					WHERE X9_SJZD.ID =@ZDID;   
				END
		FETCH NEXT FROM SJZD_cursor
			INTO @ZDID,@ZDFNAME,@ZDFTYPE,@ZDFLEN;
	END
	CLOSE SJZD_cursor;
	DEALLOCATE SJZD_cursor;
	--设置功能定义用控件
	UPDATE X9_SJZD SET EDIT=(CASE WHEN ZDBB='99' AND FMEMO LIKE '%字段%' THEN 'FieldList' ELSE 'TextEdit' END)         
		WHERE X9_SJZD.ZDBB=@ZDBB AND X9_SJZD.TNAME =@TBMC AND (EDIT IS NULL OR EDIT ='');   
	--设置分组
	UPDATE X9_SJZD SET FZMC='1.基本信息'    
		WHERE X9_SJZD.ZDBB=@ZDBB AND X9_SJZD.TNAME =@TBMC AND (X9_SJZD.FZMC ='' OR X9_SJZD.FZMC IS NULL);   
	UPDATE X9_SJZD SET EDIT='CheckEdit'  
		WHERE X9_SJZD.ZDBB=@ZDBB AND X9_SJZD.TNAME =@TBMC AND EDIT='TextEdit' AND FTYPE='bit';
	SELECT '数据字典维护';  
END TRY  
BEGIN CATCH  
	DECLARE @ERRORMESSAGE NVARCHAR(4000),@ERRORSEVERITY INT,@ERRORSTATE INT;    
	SELECT  @ERRORMESSAGE = ERROR_MESSAGE(),@ERRORSEVERITY = ERROR_SEVERITY(),@ERRORSTATE = ERROR_STATE();    
	RAISERROR (@ERRORMESSAGE,  @ERRORSEVERITY,  @ERRORSTATE );   
END CATCH;    

四、数据字典应用

数据字典通过自动加手动的编辑后，前端设计或后端设计就可以使用了。
根据需要设计可用的字段，即可满足模型化低代码的操作界面设计了。