Procedimientos Procedimientos almacenados en C#almacenados en C#

Edgar SánchezEdgar SánchezLogic StudioLogic StudioDirector Regional de Microsoft, EcuadorDirector Regional de Microsoft, Ecuador

CLR hospedadoCLR hospedado El CLR de .NET se hospeda dentro de SQL El CLR de .NET se hospeda dentro de SQL

Server para mejorar el rendimientoServer para mejorar el rendimiento Las aplicaciones corren en el mismo espacio de Las aplicaciones corren en el mismo espacio de

direcciones que direcciones que SQL ServerSQL Server Procedimientos almacenados en cualquier Procedimientos almacenados en cualquier

lenguaje soportado por el CLRlenguaje soportado por el CLR Acceso a recursos fuera de Acceso a recursos fuera de SQL ServerSQL Server

Código.NET

FunciónT-SQL

Base de datos

Proceso de SQL Server

Código procedimental en SQL Código procedimental en SQL Server 2005Server 2005 SQL Server 2005 soporta código en SQL Server 2005 soporta código en

cualquier lenguaje .NETcualquier lenguaje .NET Procedimientos almacenadosProcedimientos almacenados Funciones definidas por el usuario (UDFs)Funciones definidas por el usuario (UDFs) TriggersTriggers

El runtime de .NET se carga con el El runtime de .NET se carga con el primer accesoprimer acceso

El código .NET y Transact SQLEl código .NET y Transact SQL

El código .NET y T-SQL se pueden El código .NET y T-SQL se pueden invocar entre sí invocar entre sí Sujeto a las reglas de SQL ServerSujeto a las reglas de SQL Server Los parámetros .NET deben ajustarse a T-Los parámetros .NET deben ajustarse a T-

SQLSQL

Tanto el código .NET como T-SQL Tanto el código .NET como T-SQL tienen escenarios adecuados de usotienen escenarios adecuados de uso

El código .NET es rápido y El código .NET es rápido y seguroseguro

El código .NET puede ser más rápido El código .NET puede ser más rápido en algunos casosen algunos casos Es compilado y no interpretado como lo Es compilado y no interpretado como lo

son los procedimientos almacenados T-son los procedimientos almacenados T-SQLSQL

El código .NET es seguroEl código .NET es seguro La seguridad del código es revisada La seguridad del código es revisada

cuando se catalogacuando se cataloga Los procedimientos almacenados Los procedimientos almacenados

extendidos no se pueden revisar para extendidos no se pueden revisar para evitar código peligrosoevitar código peligroso

El código .NET y el frameworkEl código .NET y el framework

El código .NET puede usar el .NET El código .NET puede usar el .NET FrameworkFramework Sólo se aceptan métodos, clases y Sólo se aceptan métodos, clases y

assemblies específicosassemblies específicos Consideraciones de seguridadConsideraciones de seguridad Vigilado cuandoVigilado cuando

El assembly es catalogadoEl assembly es catalogado El código es ejecutadoEl código es ejecutado

T-SQL es mejor para acceso a datosT-SQL es mejor para acceso a datos No hay coerción de tiposNo hay coerción de tipos Todo el código pre-STodo el código pre-SQL Server 2005 está QL Server 2005 está

escrito en T-SQLescrito en T-SQL SQL Server 2005 agrega el manejo de SQL Server 2005 agrega el manejo de

excepciones a T-SQLexcepciones a T-SQL

Acceso a datos con Transact-Acceso a datos con Transact-SQLSQL

Transact-SQL puede ser más Transact-SQL puede ser más rápidorápido

T-SQL puede ser más rápido para T-SQL puede ser más rápido para acceso a datosacceso a datos Acceso directo a los buffers internos de Acceso directo a los buffers internos de

SQL ServerSQL Server Biblioteca de funciones rica y centrada en Biblioteca de funciones rica y centrada en

datosdatos No hay conversión de tiposNo hay conversión de tipos

Procedimientos .NET y Procedimientos .NET y Transact-SQLTransact-SQL Transact-SQL es mejor para código centrado en Transact-SQL es mejor para código centrado en

acceso a datosacceso a datos No se carga el runtime de .NETNo se carga el runtime de .NET Acceso directo a la capa de datosAcceso directo a la capa de datos Programación procedimentalProgramación procedimental

.NET es mejor para código que no accese a datos.NET es mejor para código que no accese a datos Fórmulas matemáticasFórmulas matemáticas Acceso a recursos del sistema fuera de Acceso a recursos del sistema fuera de SQL ServerSQL Server Programación orientada a objetosProgramación orientada a objetos

