You who had used MS SQL Server should already know that this database engine supports stored procedures. Its stored procedures can output recordset. In fact, a stored procedure could output multiple recordset in a single call. This tutorial about MS SQL Server stored procedure should give you bit more details about this.
The question is, "how to access specific recordsets from our Delphi applications?"
I believe most of Delphi coder would use TADOStoredProc to execute an SQL Server stored procedure. It's easy to use. Now, do you know that it has NextRecordSet method? See this page for more details about this method. I would quote the most interesting part from that page below.
Call NextRecordset in situations where multiple recordsets are returned to provide access to the second (and subsequent) recordsets. The ADO Recordset object returned by NextRecordset must be assigned to the Recordset property of an ADO dataset component, such as TADODataSet, to be accessible.
So we can call NextRecordSet and assign the returned _Recordset instance to Recordset property of a TADODataset.
I decided to use the database (including the sample data and its stored procedure) from the MS SQL Server stored procedure tutorial. So make sure you read that tutorial and download the attached DDL and sample data of the database. Using the DDL and sample data script, setup a database in your SQL Server environment.
- Create a new Delphi (VCL Form) Application. Leave the project name and its main form as they were. But feel free to change them into more meaningful names if you want to.
- Drop four TDBGrid-s into the main form. For each TDBGrid add a TLabel. Adjust and arrange them like shown in the next picture.
- Drop a TADOConnection to the main form. It will be named ADOConnection1. Leave the name as is. Setup its connection string to properly open your SQL Server database.
- Drop a TADOStoredProc to the main form. It will automatically named ADOStoredProc1. Set its Connection property to the ADOConnection1 which we setup earlier. Set its ProcedureName property to (without the double quotes): "ReturnMultiResultsets".
- Drop a TDataSource next to the TADOStoredProc. It will be automatically named DataSource1. Set its DataSet property to ADOStoredProc1.
- Set DataSource property of DBGrid1 (the first TDBGrid) to DataSource1.
- Drop a TADODataSet to the main form. It will automatically named ADODataSet1. Arrange its place to be inside DBGrid2 (the second TDBGrid). This arrangement has no real effect, I just do this arrangement to easily show which dataset and datasource affected a DBGrid.
- Drop a TDataSource next to ADODataSet1. It will automatically named DataSource2. Set its DataSet property to ADODataSet1.
- Set DataSource property of DBGrid2 (the second TDBGrid) to DataSource2.
- Drop another TADODataSet to the main form. It will automatically named ADODataSet2. Arrange its place to be inside DBGrid3 (the third TDBGrid).
- Drop another TDataSource next to ADODataSet2. It will automaticallny named DataSource3. Set its DataSet property to ADODataSet2.
- Set DataSource property of DBGrid3 (the third TDBGrid) to DataSource3.
- Drop another TADODataSet to the main form. It will automatically named ADODataSet3. Arrange its place to be inside DBGrid4 (the last TDBGrid).
- Drop another TDataSource next to ADODataSet3. It will automatically named DataSource4. Set its DataSet property to ADODataSet3.
- Set DataSource property of DBGrid4 (the last TDBGrid) to DataSource4.
- Drop a TBitBtn to the main form. It will automatically named BitBtn1. Give it caption of "Executed Stored Proc". Place it in the right area of the form, like shown in the next picture.
- Add the following method to the main form. Note that this is the core of this tutorial.
procedure TForm1.AllocateRecordsets; var i: Integer; begin ADODataSet1.Recordset := ADOStoredProc1.NextRecordset(i); ADODataSet2.Recordset := ADOStoredProc1.NextRecordset(i); ADODataSet3.Recordset := ADOStoredProc1.NextRecordset(i); end;
- Double click BitBtn1. Skeleton code for its OnClick event will be generated. Use the following codes for this event handler.
procedure TForm1.BitBtn1Click(Sender: TObject); begin ADOStoredProc1.Close; ADOStoredProc1.Open; AllocateRecordsets; end;
Executing the Demo
When you are ready, execute the demo project by pressing F9 key. Initially you will get something like shown below. That's because the stored procedure is not yet active.
Now click on Exec Stored Proc button. And you will get something like shown below. Note that each DB grid has been populated with proper record set.
And here is full source code of the demo project. Note that you need to adjust ADOConnection1's ConnectionString for the project to be able to run properly. Otherwise it will seek my SQL Server instance which should not reachable in your environment.
Project1.zip 81.81KB 527 downloads