webform.pas

Oracle Data Access Components Source Code ODAC v.6.70.0.45

unit WebForm;

interface

uses
  SysUtils, Classes, System.Collections, System.ComponentModel,
  System.Data, System.Drawing, System.Web, System.Web.SessionState,
  System.Web.UI, System.Web.UI.WebControls, System.Web.UI.HtmlControls,
  System.Globalization, DB, DBAccess, Ora, OraSmart, OraConnectionPool,
  MemData, Devart.Odac.DataAdapter;

type
  TWebForm = class(System.Web.UI.Page)
  {$REGION 'Designer Managed Code'}
  strict private
    procedure InitializeComponent;
    procedure btTest_Click(sender: System.Object; e: System.EventArgs);
    procedure btFill_Click(sender: System.Object; e: System.EventArgs);
    procedure btUpdate_Click(sender: System.Object; e: System.EventArgs);
    procedure btInsertRecord_Click(sender: System.Object; e: System.EventArgs);
    procedure dataGrid_EditCommand(source: System.Object; e: System.Web.UI.WebControls.DataGridCommandEventArgs);
    procedure dataGrid_DeleteCommand(source: System.Object; e: System.Web.UI.WebControls.DataGridCommandEventArgs);
    procedure dataGrid_CancelCommand(source: System.Object; e: System.Web.UI.WebControls.DataGridCommandEventArgs);
    procedure dataGrid_UpdateCommand(source: System.Object; e: System.Web.UI.WebControls.DataGridCommandEventArgs);
  {$ENDREGION}
  strict private
    procedure Page_Load(sender: System.Object; e: System.EventArgs);
  strict protected
    lbTitle: System.Web.UI.WebControls.Label;
    lbInfo: System.Web.UI.WebControls.Label;
    btInsertRecord: System.Web.UI.WebControls.Button;
    btUpdate: System.Web.UI.WebControls.Button;
    Label2: System.Web.UI.WebControls.Label;
    btFill: System.Web.UI.WebControls.Button;
    tbSQL: System.Web.UI.WebControls.TextBox;
    lbResult: System.Web.UI.WebControls.Label;
    lbError: System.Web.UI.WebControls.Label;
    tbUsername: System.Web.UI.WebControls.TextBox;
    tbPassword: System.Web.UI.WebControls.TextBox;
    tbServer: System.Web.UI.WebControls.TextBox;
    lbState: System.Web.UI.WebControls.Label;
    btTest: System.Web.UI.WebControls.Button;
    dataGrid: System.Web.UI.WebControls.DataGrid;
    OraDataAdapter: Devart.Odac.DataAdapter.OraDataAdapter;
    DataSet: System.Data.DataSet;
    Form1: System.Web.UI.HtmlControls.HtmlForm;
    cbDisconnectedMode: System.Web.UI.WebControls.CheckBox;
    ddlPoolingType: System.Web.UI.WebControls.DropDownList;
    pPoolingOptions: System.Web.UI.WebControls.Panel;
    Label1: System.Web.UI.WebControls.Label;
    tbMaxPoolSize: System.Web.UI.WebControls.TextBox;
    tbMinPoolSize: System.Web.UI.WebControls.TextBox;
    tbConnectionLifeTime: System.Web.UI.WebControls.TextBox;
    tbProxyUsername: System.Web.UI.WebControls.TextBox;
    Panel2: System.Web.UI.WebControls.Panel;
    tbProxyPassword: System.Web.UI.WebControls.TextBox;
    Panel3: System.Web.UI.WebControls.Panel;
    cbFailover: System.Web.UI.WebControls.CheckBox;
  protected
    OraSession: TOraSession;
    OraQuery: TSmartQuery;
    procedure OnInit(e: EventArgs); override;
    procedure SetOraSessionOptions;
    procedure BindGrid;
    procedure OraSessionConnectionLost(Sender: TObject; Component: TComponent;
      ConnLostCause: TConnLostCause; var RetryMode: TRetryMode);
  public
    { Public Declarations }
  end;

implementation