Ambos tipos de códigoAmbos tipos de código Guardan y cargan código desde la base de datosGuardan y cargan código desde la base de datos Basados en estándares ANSIBasados en estándares ANSI

Transact-SQL es una variación de la norma Transact-SQL es una variación de la norma ANSI SQL PSMANSI SQL PSM El código .NET es similar a la especificación ANSI SQL JRTEl código .NET es similar a la especificación ANSI SQL JRT

Catalogación de código .NETCatalogación de código .NET

El código procedimental .NET debe ser El código procedimental .NET debe ser catalogadocatalogado

Las funciones procedimentales .NET Las funciones procedimentales .NET deben ser catalogadasdeben ser catalogadas

public class Math{ public static int invert() { ... } public static int not() {...}}

Arith.dll

CREATE ASSEMBLY Arith ...goCREATE FUNCTION invert ...CREATE FUNCTION not ...

Mapeo de nombres T-SQLMapeo de nombres T-SQL

Nombre T-SQL debe mapearse al Nombre T-SQL debe mapearse al nombre nombre .NET.NET El nombre .NET se denomina nombre El nombre .NET se denomina nombre

externoexterno El nombre externo es delimitado por El nombre externo es delimitado por

assembly, namespace y claseassembly, namespace y clase

Nombres externos de funcionesNombres externos de funciones

Los nombres externos se componen de tres partesLos nombres externos se componen de tres partes El nombre simbólico del assembly es separado por El nombre simbólico del assembly es separado por '.''.' Nombre completo de la clase, en apóstrofes o corchetes en el Nombre completo de la clase, en apóstrofes o corchetes en el

namespace .NETnamespace .NET El nombre de función separado por El nombre de función separado por '.''.'

El nombre del assembly no distingue mayúsculas de El nombre del assembly no distingue mayúsculas de minúsculasminúsculas

El nombre de la clase y el método si los distingueEl nombre de la clase y el método si los distingue Aún si el lenguaje .NET no lo haceAún si el lenguaje .NET no lo hace

GEO.[Math.Arith].invert

Modelo de programación SQL Modelo de programación SQL ServerServer SQL Server 2005 incluye dos proveedores de datosSQL Server 2005 incluye dos proveedores de datos

System.Data.SqlServerSystem.Data.SqlServer System.Data.SqlClientSystem.Data.SqlClient

Los modelos de programación tienen algunas Los modelos de programación tienen algunas diferenciasdiferencias SqlClient usa una SqlConnectionSqlClient usa una SqlConnection SqlServer usa un SqlContextSqlServer usa un SqlContext

Database

System.Data.SqlClient

ConnectionSelect * from ...

SqlConnection

Microsoft.SqlServer.Server

SqlContextSelect * from ...

Código fuera de la base de Código fuera de la base de datosdatos El código fuera de la base de datos El código fuera de la base de datos

necesita establecer una conexiónnecesita establecer una conexión Establecer buffersEstablecer buffers Iniciar un lote de comandosIniciar un lote de comandos Iniciar una transacción o participar en unaIniciar una transacción o participar en una

Database

System.Data.SqlClient

Input Buffer

Output Buffer

Transaction

ConnectionSocket

Socket

Código dentro de la base de Código dentro de la base de datosdatos SqlServer no necesita una conexiónSqlServer no necesita una conexión

Una conexión explícita desperdicia recursosUna conexión explícita desperdicia recursos Los comandos son parte del mismo loteLos comandos son parte del mismo lote El código que se ejecuta puede ser ya parte de una El código que se ejecuta puede ser ya parte de una

transaccióntransacción

El contexto provee acceso directo a la base de datosEl contexto provee acceso directo a la base de datos El código corre como si fuera parte de la base de datosEl código corre como si fuera parte de la base de datos

SqlContextSqlContext

SqlContext representa el contexto SqlContext representa el contexto actual de ejecución en el SQL Serveractual de ejecución en el SQL Server Pipe permite insertar información en la Pipe permite insertar información en la

corriente de salida TDScorriente de salida TDS Resultsets y mensajesResultsets y mensajes

TriggerContext provee información de TriggerContext provee información de contexto cuando se ejecuta un triggercontexto cuando se ejecuta un trigger

