Elección de la fuente de datos y la manera de obtener la información
Como parte del SQL Server existe un componente llamando SQL DMO (Distribuited Management Objects) mismo que podría servirnos para obtener información de los objetos de la base de datos, entre estos objetos tendríamos a las tablas, sin embargo, a pesar de ser bueno su funcionamiento, no sería una buena opción para la creación de aplicaciones .NET y es por distintas circunstancias.
La primera es que es un objeto COM, tendríamos que valernos de la interoperabilidad (interop) y crear un componente intermedio (wraper) que sirviera de interfase entre nuestra aplicación y el componente COM, tal vez no sea problema o podría justificarse por la funcionalidad que expone, pero, existe un detalle en cuanto a este componente: Requerirá un trato especial para la distribución, ya que al ser un componente COM, debe ser registrado en el sistema operativo y entonces surge la pregunta: ¿Dónde quedó la distribución simple que promete Microsoft con las aplicaciones .NET?, por esta razón, trataremos de evitar su uso sin sacrificar la funcionalidad esperada, y eso trataremos de hacer en este pequeño artículo.
Las aplicaciones que utilizan en la actualidad SQL Server 2000 no requieren más que una conexión de red y los parámetros de autenticación y selección de la base de datos y servidor, que son los que dan origen a la cadena de conexión que se utiliza en los objetos de SQLClient dentro del .NET Framework.
SQL Server 2000 ofrece una gran variedad de herramientas a los desarrolladores para explotar el servidor en cuanto a la información de sus objetos se refiere, basta echar un vistazo a los libros en pantalla para ver la cantidad de recursos que se tienen disponibles. Muchos de estos recursos (si no es que todos) son accesibles por medio de T-SQL (Transaction SQL), lo que nos favorece, ya que cualquier cosa que pueda ser escrito en T-SQL, prácticamente podrá ser ejecutada con objeto creado a partir de System.Data.SqlClient.SqlCommand, claro que existen ciertas instrucciones que no podrán ser ejecutadas, estas instrucciones las define Microsoft en la documentación de SQL Server 2000, como instrucciones propias de las herramientas de Microsoft, tal es el caso de la palabra “GO”, que no es interpretada por las clases de SqlClient. Sin embargo, no hay problemas al ejecutar instrucciones de “DBCC” ni con la ejecución de procedimientos almacenados de sistema.
Es interesante ver la cantidad de opciones que tiene SQL Server, de las cuales se puede tomar ventaja cuando se tiene MSDE (Microsoft SQL Server 2000 Desktop Engine) y no se cuenta con un administrador visual del servidor de bases de datos.
Recursos que ofrece SQL Server 2000
Dentro de SQL Server encontraremos bastantes recursos que nos servirán para distintas tareas, como la obtención de toda la información de los objetos tanto del servidor como de una base de datos dada. Entre estos recursos podemos enumerar:
SP_HELP (Procedimiento almacenado)
SP_HELPDB (Procedimiento almacenado)
INFORMATION_SCHEMA.TABLES (Vista)
INFORMATION_SCHEMA.COLUMNS (Vista)
Estos recursos podemos utilizarlos en VC# o VB.NET usando las clases que se encuentran en el espacio de nombres System.Data.SqlClient, además, para concentrar los resultados podemos utilizar las clases que están en System.Data, por ejemplo DataSet y DataTable. Se puede usar Visual Studio .NET 2003 para el diseño y la programación de la interfase de usuario.
Aunque en este artículo cubriremos solo algunos objetos, estaré escribiendo algunos anexos para completar este artículo con otros ejemplos útiles siguiendo la técnica descrita aquí.
Obtención de bases de datos del servidor
Comenzaremos por obtener las bases de datos que se encuentran en un servidor dado. Esta tarea será muy simple, sin embargo deberemos empezar por definir las condiciones para establecer una conexión con el servidor de base de datos. Para fines prácticos utilizaré una conexión con autenticación de Windows hacia el servidor local (Localhost), es pertinente advertir que esto funciona en sistemas operativos Windows basados en tecnología NT, ya sabemos, Win2K (Windows 2000 Professional, Server), WinXP (Windows XP Home, Professional) y Win2K3 (Windows Server 2003 Web, Standard, Enterprise), también, para este ejemplo utilizaremos MSDE 2000 y la base de datos Northwind que pueden obtener del sitio de Microsoft SQL Server 2000.
Una vez teniendo todo en su lugar, construiremos primeramente la cadena de conexión para utilizarla después con un objeto SqlConnection.
string strCnn = “Integrated Security = SSPI; Server = Localhost; Database = Master”;
Si es necesario utilizar un inicio de sesión de SQL Server 2000, se puede utilizar la autenticación de SQL Server 2000 de la siguiente manera:
string strCnn = “Server = NombreServidor; Database = NombreDB; User = Usuario; Password = Contraseña ”;
En la línea anterior, se deben sustituir las definiciones correspondientes a cada dato en cursivas. Aunque aquí pondremos la información de conexión explícitamente en el código, existen maneras de hacerlo más flexible utilizando un archivo de configuración.
Continuando con el ejemplo, una vez teniendo la definición de la cadena de conexión podemos proseguir con la construcción del objeto SqlConnection que utilizaremos prácticamente en todos los ejemplos. Para tener visibilidad de las clases al nivel de SqlClient y de Data, haremos referencia al espacio de nombres System.Data.SqlClient y al espacio de nombres System.Data utilizando la palabra
using:
using System.Data;
using System.Data.SqlClient;
Esta línea estará en la parte superior de nuestro código. Con esto evitaremos escribir el nombre “largo” de las clases de SqlClient y Data. Continuando con lo que nos ocupa, escribiremos el siguiente código:
string strCnn = “Integrated Security = SSPI; Server = Localhost; Database = Northwind”;
SqlConnection cnn = new SqlConnection(strCnn);
Estas líneas muestran la declaración de una variable “cnn” del tipo SqlConnection, la cual estamos creando pasando la cadena de conexión almacenada en la variable “strCnn” al constructor de la clase. La conexión ha sido creada y está lista para utilizarse.
Pues bien, el interés está puesto en este momento en obtener las bases de datos del servidor dado, en este caso del servidor local (Localhost), y para realizar esta tarea crearemos un método que será el que devuelva como resultado un DataTable conteniendo las bases de datos y toda su información.
private string mensajeRetorno;
public DataTable GetDataBases ()
{
//Declaración de variables requeridas
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable(“DataBases”);
//Asignación de propiedades
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “sp_helpdb”;
da.SelectCommand = cmd;
//Intento de llenado de datos hacia la variable dt “DataTable”
try
{
da.Fill(dt);
}
catch(SqlException ex)
{
mensajeRetorno = “Error: “ + ex.Message;
}
catch(Exception ex)
{
mensajeRetorno = “Error: “ + ex.Message;
}
//Retorno de resultado
return dt;
}
¿Qué intentamos hacer en este método?, pues empezaremos por describir qué es lo que hicimos y entonces sabremos qué pretendemos con este método.
En principio, este método, llamado GetDataBases, devuelve un DataTable con la información de las bases de datos del servidor. En la sección de declaración de variables, declaramos tres, un SqlCommand, que será el que contendrá la información que será pasada a la segunda variable, un SqlDataAdapter, el cual utilizaremos para llenar la variable dt, un DataTable, que será la que devolverá el método para después utilizarla en donde mejor convenga.
En la sección de asignación de propiedades, antes que nada, asignamos la propiedad Connection de la variable cmd con la variable cnn que previamente creamos para establecer la conexión con el servidor SQL Server 2000. Seguido definimos el tipo de SqlCommand que utilizaremos, en este caso, como usaremos un procedimiento almacenado, asignaremos esta propiedad con el valor StoredProcedure del enumerador CommandType. Más adelante veremos otros tipos de SqlCommand y valores de CommandType. La última propiedad que asignamos del SqlCommand es CommandText, esta propiedad recibe la cadena de la instrucción T-SQL que utilizaremos.
Observación:
SqlCommand puede hacer uso de un procedimiento almacenado de SQL Server 2000 de dos maneras distintas.
Es equivalente hacer:
cmd.CommandType = CommandType.Text;
cmd.CommandText = “Exec sp_helpdb”;
en lugar de:
cmd.CommandType = CommandType.StoreProcedure;
cmd.CommandText = “sp_helpdb”;
Puede utilizarse el equivalente en este caso y no verse afectado el rendimiento de la aplicación, sin embargo, con procedimientos almacenados que requieren paso de parámetros o que devuelven valores en parámetros de salida, no sería muy buena práctica sustituir la manera en que lo hacemos ahora por la otra equivalente ya que podríamos vernos en dificultades escribiendo código de más o afectando el rendimiento, así pues, mejor por buenas prácticas, lo haremos como lo pusimos inicialmente.
Le asignamos la propiedad SelectCommand al SqlDataAdapter con el SqlCommand previamente configurado. Ahora bien, el SqlDataAdapter depende de un SqlCommand para utilizar el método Fill(). Podemos encontrar varios tipos de SqlCommand en un SqlDataAdapter, por ejemplo, InsertCommand y UpdateCommand, utilizados para trabajar con datos de manera desconectada. Por el momento solo utilizaremos el tipo SelectCommand pues las tareas que haremos serán de meramente de consulta.
En la sección de Intento de llenado, vemos que se aplica el método Fill del SqlDataAdapter al DataTable, lo ponemos en una estructura try para atrapar cualquiera de dos excepciones, mismas que definimos en dos secciones catch, la primera es específica de SqlClient, la cual nos dará información precisa de SQL Server en caso de fallar alguna tarea con el servidor, la segunda es para atrapar cualquier otra excepción. Se puede ver que el mensaje de la excepción se pasa a una variable mensajeError, misma que será utilizada para pasar el mensaje de error a otra tarea, esta variable no se declara en el método pues está pensada para ser declarada a nivel de clase.
Si somos observadores notaremos que no utilizamos en ningún momento el método Open() y Close() de SqlConnection, esto es porque internamente el SqlDataAdapter abrirá y cerrará la conexión. Como recordatorio a esta capacidad, el método Fill tiene varios comportamientos dependiendo del estado de la conexión, esto es, si la conexión está abierta, el método Fill llenará el DataTable y dejará abierta la conexión y si la conexión está cerrada, el método Fill abrirá la conexión y la cerrará enseguida de haberla utilizado.
Para finalizar, en la sección de retorno se verá la devolución del DataTable, si hubo éxito en la operación del método Fill, el DataTable contendrá o no datos y tendrá el esquema del conjunto resultante, esto es, tendrá los nombres de columna correspondientes a los que devuelve el procedimiento almacenado que estamos ejecutando aunque no tenga datos. Si no tuvo éxito el método Fill, se atrapará la excepción y se devolverá un DataTable “plano”, esto es, sin columnas, solamente estará creado. Este comportamiento es para evitar excepciones del tipo NullReferenceException si asignamos directamente el resultado de nuestro método a la propiedad DataSource de algún control o a alguna variable del tipo DataTable.
Obtención de Tablas y Vistas de una Base de Datos
Una vez que tenemos manera de obtener las bases de datos y que de estas elegir alguna de interés, procederemos a “extirparle” la información o como se dice en el medio, la haremos “cantar”. Bien, debemos obtener las tablas de la base de datos, será de manera muy simple pues será una consulta sencilla. Crearemos un método muy similar a GetDataBases, solo le haremos algunos cambios pues no utilizaremos un procedimiento almacenado.
En específico utilizaremos una vista de sistema, las vistas de sistema pueden ser consultadas desde cualquier contexto y devolverán información propia del contexto en cuestión. El contexto de nuestra conexión es “master” y las vistas de sistema solo devolverán información de la base de datos master. Así es, el contexto está definido por la base de datos con la que se inicia la conexión.
Pregunta:
¿Puedo cambiar el contexto una vez iniciada la conexión?, bueno… podríamos pensarlo un poco y la respuesta es ¡SÍ!, ¿no los hace esto felices?, bueno, pues no lo hagan muy frecuente para evitarse dolores de cabeza, el cambio de contexto de una conexión solo se da al utilizar el SqlCommand que la consume y no cambiará el valor de Database en la cadena de conexión con la que se creó el SqlConnection.
Ejemplo:
SqlCommand cmd = new SqlCommand (“Use Pubs Select * From INFORMATION_SCHEMA.Tables”);
Si consideramos el objeto cnn que creamos en un inicio y que sabemos esta en el contexto de master, pues bien, este SqlCommand al ser utilizado por algún SqlDataAdapter o al usar el método ExecuteReader del SqlCommand en cuestión para habilitar un SqlDataReader, veremos que traerá resultados de la base de datos Pubs, así es, se cambió el contexto de la conexión, pero si revisamos la propiedad ConnectionString del objeto cnn después de hacer esto, veremos que aun conserva el parámetro Database en master, asi que, si nos atreviéramos a usar cnn para trabajar con los objetos de la base de datos Pubs creyendo que ha cambiado el contexto de la conexión a Pubs, simplemente podría esperarse cualquier comportamiento incorrecto o alguna excepción. Así que debemos tener cuidado con eso. En nuestro caso la utilizaremos por la naturaleza de nuestro ejemplo, aquí solo estamos consultando información.
Observación: Al utilizar la instrucción USE de SQL Server siempre será
conveniente poner el nombre de la base de datos entre corchetes pare evitar
errores de interpretación al colorar puntos u otros símbolos.
Crearemos el código necesario dentro de un método, la finalidad será obtener un DataTable conteniendo la información de las tablas de una base de datos dada.
public DataTable GetTables(string DataBase)
{
//Declaración de variables requeridas
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable(“DataBases”);
//Asignación de propiedades
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = “Use [“ + DataBase +
“] Select * From INFORMATION_SCHEMA.TABLES Where TABLE_TYPE = 'BASE TABLE'”;
da.SelectCommand = cmd;
//Intento de llenado de datos hacia la variable dt “DataTable”
try
{
da.Fill(dt);
}
catch(SqlException ex)
{
mensajeRetorno = “Error: “ + ex.Message;
}
catch(Exception ex)
{
mensajeRetorno = “Error: “ + ex.Message;
}
//Retorno de resultado
return dt;
}
Este método es prácticamente igual al anterior, lo único que ha cambiado son dos propiedades del SqlCommand, la propiedad CommandType que cambia de CommandType.StoredProcedure a CommandType.Text, esta propiedad la cambiamos para poder utilizar en la propiedad CommandText la consulta T-SQL, además ahora este método recibe una argumento de entrada: DataBase, del tipo string. Este argumento de entrada lo usaremos para definir a qué base de datos estará dirigida la consulta, eso lo haremos cambiando el contexto de la conexión haciendo uso de la palabra USE.
Estamos haciendo uso de INFORMATION_SCHEMA.TABLES que nos proporcionan información conjunta de las tablas y vistas de la base de datos en contexto. La consulta que usaremos cambia el contexto de usuario a la base de datos que se pasó como argumento del método, seguido de esto, pedimos todo los datos de INFORMATION_SCHEMA.TABLES, datos que serán tomados de las tablas de sistema de la base de datos, también estamos solicitando solo los datos que correspondan con el criterio Table_Name = ‘Base Table’, esto es para mostrar solo tablas.
Ya entrados en gastos, podremos hacer un método que nos muestre las vistas de la base de datos deseada. Será idéntico al anterior, pero solo cambiaremos la condición de la consulta y por supuesto el nombre del método.
public DataTable GetViews(string DataBase)
{
//Declaración de variables requeridas
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable(“Tables”);
//Asignación de propiedades
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = “Use [“ + DataBase +
“] Select * From INFORMATION_SCHEMA.TABLES Where TABLE_TYPE = ‘VIEW’”;
da.SelectCommand = cmd;
//Intento de llenado de datos hacia la variable dt “DataTable”
try
{
da.Fill(dt);
}
catch(SqlException ex)
{
mensajeRetorno = “Error: “ + ex.Message;
}
catch(Exception ex)
{
mensajeRetorno = “Error: “ + ex.Message;
}
//Retorno de resultado
return dt;
}
Pues de este último método, creo que no hay mucho que comentar, solo lo que arriba se describió.
Obtención de las columnas de una tabla o vista
Ahora que tenemos la manera de obtener la lista de todas las tablas de la base de datos, podemos también obtener información de cada tabla. Siempre que podamos obtener el nombre de la tabla podremos obtener información sobre los campos y tipos de datos, longitudes y otra información de cada campo de las tablas.
Ya que tenemos la manera de obtener la lista de Base de datos y las tablas que les corresponden, el paso final sería obtener los campos de la tabla y la información que se derive de estos.
Podremos hacerlo muy similar a lo que hicimos con las tablas, será casi como copiar y pegar, pero tendremos detalles que cuidar. Uno de estos detalles será lo que pasemos a la propiedad CommandText de nuestro SqlCommand, además, nos veremos en la pronta necesidad de utilizar un parámetro para esta consulta, así que agregaremos uno a nuestro SqlCommand.
Veamos cómo queda nuestro método “GetColumns”:
public DataTable GetColumns(string DataBase, string TableName)
{
//Declaración de variables requeridas
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable(“Columns”);
//Asignación de propiedades
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = “Use [“ + DataBase +
“] Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = @TableName”;
cmd.Parameters.Add(“@TableName”,SqlDbType.NVarChar,30).Value = TableName;
da.SelectCommand = cmd;
//Intento de llenado de datos hacia la variable dt “DataTable”
try
{
da.Fill(dt);
}
catch(SqlException ex)
{
mensajeRetorno = “Error: “ + ex.Message;
}
catch(Exception ex)
{
mensajeRetorno = “Error: “ + ex.Message;
}
//Retorno de resultado
return dt;
}
Igual que en los métodos anteriores, asignamos las propiedades de los objetos correspondientes, sin embargo, aquí estamos utilizando una consulta diferente, “Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = @TableName”. Esta consulta devuelve la información relevante de las columnas de las tablas de la base de datos, por esa razón, hemos puesto una condición de búsqueda que devuelva solo la información de la tabla en cuestión. También debemos especificar la base de datos para saber en qué contexto debemos buscar. Podemos notar que hemos agregado una variable: “@TableName” misma que motiva la inclusión de un parámetro en nuestro SqlCommand. Cabe hacer notar que el parámetro del SqlCommand debe llamarse igual que la variable que estamos utilizando en la consulta, ya que internamente se declarará una variable con el nombre que asignamos y si no es el mismo, entonces el servidor devolverá un error debido a la falta de la declaración de la variable en cuestión.
Ahora que hemos creado algunos métodos que de alguna manera nos serán de utilidad, veremos a continuación la manera de explotar y aplicar el código que hasta aquí hemos escrito.
Para efectos de ejemplo, estos métodos serán escritos en la interfaz de usuario, atentando de alguna manera con las buenas prácticas, sin embargo, será tarea del desarrollador comprometido, crear una clase o componente que exponga esta funcionalidad de una mejor manera.
La interfaz de usuario
Para la interfaz de usuario será necesario un formulario con los siguientes elementos:
Tres etiquetas (label), tres cajas de lista (ListBox), será muy simple.
Para crear el proyecto Ejemplo02
- En el menú Archivo, seleccione Nuevo y después seleccione Proyecto para abrir la venta de Nuevo Proyecto.
- Seleccione la plantilla de proyecto Aplicación para Windows en la lista de proyectos para Visual C# (será semejante para Visual Basic) y escriba Ejemplo02 en la caja de texto para el Nombre.
- En el menú Archivo, elija Guardar Todo para guardar su proyecto
En el formulario que se crea predeterminadamente, dibujaremos tres controles ListBox debajo de tres controles Label la configuración de las propiedades de los controles se muestra en la tabla 1:
| Control | Propiedad | Valor |
| Form | Name |
Form1 |
| | Text |
"Ejemplo 02" |
| ListBox | Name |
listBox1 |
| |
TabIndexx | 0 |
| |
Text | "" |
| ListBox | Name |
listBox2 |
| |
TabIndex | 1 |
| |
Text | "" |
| ListBox | Name |
listBox3 |
| |
TabIndex | 2 |
| |
Text | "" |
| Label | Name | Label1 |
| |
Text | "Bases
de Datos" |
| Label | Name | Label2 |
| |
Text | "Tablas" |
| Label | Name | Label3 |
| |
Text | "Columnas" |
Tabla 1.- Propiedades de controles
Una vez realizado esto, quedará un formulario parecido al de la figura 1.
Figura 1.- Formulario de ejemplo
Será simple codificar nuestro formulario, ya que hemos descrito todo lo que necesitamos y solo bastará poner el código donde corresponde para poder ver la funcionalidad esperada.
Iniciemos por el principio, que será donde inicializamos nuestra conexión y desencadenamos la actualización de las cajas de lista para reflejar el contenido de nuestro servidor de datos. Bueno, por cuestiones de ejemplo pondremos la conexión en el evento Load de nuestro formulario a la vez que actualizamos el primer ListBox.
private void Form1_Load(object sender, System.EventArgs e)
{
cnn = new System.Data.SqlClient.SqlConnection(
"Integrated Security = SSPI; Server = Localhost; DataBase = master");
listBox1.ValueMember = "dbID";
listBox1.DisplayMember = "name";
listBox1.DataSource = GetDataBases();
listBox2.ValueMember = "TABLE_CATALOG";
listBox2.DisplayMember = "TABLE_NAME";
listBox2.DataSource = GetTables(listBox1.Text);
}
Vemos aquí la utilización del método GetDataBases(), mismo que estará declarado dentro de nuestro formulario, tal como lo definimos anteriormente e igualmente utilizaremos el método GetTables() utilizando como parámetro el texto del listBox1 para obtener las tablas correspondientes a la base de datos seleccionada. Una vez definido el comienzo continuaremos con la codificación de los eventos de las cajas de lista, será simple como se muestra a continuación:
private void listBox1_SelectedValueChanged(object sender, System.EventArgs e)
{
ListBox list = sender as ListBox;
listBox2.ValueMember = "TABLE_CATALOG";
listBox2.DisplayMember = "TABLE_NAME";
listBox2.DataSource = GetTables(list.Text);
}
private void listBox2_SelectedValueChanged(object sender, System.EventArgs e)
{
ListBox list = sender as ListBox;
listBox3.ValueMember = "TABLE_NAME";
listBox3.DisplayMember = "COLUMN_NAME";
listBox3.DataSource = GetColumns(listBox1.Text,listBox2.Text);
}
La caja de lista listBox3 no tendrá programado nada por ser el eslabón final de la cadena de información. Cada vez que se modifique el valor seleccionado se desencadenará el evento correspondiente para actualizar la información.
Por último no debemos olvidarnos de la variable donde se capturan los mensajes de error:
private string mensajeRetorno;
Esta variable podemos declararla a nivel de clase para tenerla disponible en todos nuestros métodos, si es necesario podemos crear una propiedad al formulario para obtener los mensajes que suceden en el formulario.
Para probar el proyecto
- En el menú Depurar, elija Iniciar.
El formulario Form1 se abre como se muestra en la figura 1.
- Seleccione cualquier base de datos de la lista de base de datos.
Note cómo se actualizan las otras cajas de lista.
- Seleccione cualquier tabla de la lista de tablas.
Los campos de la tabla se mostrarán en la lista de columnas.
Conclusión
Hemos observado de qué manera podemos obtener información de nuestras bases de datos utilizando nada más que nuestro proveedor de datos SqlClient, mucha información de nuestro servidor puede ser obtenida de esa manera.
Comprobamos de alguna manera que no siempre utilizar SQLDMO es lo mejor, ya que
siempre habrá una manera de obtener la información por medio de T-SQL y
SqlClient. más adelante veremos más ejemplos al respecto.
