Com a ascensão dos sistemas personalizados para cada situação de um ambiente de trabalho/negócio, as informações geradas pelos menos tem a necessidade de serem armazenadas para serem consultadas num futuro breve, fazer análises baseadas em um conjunto de outras informações afim de obter um resultado, entre outras situações. Para o armazenamento dessas informações em banco de dados são criadas estruturas em forma de tabelas, contendo colunas e linhas que classificam a informação de forma categorizada e organizada. Porém, para o usuário final, o banco de dados deve abstrair a forma pela qual as informações são organizadas, garantindo apenas que as informações estão salvas e a um fácil acesso. Contudo, no dia a dia de um programador surgem necessidades de otimizar algumas etapas no decorrer do desenvolvimento de software, como por exemplo, na construção de um framework, e para isso é preciso criar algumas rotinas que nos auxiliem em determinadas ocasiões.
Partindo desta ideia, iremos criar um programa Windows Forms em C# que disponibilize essas informações acessando Views existentes no banco de dados SQL Server, onde são disponibilizadas diversas informações sobre objetos presentes no banco, como mostra a Tabela 1.
| Views | Descrição |
|---|---|
| CHECK_CONSTRAINT | Verifica restrições |
| COLUMN_PREVILEGES | Colunas com privilégios garantidos para ou pelo usuário atual |
| COLUMNS | Todas as colunas |
| CONSTRAINT_TABLE_USAGE | Tabelas que possuem uma restrição definida |
| DOMAINS | Todos os tipos de dados definidos pelo usuário |
| KEY_COLUMN_USAGE | Colunas restringidas como uma chave |
| PARAMETERS | Todos os parâmetros para funções definidas pelo usuário e storedprocedures |
| ROUTINES | Todas as funções definidas pelo usuário e storedprocedures |
| SCHEMATA | Todos os bancos de dados |
| TABLE_CONSTRAINTS | Todas as restrições das tabelas |
| TABLE_PRIVILEGES | Tabelas com privilégio garantido para ou pelo usuário atual |
| TABLES | Todas as tabelas |
| VIEWS_TABLE_USAGE | Tabelas usadas em uma View |
| VIEWS | Todas as Views |
Para esse artigo usaremos as seguintes views:
- INFORMATION_SCHEMA.TABLES – Retorna uma linha para cada tabela presente no BD, lembrando que apenas serão listadas as tabelas que o usuário tem permissão. Esta view retorna as informações presentes na Tabela 2.
Nome Descrição Tipo TABLE_CATALOG Qualificador da tabela nvarchar(128) TABLE_SCHEMA Nome do schema que a tabela pertence nvarchar(128) TABLE_NAME Nome da tabela sysname TABLE_TYPE Tipo da tabela, view ou table varchar(10) Tabela 2. Informações da View INFORMATION_SCHEMA.TABLES - INFORMATION_SCHEMA.COLUMNS – Retorna uma linha para cada coluna que pode ser acessada pelo usuário em uma determinada tabela do banco de dados. As informações disponibilizadas são as presentes na Tabela 3.
Nome Descrição Tipo TABLE_CATALOG Qualificador da tabela nvarchar(128) TABLE_SCHEMA Nome do schema que a tabela pertence nvarchar(128) TABLE_NAME Nome da tabela nvarchar(128) COLUMN_NAME Nome da coluna nvarchar(128) ORDINAL_POSITION Número de identificação da coluna int COLUMN_DEFAULT Valor padrão da coluna nvarchar(4000) IS_NULLABLE Informa se a coluna permite NULO varchar(3) DATA_TYPE Tipo do dado nvarchar(128) CHARACTER_MAXIMUM_LENGTH Valor máximo em caracteres int CHARACTER_OCTET_LENGTH Comprimento máximo em caracteres int NUMERIC_PRECISION Precisão numérica dos dados tinyint NUMERIC_PRECISION_RADIX Precisão da raiz dos dados smallint NUMERIC_SCALE Escala aproximada de dados numéricos int DATETIME_PRECISION Tipo de datetime usado smallint CHARACTER_SET_CATALOG Indica em que banco de dados o conjunto de caracteres está alocado nvarchar(128) CHARACTER_SET_SCHEMA Sempre retorna NULO nvarchar(128) CHARACTER_SET_NAME Retorna o nome exclusivo para o conjunto de caracteres. nvarchar (128) COLLATION_CATALOG Sempre retorna NULO nvarchar(128) COLLATION_SCHEMA Sempre retorna NULO nvarchar(128) COLLATION_NAME Retorna o nome exclusivo se o tipo da coluna for texto ou conjunto de caracteres nvarchar(128) DOMAIN_CATALOG Se a coluna é um alias nvarchar(128) DOMAIN_SCHEMA Se a coluna é um tipo de dados definido pelo usuário nvarchar(128) DOMAIN_NAME Nome do tipo de dados definido pelo usuário nvarchar(128) Tabela 3. Informações da View INFORMATION_SCHEMA.COLUMNS - INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE – Retorna uma linha para cada coluna que tenha uma restrição definida nas tabelas do banco de dados em que o usuário tenha permissão. Vejamos as informações disponibilizadas por essa View na Tabela 4.
Nome Descrição Tipo TABLE_CATALOG Qualificador da tabela nvarchar(128) TABLE_SCHEMA Nome do schema de contem a coluna da tabela nvarchar(128) TABLE_NAME Nome da tabela nvarchar(128) COLUMN_NAME Nome da coluna nvarchar(128) CONSTRAINT_CATALOG Qualificador da restrição nvarchar(128) CONSTRAINT_SCHEMA Nome do schema que contém a restrição definida nvarchar(128) CONSTRAINT_NAME Nome da restrição definida nvarchar(128) Tabela 4. Informações da View INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE - INFORMATION_SCHEMA.KEY_COLUMN_USAGE – Retorna uma linha para cada coluna que representa uma chave de uma determinada tabela de um banco de dados especifico para que o usuário tenha permissão. Esta View retorna as informações presentes na Tabela 5.
Nome Descrição Tipo CONSTRAINT_CATALOG Qualificador da restrição nvarchar(128) CONSTRAINT_SCHEMA Nome do schema que contém a restrição definida nvarchar(128) CONSTRAINT_NAME Nome da restrição nvarchar(128) TABLE_CATALOG Qualificador da tabela nvarchar(128) TABLE_SCHEMA Nome do schema da tabela nvarchar(128) TABLE_NAME Nome da tabela que contem a restrição nvarchar(128) COLUMN_NAME Nome da coluna que contem a restrição nvarchar(128) ORDINAL_POSITION Número de identificação da coluna int Tabela 5. Informações da View INFORMATION_SCHEMA.KEY_COLUMN_USAGECriando um cenário de teste
Agora que já conhecemos as Views que fornecem informações sobre objetos do banco de dados, vamos criar um cenário para que possamos obter as informações dele. Para isso, utilizaremos um banco de dados SQL Server com o nome dbTeste e criaremos duas tabelas: uma de funcionário e outra de produto. Após isso criaremos uma aplicação .NET para que possamos obtê-las.
Vamos começar pelo banco de dados criando a tabela de produto com a execução do script da Listagem 1 e teremos as colunas presentes na Tabela 6.
Nome Descrição Tipo prd_id Identificador do produto int prd_cod Código do produto int prd_descricao Descrição do produto nvarchar(250) prd_abreviacao Abreviação do produto nvarchar(100) prd_tipo Tipo do produto char(2) prd_dat_cad Data de cadastro do produto datetime prd_valor Valor do produto numeric(9, 2) Tabela 6. Descrição das colunas da tabela de produtoUSE [dbTeste] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[produto]( [prd_id] [int] IDENTITY(1,1) NOT NULL, [prd_cod] [int] NOT NULL, [prd_descricao] [nvarchar](250) NOT NULL, [prd_abreviacao] [nvarchar](100) NOT NULL, [prd_tipo] [char](2) NOT NULL, [prd_dat_cad] [datetime] NOT NULL, [prd_valor] [numeric](9, 2) NOT NULL, CONSTRAINT [PK_produto] PRIMARY KEY CLUSTERED ( [prd_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GOListagem 1. Script da tabela produtosAgora iremos criar a tabela com o nome funcionario com o script da Listagem 2, e com as colunas da Tabela 7.
Nome Descrição Tipo fun_id Identificador do funcionário int fun_mat Matrícula do funcionário varchar(10) fun_nome Nome do funcionário nvarchar(250) fun_sexo Sexo do funcionário char(1) fun_dat_nasc Data de nascimento do funcionário datetime fun_ci Identidade do funcionário bigint fun_email Email do funcionário varchar(100) fun_telefone Telefone do funcionário varchar(11) Tabela 7. Descrição das colunas da tabela de funcionárioUSE [dbTeste] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[funcionario]( [fun_id] [int] IDENTITY(1,1) NOT NULL, [fun_mat] [varchar](10) NOT NULL, [fun_nome] [varchar](250) NOT NULL, [fun_sexo] [char](1) NOT NULL, [fun_dat_nasc] [datetime] NOT NULL, [fun_ci] [bigint] NOT NULL, [fun_email] [varchar](100) NULL, [fun_telefone] [varchar](11) NULL, CONSTRAINT [PK_funcionario_1] PRIMARY KEY CLUSTERED ( [fun_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GOListagem 2. Tabela FuncionarioEm seguida execute um select utilizando as Views que foram apresentadas no início do artigo, como mostra a Listagem 3.
SELECT * FROM INFORMATION_SCHEMA.TABLES t JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_NAME = T.TABLE_NAME WHERE T.TABLE_NAME = 'funcionario' ORDER BY C.ORDINAL_POSITIONListagem 3. Consulta a tabela funcionarioCom esse select é possível obter todas as colunas com suas respectivas informações da tabela de funcionário.
Vamos agora criar um projeto do tipo Windows Forms utilizando a linguagem C#. Para isso, abra o Visual Studio e acesse ao menu File > New > Project. Selecionamos a linguagem Visual C# e em seguida selecionamos o tipo Windows Forms Application, dando um nome ao projeto, como mostra a Figura 1.
Figura 1. Novo projeto do tipo Windows Forms Com o projeto criado adicionaremos uma referência para que possamos utilizar nossa ConnectionString no App.config da aplicação. Para isso, clicamos com o botão direito do mouse sobre o item Reference na Solution Explorer e clicamos em Add Reference. Com a janela nova aberta procuraremos a referência System.Configuration na aba de Framework e adicionamos ao projeto. Em seguida vamos para o arquivo de configuração e vamos inserir nossa ConnectionString dentro das tags de , como mostra a Listagem 4.
<connectionStrings> <add name="TableInformationCS" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=dbTeste;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>Listagem 4. ConnectionStringAo adicionar uma nova string de conexão, abrimos uma tag dentro das tags e primeiro informamos o nome da nossa string de conexão através da propriedade name, pois esse será com esse nome que poderemos ter acesso às informações de conexão com nosso banco de dados. Depois temos a propriedade connectionString onde nela serão adicionadas todas as informações de conexão necessárias com nosso banco de dados. Em nosso caso temos o Data Source que é composto pelo servidor\instância do banco de dados, ou seja, representamos o servidor local através do “.” e a instância do nosso banco por SQLEXPRESS. Após isso, temos o “Initial Catalog” que contém o nome do banco de dados a ser acessado, que é o dbTeste. Por último, temos o “Integrated Security” que informa se a conexão será setada através de um Boolean, True ou False. A propriedade providerName conterá o “provider” a ser utilizado na comunicação com o banco de dados, e já que estamos usando o SQL Server, o provider será o System.Data.SqlClient.
Para que possamos utilizar os dados da nossa string de conexão que criamos, vamos desenvolver uma classe para conexão com o banco. Clicando com o botão direito do mouse sobre nosso projeto, acessamos o menu Add > Class e criaremos uma classe com o nome DbUtil. Dentro desta classe conterá métodos para obter, inserir, alterar e excluir dados do nosso banco, como mostra a Listagem 5.
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace TableInformation { public class DbUtil { #region Objetos Estáticos // Objeto Connection para obter acesso ao SQL Server public static SqlConnection sqlconnection = new SqlConnection(); // Objeto SqlCommand para executar os com public static SqlCommand comando = new SqlCommand(); // Objeto SqlParameter para adicionar os parâmetros necessários em nossas consultas public static SqlParameter parametro = new SqlParameter(); #endregion #region Obter SqlConnection public static SqlConnection connection() { try { // Obtemos os dados da conexão existentes no WebConfig utilizando o ConfigurationManager string dadosConexao = ConfigurationManager .ConnectionStrings["TableInformationCS"].ConnectionString; // Instanciando o objeto SqlConnection sqlconnection = new SqlConnection(dadosConexao); //Verifica se a conexão esta fechada. if (sqlconnection.State == ConnectionState.Closed) { //Abre a conexão. sqlconnection.Open(); } //Retorna o sqlconnection. return sqlconnection; } catch (SqlException ex) { throw ex; } } #endregion #region Abre Conexão public void Open() { sqlconnection.Open(); } #endregion #region Fecha Conexão public void Close() { sqlconnection.Close(); } #endregion #region Adiciona Parâmetros public void AdicionarParametro(string nome, SqlDbType tipo, int tamanho, object valor) { // Cria a instância do Parâmetro e adiciona os valores parametro = new SqlParameter(); parametro.ParameterName = nome; parametro.SqlDbType = tipo; parametro.Size = tamanho; parametro.Value = valor; // Adiciona ao comando SQL o parâmetro comando.Parameters.Add(parametro); } #endregion #region Adiciona Parâmetros public void AdicionarParametro(string nome, SqlDbType tipo, object valor) { // Cria a instância do Parâmetro e adiciona os valores SqlParameter parametro = new SqlParameter(); parametro.ParameterName = nome; parametro.SqlDbType = tipo; parametro.Value = valor; // Adiciona ao comando SQL o parâmetro comando.Parameters.Add(parametro); } #endregion #region Remove os parâmetros public void RemoverParametro(string pNome) { // Verifica se existe o parâmetro if (comando.Parameters.Contains(pNome)) // Se exite remove o mesmo comando.Parameters.Remove(pNome); } #endregion #region Limpar Parâmetros public void LimparParametros() { comando.Parameters.Clear(); } #endregion #region Executar Consulta SQL public DataTable ExecutaConsulta(string sql) { try { // Pega conexão com a base SQL Server comando.Connection = connection(); // Adiciona a instrução SQL comando.CommandText = sql; //Executa a query sql. comando.ExecuteScalar(); // Ler os dados e passa para um DataTable IDataReader dtreader = comando.ExecuteReader(); DataTable dtresult = new DataTable(); dtresult.Load(dtreader); // Fecha a conexão sqlconnection.Close(); // Retorna o DataTable com os dados da consulta return dtresult; } catch (Exception ex) { // Retorna uma exceção simples que pode ser tratada por parte do desenvolvedor // Exemplo: if (ex.Message.toString() .Contains(‘Networkig’)) // Exemplo throw new Exception(‘Problema de rede detectado’); throw ex; } } #endregion #region Executa uma instrução SQL: INSERT, UPDATE e DELETE public int ExecutaAtualizacao(string sql) { try { //Instância o sqlcommand com a query sql que será executada e a conexão. comando = new SqlCommand(sql, connection()); comando.Connection = connection(); comando.CommandText = sql; //Executa a query sql. int result = comando.ExecuteNonQuery(); sqlconnection.Close(); // Retorna a quantidade de linhas afetadas return result; } catch (Exception ex) { // Retorna uma exceção simples que pode ser tratada por parte do desenvolvedor throw ex; } } #endregion } }Listagem 5. Classe de conexão com o banco de dadosPrecisamos agora criar um layout para nossa aplicação fazer a comunicação com o banco e obter as informações necessárias. Automaticamente, ao criarmos nosso projeto, o Visual Studio cria um formulário chamado Form1 e utilizaremos este no nosso caso de uso. Adicionaremos um “Button” para obter as tabelas do nosso banco de dados e um DataGridView para exibi-las. Em seguida, adicionaremos outro Button e DataGridView para obter as informações das colunas, como mostra a Figura 2.
Figura 2. Layout do formulário da aplicação Implementaremos agora o evento Click do Button de obter tabelas. Para isso daremos um duplo clique sobre o Button desejado e automaticamente será gerado um evento no CodeBehind para este botão, chamado btnTables_Click. Adicione ao evento o código da Listagem 6.
private void btnTables_Click(object sender, EventArgs e) { DbUtil dbUtil = new DbUtil(); dbUtil.LimparParametros(); string sql_tables = @" SELECT TABLE_SCHEMA AS 'SCHEMA', TABLE_NAME AS 'TABELA' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_TYPE"; DataTable dtTabelas = dbUtil.ExecutaConsulta(sql_tables); dgvTables.DataSource = dtTabelas; }Listagem 6. Evento onclick do buttonInstanciamos nossa classe de acesso a dados, limpamos os parâmetros e criamos o “Select” com a View INFORMATION_SCHEMA.TABLES para obter as informações sobre as tabelas presentes em nosso banco. Criamos um DataTable para guardar a informação que obtivemos através do método ExecutaConsulta da nossa classe de acesso. Por fim, associamos nosso DataTable ao DataSource do DataGridView para que a informação possa ser disponibilizada para o usuário.
Agora implementaremos o evento click do outro botão que obterá as informações das colunas da tabela selecionada no DataGridView anterior, como mostra a Listagem 7.
private void btnColumns_Click(object sender, EventArgs e) { DbUtil dbUtil = new DbUtil(); dbUtil.LimparParametros(); string sql_columns = @" SELECT COLUMN_NAME AS 'COLUNA', DATA_TYPE AS 'TIPO', IS_NULLABLE AS 'NULO' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @P_TABLE_NAME ORDER BY ORDINAL_POSITION"; dbUtil.AdicionarParametro("P_TABLE_NAME", SqlDbType.VarChar, dgvTables.SelectedRows[0].Cells[1].Value); DataTable dtColumns = dbUtil.ExecutaConsulta(sql_columns); dgvColumns.DataSource = dtColumns; }Listagem 7. Evento onclick do segundo buttonInstanciamos novamente nossa classe de acesso e limpamos os parâmetros. Além disso, montamos um Select para obter as colunas de uma determinada tabela, mas dessa vez utilizando a View INFORMATION_SCHEMA.COLUMNS. Note que passamos um parâmetro no nosso comando select contendo o nome da tabela que desejamos obter as colunas. Criamos um parâmetro contendo a informação que será filtrada no nosso Select e chamamos o método ExecutaConsulta, guardando as informações obtidas em um DataTable. Para finalizar, associamos nosso DataTable ao DataSource do DataGridView para que o usuário possa visualizar as informações da tabela selecionada no DataGridView anterior.
Neste momento já temos nossa aplicação compilando e pronta para ser executada ao apertar o botão F5. Com a janela aberta, vamos clicar no botão “Obter Tabelas” e o DataGridView irá exibir todas as tabelas do nosso banco de dados, como mostra a Figura 3.
Figura 3. Exibindo todas as tabelas do banco de dados Selecionando uma determinada tabela e clicando em Obter Colunas, o DataGridView inferior listará todas as colunas pertencentes a tabela selecionada, como mostra a Figura 4.
Figura 4. Exibindo informações da tabela selecionada Podemos também utilizar outras Views disponibilizadas para obter informações mais especificas, ou caso já tenha um banco de dados configurado em sua máquina, basta abrir o arquivo App.config contido na solução e mudar o nome do banco de dados na propriedade Initial Catalog dentro das tags e colocar o nome do banco de dados desejado.
