sexta-feira, 19 de julho de 2013

Dicas - Codificando Stored Procedures

Criar Stored Procedures é muito útil em qualquer projeto, pois nos proporciona grande flexibilidade no desenvolvimwento. Durante toda nossa atuação em projetos sempre temos algo a aprender e melhorar. Neste intuito, segue abaixo alguns recursos interessantes que podem ser aplicados a qualquer tipo de projeto, pequeno ou grande.

1 - Uso de Cursor

Em projetos grandes o uso de cursor acaba degradando a performance de sua Stored Procedure. Como alternativa, segue recurso que substitui o uso de cursores.

-- Como exemplo, criaremos uma Tabela Temporária qualquer
CREATE TABLE #tmp
(
 cod int,
 valor nvarchar(max)
)

INSERT INTO #tmp VALUES (1, 'Valor1')
INSERT INTO #tmp VALUES (2, 'Valor2')
INSERT INTO #tmp VALUES (3, 'Valor3')

-- Criamos uma nova temporária que vai contér o [ID] de cada linha, para efetuarmos o LOOP.
SELECT ROW_NUMBER() OVER(ORDER BY cod) AS row, valor
INTO #tmpLoop
FROM #tmp

-- Criamos o processo de Loop
DECLARE @iTmp INT = 1
DECLARE @countTmp INT
SELECT @countTmp = COUNT(row) FROM #tmpLoop

DECLARE @valor NVARCHAR(MAX)

WHILE (@iTmp <= @countTmp)
BEGIN 
 SELECT @valor = valor FROM #tmpLoop WHERE row = @iTmp

 SELECT @valor

 SET @iTmp = @iTmp + 1
END

2 - Retorno de Valores
É útil executar, dentro de sua Stored Procedure, uma outra para efetuar algum processo e retornar um valor. Segue exemplos abaixo.


-- Prepara a variável que receberá o valor e a execução da Proc
DECLARE @Valor NVARCHAR(MAX)
EXEC BuscarValor
  @id = 'passa algum parâmetro para a proc',
  @pValor = @Valor OUTPUT

SELECT @Valor

CREATE PROCEDURE BuscarValor
 @id NVARCHAR(MAX),
 @pValor NVARCHAR(MAX) OUTPUT
AS
BEGIN

 -- Efetua alguma busca e alimenta a variável de retorno [@pValor]
 SET @pValor = 'Algum Valor...'

END

Uma forma interessante para alimentar uma variável (@descricao) a partir da execução de um SQL dinâmico

DECLARE @ParameterDefinition NVARCHAR(MAX) = '@descricao NVARCHAR(MAX) OUTPUT'
DECLARE @SQL NVARCHAR(MAX)
DECLARE @descricao NVARCHAR(MAX)

SET @SQL = ' SELECT TOP 1 @descricao = descricao'
SET @SQL = @SQL + ' FROM TB'
SET @SQL = @SQL + ' WHERE ...'

EXECUTE sp_executesql @SQL,
       @ParameterDefinition,
       @descricao OUTPUT

SELECT @descricao

Ter como retorno uma TABELA também é útil

CREATE TABLE #tmp
(
 nome nvarchar(max),
 valor nvarchar(max)
)

EXEC BuscarTabela
  @id = 1

SELECT * FROM #tmp

CREATE PROCEDURE BuscarTabela
(
 @id INT
)
AS
BEGIN

 INSERT INTO #tmp
  SELECT *
   FROM TB
   WHERE ...

END

3 - Manipulação de XML
Com a função [OPENXML] do Sql Server fica fácil converter um XML em uma tabela.


DECLARE @xml XML, @idoc int

-- Imagine o seguinte XML
SET @xml = '<root>
 <campos>
  <campo campo_id="1" campo_nome="campo1" />
  <campo campo_id="2" campo_nome="campo2" />
  <campo campo_id="3" campo_nome="campo3" />
 </campos>
</root>'

IF OBJECT_ID('tempdb..#xmlTable') IS NOT NULL DROP TABLE #xmlTable
CREATE TABLE #xmlTable
(
 campo_id nvarchar(max), 
 campo_nome nvarchar(max)
)

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

INSERT INTO #xmlTable
SELECT *
FROM OPENXML (@idoc, '/root/campos/campo', 2)
 WITH (
   campo_id nvarchar(max) '@campo_id', 
   campo_nome nvarchar(max) '@campo_nome'
   );

EXEC sp_xml_removedocument @idoc

SELECT * FROM #xmlTable

4 - PIVOT Table (CrossTab) 
Com o resurso [pivot] do Sql Server a tarefa fica bem mais simples. Analise o exemplo abaixo.

