u_dbpublicpack.~pas

SQL的应用

unit U_DBPublicPack;

Interface
Uses
  Windows,Messages,SysUtils,Variants,Classes,Graphics,Controls,Forms,Menus,
  Dialogs,Buttons,StdCtrls,Mask,ExtCtrls,ADODB,DB,QControls,ComCtrls,IniFiles,CheckLst,
  ShellAPI,DateUtils,ComObj,Math,DBGrids,DBCtrls,WinSock,QuickRpt, QRCtrls,QRPrntr,DBClient,ADOInt;

//**************************************************************************************************
//************************************     过程名      *********************************************
//**************************************************************************************************

//获取数据库连接字符串(SqlServer2000)
Function GetDBConnectionString(IP,DataBaseName,UserName,UserPassWord:String):String;Overload;
//连接SQLSERVER2000数据库
Function ConnectDataBase():Boolean;OverLoad;
//连接XML数据库
Function Connect_XML():Boolean;
//连接数据库表
Function GetDBTableInfo(TableName:String):String;
//(精确查询)数据库表
Function Jingque_Qry(TableName:String):String;
//(模糊查询)数据库表
Function Mohu_Qry(TableName:String):String;
//显示所有记录
Function All_Qry(TableName:String):String;
//INI文件读配置
Function ReadConfig(IniSetFileName,SectionName,ValueName:String):String;
//动态创建DBEdit,DBText
Function CreateDBEditAndDBText(DBGrid:TDBGrid):String;
//销毁DBEdit,DBText
Function DestroyDBEditAndDBText(DBGrid:TDBGrid):String;
//初始连接创建
Function InitCreateADO:string;
//初始XML的控键
Function InitCreateXML:string;
//在 TreeView_DB.显示存放表名的表
Function ShowTableDB:String;
//动态创建QRLabel和QRDBText
Function CreateQRLabelAndQRDBText(DBGrid:TDBGrid):String;
//动态释放QRLabel和QRDBText
Function DestroyQRLabelAndQRDBText(DBGrid:TDBGrid):String;
//导出数据到Excel
procedure ToExcel(DBGrid:TDBGrid);

//**************************************************************************************************
//************************************     使用单元名      *********************************************
//**************************************************************************************************

Implementation

    Uses U_Main,U_RecordStruct,U_Report,U_Logon,U_Chart;

//**************************************************************************************************
//************************************      过程      *********************************************
//**************************************************************************************************


//初始连接创建连接数据库的控键
Function InitCreateADO:string;
begin
  ADOCnt:=TADOConnection.Create(Application);
  ADOQry:=TADOQuery.Create(Application);
  DS:=TDataSource.Create(Application);
  ADOQry.Connection:=ADOCnt;
  DS.DataSet:=ADOQry;
  U_Main.F_Main.DBGrid_DB.DataSource:=DS;
end;

//初始连接创建连接数据库XML的控键
Function InitCreateXML:string;
begin
  ADOCnt:=TADOConnection.Create(Application);
  ClientDS:=TClientDataSet.Create(Application);
  DS:=TDataSource.Create(Application);
  ADOQry.Connection:=ADOCnt;
  DS.DataSet:=ClientDS;
end;

//INI文件读配置
Function ReadConfig(IniSetFileName,SectionName,ValueName:String):String;
Var
  IniHandle:TIniFile;
Begin
  If FileExists(ExtractFilePath(Application.ExeName)+IniSetFileName) Then
  Begin
    IniHandle := TIniFile.Create(ExtractFilePath(Application.ExeName)+IniSetFileName);
    Result:=IniHandle.ReadString(SectionName,ValueName,'');
    IniHandle.free;
  End
  Else
    MessageDLG('读配置文件'+ExtractFilePath(Application.ExeName) + IniSetFileName+'不存在',MtError,[Mbyes],0);
end;

//获取数据库连接字符串(SqlServer2000)
Function GetDBConnectionString(IP,DataBaseName,UserName,UserPassWord:String):String;
Var
  DBConnectionString:String;
