xml, distribución y componentes tema 2 – acceso a datos ado.net
TRANSCRIPT
XML, Distribución y Componentes
Tema 2 – Acceso a Datos ADO.NET http://paginaspersonales.deusto.es/dipina/MasterISW/
Dr. Diego Lz. de Ipiña Gz. de Artazahttp://paginaspersonales.deusto.es/dipina (Personal)
http://www.morelab.deusto.es (Research Group)http://www.smartlab.deusto.es (Research Lab)
http://www.ctme.deusto.es (Cátedra de Telefónica Móviles)http://www.tecnologico.deusto.es (Tecnológico-Fundación Deusto)
2/65
Temario
Acceso a datos ADO.NET
3/65
ADO.NET
Programar básicamente consiste en manejar y manipular datos Detrás de toda aplicación seria hay un gestor de bases de datos
ADO.NET es una API de acceso a bases de datos para aplicaciones gestionadas
Las clases definidas por ADO.NET se encuentran en System.Data y sus descendientes
Diseñado para permitir trabajar en un mundo sin conexiones de la web.
Se integra muy bien con XML, facilitando la transformación de datos entre SQL y XML en ambos sentidos
4/65
Proveedores de Datos
Los accesos a bases de datos mediante ADO.NET van a través de módulos conocidos como proveedores de datos
Hay dos proveedores de datos principales: El proveedor SQL Server .NET, que interactúa con Microsoft SQL
Server sin ayuda de proveedores unmanaged El proveedor OLE DB .NET que interactúa con bases de datos a
través de proveedores OLE DB Los drivers OLE DB crearon una API orientada a objetos
sobre bases de datos, del mismo modo que los drivers Open Database Connectivity (ODBC) ofrecían una interfaz procedural
El proveedor de la .NET Framework OLE DB .NET permite el acceso desde .NET a bases de datos que ofrecen drivers OLE DB. No es compatible con todas las bases de datos y a menudo es
necesario utilizar el Microsoft’s ODBC .NET driver http://www.microsoft.com/downloads/details.aspx?FamilyID
=6ccd8427-1017-4f33-a062-d165078e32b1&displaylang=en
5/65
Proveedores de Datos
Cada proveedor aporta clases y estructuras de las cuales las más importantes son (precedidas por OleDb, Sql, Oracle u Odbc): Connection establecen la conexión con el origen de datos Command sirven para ejecutar comandos sobre una conexión
abierta CommandBuilder genera los comandos de inserción,
actualización y borrado para un DataAdapter DataReader los resultados devueltos por un comando se leen
secuencialmente DataAdapter los resultados se cargan en memoria por un DataAdapter
6/65
Clases Genéricas de Acceso a Datos
Con independencia del proveedor utilizado podemos acceder a los datos mediante las siguientes clases: DataSet DataTable DataRow DataColumn
El puente de conexión entre estas clases genéricas y las anteriores es un adaptador de datos El DataAdapter genera el DataSet a partir de comandos
específicos y propaga las inserciones, borrados y modificaciones. Un DataSet contiene filas y columnas, así como objetos DataRelation y DataView.
7/65
SqlServer 2005 Express Edition
Una vez instalado efectuar los siguientes cambios: Abrir Surface Area Configuration for Services and Connections
Elegir Remote Connections/Using TCP/IP only Abrir el SQL Server Configuration Manager
Right click sobre SQL Server y clic sobre properties En Service StartMode automatic
Network configuration/protocols for SQLEXPRESS Enabled YES IP Addresses Active, Enabled (yes), TCP Dynamic Ports (0)
Permitir modo de autenticación mixed (por defecto, sólo modo Windows):1. Arrancar panel de control escribiendo ‘control’ en cmd2. Abrir SQL Configuration Manager (parar SQL Server y SQL Server Browser)3. Abir regedit 4. Localizar HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\
MSSqlServer 5. Hacer doble clic sobre LoginMode6. Cambiar el valor a 2 (antes 1)7. Rearrancar SQLServer y Browser
8/65
SqlServer 2005 Express Edition
Para cambiar la password del usuario ‘sa’ haga lo siguiente:
1. osql -E -S "localhost\SQLEXPRESS“ O sqlcmd -SDIPINA-6400\SQLEXPRESS –E
2. C:\Documents and Settings\Diego López de Ipiña>osql -E -S localhost\SQLEXPRESS
3. 1> alter login sa enable4. 2> alter login sa with
password='distributed.net'5. 3> go6. 1> exit
7. C:\Documents and Settings\Diego López de Ipiña>osql -Usa -S localhost\SQLEXPRESS
8. Password:9. 1> exit10. C:\Documents and Settings\Diego López de Ipiña>
9/65
Configuración SQLEXPRESS
Para cambiar la password del usuario ‘sa’ haga lo siguiente:1. osql -E -S "localhost\SqlExpress“ O sqlcmd -SDIPINA-6400\SQLEXPRESS –
E2. C:\Documents and Settings\Diego López de Ipiña>osql -E -S localhost\
SQLEXPRESS3. 1> alter login sa enable4. 2> alter login sa with password='distributed.net'5. 3> go6. 1> exit
7. C:\Documents and Settings\Diego López de Ipiña>osql -Usa -S localhost\SQLEXPRESS
8. Password:9. 1> exit
10.C:\Documents and Settings\Diego López de Ipiña>
10/65
SqlServer 2005 Express Edition y Bases de Datos de Ejemplo
SQL Server 2005 Express Edition: http://msdn.microsoft.com/vstudio/express/sql/
Download de: http://msdn.microsoft.com/vstudio/express/sql/download/
Documentación herramienta OSQL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp
Para cambiar la password del usuario ‘sa’ haga lo siguiente:1. osql –E2. sp_password @old = null, @new = ‘distributed.net',
@loginame ='sa‘3. go
11/65
Instalando MSDE y las Bases de Datos de Ejemplos II
Si al hacer login con osql -U sa recibe: Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection
Debe cambiar el modo de autenticación de Windows a Mixed Mode: Por defecto su valor es 1 para autenticación Windows. Para Mixed Mode el valor es 2.
Pasos a seguir:1. Arrancar panel de control escribiendo ‘control’ en cmd2. Parar MSSQLSERVER y SQLSERVERAgent3. Abir regedt32 4. Localizar HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer o
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\
5. Hacer doble clic sobre LoginMode6. Cambiar el valor a 27. Rearrancar MSSQLSERVER y SQLSERVERAgent para que el cambio tome efecto.
12/65
Instalando MSDE y las Bases de Datos de Ejemplos III
Bajarse la base de datos con ejemplos pubs de:http://www.microsoft.com/downloads/details.aspx?FamilyId
=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en Hacer doble click sobre el fichero bajado Hacer cd al directorio donde se descomprimen los ejemplos Ejecutar:
osql –U sa –S localhost\SQLEXPRESS –i instnwnd.sql y osql –U sa –S localhost\SQLEXPRESS –i instpubs.sql
oOsql –E –i instnwnd.sql –S localhost\SQLEXPRESS y osql –E –S localhost\
SQLEXPRESS -i instpubs.sql Para hacer login en una instancia haga:
osql -U sa –S localhost/SQLEXPRESS -S servername\instancename osql -Usa -Slocalhost\SQLEXPRESS -d pubs
13/65
Ayuda MSDE (ReadmeMSDE2000A.htm)
To install a new instance of Desktop Engine 1. Open a command prompt window. 2. From the command prompt, use the cd command to navigate to the folder containing
the MSDE 2000 Release A Setup utility: cd c:\MSDE2000AFolder\MSDE where c:\MSDE2000AFolder is the path to the folder where you extracted the MSDE 2000 Release A files.
3. Execute one of the following commands: To install a default instance configured to use Windows Authentication Mode, execute:
setup SAPWD="AStrongSAPwd" Where AStrongSAPwd is a strong password to be assigned to the sa login.
To install a named instance configured to use Windows Authentication Mode, execute: setup INSTANCENAME="InstanceName" SAPWD="AStrongSAPwd" Where AStrongSAPwd is a strong password to be assigned to the sa login and InstanceName is the name to be assigned to the instance.
To install a default instance configured to use Mixed Mode, execute: setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL Where AStrongSAPwd is a strong password to be assigned to the sa login.
To install a named instance configured to use Mixed Mode, execute: setup INSTANCENAME="InstanceName" SECURITYMODE=SQL SAPWD="AStrongSAPwd"
14/65
Espacios de Nombres System.Data.SqlClient y System.Data.OleDb
Desafortunadamente dependiendo de si queremos utilizar SQL Server o un motor compatible con OleDB, el código escrito varía: Nombres de clases Strings de conexión
15/65
Conexiones, Comandos y Data Readers
El uso canónico de comandos en ADO.NET es el siguiente: Crear un objeto de conexión (SqlConnection o OleDbConnection) que encapsula un string de conexión
Abrir la conexión invocando al método Open del objeto conexión
Crear un comando (SqlCommand o OleDbCommand) encapsulando el comando SQL y la conexión usada por el comando
Invocar un método en el objeto command Como resultado de invocar un comando se devuelve un DataReader
Cerrar la conexión invocando su método Close
16/65
Ejemplo SqlReader: ListTitlesSQLProvider.csusing System;using System.Data;using System.Data.SqlClient;public class ListTitlesSQLProvider { public static void Main() { SqlConnection conn = new SqlConnection("server=<my-server-;database=pubs;uid=sa;pwd=<my-pass>"); try { conn.Open (); SqlCommand cmd = new SqlCommand ("select * from titles", conn); SqlDataReader reader = cmd.ExecuteReader (); while (reader.Read ()) Console.WriteLine (reader["title"]); }
catch (SqlException ex) { Console.WriteLine (ex.Message); } finally { conn.Close (); }}
}
17/65
Tabla Titles de Pubs
18/65
Ejemplo OleDbReader: ListTitlesOleDbProvider.cs
using System;using System.Data;using System.Data.OleDb;public class ListTitlesOLEDBProvider { public static void Main() { OleDbConnection conn = new OleDbConnection("provider=sqloledb;server=<nombre-
servidor>;database=pubs;uid=sa;pwd=<password>"); try { conn.Open (); OleDbCommand cmd = new OleDbCommand ("select * from titles", conn); OleDbDataReader reader = cmd.ExecuteReader (); while (reader.Read ()) Console.WriteLine (reader["title"]); } catch (OleDbException ex) { Console.WriteLine (ex.Message); } finally { conn.Close (); } }}
19/65
La clase SqlConnection
La siguiente sentencia crea un objeto SqlConnection y lo inicializa con un string de conexión que abre la base de datos Pubs que viene con SQLServer, usando el nombre de usuario “sa” y la password vacía:SqlConnection conn = new SqlConnection ();conn.ConnectionString =
"server=localhost;database=pubs;uid=sa;pwd="; De un solo paso, lo mismo podría hacerse:
SqlConnection conn = new SqlConnection ("server=localhost\SQLEXPRESS;database=pubs;uid=sa;pwd=distributed.net");
Las propiedades de un SqlConnection.ConnectionString son: Server=localhost, también se puede escribir Server=(local) o Data
Source=(local) El parámetro Database o Initial Catalog, identifies la base de datos Uid, o User ID, especifica el nombre de usuario Pwd o Password, la contraseña
Hay otros parámetros opcionales, para más detalles mirar documentación de SqlConnection.ConnectionString:SqlConnection conn = new SqlConnection ("server=hawkeye\
wintellect;database=pubs;uid=sa;pwd=;" +"min pool size=10;max pool size=50;connect timeout=10");
20/65
La clase OleDbConnection
La clase System.Data.OleDb.OleDbConnection representa conexiones a bases de datos accesibles a través del proveedor OLE DB de .NET
El formato de los parámetros utilizados sigue las convenciones de las conexiones OLE DBOleDbConnection conn = new OleDbConnection
("provider=SQLNCLI;server=localhost;database=pubs;uid=sa;pwd=");
El parámetro Provider identifica al proveedor de OLE DB usado para interactuar con la base de datos, SQLOLEDB para SQLServer o MSDAORA para Oracle
La conexión anterior se podría haber abierto usandoOleDbConnection conn = new OleDbConnection ("provider=sqloledb;data source=localhost;" + "initial catalog=pubs;user id=sa;password=");
21/65
Abriendo y Cerrando Conexiones
En SQLServer se usaría el siguiente código:SqlConnection conn = new SqlConnection
("server=localhost;database=pubs;uid=sa;pwd="); conn.Open ();
En cualquier otra base de datos que soporte OLE DB:OleDbConnection conn = new
OleDbConnection("provider=sqloledb;server=localhost;database=pubs;uid=sa;pwd=");
try { conn.Open (); // TODO: Use the connection}catch (OleDbException ex) { // TODO: Handle the exception}finally { conn.Close ();}
22/65
Otros strings de conexión
Si estamos usando una BBDD que no es ni SQL Server podemos encontrar sus strings de conexión correspondientes en: http://www.connectionstrings.com/
23/65
Clases Command ADO.NET provee un par de clases comando SqlCommand y OleDbCommand
SqlConnection conn = new SqlConnection ("server=localhost;database=pubs;uid=sa;pwd=");try { conn.Open (); SqlCommand cmd = new SqlCommand (); cmd.CommandText = "delete from titles where title_id =
'BU1032'"; cmd.Connection = conn; cmd.ExecuteNonQuery (); // Execute the command}catch (SqlException ex) { // TODO: Handle the exception}finally { conn.Close ();}
El comando se podría haber creado de manera más sencilla:SqlCommand cmd = new SqlCommand ("delete from titles where title_id = 'BU1032'", conn);cmd.ExecuteNonQuery (); // Execute the command
24/65
Método ExecuteNonQuery
El método ExecuteNonQuery es un vehículo para ejecutar comandos SQL: INSERT, UPDATE, DELETE (devuelve el número de filas afectadas), y otros que no devuelven valores: Comandos CREATE DATABASE y CREATE TABLE.
Ejemplos:SqlCommand cmd = new SqlCommand ("insert into titles (title_id, title, type, pubdate) " + "values ('JP1001', 'Programming Microsoft .NET', " + "'business', 'May 2002')", conn);
SqlCommand cmd = new SqlCommand("update titles set title_id = 'JP2002' " +
"where title_id = 'JP1001'", conn); Revisar ejemplo: ExampleExecuteNonQuery.cs
25/65
Método ExecuteScalar
Ejecuta un comando SQL y devuelve el valor de la primera columna de la primera fila
Se suele utilizar con funciones SQL como: COUNT, AVG, MIN, MAX, y SUM.SqlConnection conn = new SqlConnection ("server=localhost;database=pubs;uid=sa;pwd=");try { conn.Open (); SqlCommand cmd = new SqlCommand ("select max (advance) from titles", conn); decimal amount = (decimal) cmd.ExecuteScalar (); Console.WriteLine ("ExecuteScalar returned {0:c}", amount);}catch (SqlException ex) { Console.WriteLine (ex.Message);}finally { conn.Close ();}
ExecuteScalar devuelve un Object al que hay que aplicarle casting. Otro uso importante de ExecuteScalar es para almacenar BLOBs (Binary
Large Objects) y recuperarlos de una base de datos
26/65
Recuperando un BLOB con ExecuteScalar I// file : RetrievingBLOB.cs// compile : csc RetrievingBLOB.csusing System;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.IO;public class RetrievingBLOB {
public static void Main(string[] args) {
if (args.Length != 2) {
Console.WriteLine("Uso: RetrivingBLOB.exe <id-publicación> <fichero-guardar-logo>");
return;}FileStream fichero = File.Open (args[1], FileMode.CreateNew,
FileAccess.Write);BinaryWriter writer = new BinaryWriter (fichero);MemoryStream stream = new MemoryStream ();SqlConnection conn = new SqlConnection
("server=<server-name>;database=pubs;uid=sa;pwd=<password>");
27/65
Recuperando un BLOB con ExecuteScalar II
try {
conn.Open ();SqlCommand cmd = new SqlCommand("select logo from pub_info
where pub_id='" + args[0] + "'", conn);byte[] blob = (byte[]) cmd.ExecuteScalar ();stream.Write (blob, 0, blob.Length);Bitmap bitmap = new Bitmap (stream);stream.WriteTo(fichero);bitmap.Dispose ();
}catch (SqlException ex) {
// TODO: Handle the exception}finally {
stream.Close ();writer.Close();fichero.Close();conn.Close ();
}
}}
28/65
Insertando un BLOB I// file : StoringBLOB.cs// compile : csc StoringBLOB.csusing System;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.IO;public class StoringBLOB {
public static void Main(string[] args) {
if (args.Length != 2) {
Console.WriteLine("Uso: StoringBLOB.exe <id-publicación> <fichero-importar-logo>");
return;}FileStream stream = new FileStream (args[1], FileMode.Open);byte[] blob = new byte[stream.Length];stream.Read (blob, 0, (int) stream.Length);stream.Close ();
SqlConnection conn = new SqlConnection("server=<server-name>;database=pubs;uid=sa;pwd=<password>");
try {
conn.Open ();SqlCommand cmd = new SqlCommand
("delete from pub_info where pub_id='" + args[0] + "'",conn);
cmd.ExecuteNonQuery ();
29/65
Insertando un BLOB II
cmd = new SqlCommand("delete from publishers where pub_id='" + args[0] + "'",conn);
cmd.ExecuteNonQuery ();cmd = new SqlCommand
("insert into publishers values (" + args[0] + ", 'Ediciones Deusto', 'Bilbao' , 'BI', 'SPAIN')",
conn);cmd.ExecuteNonQuery ();Console.WriteLine("Información de Publisher insertada");cmd = new SqlCommand
("insert into pub_info (pub_id, logo) values ('" + args[0] + "', @logo)", conn);
cmd.Parameters.Add ("@logo", blob);cmd.ExecuteNonQuery ();Console.WriteLine("Logo cargado en base de datos");
}catch (SqlException ex) {
// TODO: Handle the exceptionConsole.WriteLine("Excepción lanzada: " + ex.Message);
}finally {
conn.Close ();}
}}
30/65
El método ExecuteReader I
El método ExecuteReader existe para sólo un propósito: Realizar consultas de bases de datos y obtener los
resultados lo más rápida y eficientemente posible. ExecuteReader devuelve un objeto DataReader, de nombre SqlDataReader para SqlCommand y OleDbDataReader para OleDbCommand.
DataReader tiene métodos y propiedades que te permiten iterar sobre los resultados Solamente puede leer para adelante
31/65
El método ExecuteReader II
Para recuperar los metadatos de una relación se pueden usar los métodos: GetSchemaTable GetFieldType y GetDataTypeName
32/65
Ejemplo ExecuteReader I
// file : ExampleExecuteReader.cs// compile : csc ExampleExecuteReader.csusing System;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.IO;
public class ExampleExecuteReader {
public static void Main(string[] args) {
SqlConnection conn = new SqlConnection("server=<server-
name>;database=pubs;uid=sa;pwd=<password>");
try {
conn.Open ();
33/65
Ejemplo ExecuteReader II
SqlCommand cmd = new SqlCommand ("select * from titles", conn);SqlDataReader reader = cmd.ExecuteReader ();// Lista todos los campos leidoswhile (reader.Read ())
Console.WriteLine (reader["title"]);// Los nombres de los campos de la tabla leídaConsole.WriteLine("\nLos nombres de los campos de la base de datos
son:");for (int i=0; i<reader.FieldCount; i++)
Console.WriteLine (reader.GetName (i));// Recupera el índice de un campo y luego devuelve sus valoresreader.Close();reader = cmd.ExecuteReader ();Console.WriteLine("\nLos valores del campo avance son:");int index = reader.GetOrdinal ("advance");while (reader.Read ())
Console.WriteLine ("{0:c}", reader.GetDecimal (index));}catch (SqlException ex) {
Console.WriteLine (ex.Message);}finally {
conn.Close ();}
}}
34/65
Transacciones en ADO.NET
Las transacciones son operaciones importantes en muchas aplicaciones orientadas a los datos
Una transacción es simplemente dos o más unidades de trabajo independientes agrupadas como una unidad lógica.
ADO.NET simplifica las transacciones locales a través del método BeginTransaction de su clase Connection y ofreciendo clases Transaction específicas al proveedor de bases de datos
Sin transacciones en una transferencia bancaria encontraríamos dos problemas: Si el débito tiene éxito pero el crédito no desaparece el dinero. Si otra aplicación consulta los balances de cuenta, justo después
del débito pero antes del crédito, podría encontrar resultados inconsistentes
35/65
Ejemplo Soporte de Transacciones I
SqlTransaction trans = null;SqlConnection conn = new SqlConnection ("server=localhost;database=mybank;uid=sa;pwd=");
try { conn.Open ();
// Start a local transaction trans = conn.BeginTransaction
(IsolationLevel.Serializable);
// Create and initialize a SqlCommand object SqlCommand cmd = new SqlCommand (); cmd.Connection = conn; cmd.Transaction = trans;
36/65
Ejemplo Soporte de Transacciones II
// Debit $1,000 from account 1111 cmd.CommandText = "update accounts set balance = " + "balance - 1000 where account_id = '1111'"; cmd.ExecuteNonQuery ();
// Credit $1,000 to account 2222 cmd.CommandText = "update accounts set balance = " + "balance + 1000 where account_id = '2222'"; cmd.ExecuteNonQuery ();
// Commit the transaction (commit changes) trans.Commit ();}catch (SqlException) { // Abort the transaction (roll back changes) if (trans != null) trans.Rollback ();}finally { conn.Close ();}
37/65
Comandos Parametrizados
SqlConnection conn = new SqlConnection("server=localhost;database=mybank;uid=sa;pwd=");try { conn.Open (); // Create and initialize a SqlCommand object SqlCommand cmd = new SqlCommand ("update accounts set balance = balance + @amount " + "where account_id = @id", conn); cmd.Parameters.Add ("@amount", SqlDbType.Money); cmd.Parameters.Add ("@id", SqlDbType.Char);
// Debit $1,000 from account 1111 cmd.Parameters["@amount"].Value = -1000; cmd.Parameters["@id"].Value = "1111"; cmd.ExecuteNonQuery ();
// Credit $1,000 to account 2222 cmd.Parameters["@amount"].Value = 1000; cmd.Parameters["@id"].Value = "2222"; cmd.ExecuteNonQuery ();}catch (SqlException ex) { // TODO: Handle the exception}finally { conn.Close ();}
38/65
Procedimientos Almacenados
Con SqlCommand y OleDbCommand se puede invocar al método Prepare para compilar una sentencia SQL y reutilizarla luego N veces Hay un mejor mecanismo para ejecutar consultas que se repiten mucha
veces PROCEDIMIENTOS ALMACENADOS Un procedimiento almacenado es un comando definido por el usuario
y añadido a la base de datos. Se ejecutan más rápidamente que las sentencias SQL dinámicas porque
ya están compilados Similar al efecto código compilado vs. código interpretado
39/65
Ejemplo 1 Procedimientos Almacenados I
CREATE PROCEDURE proc_TransferFunds @Amount money, @From char (10), @To char (10)AS BEGIN TRANSACTION UPDATE Accounts SET Balance = Balance - @Amount WHERE Account_ID = @From IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION RETURN END UPDATE Accounts SET Balance = Balance + @Amount WHERE Account_ID = @To IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION RETURN END COMMIT TRANSACTIONGO
40/65
Ejemplo 1 Procedimientos Almacenados II
Así es como se puede invocar desde ADO.NET al procedimiento almacenado:
SqlConnection conn = new SqlConnection ("server=localhost;database=mybank;uid=sa;pwd=");
try { conn.Open (); SqlCommand cmd = new SqlCommand ("proc_TransferFunds", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add ("@amount", 1000); cmd.Parameters.Add ("@from", 1111); cmd.Parameters.Add ("@to", 2222); cmd.ExecuteNonQuery ();}catch (SqlException ex) { // TODO: Handle the exception}finally { conn.Close ();}
41/65
Ejemplo2 Procedimientos Almacenados I
Este ejemplo ilustra como recuperar los resultados devueltos por un procedimiento almacenado:
CREATE PROCEDURE proc_GetBalance @ID char (10), @Balance money OUTPUTAS SELECT @Balance = Balance FROM Accounts WHERE Account_ID
= @ID IF @@ROWCOUNT = 1 RETURN 0 ELSE BEGIN SET @Balance = 0 RETURN -1 ENDGO
42/65
Ejemplo2 Procedimientos Almacenados II
SqlConnection conn = new SqlConnection ("server=localhost;database=mybank;uid=sa;pwd=");try { SqlCommand cmd = new SqlCommand ("proc_GetBalance", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add ("@id", 1111);
SqlParameter bal = cmd.Parameters.Add ("@balance", SqlDbType.Money); bal.Direction = ParameterDirection.Output;
SqlParameter ret = cmd.Parameters.Add ("@return", SqlDbType.Int); ret.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery ();
int retval = (int) ret.Value; decimal balance = (decimal) bal.Value;}catch (SqlException ex) { // TODO: Catch the exception}finally { conn.Close ();}
43/65
DataSets y DataReaders
Los DataReader son orientados al stream, read y forward only.
Los accesos basados en conjuntos (Set-based data access) capturan una consulta entera en memoria y soportan moverte hacia delante y atrás e incluso modificar el resultado
System.Data.DataSet es el equivalente a una base de datos en memoria
DataAdapter sirve como un puente entre DataSets y fuentes de datos físicas
44/65
System.Data.DataSet
Un DataSet es una base de datos en memoria Los datos en un DataSet se guardan en objetos DataTable
La propiedad DataSet.Tables expone las tablas en un DataSet Los registros en un DataTable son representados por objetos
DataRow y los campos por DataColumn Las propiedades de DataTable Rows y Columns exponen las
colecciones DataRows y DataColumns que constituyen la tabla Las restricciones de columnas son reflejadas en la propiedad
Constraints La propiedad Relation de un DataSet mantiene una colección de
objetos DataRelation, cada uno correspondiendo a una relación entre dos tablas
La propiedades que distinguen a un DataReader de un DataSet son: Soporta acceso directo a los registros (no secuencial) Los cambios en un DataSet se pueden propagar a la base de datos Los DataSets permiten cachear datos, ideales para aplicaciones web
45/65
System.Data.DataSet
46/65
DataSet vs. DataReader
Si pretendes consultar una base de datos y leer los registros uno a uno hasta que encuentras el que buscabas, entonces un DataReader es la herramienta ideal
Si pretendes acceder a todos los resultados, necesitas la habilidad de iterar para adelante y atrás a través de un resultado, o si quieres cachear resultados en memoria, utiliza DataSet
Muchos controles web o de formularios que permiten asociar un DataSet también permiten asociar un DataReader:
DataSet ds = new DataSet ();// TODO: Initialize the DataSetMyDataGrid.DataSource = ds;MyDataGrid.DataBind ();
SqlDataReader reader = cmd.ExecuteReader ();MyDataGrid.DataSource = reader;MyDataGrid.DataBind ();
47/65
DataAdapter
Aunque se pueden construir DataSets en memoria, normalmente estos son inicializados a partir de consultas a bases de datos o documentos XML Sin embargo, los DataSets no interactúan con las bases de datos
directamente, lo hacen a través de DataAdapters El propósito de un DataAdapters es realizar una consulta y
crear a partir de ella objetos DataTable que contienen el resultado Un DataAdapter deriva de
System.Data.Common.DbDataAdapter y es especifíco al proveedor ADO.NET: SqlDataAdapter o OleDbDataAdapter
Proporciona dos métodos principales: Fill y Update
48/65
DataAdapter.Fill
SqlDataAdapter adapter = new SqlDataAdapter ("select * from titles", "server=localhost;database=pubs;uid=sa;pwd=");
DataSet ds = new DataSet ();adapter.Fill (ds, "Titles"); Las acciones realizadas por este código son:
Fill abre una conexión a la BD Pubs usando el string de conexión facilitado Realiza una consulta en la base de datos Pubs usando el string de consulta pasado
al constructor de SqlDataAdapter. Crear un objeto DataTable llamado “Titles” en el DataSet. Inicializa DataTable con un schema correspondiente a la tabla “Titles” en la BD. Recupera todos los registros producidos por la consulta y los escribe a la
DataTable. Cierra la conexión
Un DataSet puede utilizarse N veces, para limpiar DataTables antiguas simplemente invocar a DataSet.Clear
49/65
Manejando DataTable
Listar los nombres de las tablas contenidas en un DataSet:foreach (DataTable table in ds.Tables) Console.WriteLine (table.TableName); Listar el contenido de la primera tabla contenida en un DataSet:DataTable table = ds.Tables[0];foreach (DataRow row in table.Rows) Console.WriteLine (row[0]); Listar el contenido de columna title de la primera tabla contenida en
un DataSet:DataTable table = ds.Tables[0];foreach (DataRow row in table.Rows) Console.WriteLine (row[“title"]); Listar el nombre y tipo de las columnas en la primera tabla de un
DataSet:DataTable table = ds.Tables[0];foreach (DataColumn col in table.Columns) Console.WriteLine ("Name={0}, Type={1}", col.ColumnName, col.DataType);
50/65
Realizando cambios con DataAdapter.UpdateSqlDataAdapter adapter = new SqlDataAdapter ("select * from titles", "server=localhost;database=pubs;uid=sa;pwd=");
SqlCommandBuilder builder = new SqlCommandBuilder (adapter);DataSet ds = new DataSet ();adapter.Fill (ds, "Titles");
// Insert a recordDataTable table = ds.Tables["Titles"];DataRow row = table.NewRow ();row["title_id"] = "JP1001";row["title"] = "Programming Microsoft .NET";row["price"] = 59.99m;row["ytd_sales"] = 1000000;row["type"] = "business";row["pubdate"] = new DateTime (2002, 5, 1);table.Rows.Add (row);// Update the databaseadapter.Update (table);
51/65
Realizando cambios con DataSet.Update
Se pueden conseguir deltas de las modificaciones de tablas usando el siguiente código:
// Update the databaseDataTable deletes = table.GetChanges (DataRowState.Deleted);adapter.Update (deletes);DataTable inserts = table.GetChanges (DataRowState.Added);adapter.Update (inserts); SqlCommandBuilder builder = new SqlCommandBuilder
(adapter); Si se omite esta sentencia la invocación a Update lanza una excepción. Un
DataAdapter tiene cuatro propiedades que controlan su comunicación con un BD: SelectCommand, encapsula los comandos para realizar queries InsertCommand, comandos para insertar filas UpdateCommand, para actualizar filas DeleteCommand, para borrarlas
Cuando se crea un adapter se inicializa la propiedad SelectCommand pero las demás se inicializan a null, por eso es necesario utilizar el CommandBuilder
52/65
Seleccionando Registros
Algunos ejemplos de Select son:DataRow[] rows = table.Select ("title_id = 'JP1001'");
DataRow[] rows = table.Select ("price < 10.00");
DataRow[] rows = table.Select ("pubdate >= '#1/1/2000#'");
DataRow[] rows = table.Select ("state in ('ca', 'tn', 'wa')");
DataRow[] rows = table.Select ("state like 'ca*'");
DataRow[] rows = table.Select ("isnull (state, 0) = 0");
DataRow[] rows = table.Select ("state = 'tn' and zip like '37*'");
53/65
La clase DataView<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><html> <body> <form runat="server"> <asp:DataGrid ID="MyDataGrid" RunAt="server" /> </form> </body></html><script language="C#" runat="server"> void Page_Load (object sender, EventArgs e) { SqlDataAdapter adapter = new SqlDataAdapter ("select * from titles", "server=<server-name>;database=pubs;uid=sa;pwd=<password>");
DataSet ds = new DataSet (); adapter.Fill (ds, "Titles");
DataView view = new DataView (ds.Tables["Titles"]); view.Sort = "title ASC"; MyDataGrid.DataSource = view; MyDataGrid.DataBind (); }</script>
54/65
ADO.NET y XML
ADO.NET ofrece un buen soporte de XML El método ReadXml de un DataSet permite transformar un fichero
XML en un DataSet:DataSet ds = new DataSet ();ds.ReadXml ("Rates.xml");
Luego podríamos iterar sobre el contenido del fichero XML usando los métodos de un DataSet:foreach (DataRow row in ds.Tables[0].Rows) Currencies.Items.Add (row["Currency"].ToString ());
ReadXml es complementado por el método WriteXml. Una buena manera de crear ficheros XML es crear un DataSet y
luego escribir el contenido con un WriteXml. WriteXml convierte datos relacionales en XML Se puede usar un DataAdapter para inicializar un DataSet con una
consulta a una BD y escribir los resultados a un fichero XML con WriteXml
55/65
ADO.NET y Access
En el Web.config se podría declarar el ConnString:<appSettings>
<add key="ConnString“ value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\
temp\\votacionesud\\BaseDatos\\basedatos.mdb"/> </appSettings>
Luego se podría consultar esa fuente de datos del siguiente modo:private string strConexion =
System.ConfigurationSettings.AppSettings["ConnString"];OleDbConnection con = new OleDbConnection(strConexion);con.Open();string query = "SELECT * FROM Elector";OleDbDataAdapter oda = new OleDbDataAdapter(query, con);DataSet ds = new DataSet();oda.Fill(ds,"Elector"); // ds.Tables[0];con.Close();
56/65
Controles de WebMatrix
WebMatrix provee controles de acceso a datos correspondientes a los diferentes proveedores vistos AccessDataSourceControl
Permite trabajar con Access exclusivamente SqlDataSourceControl
Por ejemplo, para conseguir un conjunto de datos a partir de una tabla Access haríamos:<wmx:AccessDataSourceControl id="AccessDS" runat= "server"
SelectCommand= "SELECT * FROM Libros" ConnectionString= "Provider=Mmicrosft.Jet.OLDEB.4.0; Ole DB Services=-4; Data Source=\Mis documentos\Libros.mdb">
Para usarlo desde Visual Studio.NET necesitamos instalar el ensamblado Microsoft.Matrix.Framework
57/65
Nuevo en ADO.NET 2.0
Procesamiento asíncrono Multiple active result sets más de un SqlDataReader abierto en
una conexión Permite recepción de notificaciones de SqlServer Control de Pools programáticos
Métodos ClearAllPools y ClearPool Nuevo tipo de datos XML Nuevo tipo DataTableReader Serialización binaria de DataSets Más detalles en:
http://msdn2.microsoft.com/en-us/library/ex6y04yf(vs.80).aspx
58/65
Declarative Connection String
<connectionStrings> <add name="Pubs" connectionString="Server=(local)\
SQLExpress;Integrated Security=True;Database=pubs;Persist Security Info=True" providerName="System.Data.SqlClient" />
<add name="Northwind" connectionString="Server=(local)\SQLExpress;Integrated Security=True;Database=Northwind;Persist Security Info=True“ providerName="System.Data.SqlClient" />
</connectionStrings>
59/65
Funcionalidad de Ejecución Asíncrona
La adición de un API asíncrona permite escenarios donde es importante para una aplicación continuar su ejección sin esperar a que las operaciones de BBDD concluyan
Se hace de manera puramente asíncrona, sin hilos de background bloqueados para que una operación de IO concluya, usan overlapped IO y las facilidades de compleción de puertos de entrada y salida
Un escenario interesante para la ejecución asíncrona de comandos es la ejecución de varias sentencias SQL en paralelo, bien contra el mismo o otra servidor de bases de datos.
60/65
Funcionalidad de Ejecución Asíncrona
Métodos Síncronos
Métodos ASíncronos
Parte “Begin” Parte “End”
ExecuteNonQuery BeginExecuteNonQuery EndExecuteNonQuery
ExecuteReader BeginExecuteReader EndExecuteReader
ExecuteXmlReader BeginExecuteXmlReader EndExecuteXmlReader
61/65
Funcionalidad de Ejecución Asíncrona
Ejemplo:IAsyncResult ar = command.BeginExecuteReader(); // do other processing SqlDataReader r = command.EndExecuteReader(ar); // use the reader and then close it and connection
Para usar comandos asíncronos, las conexiones deben ser inicializadas con el flag async=true
Más información en: http://msdn2.microsoft.com/en-us/library/ms379553(VS.80).aspx
62/65
Ejemplo
public class AsyncOrders : IHttpAsyncHandler{ protected SqlCommand _cmd; protected HttpContext _context; // asynchronous execution support is split between // BeginProcessRequest and EndProcessRequest
public IAsyncResult BeginProcessRequest(HttpContext context, AsyncCallback cb, object extraData) { // get the ID of the customers we need to list the orders for // (it's in the query string) string customerId = context.Request["customerId"]; if(null == customerId) throw new Exception("No customer ID specified"); // obtain the connection string from the configuration file string connstring = ConfigurationSettings.AppSettings["ConnectionString"]; // connect to the database and kick-off the query SqlConnection conn = new SqlConnection(connstring); try { conn.Open(); // we use an stored-procedure here, but this could be any statement _cmd = new SqlCommand("get_orders", conn); _cmd.CommandType = CommandType.StoredProcedure; _cmd.Parameters.AddWithValue("@ID", customerId); // begin execution of the command. This method will return post // the query // to the database and return without waiting for the results // NOTE: we are passing to BeginExecuteReader the callback // that ASP.NET passed to us; so ADO.NET will call cb directly // once the first database results are ready. You can also use // your own callback and invoke the ASP.NET one as appropiate
63/65
Ejemplo
IAsyncResult ar = _cmd.BeginExecuteReader(cb, extraData); // save the HttpContext to use it in EndProcessRequest _context = context; // we're returning ADO.NET's IAsyncResult directly. a more // sophisticated application might need its own IAsyncResult // implementation return ar; }
catch { // only close the connection if we find a problem; otherwise, we'll // close it once we're done with the async handler conn.Close(); throw; } } // ASP.NET will invoke this method when it detects that the async // operation finished public void EndProcessRequest(IAsyncResult result) { try { // obtain the results from the database SqlDataReader reader = _cmd.EndExecuteReader(result); // render the page RenderResultsTable(_context, "Orders (async mode)", reader); } finally { // make sure we close the connection before returning from // this method _cmd.Connection.Close(); _cmd = null; } } // rest of AsyncOrders members // ...}
64/65
Ejemplo DataTableReader
private static void TestCreateDataReader(DataTable dt){ // Given a DataTable, retrieve a DataTableReader // allowing access to all the tables' data: using (DataTableReader reader = dt.CreateDataReader()) { do { if (!reader.HasRows) { Console.WriteLine("Empty DataTableReader"); } else { PrintColumns(reader); } Console.WriteLine("========================"); } while (reader.NextResult()); }}
65/65
Ejemplo DataTableReaderprivate static DataTable GetCustomers(){ // Create sample Customers table, in order // to demonstrate the behavior of the DataTableReader. DataTable table = new DataTable();
// Create two columns, ID and Name. DataColumn idColumn = table.Columns.Add("ID", typeof(int)); table.Columns.Add("Name", typeof(string));
// Set the ID column as the primary key column. table.PrimaryKey = new DataColumn[] { idColumn };
table.Rows.Add(new object[] { 1, "Mary" }); table.Rows.Add(new object[] { 2, "Andy" }); table.Rows.Add(new object[] { 3, "Peter" }); table.Rows.Add(new object[] { 4, "Russ" }); return table;}
private static void PrintColumns(DataTableReader reader){ // Loop through all the rows in the DataTableReader while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.Write(reader[i] + " "); } Console.WriteLine(); }}