{$REGION 'Designer Managed Code'}
/// <summary>
/// Required method for Designer support -- do not modify
/// the contents of this method with the code editor.
/// </summary>
procedure TWebForm.InitializeComponent;
begin
  Self.OraDataAdapter := Devart.Odac.DataAdapter.OraDataAdapter.Create;
  Self.DataSet := System.Data.DataSet.Create;
  (System.ComponentModel.ISupportInitialize(Self.DataSet)).BeginInit;
  Include(Self.btInsertRecord.Click, Self.btInsertRecord_Click);
  Include(Self.btUpdate.Click, Self.btUpdate_Click);
  Include(Self.btFill.Click, Self.btFill_Click);
  Include(Self.btTest.Click, Self.btTest_Click);
  Include(Self.dataGrid.CancelCommand, Self.dataGrid_CancelCommand);
  Include(Self.dataGrid.EditCommand, Self.dataGrid_EditCommand);
  Include(Self.dataGrid.UpdateCommand, Self.dataGrid_UpdateCommand);
  Include(Self.dataGrid.DeleteCommand, Self.dataGrid_DeleteCommand);
  // 
  // OraDataAdapter
  // 
  Self.OraDataAdapter.DataSet := nil;
  Self.OraDataAdapter.Name := '';
  Self.OraDataAdapter.Tag := nil;
  // 
  // DataSet
  // 
  Self.DataSet.DataSetName := 'NewDataSet';
  Self.DataSet.Locale := System.Globalization.CultureInfo.Create('ru-RU');
  Include(Self.Load, Self.Page_Load);
  (System.ComponentModel.ISupportInitialize(Self.DataSet)).EndInit;
end;
{$ENDREGION}

procedure TWebForm.Page_Load(sender: System.Object; e: System.EventArgs);
begin
  dataSet := System.Data.DataSet(HttpContext.Current.Session['dataset']);
end;

procedure TWebForm.OnInit(e: EventArgs);
begin
  //
  // Required for Designer support
  //
  InitializeComponent;

  OraSession := TOraSession.Create(nil);
  with OraSession do begin
    ConnectPrompt := False;
    Username := 'scott';
    Password := 'tiger';
    Options.DisconnectedMode := True;
    Pooling := True;
    OnConnectionLost := OraSessionConnectionLost;
  end;
  OraQuery := TSmartQuery.Create(nil);
  with OraQuery do begin
    Session := OraSession;
    SQL.Text := 'select a.rowid, a.* from dept a';
  end;
  OraDataAdapter.DataSet := OraQuery;

  tbUsername.Text := OraSession.Username;
  tbPassword.Text := OraSession.Password;
  tbServer.Text := OraSession.Server;
  cbDisconnectedMode.Checked := OraSession.Options.DisconnectedMode;
  if OraSession.Pooling then
    case OraSession.PoolingOptions.PoolType of
      optLocal:
        ddlPoolingType.SelectedIndex := 1;
      optOCI:
        ddlPoolingType.SelectedIndex := 2;
      optMTS:
        ddlPoolingType.SelectedIndex := 3;
    end
  else
    ddlPoolingType.SelectedIndex := 0;
  tbMaxPoolSize.Text := IntToStr(OraSession.PoolingOptions.MaxPoolSize);
  tbMinPoolSize.Text := IntToStr(OraSession.PoolingOptions.MinPoolSize);
  tbConnectionLifeTime.Text := IntToStr(OraSession.PoolingOptions.ConnectionLifetime);
  tbProxyUsername.Text := OraSession.PoolingOptions.ProxyUsername;
  tbProxyPassword.Text := OraSession.PoolingOptions.ProxyPassword;
  cbFailover.Checked := Assigned(OraSession.OnConnectionLost);
  tbSQL.Text := OraQuery.SQL.Text;

  inherited OnInit(e);
end;

procedure TWebForm.SetOraSessionOptions;
begin
  OraSession.Username := tbUsername.Text;
  OraSession.Password := tbPassword.Text;
  OraSession.Server := tbServer.Text;
  OraSession.Options.DisconnectedMode := cbDisconnectedMode.Checked;

  if ddlPoolingType.SelectedIndex = 0 then
    OraSession.Pooling := False
  else begin
    OraSession.Pooling := True;
    case ddlPoolingType.SelectedIndex of
      1:
        OraSession.PoolingOptions.PoolType := optLocal;
      2:
        OraSession.PoolingOptions.PoolType := optOCI;
      3:
        OraSession.PoolingOptions.PoolType := optMTS;
    end;
    OraSession.PoolingOptions.MaxPoolSize := StrToInt(tbMaxPoolSize.Text);
    OraSession.PoolingOptions.MinPoolSize := StrToInt(tbMinPoolSize.Text);
    OraSession.PoolingOptions.ConnectionLifetime := StrToInt(tbConnectionLifeTime.Text);
    OraSession.PoolingOptions.ProxyUsername := tbProxyUsername.Text;
    OraSession.PoolingOptions.ProxyPassword := tbProxyPassword.Text;
  end;

  if cbFailover.Checked then
    OraSession.OnConnectionLost := OraSessionConnectionLost
  else
    OraSession.OnConnectionLost := nil;
