在sql server 2008中使用dephi插入数据的最佳方式 [英] best way to insert data using dephi in sql server 2008
问题描述
我一直使用这样的脚本将数据插入到delphi 7中的表中
I've always used such 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 friend just showed me another way that looks cleaner, like so:
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;
两种方法中哪一种更好(更快,更容易阅读/调试)?特别是当 table_foo
中的数据很大或者有更多的字段要填充。
which of the two methods are better (faster, easier to read/debug)? especially when the data in table_foo
is large or there are a lot more fields to fill.
推荐答案
如果您使用 INSERT INTO
语句使用参数(由于可读性,避免SQL注入,SQL缓存),例如:
If you do use INSERT INTO
statements use parameters (for reasons of readability, avoid SQL injection, SQL caching) e.g.:
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 small tweak which I'll explain). Since you are inserting one record, the tweak is to select an empty recordset i.e.:
SELECT * FROM table_foo where 1=0
这样你就不要选择 记录从表中。
在分配值时也不需要使用 QuotedStr
This way you don't select all records form the table.
Also no need to use QuotedStr
when assigning the values i.e.:
adoquery1.FieldByName('field1').AsString := 'value1';
我使用这种方法的主要原因是它易于阅读和维护。
我不需要用纯SQL查询来打扰自己。我不需要处理有时需要指定参数的数据类型的参数(例如 Parameters.ParamByName('field1')。DataType:= ftInteger
)。不需要 ParseSQL
。
我只使用DataSet As(Type)
The main reason I use this method is because it's easy to read and to maintain.
I don't need to bother myself with pure SQL queries. I don't need to deal with Parameters which sometime required to specify the data type for the parameters (e.g. Parameters.ParamByName('field1').DataType := ftInteger
). No need to ParseSQL
.
I simply use the DataSet As(Type)
e.g.
FieldByName('field1').AsBoolean := True;
如果我需要在单个事务中插入多个记录,我还希望使用此方法。
第二种方法的缺点是通过 SELECT FROM
短时间到SQL服务器。
I would also prefer to use this method if I need to insert multiple records in a single transaction.
The downside for the second method is the short trip to the SQL server via SELECT FROM
.
另一个选择是创建SQL存储过程,将值传递给SP,并将所有SQL逻辑写入SP。
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.
这篇关于在sql server 2008中使用dephi插入数据的最佳方式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!