Como obter informações do banco de dados com Views do SQL Server

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.

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_USAGE

  Criando 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 produto
  USE [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 GO

  Listagem 1. Script da tabela produtos

  Agora 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ário
  USE [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 GO

  Listagem 2. Tabela Funcionario

  Em 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_POSITION

  Listagem 3. Consulta a tabela funcionario

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

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

  Ao 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 dados

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

  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 button

  Instanciamos 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 button

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

  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.

  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.