unit DBConnection_U;

interface

uses
  Forms, SysUtils, Classes, DB, ADODB, StdCtrls, DBGrids, DBCtrls,ComCtrls;

type
  redArr = array[0..10] of Byte;

  TConnection = class(TObject)
  private

  public
    dbConnection: TADOConnection;
    dsrOne, dsrFour, dsrQRYA, dsrQRYB,dsrQryC, dsrQryD,dsrTwo,dsrThree: TDataSource;
    tblOne, tblTwo,tblThree, tblFour: TADOTable;
    qryA, qryB, qryC, qryD: TADOQuery;

    procedure dbConnect;
    procedure dbDisconnect;
    procedure RestoreDatabase;
//set up different dbgrids...
    procedure Gridsetup(var GridOne,  GridSQL: TDBGrid);overload;
    procedure Gridsetup(var GridOne, GridTWo, GridSQL: TDBGrid);overload;
    procedure Gridsetup(var GridOne, GridTWo,GridThree, GridSQL: TDBGrid);overload;
    procedure Gridsetup(var GridOne,  GridTWo,GridThree,GridFour,  GridSQL: TDBGrid);overload;
//setup headings...
    procedure setupControls(var lblOne: Tlabel); overload;
    procedure setupControls(var lblOne,lblTwo: Tlabel); overload;
    procedure setupControls(var lblOne,lblTwo,lblThree: Tlabel); overload;
    procedure setupControls(var lblOne,lblTwo,lblThree,lblFour: Tlabel); overload;

//setup richedit...
     procedure setupControls(sHeading:string;iNumCols:Byte;Colwidths:redArr;var redQ2:TRichedit); overload;

    procedure FormatTableSpecialFields;
    procedure SetWidths(GridSQL: TDBGrid);
    procedure RunSQL(Statement: string; GridSQL: TDBGrid);
    procedure ExecuteSQL(Statement: string; var Changed: Boolean);
    procedure SetGridColumnWidths(Grid: Tdbgrid);
    procedure DisplayAll(gridSql:tdbGrid);
  end;

var
  MyForm: TForm;

implementation

{ TConnection }
uses Controls, Dialogs;

const
  DBFileName: String = 'Esports.mdb';
  OneTableName: String = 'tblPlayers';
  TwoTableName: String = 'tblTeamGames';
  BackupOneTblName: String = 'CopytblPlayers';
  BackupTWoTblName: String = 'CopytblTeamGames';

  procedure TConnection.dbConnect;
begin
  dbConnection := TADOConnection.Create(MyForm);
  dbConnection.LoginPrompt := False;
  dbConnection.ConnectionString :=
    'Provider=Microsoft.Jet.OLEDB.4.0;' + 'Data Source=' + DBFileName + ';' +
    'Mode=ReadWrite;' + 'Persist Security Info=False;' +
    'Jet OLEDB:Database Password=';
  dbConnection.Provider := 'Provider=Microsoft.Jet.OLEDB.4.0;';
  dbConnection.Open;

  tblOne := TADOTable.Create(MyForm);
  tblOne.Connection := dbConnection;
  tblOne.TableName := OneTableName;
  tblOne.Open;
//  tblOne.Sort := 'Code ASC';
  tblOne.First;

   TblTwo := TADOTable.Create(MyForm);
  TblTwo.Connection := dbConnection;
  TblTwo.TableName := TwoTableName;
  TblTwo.Open;
  //TblTwo.Sort := 'Code ASC';
  TblTwo.First;

//  tblThree := TADOTable.Create(MyForm);
//  tblThree.Connection := dbConnection;
//  tblThree.TableName := ThreeTableName;
//  tblThree.Open;
////  tblThree.Sort := 'BreedCode ASC';
//  tblThree.First;
//
//  tblFour := TADOTable.Create(MyForm);
//  tblFour.Connection := dbConnection;
//  tblFour.TableName := FourTableName;
//  tblFour.Open;
//
//  tblFour.First  ;

  qryA := TADOQuery.Create(MyForm);
  qryA.Connection := dbConnection;
  qryB := TADOQuery.Create(MyForm);
  qryB.Connection := dbConnection;
