ADO.NET: Introdução

Com o advento Framework.Net, se possibilitou novos recursos de acessos a dados, de forma a facilitar do desenvolvimento até migração entre plataformas de SGDB.

O ADO.Net, principal recurso de acesso a banco de dados na plataforma .Net, se popularizou por sua arquitetura orientada a objeto e o suporte as principais plataforma de SGDB do mercado, como Oracle e SQL Server, além de suporte a OleDb e ODBC, que possibilitam a integração com Excel, DB2, Interbase, PostGree, Firebird e MySQL, sem a necessidade de utilizar qualquer componente de terceiros.

Também existem outros recursos desenvolvidos que estendem ou reimplementam a arquitetura de acessos a SGDB de forma especializada, como no caso da Oracle, do Firebird e do MySQL, que desenvolvem suas próprias extensões do ADO.Net, afim de atender necessidade específicas de suas plataformas. Em relação ao SQL Server, a Framework.Net possui outros recursos exclusivos para prover integração com CLR.

Existem duas formas básicas de trabalhar com o ADO.Net, por meio de classes conectadas e desconectadas.

As classes conectadas permitem o acesso ao SGBD, por meio do .Net Data Provider, que constitui basicamente das seguintes entidades:

Connection: Responsável pela conexão ao SGDB, esta classe varia SGDB para SGDB (ex. SQLConnection, OracleConnection, OleDbConnection e OdbcConnection), possuindo métodos e propriedades que facilitam criar a conexão com cada um dos SGDB.

Por questões de arquitetura, para exigir que a conexão se feche depois de realizada a operação é recomendado o uso do bloco “Using”, exemplo:

// Criando o objeto de conexão ao banco de dados
using (SqlConnection conn = new SqlConnection("string de conexão"))
{
  // Chamando o método responsável pela abertura da conexão.
  conn.Open();
}

Recomendável não utilizar nenhum retorno de métodos dentro de blocos “Using”:

// Criando o objeto de conexão ao banco de dados
using (SqlConnection conn = new SqlConnection("string de conexão"))
{
  // Chamando o método responsável pela abertura da conexão.
  conn.Open();
  // Nunca faça isso:
  return true;

}

String de conexão: É responsável por identificar o endereço do SGDB, formas de autenticação e configurações de acesso.

Em aplicações Web, normalmente esta string é armazenada no arquivo web.config ou aplicações Windows e Windows Mobile no arquivo app.config, por questões de reaproveitamento e para facilitar alterações. Exemplo:

<!--web.config-->
<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="nome" connectionString="Data Source=in;Initial Catalog=db;User=us;Password=sn;"/>
  </connectionStrings>
</configuration>

Método responsável por retornar a string de conexão do web.config, as vezes se faz necessário adicionar a referência a System.Configuration ao projeto.

ConfigurationManager.ConnectionStrings["nome"].ToString();
// Criando o objeto de conexão ao banco de dados
string connectionString = ConfigurationManager.ConnectionStrings["nome"].ToString()
using (SqlConnection conn = new SqlConnection(connectionString))
{
  // Chamando o método responsável pela abertura da conexão.
  conn.Open();
}

