MoreLab - Mobility Research Lab - Deusto

MoreLab - Mobility Research Lab - Deusto

XML, Distribucin y Componentes Tema 2 Acceso a Datos ADO.NET http://paginaspersonales.deusto.es/dipina/MasterISW/ Dr. Diego Lz. de Ipia 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 (Ctedra de Telefnica Mviles) http://www.tecnologico.deusto.es (Tecnolgico-Fundacin Deusto) Temario Acceso a datos ADO.NET 2/65 ADO.NET Programar bsicamente consiste en manejar y manipular datos

Detrs de toda aplicacin 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 Diseado para permitir trabajar en un mundo sin conexiones de la web. Se integra muy bien con XML, facilitando la transformacin 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 travs de mdulos conocidos como proveedores de datos Hay dos proveedores de datos principales:

El proveedor SQL Server .NET, que interacta con Microsoft SQL Server sin ayuda de proveedores unmanaged El proveedor OLE DB .NET que interacta con bases de datos a travs 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) ofrecan 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 Microsofts ODBC .NET driver http://www.microsoft.com/downloads/details.aspx?FamilyID =6ccd8427-1017-4f33-a062-d165078e32b1&displaylang=en 4/65 Proveedores de Datos Cada proveedor aporta clases y estructuras de las cuales

las ms importantes son (precedidas por OleDb, Sql, Oracle u Odbc): Connection establecen la conexin con el origen de datos Command sirven para ejecutar comandos sobre una conexin abierta CommandBuilder genera los comandos de insercin, actualizacin 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 Genricas de Acceso a Datos Con independencia del proveedor utilizado podemos acceder a los datos mediante las siguientes clases:

El puente de conexin entre estas clases genricas y las anteriores es un adaptador de datos DataSet DataTable DataRow DataColumn El DataAdapter genera el DataSet a partir de comandos especficos 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 autenticacin mixed (por defecto, slo 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 Lpez de Ipia>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 Lpez de Ipia>osql -Usa -S localhost\SQLEXPRESS Password: 1> exit C:\Documents and Settings\Diego Lpez de Ipia> 8/65 Configuracin 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 Lpez de Ipia>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 Lpez de Ipia>osql -Usa -S localhost\ SQLEXPRESS Password: 1> exit 10. C:\Documents and Settings\Diego Lpez de Ipia> 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/ Documentacin 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: osql E 2. sp_password @old = null, @new = distributed.net', @loginame ='sa 3. go 1. 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 autenticacin 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 autenticacin 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\\ 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-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 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. 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: 1.

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 cdigo escrito vara:

Nombres de clases Strings de conexin 14/65 Conexiones, Comandos y Data Readers El uso cannico de comandos en ADO.NET es el siguiente: Crear un objeto de conexin (SqlConnection o OleDbConnection) que encapsula un string de conexin Abrir la conexin invocando al mtodo Open del objeto conexin Crear un comando (SqlCommand o OleDbCommand) encapsulando el comando SQL y la conexin usada por el comando Invocar un mtodo en el objeto command Como resultado de invocar un comando se devuelve un DataReader

Cerrar la conexin invocando su mtodo 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="); 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=;database=pubs;uid=sa;pwd="); 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 conexin que abre la base de datos Pubs que viene con SQLServer, usando el nombre de usuario sa y la password vaca: SqlConnection conn = new SqlConnection (); conn.ConnectionString = "server=localhost;database=pubs;uid=sa;pwd=";

De un solo paso, lo mismo podra hacerse: SqlConnection conn = new SqlConnection ("server=localhost\ SQLEXPRESS;database=pubs;uid=sa;pwd=distributed.net"); Las propiedades de un SqlConnection.ConnectionString son: Server=localhost, tambin se puede escribir Server=(local) o Data Source=(local) El parmetro Database o Initial Catalog, identifies la base de datos Uid, o User ID, especifica el nombre de usuario Pwd o Password, la contrasea Hay otros parmetros opcionales, para ms detalles mirar documentacin 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 travs del proveedor OLE DB de .NET El formato de los parmetros utilizados sigue las convenciones de las conexiones OLE DB OleDbConnection conn = new OleDbConnection ("provider=SQLNCLI;server=localhost;database=pubs;uid=sa; pwd="); El parmetro Provider identifica al proveedor de OLE DB usado para interactuar con la base de datos, SQLOLEDB para SQLServer o MSDAORA para Oracle La conexin anterior se podra 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 usara el siguiente cdigo: 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=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 conexin