//  qryC := TADOQuery.Create(MyForm);
//  qryC.Connection := dbConnection;
//  qryD := TADOQuery.Create(MyForm);
//  qryD.Connection := dbConnection;



  dsrOne := TDataSource.Create(MyForm);
  dsrOne.DataSet := tblOne;
  dsrTwo := TDataSource.Create(MyForm);
  dsrTwo.DataSet := tblTWo;
//  dsrThree := TDataSource.Create(MyForm);
//  dsrThree.DataSet := tblThree;
//  dsrFour := TDataSource.Create(MyForm);
//  dsrFour.DataSet := tblFour;

  dsrQRYA := TDataSource.Create(MyForm);
  dsrQRYA.DataSet := qryA;
  dsrQRYB := TDataSource.Create(MyForm);
  dsrQRYB.DataSet := qryB;
//  dsrQRYC := TDataSource.Create(MyForm);
//  dsrQRYC.DataSet := qryC;
//  dsrQRYD := TDataSource.Create(MyForm);
//  dsrQRYD.DataSet := qryD;

end;

procedure TConnection.dbDisconnect;
begin
  qryA.Free;
  qryA := nil;
  qryB.Free;
  qryB := nil;
//  qryC.Free;
//  qryC := nil;
//  qryD.Free;
//  qryD := nil;
  tblOne.Free;
  tblOne := nil;
  tblTwo.Free;
  tblTwo := nil;
//  tblThree.Free;
//  tblThree := nil;
//  tblFour.Free;
//  tblFour := nil;
//  dbConnection.Close;
  dbConnection.Free;
  dbConnection := nil;
end;



procedure TConnection.DisplayAll(gridSql: tdbGrid);
begin
    qryA.Close;
    qryA.SQL.Text := 'Select * from tblPlayers order by PlayerID' ;
    qryA.Open;
    SetGridColumnWidths(gridSql);
end;

procedure TConnection.ExecuteSQL(Statement: string; var Changed: Boolean);
begin
  if Length(Statement) <> 0 then
  begin
    qryA.Close;
    qryA.SQL.Text := Statement;
    qryA.ExecSQL;
    Changed := True;
  end
  else
  begin
    MessageDlg('ERROR: No SQL statement!', mtError, [mbOk], 0);
    Changed := False;
  end;
end;



procedure TConnection.FormatTableSpecialFields;
begin
//  TDateTimeField(tblMany.FieldByName('startdate')).DisplayFormat := 'YYYY/MM/DD';
//  TCurrencyField(tblMany.FieldByName('allowance')).DisplayFormat := '$ #00.00';
end;

procedure TConnection.RestoreDatabase;
var
  sMsg: string;
begin
  // restore the orginal data in database.
  sMsg := 'Are you sure you want to reset the database?';
  if MessageDlg(sMsg, mtConfirmation, [mbYes, mbNo], 0) = mrYes then
  begin
    try
      Screen.Cursor := crHourGlass;
      try
        tblOne.Close;
        tblTwo.Close;
         DeleteFile('ESports.mdb') ;
         RenameFile('ESports - Copy.mdb', 'ESports.mdb');
         tblOne.Open;
         tblTwo.Open;
      except
        on e: Exception do
        begin
          ShowMessage(e.Message);
        end;
      end;
    finally
      Screen.Cursor := crDefault;
    end;
    MessageDlg('Database reset complete.', mtInformation, [mbOk], 0);
  end; // if
end;

procedure TConnection.RunSQL(Statement: string; GridSQL: TDBGrid);
var
  i: integer;
begin
  if Length(Statement) <> 0 then
  begin
    qryA.Close;
    qryA.SQL.Text := Statement;
    qryA.Open;
  end
  else
  begin
    MessageDlg('ERROR: No SQL statement.', mtError, [mbOk], 0);
  end;

end;


procedure Tconnection.SetGridColumnWidths(Grid: Tdbgrid);
const
  DEFBORDER = 10;
var
  temp, n: Integer;
  lmax: array [0..30] of Integer;
