在Delphi中使用原生ADO控制数据库

我发现很多朋友在开发数据库时都使用 Delphi 自带的 ADO 组件 或 Diamond ADO，其实在 Delphi 中使用原生 ADO 接口也是十分方便和有效的。我使用原生 ADO 开发项目已有很长一段时间，也回答过一些朋友类似的问题，现在把自己的一点心得与大家分享，班门弄斧，只是希望能对大家有所帮助。当然，这帖子也是原生的，不是转贴的。

一、优点

1、大家知道 Delphi 对 ADO 接口进行了一番包装后形成了 ADOExpress，我想 Borland 的主要目的还是想与自己的数据敏感控件相连。然而事实上数据敏感控件并不是那么耀眼，如果你希望编出来的程序稍微有点水准的话就别用那玩意；如果你很少使用数据敏感控件，那么 ADOExpress 基本上失去了其应有的作用，无数冗余的属性、虚方法，不管你用不用得到一股脑给你编译进去，也会使你的程序再大上 200K；效率么，不说了。

2、MSDN 和 VB 中的例子你可以搬过来就用。

3、关于代码重用：我给大家的例子都是以函数或过程形式，重用性不好么？

4、别说帖子太长，那你看看 DB.pas, ADODB.pas 多长？

二、基本储备

1、一些必须的单元

uses

Variants, ComObj;

2、一些基本常数（其它查 ADODB2000.pas）：

const

adOpenDynamic = $00000002;

adOpenStatic = $00000003;

adLockOptimistic = $00000003;

adLockBatchOptimistic = $00000004;

adStateClosed = $00000000;

adStateOpen = $00000001;

adStateConnecting = $00000002;

adStateExecuting = $00000004;

adStateFetching = $00000008;

adUseServer = $00000002;

adUseClient = $00000003;

adModeReadWrite = $00000003;

adXactCursorStability = $00001000;

adCmdText = $00000001;

adCmdTable = $00000002;

adCmdStoredProc = $00000004;

adCmdFile = $00000100;

adAffectCurrent = $00000001;

adAffectGroup = $00000002;

adAffectAll = $00000003;

adAffectAllChapters = $00000004;

3、一些基本函数和过程

//创建 Connection 对象

function CreateConnection: OleVariant;

//释放 Connection 对象；cnn 为 Connection 对象

procedure FreeConnection(var cnn: OleVariant);

//创建 Recordset 对象

function CreateRecordset: OleVariant;

//释放 Recordset 对象；rst 为 Recordset 对象

procedure FreeRecordset(var rst: OleVariant);

//创建 Command 对象

function CreateCommand: OleVariant;

//释放 Command 对象；cmd 为 Command 对象

procedure FreeCommand(var cmd: OleVariant);

//用 Connection 连接到 SQLServer 数据库；cnn 为 Connection 对象，db 数据库名，host 主机名，usr 用户名，pwd 密码

function ConnectToDB(cnn: OleVariant; const db, host, usr, pwd: string): Boolean;

//执行 SQL 语句，有返回行，无事务处理；cnn 为 Connection 对象，rst 为 Recordset 对象，sql 为 SQL 语句（可以是存储过程）

function ExecSQL(cnn, rst: OleVariant; const sql: string): Boolean;

//执行 SQL 语句，无返回行，有事务处理；cnn 为 Connection 对象，cmd 为 Command 对象，sql 为 SQL 语句（可以是存储过程）

function ExecSQLA(cnn, cmd: OleVariant; const sql: string): Boolean;

function CreateConnection: OleVariant;

begin

try

Result := CreateOleObject('ADODB.Connection');

Result.CursorLocation := adUseServer;

Result.IsolationLevel := adXactCursorStability;

Result.Mode := adModeReadWrite;

Result.Provider := 'SQLOLEDB.1';

except

if not VarIsEmpty(Result) then Result := Unassigned;

end;

end;

