dbfuncs.pas

详细说明:毕业论文中关于小型宾馆管理系统的详细设计毕 业论文中关于小型宾馆...一个酒店管理系统VB+Access [学生学籍管理系统(VB+Acess).zip] - !这个是刚刚编的毕业设计,可能

//
// Misc. functions used with the database.
//
// (c) Chicony Software 2001
//
// When       Who  Why
// ---------  ---  ------------------------------------------------------------
// 2001.6.6   Century Initial version
//
unit dbfuncs;

interface

uses sysutils, stdctrls, classes, ADOdb, dbtables, controls, db, registry,
     Windows,
     utils,decl,dialogs;

//==============================================================================
//
// The connection could either be via ADO or BDE. Which method is used depends
// upon the class constructor used.
//
type
  TPodmsConnection = class                                             
  public
    function InTransaction: Boolean;
    procedure Commit;
    procedure Rollback;
    procedure StartTransaction;
    procedure Open;
    procedure Close;
    function BDECheck(var errmsg: String): Boolean;
    constructor CreateADO(const connection_string: String; const provider: String; username: String=''; password: String=''; server: String=''; database_name: String=''; active: Boolean=FALSE);
    constructor CreateBDE(const username, password, server, database_name, bde_alias: String; active: Boolean=FALSE; default_session: Boolean=FALSE);
    destructor Destroy; override;
  private
    use_ado: Boolean;                   // If TRUE, ADO is being used else
                                        // BDE is being used
    use_default_session: Boolean;       // If TRUE, the default session
                                        // is being used
    ado_connection: TADOConnection;     // Only used if use_ado=TRUE
    bde_database: TDatabase;            // Only used if use_ado=FALSE
    bde_session: TSession;              // Only used if use_ado=FALSE
  end;

//
// BDE queries use TQuery. ADO queries use TADOQuery. They have different
// methods and properties. PODMS can use either BDE or ADO. This class is a
// wrapper that uses either BDE or ADO depending upon the connection it is
// given (TPodmsConnection) when the class constructor is called.
//
// This class should be used just like a TQuery or TADOQuery class
//
type
  TPodmsQuery = class
  public
    function EOF: Boolean;
    function IsFieldNull(field: Integer): Boolean;
    function IntegerFieldByName(const s: String): Integer;
    function StringFieldByName(const s: String): String;
    function BooleanFieldByName(const s: String): Boolean;
    function IntegerFieldByNumber(field: Integer): Integer;
    function DateTimeFieldByName(const s: String): TDateTime;
    procedure SQLAdd(const s: String);
    procedure SQLClear;
    function SQLCount: Integer;
    function SQLStrings(s: Integer): String;
    procedure ParamVariant(const s: String; const v: Variant);
    procedure ParamString(const s: String; const v: String);
    procedure ParamInteger(const s: String; const v: Integer);
    procedure ParamDateTime(const s: String; const v: TDateTime);
    procedure ParamDate(const s: String; const v: TDate);
    procedure ParamTime(const s: String; const v: TTime);
    procedure ParamBoolean(const s: String; v: Boolean);
    procedure ParamCurrency(const s: String; v: Currency);
    procedure ParamFloat(const s: String; v: Extended);
    procedure ParamBolb(const s:String;v:String);
    function ParamsCount: Integer;
    function ParamName(p: Integer): String;
    function ParamDataType(p: Integer): TFieldType;
    function ParamIsNull(p: Integer): Boolean;
    function ParamText(p: Integer): String;
    procedure Open;
    procedure Close;
    procedure ExecSQL;
    procedure First;
    procedure Next;
    procedure Last;
    Procedure Insert;
    procedure Post;
    procedure Edit;
    procedure Prior;
    procedure Refresh;
    procedure InsertRecord(const Values: array of const);
    function MoveBy(Distance: Integer): Integer;

    function FieldbyName(const FieldName:string):Tfield;
    function Locate(const KeyFields: String; const KeyValues: Variant; Options: TLocateOptions
             ): Boolean;
    function RecordCount: Integer;
    function RowsAffected: Integer;
    function ColumnValue(const column: String): Variant;
    function SQLtostr:TStrings;
    property data[const column: String]: Variant read ColumnValue; default;
    function Fields:Tfields;
    constructor Create(const podms_connection: TPodmsConnection);
    destructor Destroy; override;
  private
    use_ado: Boolean;
    bde_query: TQuery;
    ado_query: TADOQuery;
  end;

