Dr. Diego Lz. de Ipiña Gz. de Artaza paginaspersonales.deusto.es/dipina (Personal)

1. XML, Distribución y ComponentesTema 2 – Acceso a Datos ADO.NEThttp://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)

2. Temario • Acceso a datos ADO.NET

3. 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. 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. 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. 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. 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): • 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

8. SqlServer 2005 Express Edition • Para cambiar la password del usuario ‘sa’ haga lo siguiente: • 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>

9. Configuración SQLEXPRESS • Para cambiar la password del usuario ‘sa’ haga lo siguiente: • 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>

10. 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: • osql –E • sp_password @old = null, @new = ‘distributed.net', @loginame ='sa‘ • go

11. 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: • 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.

12. 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

13. 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. • 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. 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. 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. 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 (); } } }

17. Tabla Titles de Pubs

18. 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. 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. 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=");

21. 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. 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. 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. 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. 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. 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>");

27. 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. 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> <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. 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 (); } } }

30. 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. El método ExecuteReader II • Para recuperar los metadatos de una relación se pueden usar los métodos: • GetSchemaTable • GetFieldType y • GetDataTypeName

32. 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=<server-name>;database=pubs;uid=sa;pwd=<password>"); try { conn.Open ();

33. Ejemplo ExecuteReader II 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 son:"); 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 (); } } }

34. 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. 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. 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. 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. 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. 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

40. 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. 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

42. 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. 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. 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 DataTableRows 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. System.Data.DataSet

46. 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 ();

47. 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. 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. 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. 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);