客户端如何通过咏南中间件调用存储过程和数据分页查询和文件传输的演示
演示使用MSSQL 2000的存储过程，其它类型的数据库的存储过程的语法是有所不同的。
1）MSSQL2000通用数据分页存储过程，仅作演示用，在此不探讨性能问题
ALTER proc [dbo].[sp_page]
@RecordCount int output, --查询到的记录总数，输出参数
@QueryStr nvarchar(1000)='table1', --表名、视图名、查询语句
@PageSize int=20, --每页的大小(行数)
@PageCurrent int=0, --要显示的页 从0开始
@FdShow nvarchar (2000)='*', --要显示的字段列表
@IdentityStr nvarchar (100)='id', --主键
@WhereStr nvarchar (2000)='1=1',
@FdOrder nvarchar(100)='desc' --排序 只能取desc或者asc
as
set nocount on
declare
@sql nvarchar(2000)
if @WhereStr = '' begin
set @WhereStr = '1=1'
end
declare @tsql nvarchar(200)
set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr
exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output
if @PageCurrent = 0 begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder
end
else begin
if upper(@FdOrder) = 'DESC' begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc'
end
else begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc'
end
end
--print @sql
execute(@sql)
2）客户端调用代码
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DB, DBClient, StdCtrls, Grids, DBGrids;
type
TForm1 = class(TForm)
DBGrid1: TDBGrid;
btnQry: TButton;
DataSource1: TDataSource;
ClientDataSet1: TClientDataSet;
btnSave: TButton;
downFile: TButton;
uploadFile: TButton;
Button1: TButton;
Button2: TButton;
Button3: TButton;
procedure btnQryClick(Sender: TObject);
procedure btnSaveClick(Sender: TObject);
procedure downFileClick(Sender: TObject);
procedure uploadFileClick(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
FPageCurrent: Integer;
function qryPage(tableName, fieldList, keyField, whereStr, orderType: string; pageSize, pageCurrent: Integer): OleVariant;
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
uses
untDllMethods;
{$R *.dfm}
procedure TForm1.btnQryClick(Sender: TObject);
begin
ClientDataSet1.Data := untDllMethods.QuerySql('0', 'select c1,c2,字段3 as c3 from t1');
end;
procedure TForm1.btnSaveClick(Sender: TObject);
begin
untDllMethods.SaveData('0', 't1', ClientDataSet1.Delta);
end;
procedure TForm1.downFileClick(Sender: TObject);
var
v: OleVariant;
LStream: TMemoryStream;
begin
v := untDllMethods.DownLoadFile2('down.txt');
if v = null then
Exit;
LStream := TMemoryStream.Create;
untDllMethods.VariantToStream(v, LStream);
LStream.SaveToFile('c:down.txt');
LStream.Free;
ShowMessage('成功下载文件 c:down.txt');
end;
procedure TForm1.uploadFileClick(Sender: TObject);
var
LStream: TFileStream;
v: OleVariant;
begin
if not FileExists('c:down.txt') then
begin
ShowMessage('请准备一个要上传的文件 c:down.txt');
Exit;
end;
LStream := TFileStream.Create('c:down.txt', fmOpenRead);
untDllMethods.StreamToVariant(LStream, v);
if untDllMethods.UploadFile2(v, 'd:down.txt') then
ShowMessage('上传文件成功')
else
ShowMessage('上传文件失败');
LStream.Free;
end;
function TForm1.qryPage(tableName, fieldList, keyField, whereStr, orderType: string; pageSize, pageCurrent: Integer): OleVariant;
var
p: TParams;
begin
p := TParams.Create(nil);
p.Clear;
p.CreateParam(ftInteger, '@RecordCount', ptOutput); // 查询到的记录总数，输出参数
p.CreateParam(ftString, '@QueryStr', ptInput).AsString := tableName; // 表名、视图名、查询语句
p.CreateParam(ftInteger, '@PageSize', ptInput).AsInteger := pageSize; // 每页的大小(行数)
p.CreateParam(ftInteger, '@PageCurrent', ptInput).AsInteger := PageCurrent; // 要显示的页 从0开始
p.CreateParam(ftString, '@FdShow', ptInput).AsString := fieldList; // 要显示的字段列表
p.CreateParam(ftString, '@IdentityStr', ptInput).AsString := keyField; // 主键
p.CreateParam(ftString, '@WhereStr', ptInput).AsString := whereStr; // where条件
p.CreateParam(ftString, '@FdOrder', ptInput).AsString := orderType; // 排序 只能取desc或者asc
result := untDllMethods.spOpenOut('0', 'sp_page', PackageParams(p));
p.Free;
end;
procedure TForm1.Button3Click(Sender: TObject);
var
r: OleVariant;
p: TParams;
LRecordCount: Integer;
begin
FPageCurrent := 0;
r := Self.qryPage('t1', '*', 'c1', '1=1', 'asc', 10, FPageCurrent);
ClientDataSet1.Data := r[0];
p := tparams.Create(nil);
UnpackParams(r[1], p);
LRecordCount := p.ParamByName('@RecordCount').AsInteger;
p.Free;
Caption := '记录总数：' + IntToStr(LRecordCount);
end;
procedure TForm1.Button1Click(Sender: TObject);
var
r: OleVariant;
p: TParams;
LRecordCount: Integer;
begin
if FPageCurrent = 0 then
begin
ShowMessage('当前是第一页');
Exit;
end;
Dec(FPageCurrent);
r := Self.qryPage('t1', '*', 'c1', '1=1', 'asc', 10, FPageCurrent);
ClientDataSet1.Data := r[0];
p := tparams.Create(nil);
UnpackParams(r[1], p);
LRecordCount := p.ParamByName('@RecordCount').AsInteger;
p.Free;
Caption := '记录总数：' + IntToStr(LRecordCount);
end;
procedure TForm1.Button2Click(Sender: TObject);
var
r: OleVariant;
p: TParams;
LRecordCount: Integer;
begin
inc(FPageCurrent);
r := Self.qryPage('t1', '*', 'c1', '1=1', 'asc', 10, FPageCurrent);
ClientDataSet1.Data := r[0];
p := tparams.Create(nil);
UnpackParams(r[1], p);
LRecordCount := p.ParamByName('@RecordCount').AsInteger;
p.Free;
Caption := '记录总数：' + IntToStr(LRecordCount);
end;
end.