Begin
  DBConnectionString:='Provider=SQLOLEDB.1;Password='+UserPassWord+';Persist Security Info=True';
  DBConnectionString:=DBConnectionString+';User ID='+UserName+';Initial Catalog='+DataBaseName+';Data Source='+IP;
  Result:=DBConnectionString;
End;

//连接SQLSERVER2000数据库
Function ConnectDataBase():Boolean;
Var
  DataBaseName,UserName,UserPassWord,IP:String; //定义数据库名、用户名、密码、表名和连接IP
Begin
  IP:=ReadConfig(IniFileName,'DBSet','IP');                    //获取计算机IP
  DataBaseName:=ReadConfig(IniFileName,'DBSet','DataBaseName'); //数据库名称
  UserName:=ReadConfig(IniFileName,'DBSet','UserName');         //数据库用户名称
  UserPassWord:=ReadConfig(IniFileName,'DBSet','UserPassWord'); //数据库密码
  Try
    With ADOCnt Do
    Begin
      LoginPrompt:=False;
      ConnectionString:=GetDBConnectionString(IP,DataBaseName,UserName,UserPassWord);
      Connected:=True;
      Result:=True;
    End;
  Except
    On E:EOleException Do
    Begin
      Result:=False;
      Beep();Beep();
      MessageDLG('Error：'+E.Message,MtWarning,[MBOK],0);
    End;
  End;
End;

//连接XML数据库
Function Connect_XML():Boolean;
Var
  DataBaseName,UserName,UserPassWord,IP:String; //定义数据库名、用户名、密码、表名和连接IP
Begin
  IP:=ReadConfig(IniFileName,'DBSet','IP');                    //获取计算机IP
  DataBaseName:=ReadConfig(IniFileName,'DBSet','DataBaseName'); //数据库名称
  UserName:=ReadConfig(IniFileName,'DBSet','UserName');         //数据库用户名称
  UserPassWord:=ReadConfig(IniFileName,'DBSet','UserPassWord'); //数据库密码
  Try
    With F_Main.ADOCnt_XML Do
    Begin
      LoginPrompt:=False;
      ConnectionString:=GetDBConnectionString(IP,DataBaseName,UserName,UserPassWord);
      Connected:=True;
      Result:=True;
    End;
  Except
    On E:EOleException Do
    Begin
      Result:=False;
      Beep();Beep();
      MessageDLG('Error：'+E.Message,MtWarning,[MBOK],0);
    End;
  End;
End;


//连接数据库表
Function GetDBTableInfo(TableName:String):String;
Var
  QryStr:string;
begin
//初始连接创建
  InitCreateADO;
  if ConnectDataBase()=true then
  With ADOQry Do
    begin
      QryStr:='select * from  '+TableName;
      Close;SQL.Clear;SQL.Add(QryStr);Open;
      //把表里面的字段名写入F_Main.DBComboBox_Condition中
      ADOCnt.GetFieldNames(TableName,F_Main.ComboBox_Condition.Items);
      F_Main.ComboBox_Condition.ItemIndex:=0;
//      RecordConut:=IntTostr(ADOQuery_DB.RecordCount);
//      F_Main.Label_TableName.Caption:=F_main.TreeView_DB.Selected.Text+'    总共有 '+RecordConut+' 条记录';
      Result:=TableName;
    end;
End;

//(精确查询)数据库表
Function Jingque_Qry(TableName:String):String;
Var
  QryStr:string;
begin
  With ADOQry Do
    Begin
      QryStr:='Select * From '+TableName+' Where '+F_Main.ComboBox_Condition.Text+'='+''''+F_Main.Edit_Content.Text+'''';
      Close;SQL.Clear;SQL.Add(QryStr);Open;
      if RecordCount=0 then
         MessageDlg('对不起！查找不到相关记录!',MTConfirmation ,[MBOK],0);
    End;
    Result:=TableName;
End;

//显示所有记录
Function All_Qry(TableName:String):String;
Var
  QryStr:String;
begin
    With ADOQry Do
      Begin
        QryStr:='Select * From '+TableName;
        Close;SQL.Clear;SQL.Add(QryStr);Open;
      End;
  Result:=TableName;
End;

//(模糊查询)数据库表
Function Mohu_Qry(TableName:String):String;
Var
  Str:string;
  QryStr:String;
