database.pas

SQL 分析相关代码,SQL 脚本分析相关代码

unit DataBase;


interface
  uses sysutils,Windows,pub,Classes,db,math, Graphics,printers,StdCtrls,Dialogs,ADODB,forms,inifiles,Controls,XMLDoc, XMLIntf;

  function OpenConn(ADOConn: TADOConnection;const AReConnect:boolean=false;Const ANewDBName:string =''):boolean;//开始连接数据库,成功返回true,否则返回失败

  function GetObjectType(Atext:string):  TTreeHitType;

  function GetDataBase(ADOConn: TADOConnection):TStrings;//返回所有数据库名称

  function GetObjects(ADOConn: TADOConnection;const ADBName:string;const AObjectType:string):TStrings;

  function CreateDataBaseDL(ADOConn: TADOConnection ;ACombobox:Tcombobox):boolean; //返回数据库名称

  function CreateTableViewDL(ADOConn: TADOConnection;
        const ADBName:string;const AObjectType:string;ACombobox:Tcombobox):boolean;

  function GetTableDef(ADOConn: TADOConnection;Const ATableName:string;
      var ATableInfo: TTableInfo;
      const fk_index:bool=false //是否返回外键和索引信息
      ):boolean;//返回表/试图的结构 ;结果在ATableInfo中

  function GetTableRecordCount(ADOConn: TADOConnection;Const ATableName:string;
       Const Atop:integer;//-1 表示全部
     const AWhere:string;out Count:integer):string;
  //得到表中的记录数量 返回错误信息

  function GetDataSetDef(ADOConn: TADOConnection;Const ASQL:string;
      var ATableInfo: TTableInfo):boolean; overload;//以字符串返回DS结构;结果在ATableInfo中

  function GetDataSetDef(ADOConn: TADOConnection;ATSP_Info:TSP_Info;
      var ATableInfo: TTableInfo):boolean; overload;//以字符串返回DS结构;结果在ATableInfo中


  function GetSPParameters(ADOConn: TADOConnection;Const ASPName:string;
      var ATSP_Info:  TSP_Info):boolean;
      //返回存储过程参数，结果存在 ATSP_Parameters

  function SQLDataToCDataType(Const ASQLDataType:string):string; //将来数据库类型转换成C#类型

  function FieldTypeToCDataType(Const AFieldType:TFieldType):string;
   //将来执行SQL语句返回的字段类型转换成C#类型


  function ExeSQL(ADOConn: TADOConnection;Const ASQL:string):string;
    //执行SQL,返回错误；如果没有错误，则返回空

  function GetViewScript(ADOConn: TADOConnection;
    const AViewName:string):string;
    //返回视图的脚本

  function GetViewComments(ADOConn: TADOConnection;
    const AViewName:string):string;
    //返回视图的SQL备注

  function GetSPScript(ADOConn: TADOConnection; const ASPName:string):string;
    //返回存储过程脚本

  function ObjectExists(ADOConn: TADOConnection;const ObjectNAme:string):boolean;
  function RemoveRemark(SQLStr:String):string;// 祛除SQL中的备注
  procedure AnalysisViewScript(const ASQLStr:String;
        out  ATables:string;out AViewFields:TCodeValueList); // )
        //ATables 返回与视图相关的表 ,以逗号隔开
        //ViewFields返回 视图字段
          //Code 为视图字段别名
        //Value为实际的字段名，TableName.Fieldname的形式
  function GetNullExpression(Const CDataType :string):string;

  function GetTableNameByRefOrder(ADOConn: TADOConnection;const ADBName:string):TStrings;//按得到表名称按引用的顺序，用于生成插入表数据

  function GetReferences(ADOConn: TADOConnection;const ADBName:string):TCodeValueList ;
  //得到引用关系，用于排插入顺序

  function GetSQLDataTypes(ADOConn: TADOConnection;out ADataTypes:TCodeValueList):Boolean;//
  //code 是类型，包括字定义类型 value是 原始类型

implementation

uses UDbConnSetting,  Unit_des;


   function FieldTypeToSQLDataType(Const AFieldType:TFieldType):string;
   //将来执行SQL语句返回的字段类型转换成SQL Server类型
   begin
    case  AFieldType of
     ftLargeint: result:='bigint';
     ftBytes: result:='binary';
     ftBoolean: result:='bit';
     //ftString: result:='char';
     ftDateTime: result:='datetime';
     ftBCD: result:='decimal';
     ftFloat: result:='float';
     ftBlob: result:='image';
     ftInteger: result:='int';
     //ftBCD: result:='money';
    // ftWideString: result:='nchar';
     ftMemo: result:='ntext';
    // ftBCD: result:='numeric';
     ftWideString: result:='nvarchar';
    // ftFloat: result:='real';
   //  ftDateTime: result:='smalldatetime';
     ftSmallint: result:='smallint';
     //ftBCD: result:='smallmoney';
     //ftMemo: result:='text';
     //ftBytes: result:='timestamp';
     ftWord: result:='tinyint';
     ftGuid: result:='uniqueidentifier';
     ftVarBytes: result:='varbinary';
     ftString: result:='varchar';
     end;

   end;

