Oxygene Database read/update/add/delete -- (ex Delphi) Help!

Elements Oxygene
1

As an ex Delphi person, I’ve enjoyed (!) the migration to RemObjects. Much of the re-learning has been beneficial in bringing me closer to the underlying Dot Net principles, (and freeing me from some of the Delphi peculiarities!).
However, trying to manage the Database read/add/edit/delete has been a nightmare. What was simple in Delphi ADO seems to be massively complex. Even doing extensive reviews on the web fails to reveal a simple principle. There are numerous postings from people having problems doing “simple” deletes/updates.

In my Delphi stuff, I had a small number of tailored routines that did all I needed. In RemObjects I have attempted to do the same, but I have repeatedly found that that they don’t work in individual circumstances.

I know this is not a RemObjects problem, so forgive me for the rant. But if anyone can point me to a definitive source for a standard database access/update approach I would be very grateful!

As an example, my latest problem is a routine which deletes the rows in a specified table, then updates the underlying database with that change.

For a number of my tables in an Access database, this works fine, but in one table it fails with the famous “Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.”.

And yes it does have a primary key,

The relevant code is:

method clsDB.SetDBTableForUpdates(pTableNameStr: String; var pOleDbAdapt: System.Data.OleDb.OleDbDataAdapter; var pDTable: System.Data.DataTable);
begin
var cb := new System.Data.OleDb.OleDbCommandBuilder(pOleDbAdapt);
pOleDbAdapt.UpdateCommand := cb.GetUpdateCommand;
pOleDbAdapt.InsertCommand := cb.GetInsertCommand;
var DSet := new System.Data.DataSet;
pOleDbAdapt.Fill(DSet, pTableNameStr);
pDTable := DSet.Tables[pTableNameStr];
end;

method clsDB.DBTableDeleteAll(pTableNameStr: String);
begin
var OleDbAdapt := new System.Data.OleDb.OleDbDataAdapter('select * from ’ + pTableNameStr, clsVariables.TheDBOleDbConn);
var TheDTable := new System.Data.DataTable;
clsDB.SetDBTableForUpdates(pTableNameStr, var OleDbAdapt, var TheDTable);
var cm := TheDTable.Rows.Count - 1;
if cm = -1 then exit;
var c: Integer;
for c := 0 to cm do
begin
TheDTable.Rows[c].Delete;
end;
OleDbAdapt.Update(TheDTable);
end;

I may be me missing something obvious, in which case I apologise.

Any help/advice/derision (:slight_smile:) would be very welcome.

2

Why do you make it this difficult?
Why not use a command to execute 'Delete from ’ + pTableNameStr if you want to delete all records?

3

Hi,

This is new code right?

There are lots of options to access a database on .NET, our data abstract product is of course a great option, but presuming you want to use the apis directly I would advise against using Data Adapters and data tables; they’re ancient. Entity Frameworks is a good option, and also a lot safer to use than string building like above, Dapper is a lower level. All 3 map to lists/sequences of regular classes and should just work with MSAccess. In your case, I’d use DA if I were to go for multitier, and Entity Framework for code that talks directly to a database.

4

As always, many thanks for feedback. I haven’t really looked at Entity Framework - it always sounded a bit over the top for the kind of “lower level” stuff I do. But I’ll have an explore.

5

Thanks for feedback on delete command. I’ll continue exploring. It actually turned out that the error I was getting was due to my mis-spelling the table name! :tired_face: Too late at night, and blindingly obvious in the morning!

6

If you want to just use SQL dapper is a good option too.

7

There’s a reason why it’s literally called CRUD. Like you, I prefer to be as low-level as reasonable. I’m sure Entity and the Data Abstraction thing are great, but I don’t like the “and then magic occurs” aspect. In fact I’ve been up all week diagnosing a case where the “magic” doesn’t work as it should.

8

Thats why I also mentioned Dapper. With dapper you can do something like:

type
  MyClass = class
  public
    property Name: String;
    property ID: Integer;    
  end;

var lData := db.Query<MyClass>('select Name, ID from Table').ToList(); << List of MyClass.
9

I haven’t done any coding in a while, but I was using ADO.Net. Fairly straightforward. Check out the msdn documentation examples: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples

10

Thanks for this. I’m still floating around a bit trying to decide on the best approach. I’ll have another look.