从delphi 6 开始,datasnap 作为delphi 自带的多层框架,一直更新到最新的delphi 10.3 。同时逐步增加了很多新的功能
,比如支持REST 调用,支持 IIS ,apache 等服务器。今天简单介绍一下如何在datasnap 中使用unidac 访问数据库。
首先,按照向导生成一个标准的datasnap 服务器。
、
点完成就自动生成对应的工程文件
其中
- DSServer — 用来控制数据c传输和服务类;
- DSServerClass — 用来定义可供客户端调用的服务器类方法;
- DSHTTPService — 用来使用HTTP 协议访问服务器;
- DSAuthenticationManager —控制调用服务器方法的用户认证及授权;
- DSTCPServerTransport — 用来使用TCP/IP访问服务器。
我们可以通过代码做一些对应的设置
主界面代码:
procedure Tmainf.btStartClick(Sender: TObject); begin if not ServerContainer1.DSServer1.Started then begin ServerContainer1.DSTCPServerTransport1.Port := StrToInt(edTCPIPPort.Text); ServerContainer1.DSHTTPService1.DSPort := StrToInt(edHTTPPort.Text); ServerContainer1.DSServer1.Start; mStatus.Lines.Add( Formatdatetime('yyyy-mm-dd hh:nn:ss',now) + 'TCP/IP 启动侦听'); ServerContainer1.DSHTTPService1.Start; mStatus.Lines.Add(Formatdatetime('yyyy-mm-dd hh:nn:ss',now) +'HTTP 启动侦听'); btStart.Caption := '停止'; end else begin ServerContainer1.DSHTTPService1.Stop; mStatus.Lines.Add(Formatdatetime('yyyy-mm-dd hh:nn:ss',now) + 'TCP/IP 停止侦听'); ServerContainer1.DSServer1.Stop; mStatus.Lines.Add(Formatdatetime('yyyy-mm-dd hh:nn:ss',now) +'HTTP 停止侦听'); btStart.Caption := '启动'; end; end;
procedure TServerContainer1.DSServer1Connect( DSConnectEventObject: TDSConnectEventObject); var ci: TDBXClientInfo; begin ci := DSConnectEventObject.ChannelInfo.ClientInfo; mainf.mStatus.Lines.Add(Format('客户端以 %s 方式连接 IP: %s, 端口: %s', [ci.Protocol, ci.IpAddress, ci.ClientPort])); end; procedure TServerContainer1.DSServer1Disconnect( DSConnectEventObject: TDSConnectEventObject); var ci: TDBXClientInfo; begin ci := DSConnectEventObject.ChannelInfo.ClientInfo; mainf.mStatus.Lines.Add(Format('客户端以 %s 方式断开 IP: %s, Port: %s', [ci.Protocol, ci.IpAddress, ci.ClientPort])); end;
以上datasnap 服务器端基本上就设置好了,现在我们要增加数据库访问功能。数据库访问控件毫无疑问,使用unidac.
本次还是以postgresql 为例。
启动postgresql 服务器
(这个服务器在哪里下载?请猛戳 www.haosql.com).
运行数据库脚本
CREATE TABLE dept ( deptno serial NOT NULL, dname character varying(14), loc character varying(13), cnt integer, CONSTRAINT "PK_MASTER" PRIMARY KEY (deptno) ); CREATE TABLE emp ( empno serial NOT NULL, ename character varying(10), job character varying(9), mgr integer, hiredate timestamp without time zone, sal real, comm real, deptno integer NOT NULL, CONSTRAINT "PK_DETAIL" PRIMARY KEY (deptno, empno) ); CREATE TABLE users ( id numeric NOT NULL, username character(20) NOT NULL, passwd character(20) NOT NULL, CONSTRAINT pk_users_id PRIMARY KEY (id), CONSTRAINT uq_users_name UNIQUE (username) ); CREATE OR REPLACE FUNCTION sp_check_user(a_username character DEFAULT ''::bpchar, a_passwd character DEFAULT ''::bpchar) RETURNS boolean AS $BODY$BEGIN IF EXISTS (SELECT 1 FROM users WHERE username = a_username AND passwd = a_passwd) THEN RETURN True; ELSE RETURN False; END IF; END; $BODY$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION sp_dept_modify(a_deptno numeric DEFAULT (-1), a_dname character DEFAULT ''::bpchar, a_loc character DEFAULT ''::bpchar, a_op_type numeric DEFAULT 0) RETURNS void AS $BODY$begin case a_op_type when 0 then delete from dept where deptno = a_deptno; when 1 then insert into dept (deptno, dname, loc) values (a_deptno, a_dname, a_loc); else update dept set dname = a_dname, loc = a_loc where deptno = a_deptno; end case; end;$BODY$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION sp_emp_modify(a_empno numeric DEFAULT (-1), a_ename character DEFAULT ''::bpchar, a_job character DEFAULT ''::bpchar, a_mgr numeric DEFAULT 0, a_hiredate timestamp without time zone DEFAULT (now())::timestamp without time zone, a_sal numeric DEFAULT 0, a_comm numeric DEFAULT 0, a_deptno numeric DEFAULT (-1), a_op_type numeric DEFAULT 0) RETURNS void AS $BODY$begin case a_op_type when 0 then delete from emp where empno = a_empno; when 1 then insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (a_empno, a_ename, a_job, a_mgr, a_hiredate, a_sal, a_comm, a_deptno); else update emp set ename = a_ename, job = a_job, mgr = a_mgr, hiredate = a_hiredate, sal = a_sal, comm = a_comm where empno = a_empno; end case; end;$BODY$ LANGUAGE plpgsql;
在ServerMethodsUnit1 里面放置对应的控件。
设置数据库连接
设置对应的sql 参数
添加对应的 服务器端代码
function GetDept: TDataSet; function GetEmp(DeptNo: integer = -1): TDataSet; function CheckUser(username: string = ''; passwd: string = ''):boolean; procedure DeptModify(DeptNo: integer = -1; DName: string = ''; Loc: string = ''; op_type: integer = 0); procedure EmpModify(Empno: integer = -1; EName: string = ''; Job: string = ''; Mgr: integer = 0; HireDate: TDateTime = 0; Sal: double = 0; Comm: double = 0; Deptno:integer = -1; op_type: integer = 0);
如图
procedure TServerMethods1.DeptModify(DeptNo: integer; DName, Loc: string; op_type: integer); begin usp_dept_modify.ParamByName('a_deptno').AsInteger := DeptNo; usp_dept_modify.ParamByName('a_dname').AsString := DName; usp_dept_modify.ParamByName('a_loc').AsString := Loc; usp_dept_modify.ParamByName('a_op_type').AsInteger := op_type; usp_dept_modify.Execute; end; procedure TServerMethods1.EmpModify(Empno: integer; EName, Job: string; Mgr: integer; HireDate: TDateTime; Sal, Comm: double; Deptno, op_type: integer); begin usp_emp_modify.ParamByName('a_empno').AsInteger := Empno; usp_emp_modify.ParamByName('a_ename').AsString := EName; usp_emp_modify.ParamByName('a_job').AsString := Job; usp_emp_modify.ParamByName('a_mgr').AsInteger := Mgr; usp_emp_modify.ParamByName('a_hiredate').AsDateTime := HireDate; usp_emp_modify.ParamByName('a_sal').AsFloat := Sal; usp_emp_modify.ParamByName('a_Comm').AsFloat := Comm; usp_emp_modify.ParamByName('a_deptno').AsInteger := Deptno; usp_emp_modify.ParamByName('a_op_type').AsInteger := op_type; usp_emp_modify.Execute; end; function TServerMethods1.GetDept: TDataSet; begin uqDept.Close; uqDept.Open; Result := uqDept; end; function TServerMethods1.GetEmp(DeptNo: integer): TDataSet; begin uqEmp.Close; uqEmp.ParamByName('DEPTNO').AsInteger := DeptNo; uqEmp.Open; Result := uqEmp; end; function TServerMethods1.EchoString(Value: string): string; begin Result := Value; end; function TServerMethods1.ReverseString(Value: string): string; begin Result := System.StrUtils.ReverseString(Value); end;
服务器端就OK 了。
运行起来。
在浏览器里面我们也可以直接访问它的REST 服务
剩下就是客户端的编写了,我们放到下一篇。
先来个客户端效果照。