-- Imagine uma [TABELA_ORIGINAL] que contém a estrutura:
-- linha INT
-- campo NVARCHAR(MAX)
-- valor NVARCHAR(MAX)
-- 
-- EXEMPLO:
-- linha campo valor
-- ---------------------------
-- 1  c1 valor1
-- 1  c2 valor2
-- 2  c1 valor3
-- 2  c2 valor4
--
-- E deseja converter os valores do campo [campo] em COLUNAS
-- Execute a Proc abaixo para montar o SQL que realizará esta tarefa
-- e terá como resultado
--
-- linha c1 c2
-- ---------------------------
-- 1  valor1 valor2
-- 2  valor3 valor4

IF OBJECT_ID('tempdb..#tmpCampos') IS NOT NULL DROP TABLE #tmpCampos

CREATE TABLE #tmpCampos
(
 row int,
 campo nvarchar(max)
)

DECLARE @Insert NVARCHAR(MAX)
SET @Insert = 'INSERT INTO #tmpCampos'
SET @Insert = @Insert + ' SELECT ROW_NUMBER() OVER(ORDER BY campo) row, campo'
SET @Insert = @Insert + ' FROM TABELA_ORIGINAL'
SET @Insert = @Insert + ' GROUP BY campo'
SET @Insert = @Insert + ' ORDER BY campo'
EXEC(@Insert)
 
