应用场景简介:
Delphi XE4(含第三方插件DBGRIDEH和自定义消息框MsgBox)+ORACLE 11G R2环境下,登录窗口点击〖登录〗,隐藏FormLogin,弹出FormMianOperation,在主操作窗口,有3个选项卡,每个选项卡下面都有1个DBGRIDEH和各自的按钮事件,至少都有查询和更改操作,为了达到松藕效果,去掉登录的的TADOConnection,TADOQuery组件,简单封装ADO相关代码,但是FormMianOperation必须存在TADOQuery组件,因为为了防止窜数据,我把每一个DBGRIDEH对应一个TADOQuery组件,所以必须有实体指向。(细心的话你会发现,如果需要存在多个ADOConnection,也必须存在TADOConnection组件,因为目前我设计的是一个TADOConnection对应多个TADOQuery组件操作数据库,所以会这样操作)
说明:
1、使用松藕原则,LoginForm不需要TADOConnection,TADOQuery组件;
2、登陆框点击〖登录〗按钮,调用SetADOConnection判断无异常,调用GetADOConnection判断是否连接(主要用在重复点击〖登录〗时候处理)
3、ADOConnection: TADOConnection必须做全局申明
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, MyMsgBox, cxGraphics, cxControls, Data.DB, Data.Win.ADODB;
var
ADOConnection: TADOConnection;
function SetADOConnection(sDbName, sUserName, sPassWord: string): Boolean;
function GetADOConnection: TADOConnection;
function PrepareSql(ADOQuery: TADOQuery; bQuery: Boolean; sMySql: string): Boolean;
implementation
{-------------------------------------------------------------------------------
作者: 曾聪
日期: 2013.09.11
过程名: SetADOConnection
功能: 封装SQL连接ADO代码,主要含传入的连接数据库的参数
参数: sDbName, sUserName, sPassWord: string
返回值: Boolean
说明: 使用try......finally作为预留扩展内容
-------------------------------------------------------------------------------}
function SetADOConnection(sDbName, sUserName, sPassWord: string): Boolean;
var
bFlag: Boolean;
begin
bFlag := True;
try
try
if ADOConnection <> nil then
ADOConnection.Free;
ADOConnection := TADOConnection.Create(nil);
ADOConnection.ConnectionString := 'Provider=MSDAORA.1;User ID=' + sUserName + ';password=' + sPassWord + ';Data Source=' + sDbName + ';Persist Security Info=False;';
ADOConnection.LoginPrompt := False; //不弹出ADOConnection组件连接数据库的登录窗口
ADOConnection.Connected := True;
except
on E: Exception do
begin
bFlag := False; //可以把错误提示放在调用时判断,也可以在封装函数判断
ADOConnection.Connected := False;
if Pos(UpperCase('ORA-01017'), UpperCase(e.Message)) <> 0 then
MsgBox('对不起,您填入的服务器名称/用户名/密码错误,连接数据库失败!', '错误', MB_OK + MB_USERICON, 2, MB_ICONHAND)
else if Pos(UpperCase('ORA-12154'), UpperCase(e.Message)) <> 0 then
MsgBox('对不起,您填入的服务器名称错误,连接数据库失败!', '错误', MB_OK + MB_USERICON, 2, MB_ICONHAND)
else if Pos(UpperCase('ORA-12541'), UpperCase(e.Message)) <> 0 then
MsgBox('对不起,未检测到监听程序,请确认操作系统的〖服务组件〗中的监听服务已经启动,连接数据库失败!', '错误', MB_OK + MB_USERICON, 2, MB_ICONHAND)
else
MsgBox(PWideChar(WideString(e.Message)), '错误', MB_OK + MB_USERICON, 2, MB_ICONHAND); //显示详细错误信息
end;
end;
finally
Result := bFlag;
end;
end;
{-------------------------------------------------------------------------------
作者: 曾聪
日期: 2013.09.11
过程名: GetADOConnection
功能: 封装SQL连接ADO代码,判断是否已经连接,如果断开尝试连接
参数: 无
返回值: TADOConnection
说明: 主要在执行SQL前判断使用
-------------------------------------------------------------------------------}
function GetADOConnection: TADOConnection;
begin
if ADOConnection = nil then
begin
MsgBox('尚未创建数据库连接', '错误', MB_OK + MB_USERICON, 2, MB_ICONHAND);
Exit;
end;
try
if not ADOConnection.Connected then
ADOConnection.Connected := True;
except
on E: Exception do
MsgBox(PWideChar(WideString('调用GetADOConnection函数发生未知错误: ' + e.Message)), '错误', MB_OK + MB_USERICON, 2, MB_ICONHAND);
end;
Result := ADOConnection;
end;
{-------------------------------------------------------------------------------
作者: 曾聪
日期: 2013.09.11
过程名: PrepareSql
功能: 封装SQL语句执行函数
参数: adoQry: TADOQuery; bQuery: Boolean; sMySql: string
返回值: Boolean
说明: 使用try......finally作为预留扩展内容
-------------------------------------------------------------------------------}
function PrepareSql(ADOQuery: TADOQuery; bQuery: Boolean; sMySql: string): Boolean;
var
bFlag: Boolean;
begin
bFlag := True;
with ADOQuery do
try
try
Connection := GetAdoConnection;
Close;
SQL.Clear;
SQL.Add(sMySql);
if bQuery = True then
try
Open;
except
on E: Exception do
begin
bFlag := False;
MsgBox(PWideChar(WideString('对数据库执行查询操作发生错误:' + e.Message)), '错误', MB_OK + MB_USERICON, 2, MB_ICONEXCLAMATION);
end;
end
else
try
Connection.BeginTrans;
ExecSQL;
Connection.CommitTrans;
except on E: Exception do
begin
Connection.RollbackTrans;
bFlag := False;
MsgBox(PWideChar(WideString('对数据库执行更改操作发生错误:' + e.Message + ', 为了数据安全,已经回滚到操作更改前状态!')), '错误', MB_OK + MB_USERICON, 2, MB_ICONEXCLAMATION);
end;
end;
except
on E: Exception do
begin
bFlag := False;
MsgBox(PWideChar(WideString('执行SQL语句发生错误:' + e.Message)), '错误', MB_OK + MB_USERICON, 2, MB_ICONEXCLAMATION);
end;
end;
finally
Result := bFlag;
end;
end;
end.