Transcript MoreLab - Mobility Research Lab

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 Artaza
http://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)
Temario

Acceso a datos ADO.NET
2/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
3/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=6ccd84271017-4f33-a062-d165078e32b1&displaylang=en
4/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
5/65
Clases Genéricas de Acceso a
Datos

Con independencia del proveedor utilizado podemos
acceder a los datos mediante las siguientes clases:





El puente de conexión entre estas clases genéricas y las
anteriores es un adaptador de datos


DataSet
DataTable
DataRow
DataColumn
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.
6/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


Network configuration/protocols for SQLEXPRESS



En Service StartMode  automatic
Enabled  YES
IP Addresses  Active, Enabled (yes), TCP Dynamic Ports (0)
Permitir modo de autenticación mixed (por defecto, sólo modo Windows):
1.
2.
3.
4.
5.
6.
7.
Arrancar panel de control escribiendo ‘control’ en cmd
Abrir SQL Configuration Manager (parar SQL Server y SQL Server Browser)
Abir regedit
Localizar HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSqlServer
Hacer doble clic sobre LoginMode
Cambiar el valor a 2 (antes 1)
Rearrancar SQLServer y Browser
7/65
SqlServer 2005 Express Edition

Para cambiar la password del usuario ‘sa’ haga lo
siguiente:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
osql -E -S "localhost\SQLEXPRESS“ O sqlcmd SDIPINA-6400\SQLEXPRESS –E
C:\Documents and Settings\Diego López de
Ipiña>osql -E -S localhost\SQLEXPRESS
1> alter login sa enable
2> alter login sa with password='distributed.net'
3> go
1> exit
C:\Documents and Settings\Diego López de
Ipiña>osql -Usa -S localhost\SQLEXPRESS
Password:
1> exit
C:\Documents and Settings\Diego López de Ipiña>
8/65
Configuración SQLEXPRESS

Para cambiar la password del usuario ‘sa’ haga lo siguiente:
1.
2.
3.
4.
5.
6.
7.
8.
9.
osql -E -S "localhost\SqlExpress“ O sqlcmd -SDIPINA-6400\SQLEXPRESS –
E
C:\Documents and Settings\Diego López de Ipiña>osql -E -S
localhost\SQLEXPRESS
1> alter login sa enable
2> alter login sa with password='distributed.net'
3> go
1> exit
C:\Documents and Settings\Diego López de Ipiña>osql -Usa -S
localhost\SQLEXPRESS
Password:
1> exit
10. C:\Documents
and Settings\Diego López de Ipiña>
9/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/enus/coprompt/cp_osql_1wxl.asp
Para cambiar la password del usuario ‘sa’ haga lo siguiente:
osql –E
sp_password @old = null, @new = ‘distributed.net',
@loginame ='sa‘
3. go
1.
2.
10/65
Instalando MSDE y las Bases de
Datos de Ejemplos II

Si al hacer login con osql -U sa recibe:


Debe cambiar el modo de autenticación de Windows a Mixed Mode:


Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection
Por defecto su valor es 1 para autenticación Windows. Para Mixed Mode el valor es 2.
Pasos a seguir:
1.
2.
3.
4.
5.
6.
7.
Arrancar panel de control escribiendo ‘control’ en cmd
Parar MSSQLSERVER y SQLSERVERAgent
Abir regedt32
Localizar HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer o
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance
Name>\MSSQLServer\
Hacer doble clic sobre LoginMode
Cambiar el valor a 2
Rearrancar MSSQLSERVER y SQLSERVERAgent para que el cambio tome efecto.
11/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-46A08DA2-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
o
Osql –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
12/65
Ayuda MSDE
(ReadmeMSDE2000A.htm)

To install a new instance of Desktop Engine
Open a command prompt window.
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:
1.
2.




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"
13/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
14/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
15/65
Ejemplo SqlReader:
ListTitlesSQLProvider.cs
using 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 ();
}
}
}
16/65
Tabla Titles de Pubs
17/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=<nombreservidor>;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 ();
}
}
}
18/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=distribu
ted.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");
19/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 DB
OleDbConnection 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 usando
OleDbConnection conn = new OleDbConnection
("provider=sqloledb;data source=localhost;" +
"initial catalog=pubs;user id=sa;password=");
20/65
Abriendo y Cerrando Conexiones

En SQLServer se usaría el siguiente código:
SqlConnection conn = new SqlConnection
base=pubs;uid=sa;pwd=");
conn.Open ();

