sqlfunctionunit.pas

软件可自动完成SQL Server数据库和文档的数据定时自动备份

unit SqlFunctionUnit;

interface
uses
  Classes, DB, ADODB, Windows, SysUtils;

//连接数据库(SQL)
procedure ConnectToSqlServer(_pswd, _user, _server, _databasename: String; var _conn:TADOConnection);
//连接数据库(Access)
procedure ConnectToAccess(_db:String; var _conn:TADOConnection);
//执行sql语句
procedure ExecuteSQLPro(_SQLStr:String; _sqltype:integer; var _query: TADOQuery);
//时间格式化
function CovFileDate(Fd:_FileTime):TDateTime;
//获取文件最近一次被修改的时间
function GetFileLastModifyTime(sFileName:string):TDateTime;
//获取文件夹的大小
procedure GetDirSize(sDirName:String; atItTime:TDateTime; var dirSizeTotal:real);
//获取文件的大小
function GetFileSize(sFileName:string): real;
//传输文件(块传输)
function SendFile(sFileName, aFileName:String):Boolean;
//加密
function Encrypt(_str: String):String;
//解密
function DisEncrypt(_str: String):String;
//文件夹复制
procedure DoCopyDir(sDirName:String;sToDirName:String;atTime:TDateTime);
implementation

procedure ExecuteSQLPro(_SQLStr:String; _sqltype:integer; var _query: TADOQuery);
begin
  with _query do
    begin
      Close;
      SQL.Clear;
      SQL.Add(_SQLStr);
      if  _sqltype=0 then
        Open
      else
        ExecSQL;
    end;
end;

procedure ConnectToSqlServer(_pswd, _user, _server, _databasename: String; var _conn:TADOConnection);
var
  connString:String;
begin
  connString:='Provider=SQLOLEDB.1;Password='+_pswd
                +';Persist Security Info=True;User ID='+_user
                +';Initial Catalog='+_databasename
                +';Data Source='+_server;
  if _conn.Connected then
     _conn.Close;
  _conn.ConnectionString:=connString;
end;

procedure ConnectToAccess(_db:String; var _conn:TADOConnection);
var
  connString:String;
