Desenvolvimento Código Aberto

Stored Procedures são procedimentos de código SQL armazenados no banco de dados, as stored procedures são mais rápidas do que o código tradicional, o que significa que elas estão se tornando cada vez mais populares. Com código da aplicação se mudando dos programas externos para o motor de banco de dados, Os DBAs precisam entender os requisitos de memória relacionados para procedimentos armazenados e saber como gerenciar estes procedimentos para o ideal desempenho do banco de dados. Isto muda de banco para banco, cada um deles possui suas recomendações de melhores praticas para atingir um melhor desempenho.

Neste site existe uma versão deste mesmo programa utilizando código SQL direto dentro da aplicação e podemos comparar a diferença com o código SQL sendo executado dentro do motor do banco de dados e o resultado é visivelmente notado, sem duvida o código executado utilizando procedures é mais rápido.

Ainda existe um outro modo de utilizar o banco de dados que é utilizando um set de dados persistente, onde uma camada  de classes ou componentes geram o código SQL para o banco de dados através de instruções definidas pelas classes, mas este método por possuir uma camada que cria o código para o banco pode se tornar menos atrativo em termos de desempenho, apesar de ser muito mais fácil e rápido para se desenvolver, sendo assim vale avaliar a quantidade de dados que sua aplicação pretende manipular.

Para adquirir o script SQL para criar a tabela deste exemplo ou visualizar o método utilizando SQL direto da aplicação: clique aqui.

Restrições

Um problema com o ADO.NET é que a Microsoft não pode acessar todos os recursos dos bancos de dados Oracle e IBM DB2, então se o desenvolvedor optar assim como nós por utilizar os drivers de cada fornecedor, diferentemente da linguagem de programação JAVA que também é uma linguagem nativa para os bancos de dados Oracle e DB2, não é possível utilizar instruções C# únicas para os diferentes bancos de dados, pois os drivers acabam sendo incompatíveis, uma opção seria criar um dicionário de dados.

Stored Procedure

Visual Studio

Crie um novo projeto C# e utilize a imagem abaixo para criar o design da aplicação utilizando 2 componentes Panels, 7 componentes Labels, 6 TextBoxes e um componente Button.

Design

Exemplo:

Neste exemplo utilizamos Stored Procedures que retornam parâmetros para criar um método dentro do banco de dados ao invés de criar o método dentro da aplicação utilizando a linguagem de programação nativa.

Ao contrario da linguagem de programação Java não podemos utilizar o mesmo objeto para manipular todos os bancos de dados e utilizar todos os seus recursos ao mesmo tempo, a não ser se pretendermos restringir a aplicação apenas aos recursos comuns entre os três bancos de dados e utilizar apenas os drivers fornecidos pela Microsoft.

Obs: Para criar uma Stored Procedure utilizando o IBM Data Studio, você deve utilizar o editor de procedures e não o editor comum de SQL, pois encontrará um erro do caractere ponto e virgula no momento de escrever sua sintaxe.

Oracle

create or replace PROCEDURE buscaFuncionario
- Cria parametros
(pid_funcionario IN FUNCIONARIOS.ID_FUNCIONARIO%TYPE,
 p_nome OUT FUNCIONARIOS.NOME%TYPE,
 p_sobrenome OUT FUNCIONARIOS.SOBRENOME%TYPE,
 p_cargo OUT FUNCIONARIOS.CARGO%TYPE,
 p_salario OUT FUNCIONARIOS.SALARIO%TYPE
 )
AS
BEGIN
-- Insere dados da query dentro dos parametros de saida
  SELECT NOME, SOBRENOME, CARGO, SALARIO
  INTO p_nome, p_sobrenome, p_cargo, p_salario
  FROM FUNCIONARIOS
  WHERE ID_FUNCIONARIO = pid_funcionario;

END;

DB2

-- Cria procedure
CREATE PROCEDURE buscaFuncionario
( IN pid_funcionario INTEGER,
  OUT p_nome VARCHAR(30),
  OUT p_sobrenome VARCHAR(70),
  OUT p_cargo VARCHAR(30),
  OUT p_salario DECIMAL(9,2)
 )

P1: BEGIN

  SELECT NOME, SOBRENOME, CARGO, SALARIO
  INTO p_nome, p_sobrenome, p_cargo, p_salario
  FROM FUNCIONARIOS
  WHERE ID_FUNCIONARIO = pid_funcionario;

END P1

MSSQL

CREATE PROCEDURE buscaFuncionario
   @pfuncionario INT,
   @pnome		 NVarchar(30) OUTPUT,
   @psobrenome	 NVarchar(70) OUTPUT,
   @pcargo	     NVarchar(30) OUTPUT,
   @psalario	 Decimal(9,2) OUTPUT