Si estamos usando una BBDD que no es ni SQL Server podemos encontrar sus strings de conexin 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 podra haber creado de manera ms sencilla: SqlCommand cmd = new SqlCommand ("delete from titles where title_id = 'BU1032'", conn); cmd.ExecuteNonQuery (); // Execute the command 23/65 Mtodo ExecuteNonQuery El mtodo ExecuteNonQuery es un vehculo para ejecutar comandos SQL: INSERT, UPDATE, DELETE (devuelve el nmero 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 Mtodo 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 "); 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=;database=pubs;uid=sa;pwd="); 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 "); 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=;database=pubs;uid=sa;pwd="); 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("Informacin 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("Excepcin lanzada: " + ex.Message); } finally { conn.Close (); } } } 29/65 El mtodo ExecuteReader I El mtodo ExecuteReader existe para slo un

propsito: Realizar consultas de bases de datos y obtener los resultados lo ms rpida y eficientemente posible. ExecuteReader devuelve un objeto DataReader, de nombre SqlDataReader para SqlCommand y OleDbDataReader para OleDbCommand. DataReader tiene mtodos y propiedades que te permiten iterar sobre los resultados Solamente puede leer para adelante 30/65 El mtodo ExecuteReader II Para recuperar los metadatos de una relacin se pueden usar los mtodos: 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=;database=pubs;uid=sa;pwd="); 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 leda Console.WriteLine("\nLos nombres de los campos de la base de datos for (int i=0; i

conn.Close (); } } } 33/65 Transacciones en ADO.NET Las transacciones son operaciones importantes en muchas aplicaciones orientadas a los datos Una transaccin es simplemente dos o ms unidades de trabajo independientes agrupadas como una unidad lgica. ADO.NET simplifica las transacciones locales a travs del mtodo BeginTransaction de su clase Connection y ofreciendo clases Transaction especficas al proveedor de bases de datos Sin transacciones en una transferencia bancaria encontraramos dos problemas: Si el dbito tiene xito pero el crdito no desaparece el dinero.

Si otra aplicacin consulta los balances de cuenta, justo despus del dbito pero antes del crdito, podra 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 mtodo 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 aadido a la base de datos. Se ejecutan ms rpidamente que las sentencias SQL dinmicas porque ya estn compilados Similar al efecto cdigo compilado vs. cdigo 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 atrs 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 fsicas 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 coleccin de objetos DataRelation, cada uno correspondiendo a una relacin 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 atrs a travs de un resultado, o si quieres cachear resultados en memoria, utiliza DataSet Muchos controles web o de formularios que permiten asociar un DataSet tambin 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 interactan con las bases de datos directamente, lo hacen a travs de DataAdapters El propsito 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 especifco al proveedor ADO.NET: SqlDataAdapter o OleDbDataAdapter Proporciona dos mtodos 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 cdigo son: Fill abre una conexin a la BD Pubs usando el string de conexin 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 conexin

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 cdigo: // 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 invocacin a Update lanza una excepcin. Un DataAdapter tiene cuatro propiedades que controlan su comunicacin 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 dems 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" %>

53/65 ADO.NET y XML ADO.NET ofrece un buen soporte de XML El mtodo ReadXml de un DataSet permite transformar un fichero XML en un DataSet: DataSet ds = new DataSet (); ds.ReadXml ("Rates.xml"); Luego podramos iterar sobre el contenido del fichero XML usando los mtodos de un DataSet: foreach (DataRow row in ds.Tables[0].Rows) Currencies.Items.Add (row["Currency"].ToString ());

ReadXml es complementado por el mtodo 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 podra declarar el ConnString: Luego se podra 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 haramos:

Para usarlo desde Visual Studio.NET necesitamos instalar el ensamblado Microsoft.Matrix.Framework 56/65 Nuevo en ADO.NET 2.0 Procesamiento asncrono Multiple active result sets ms de un SqlDataReader abierto en una conexin Permite recepcin de notificaciones de SqlServer Control de Pools programticos

