Uma funcionalidade que existe no SQL Server é a capacidade de iterar pelos resultados de uma consulta através de cursores. Não sou especialista em SQL Server, mas sei que a sua utilização degrada consideravelmente a performance. Talvez se utilizá-lo em uma quantidade pequena de informações, ele pode executar bem, mas não sei ao o impacto que isso causa, devido aos recursos do sistema que ele utiliza para fazer funcionar.

Como havia a necessidade de melhorar o resultado de um relatório extremamente complexo, e as alternativas em T-SQL já estavam esgotadas, a solução foi recriar a Stored Procedure utilizando o .NET. Sustituimos os cursores por SqlDataReaders, e a diferença foi bastante significativa. E ainda nem precisei abrir mão da segurança, já que o Assembly com a Stored Procedure gerenciada, foi catalogado com o nível de segurança definido como Safe.

Suponha que você tenha uma lista na sua aplicação, onde cada um dos elementos desta lista representa  um Id na base de dados. Você precisa submeter esta lista para uma Stored Procedure para atualizar os respectivos registros. Aqui entra o problema: como você irá fazer isso?

Provavelmente você já deve ter passado por esse problema, e as possíveis soluções para isso seria percorrer a lista, elemento à elemento, configurar o(s) parâmetro(s) e, finalmente, invocar a Stored Procedure. Outra possibilidade seria formatar todos os Ids em uma única String, separados por uma vírgula, e na Stored Procedure criar um parâmetro do tipo Varchar(X) para receber tais Ids. O problema da primeira técnica é a quantidade de comandos que você executa, ou seja, se tiver 200 Ids, serão duzentas execuções da Stored Procedure; já a segunda nos obrigará a criar uma query dinâmica, o que poderá tornar o nosso código vulnerável à SQL Injection e, além disso, se a concatenação dos Ids na String resultar em uma String maior do que o comprimento do parâmetro, a query não executará como o esperado.

Uma forma elegante de resolver isso no SQL Server 2008, é utilizando um novo tipo de dado, chamado de Table-Valued Parameter. Com este tipo especial, é possível determinarmos uma estrutura de dados e, definí-lo como parâmetro de entrada em uma Stored Procedure. O código abaixo ilustra a criação do Table-Valued Parameter e, logo em seguida, a Stored Procedure que utiliza o mesmo:

CREATE TYPE ColecaoDeIds AS TABLE
(
    Id INT NOT NULL
)
GO

CREATE PROCEDURE AtualizarUsuarios
    @ColecaoDeIds As ColecaoDeIds READONLY,
    @Ativo As Bit
AS
BEGIN
    UPDATE Usuarios SET
        Ativo = @Ativo
    WHERE Id IN
    (
        SELECT Id FROM @ColecaoDeIds
    )
END
GO

Note que o tipo “ColecaoDeIds” contém apenas uma coluna chamada “Id”, mas você poderia adicionar quantas forem necessárias. Um detalhe importante é que, quando utilizamos um Table-Valued Parameter como parâmetro em uma Stored Procedure, ele deverá obrigatoriamente ser definido como ReadOnly; se precisar inserir, atualizar ou excluir registros dele, então você deve inserir os dados em uma tabela temporária e, depois disso, fazer a manipulação desejada. O interessante é que o Table-Valued Parameter nada mais é que uma espécie de “tabela”, permitindo efetuar queries em cima dela.

Já no código .NET, poucas mudanças são necessárias. Basicamente o que você precisa fazer é, criar uma instância da classe SqlParameter e definir a propriedade SqlDbType para SqlDbType.Structured. Há uma limitação na definição do tipo de objeto que você pode passar como valor para este parâmetro: deverá utilizar DataTable, DbDataReader ou IEnumerable<SqlDataRecord>. Independentemente de qual destes repositórios você irá utilizar, obrigatoriamente eles devem seguir a mesma estrutura do Table-Valued Parameter que, no nosso caso, possui apenas uma coluna chamada “Id” do tipo INT. O código abaixo mostra como podemos proceder para a configuração deste parâmetro:

using (SqlCommand cmd = new SqlCommand(“AtualizarUsuarios”, conn))
{
    cmd.Parameters.Add(
        new SqlParameter(“@ColecaoDeIds”, tuaDataTableAqui) { SqlDbType = SqlDbType.Structured });
}

Se você já utiliza DataTables para armazenamento e/ou manipulação dos dados, então tudo o que precisa fazer é criar uma nova DataTable com a mesma estrutura do Table-Valued Parameter e populá-la. Mas e se estivermos utilizando algum array ou coleção? Para facilitar,, eu criei um extension method chamado ToDataTable<T> que, como o próprio nome diz, transforma a coleção/array em uma DataTable. Como a estrutura da DataTable precisa seguir a mesma estrutura do parâmetro, eu criei um atributo chamado GenerateColumnAttribute, para que você decore as propriedades de sua entidade, para que elas sejam criadas como DataColumns no DataTable. Com essa estrutura, poderíamos fazer algo como:

