Criando Banco de dados , Tabelas , Stored Procedures e Views no MSDE

VB.NET - Criando Banco de dados , tabelas, Stored Procedures e Views no MSDE

Você saberia criar um banco de dados , tabelas , stored procedures e Views no MSDE (SQL Server) via código ser for preciso ?

Pois se não sabia ao final deste artigo vai fazer isto com um pé nas costas... Sem contar que todo o código usado aqui para o MSDE por tabela é válido também para o SQL Server , afinal , o MSDE é o primo pobre do SQL Server.

Para testar o código deste artigo você precisa ter o MSDE ou o SQL Server instalado em sua máquina e corretamente configurado com as permissões adequadas. Portanto você vai precisar :

Ter o MSDE instalado - MSDE - Instalação e migração de dados usando Access 2000

Nota: Para saber mais sobre o MSDE leia também os seguintes artigos :

Access ou SQL Server ? Entre a cruz e a espada ? Que tal o MSDE ?
MSDE - Criando tabelas com o Web Matrix
Migração Access -> SQL Server
Usando MSDE com o Visual Basic

Para relembrar a sintaxe DDL e de outros conceitos relacionados ao SQL Server leia os artigos :

SQL SERVER - Usando a linguagem de definição de dados (DDL)
Migração Access -> SQL Server
Access ou SQL Server ? Entre a cruz e a espada ? Que tal o MSDE ?
SQL - Triggers
ASP - Stored Procedures : migrando do Access para o SQL Server

Nota: Por que eu insisto tanto no MSDE ? Nada contra o SQL Server mas o MSDE é grátis e o SQL Server não.

Inicie um novo projeto no Visual Studio.NET com as seguintes características (sinta-se a vontade para alterar a seu gosto.)

Project Types : Visual Basic Projects
Templates : Windows Application
Name : gerenciaDados
Location : c:\vbnet\gerenciaDados

- Selecione o arquivo form1.vb no Solution Explorer e na janela propriedades altere o nome do arquivo para gerenciaEstruturaDados.vb

No formulário principal carregado inclua um componente DataGrid - DataGrid1 - e 10 botões de comando conforme o layout abaixo:

Declare o seguinte namespace no seu projeto:

Imports System.Data.SqlClient

A seguir no início do formulário declare as variáveis objetos

Private reader As SqlDataReader = Nothing
Private conn As SqlConnection = Nothing
Private cmd As SqlCommand = Nothing
Private sql As String = Nothing
Private connectionstring As String

No evento Load do formulário defina a string de conexão :

 Private Sub frmGerenciaEstruturaDados_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     connectionstring = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=;Data Source=MACORATI\VSDOTNET;"
 End Sub

Esta string de conexão inicial se conecta com o meu servidor MSDE (MACORATI\VSDOTNET) mas não define um catálogo inicial.

A primeira tarefa será criar um novo Banco de dados no MSDE. Para isto usamos o código abaixo no evento Click do botão - Criar DB:

Private Sub criaDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles criaDB.Click
        ' Cria uma conexão
        conn = New SqlConnection(connectionstring)
        ' Abre a conexão
        If conn.State <> ConnectionState.Open Then
            Try
                conn.Open()
            Catch ex As SqlException
                MsgBox(ex.Message & " - " & ex.Number & " - " & ex.Source, MsgBoxStyle.Critical)
            End Try
        End If
        'cria a instruão para criar o banco de dados
        Dim sql As String = "CREATE DATABASE macoratiDB ON PRIMARY" + _ 
                                  "(Name=teste_dado, filename = 'd:\teste\macoratiDB_data.mdf', size=3," + "maxsize=5, filegrowth=10%)log on" + _ 
                                  "(name=macoratiDB_log, filename='d:\teste\macoratiDB_log.ldf',size=3," + "maxsize=20,filegrowth=1)"
        'executa a instrução
        ExecuteSQLStmt(sql)
    End Sub

Neste código estou criando um novo objeto Connectin (conn) e verificando o estado da conexão (state). Se ela estiver aberta eu fecho a conexão e crio uma nova conexão usando a string de conexão (connectionstring) atual.

A seguir eu monto a string com o comando SQL para criar o banco de dados no MSDE. A sintaxe é idêntica para o SQL Server. Finalmente executo a instrução SQL chamando o rotina ExecuteSQLStmt(sql)

