Post on 16-Feb-2015
transcript
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