//********************
function GetObjectType(Atext:string): TTreeHitType;
var   XType:string;
      P:integer;
begin
           Xtype:=trim(Atext) ;
           P:=pos('.', Xtype);
           Xtype:=copy(Xtype,p+1,length(Xtype));
 
           if Xtype='用户表' then Result:=pub.tShowTable
            else
            if Xtype='视图' then Result:=pub.tShowView
            else
            if Xtype='存储过程' then Result:=pub.tShowSP


end;


function TryOpenConn(ADOConn: TADOConnection):boolean;//开始连接数据库,成功返回true,否则返回失败
  begin
     try
       ADOConn.Open ;
       result:=true;
       exit;
    except
      result:=false;
    end;
  end;
function SettingConnectionString:string;//通过设置窗口得到连接串
   begin
    with TfrmDbConnSetting.Create(nil) do
    try
     if ShowModal=mrOk then
        result:=  DBConnSetting;

    finally
      free;
    end;
   end;
function OpenConn(ADOConn: TADOConnection;const AReConnect:boolean=false;Const ANewDBName:string =''):boolean;//开始连接数据库,成功返回true,否则返回失败
var FileName:string;
    TmpStr:string;
      SvrAddress:string;
      Integrated:boolean;
      UID:string;
      pwd:String;
     // DBName:string ;
   
begin
 result:=false;

if AReConnect then
   begin
      ADOConn.Close;
      ADOConn.ConnectionString :='';
   end;

 if ADOConn.Connected then
  begin
    result:=true;
    exit;
  end;

  if ADOConn.ConnectionString <>'' then  //有连接串,尝试打开
     result:=TryOpenConn(ADOConn);
   if result then exit;
    ///////////////////////////////////////
 
  Filename:=ExtractFileDir(Application.ExeName) + '\conn.ini';
  if FileExists(Filename) then//看是否有保存的
   with TIniFile.Create(Filename) do
   try
    begin
       SvrAddress:=ReadString('Parameters','ServerName','');
       TmpStr :=ReadString('Parameters','LoginType','');
       Integrated:= TmpStr='1';

       UID:=ReadString('Parameters','UID','');

       TmpStr:= ReadString('Parameters','SaveSetting','');

    if TmpStr='1' then  //保存密码
      begin
        TmpStr :=ReadString('Parameters','Pwd','');
        pwd :=Unit_des.DESryStrHex(TmpStr,pub.DESKey);
        ADOConn.ConnectionString :=pub.BuildConnstring(SvrAddress,Integrated,UID,pwd,ANewDBName);
      end
      else
          ADOConn.ConnectionString :=SettingConnectionString;
 
    end;//if FileExists(Filename) then//看是否有保存的
    finally
      free;
    end
      else
         ADOConn.ConnectionString :=SettingConnectionString;

//***********************
  if ADOConn.ConnectionString <>'' then  //有连接串,尝试打开
     result:=TryOpenConn(ADOConn)
     else
     result:=false;