O código da rotina ExecuteSQLStmt é mostrado a seguir :

 Private Sub ExecuteSQLStmt(ByVal sql As String)
        ' Cria uma conexão
        conn = New SqlConnection(connectionstring)
        ' Verifica a conexao e abre a uma nova conexao
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        connectionstring = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=macoratiDB; _
                                  Data Source=MACORATI\VSDOTNET;"
        conn.ConnectionString = connectionstring
        conn.Open()
        'cria um objeto command
        cmd = New SqlCommand(sql, conn)
        Try
            'executa o comando sem retornar registros
            cmd.ExecuteNonQuery()
            MsgBox("Operação realizada com sucesso.", MsgBoxStyle.Exclamation, "Executa instrução")
        Catch ae As SqlException
            'exibe mensagem de erros
            MessageBox.Show(ae.Message.ToString())
        End Try
    End Sub

A novidade neste código é que eu estou criando uma nova string de conexão definindo parâmetro Initial Catalog com o nome do banco de dados que eu vou criar e no qual eu vou criar os demais objetos.

A seguir crio um novo objeto Command e usando o método ExecuteNonQuery para executar o comando SQL e não retornar registros. (para saber mais sobre este método leia o artigo : VB.NET - Operações com dados que não retornam registros. )

O código para criar um nova tabela no banco de dados macoratiDB esta no evento click do botão - Criar Tabela :

 Private Sub criaTabela_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles criaTabela.Click

        ' Cria uma conexão
        conn = New SqlConnection(connectionstring)
        ' Abre a conexao
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If

        connectionstring = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=macoratiDB; _ 
                                  Data Source=MACORATI\VSDOTNET;"

        conn.ConnectionString = connectionstring
        conn.Open()

        'cria a instrução para criar a tabela
        sql = "CREATE TABLE macoratiTabela" + "(codigo INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + 
                "Nome CHAR(50), Endereco CHAR(255), Salario FLOAT)"

        cmd = New SqlCommand(sql, conn)

        Try
            cmd.ExecuteNonQuery()
            ' inclui alguns registros na tabela
            sql = "INSERT INTO macoratiTabela(codigo, Nome, Endereco, Salario) " + _ 
                   "VALUES (001, 'Jose Carlos Macoratti', 'R. XV Novembro , 449', 1230.98 ) "
            cmd = New SqlCommand(sql, conn)
            cmd.ExecuteNonQuery()
            sql = "INSERT INTO macoratiTabela(codigo, Nome, Endereco, Salario) " + _ 
                    "VALUES (002, 'Jessica Naara Macoratti', 'Av. Aeroporto , 1092', 2353.64) "
            cmd = New SqlCommand(sql, conn)
            cmd.ExecuteNonQuery()
            sql = "INSERT INTO macoratiTabela(codigo, Nome, Endereco, Salario) " + _ 
                   "VALUES (003, 'Janice Raquel Siqueira', 'Travessa Olinda , 91', 1430.43) "
            cmd = New SqlCommand(sql, conn)
            cmd.ExecuteNonQuery()
            sql = "INSERT INTO macoratiTabela(codigo, Nome, Endereco, Salario) " + _ 
                   "VALUES (004, 'Marcia Ricci', 'Pça Panamericana , 12', 3230.00) "
            cmd = New SqlCommand(sql, conn)
            'executa a instrução que nao retorna registros
            cmd.ExecuteNonQuery()
        Catch ae As SqlException
            MessageBox.Show(ae.Message.ToString())
        End Try
    End Sub

Neste código eu monto a instrução SQL CREATE TABLE - para criar a tabela : macoratiTabela. A tabela possui a seguinte estrutura:

A seguir ,usando a instrução INSERT INTO estou incluindo 4 registros com respectivos valores na tabela para que ela possa ser exibida no datagrid. Após a execução do código teremos os seguintes dados na tabela :

Criar uma stored Procedure é bem simples basta usar o comando CREATE PROCEDURE. No código abaixo estou criando a stored procedure macoratiSP que irá retornar o nome e endereço da tabela.

Private Sub criaSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles criaSP.Click
        sql = "CREATE PROCEDURE macoratiSP AS" + " SELECT Nome, Endereco FROM macoratiTabela GO"
        ExecuteSQLStmt(sql)