List<Usuario> list = CarregarUsuarios();

using (SqlCommand cmd = new SqlCommand(“AtualizarUsuarios”, conn))
{
    cmd.Parameters.Add(
        new SqlParameter(
            “@ColecaoDeIds”,
            list.ToDataTable(typeof(GenerateColumnAttribute))) { SqlDbType = SqlDbType.Structured });

    Console.WriteLine(cmd.ExecuteNonQuery());
}

O método ExecuteNonQuery retornará a quantidade de registros afetados pela query. O código .NET de exemplo pode ser encontrado aqui.

Diversas vezes me perguntaram como é possível acessar os dados de metadados, de gerenciamento e manipulação de objetos de um banco de dados SQL Server. Para isso, podemos recorrer ao tradicional SqlConnection e SqlCommand, executando queries, stored procedures e views que trazem informações a respeito destes recursos.

Como alternativa, a Microsoft disponibilizou um conjunto de tipos que permitem o acesso e manipulação, de forma tipada, à um servidor SQL Server, podendo extrair informações ou até mesmo criar novos objetos dentro do mesmo. O namespace em questão é o Microsoft.SqlServer.Management.Smo e está contido no assembly Microsoft.SqlServer.Smo.dll que, em conjunto com alguns outros, permitem acessar um servidor e extrair tais informações, como é mostrado através do exemplo abaixo:

foreach (Database db in new Server("localhost").Databases)
{
    Console.WriteLine(db.Name);

    foreach (Table t in db.Tables)
    {
        Console.WriteLine("\t" + t.Name);

        foreach (Column c in t.Columns)
        {
            Console.WriteLine("\t\t" + c.Name);
        }
    }
}

Durante os treinamentos que ministro, principalmente no curso de ADO.NET (2389) eu sempre oriento o pessoal a utilizar firmemente os Parameters ao invés de utilizar queries concatenadas com valores que são passados via formulários, QueryStrings, ou qualquer outra forma onde é o usuário que informa os dados.

Mas é necessário uma atenção especial quando se utiliza queries dinâmicas dentro do SQL Server. Mesmo utilizando parâmetros pois, neste caso, nem mesmo os parâmetros conseguem evitar o SQL Attack. É muito comum ver o pessoal utilizando essa técnica (eu também já utilizei) para suprir algumas limitações que existem com o SQL Server 2000 e que melhorou no 2005. Imaginem a Stored Procedure abaixo:

CREATE PROCEDURE RecuperaUsuarios
 @Nome As Varchar(50) AS

