怎樣動態創建數據集的字段

在Delphi的數據庫開發中，如果數據字段每次更改就重抓一次字段會很麻煩，下面的方法就可以省去重復抓數據的煩惱！

第一步：創建保存字段的表

CREATE TABLE [dbo].[AppTableField] ( -- [ID] [int] IDENTITY (1, 1) NOT NULL , -- ID [TableName] [varchar] (50) NOT NULL , -- 表名 [TableCaption] [varchar] (100) NOT NULL , -- 表標題 [TableType] [varchar] (10) NOT NULL , -- 表類型 [FieldName] [varchar] (30) NOT NULL , -- 字段名 [FieldCaption] [varchar] (100) NOT NULL , -- 字段標題 [FieldType] [smallint] NOT NULL , -- 字段類型 [FieldSize] [smallint] NOT NULL , -- 字段大小 [FieldPrecision] [ smallint], -- 字段精度(主要用於BCD類型） [FieldScale] [smallint], -- 字段小數位(主要用於BCD類型) [FieldDisplaySize] [smallint] NOT NULL , -- 字段顯示長度 [FieldIndex] [int] NULL , -- 字段序號(0~MAXLEN) [IsVisible] [bit] NULL , -- 是否可見 [IsPrimaryKey] [bit] NULL , -- 是否為主關鍵字段 [IsIndex] [bit] NULL , -- 是否為縈引字段 [IsReadOnly] [bit] NULL , -- 是否只讀 [IsUpdate] [bit] NULL , -- 是否可更新 [IsWhere] [bit] NOT NULL , -- 是否可查詢 [IsForeignKey] [bit] NULL , -- 是否為外鍵 [ForeignKeyTable] [varchar] (30) , -- 外鍵表 [Memo] [varchar] (50) -- 說明 ) ON [PRIMARY] GO 系統對象類型定義

第二步：創建抓字段的存儲過程

/***** 名稱：Sp_InsertAppTableField 定義： Sp_InsertAppTableField(@ACC VARCHAR(30),@Data VARCHAR(30)) 功能：取得用戶庫中的表、視圖的字段資料插入到賬套表的AppTableField中 入口參數： @Acc ：賬套庫名稱 @Data ：用戶庫名稱 出口參數 ： 無 *****/ CREATE Proc [dbo].[Sp_InsertAppTableField](@ACC VARCHAR(30),@Data VARCHAR(30)) As SET NOCOUNT ON --清除已刪除的表的字段列表] EXEC (N'DELETE FROM '+@ACC+'.dbo.AppTableField WHERE TableName NOT IN (SELECT NAME FROM '+@Data+'.dbo.SysObjects)') --清除已刪除的字段列 EXEC ( N'DELETE FROM '+@ACC+'.dbo.AppTableField WHERE FieldName+TableName NOT IN (SELECT A.NAME+B.Name as tableName FROM '+@Data+'.dbo.SysColumns A INNER JOIN '+@Data+'.dbo.SysObjects B ON A.ID=B.ID )') --插入新增的字段 EXEC ( N'Insert Into '+@Acc+'.dbo.AppTableField (TableName,TableCaption,TableType,FieldName,FieldCaption,FieldType,FieldSize,FieldPrecision,FieldScale,FieldDisplaySize,FieldIndex,IsWhere,IsUpdate,IsPrimaryKey,IsIndex) Select aTableName,aTableCaption,aTableType, Name,Name,xType,length,xPrec,xScale,Length,a.ColID,0, CASE WHEN (aTableType=''u'') and (autoval is null) THEN 1 ELSE 0 END,0,a.IsIndex From (Select DISTINCT B.Name as aTableName,B.Name as aTableCaption,B.Type as aTableType , A.*,CASE WHEN A.CCOLID IS NULL THEN 0 ELSE 1 END AS ISINDEX from (SELECT A.*,C.COLID AS CCOLID FROM '+@Data+'.dbo.syscolumns A LEFT JOIN '+@Data+'.dbo.sysindexkeys C on A.ID=C.ID and A.ColID=C.ColID ) A inner join (Select * from '+@Data+'.dbo.sysobjects where (type=''u'' or type=''v'') and status>0 ) B on A.ID=B.ID ) A Left Join '+@Acc+'.dbo.AppTableField B ON A.aTableName=B.TableName and A.Name=B.FieldName Where B.TableName is null order by A.aTableName' ) --更新修改過的字段 EXEC (N'UPDATE '+@Acc+'.dbo.AppTableField SET FieldType=xType,FieldSize=Length,FieldPrecision=xPrec,FieldScale=xScale FROM '+@Acc+'.dbo.AppTableField A INNER JOIN ' +@Data+'.dbo.syscolumns B ON B.ID=object_ID('''+@Data+'.dbo.''+A.TableName) and A.FieldName=B.Name ') --更新字段的主關鍵屬性，並置可更新條件為主關鍵屬性相同 Exec (N' UPDATE '+@Acc+'.dbo.AppTableField SET IsWhere=1,IsPrimaryKey=1 FROM '+@Acc+'.dbo.AppTableField A INNER JOIN (select A.ID,A.Name,B.ColID,B.Name as FieldName from '+@Data+'.dbo.sysindexes A Inner Join (select B.*,A.Name from '+@Data+'.dbo.syscolumns A inner join '+@Data+'.dbo.sysindexkeys B ON A.ColID=B.ColID AND A.ID=B.ID ) B ON A.ID=B.ID and A.indid=B.indid where A.name in (select name from '+@Data+'.dbo.sysobjects where parent_obj=A.ID and xtype=''PK'' ) ) B ON object_id('''+@Data+'.dbo.''+TableName)=B.ID and A.FieldName=B.FieldName ') --更新命名為“VW+用戶表名”的視圖中對應字段為可更新字段 EXEC (N'UPDATE AppTableField SET IsUpdate =B.IsUpdate,IsIndex=B.IsIndex,IsWhere=B.IsWhere,IsPrimaryKey=B.IsPrimaryKey FROM AppTablefield A INNER JOIN (SELECT * FROM AppTableField WHERE TableType=''U'')B ON SUBSTRING(A.TableName,3,100)=B.TableName AND A.FieldName=B.FieldName ') SET NOCOUNT OFF GO

