Procedimientos almacenados en C#

1. Procedimientos almacenados en C# Edgar Sánchez Logic Studio Director Regional de Microsoft, Ecuador

2. Proceso de SQL Server Función T-SQL Código .NET Base de datos CLR hospedado • El CLR de .NET se hospeda dentro de SQL Server para mejorar el rendimiento • Las aplicaciones corren en el mismo espacio de direcciones que SQL Server • Procedimientos almacenados en cualquier lenguaje soportado por el CLR • Acceso a recursos fuera de SQL Server

3. Código procedimental en SQL Server 2005 • SQL Server 2005 soporta código en cualquier lenguaje .NET • Procedimientos almacenados • Funciones definidas por el usuario (UDFs) • Triggers • El runtime de .NET se carga con el primer acceso

4. El código .NET y Transact SQL • El código .NET y T-SQL se pueden invocar entre sí • Sujeto a las reglas de SQL Server • Los parámetros .NET deben ajustarse a T-SQL • Tanto el código .NET como T-SQL tienen escenarios adecuados de uso

5. El código .NET es rápido y seguro • El código .NET puede ser más rápido en algunos casos • Es compilado y no interpretado como lo son los procedimientos almacenados T-SQL • El código .NET es seguro • La seguridad del código es revisada cuando se cataloga • Los procedimientos almacenados extendidos no se pueden revisar para evitar código peligroso

6. El código .NET y el framework • El código .NET puede usar el .NET Framework • Sólo se aceptan métodos, clases y assemblies específicos • Consideraciones de seguridad • Vigilado cuando • El assembly es catalogado • El código es ejecutado

7. Acceso a datos con Transact-SQL • T-SQL es mejor para acceso a datos • No hay coerción de tipos • Todo el código pre-SQL Server 2005 está escrito en T-SQL • SQL Server 2005 agrega el manejo de excepciones a T-SQL

8. Transact-SQL puede ser más rápido • T-SQL puede ser más rápido para acceso a datos • Acceso directo a los buffers internos de SQL Server • Biblioteca de funciones rica y centrada en datos • No hay conversión de tipos

9. Procedimientos .NET y Transact-SQL • Transact-SQL es mejor para código centrado en acceso a datos • No se carga el runtime de .NET • Acceso directo a la capa de datos • Programación procedimental • .NET es mejor para código que no accese a datos • Fórmulas matemáticas • Acceso a recursos del sistema fuera de SQL Server • Programación orientada a objetos • Ambos tipos de código • Guardan y cargan código desde la base de datos • Basados en estándares ANSI • Transact-SQL es una variación de la norma ANSI SQL PSM • El código .NET es similar a la especificación ANSI SQL JRT

10. Arith.dll public class Math { CREATE ASSEMBLY Arith ... public static int invert() go { ... } CREATE FUNCTION invert ... public static int not() CREATE FUNCTION not ... {...} } Catalogación de código .NET • El código procedimental .NET debe ser catalogado • Las funciones procedimentales .NET deben ser catalogadas

11. Mapeo de nombres T-SQL • Nombre T-SQL debe mapearse al nombre .NET • El nombre .NET se denomina nombre externo • El nombre externo es delimitado por assembly, namespace y clase

12. Nombres externos de funciones • Los nombres externos se componen de tres partes • El nombre simbólico del assembly es separado por '.' • Nombre completo de la clase, en apóstrofes o corchetes en el namespace .NET • El nombre de función separado por '.' • El nombre del assembly no distingue mayúsculas de minúsculas • El nombre de la clase y el método si los distingue • Aún si el lenguaje .NET no lo hace GEO.[Math.Arith].invert

13. System.Data.SqlClient SqlConnection Select * from ... Microsoft.SqlServer.Server Connection SqlContext Select * from ... Database Modelo de programación SQL Server • SQL Server 2005 incluye dos proveedores de datos • System.Data.SqlServer • System.Data.SqlClient • Los modelos de programación tienen algunas diferencias • SqlClient usa una SqlConnection • SqlServer usa un SqlContext

