Retrieve Multiple Records from Database Using SOA
- Last UpdatedJul 01, 2024
- 4 minute read
This example illustrates, how to get the multiple records from database by using SOA.
Assume that the following are the database details:
The following is the SQL script:
USE [EmployeeDetails]
GO
/****** Object: Table [dbo].[Employee] Script Date: 10/09/2008 14:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
[Id] [uniqueidentifier] NULL,
[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Designation] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Salary] [decimal](18, 2) NULL,
[DateOfJoining] [datetime] NULL
) ON [PRIMARY]
Assume that there are three records in the Employee Table of EmployeeDetails Database.
To get the multiple records from database through SOA, follow the steps given below:
-
Prepare an Assembly with the following sample code for getting multiple records.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace EmployeeDetails
{
public class Employee
{
private string _connectionString = string.Empty;
public string ConnectionString
{
get
{
return _connectionString;
}
set
{
_connectionString = value;
}
}
private string _tableName = string.Empty;
public string TableName
{
get
{
return _tableName;
}
set
{
_tableName = value;
}
}
public Employee(string server, string database, string tablename)
{
this.ConnectionString = "server=" + server + ";database=" + database + ";Integrated Security=true;pooling=false;";
this.TableName = tablename;
}
public string GetEmployeeDetails()
{
string connectionString = this.ConnectionString;
string strQry = @"SELECT * FROM " + this.TableName;
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand(strQry, connection);
connection.Open();
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet, this.TableName);
connection.Close();
return dataSet.GetXml();
}
public string GetEmployeeSchema()
{
string connectionString = this.ConnectionString;
string strQry = @"SELECT * FROM " + this.TableName;
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand(strQry, connection);
connection.Open();
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet, this.TableName);
connection.Close();
return dataSet.GetXmlSchema();
}
}
}
The above code having two methods. One method is to get the Database Schema and other method is to get the raw XML. By passing the Server, Database and the Table name to the constructor it will create the object by that you can access the data from the database.
-
You have to register the assembly in SOA Assembly.
-
To register a assembly, select Settings ® SOA Folder ® New Assembly Folder from the Enterprise Console pull-down menu. Register a assembly called 'EmployeeDetails'.
-
Click Save. Now the EmployeeDetails assembly has been registered as shown below.
-
Select Tools > SOA Assembly > From Assembly Folder from the Enterprise Console pull-down menu. Expand EmployeeDetails and select dll and then click Next.
-
The Object Setup screen is displayed. Expand all class, methods and properties as shown below.
-
Click Finish & Publish to save the assembly. This will be listed in the Assembly List.
-
Design a workflow called 'Employee Details' as shown below.
-
Right-click the Start Activity and then select Activity Properties.
-
Select the SOA Object Instances property.
-
The SOA Object Instance screen is displayed. Drag and drop the constructor of the class in SOA Property editor. Here, the constructor takes three parameters with Server name, Database name, and Table name.
-
Pass the parameter to the constructor. Save the constructor. After saving the instance details, all the members of the class will be listed in the Expression Editor.
-
Click Save in the Activity Properties window.
-
Make use of the SOAExecute activity to execute the method and update the Variable with the Schema and the Raw XML details.
-
Right-click the SOA Execute activity and select Activity Properties.
-
Select Execute an SOA Expression property to build the expression.
-
Click OK and then click Save in the Activity Properties window.
-
Display the value in the logger using Logger activity. Right-click the Logger activity and then select Activity Properties.
-
Select LogMessage property and then type the logger message in the Logger Message window.
-
Execute the workflow.
-
The following figure shows the logger with the Schema XML.
-
The following figure shows the logger with the Raw XML:
Note: You can even create an XML Variable with the Schema XML and you can bind the data with the Raw XML.