("server=localhost;data
En cualquier otra base de datos que soporte OLE DB:
OleDbConnection conn = new
OleDbConnection("provider=sqloledb;server=localhost;database=pu
bs;uid=sa;pwd=");
try {
conn.Open ();
// TODO: Use the connection
}
catch (OleDbException ex) {
// TODO: Handle the exception
}
finally {
conn.Close ();
}
21/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/
22/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
23/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
24/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
25/65
Recuperando un BLOB con
ExecuteScalar I
// file
: RetrievingBLOB.cs
// compile : csc RetrievingBLOB.cs
using 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>");
26/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 ();
}
}
}
27/65
Insertando un BLOB I
// file
: StoringBLOB.cs
// compile : csc StoringBLOB.cs
using 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> <ficheroimportar-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 ();
28/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 exception
Console.WriteLine("Excepción lanzada: " + ex.Message);
}
finally
{
conn.Close ();
}
}
}
29/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
30/65
El método ExecuteReader II

Para recuperar los metadatos de una relación se
pueden usar los métodos:
 GetSchemaTable
 GetFieldType
y
 GetDataTypeName
31/65
Ejemplo ExecuteReader I
// file
: ExampleExecuteReader.cs
// compile : csc ExampleExecuteReader.cs
using 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=<servername>;database=pubs;uid=sa;pwd=<password>");
try
{
conn.Open ();
32/65
Ejemplo ExecuteReader II
son:");
SqlCommand cmd = new SqlCommand ("select * from titles", conn);
SqlDataReader reader = cmd.ExecuteReader ();
// Lista todos los campos leidos
while (reader.Read ())
Console.WriteLine (reader["title"]);
// Los nombres de los campos de la tabla leída
Console.WriteLine("\nLos nombres de los campos de la base de datos
for (int i=0; i<reader.FieldCount; i++)
Console.WriteLine (reader.GetName (i));
// Recupera el índice de un campo y luego devuelve sus valores
reader.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 ();
}
}
}
33/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
34/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;
35/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 ();
}
36/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 ();
}
37/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
38/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 TRANSACTION
GO
39/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 ();
}
40/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 OUTPUT
AS
SELECT @Balance = Balance FROM Accounts WHERE
Account_ID = @ID
IF @@ROWCOUNT = 1
RETURN 0
ELSE
BEGIN
SET @Balance = 0
RETURN -1
END
GO
41/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 ();
}
42/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
43/65
System.Data.DataSet


Un DataSet es una base de datos en memoria
Los datos en un DataSet se guardan en objetos DataTable



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



La propiedad DataSet.Tables expone las tablas en un DataSet
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
44/65
System.Data.DataSet
45/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 DataSet
MyDataGrid.DataSource = ds;
MyDataGrid.DataBind ();
SqlDataReader reader = cmd.ExecuteReader ();
MyDataGrid.DataSource = reader;
MyDataGrid.DataBind ();
46/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

47/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
48/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);
49/65
Realizando cambios con
DataAdapter.Update
SqlDataAdapter 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 record
DataTable 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 database
adapter.Update (table);
50/65
Realizando cambios con
DataSet.Update

Se pueden conseguir deltas de las modificaciones de tablas usando el siguiente
código:
// Update the database
DataTable 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,
InsertCommand,
UpdateCommand,
DeleteCommand,
encapsula los comandos para realizar queries
comandos para insertar filas
para actualizar filas
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
51/65
Seleccionando Registros

Algunos ejemplos de Select son:
DataRow[] rows = table.Select
'JP1001'");
DataRow[] rows = table.Select
10.00");
DataRow[] rows = table.Select
'#1/1/2000#'");
DataRow[] rows = table.Select
('ca', 'tn', 'wa')");
DataRow[] rows = table.Select
'ca*'");
DataRow[] rows = table.Select
(state, 0) = 0");
DataRow[] rows = table.Select
'tn' and zip like '37*'");
("title_id =
("price <
("pubdate >=
("state in
("state like
("isnull
("state =
52/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>
53/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
54/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();
55/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
56/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
57/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>
58/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.
59/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
60/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
61/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
62/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
// ...
}
63/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());
}
}
64/65
Ejemplo DataTableReader
private 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
table.Rows.Add(new
table.Rows.Add(new
table.Rows.Add(new
return table;
object[]
object[]
object[]
object[]
{
{
{
{
1,
2,
3,
4,
"Mary" });
"Andy" });
"Peter" });
"Russ" });
}
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();
}
}
65/65