Need help to connect to a SQL Server DB
Hello,
I am new in Pega Robotics Studio and just has finished successfully my first project to download data from a Web Page.
Now I need create a second solution to read from a SQL Server Database and insert the results into a Web Page.
Unfortunately I cannot find a clear example or video where explains how to use sqlQuery, dataSet and/or some other tools to extract the information. I don't need anything about how to enter it in the Web Page, just to understand how to send the results from sqlQuery to the dataSet and run over it afterwards.
Attached the steps I did, the Execute looks like it is working fine but I don't know how to link the results in the dataSet.
Any help would be very appreciated.
***Moderator Edit-Vidyaranjan: Updated Platform Capability***
Accepted Solution
Pegasystems Inc.
US
The GetTable returns a DataTable object. You would interact with it much like you would in C#. If you know the columns and Data types of the results, you could potentially create a LookupTable and define the columns to match your results. You could pass the GetTable result into the LookupTable.ReplaceTable method and then interact with the lookup table. What is it you would like to do with the result? Are you planning on iterating through them? You could pass the Rows property of the DataTable into a ListLoop. From a Row, you could get a column value. There are basically many possibilities here. Below is a link to the definition of a DataTable.
https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?view=netframework-4.6.1
Ford
ES
Based in your reply, I did the following steps:
1. Created a sqlQuery in the Global Container and in Properties I set the ConnectionString as "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<DATABASENAME>;Data Source=<SERVER_NAME>"
2. In the sqlQuery I assigned a query with a short set of results (e.g. SELECT TOP 3 * FROM Employee).
3. In Global Container I created a tableView and in TableProvider I linked to the GlobalContainer/sqlQuery
4. Important: In sqlQuery I click in Test Query, otherwise when you insert the tableView it shows no fields
5. In the automation, first I associate the sqlQuery/CommandText with the SQL command
6. I invoke sqlQuery/Execute
7. I run the results using a Loop that shows in screen the fields that are being obtained and allows to use then to call any other procedure