AS
BEGIN
   SELECT @pnome = Nome,
		  @psobrenome = Sobrenome,
		  @pcargo = Cargo,
		  @psalario = Salario
   FROM Funcionarios
   WHERE ID_Funcionario = @pfuncionario
END

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;
using IBM.Data.DB2;

namespace StoredCs
{
    public partial class Procedures : Form
    {
        // Declara componentes de conexão
        private static OracleConnection connORA; // ODAC 12c
        private static DB2Connection connDB2;   // IBM Data Server Provider
        private static SqlConnection connMSSQL; // ADO .NET

       // ************** Sobre Conexões - ADO.NET **************
       //
       // A Microsoft disponibiliza por exemplo, o System.Data.OracleClient
       // porem se tornou obsoleto e ainda é suportado somente
       // a nível de compatibilidade com versões anteriores do Framework.
       // A Microsoft recomenda utilizar o driver de cada fornecedor:
       //
       // veja: http://msdn.microsoft.com/en-us/library/77d8yct7.aspx
       //
       // Você pode utilizar a classe DbProviderFactory
       // para criar um único datasource para todos os bancos de dados:
       //
       // http://msdn.microsoft.com/pt-br/library/system.data.common.dbproviderfactory(v=vs.110).aspx
       //
       // No entanto diferentemente da linguagem JAVA (JDBC) o ADO.NET não suporta
       // alguns recursos do Oracle e IBM DB2.
       //
       // *** Factory ***
       //
       // Utilizando conexões únicas de cada provedor de banco de dados
       // através de um Factory, permite que você utilize um único set de instruções
       // para todos os bancos de dados.
       //
       // Atente-se que se utilizar instruções únicas, em alguns casos poderá encontrar
       // alguns erros de compatibilidade ou criar certas limitações a outros bancos.
       // O ADO.NET é desenvolvido para o MSSQL Server e está sujeito
       // a algumas limitações quando utilizar alguns tipos de campos,
       // conceitos de conexão e acesso a dados de outros bancos de dados.
       //
       // Sistemas de grande porte possuem um Dicionário de dados
       // para se prevenir destas situações.
       //
       // Veja este mesmo programa utilizando o DriverManager equivalente ao factory para java
       // e veja comentários extras no código apontando diferenças de conceitos.

        // Declara variável do banco de dados
        private static string DBconexao;

        public Procedures()
        {
            InitializeComponent();
        }