Todos los comandos son parte del lote Todos los comandos son parte del lote actualactual Pueden heredar la transacción actualPueden heredar la transacción actual No afectan al nivel de anidamiento No afectan al nivel de anidamiento

transaccionaltransaccional

Resultados de comandosResultados de comandos

El acceso a SQL Server produce uno de El acceso a SQL Server produce uno de cuatro tipos de resultadoscuatro tipos de resultados Cuenta de filas afectadas por el comandoCuenta de filas afectadas por el comando Resultado escalar, p.ej. Un agregado Resultado escalar, p.ej. Un agregado

como el total de ventascomo el total de ventas Resultado con una sola fila, p.ej. Una Resultado con una sola fila, p.ej. Una

dirección de un vendedordirección de un vendedor Resultado multi-fila, p.ej. Un conjunto de Resultado multi-fila, p.ej. Un conjunto de

facturas pendientesfacturas pendientes

Resultado escalarResultado escalar Producido por SqlCommand.ExecuteScalarProducido por SqlCommand.ExecuteScalar

La manera más eficiente y simple de obtener un La manera más eficiente y simple de obtener un valor escalarvalor escalar

Usado para obtener un valor agregadoUsado para obtener un valor agregado Usado para obtener un valor devuelto por un Usado para obtener un valor devuelto por un

procedimiento almacenadoprocedimiento almacenado Se debe convertir al tipo apropiadoSe debe convertir al tipo apropiado

cmd.CommandText="select count(*) from authors";int count = (int)cmd.ExecuteScalar();

Función agregada

Obtener el resultado escalar

Conversión al tipo

apropiado

Resultado con una sola filaResultado con una sola fila Producido por Producido por SqlCommand.ExecuteRowSqlCommand.ExecuteRow

Devuelve un ISqlRecord, puede ser convertido a Devuelve un ISqlRecord, puede ser convertido a IDataRecord IDataRecord

Usar solo cuando se Usar solo cuando se esperaespera una única fila una única fila La fila es de lectura solamenteLa fila es de lectura solamente Todas las filas son devueltas pero solo la primera Todas las filas son devueltas pero solo la primera

es accesible, la consulta debe limitar a una solaes accesible, la consulta debe limitar a una sola

cmd.CommandText="Select top 1 * from authors";ISqlRecord row = cmd.ExecuteRow();String lastName = row.GetString(1);

Limitar a una sola fila

Obtener el resultado de una sola fila

Resultado multi-filaResultado multi-fila

Producido por SqlCommand.ExecuteReaderProducido por SqlCommand.ExecuteReader SqlDataReaderSqlDataReader es muy liviano es muy liviano Comportamiento forward-only, read-onlyComportamiento forward-only, read-only Se puede devolver al clienteSe puede devolver al cliente

SqlDataReader rdr = cmd.ExecuteReader();while(rdr.Read() == true){ // trabajar con la fila}

El resultado será read-only

Mínimo derecursosutilizado

Funciones que devuelven Funciones que devuelven tablas y ISqlReadertablas y ISqlReader

El proveedor SqlServer puede devolver una El proveedor SqlServer puede devolver una TABLE a través de una UDFTABLE a través de una UDF Se declara como otras UDFs, el valor retornado Se declara como otras UDFs, el valor retornado

es un ISqlReaderes un ISqlReader Devolver SqlDataReader no es soportadoDevolver 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);}

SqlTriggerContextSqlTriggerContext

SqlTriggerContext es el ambiente SqlTriggerContext es el ambiente dentro de un triggerdentro de un trigger Se puede obtener en Se puede obtener en

SqlContext.TriggerContextSqlContext.TriggerContext Contiene a la enumeración Trigger.ActionContiene a la enumeración Trigger.Action

INSERT, UPDATE, or DELETEINSERT, UPDATE, or DELETE

Contiene IsUpdatedColumn()Contiene IsUpdatedColumn() Booleano que dice si una columna está siendo Booleano que dice si una columna está siendo

actualizadaactualizada

Se puede obtener EventDataSe puede obtener EventData Para triggers extendidos y mayor controlPara triggers extendidos y mayor control

Uso de SqlTriggerContextUso de SqlTriggerContextpublic 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); }

} }}

¿Preguntas?¿Preguntas?

Lectura recomendada:Lectura recomendada:

A First Look at Microsoft SQL Server 2005 for Developers

Bob Beauchemin, Niels Berglund, Dan SullivanAddison-Wesley Professional; 1st edition (June 25, 2004) ISBN: 0321180593