end;

procedure TWebForm.BindGrid();
begin
  if dataSet.Tables['Table'] <> Nil then begin
    dataGrid.DataSource := dataSet.Tables['Table'].DefaultView;
    lbResult.Visible := true;
    btInsertRecord.Visible := true;
    if dataSet.Tables['Table'].GetChanges() <> Nil then
      lbInfo.Text := 'Changed'
  end
  else begin
    dataGrid.DataSource := Nil;
    lbResult.Visible := false;
    btInsertRecord.Visible := false;
  end;

  dataGrid.DataBind();
end;

procedure TWebForm.OraSessionConnectionLost(Sender: TObject; Component: TComponent;
  ConnLostCause: TConnLostCause; var RetryMode: TRetryMode);
begin
end;

procedure TWebForm.dataGrid_UpdateCommand(source: System.Object; e: System.Web.UI.WebControls.DataGridCommandEventArgs);
var
 i : integer;
 colValue: string;
begin
  for i := 2 to e.Item.Cells.Count - 1 do begin
    if e.Item.Cells[i].Controls.Count > 0 then begin
      colValue := System.Web.UI.WebControls.TextBox(e.Item.Cells[i].Controls[0]).Text;
      if colValue = '' then
        dataSet.Tables['Table'].Rows[e.Item.ItemIndex][i - 2] := Convert.DBNull
      else
        dataSet.Tables['Table'].Rows[e.Item.ItemIndex][i - 2] := colValue;
    end;
  end;

  dataGrid.EditItemIndex := -1;
  BindGrid();
end;

procedure TWebForm.dataGrid_CancelCommand(source: System.Object; e: System.Web.UI.WebControls.DataGridCommandEventArgs);
begin
  dataGrid.EditItemIndex := -1;
  BindGrid();
end;

procedure TWebForm.dataGrid_DeleteCommand(source: System.Object; e: System.Web.UI.WebControls.DataGridCommandEventArgs);
begin
  dataSet.Tables['Table'].Rows[e.Item.ItemIndex].Delete();
  BindGrid();
end;

procedure TWebForm.dataGrid_EditCommand(source: System.Object; e: System.Web.UI.WebControls.DataGridCommandEventArgs);
begin
  dataGrid.EditItemIndex := System.Int32(e.Item.ItemIndex);
  BindGrid();
end;

procedure TWebForm.btInsertRecord_Click(sender: System.Object; e: System.EventArgs);
var
  row : DataRow;
begin
  row := dataSet.Tables['Table'].NewRow();
  dataSet.Tables['Table'].Rows.Add(row);
  dataGrid.EditItemIndex := dataSet.Tables['Table'].Rows.Count - 1;
  BindGrid();
end;

procedure TWebForm.btUpdate_Click(sender: System.Object; e: System.EventArgs);
begin
  if dataSet.Tables['Table'] <> Nil then begin
    try try
      SetOraSessionOptions;

      OraQuery.SQL.Text := tbSQL.Text;

      OraDataAdapter.Update(dataSet, 'Table');

      lbInfo.Text := 'Updated';
    except
      on exception : Exception do begin
        lbError.Text := exception.Message;
      end;
    end;
    finally
       BindGrid();
    end;
  end;
end;

procedure TWebForm.btFill_Click(sender: System.Object; e: System.EventArgs);
var
  i : integer;
  table : DataTable;
begin
  try try
    dataSet.Clear();
    for i := 0 to dataSet.Tables.Count - 1 do begin
      table := dataSet.Tables[i];
      table.Constraints.Clear();
      table.Columns.Clear();
    end;
    dataSet.Tables.Clear();

    SetOraSessionOptions;

    OraQuery.SQL.Text := tbSQL.Text;

    OraDataAdapter.Fill(dataSet, 'Table');

    lbInfo.Text := 'Filled';
  except
    on exception : Exception do
     lbError.Text := exception.Message;
  end;
  finally
    BindGrid();
  end;
end;

procedure TWebForm.btTest_Click(sender: System.Object; e: System.EventArgs);
begin
  try
    SetOraSessionOptions;

    OraSession.Open;
    lbState.Text := 'Success';
    lbState.ForeColor := Color.Blue;
    OraSession.Close;
  except on exception: Exception do
    begin
      lbState.Text := 'Failed';
      lbState.ForeColor := Color.Red;
      lbError.Text := exception.Message;
    end;
  end;

end;

end.