begin
  Str:='%'+F_Main.Edit_Content.Text+'%';
    With ADOQry Do
      Begin
        QryStr:='Select * From '+TableName+' Where '+F_Main.ComboBox_Condition.Text+' like '+''''+Str+'''';
        Close;SQL.Clear;SQL.Add(QryStr);Open;
        if RecordCount=0 then
           MessageDlg('对不起！查找不到相关记录!',MTConfirmation ,[MBOK],0);
      End;
  Result:=TableName;
End;

//动态创建DBEdit和DBText
Function CreateDBEditAndDBText(DBGrid:TDBGrid):String;
var
  i:integer;
begin
   For i:=0 To DBGrid.FieldCount-1 Do
    Begin
      DBEdit[i]:=TDBEdit.Create(Application);
      DBEdit[i].DataSource:=DS;
      DBEdit[i].DataField:=DBGrid.Fields[i].FieldName;
      DBText[i]:=TDBText.Create(Application);
      DBText[i].Caption:=DBGrid.Fields[i].FieldName;
      DBEdit[i].Top:=i*20+50;  //原来是加 20 88
      DBText[i].Top:=DBEdit[i].Top+3;
      DBEdit[i].Left:=590;
      DBText[i].Left:=DBEdit[i].Left-80;
      DBEdit[i].Parent:=U_Main.F_Main;
      DBText[i].Parent:=U_Main.F_Main;
      DBEdit[i].AutoSize:=False;
      DBText[i].AutoSize:=False;
      DBEdit[i].Width:=188;
    End;
end;

//动态释放DBEdit和DBText
Function DestroyDBEditAndDBText(DBGrid:TDBGrid):String;
var
  i:integer;
begin
  For i:=0 To DBGrid.FieldCount-1 Do
     Begin
       DBEdit[i].Free;
       DBText[i].Free;
     End;
end;

//动态创建QRLabel和QRDBText
Function CreateQRLabelAndQRDBText(DBGrid:TDBGrid):String;
var
 i:integer;
begin
  ADOQry.Open;
  With F_Report Do
  Begin
  While Not ADOQry.Eof Do
    Begin
       F_Report.QuickRep_DB.DataSet:=ADOQry;
       For i:=0 To DBGrid.FieldCount-1 Do
        Begin
          QRLabel[i]:=TQRLabel.Create(Application.Owner);
          QRDBText[i]:=TQRDBText.Create(Application.Owner);
          QRLabel[i].Parent:=F_Report.QuickRep_DB.Bands.ColumnHeaderBand;//.QuickRep_DB.Bands.TitleBand;//.QRBand_Lb;//F_Report.QRBand_DB;
          QRDBText[i].Parent:=F_Report.QuickRep_DB.Bands.DetailBand;//.QRBand_DB;
          QRLabel[i].Caption:=DBGrid.Fields[i].FieldName;
          QRDBText[i].DataSet:=ADOQry;
          QRDBText[i].DataField:=DBGrid.Fields[i].FieldName;
          QRLabel[i].Top:=10;
//          QRDBText[i].Top:=QRLabel[i].Top+80;
          QRLabel[i].Left:=i*80+50;
          QRDBText[i].Left:=QRLabel[i].Left;
          QRLabel[i].Width:=100;
          QRDBText[i].Width:=100;
          QRLabel[i].Height:=16;
          QRDBText[i].Height:=16;
          QRLabel[i].Transparent:=True;
          QRLabel[i].Frame.DrawBottom:=True;
          ADOQry.Next;
        End;
        QRLabel[DBGrid.FieldCount]:=TQRLabel.Create(Application.Owner);
        QRLabel[DBGrid.FieldCount].Caption:='表名：'+DBTableName+'    打印人：'+U_Logon.F_Logon.Edit_User.Text+'        打印时间：'+DateTimeToStr(Now);
        QRLabel[DBGrid.FieldCount].Parent:=F_Report.QuickRep_DB.Bands.PageHeaderBand;
        QRLabel[DBGrid.FieldCount+1]:=TQRLabel.Create(Application.Owner);
        QRLabel[DBGrid.FieldCount+1].Caption:='总共有 '+IntTostr(ADOQry.RecordCount)+' 条记录';
        QRLabel[DBGrid.FieldCount+1].Parent:=F_Report.QuickRep_DB.Bands.PageFooterBand;
    End;
  End;
