Debido a que creo que hay bastantes dudas y poca información sobre el SQL Adapter, me he decidido a escribir este post para intentar al menos sentar las bases del uso del adaptador.
Este ejemplo no pretende ser código optimizado listo por ponerse en producción, sino más bien una prueba de concepto que facilite la tarea de aprendizaje de todas las personas que se empiecen a "pegar" con el SQL Adapter y solo encuentren documentación en inglés.
En el ejemplo usaremos el SQL Adapter para ejecutar un procedimiento almacenado el cual insertará un registro en una tabla y obtendrá como resultado los datos del registro insertado anteriormente.
Crear tabla y procedimiento almacenado
- Crear la tabla de ejemplo
CREATE TABLE [dbo].[tablaejemplo]
(
[ID] [int] NULL,
[Descripcion] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL
) ON [PRIMARY]
CREATE PROCEDURE [dbo].[insert_tablaejemplo]
@id int,
@desc varchar(50)
AS
BEGIN
SET NOCOUNT ON;
insert into tablaejemplo(id,descripcion) values(@id,@desc)
select id,descripcion from tablaejemplo where id=@id for xml auto,xmldata
END
La clausula xmldata causa que SQL Server devuelva como resultado de la consulta el esquema además de los datos.
Esta clausula la eliminaremos después de configurar los metadatos del adaptador.
Crear un proyecto de Biztalk
Añadir metadatos al proyecto
- Pulsamos con el botón derecho sobre el proyecto que hemos creado previamente y seleccionamos Add --> Add Generated Items…
En la siguiente pantalla seleccionamos la opción de Add Adapter Metadata y pulsamos el botón Add:
- Deberemos seleccionar SQL en la lista de adaptadores registrados.
En la misma pantalla indicamos el servidor y la base de datos donde se encuentra el procedimiento almacenado que vamos a invocar.
Por ahora no seleccionaremos nada en la lista de puertos.
- En la pantalla Database Information definimos la cadena de conexión de la base de datos que usaremos posteriormente.
- En la pantalla Schema Information debemos definir la siguiente información:
- Target namespace: http://EjemploSQLAdapter
- Seleccionar Send Port ya que vamos a enviar una petición a SQL Server.
- Debemos asignar un nombre al elemento raíz del documento de petición: root_request
- Debemos asignar un nombre al elemento raíz del documento de respuesta: root_response
- En la pantalla Statement type information seleccionar Stored Procedure como tipo de sentencia.
Por último en la pantalla Statement Information debemos seleccionar el procedimiento almacenado que vamos a ejecutar.
Una vez seleccionado, pulsaremos el botón de Generate para generar el script de ejecución del procedimiento almacenado.
Los valores de los parámetros son almacenados solamente para la generación del esquema. En ejecución se usaran los parámetros pasados en el mensaje de petición.
• El checkbox asigna el parámetro a NULL
• Haciendo doble click sobre el parámetro podremos introducir los valores deseados.
Una vez finalizado este proceso podemos comprobar que se han creado dos archivos: BizTalk Orchestration.odx y SQLService.xsd. El primero es una orquestación vacía con algunos tipos de puertos pre-configurados. El segundo archivo es el esquema que va a contener la estructura del mensaje de petición y de respuesta del adaptador.
En este punto debemos modificar el procedimiento almacenado y eliminar la cláusula xmldata:
select id,descripcion from tablaejemplo where
id=@id for xml auto ,xmldata
Crear los esquemas necesarios para el proyecto
Pulsamos con el botón derecho sobre el proyecto que hemos creado previamente y seleccionamos Add --> New Item --> Schema.
Llamar EsquemaRequest.xsd al nuevo esquema.
Pulsar con el botón derecho en el nodo raíz (root) del nuevo esquema y seleccionar Insert Schema Node --> Child Field Element.
Llamar a este elemento ID.
Seleccionar este nodo y en la ventana de propiedades modificar el campo Data Type a xs:int.
Pulsar con el botón derecho en el nodo raíz (root) del nuevo esquema y seleccionar Insert Schema Node --> Child Field Element.
Llamar a este elemento Descripcion.
Una vez hayamos finalizado debemos tener un esquema igual al de la siguiente pantalla:
Crear mapa
Debemos crear un mapa que convierta el mensaje recibido por la orquestación al mensaje de petición del SQL Adapter.
- Pulsamos con el botón derecho sobre el proyecto que hemos creado previamente y seleccionamos Add --> New Item --> Map.
Llama Mapa.btm al nuevo mapa.
- Hacer click en el enlace Open Source Schema y seleccionar DemoSqlAdapter.EsquemaRequest.
- Hacer click en el enlace Open Destination Schema y seleccionar DemoSqlAdapter.SQLService.
- Seleccionar root_request.
- Asociar los nodos ID de ambos esquemas.
- Asociar el nodo Descripcion del esquema origen y el nodo desc del esquema destino.
Añadir mensajes a la orquestación
- Crear un mensaje llamado msgCallSQL y seleccionar como tipo de mensaje DemoSqlAdapter.SQLService.root_request.
- Crear un mensaje llamado msgPeticion y seleccionar como tipo de mensaje DemoSqlAdapter.EsquemaRequest.
- Crear un mensaje llamado msgResponse y seleccionar como tipo de mensaje DemoSqlAdapter.SQLService.root_response.
Añadir componentes a la orquestación
- Crear un shape de tipo Receive al que llamaremos ObtenerPeticion.
Modificar la propiedad Activate de este shape a true.
Seleccionar msgPeticion en la propiedad Message.
- Crear un shape de tipo Transform inmediatamente debajo del anterior.
- Seleccionar el shape de tipo Construct Message. Seleccionar el esquema msgCallSQL en la propiedad Messages Constructed.
- Seleccionar el shape de tipo Transform y modificar la propiedad name con el valor Transform_ParametrosPA.
- Configurar la transformación haciendo click en la propiedad Input Messages.
- Hacer click en la opción Existing Map y posteriormente seleccionar DemoSqlAdapter.Mapa en el combo Fully Quafiled Map Name.
- Hacer click en Source y seleccionar posteriormente en el campo Variable Name msgPeticion.
- Hacer click en Destination y seleccionar posteriormente en el campo Variable Name msgCallSQL.
- Crear un shape de tipo Send inmediatamente debajo del anterior, al que llamaremos EjecutarPeticionSQL.
Seleccionar msgCallSQL en la propiedad Message.
- Crear un shape de tipo Receive inmediatamente debajo del anterior, al que llamaremos ObtenerResultado.
Seleccionar msgResponse en la propiedad Message.
- Crear un shape de tipo Send inmediatamente debajo del anterior, al que llamaremos EnviarRes.
Seleccionar msgResponse en la propiedad Message.
Añadir puertos a la orquestación
Crear un puerto y situarlo en el área izquierda de puertos a la altura del shape ObtenerPeticion.
Definir las siguientes propiedades del puerto:
- Nombre: Port_ObtenerPeticion
- Port Type: PortType_ObtenerPeticion
- Communication Pattern: One-way
- Port direction of communication: I’ll always be receiving messages on this port.
- Port binding: Specify now
- Transport: FILE
- URI: escoger una ruta donde dejaremos el fichero xml que desencadene la orquestación. Ej.: c:\demo\in\*.xml
Enlazamos el puerto Port_ObtenerPeticion con el shape ObtenerPeticion.
Crear un puerto y situarlo en el área derecha de puertos entre los shape EjecutarPeticionSQL y ObtenerResultado. Definir las siguientes propiedades del puerto:
- Nombre: Port_CallSQL.
- Port Type: PortType_CallSQL
- Communication Pattern: Request-Response
- Port direction of communication: I’ll be sending a request and receiving a response.
- Port binding: Specify Later
Enlazamos el shape EjecutarPeticionSQL con el mensaje de operación request del puerto Port_CallSQL.
Enlazamos el shape EnviarRes con el mensaje de operación response del puerto Port_CallSQL.
- Crear un puerto y situarlo en el área izquierda de puertos a la altura del shape EnviarRes. Definir las siguientes propiedades del puerto:
- Nombre: Port_Resultado
- Port Type: PortType_Resultado
- Communication Pattern: One-way
- Port direction of communication: I’ll always be sending messages on this port.
- Port binding: Specify now
- Transport: FILE
- URI: escoger una ruta donde dejaremos el fichero xml con el resultado de la operación. Ej.: c:\demo\out\res.xml
Enlazamos el puerto Port_Resultado con el shape ObtenerResultado.
- Anteriormente hemos creado un puerto llamado Port_CallSQL y hemos especificado que el binding lo haremos mas tarde. Ahora vamos a crear un puerto con el que haremos el binding.
- En Biztalk Explorer hacemos click con el botón derecho en Send Ports y seleccionamos la opción Add Send Port.
- En la lista seleccionamos Static Solicit-Response Port.
- Definir las siguientes propiedades:
- Transport type: SQL
- Address (URI):
- Connection String: Configuraremos la cadena de conexión
- Document Target Namespace: tenemos que escribir el mismo namespace que el que definimos en los metadatos del esquema. En nuestro caso
http://EjemploSQLAdapter.
Response Document Root Element: tenemos que escribir el mismo nombre, que el que definimos como elemento raíz del documento de respuesta cuando creamos los metadatos del esquema. En nuestro caso root_response.
Send Pipeline: Microsoft.BizTalk.DefaultPipelines.XMLTransmit
Solicit - Response: Microsoft.BizTalk.DefaultPipelines.XMLTransmit
Renombrar el puerto de SendPort1 a SendPortCall
Finalmente deberemos tener una orquestación similar a la siguiente:
Deploy de la aplicación
Ya solo nos queda hacer el deploy de nuestra orquestación para poder probar el desarrollo.
Para ello tenemos que firmar la orquestación con un strong name.
Una vez distribuida la orquestación, tenemos que hacer el binding entre el puerto Port_CallSQL y el puerto SendPortSQL.
Seleccionamos Host en Configurations y seleccionamos BizTalkServerApplication como host en el que enlistaremos la orquestación.
Para probar la orquestación, solo tenemos que dejar un fichero xml que cumpla con el esquema de tipo EsquemaRequest en la ruta establecida en el puerto Port_ObtenerPeticion.