// See GiveDateOrTime function
type DT_TYPES=(DT_DATE, DT_TIME, DT_DATETIME);

// User-defined exceptions
type EUnknownTable = class(Exception);

// Declarations

function SafeDate(date_time: Variant; use_default: TDateTime=0.0): Variant;
function MakeBoolean(value: Variant): WordBool;
//function NewOpLock: G_T_OPLOCK;
function FillComboBoxWithBDE(var combo_box: TComboBox): Integer;
function FillComboBoxWithADO(var combo_box: TComboBox): Integer;
function IsBDEInstalled: Boolean;
function IsADOInstalled: Boolean;
function IsODBCDriver(const AliasName: String): Boolean;

// BDE functions
function GiveDateOrTime(bCombin:boolean;var q: TPodmsQuery; pname: String; give_what: DT_TYPES;
         datetime: TDateTime): Boolean;
function CreateNewQuery(const connection: TPodmsConnection): TPodmsQuery;
procedure CloseNewQuery(var q: TPodmsQuery);
function ConvalOut(val:string;tb,field:string):string;
function ConvalIn(val:string;tb,field:string):string;
function InsLogs(q:TPodmsQuery):widestring;
//function DumpSQL(query: TPodmsQuery): String;

implementation

// Podms modules
uses prefs;

//
// Create an ADO connection to a database
//
// Args: connection string (can be empty)
//       ADO provider (SQLOLEDB used by default)
//       login username (ignored if connection string passed)
//       login password (ignored if connection string passed)
//       database server hostname (ignored if connection string passed)
//       name of database (ignored if connection string passed)
//
constructor TPodmsConnection.CreateADO(const connection_string: String; const provider: String; username: String=''; password: String=''; server: String=''; database_name: String=''; active: Boolean=FALSE);
begin
     inherited Create;

     // Create ADO connection
     use_ado:=TRUE;
     bde_database:=nil;
     bde_session:=nil;
     use_default_session:=FALSE;
     ado_connection:=TADOConnection.Create(nil);
     ado_connection.LoginPrompt:=FALSE;
     if provider<>'' then ado_connection.Provider:=provider
     else if connection_string='' then ado_connection.Provider:='SQLOLEDB';
     if connection_string<>'' then ado_connection.ConnectionString:=connection_string
     else ado_connection.ConnectionString:='User ID='+username+';Password='+password+';Initial Catalog='+database_name+';Data Source='+server;
     if Active then ado_connection.Open;
end;

//
// Create a BDE connection to a database
//
// Args: login username (can be empty string if in BDE config)
//       login password (can be empty string if in BDE config)
//       database server hostname (can be empty string if in BDE config)
//       name of database (can be empty string if in BDE config)
//       BDE/ODBC alias
//       make active?
//       use the default session?
//
constructor TPodmsConnection.CreateBDE(const username, password, server, database_name, bde_alias: String; active: Boolean; default_session: Boolean);
begin
     inherited Create;

     // Create database session
     use_ado:=FALSE;
     ado_connection:=nil;
     if default_session then begin
        use_default_session:=TRUE;
        bde_session:=nil;
     end else begin
        use_default_session:=FALSE;
        bde_session:=TSession.Create(nil);
        bde_session.AutoSessionName:=TRUE;
        bde_session.SQLHourGlass:=FALSE;
        bde_session.KeepConnections:=TRUE;
        bde_session.NetFileDir:=Session.NetFileDir;
        bde_session.Active:=active;
     end;

     // Create database connection
     bde_database:=TDatabase.Create(nil);
     if use_default_session then bde_database.SessionName:=Session.SessionName
     else bde_database.SessionName:=bde_session.SessionName;
     bde_database.AliasName:='';
     bde_database.Connected:=False;
     bde_database.HandleShared:=True;
     bde_database.KeepConnection:=True;
     bde_database.LoginPrompt:=False;
     bde_database.Name:='D'+bde_database.SessionName+IntToStr(Random(999999));
     bde_database.Params.Clear;
     if Trim(username)<>'' then bde_database.Params.Add('USER NAME='+username);
     if Trim(password)<>'' then bde_database.Params.Add('PASSWORD='+password);
     if Trim(server)<>'' then bde_database.Params.Add('SERVER NAME='+server);
     if Trim(database_name)<>'' then bde_database.Params.Add('DATABASE NAME='+database_name);
     bde_database.DatabaseName:=bde_alias;
