“Programación de Consultas SQL – ADO.Net”
LDP3501-2011 / DUOC-AV
En esta guía:
Clases de Acceso a datos ADO.Net en System.Data.SqlClient... 1
SqlConnection ... 1
SqlCommand ... 2
SqlDataAdapter ... 3
Controles de origen y enlace de datos en ASP.Net... 5
Controles de origen de datos ... 5
Controles enlazados a datos ... 6
Establecer enlaces a bases de datos ... 7
Control SqlDataSource ... 7
Clases de Acceso a datos ADO.Net en System.Data.SqlClient
SqlConnection
Un objeto SqlConnection representa una única sesión con un origen de datos de SQL Server. Con un sistema de bases de datos de cliente y servidor, equivale a una conexión de red al servidor. SqlConnection se utiliza junto con SqlDataAdapter y SqlCommand para aumentar el rendimiento de la conexión a una base de datos de Microsoft SQL Server. Con productos SQL Server de otros proveedores y otros orígenes de datos compatibles con OLE DB, se debe utilizar OleDbConnection.
Cuando se crea una instancia de SqlConnection, se establecen todas las propiedades en sus valores iniciales. Si la conexión SqlConnection está fuera de ámbito, permanecerá abierta. Por tanto, debe cerrar la conexión explícitamente mediante una llamada al método Close o Dispose. Close y Dispose tienen una funcionalidad equivalente. Si el valor de agrupación de conexiones Pooling se establece en true o yes, la conexión subyacente se devuelve al grupo de conexiones. Por otro lado, si Pooling se establece en false o no, la conexión subyacente al servidor se cerrará.
Para garantizar que las conexiones se cierren siempre, abra la conexión dentro de un bloque using, como se muestra en el fragmento de código siguiente. Haciendo esto se garantiza que la conexión se cierra automáticamente cuando el código sale del bloque.
private static void OpenSqlConnection() {
string connectionString = GetConnectionString();
using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); Console.WriteLine("ServerVersion: {0}", connection.ServerVersion); Console.WriteLine("State: {0}", connection.State); } }
static private string GetConnectionString() {
// Para evitar almacenar el string de conexión en el código
// es posible recuperarlo desde lel archivo de configuración, mediante la // propiedad System.Configuration.ConfigurationSettings.AppSettings return "Data Source=(local);Initial Catalog=AdventureWorks;"
+ "Integrated Security=SSPI;"; }
Si el método que ejecuta un objeto SqlCommand genera una excepción SqlException, SqlConnection permanece abierta cuando el nivel de gravedad es igual o menor que 19. Si el nivel de gravedad es igual o mayor que 20, normalmente, el servidor cierra la conexión SqlConnection. Sin embargo, el usuario puede volver a abrir la conexión y continuar.
Una aplicación que crea una instancia del objeto SqlConnection puede requerir que todos los llamadores directos e indirectos tengan permisos suficientes en el código estableciendo solicitudes de seguridad declarativas o imperativas. SqlConnection realiza solicitudes de seguridad utilizando el objeto
SqlClientPermission. Los usuarios pueden comprobar que el código tiene los permisos adecuados mediante el objeto SqlClientPermissionAttribute.
SqlCommand
Representa un procedimiento almacenado o una instrucción de Transact-SQL que se ejecuta en una base de datos de SQL Server. Cuando se crea una instancia de SqlCommand, las propiedades de lectura y escritura se establecen en sus valores iniciales. Para obtener una lista de esos valores, vea el constructor SqlCommand. SqlCommand proporciona los siguientes métodos de ejecución de comandos en una base de datos de SQL Server:
Métodos Descripción
ExecuteReader Ejecuta comandos que devuelven filas. Para obtener un mayor
rendimiento, ExecuteReader invoca a los comandos mediante el procedimiento almacenado en el sistema sp_executesql de Transact-SQL. Por consiguiente, ExecuteReader puede no tener el efecto deseado si se utiliza para ejecutar comandos como las instrucciones SET de Transact-SQL.
ExecuteNonQuery Ejecuta comandos como instrucciones INSERT, DELETE, UPDATE y SET de
Transact-SQL.
ExecuteScalar Recupera un único valor (por ejemplo, un valor agregado) de una base de
datos.
ExecuteXmlReader Envía CommandText a Connection y crea un objeto XmlReader.
Se puede restablecer el valor de la propiedad CommandText y volver a utilizar el objeto SqlCommand. Sin embargo, es necesario cerrar SqlDataReader para poder ejecutar un comando nuevo o previo.
Si el método que ejecuta un objeto SqlCommand genera una excepción SqlException, SqlConnection permanece abierta cuando el nivel de gravedad es igual o menor que 19. Si el nivel de gravedad es igual o mayor que 20, normalmente, el servidor cierra la conexión SqlConnection. Sin embargo, el usuario puede volver a abrir la conexión y continuar.
En el siguiente ejemplo, se crean una conexión SqlConnection, un objeto SqlCommand y un SqlDataReader. Posteriormente, se leen los datos y se escriben en la consola. Por último, en el ejemplo se cierra SqlDataReader y, a continuación, SqlConnection.
private static void ReadOrderData(string connectionString) {
string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;"; using (SqlConnection connection = new SqlConnection(
connectionString)) {
SqlCommand command = new SqlCommand(queryString, connection); connection.Open();
try { while (reader.Read()) { Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1])); } } finally {
/* Siempre se debe cerrar el Reader */ reader.Close();
} } }
SqlDataAdapter
Representa un conjunto de comandos de datos y una conexión de base de datos que se utilizan para rellenar un DataSet y actualizar una base de datos de SQL Server.
SqlDataAdapter, se utiliza como un puente entre DataSet y SQL Server para recuperar y guardar datos. SqlDataAdapter proporciona este puente mediante la asignación de Fill, que cambia los datos en DataSet para que coincidan con los datos del origen de datos; y Update, que cambia los datos en el origen de datos para que coincidan con los datos en DataSet utilizando las instrucciones de Transact-SQL en el origen de datos adecuado.
Cuando SqlDataAdapter rellene un objeto DataSet, creará las tablas y columnas necesarias para los datos devueltos si todavía no existen. Sin embargo, la información de clave principal no se incluirá en el esquema creado implícitamente a menos que la propiedad MissingSchemaAction se establezca en AddWithKey. También se puede hacer que SqlDataAdapter cree el esquema de DataSet, incluida la información de clave principal, antes de rellenarlo de datos mediante el método FillSchema.
SqlDataAdapter se utiliza junto con SqlConnection y SqlCommand para aumentar el rendimiento en la conexión con una base de datos de SQL Server. SqlDataAdapter contiene también las propiedades SelectCommand, InsertCommand, DeleteCommand, UpdateCommand y TableMappings para facilitar la carga y la actualización de los datos. Cuando se crea una instancia de SqlDataAdapter, las propiedades de lectura y escritura se establecen en sus valores iniciales.
En el ejemplo siguiente se utilizan SqlCommand, SqlDataAdapter y SqlConnection para seleccionar registros de una base de datos, y se rellena DataSet con las filas seleccionadas. A continuación, se devuelve el DataSet rellenado. Para ello, al método se le pasan un DataSet inicializado, una cadena de conexión y una cadena de consulta que es una instrucción SELECT de Transact-SQL.
private static DataSet SelectRows(DataSet dataset, string connectionString,string queryString) {
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand(
queryString, connection); adapter.Fill(dataset);
return dataset; }
Controles de origen y enlace de datos en ASP.Net
ASP.NET incluye dos tipos de controles de servidor que participan en el modelo de enlace de datos declarativo: controles de origen de datos y controles enlazados a datos. Estos controles administran las tareas subyacentes requeridas por el modelo Web sin estado para mostrar y actualizar datos en páginas Web ASP.NET. Por tanto, no es estrictamente necesario conocer los detalles del ciclo de vida de la solicitud de página si sólo se va a realizar el enlace de datos.
Controles de origen de datos
Los controles de origen de datos son controles ASP.NET que administran las tareas de conexión a un origen de datos y de lectura y escritura de datos. Los controles de origen de datos no representan ninguna interfaz de usuario, sino que actúan como intermediarios entre un almacén de datos en particular (como una base de datos, un objeto comercial o un archivo XML) y los demás controles de la página Web ASP.NET. Los controles de origen de datos habilitan un amplio conjunto de funciones para recuperar y modificar datos, entre las que se incluyen la consulta, la ordenación, la paginación, el filtrado, la actualización, la eliminación y la inserción. ASP.NET incluye los controles de origen de datos siguientes:
Control Descripción
ObjectDataSource Permite trabajar con un objeto comercial u otra clase y crear aplicaciones
Web basadas en objetos de nivel medio para administrar los datos.
SqlDataSource Permite trabajar con proveedores de datos administrados de ADO.NET,
que proporcionan acceso a bases de datos de Microsoft SQL Server, OLE DB, ODBC u Oracle.
AccessDataSource Permite trabajar con una base de datos de Microsoft Access.
XmlDataSource Permite trabajar con un archivo XML, que es especialmente útil para
controles de servidor ASP.NET jerárquicos tales como el control TreeView o Menu.
SiteMapDataSource Se utiliza con la exploración del sitio ASP.NET. Para obtener más
información, vea Información general sobre la exploración del sitio de ASP.NET.
Los controles de origen de datos también se pueden ampliar para admitir proveedores de almacenamiento y acceso a datos adicionales.
Controles enlazados a datos
Los controles enlazados a datos representan datos como HTML al explorador que realizó la solicitud. Un control enlazado a datos se puede enlazar a un control de origen de datos y buscar datos automáticamente en el momento apropiado del ciclo de vida de la solicitud de página. Los controles enlazados a datos pueden aprovechar las ventajas de las funciones proporcionadas por un control de origen de datos entre las que se incluyen la ordenación, la paginación, el almacenamiento en caché, el filtrado, la actualización, la eliminación y la inserción. Un control enlazado a datos establece una conexión con un control de origen de datos a través de su propiedad DataSourceID.
ASP.NET incluye los controles enlazados a datos que se describen en la tabla siguiente:
Control Descripción
Controles de lista Representa los datos en una variedad de formato de listas. Entre los
controles de lista se incluyen los controles BulletedList, CheckBoxList, DropDownList, ListBox y RadioButtonList
AdRotator Representa los anuncios de una página como una imagen en la que los
usuarios pueden hacer clic para ir a una dirección URL asociada al anuncio.
DataList Representa los datos en una tabla. Cada elemento se representa utilizando
una plantilla de elemento definida por el usuario.
DetailsView Muestra un registro cada vez en disposición de tabla y permite editar,
Control Descripción
través de varios registros.
FormView Es similar al control DetailsView, pero permite definir una disposición de
formato libre para cada registro. El control FormView es como un control DataList para un registro único.
GridView Muestra los datos en una tabla e incluye compatibilidad para editar,
actualizar, ordenar y paginar datos sin necesidad de código.
Menu Representa los datos en un menú dinámico jerárquico que puede incluir
submenús.
Repeater Representa los datos en una lista. Cada elemento se representa utilizando
una plantilla de elemento definida por el usuario.
TreeView Representa los datos en un árbol jerárquico de nodos que se pueden
expandir.
Establecer enlaces a bases de datos
Las aplicaciones Web muestran normalmente datos procedentes de una base de datos relacional como Microsoft SQL Server, Microsoft Access, Oracle, o de un almacén de datos de OLE DB u ODBC. Para simplificar la tarea de enlazar un control a los datos de una base de datos, ASP.NET proporciona el control SqlDataSource.
Control SqlDataSource
El control SqlDataSource representa una conexión directa a una base de datos en una aplicación Web. Los controles enlazados a datos como los controles GridView, DetailsView y FormView pueden utilizar el control SqlDataSource para recuperar y modificar datos automáticamente. Se especifican comandos para seleccionar, insertar, actualizar y eliminar datos como parte del control SqlDataSource, y el control realiza estas operaciones automáticamente. No es necesario escribir código (por ejemplo, código de ADO.NET que utiliza clases del espacio de nombres System.Data) para crear una conexión ni especificar comandos para consultar y actualizar una base de datos.
El control SqlDataSource conecta directamente a una base de datos y, por consiguiente, implementa un modelo de datos de dos niveles. Si se necesita enlazar un objeto comercial de nivel medio que realiza recuperaciones y actualizaciones de datos, se puede utilizar el control ObjectDataSource.