end;

//动态释放QRLabel和QRDBText
Function DestroyQRLabelAndQRDBText(DBGrid:TDBGrid):String;
var
  i:integer;
begin
  With F_Report Do
  Begin
  For i:=0 To DBGrid.FieldCount-1 Do
    Begin
      QRLabel[i].Free;
      QRDBText[i].Free;
    End;
  End;
end;

//在 TreeView_DB.显示存放表名的表
Function ShowTableDB:String;
Var
  QryStr,FieldName:string;
Begin
  DBTableName:=ReadConfig(IniFileName,'DBTableName','TableDB');//获取表名
  FieldName:=ReadConfig(IniFileName,'DBTableName','FieldName');//获取表里保存表名的字段名
  GetDBTableInfo(DBTableName);
  With ADOQry Do
    Begin
      QryStr:='Select '+FieldName+' From '+DBTableName;
      Close;SQL.Clear;SQL.Add(QryStr);Open;
      while not Eof do
        Begin
          F_Main.TreeView_DB.Items.AddChild(F_Main.TreeView_DB.Items.Item[0],ADOQry.FieldValues[FieldName]);
          Next;
        End;
    End;
End;

//DBGrid分页显示
Function SeparatePage(DBGrid:TDBGrid):String;
Begin

End;

//导出数据到Excel
procedure ToExcel(DBGrid:TDBGrid);
var
  ExcelApp: Variant;
  i,j,k:integer;
  FileName:string;
  DlgSave:TsaveDialog;
Begin
  If ADOQry.RecordCount=0 Then
  Begin
    ShowMessage('对不起！表中没有任何记录！');
    Exit;
  End
  Else
  Begin
  DlgSave:=TsaveDialog.Create(nil);
  DlgSave.Title:='导出到 >>>         Endo 制作';
  DlgSave.Filter:='*.xls|*.xls';
  if DlgSave.Execute then
  Begin
    application.ProcessMessages;
    Filename:=DlgSave.FileName;
    ExcelApp := CreateOleObject( 'Excel.Application' );
    ExcelApp.Caption :='监控系统日志数据';//'Microsoft Excel';
    ExcelApp.WorkBooks.Add;
    application.ProcessMessages;
    ExcelApp.WorkSheets[1].Activate;
    K:=1;
    For i:=0 To DBGrid.Columns.Count-1 Do
    Begin
      if DBGrid.Columns[i].Visible Then
      Begin
        ExcelApp.Cells[1,K]:=DBGrid.Columns[i].Title.Caption;
        k:=k+1;
      End;{if}
    End;{for}
    ExcelApp.rows[1].font.name:='宋体';
    ExcelApp.rows[1].font.size:=10;
    ExcelApp.rows[1].Font.Color:=clBlack;
    ExcelApp.rows[1].Font.Bold:=true;
    j:=1;
    For i:=0 To DBGrid.Columns.Count-1 Do
    Begin
      If DBGrid.Columns[i].Visible Then
      Begin
        ADOQry.First;
        for k:=1 To ADOQry.RecordCount-1 Do
        Begin
          ExcelApp.Cells[K+1,j]:=ADOQry.FieldByName(DBGrid.Columns[i].FieldName).Asstring;
          ADOQry.Next;
        End;{for}
      j:=j+1;
    End;{if}
    End;{for}
    For I:=1 To ADOQry.recordcount Do
    ExcelApp.rows[i].Font.SIZE:=9;
    ExcelApp.Columns.AutoFit;
    ExcelApp.ActiveWorkBook.SaveAs(FileName);
    ExcelApp.WorkBooks.Close;
    Application.MessageBox('数据导出成功....','数据导出',0);
    ExcelApp.Quit;
    ExcelApp:=Unassigned;
    DlgSave.Destroy;
  End;
  End;
end;

//定义图表格式
Function CreateChart():String;
Begin
//  U_Chart.F_Chart.DecisionGraph.AddSeries(Bar);
End;

End.