Também existem formas de gerar a connection string em tempo de execução.
(at. http://msdn.microsoft.com/en-us/library/ms254947(VS.80).aspx )

Alguns exemplos de string de conexão para SQL Server e outros bancos de dados:
https://sqlfromhell.wordpress.com/2009/08/04/connection-strings/
http://msdn.microsoft.com/en-us/library/ms254500.aspx

Command: Depois de estabelecida a conexão com o SGDB, esta classe é o responsável por executar comandos e consultas no banco de dados. Seus objetos podem ser instanciado da forma tradicional por meio de um construtor ou chamando o método CreateCommand de objetos de conexão.Exemplo:

using (SqlConnection conn= new SqlConnection(""))
{
  using (SqlCommand cmd = new SqlCommand())
  {
    cmd.Connection = conn;
  }
}

Ou

using (SqlConnection conn= new SqlConnection(""))
{
  using (SqlCommand cmd = conn.CreateCommand())
  {
  }
}

Estes comandos podem ser comandos em SQL, nomes de StoredProcedures ou nomes de tabelas, para define qual o tipo comando é possível definir por meio do enumerador CommandType. Ex.:

// Para comandos SQL
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM tabela";
// Para stored procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SpTabelaSel";
// Para tabela
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = "tabela";

Parâmetros: É a forma ideal de passar valores para comandos SQL e Stored Procedures. São eficazes para combater possíveis ataques de “SQL-Injection”. Exemplos:

//Forma de errada de passar valores para os comandos
cmd.CommandText = "INSERT INTO tabela (valor1) VALUES " + valor + ")";
//Utilizando parâmetros para inserir dados
cmd.CommandText = "INSERT INTO tabela (valor1) VALUES (@nome_parametro)";
cmd.Parameters.Add(new SqlParameter("@nome_parametro", valor);
//Utilizando parâmetros para excluir
cmd.CommandText = "DELETE FROM tabela WHERE valor1 = @nome_parametro";
cmd.Parameters.Add(new SqlParameter("@nome_parametro", valor);
//Utilizando parâmetros para alterar
cmd.CommandText = "UPDATE tabela SET valor1 = @nome_parametro1 WHERE valor2 = @nome_parametro2";
cmd.Parameters.Add(new SqlParameter("@nome_parametro1", valor1);
cmd.Parameters.Add(new SqlParameter("@nome_parametro2", valor2);
//Utilizando parâmetros para consultas
cmd.CommandText = "SELECT * FROM tabela WHERE valor1 = @nome_parametro";
cmd.Parameters.Add(new SqlParameter("@nome_parametro", valor);
//Utilizando parâmetros para StoreProcedures
cmd.CommandText = " SpTabelaSel";
cmd.Parameters.Add(new SqlParameter("@nome_parametro", valor);

A forma ideal de criar parâmetros é definir o tipo, tamanho e valor de forma separada.

//Forma ideal de criar um parâmetro
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@nome_parametro";
parameter.SqlDbType = SqlDbType.VarChar;
parameter.Size = 90;
parameter.Value = valor;

ou

SqlParameter parameter = new SqlParameter("@nome_parametro", SqlDbType.VarChar, 90);
parameter.Value = valor;

Métodos de execução: Para comandos que não são esperados retornos é recomendado o uso do método ExecuteNonQuery, cujo o retorno é um inteiro especificando o número de linhas afetadas. Exemplo:

cmd.CommandText = "INSERT INTO tabela (coluna) VALUES ('valor')";
cmd.ExecuteNonQuery();

Para comandos que se espera uma única célula, exemplo a execução das funções SQL, COUNT, SUM, MAX, MIN ou qualquer outra consulta que ternar somente um valor, é recomendado o uso do método ExecuteScalar. Exemplo:

cmd.CommandText = "SELECT COUNT(*) FROM tabela";
int count = (int)cmd.ExecuteScalar();

Alterando as portas de conexão do SQL Server

Como muitos já sabem, o SQL Server por padrão usa da porta 1433 (TCP) para se conectar por meio do protocolo TCP/IP. Mesmo que esta porta seja registrada para este fim em um órgão regulador, o Internet Assigned Numbers Authority, é possível alterar para utilizar outras portas se for necessário.

Antes de tudo, não recomendo esta alteração, principalmente por causar muito desconforto aos já acostumados com trabalhar da forma convencional e porque algumas ferramentas de terceiros não suportam o SQL Server com esta alteração. Na verdade, conheço alguns softwares muito bons que não suportam nem mesmo instâncias nomeadas como .\SQLEXPRESS e .\SQL2005.

Mas, voltando ao objetivo do post…

Primeiro, execute o “SQL Server Configuration Manager”, que se encontra normalmente no menu “Iniciar\Programas\Microsoft SQL Server 200X\Configuration Tools”.

40_01

Obs.: Para esta demonstração estou utilizando um Windows 2008 Server x64, então não se surpreendam com o número de opções que venham a aparecer, mas para esta demonstração não utilizarei nada que não seja possível em um Windows 2003, XP, Vista ou Seven, até mesmo porque estou optando por alterar uma instancia do SQL Server Express.

No menu laterial, expanda a opção “SQL Server Network Configuration” e clique sobre o item “Protocols for XXX” (XXX será o nome da instancia que você estará trabalhando, no meu caso SQLEXPRESS) e de um duplo clique em TCP/IP.

40_02

Se preocupe em verificar se as propriedades Enabled e Listen All desta primeira aba estão dispostas da seguinte forma:

40_03

Na segunda aba, inicialmente evite se preocupar com as várias propriedades existentes. Se atente ao grupo de propriedades IPAll e determine a porta TCP que você deseja configurar, no meu caso “666”:

40_04

Após clicar no botão OK, você receberá o aviso que para as configurações tenham efeito será necessário reiniciar o serviço do SQL Server.

40_05

Então, voltando para o menu lateral “SQL Server Services”, selecione o serviço do SQL Server que você acabou de configura e clique no botão “Restart Service”.

40_06

Pronto, agora é só testar…

Como assim, não sabe onde configurar a porta TCP no seu SQL Server Management Studio???

Fica tranqüilo! No campo “Server Name”, escreva conforme a sintaxe:
“EndereçoDoServidor\NomeDaInstancia, PortaConfigurada”

40_07

Pronto, agora você tem um SQL Server configurado na “porta errada”. Boa sorte e tomara que nenhum desenvolvedor irado queira te matar depois de você realizar esta alteração!

Até o próximo tópico!

Trabalhando com datas – Cálculos com meses

É muito comum aparecer no dia a dia, muitas necessidades estranhas para trabalhar com SQL, principalmente quando se trabalha com os campos de data. Mas a sensação de superar obstáculos somente pode ter sentido com estes pequenos e mirabolantes desafios aparecem, e a vontade de dar um jeitinho no SQL começa aparecer.

Para enterrar o tópico “Trabalhando com datas”, segue dois desafios legais que já encontrei.

Retornando o primeiro dia da mês:

DECLARE @DATA DATETIME
SET @DATA = GETDATE()
SELECT CONVERT(VARCHAR, @DATA - DAY(@DATA) + 1, 103) as PrimeiroDiaDoMes
Ou
SELECT CONVERT(VARCHAR, @DATA - DATEPART(DAY, @DATA) + 1, 103) as PrimeiroDiaDoMes

Retornando o número da semana do mês:

DECLARE @DATA DATETIME
SET @DATA = GETDATE()
SELECT DATEPART(WK, @DATA) - DATEPART(WK, @DATA - DAY(@DATA) + 1) + 1 as SemanaDoMes
Ou
SELECT DATEPART(WEEK, @DATA) - DATEPART(WEEK, @DATA - DATEPART(DAY, @DATA) + 1) + 1 as SemanaDoMes

Valeu Maikel pelo desafio e por liberar que fosse postado!

O Profissional de Banco de Dados

A área de banco de dados é um ramo da tecnologia de informação muito pouco explorado em comparação da necessidade das empresas de soluções.

Considero o profissional de banco de dados um especialista em infra-estrutura, em desenvolvimento e/ou em negócios, que possui capacidade de prover serviços de qualidade na área de banco de dados. Desta forma o fato dele ter conhecimento de banco de dados somente é um diferencial competitivo entre os vários outros profissionais da área de tecnologia da informação.

Existem tentativas de categorizar os profissionais de banco de dados, basicamente divididos nas categorias de Administrador de Banco de dados (Database Administrador), Desenvolvedor de Banco de dados (Database Developer) e Profissional em Business Intelligence, mas todas são somente formas das empresas conseguirem entender qual “DBA” precisam contratar.

O profissional em banco de dados não pode ser somente DBA ou desenvolvedor ou somente o cara do BI. Na verdade ele precisa ser um profissional “Google” (“Wikipédia” ou “Yahoo” ou “Bing” se preferir), pois as organizações estão sedentas de profissionais capacitados e elas não importam muito com qual o nome do cargo dele, mas sim “como ele pode me auxiliar na minha estratégia?”. Este auxílio pode ser uma solução maravilhosa com a qual a empresa pode ganhar (ou não perder) dinheiro ou o simples fato de ter este profissional tudo na empresa estará funcionando perfeitamente.

E respondendo a pergunta “Qual é a minha visão do mercado para o DBA hoje em dia?” que recebi do Anderson, existem muitas oportunidades de trabalho na área de TI, principalmente para profissionais de banco de dados, só que para preencher estas vagas é necessário provar sua aptidão para ocupá-la, então se qualifique e aproveite sempre a oportunidade de aprender mais! Pessoalmente gosto muito deste ramo e vejo nele muitas oportunidades de crescimento.

Como eu disse “conhecer de banco de dados somente é um diferencial competitivo”, logo é possível aumentar o impacto deste diferencial, com trabalho, estudos, treinamentos e dedicação, mas não deve se esquecer de temperar com um pouco de “Google” e aprender também sobre o que esta além do banco de dados.

Obrigado pelas críticas e sugestões que tenho recebido!

 

Artigos relevantes:

http://imasters.uol.com.br/artigo/10797/bancodedados/quer_um_emprego_como_dba/

http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!299.entry

http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!136.entry

http://imasters.uol.com.br/artigo/13518/oracle/que_tipo_de_dba_e_voce/ 
(Profissional Google para eles quer dizer outra coisa…rsss)

http://guiarh.com.br/y68.htm

http://guiarh.com.br/pp54.htm

Trabalhando com datas – Lista de meses e dias da semana

Pessoal, no último post relacionei funções para trabalhar com campos do tipo data, acabei recebendo um bom feedback e algumas perguntas sobre como relacionar todos os meses ou dias da semana por meio de query. Para responder estas perguntas, relacionei formas de gerar listas com os meses e dias da semana por meio de CTE.

Nestas duas primeiras queries, faço uso de um CTE recursivo básico, com as funções DATENAME e DATEADD e a instrução SET LANGUAGE para definir o idioma ‘Brasileiro’. Estas queries são bem mais simples e tornam mais fácil trazer novos campos. 

Tips:
1: Os campos de data quando somados a tipos inteiros, adicionam o valor dos inteiros em dias.
2: A função DATEADD simplifica a adição de outros quantitativos como meses, horas e anos.
3: O inteiro ‘0’ (zero) quando convertido para data se torna ‘1900-01-01 00:00:00.000’, uma segunda-feira.
4: O inteiro ‘-1’ quando convertido para data se torna ‘1899-12-31 00:00:00.000’, um domingo.

Gerando uma lista de meses:

SET LANGUAGE 'Brazilian'
;WITH Meses AS
(
  SELECT 1 AS IdMes, DATENAME(MONTH, 0) AS NomeMes
  UNION ALL
  SELECT IdMes + 1, DATENAME(MONTH, DATEADD(MONTH, IdMes, 0))
  FROM Meses
  WHERE IdMes < 12
)
SELECT * FROM Meses

Gerando uma lista de dias da semana começando pelo domingo:

SET LANGUAGE 'Brazilian'
;WITH DiaSemana AS
(
  SELECT 1 AS idDSe, DATENAME(WEEKDAY, -1) AS nomeDSe
  UNION ALL
  SELECT idDSe + 1, DATENAME(WEEKDAY, idDSe -1)
  FROM DiaSemana
  WHERE idDSe < 7
)
SELECT idDSe, nomeDSe FROM DiaSemana

Ou pela Segunda-Feira:

SET LANGUAGE 'Brazilian'
;WITH DiaSemana AS
(
  SELECT 1 AS idDSe, DATENAME(WEEKDAY, 0) AS nomeDSe
  UNION ALL
  SELECT idDSe + 1, DATENAME(WEEKDAY, idDSe)
  FROM DiaSemana
  WHERE idDSe < 7
)
SELECT idDSe, nomeDSe FROM DiaSemana

Nas duas próximas queries, avanço um pouco a complexidade das CTEs, utilizando a view syslanguage e uma CTE de Split que modifiquei do site do Zavaschi. Elas realmente se diferenciam pelo campo utilizado da syslanguages facilitando alterações neste sentido.

Gerando uma lista dos meses:

DECLARE @Texto VARCHAR(8000),
        @Delimitador CHAR
SET @Texto = (SELECT months FROM sys.syslanguages WHERE alias = 'Brazilian')
SET @Delimitador = ','
;WITH _SPLIT(ID, _INDEX, _LENGTH) AS
(
  SELECT
    1,
    1,
    CHARINDEX(@Delimitador, @Texto + @Delimitador)
UNION ALL
  SELECT
    ID + 1,
    _LENGTH + 1,
    CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1)
  FROM _SPLIT
  WHERE CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1) <> 0
)
, Meses (idMes, nomeMes) AS
(
  SELECT
    ID,
    SUBSTRING(@Texto, _INDEX, _LENGTH - _INDEX)
  FROM _SPLIT
)
SELECT idMes, nomeMes FROM Meses

Gerando uma lista de dias da semana:

DECLARE @Texto VARCHAR(8000),
        @Delimitador CHAR
SET @Texto = (SELECT days FROM sys.syslanguages WHERE alias = 'Brazilian')
SET @Delimitador = ','
;WITH _SPLIT(ID, _INDEX, _LENGTH) AS
(
  SELECT
    1,
    1,
    CHARINDEX(@Delimitador, @Texto + @Delimitador)
UNION ALL
  SELECT
    ID + 1,
    _LENGTH + 1,
    CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1)
  FROM _SPLIT
  WHERE CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1) <> 0
)
, DiaSemana (idDSe, nomeDSe) AS
(
  SELECT
    ID,
    SUBSTRING(@Texto, _INDEX, _LENGTH - _INDEX)
  FROM _SPLIT
)
SELECT idDSe, nomeDSe FROM DiaSemana

Eu espero que elas respondam as necessidades que venham a aparecer!

Não se esqueçam de comentar!

Arquivo de log gigante?

Para quem sofre com este problema, a prática mais comum no SQL Server 2005 é utilizar o Backup Log Truncate em seguida Shrink (at. http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ ), mas será que é realmente a melhor escolha a fazer? Será que não há um motivo para não haver isso no SQL Sever 2008? Será que não vale a pena mudar o Recovery Model do banco de dados para Simple?

Bem quer saber o jeito correto de para resolver o problema com o arquivo de log? Então dê uma boa lida nestes dois tópicos sobre o assunto, escritos pelo MVP Gustavo Maia Aguiar:
Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte I
Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte II

Já tive uns problemas legais com arquivo de log gigante, que nem com o método Truncate/Shrink deu jeito, mas sempre vale a pena verificar se o plano de manutenção do SQL Server não esta mal estruturado (já resolvi este problema assim), ou se tem replicações “mortas” no banco de dados (um dia, talvez eu entenda o que aconteceu e como resolvi o problema com replicações “mortas” e o arquivo de log, para poder escrever sobre).