begin
  connString:='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+
              _db+';Mode=Share Deny None;Extended Properties=""'+
              ';Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=""'+
              ';Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;'+
              'Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=""'+
              ';Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don'+
              ''''+'t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False';
  if _conn.Connected then
     _conn.Close;
  _conn.ConnectionString:=connString;
end;

function CovFileDate(Fd:_FileTime):TDateTime;
{ 转换文件的时间格式 } 
var 
  Tct:_SystemTime;
  Temp:_FileTime;
begin 
  FileTimeToLocalFileTime(Fd,Temp);
  FileTimeToSystemTime(Temp,Tct);
  CovFileDate:=SystemTimeToDateTime(Tct); 
end;

function GetFileLastModifyTime(sFileName:string):TDateTime;
{ 获取文件时间，Tf表示目标文件路径和名称 } 
const 
  Model='yyyy/mm/dd hh:mm:ss'; { 设定时间格式 }
var 
  Tp:TSearchRec; { 申明Tp为一个查找记录 }
  //T1,T2,T3:string;
begin
  FindFirst(sFileName,faAnyFile,Tp); { 查找目标文件 }
  //result:=StrToDateTime(FormatDateTime(Model,CovFileDate(Tp.FindData.ftCreationTime)));
  { 返回文件的创建时间 }
  result:=StrToDateTime(FormatDateTime(Model,CovFileDate(Tp.FindData.ftLastWriteTime)));
  { 返回文件的修改时间 }
  //T3:=FormatDateTime(Model,Now));
  { 返回文件的当前访问时间 }
  FindClose(Tp);
end;

procedure GetDirSize(sDirName:String; atItTime:TDateTime; var dirSizeTotal:real);
var
  hFindfile:Cardinal;
  tfile:String;
  sCurDir:String[255];
  FindFileData:WIN32_FIND_DATA;
begin
  //先保存当前目录
  sCurDir:=GetCurrentDir;   //获取当前目录
  ChDir(sDirName);          //转到指定目录
  hFindfile:=FindFirstFile('*.*',FindFileData);
  if hFindFile<>INVALID_HANDLE_VALUE then
    begin
      repeat
          tfile:=FindFileData.cFileName;
          if (tfile='.') or (tfile='..') then
            Continue;
          //^---
          //if (FindFileData.dwFileAttributes=FILE_ATTRIBUTE_DIRECTORY) or (FindFileData.dwFileAttributes=48) then
          //^ 判断所得资源是否是文件夹， 注意48的
          if FindFileData.dwFileAttributes and faDirectory = faDirectory then
            begin
              //^---
              if sDirName[Length(sDirName)]<>'\' then
                GetDirSize(sDirName+'\'+tfile,atItTime,dirSizeTotal)
              else
                GetDirSize(sDirName+tfile,atItTime,dirSizeTotal);
              //^ 如果含有子目录，递归调用 GetDirSize
            end
          else
            begin
              if GetFileLastModifyTime(sDirName+'\'+tfile)>atItTime then
                begin
                  dirSizeTotal:=dirSizeTotal+GetFileSize(sDirName+'\'+tfile);
                end;
            end;
      until FindNextFile(hFindFile,FindFileData)=false;
      Windows.FindClose(hFindFile);
    end
  else
    begin
      ChDir(sCurDir);
      exit;
    end;
  //回到原来的目录下
  ChDir(sCurDir);
end;

function GetFileSize(sFileName:string): real;
var f : TFileStream;
begin
  if FileExists(sFileName) then
    begin
      f:= TFileStream.Create(sFileName,fmOpenRead or fmShareDenyNone);
      Result:= f.Size/1024/1024;   //返回文件大小
      F.Free;
    end
  else
    Result:= 0;
end;

function SendFile(sFileName, aFileName:String):Boolean;
var
  sourceFile: file;
  aimFile: file;
  NumRead,NumWrite:integer;
  Buf:array[1..2048] of Char;
  noWrong:Boolean;
begin
  noWrong:=True;
  try
    try
      AssignFile(sourceFile,sFileName);
      Reset(sourceFile,1);
      AssignFile(aimFile,aFileName);
      Rewrite(aimFile,1);
      repeat
        BlockRead(sourceFile,Buf,SizeOf(Buf),NumRead);
        BlockWrite(aimFile,Buf,NumRead,NumWrite);
      until (NumRead = 0)or(NumWrite <> NumRead);
    except
      noWrong:=False;
    end;
  finally
    CloseFile(sourceFile);
    CloseFile(aimFile);
  end;
  result:=noWrong;
end;

function Encrypt(_str: String):String;
const  encryptNum=3;     //插入字符数
var
  encryptCodeID:integer;  //每次随机产生的字符
  encryptCodeStr:String;
  returnStr:String;
  i, pos, insertPos:integer;
  strLength:integer;
begin
  returnStr:=_str;
  strLength:=length(_str);
  if strLength=0 then      //如果明文为空
    begin
      encryptCodeStr:='';
      for i:=1 to encryptNum do
        begin
          Randomize;
          encryptCodeID:=Random(255);
          encryptCodeStr:= encryptCodeStr + chr(encryptCodeID);
        end;
      returnStr:= encryptCodeStr;
    end
  else
    begin
      pos:=1;
      insertPos:=2;
      while pos<strLength do
        begin
          encryptCodeStr:='';
          for i:=1 to encryptNum do
            begin
              Randomize;
              encryptCodeID:=Random(255);
              encryptCodeStr:= encryptCodeStr + chr(encryptCodeID);
            end;
          insert(encryptCodeStr,returnStr,insertPos);
          insertPos:=insertPos+encryptNum+1;
          pos:=pos+1;
        end;
    end;
  result:= returnStr;
end;

function DisEncrypt(_str: String):String;
const  encryptNum=3;   //与加密数必须相同
var
  returnStr:String;
  i:integer;
  strLength:integer;
begin
  returnStr:=_str;
  strLength:=Length(_str);
  if strLength=encryptNum then
    returnStr:=''
  else
    begin
      for i:=1 to  (strLength-1)div(encryptNum+1) do
        begin
          delete(returnStr,i+1,encryptNum);
        end;
    end;
  result:= returnStr;
end;

procedure DoCopyDir(sDirName:String;sToDirName:String;atTime:TDateTime);
var
  hFindfile:Cardinal;
  tfile:String;
  t:String;
  sCurDir:String[255];
  FindFileData:WIN32_FIND_DATA;
begin
  //先保存当前目录
  sCurDir:=GetCurrentDir;
  ChDir(sDirName);
  hFindfile:=FindFirstFile('*.*',FindFileData);
  if hFindFile<>INVALID_HANDLE_VALUE then
    begin
      if not DirectoryExists(sToDirName) then
        ForceDirectories(sToDirName);
      repeat
          tfile:=FindFileData.cFileName;
          if (tfile='.') or (tfile='..') then
            Continue;
          //if (FindFileData.dwFileAttributes=FILE_ATTRIBUTE_DIRECTORY) or (FindFileData.dwFileAttributes=48) then
          if FindFileData.dwFileAttributes and faDirectory = faDirectory then
            begin
              t:=sToDirName+'\'+tfile;
              if not DirectoryExists(t) then
                ForceDirectories(t);
              if sDirName[Length(sDirName)]<>'\' then
                DoCopyDir(sDirName+'\'+tfile,t,atTime)
              else
                DoCopyDir(sDirName+tfile,sToDirName+tfile,atTime);
            end
          else
            begin
              t:=sToDirName+'\'+tFile;
              if GetFileLastModifyTime(sDirName+'\'+tfile)>atTime then
                begin
                  try
                    CopyFile(PChar(sDirName+'\'+tfile),PChar(t),False);
                  except
                    SendFile(sDirName+'\'+tfile,t);
                  end;
                end;
            end;
      until FindNextFile(hFindFile,FindFileData)=false;
      Windows.FindClose(hFindFile);
    end
  else
    begin
      ChDir(sCurDir);
      exit;
    end;
  //回到原来的目录下
  ChDir(sCurDir);
end;

end.