End Sub

A seguir temos o código que cria a View (CREATE VIEW): macoratiView que exibe o campo nome dos registros da tabela:

 Private Sub criaView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles criaView.Click
        sql = "CREATE VIEW macoratiView AS SELECT nome FROM macoratiTabela"
        ExecuteSQLStmt(sql)
 End Sub

Podemos também alterar a estrutura da tabela criada usando o comando ALTER TABLE ... COLUMN. No caso estou alterando o tamanho a coluna Nome de 50 para 100 caracteres.

Private Sub alteraTabela_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles alteraTabela.Click
        sql = "ALTER TABLE macoratiTabela ALTER COLUMN " + "Nome CHAR(100) NOT NULL"
        ExecuteSQLStmt(sql)
End Sub

Com todas as rotinas prontos podemos partir para o código que visualiza o resultado de cada uma delas exibindo os valores no DataGrid do formulário.

1- Exibindo os dados da tabela macoratiTabela no datagrid

 Private Sub verDados_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles verDados.Click
        ' Cria uma conexão
        conn = New SqlConnection(connectionstring)
        ' 'Abre a conexao
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        connectionstring = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=macoratiDB; _ 
                                  Data Source=MACORATI\VSDOTNET;"

        conn.ConnectionString = connectionstring
        conn.Open()

        ' Cria um dataadapter
        Dim da As New SqlDataAdapter("SELECT * FROM macoratiTabela", conn)

        ' Cria um dataset , preenche e exibe os dados no dataagrid
        Dim ds As New DataSet("macoratiTabela")

        da.Fill(ds, "macoratiTabela")
        DataGrid1.DataSource = ds.Tables("macoratiTabela").DefaultView

    End Sub

Estou criando um DataAdapter sobre a conexão para exibir todos os registros da tabela macoratiTabela ; a seguir estou preenchendo um DataSet e vinculando os dados no modo Default no DataGrid. O resultado é exibido abaixo:

2- Exibindo os dados da stored Procedure : macoratiSP

Private Sub verSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles verSP.Click
        ' Cria uma conexão
        conn = New SqlConnection(connectionstring)
        'Abre a conexao
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If

        connectionstring = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=macoratiDB; _ 
                                   Data Source=MACORATI\VSDOTNET;"
        conn.ConnectionString = connectionstring
        conn.Open()

        ' Cria um dataadapter
        Dim da As New SqlDataAdapter("macoratiSP", conn)

        ' Cria um DataSet, preenche e exibe os dados
        Dim ds As New DataSet("SP")
        da.Fill(ds, "SP")

        DataGrid1.DataSource = ds.DefaultViewManager

    End Sub

O resultado da exibição da SP é :

3- Para exibir os dados da View - macoratiView , usamos o código :

Private Sub verView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles verView.Click
        ' Cria uma conexão
        conn = New SqlConnection(connectionstring)
        ' 'Abre a conexao
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        connectionstring = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=macoratiDB; _ 
                                  Data Source=MACORATI\VSDOTNET;"
        conn.ConnectionString = connectionstring
        conn.Open()
        ' Cria um dataadapter
        Dim da As New SqlDataAdapter("SELECT * FROM macoratiView", conn)
        ' Cria um DataSet, preenche e exibe os dados
        Dim ds As New DataSet
        da.Fill(ds)
        DataGrid1.DataSource = ds.DefaultViewManager
    End Sub

O resultado da exibição da View é :

Finalmente para ter certeza de que as conexões foram encerradas utilizei o rotina appSai() que é chamada no evento CLick do botão Sair.

O código verifica se o DataReader e a conexão estão vivos e os fecha liberando a memória.

Private Sub AppSai()
        If Not (reader Is Nothing) Then
            reader.Close()
        End If
        If Not (conn Is Nothing) Then
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End If
    End Sub

Você acabou de aprender a criar banco de dados , criar tabelas , criar stored procedures , criar Views , alterar estrutura de tabelas em um banco de dados MSDE (SQL Server) via código.

É isto ai garoto ... pegue o código completo aqui : gerenciaDados.zip

Eu sei , é apenas VB.NET , mas eu gosto...

Macoratti