Procedimientos almacenados en C

1
Procedimientos almacenados en C

• Edgar Sánchez
• Logic Studio
• Director Regional de Microsoft, Ecuador

2
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
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
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
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() Strin
g 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 connectiontrue"))
SqlTriggerContext tc SqlContext.TriggerContext
if (tc.TriggerAction TriggerAction.Inser
t) SqlCommand cmd new SqlCommand()
cmd.CommandText "select count() from
inserted " "where au_id
'111-11-1111'" cmd.Connection
conexion if ((int)cmd.ExecuteScalar() gt
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