Best way to insert data using dephi in sql server 2008

Question

I always used such a script to insert data into a table in delphi 7

sql := 'INSERT INTO table_foo (field1,field2,field3) VALUES (' +quotedstr('value1') +','+quotedstr('value2') +','+quotedstr('value3') +')'; adoquery1.close; adoquery1.sql.text := sql; adoquery1.execsql; 

but one of my friends just showed me another way that looks cleaner, like this:

 sql := 'SELECT * FROM table_foo'; adoquery1.close; adoquery1.sql.text := sql; adoquery1.open; adoquery1.insert; adoquery1.fieldbyname('field1').asstring := quotedstr('value1'); adoquery1.fieldbyname('field2').asstring := quotedstr('value2'); adoquery1.fieldbyname('field3').asstring := quotedstr('value3'); adoquery1.post; 

which of the two methods is better (faster, easier to read / debug)? especially when the data in table_foo large or more fields are filled.

Answer 1

If you use INSERT INTO , use parameters (for readability, avoid SQL injection, SQL caching), for example:

 adoquery1.sql.text := 'INSERT INTO table_foo (field1, field2) values (:field1, :field2)'; adoquery1.Parameters.ParamByName('field1').Value := value1; adoquery1.Parameters.ParamByName('field2').Value := value2; 

I prefer the second way (with a little tweak, which I will explain). Since you are inserting a single record, the setup is to select an empty set of entries ie:

 SELECT * FROM table_foo where 1=0 

Thus, you do not select all records from the table. Also, you do not need to use QuotedStr when assigning ie values:

 adoquery1.FieldByName('field1').AsString := 'value1'; 

The main reason I use this method is that it is easy to read and maintain. I do not need to worry about pure SQL queries. I do not need to deal with the parameters that were once required to indicate the data type for the parameters (for example, Parameters.ParamByName('field1').DataType := ftInteger ). No need for ParseSQL . I just use DataSet As(Type) , for example.

 FieldByName('field1').AsBoolean := True; 

I would also prefer to use this method if I need to insert multiple records into one transaction. The disadvantage of the second method is a short trip to the SQL server through SELECT FROM .

Another option would be to create a SQL stored procedure, pass your values ​​to the SP, and write all the SQL logic inside the SP.

Answer 2

The second approach requires more local resources from the data set, since it will store the memory of the original result set, and then use this memory to decide which records should be sent to the server using which SQL statement. This approach also requires a live connection to the server and a bi-directional local cursor set in the dataset. TADODataset does everything for you. It works more for you less, but it will consume more from the system. The decision, in my opinion, depends on which resource is more important, your time or computer resources.

Persons, I prefer to use TClientDataset (CDS). This will allow you to have a dataset in memory and using the TDatasetProvider.BeforeUpdateRecord event in the corresponding TDatasetProvider you will get the best of both worlds: absolute control over which sentence will be sent to the server, and a flexible and batch dataset that works very well in graphical interfaces .

In addition (this is the most important thing for me), with CDS you can isolate the specifics of your DBMS from the main logic of your application, because this logic will work with a DB-independent data set. If you need to switch from ADO to, say, DBX, your main code will not suffer because it is written in CDS.