第二步：寫自動加字段的過程

//加入字段到數據集 function TsData.AppAddField(const Name: WideString; const TableName: WideString; const Flag: WideString): WideString; Var c:TComponent; Ado:TAdoQuery; sField:TField; begin try //判斷是否存在數據集如果不存在則創建 { if Flag='0' then c := FindComponent('Ado'+Name) else C:=FindComponent('AccAdo'+Name); } c := findComponent(Name); if c <>nil then c := findComponent(TDataSetProvider(c).DataSet.Name); if c=nil then begin AppProvider(Name,Flag); if Flag='0' then c := FindComponent('Ado'+Name) else C:=FindComponent('AccAdo'+Name); end; //鏈接數據集 Ado := TAdoQuery(C); Ado.Close ; //清除對就數據字段 Ado.Fields.Clear; With Sys do begin Close; //查找字段 SQL.Text := 'Select * from AppTableField Where TableName='''+TableName+''' Order by FieldIndex'; Open; first; While not eof do begin //根把字段類型定義對應對象 Case FieldByName('FieldType').AsInteger of 34 : sField := TBlobField.Create(nil);//Ado.Fields.Add(TBlobField(sField));//sField.SetFieldType(ftBlob);// image 35, 99: sField := TMemoField.Create(nil);//sField.SetFieldType(ftMemo);// text; ntext 36 : sField := TGuidField.Create(nil);// sField.SetFieldType(ftGuid);// uniqueidentifier 48 : sField := TWordField.Create(nil);//sField.SetFieldType(ftWord);// tinyint 52 : sField := TSmallintField.Create(nil);//sField.SetFieldType(ftsmallint);//smallint 56 : sField := TIntegerField.Create(nil);//sField.SetFieldType(ftInteger);// int 58,61 : sField := TDateTimeField.Create(nil);//sField.SetFieldType(ftDateTime);//smalldatetime; datetime 59,62 : sField := TFloatField.Create(nil);//sField.SetFieldType(ftFloat);// real; float 98 : sField := TVariantField.Create(nil);//sField.SetFieldType(ftVariant);// sql_variant 104 : sField := TBooleanField.Create(nil);//sField.SetFieldType(ftBoolean);// Bit; 106,108,122,60 :sField := TBcdField.Create(nil);//sField.SetFieldType(ftBCD);//decimal; numeric ; smallmoney; money 127 : sField := TLargeintField.Create(nil);//sField.SetFieldType(ftLargeint);// bigint 165 : sField := TVarBytesField.Create(nil);//sField.SetFieldType(ftVarBytes);// varbinary 167,175 : sField := TStringField.Create(nil);//sField.SetFieldType(ftString);// varchar; char 173,189 : sField := TBytesField.Create(nil);//sField.SetFieldType(ftBytes);// binary ;timestamp 231,239 : sField := TWideStringField.Create(nil);//sField.SetFieldType(ftWideString);//nvarchar , nchar 250 : sField := TAutoIncField.Create(nil);//sField.SetFieldType(ftAutoInc);// end; //加入字段名 sField.FieldName := FieldByName('FieldName').AsString ; if not (FieldByName('FieldType').AsInteger in [36,35,99,52,56,58,61,104,127,250]) then try //由於int,smallint等固定大小的類型沒有size屬性，則過濾以免報錯 sField.Size := FieldByName('FieldSize').AsInteger; except end; //BCD類型的字段要定義其精度 if sField is TBCDField then begin TBCDField(sField).Precision := FieldByName('FieldPrecision').AsInteger ; sField.Size := FieldByName('FieldScale').AsInteger ; end; sField.DisplayWidth := FieldByName('FieldDisplaySize').AsInteger ; sField.ReadOnly := FieldByName('IsReadOnly').AsBoolean ; sField.Visible := FieldByName('IsVisible').AsBoolean ; sField.ProviderFlags := []; if FieldByName('IsUpdate').AsBoolean then sField.ProviderFlags := sField.ProviderFlags +[pfInUpdate]; if FieldByName('IsWhere').AsBoolean then sField.ProviderFlags := sField.ProviderFlags + [pfInWhere]; if FieldByName('IsPrimaryKey').AsBoolean or FieldByName('IsIndex').AsBoolean then sField.ProviderFlags := sField.ProviderFlags + [pfInKey]; sField.DisplayLabel := FieldByName('FieldCaption').AsString ; sField.DataSet := Ado; //註意如玩此句將無法加入到數據集中！！！！！ Next; end; end; Result := '1'; except on E:Exception do Result := E.Message ; end; Sys.Close; end; 如果有什么疑問歡迎大在跟貼！