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();
}
}
}
}
Ricardo Mantovani
Desenvolvimento Código Aberto 