DECLARE @cols AS NVARCHAR(max), @campos AS NVARCHAR(max), @icampos INT
SET @icampos = (SELECT min(row) FROM #tmpCampos)
 
--Variável para conter a relação de campos
SET @cols = N''
--Montagem da relação de campos
WHILE (@icampos is not null)
BEGIN
 SELECT @campos = campo FROM #tmpCampos WHERE row = @icampos
 SET @cols = @cols+N',[' + @campos + N']'
 SET @icampos = (SELECT min(row) FROM #tmpCampos WHERE row > @icampos)
END

SET @cols = substring(@cols,2,len(@cols))

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT *'
SET @SQL = @SQL + N' FROM (SELECT linha,campo,valor FROM TABELA_ORIGINAL) AS p'
SET @SQL = @SQL + N' PIVOT (MAX(valor) FOR campo IN (' + @cols + N')) AS pv'

That's It.

terça-feira, 2 de julho de 2013

Visualizar relatório (Reporting Services) em ASP.NET MVC

Sabemos que é possível distribuir de forma amigável para o usuário final a visualização de um relatório desenvolvido em Reporting Services como parte de sua aplicação. Porém, para quem está desenvolvendo em ASP.NET MVC, parece não existir uma solução tão trivial, comparada a forma ASPX tradicional.

É de conhecimento dos desenvolvedores o uso da biblioteca [Microsoft.ReportViewer.WebForms.dll] para visualização do relatório em suas páginas, mas para MVC parece não existir [ainda] tal biblioteca!... como resolver isto então? Vamos juntar as duas tecnologias!

Para começar, adicione a biblioteca citada no seu projeto ASP.NET MVC:
- Para o Visual Studio 2010 - \Program Files (x86)\Microsoft Visual Studio 10.0\ReportViewer\
- Para o Visual Studio 2012 - \Program Files (x86)\Microsoft Visual Studio 11.0\ReportViewer\

Depois trabalhe de forma a juntar as tecnologias MVC, com uso do Razor .cshtml/.vbhtml e uma página padrão .aspx, da seguinte maneira:

1 - Crie sua página de PrintPreview.cshtml, tendo como conteúdo:

<script language="javascript" type="text/javascript">

    $(document).ready(function () {

        ResizePrintPreview();
    });

    $(window).resize
    (
        function () { ResizePrintPreview(); }
    );

    function ResizePrintPreview() {
        var windowHeight = $(window).height();
        $('#iframe_printpreview').height(windowHeight);
    }

</script>

<iframe id="iframe_printpreview" src="@ViewBag.srcPrintPreview" width="100%">
</iframe>

Como pontos de atenção temos:
- Duas funções JQuery que coloquei apenas para formatar melhor um IFrame na página
- $(document).ready
- $(window).resize

E o IFrame [iframe_printpreview] que vai mostrar nosso relatório do Reporting Services, através do src [src="@ViewBag.srcPrintPreview"]

2 - Sua Index.cshtml
Como toda aplicação MVC, altere sua Index.cshtml, para contér o seguinte script:

<script src="@Url.Content("~/Scripts/jquery/jquery-1.7.2.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/Relatorio/Relatorio.js")" type="text/javascript"></script>

Isto é necessário para ativar o Script do JQuery e sua biblioteca JS - neste exemplo, Relatorio.js .

3 - Sua biblioteca Relatorio.js

function executarRelatorio(urlRS) {
    var location = String(window.location.protocol) + "//" + String(window.location.host) + "/";
    
    var urlPreview = "/PrintPreview";
    urlPreview += "?urlRS=" + urlRS;     
    urlPreview += "&Location=" + location;

    var sFeatures = "height=600, width=1100, resizable=yes, scroll=yes, status=no";
    window.open(urlPreview, null, sFeatures);
}

O que contém de interesante na função [executarRelatorio]
- A passagem de parâmetro [urlRS] que conterá o caminho do seu relatório do servidor do RS, por exemplo:
http://rsServer/reportserver/Desenv/NomedoMeuRelatorio

- A variável [urlPreview] - vai contér o caminho da função que será executada dentro do CONTROLER principal do seu projeto ASP.NET MVC.

- Os parâmetros que serão passados para a classe PrintPreview do controlador principal do seu projeto.

4 - A função do seu CONTROLADOR

        public ActionResult PrintPreview(string urlRS, string Location)
        {
            var rsName = "reportserver";
            var rsLeng = rsName.Length;
            var pos = urlRS.IndexOf(rsName) + rsLeng;
            var ReportUrl = urlRS.Substring(0, pos);
            var ReportPath = urlRS.Substring(pos, urlRS.Length- pos);

            Session["ReportUrl"] = ReportUrl;
            Session["ReportPath"] = ReportPath;

            ViewBag.srcPrintPreview = Location + "DIRETORIO_ASPX/PrintPreview.aspx";
            
            return View("PrintPreview");
        }

Note a preparação da [ViewBag.srcPrintPreview] usada no passo 1 deste artigo, que finalmente vai mostrar na página ASPX seu relatório do Reporting Services.

Note também que, no lugar do diretório [DIRETORIO_ASPX] que coloquei como exemplo você precisa substituir por um diretório válido.

Importante: Este diretório não pode estar [dentro] da estrutura padrão MVC, pois não é possivel ao mesmo tempo mesclar dentro desta esttrutura as arquiteturas MVC e ASPX padrão. Portanto, crie este diretório fora da estrutura:

PROJETO_MVC\VIEWS\

5 - Faltou agora definir a página PrintPreview.aspx e seu Code-Behind

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PrintPreview.aspx.cs" Inherits="Relatorios.PrintPreview" ValidateRequest="false" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>

        <rsweb:ReportViewer ID="rswebPrintPreview" runat="server" Width="100%" ProcessingMode="Remote">
        </rsweb:ReportViewer>

    </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using Microsoft.Reporting.WebForms;
using System.Web.Helpers;

namespace Relatorios
{
    public class ReportCredentials : Microsoft.Reporting.WebForms.IReportServerCredentials
    {
        string _userName, _password, _domain;

        public ReportCredentials(string userName, string password, string domain)
        {
            _userName = userName;
            _password = password;
            _domain = domain;
        }

        public System.Security.Principal.WindowsIdentity ImpersonationUser
        {
            get
            {
                return null;
            }
        }

        public System.Net.ICredentials NetworkCredentials
        {
            get
            {
                return new System.Net.NetworkCredential(_userName, _password, _domain);
            }
        }

        public bool GetFormsCredentials(out System.Net.Cookie authCoki, out string userName, out string password, out string authority)
        {
            userName = _userName;
            password = _password;
            authority = _domain;
            authCoki = new System.Net.Cookie(".ASPXAUTH", ".ASPXAUTH", "/", _domain);
            return true;
        }
    }

    public partial class PrintPreview : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (this.IsPostBack) return;

            string usr = "reportUserName";
            string pwd = "reportPassword";
            string domain = "reportDomain";

            string ReportUrl = Session["ReportUrl"].ToString();
            string ReportPath = Session["ReportPath"].ToString();

            rswebPrintPreview.ServerReport.ReportServerCredentials = new ReportCredentials(usr, pwd, domain);
            rswebPrintPreview.ServerReport.ReportServerUrl = new Uri(ReportUrl);
            rswebPrintPreview.ServerReport.ReportPath = String.Format(ReportPath);
        }
    }
}

Portanto, em resumo, faremos o seguinte:
- Criaremos uma PartialView chamada PrintPreview.cshtml;
- Adicionaremos os scripts necessários na Index.cshtml;
- Criaremos a biblioteca Relatorio.js, com a função que será chamada, por exemplo, ao clicar em um Botão dentro de sua Index.cshtml;
- Adicionaremos a função PrintPreview dentro do seu CONTROLADOR do ASP.NET MVC;
- Criaremos a página PrintPreview.aspx e seu Code-Behind

Abraços!