DECLARE @Query As Varchar(1000)
SET @Query = 'SELECT * FROM Usuarios WHERE Nome = ''' + @Nome + ''''
EXEC(@Query)

E agora o código .NET:

SqlCommand cmd = new SqlCommand("RecuperaUsuarios", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter("@Nome", SqlDbType.VarChar);
param.Value = "'; TRUNCATE TABLE Usuarios--";
cmd.Parameters.Add(param);

Reparem que estou fixando a propriedade Value do objeto SqlParameter, mas o valor poderia ser proveniente de algum formulário que o usuário digitasse. Ao executar esse código, digam adeus aos seus registros.

Mas é importante dizer que muito se utiliza essa técnica (queries dinâmicas) quando utilizamos SQL Server 2000 (e que foi melhorado no 2005) e precisamos passar para a Stored Procedure a quantidade (TOP) de registros que queremos recuperar ou até mesmo quando precisamos efetuar uma paginação de resultados.

Para evitar problemas deste tipo precisamos ter como premissa que tudo que o usuário informa para a aplicação é extremamente perigoso. Isso nos levará a validar todas as informações, tanto do lado do cliente, quando do servidor, ou como fazem alguns bancos: só do lado do servidor. E quando eu digo validação, me refiro a tamanho, range, formato e tipo.

Como já sabemos, o ADO.NET disponibiliza as Base Classes para suportar código genérico para acesso à diversas bases de dados. Uma das coisas que muitos tem dúvidas é com relação à nomenclatura dos parametros, já que isso varia de base de dados para base de dados.

Quando utilizamos SQL Server (SqlClient), o correto é utilizarmos o caracter "@" seguido do nome do parametro. Já no Oracle (OracleClient), ao invés do caracter "@", utilizamos o ":" também seguido do nome do parametro. Nesses dois casos, a ordem de inserção dos parametros na coleção dos mesmos no XXXCommand não importa. Já quando trabalhamos com OleDb, a ordem de inserção é o que predomina, não importando o nome.

Como podemos ver, os formatos variam e, temos que analisar isso em tempo de execução para anexarmos corretamente o parametro da query/Stored Procedure e, para isso, temos que, de alguma forma, saber qual é o tipo de parametros que a base de dados trabalha. Para atender essa necessidade, extraimos essa informação através do método GetSchema da classe DbConnection, exemplo:

string parameterFormat =
     conn.GetSchema("DataSourceInformation").Rows[0]["ParameterMarkerFormat"].ToString();

Esse método, retorna um objeto do tipo DataTable contendo informações do schema. Ele se baseia na instancia da classe concreta que o objeto DbConnection está armazenando, extraindo todos os meta-dados necessários para aquela base de dados. É importante dizer que, para chamar esse método, é necessário que a conexão com a base de dados esteja aberta.

Sinceramente imaginei que as coisas seriam mais fáceis quando se trabalha com Nullable Types e base de dados. Hoje estou fazendo alguns testes para entender o comportamento desta nova feature do .NET Framework 2.0. Independente de qualquer coisa que venho a dizer, é de qualquer forma bem legal trabalhar com eles, mas infelizmente quando fui testar a principal utilização do mesmo ao meu ver, que é em conjunto com uma base de dados, fui surpreendido! Explico:

Imaginei eu que poderia fazer algo como:

Dim dr As SqlDataReader = cmd.ExecuteReader()
'...
Dim dataCompra As Nullable(Of DateTime) = dr.GetDateTime(0)
'...
If Not dataCompra.HasValue Then MessageBox.Show("Valor Nulo.")

Isso seria o ideal ao meu ver, mas quando tentei rodar, me deparei com a seguinte Exception (para minha decepção): System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

Se dermos uma procurada dentro do Reflector pelo método GetDateTime do SqlDataReader, chegamos à função chamada ThrowIfNull(), que atira a exception acima, caso o campo da base de dados que estamos tentando acessar for nulo:

Private Sub ThrowIfNull()
      If Me.IsNull Then
            Throw New SqlNullValueException
      End If
End Sub

Depois disso, resolvi procurar algo no Google a respeito e me deparei com estes dois posts que abordam exatamente isso, ou seja, pelo que entendi, não podemos acessar diretamente os valores nulos da base de dados e atribuirmos aos CLR Nullables Types, sendo necessário uma verificação antes, ficando o código dessa forma:

Dim dr As SqlDataReader = cmd.ExecuteReader()
'...
Dim dataCompra As Nullable(Of DateTime)
If (Not dr.IsDBNull(0)) Then dataCompra = dr.GetDateTime(0)
'...
If Not dataCompra.HasValue Then MessageBox.Show("Valor Nulo.")

E para piorar, nem o "operador" IIf (no caso do VB.NET) não pode ser utilizado por problemas já reportados anteriormente aqui. A questão é que, se eu tranformar a minha variável dataCompra em um tipo SqlDateTime (tipo que está contido dentro do Namespace System.Data.SqlTypes), eu consigo chegar ao resultado esperado sem problemas:

Dim dr As SqlDataReader = cmd.ExecuteReader()
Dim dataCompra As SqlTypes.SqlDateTime = dr.GetSqlDateTime(0)
If dataCompra.IsNull Then MessageBox.Show("Valor Nulo.")

O problema que vejo nisso, é quando vamos atribuir o result-set em um objeto de negócio que temos - quais sempre criamos as suas propriedades com tipos do CLR e não tipos específicos de uma base de dados qualquer - irá obrigar-nos antes de atribuir o valor à mesma, verificar antes se é ou não nulo.

Umas preocupações que mais vejo quando alguém quer incluir datas em uma coluna do tipo DateTime no banco de dados SQL Server 2000 é saber qual o formato enviar. Já vi muitos casos onde o pessoal faz algo do tipo:

     Dim d As DateTime = DateTime.Now
     Dim query As String = "INSERT INTO Tabela VALUES('" & d.ToString("MM/dd/yyyy") & "')"

Eu particularmente também já tive essa dúvida e ao procurar saber, o segredo está em não espeficar um formato como é feito acima, deixando o SqlParameter/SqlCommand se encarregar de fazer essa trabalho por nós. Para consertar o código acima, fazendo da forma correta, temos que criar os devidos parametros para o campo (não somente do tipo data, mas qualquer um) e executar a query. Abaixo o exemplo:

     Dim cmd As New SqlCommand("INSERT INTO Tabela VALUES(@Data)", conn)

     Dim data As New SqlParameter("@Data", SqlDbType.DateTime)
     data.Value = DateTime.Now
     cmd.Parameters.Add(data)
     '....
     cmd.ExecuteNonQuery()

Dando uma olhada no Help do Visual Studio 2005 Beta 2, encontrei um novo operador dentro da linguagem C#. Trata-se do ??. Um exemplo do uso do mesmo, é mostrado abaixo:

     int x = 2;
     int? y = null;

     int z = x ?? y;
     Console.WriteLine(z.ToString());

O sintaxe retorna o valor que está definido do lado esquerdo do operador se o mesmo não for nulo. Caso contrário, o valor que está definido do lado direito do operador é retornado. O output do código acima retorna 2, pois y está definido como nulo. Se alterarmos o valor de y de nulo para algum outro valor válido (inteiro), o valor dele será atribuido à z e, consequentemente exibido.

Para quem quiser saber mais sobre Nullable Types, acesse este link.