restful - rest api c# sql server

Llamar al servicio web desde SQL CLR? (2)

Tengo un procedimiento almacenado de SQL Server 2012 que devuelve una tabla. Tengo que modificar ese SP para agregar un valor adicional a la tabla devuelta. Desafortunadamente, ese valor agregado proviene de una llamada a un servicio web. De mi investigación, deduzco que las principales formas de hacerlo son usar los procedimientos de automatización OLE (sp_OA ...) en SQL o un procedimiento almacenado SQLCLR. Dado el contexto de seguridad en el que se ejecutan los procedimientos sp_OA ..., el único valor de retorno es una clave de registro VARCHAR (10), y las llamadas al servicio son pocas (de diez a veinte por hora), supongo que el método SQLCLR es el camino a seguir. Además, el servicio web está alojado en nuestra intranet y no es accesible para el mundo exterior.

¿Hay una mejor manera de lograr lo que necesito? Mejor significado más rendimiento, mejor seguridad, más fácil de codificar y mantener

Definitivamente puede llamar a un servicio WCF utilizando SQL CLR .

Si no quiere eso, podría escribir un Servicio de Windows en C # que mire o sondee la tabla para ver los cambios . Dependiendo de cómo implemente este servicio, la reacción a un nuevo registro sería casi inmediata. Lea también ¿Cómo notificar a un servicio de Windows (c #) de un cambio de tabla DB (sql 2005)? .

Luego puede realizar la llamada de servicio desde C #, realizar el trabajo requerido y almacenar el resultado en la columna.

Cuando requiera más información, por ejemplo, variables adicionales obtenidas durante el intercambio, puede presentar una nueva tabla para almacenar eso y el resultado real que le interesa. Luego, una la tabla de la pregunta.

No utilice los procedimientos de Automatización OLE sp_OA* . No parecen oficialmente desaprobados, pero SQLCLR reemplaza tanto los procedimientos de automatización OLE como los procedimientos almacenados extendidos.

Sí, esto se puede hacer fácilmente en SQLCLR. Puede encontrar ejemplos sobre el uso de WCF (como se muestra en la respuesta de @ CodeCaster) o usando HttpWebRequest / HttpWebResponse (Tengo más información en esta respuesta: Cómo invocar el servicio web desde el procedimiento almacenado de SQL Server ). Además, tenga en cuenta que a veces también necesitará agregar el ensamblaje de serialización: usar servicios web y serialización Xml en la integración de CLR

Codificación y mantenimiento
Los servicios web brindan una buena API, pero si cambias la estructura, deberás recompilar y volver a desplegar al menos parte de esto. Suponiendo que la información que se intercambia es lo suficientemente simple, tiendo a pensar que tratar esto como una solicitud web estándar agrega mucha flexibilidad. Puede crear una función de solicitud web genérica (escalar o TVF) que tome los parámetros y el URI y construya la solicitud XML formateada correctamente y la envíe al URI. A continuación, obtiene la respuesta y simplemente devuelve el XML. Así que cambias un poco la responsabilidad ya que ahora necesitas analizar la respuesta XML en lugar de obtener un buen objeto. Pero, XML es fácil de analizar en SQL Server, y puede volver a utilizar esta función en cualquier cantidad de lugares. Y, si el servicio remoto se actualiza alguna vez, actualizar un Procedimiento almacenado para cambiar la cadena de consulta que se pasa al Servicio web y / o cambiar el análisis sintáctico de la respuesta XML es un PROCEDIMIENTO DE ALTERACIÓN simple y debería ser fácil de probar. No es necesario volver a compilar / volver a implementar el ensamblado SQLCLR.

Seguridad
Independientemente de cuán "pura" sea la llamada a un servicio web que desee, lo principal, en TRUSTWORTHY ON seguridad, es NO ser flojo y TRUSTWORTHY ON (como también se muestra en la página vinculada desde la respuesta de @ CodeCaster, y desafortunadamente la mayoría de los ejemplos aquí en las interwebs). La forma correcta de hacerlo seguro es hacer lo siguiente:

Firme su Asamblea
En la base de datos [master] , cree una Clave asimétrica a partir de la DLL de su Asamblea.
Además, en [master] , cree un inicio de sesión desde esa clave asimétrica
Otorgue a su nuevo usuario el permiso de EXTERNAL ACCESS ASSEMBLY
Crea tu Asamblea con un PERMISSION_SET de EXTERNAL_ACCESS , no UNSAFE