procedure FreeConnection(var cnn: OleVariant);

begin

if not VarIsEmpty(cnn) then

begin

if cnn.State <> adStateClosed then cnn.Close;

cnn := Unassigned;

end;

end;

function CreateRecordset: OleVariant;

begin

try

Result := CreateOleObject('ADODB.Recordset');

Result.CacheSize := 1000;

Result.CursorType := adOpenStatic;

Result.CursorLocation := adUseServer;

Result.LockType := adLockOptimistic;

except

if not VarIsEmpty(Result) then Result := Unassigned;

end;

end;

procedure FreeRecordset(var rst: OleVariant);

begin

FreeConnection(rst);

end;

function CreateCommand: OleVariant;

begin

try

Result := CreateOleObject('ADODB.Command');

Result.CommandType := adCmdText;

Result.CommandTimeout := 5;

except

if not VarIsEmpty(Result) then Result := Unassigned;

end;

end;

procedure FreeCommand(var cmd: OleVariant);

begin

if not VarIsEmpty(cmd) then cmd := Unassigned;

end;

function ConnectToDB(cnn: OleVariant; const db, host, usr, pwd: string): Boolean;

begin

Result := not VarIsEmpty(cnn);

if Result then

begin

if cnn.State <> adStateClosed then cnn.Close;

cnn.ConnectionString :=

'Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=' +

db + ';Data Source=' + host + ';Connect Timeout=5;' +

'Use Procedure for Prepare=1';

try

cnn.Open(cnn.ConnectionString, usr, pwd, -1);

except

Result := False;

end;

end;

end;

function ExecSQL(cnn, rst: OleVariant; const sql: string): Boolean;

begin

Result := not (VarIsEmpty(cnn) or VarIsEmpty(rst)) and (cnn.State = adStateOpen);

if Result then

begin

if rst.State <> adStateClosed then rst.Close;

try

rst.Open(sql, cnn, adOpenStatic, adLockOptimistic, adCmdText);

except

Result := False;

end;

end;

end;

function ExecSQLA(cnn, cmd: OleVariant; const sql: string): Boolean;

begin

Result := not (VarIsEmpty(cnn) or VarIsEmpty(cmd)) and (cnn.State = adStateOpen);

if Result then

begin

cnn.BeginTrans;

try

cmd.ActiveConnection := cnn;

cmd.CommandText := sql;

cmd.Prepared := True;

cmd.Execute;

cnn.CommitTrans;

except

cnn.RollbackTrans;

Result := False;

end;

end;

end;

三、访问数据

1、最前 rst.MoveFirst;

2、最后 rst.MoveLast;

3、向前 rst.MovePrevious;

4、向后 rst.MoveNext;

5、取当前记录 rst.Fields[0].Value 或 rst.Fields['字段名'].Value;

6、修改当前记录 rst.Update(rst.Fields[0].Name, 某值);

7、取消修改 rst.CancelUpdate;

8、删除当前记录 rst.Delete(adAffectCurrent);

9、删除所有记录 rst.Delete(adAffectAll);

10、追加记录

rst.AddNew;

rst.Fields[0].Value := 值1;

rst.Fields[1].Value := 值2;

rst.Update;

11、刷新 rst.Refresh;

12、记录数 rst.RecordCount

15、其它方法和属性查 MSDN 或 ADO 的帮助;

四、一些例子

//变量声明

var

cnn, rst, cmd: OleVariant;

//创建对象

procedure TForm1.FormCreate(Sender: TObject);

begin

cnn := CreateConnection;

rst := CreateRecordset;

cmd := CreateCommand;

end;

//释放对象

procedure TForm1.FormDestroy(Sender: TObject);

begin

FreeCommand(cmd);

FreeRecordset(rst);

FreeConnection(cnn);

end;

//连接数据库

procedure TForm1.Button1Click(Sender: TObject);

begin

if ConnectToDB(cnn, 'mydb', '127.0.0.1', 'sa', 'ok') then