end;

// Close the database connection
procedure TPodmsConnection.Close;
begin
     if use_ado then ado_connection.Close
     else bde_database.Close;
end;

// Open the database connection
procedure TPodmsConnection.Open;
begin
     if use_ado then ado_connection.Open
     else bde_database.Open;
end;

// Return TRUE if in a database transaction
function TPodmsConnection.InTransaction: Boolean;
begin
     if use_ado then Result:=ado_connection.InTransaction
     else Result:=bde_database.InTransaction;
end;

// Start a database transaction
procedure TPodmsConnection.StartTransaction;
begin
     if use_ado then ado_connection.BeginTrans
     else bde_database.StartTransaction;
end;

// Commit a database transaction
procedure TPodmsConnection.Commit;
begin
     if use_ado then ado_connection.CommitTrans
     else bde_database.Commit;
end;

// Rollback a database transaction
procedure TPodmsConnection.Rollback;
begin
     if use_ado then ado_connection.RollbackTrans
     else bde_database.Rollback;
end;

// Destroy connection
destructor TPodmsConnection.Destroy;
begin
     if ado_connection<>nil then ado_connection.Free;
     if bde_database<>nil then bde_database.Free;
     if bde_session<>nil then bde_session.Free;
     inherited Destroy;
end;

//
// Check that BDE is configured correctly. This function always returns TRUE
// if BDE is not being used.
//
// Args: errmsg is set to an error message if the result is FALSE
//
// Returns: TRUE if BDE is configured correctly or BDE is not being used
//          FALSE if BDE is not configured correctly (errmsg is set)
//
function TPodmsConnection.BDECheck(var errmsg: String): Boolean;
var BDE_Settings: TStrings;
begin
     // Initialise
     Result:=TRUE;
     errmsg:='';

     // Using ADO?
     if use_ado then Exit;

     // Using BDE
     BDE_Settings:=TStringList.Create;
     bde_session.GetConfigParams('\SYSTEM\INIT', '', BDE_Settings);
     if StrToInt(BDE_Settings.Values['SHAREDMEMSIZE']) < 8192 then begin
        Result:=FALSE;
        errmsg:='BDE SHAREDMEMSIZE value too small - set to 8192 and restart';
     end;
end;

//------------------------------------------------------------------------------

//
// Create a  query using a podms connection
//
constructor TPodmsQuery.Create(const podms_connection: TPodmsConnection);
begin
     inherited Create;
   try
     // ADO?
     if podms_connection.use_ado then begin
        // ADO
        use_ado:=TRUE;
        bde_query:=nil;
        ado_query:=TADOQuery.Create(nil);
        ado_query.Active:=False;
        ado_query.Connection:=podms_connection.ado_connection;
        ado_query.SQL.Clear;

     end else begin
        // BDE
        use_ado:=FALSE;
        ado_query:=nil;
        bde_query:=TQuery.Create(nil);
        bde_query.Active:=False;
        bde_query.DatabaseName:=Podms_connection.bde_database.DatabaseName;
        if Podms_connection.use_default_session then bde_query.SessionName:=Session.SessionName
        else bde_query.SessionName:=Podms_connection.bde_session.SessionName;
        bde_query.UniDirectional:=FALSE; // Setting to TRUE can cause errors
                                         // that are difficult to find!
        bde_query.CachedUpdates:=FALSE;
        bde_query.SQL.Clear;
     end;
   except on E:Exception do begin
        ShowMessage('不能打开数据库，检查是否数据库已连接.'+e.Message);
   end;
   end;
end;

// Destroy query
destructor TPodmsQuery.Destroy;
begin
     if bde_query<>nil then bde_query.Free;
     if ado_query<>nil then ado_query.Free;
     inherited Destroy;
end;

// EOF?
function TPodmsQuery.EOF: Boolean;
begin
     if use_ado then Result:=ado_query.EOF
     else Result:=bde_query.EOF;