begin
  with Grid as TDBGrid do
  begin
    Canvas.Font := Font;
    for n := 0 to Columns.Count - 1 do
      lmax[n] := Canvas.TextWidth(Fields[n].FieldName) + DEFBORDER;
    DataSource.DataSet.First;
    while not DataSource.DataSet.EOF do
    begin
      for n := 0 to Columns.Count - 1 do
      begin
        temp := Canvas.TextWidth(trim(Columns[n].Field.DisplayText)) + DEFBORDER;
        if temp > lmax[n] then lmax[n] := temp;
      end; {for}
      DataSource.DataSet.Next;
    end;
    DataSource.DataSet.First;
    for n := 0 to Columns.Count - 1 do
      if lmax[n] > 0 then
        Columns[n].Width := lmax[n];
  end;
end;


procedure TConnection.setupControls(sHeading: string; iNumCols: Byte;
  Colwidths: redArr; var redQ2: TRichedit);
var
 I:Byte;
 iLength: Byte;
 sLine : String;

begin
redQ2.Font.Name := 'Consolas';
redQ2.SelAttributes.Size := 14;
redQ2.Lines.Add(sHeading);
iLength := Length(sHeading);
for I := 1 to iLength+2 do
  sLine := sLine + '=';
redQ2.Lines.Add(sLine);
redQ2.Paragraph.TabCount := iNumCols;
for I := 0 to iNumCols-1 do
  redQ2.Paragraph.Tab[I] := Colwidths[i];


end;

procedure TConnection.setupControls(var lblOne: Tlabel);
begin
lblOne.Caption:= OneTableName;
end;

procedure TConnection.setupControls(var lblOne, lblTwo: Tlabel);
begin
lblOne.Caption:= OneTableName;
lblTwo.Caption:= TwoTableName;
end;

procedure TConnection.setupControls(var lblOne, lblTwo, lblThree: Tlabel);
begin
lblOne.Caption:= OneTableName;
lblTwo.Caption:= TwoTableName;
//lblThree.Caption:= ThreeTableName;
end;

procedure TConnection.setupControls(var lblOne, lblTwo, lblThree,
  lblFour: Tlabel);
begin
lblOne.Caption:= OneTableName;
lblTwo.Caption:= TwoTableName;
//lblThree.Caption:= ThreeTableName;
//lblFour.Caption:= FourTableName;
end;


procedure TConnection.SetWidths(GridSQL: TDBGrid);
begin

end;

procedure TConnection.Gridsetup(var GridOne, GridSQL: TDBGrid);
begin
  GridOne.DataSource := dsrOne;
  GridSQL.DataSource := dsrQRYA;
  SetGridColumnWidths(gridOne);

  tblOne.First;
  tblTwo.First;
//  tblThree.First;
//  tblFour.First;
end;

procedure TConnection.Gridsetup(var GridOne, GridTWo, GridSQL: TDBGrid);
begin
  GridOne.DataSource := dsrOne;
  GridTwo.DataSource := dsrTWo;
  GridSQL.DataSource := dsrQRYA;
  SetGridColumnWidths(gridOne);
  SetGridColumnWidths(gridTwo);

  tblOne.First;
  tblTwo.First;
//  tblThree.First;
//  tblFour.First;
end;

procedure TConnection.Gridsetup(var GridOne, GridTWo, GridThree,
  GridSQL: TDBGrid);
begin
  GridOne.DataSource := dsrOne;
  GridTwo.DataSource := dsrTWo;
//  GridThree.DataSource := dsrThree;
  GridSQL.DataSource := dsrQRYA;
  SetGridColumnWidths(gridOne);
  SetGridColumnWidths(gridTwo);
//  SetGridColumnWidths(gridThree);

  tblOne.First;
  tblTwo.First;
//  tblThree.First;
//  tblFour.First;
end;

procedure TConnection.Gridsetup(var GridOne, GridTWo, GridThree, GridFour,
  GridSQL: TDBGrid);
begin
  GridOne.DataSource := dsrOne;
  GridTwo.DataSource := dsrTWo;
//  GridThree.DataSource := dsrThree;
//  GridFour.DataSource := dsrFour;
 GridSQL.DataSource := dsrQRYA;
  SetGridColumnWidths(gridOne);
  SetGridColumnWidths(gridTwo);
//  SetGridColumnWidths(gridThree);
//  SetGridColumnWidths(gridFour);

  tblOne.First;
  tblTwo.First;
//  tblThree.First;
//  tblFour.First;
end;


end.