Mtodos ClearAllPools y ClearPool Nuevo tipo de datos XML Nuevo tipo DataTableReader Serializacin binaria de DataSets Ms detalles en: http://msdn2.microsoft.com/en-us/library/ex6y04yf(vs.80).aspx 57/65 Declarative Connection String 58/65

Funcionalidad de Ejecucin Asncrona La adicin de un API asncrona permite escenarios donde es importante para una aplicacin continuar su ejeccin sin esperar a que las operaciones de BBDD concluyan Se hace de manera puramente asncrona, sin hilos de background bloqueados para que una operacin de IO concluya, usan overlapped IO y las facilidades de complecin de puertos de entrada y salida Un escenario interesante para la ejecucin asncrona de comandos es la ejecucin de varias sentencias SQL en paralelo, bien contra el mismo o otra servidor de bases de datos. 59/65 Funcionalidad de Ejecucin Asncrona Mtodos Sncronos Mtodos ASncronos Parte Begin

Parte End ExecuteNonQuery BeginExecuteNonQuery EndExecuteNonQuery ExecuteReader BeginExecuteReader EndExecuteReader ExecuteXmlReader BeginExecuteXmlReader EndExecuteXmlReader 60/65 Funcionalidad de Ejecucin Asncrona 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 asncronos, las conexiones deben ser inicializadas con el flag async=true Ms informacin 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

Recently Viewed Presentations

  • Genetics and Statistics

    Genetics and Statistics

    Genetics and Statistics A Tale of Two Hypotheses ... We call this type of analysis a CHI SQUARE The purpose is to determine whether the results are statistically significant. What are the odds that your tigers are Aa x Aa?...
  • Elasticity - Homepages

    Elasticity - Homepages

    4/12/2002, Friday Fatigue Chapter 12, 13 Fatigue Fence Cyclic Stress Development of Surface Roughness S-N curve Fatigue Crack Growth in Ductile Metals Fatigue Striations Closure Fatigue Crack Advancing Fatigue Crack Shape of Crack Tip Plastic Zone 4/12/2002, Friday Fatigue Fatigue...
  • Engaging Students with Library Materials: How LMS Administrators,

    Engaging Students with Library Materials: How LMS Administrators,

    Interwoven (Deep) IntegrationLearning Tools Interoperability (LTI)Remote Plugins (LTI 2.0)Valence - requires programming skill. ... Lorene Flanders and Carol Goodson, University of West Georgia, Ingram Library. ... PowerPoint Presentation
  • 7th Lecture, STV4346B: &quot;The economic effects of institutions&quot;

    7th Lecture, STV4346B: "The economic effects of institutions"

    7th Lecture, STV4346B: "The economic effects of institutions" Carl Henrik Knutsen, Department of Political Science, UiO 24/11-2008 Some introductory notes Last lecture, we looked at studies that claimed a positive effect on the economy from a complex (of little specified)...
  • The day of the dead is a day

    The day of the dead is a day

    The day of the dead is a day of remembrance in Mexican culture. They do not fear death, they believe it to be a new journey. Food is placed on a table along with other offerings to the dead. On...
  • Rivier University Education Division Specialist in Assessment of

    Rivier University Education Division Specialist in Assessment of

    The Vineland Adaptive Behavior Scale v-scale extends the scaled score measurement downward another 5 points to differentiate among persons with very low ratings because the Vineland is often used with persons who obtain extremely low ratings. The v-scale helpfully subdivides...
  • Lifestyle Improvement Strategies - Pitchero

    Lifestyle Improvement Strategies - Pitchero

    Physical Activity. Get going everyday. 10 min shake up. Help you find local activities. Stepometer app. Alcohol. Choose less booze. Alcohol tracker app. Tips to cut down/alternatives
  • Alignment of Moe Syllabus With Khan Academy Resources for ...

    Alignment of Moe Syllabus With Khan Academy Resources for ...

    NIG. KHAN ACADAMY. Introduction To Chemistry. Introduction to Chemistry. Chemical Industries. Standard Separation Techniques For Mixtures. Particular Nature Of Matter