14. System.Data.SqlClient Input Buffer Connection Socket Output Buffer Socket Database Transaction Código fuera de la base de datos • El código fuera de la base de datos necesita establecer una conexión • Establecer buffers • Iniciar un lote de comandos • Iniciar una transacción o participar en una

15. Código dentro de la base de datos • SqlServer no necesita una conexión • Una conexión explícita desperdicia recursos • Los comandos son parte del mismo lote • El código que se ejecuta puede ser ya parte de una transacción • El contexto provee acceso directo a la base de datos • El código corre como si fuera parte de la base de datos

16. SqlContext • SqlContext representa el contexto actual de ejecución en el SQL Server • Pipe permite insertar información en la corriente de salida TDS • Resultsets y mensajes • TriggerContext provee información de contexto cuando se ejecuta un trigger • Todos los comandos son parte del lote actual • Pueden heredar la transacción actual • No afectan al nivel de anidamiento transaccional

17. Resultados de comandos • El acceso a SQL Server produce uno de cuatro tipos de resultados • Cuenta de filas afectadas por el comando • Resultado escalar, p.ej. Un agregado como el total de ventas • Resultado con una sola fila, p.ej. Una dirección de un vendedor • Resultado multi-fila, p.ej. Un conjunto de facturas pendientes

18. Resultado escalar • Producido por SqlCommand.ExecuteScalar • La manera más eficiente y simple de obtener un valor escalar • Usado para obtener un valor agregado • Usado para obtener un valor devuelto por un procedimiento almacenado • Se debe convertir al tipo apropiado Función agregada cmd.CommandText="select count(*) from authors"; int count = (int)cmd.ExecuteScalar(); Obtener el resultado escalar Conversión al tipo apropiado

19. Resultado con una sola fila • Producido por SqlCommand.ExecuteRow • Devuelve un ISqlRecord, puede ser convertido a IDataRecord • Usar solo cuando se espera una única fila • La fila es de lectura solamente • Todas las filas son devueltas pero solo la primera es accesible, la consulta debe limitar a una sola Limitar a una sola fila cmd.CommandText="Select top 1 * from authors"; ISqlRecord row = cmd.ExecuteRow(); String lastName = row.GetString(1); Obtener el resultado de una sola fila

20. Resultado multi-fila • Producido por SqlCommand.ExecuteReader • SqlDataReader es muy liviano • Comportamiento forward-only, read-only • Se puede devolver al cliente El resultado será read-only SqlDataReader rdr = cmd.ExecuteReader();while(rdr.Read() == true){ // trabajar con la fila } Mínimo derecursos utilizado

21. Funciones que devuelven tablas y ISqlReader • El proveedor SqlServer puede devolver una TABLE a través de una UDF • Se declara como otras UDFs, el valor retornado es un ISqlReader • Devolver SqlDataReader no es soportado public static ISqlReader myTVF(SqlString region) { ISqlReader irdr = null; // obtener una clase que implementa ISqlReader // poblar el objeto ISqlReader return(irdr); }

22. SqlTriggerContext • SqlTriggerContext es el ambiente dentro de un trigger • Se puede obtener en SqlContext.TriggerContext • Contiene a la enumeración Trigger.Action • INSERT, UPDATE, or DELETE • Contiene IsUpdatedColumn() • Booleano que dice si una columna está siendo actualizada • Se puede obtener EventData • Para triggers extendidos y mayor control

23. Uso de SqlTriggerContext public static void DontInsertSpecialID() { using(SqlConnection conexion = new SqlConnection("context connection=true")) { SqlTriggerContext tc = SqlContext.TriggerContext; if (tc.TriggerAction == TriggerAction.Insert) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select count(*) from inserted " + "where au_id = '111-11-1111'"; cmd.Connection = conexion; if ((int)cmd.ExecuteScalar() > 0) { cmd.CommandText = "ROLLBACK TRANSACTION"; SqlContext.Pipe.Execute(cmd); } } } }

24. ¿Preguntas? • Lectura recomendada: A First Look at Microsoft SQL Server 2005 for Developers Bob Beauchemin, Niels Berglund, Dan Sullivan Addison-Wesley Professional; 1st edition (June 25, 2004) ISBN: 0321180593