        public void conectarDB(string banco)
        {
            DBconexao = banco;

            if (banco == "oracle")
            {

                try
                {
                    // String de Conexao
                    string connectionString =

                    // Usuario
                    "User Id=daberto" +

                    // Senha
                    ";Password=p@55w0rd" +

                    // TNSnames
                    ";Data Source=XE";

                    //Conecta ao datasource usando a conexão Oracle
                    connORA = new OracleConnection(connectionString);

                    //Abre a conexão com o banco de dados
                    connORA.Open();
                }
                // Retorna erro
                catch (Exception ex)
                {
                    // Mostra menssagem de erro
                    MessageBox.Show(ex.ToString());
                }
            }

            if (banco == "db2")
            {
                try
                {
                    // String de Conexao
                    string connectionString =

                        // Servidor
                        "Server=localhost" +

                        // Banco de dados
                        ";Database=DEVA" +

                        // Usuario
                        ";UID=db2admin" +

                        // Senha
                        ";PWD=p@55w0rd" +

                        // Timeout
                        ";Connect Timeout=40";

                    //Conecta ao datasource usando a conexão DB2
                    connDB2 = new DB2Connection(connectionString);

                    //Abre a conexão com o banco de dados
                    connDB2.Open();

                }
                // Retorna erro
                catch (Exception ex)
                {
                    // Mostra menssagem de erro
                    MessageBox.Show(ex.ToString());

                }

            }

            if (banco == "mssql")
            {
                try
                {
                    // String de Conexao
                    string connectionString =

                        // Servidor
                        "Data Source=localhost" +

                        // Banco de dados
                        ";Initial Catalog=DevAberto" +

                        // Usuario
                        ";User ID =devaberto" +

                        // Senha
                        ";Password=p@55w0rd" +

                        // Timeout
                        ";Connect Timeout=40";

                    //Conecta ao datasource usando a conexão Padrão
                    connMSSQL = new SqlConnection(connectionString);

                    //Abre a conexão com o banco de dados
                    connMSSQL.Open();

                }
                // Retorna erro
                catch (Exception ex)
                {
                    // Mostra menssagem de erro
                    MessageBox.Show(ex.ToString());

                }

            }

        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Cria instancia do objeto
            Procedures proc = new Procedures();

            // A variável abaixo:
            // Define banco de dados
            // oracle = Oracle Database
            // db2    = IBM DB2 Database
            // mssql  = Microsoft SQL Server
            proc.conectarDB("db2");

            // Define conexão
            label7.Text = "Database - Stored Procedure - " + DBconexao;

            // Define comandos
            // Um problema com as classes do ADO.NET entre o Oracle e o IBM DB2
            // são a incompatibilidade de drivers e alguns dos tipos de campos.
            // Se você pretende utilizar todos os recursos destes bancos você não pode
            // utilizar um driver ADO.NET Nativo e sim um driver do proprio fabricante.
            // Apesar da sintaxe ser igual, em certos casos não é possível utilizar um unico comando
            // para manipular dados entre todos os bancos.

            OracleCommand cmdORA;
            DB2Command cmdDB2;
            SqlCommand cmdSQL;            

            if (DBconexao== "oracle")
            {
                // Define a instrução SQL e a conexão
                cmdORA = new OracleCommand("buscaFuncionario", connORA);

                // Define tipo como Stored procedure
                cmdORA.CommandType = CommandType.StoredProcedure;

                // Define parametros, tipos de campos e direção
                cmdORA.Parameters.Add("pid_funcionario", OracleDbType.Double).Value = Convert.ToDouble(textBox1.Text);
                cmdORA.Parameters.Add("p_nome", OracleDbType.Varchar2, 30).Direction = ParameterDirection.Output;
                cmdORA.Parameters.Add("p_sobrenome", OracleDbType.Varchar2, 70).Direction = ParameterDirection.Output;
                cmdORA.Parameters.Add("p_cargo", OracleDbType.Varchar2, 30).Direction = ParameterDirection.Output;
                cmdORA.Parameters.Add("P_salario", OracleDbType.Decimal).Direction = ParameterDirection.Output;

                // Executa query
                cmdORA.ExecuteNonQuery();

                // Neste caso você pode alimentar variaveis e
                // criar uma unica exibição para todos os bancos
                // optamos neste exemplo pelo classico CTRL+C  e CTRL + V

                textBox2.Text = textBox1.Text;
                textBox3.Text = cmdORA.Parameters[1].Value.ToString();
                textBox4.Text = cmdORA.Parameters[2].Value.ToString();
                textBox5.Text = cmdORA.Parameters[3].Value.ToString();
                textBox6.Text = cmdORA.Parameters[4].Value.ToString();
            }

            // Aqui foi efetuado um CTRL + C e um CTRL + V
            // Mudando apenas os drivers, uma opção é criar um dicionario de dados

            if (DBconexao == "db2")
            {
                cmdDB2 = new DB2Command("buscaFuncionario", connDB2);

                cmdDB2.CommandType = CommandType.StoredProcedure;

                cmdDB2.Parameters.Add("pid_funcionario", DB2Type.Integer).Value = Convert.ToInt32(textBox1.Text);
                cmdDB2.Parameters.Add("p_nome", DB2Type.VarChar, 30).Direction = ParameterDirection.Output;
                cmdDB2.Parameters.Add("p_sobrenome", DB2Type.VarChar, 70).Direction = ParameterDirection.Output;
                cmdDB2.Parameters.Add("p_cargo", DB2Type.VarChar, 30).Direction = ParameterDirection.Output;
                cmdDB2.Parameters.Add("p_salario", DB2Type.Decimal).Direction = ParameterDirection.Output;

                cmdDB2.ExecuteNonQuery();

                textBox2.Text = textBox1.Text;
                textBox3.Text = cmdDB2.Parameters[1].Value.ToString();
                textBox4.Text = cmdDB2.Parameters[2].Value.ToString();
                textBox5.Text = cmdDB2.Parameters[3].Value.ToString();
                textBox6.Text = cmdDB2.Parameters[4].Value.ToString();

            }

            if (DBconexao == "mssql")
            {
                cmdSQL = new SqlCommand("buscaFuncionario", connMSSQL);

                cmdSQL.CommandType = CommandType.StoredProcedure;

                cmdSQL.Parameters.Add("@pfuncionario", SqlDbType.Int).Value = Convert.ToInt32(textBox1.Text);
                cmdSQL.Parameters.Add("@pnome", SqlDbType.NVarChar, 30).Direction = ParameterDirection.Output;
                cmdSQL.Parameters.Add("@psobrenome", SqlDbType.NVarChar, 70).Direction = ParameterDirection.Output;
                cmdSQL.Parameters.Add("@pcargo", SqlDbType.NVarChar, 30).Direction = ParameterDirection.Output;
                cmdSQL.Parameters.Add("@Psalario", SqlDbType.Decimal).Direction = ParameterDirection.Output;

                cmdSQL.ExecuteNonQuery();

                textBox2.Text = textBox1.Text;
                textBox3.Text = cmdSQL.Parameters[1].Value.ToString();
                textBox4.Text = cmdSQL.Parameters[2].Value.ToString();
                textBox5.Text = cmdSQL.Parameters[3].Value.ToString();
                textBox6.Text = cmdSQL.Parameters[4].Value.ToString();

            }
        }
    }
}