end;
//AObjectType:U,V,P
function GetObjects(ADOConn: TADOConnection;const ADBName:string;const AObjectType:string):TStrings;
begin
result:=TStringList.Create ;
    with TADOQuery.Create(nil) do
          try
            Connection :=ADOConn;
            if Database.OpenConn(ADOConn,true,ADBName) then
             begin

               SQL.Text :='select   owner = user_name(uid), name  from  '+ADBName+'.dbo.sysobjects o where type=N'''+AObjectType
                 +'''  and ISNULL(OBJECTPROPERTY(o.id, N''IsMSShipped''), 1)=0 and name<>''dtproperties'' and name<>''sysdiagrams'' order by name';
               Open ;
               while not Eof do
                begin
                 result.Add(FieldByName('owner').AsString+'.'+ FieldByName('name').AsString + '');
                 Next;
               end;

            end//if(Database.OpenConn(ADOConn) then
            finally
             free;
            end;

end;

function GetDataBase(ADOConn: TADOConnection):TStrings;//返回所有数据库名称
 begin
     result:=TStringList.Create ;
     with TADOQuery.Create(nil) do
          try
            Connection :=ADOConn;
            if Database.OpenConn(ADOConn) then
             begin
              SQL.Text :='select * from master.dbo.sysdatabases where name not in(''master'',''model'',''msdb'',''tempdb'') order by name';
              Open ;
              while not Eof do
               begin
                result.Add(Fields[0].AsString);
                Next;
               end;
            end//if(Database.OpenConn(ADOConn) then

          finally
            free;
          end;
 end;

function CreateDataBaseDL(ADOConn: TADOConnection ;ACombobox:Tcombobox):boolean; //返回数据库名称
  begin
  try
     ACombobox.Items:=GetDataBase(ADOConn);
     result:=true;
     except
     result:=false;
     end;
  end;

function CreateTableViewDL(ADOConn: TADOConnection;
        const ADBName:string;const AObjectType:string;
        ACombobox:Tcombobox):boolean;
        begin
        try
           ACombobox.Items :=DataBAse.GetObjects(ADOConn,ADBName,AObjectType);
           result:=true;
          except
           result:=false;
          end;
        end;
 //
 function GetCommentsByFullFieldName(ADOConn: TADOConnection;
 const AFullFieldName:string):string;
 //得到字段的备注 AFullFieldName至少需要包括表名和字段名称owner.tablename.fieldname/tablename.fieldname
 var TS:TStrings;
     SQLStr:string;
     OwnerName:string;
     TableName:string;
     FieldName:string;
     I:integer;
 begin
  TS:=pub.SplitString(AFullFieldName,'.');
  for I:=0 to TS.Count -1 do
    if trim(TS[I]) ='' then
      TS.Delete(I); 
  result:='';

  try
  if TS.Count<=1 then exit;//没有表名称
  if TS.Count = 2 then  //tablename.fieldname
    begin
      OwnerName:= 'dbo';
      TableName:=TS[0];
      FieldName:=TS[1];
    end else //>=3
      begin
          OwnerName:=TS[TS.count-3];;
          TableName:=TS[TS.count-2];
          FieldName:=TS[TS.count-1];

      end;

      SQLStr:='select   cast(xp.[value] as nvarchar(4000)) from ::::fn_listextendedproperty '
                       + ' (NULL, N''user'', N'''+OwnerName+''', N''table'', N'''+TableName+''', N''column'', N''' +
                       FieldName
                       +''') xp where xp.name'
                       +' = N''MS_Description''';
       with TADOQuery.Create(nil) do
          try
            Connection :=ADOConn;
            if Database.OpenConn(ADOConn) then
             begin
                 SQL.Text :=SQLStr;
                 open;
                 if Recordcount>0 then
                   result:=Fields[0].AsString ;
                 //**************
                 Close;
             end;
           finally
             free;
           end;
  finally
    TS.Free ;
  end;
 end;
 //***********************************
 function GetCommentsFromTables(ADOConn: TADOConnection;
          const AFieldName:string;const ATables:string):string;
 //从表中得到字段的备注
 //多个表用逗号隔开
 var TS:TStrings;
     SQLStr:string;
     OwnerName:string;
     TableName:string;
     FieldName:string;
     I:integer;
     P:integer;
 begin
  TS:=pub.SplitString(ATables,',');
  for I:=0 to TS.Count -1 do
    if trim(TS[I]) ='' then
      TS.Delete(I); 
  result:='';

  try
  if TS.Count=0 then exit;//列表中没有表名称
      OwnerName:='';
      FieldName:=AFieldName;

       with TADOQuery.Create(nil) do
          try
            Connection :=ADOConn;
            if Database.OpenConn(ADOConn) then
             begin
               for I:=0 to TS.Count -1 do
                begin

                  TableName:=TS[i];
                  P:=pos('.', TableName);
                  if P<>0 then
                    begin
                      OwnerName:=copy(TableName,1,P-1);
                      if OwnerName='' then

                      TableName:=copy(TableName,P+1,length(TableName));
                    end;

                   OwnerName:='dbo'; //20070329  

                  SQLStr:='select   cast(xp.[value] as nvarchar(4000)) from ::::fn_listextendedproperty '
                       + ' (NULL, N''user'', N'''+OwnerName+''', N''table'', N'''+TableName+''', N''column'', N''' +
                       FieldName
                       +''') xp where xp.name'
                       +' = N''MS_Description''';
                 Close;
                 SQL.Text :=SQLStr;
                 open;
                 if Recordcount>0 then
                  begin
                   result:=Fields[0].AsString ;
                   break;//找到了，跳出去
                  end;
                 //**************
                 Close;
                end; 
             end;
           finally
             free;
           end;
  finally
    TS.Free ;
  end;
 end;


 function IsField(const AFieldName:string):boolean;
 //判断视图字段是否来自一个实际的字段
 //排除 数字，字符串，函数，@@,带操作符的
 var  F:Extended;//临时变量
      B:boolean;
      j:integer;