Caption := '连接成功'

else Caption := '连接失败';

end;

//取记录

procedure TForm1.Button2Click(Sender: TObject);

begin

if ExecSQL(cnn, rst, 'select * from 表a') then

Caption := VarToStr(rst.Fields['字段a'].Value);

end;

五、原生 ADO 与 Delphi ADOExpress 组件的对应关系

1、Connection <=> ADOConnection.ConnectionObject;

2、Recordset <=> ADODataSet.Recordset;

3、Command <=> ADOCommand.CommandObject;

4、? <=> ADOQuery，因为 ADOQuery 根本就不是原生 ADO 对象

5、ExecSQL <=> ADODataSet.Open;

6、ExecSQLA <=> ADOCommand.Execute;

7、有了上面几个其它的就不多说了

六、与数据库结构有关的一些函数

1、动态改变字段名称

uses ComObj;

//Access

//TableName: 表名; OldColName: 原字段名; NewColName: 新字段名;

procedure RenameField(const TableName, OldColName, NewColName: string);

var

DB, Col: OleVariant;

begin

DB := CreateOleObject('ADOX.Catalog');

DB.ActiveConnection := ADOConnection1.ConnectionObject;

Col := CreateOleObject('ADOX.Column');

Col := DB.Tables[TableName].Columns[OldColName];

Col.Name := NewColName;

end;

//SQLServer

procedure RenameField(const TableName, OldColName, NewColName: string);

begin

with ADOCommand1 do

begin

CommandText := 'EXEC sp_rename ''' + TableName + '.' + OldColName +

''',''' + NewColName + ''',''COLUMN'';';

Excute;

end;

end;

2、取得 Access 库中的表结构

type

TTableDef = record

Name,

DateCreated,

LastUpdated,

Description: string;

end;

TTableDefs = array of TTableDef;

procedure GetTableDefs(const DBName: string; out TableDefs: TTableDefs);

var

DBEngine, DB: OleVariant;

I: Longint;

begin

try

DBEngine := CreateOleObject('DAO.DBEngine.36');

DB := DBEngine.OpenDatabase(DBName);

SetLength(TableDefs, Longint(DB.TableDefs.Count));

for I := Low(TableDefs) to High(TableDefs) do

begin

TableDefs[I].Name := DB.TableDefs[I].Name;

TableDefs[I].DateCreated := DB.TableDefs[I].DateCreated;

TableDefs[I].LastUpdated := DB.TableDefs[I].LastUpdated;

try

TableDefs[I].Description := DB.TableDefs[I].Properties['Description'].Value;

except

TableDefs[I].Description := '';

end;

end;

finally

DB := Unassigned;

DBEngine := Unassigned;

end;

end;

3、取得 Access 表中的字段结构

type

TFieldDef = record

Name: string;

Types,

Size: Longint;

Description: string;

end;

TFieldDefs = array of TFieldDef;

procedure GetFieldDefs(const DBName, TableName: string; out FieldDefs: TFieldDefs);

var

DBEngine, DB: OleVariant;

I: Longint;

begin

try

DBEngine := CreateOleObject('DAO.DBEngine.36');

DB := DBEngine.OpenDatabase(DBName);

SetLength(FieldDefs, Longint(DB.TableDefs[TableName].Fields.Count));

for I := Low(FieldDefs) to High(FieldDefs) do

begin

FieldDefs[I].Name := DB.TableDefs[TableName].Fields[I].Name;

FieldDefs[I].Types := DB.TableDefs[TableName].Fields[I].Type;

FieldDefs[I].Size := DB.TableDefs[TableName].Fields[I].Size;

try

FieldDefs[I].Description := DB.TableDefs[TableName].Fields[I].Properties['Description'].Value;

except

FieldDefs[I].Description := '';

end;

end;

finally

DB := Unassigned;

DBEngine := Unassigned;

end;

end;