end;

// Does a field in the result set have a null value?
function TPodmsQuery.IsFieldNull(field: Integer): Boolean;
begin
     if use_ado then Result:=ado_query.Fields[field].IsNull
     else Result:=bde_query.Fields[field].IsNull
end;
function TPodmsQuery.IntegerFieldByName(const s: String): Integer;
begin
     if use_ado then Result:=ado_query.FieldByName(s).AsInteger
     else Result:=bde_query.FieldByName(s).AsInteger;
end;

function TPodmsQuery.StringFieldByName(const s: String): String;
begin

     if use_ado then Result:=ado_query.FieldByName(s).AsString
     else Result:=bde_query.FieldByName(s).AsString;

end;
function TPodmsQuery.DateTimeFieldByName(const s: String): TDateTime;
begin
     if use_ado then Result:=ado_query.FieldByName(s).AsDateTime
     else Result:=bde_query.FieldByName(s).AsDateTime;
end;
function TPodmsQuery.BooleanFieldByName(const s: String): Boolean;
begin
     if use_ado then Result:=ado_query.FieldByName(s).AsBoolean
     else Result:=bde_query.FieldByName(s).AsBoolean;
end;

function TPodmsQuery.IntegerFieldByNumber(field: Integer): Integer;
begin
     if use_ado then Result:=ado_query.Fields[field].AsInteger
     else Result:=bde_query.Fields[field].AsInteger
end;

procedure TPodmsQuery.SQLAdd(const s: String);
begin
     if use_ado then ado_query.SQL.Add(s)
     else bde_query.SQL.Add(s);
end;

procedure TPodmsQuery.SQLClear;
begin
     if use_ado then ado_query.SQL.Clear
     else bde_query.SQL.Clear;
end;

function TPodmsQuery.SQLCount: Integer;
begin
     if use_ado then Result:=ado_query.SQL.Count
     else Result:=bde_query.SQL.Count;
end;

function TPodmsQuery.SQLStrings(s: Integer): String;
begin
     if use_ado then Result:=ado_query.SQL.Strings[s]
     else Result:=bde_query.SQL.Strings[s];
end;

procedure TPodmsQuery.ParamVariant(const s: String; const v: Variant);
begin
    if use_ado then ado_query.Parameters.ParamByName(s).Value:=v
    else bde_query.ParamByName(s).Value:=v;
end;

procedure TPodmsQuery.ParamString(const s: String; const v: String);
begin

    if use_ado then ParamVariant(s, v)
    else bde_query.ParamByName(s).AsString:=v;
end;

procedure TPodmsQuery.ParamInteger(const s: String; const v: Integer);
begin
    if use_ado then ParamVariant(s, v)
    else bde_query.ParamByName(s).AsInteger:=v;
end;

procedure TPodmsQuery.ParamDate(const s: String; const v: TDate);
begin
    if use_ado then ParamVariant(s, v)
    else bde_query.ParamByName(s).AsDate:=v;
end;

procedure TPodmsQuery.ParamTime(const s: String; const v: TTime);
begin
    if use_ado then ParamVariant(s, v)
    else bde_query.ParamByName(s).AsTime:=v;
end;

procedure TPodmsQuery.ParamDateTime(const s: String; const v: TDateTime);
begin
    if use_ado then ParamVariant(s, v)
    else bde_query.ParamByName(s).AsDateTime:=v;
end;

procedure TPodmsQuery.ParamBoolean(const s: String; v: Boolean);
begin

    if use_ado then ParamVariant(s, v)
    else bde_query.ParamByName(s).AsBoolean:=v;
end;
procedure TPodmsQuery.ParamBolb(const s: String; v: string);
begin
    if use_ado then begin
        ado_query.Parameters.ParamByName(s).LoadFromFile(v,ftBlob);
    end else bde_query.ParamByName(s).LoadFromFile(v,ftBlob);
end;
procedure TPodmsQuery.ParamCurrency(const s: String; v: Currency);
begin
    if use_ado then ParamVariant(s, v)
    else bde_query.ParamByName(s).AsCurrency:=v;
end;

procedure TPodmsQuery.ParamFloat(const s: String; v: Extended);
begin