VB .NET - Tratando com instâncias do SQL Server, Banco de dados e Tabelas

Neste artigo vou mostrar como usar os recursos do SQL Server e através de uma aplicação Visual Basic realizar algumas operações com a instância do SQL Server Express local instalado.

Nossa aplicação irá precisar usar um banco de dados chamado EscolaDB e uma tabela Alunos e iremos criar esses recursos em tempo de execução se eles não existirem verificando se existe uma instância do SQL Server, o banco de dados e a tabela.

Então, ao executar a aplicação Visual Basic pela primeira vez ela deverá fazer o seguinte:

1- Verificar se existe uma instância do SQL Server rodando ou se ela esta parada ou pausada;
Se o SQL Server não estiver em execução será exibida uma mensagem ao usuário para que verifique se o SQL Server esta parado ou suspenso e que ponha a instância em execução

2- Se existir um a instância do SQL Server em execução vamos verificar se o banco de dados EscolaDB existe;
- Se o banco de dados não existir iremos criar o banco de dados no SQL Server rodando um script SQL existente no projeto;
- A seguir vamos verificar se a tabela existe no banco de dados, e executar um script SQL existente no projeto para criar a tabela no banco de dados;

3 - Verificar se o banco de dados existe e se a tabela não existe;
- A seguir vamos verificar se a tabela existe no banco de dados, e executar um script SQL existente no projeto para criar a tabela no banco de dados;

Os recursos usados no projeto são :

Criando o Projeto

Abra o Visual Basic 2010 Express Editon e crie um projeto do tipo Window Forms Application com o nome CriaSQLServerNet;

No formulário form1.vb inclua um controle Button (btnEncerrar) com o texto Sair;

Abaixo temos a imagem do formulário form1.vb com imagem ilustrativa:

Clique com o botão direito do mouse sobre o nome do projeto e selecione Add ->New Folder e informe o nome DALHelper;

Repita o procedimento acima e crie a pasta Scripts;

Neste momento nosso projeto irá apresentar na janela Solution Explorer a estrutura mostrada na figura abaixo :

Na pasta Scripts vamos criar os Scripts SQL para criar o banco de dados EscolaDB e a tabela Alunos e na pasta DALHelper vamos criar a classe com os métodos que irão verificar a instância do SQL Server, obter o nome da instância, criar o banco de dados e a tabela;

Criando os scripts SQL

Clique com o botão direito do mouse sobre a pasta Scripts e selecione Add New Item;

A seguir selecione o template Code File e informe o nome CriaEscolaBD.sql e clique no botão Add;

Repita o procedimento acima e crie outro arquivo de script com o nome CriaTabelaAlunos.sql;

1- Abra o arquivo CriaEscolaBD.sql e digite o código a seguir :

CREATE DATABASE [EscolaDB] ON  PRIMARY 
( NAME = N'EscolaDB', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\EscolaDB.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'EscolaDB_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\EscolaDB_log.LDF' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [EscolaDB] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [EscolaDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [EscolaDB] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [EscolaDB] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [EscolaDB] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [EscolaDB] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [EscolaDB] SET ARITHABORT OFF 
GO
ALTER DATABASE [EscolaDB] SET AUTO_CLOSE ON 
GO
ALTER DATABASE [EscolaDB] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [EscolaDB] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [EscolaDB] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [EscolaDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [EscolaDB] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [EscolaDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [EscolaDB] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [EscolaDB] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [EscolaDB] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [EscolaDB] SET  ENABLE_BROKER 
GO
ALTER DATABASE [EscolaDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [EscolaDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [EscolaDB] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [EscolaDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [EscolaDB] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [EscolaDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [EscolaDB] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [EscolaDB] SET  READ_WRITE 
GO
ALTER DATABASE [EscolaDB] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [EscolaDB] SET  MULTI_USER 
GO
ALTER DATABASE [EscolaDB] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [EscolaDB] SET DB_CHAINING OFF 
GO

Este script irá criar o banco de dados SQL Server EscolaDB na instância local do SQL Server;

Agora abra o arquivo CriaTabelaAlunos.sql e digite o código abaixo:

SET QUOTED_IDENTIFIER OFF;
GO
USE [EscolaDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

IF OBJECT_ID(N'[dbo].[Alunos]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Alunos];
GO

CREATE TABLE [dbo].[Alunos] (
    [alunoID] int IDENTITY(1,1) NOT NULL,
    [nome] nvarchar(50)  NULL,
    [endereco] nvarchar(50)  NULL,
    [cep] nvarchar(50)  NULL,
    [cidade] nvarchar(50)  NULL,
    [pais] nvarchar(50)  NULL,
    [foto] varbinary(max)  NULL,
    [notas] nvarchar(max)  NULL
);
GO

ALTER TABLE [dbo].[Alunos]
ADD CONSTRAINT [PK_Alunos]
    PRIMARY KEY CLUSTERED ([alunoID] ASC);

Este script irá criar a tabela Alunos no banco de dados EscolaDB com os seguintes campos:

Vamos incluir na pasta Scripts alguns arquivos .dll referente ao SQL que serão usados em nossa classe DALHelper;

Clique com o botão direito do mouse sobre a pasta Scripts e selecione Add -> Existing Item e selecione os arquivo abaixo na pasta : C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

Agora vamos criar a nossa classe DALHelper na pasta DALHelper;

Selecione a pasta DALHelper e no menu Project clique em Add Class e informe o nome DALHelper.vb;

A seguir digite o código abaixo neste arquivo:

Imports System
Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
Imports System.ServiceProcess
Imports System.Reflection

Public Class DALHelper

    'estas constantes poderiam ser obtidas de um arquivo de configuração externo
    'para simplificar o código estou declarando-as como constantes no arquivo DALHelper
    Public Const strNomeBancoDados As String = "EscolaDB"
    Public Const strNomeTabela As String = "Alunos"
    Public Const strSQLCriaTabelaAlunos As String = "..\..\Scripts\CriaTabelaAlunos.sql"
    Public Const strSQLCriaBancoDadosEscolaDB As String = "..\..\Scripts\CriaEscolaBD.sql"
    Public Shared strConnString As String = ""

    'verifica se o SQL Server esta em execução
    Public Shared Function verificaSeSQLServerNaoEstaRodando() As Boolean
        Dim servicoControle As New ServiceController("MSSQL$SQLEXPRESS")
        If servicoControle.Status <> ServiceControllerStatus.Running OrElse servicoControle.Status = ServiceControllerStatus.Stopped 
OrElse servicoControle.Status = ServiceControllerStatus.Paused Then
            servicoControle.Start()
            Return True
        End If
        Return False
    End Function
    Public Shared Function verificaSeBancoDadosExiste(ByVal strNomeBD As String) As Boolean
        'Inclua referências a todas as .dll's que estão na pasta  "Scripts" 
        'estes arquivos dll's estão na pasta C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
        Dim dbServer As New Server(GetNomeSQLServer())
        If dbServer.Databases(strNomeBD) IsNot Nothing Then
            Return True
        End If
        Return False
    End Function
    Private Shared Function GetNomeSQLServer() As String
        'Nome do PC local
        Dim strPCname As String = Environment.MachineName
        ' nome do serviço do SQL Server Express
        Dim strInstancia As String = "MSSQL$SQLEXPRESS"
        Dim strNomeSQLServer As String = String.Empty

        ' Inclua uma referência a : System.ServiceProcess;
        Dim servicos As ServiceController() = ServiceController.GetServices()
        ' percorre os serviços 
        For Each servico As ServiceController In servicos
            If servico Is Nothing Then
                Continue For
            End If
            Dim strNomeDoServico As String = servico.ServiceName
            If strNomeDoServico.Contains(strInstancia) Then
                strNomeSQLServer = strNomeDoServico
            End If
        Next
        Dim IndiceInicio As Integer = strNomeSQLServer.IndexOf("$")
        If IndiceInicio > -1 Then
            'strSqlServerName=NomeDoSeuPC\SQLEXPRESS;
            strNomeSQLServer = strPCname + "\" + strNomeSQLServer.Substring(IndiceInicio + 1)
        End If
        Return strNomeSQLServer
    End Function
    Public Shared Sub CriaBancoDeDados(ByVal strNomeDB As String)
        Dim dbServidor As New Server(GetNomeSQLServer())
        Dim mBancoDeDados As New Database(dbServidor, strNomeDB)
        'cria o banco de dados
        mBancoDeDados.Create()
    End Sub
    Public Shared Function VerificaSeTabelaExiste(ByVal strNomeBD As String, ByVal strNomeTabela As String) As Boolean
        'pega o nome do servidor e do banco de dados
        Dim dbServidor As New Server(GetNomeSQLServer())
        Dim mBancoDeDados As Database = dbServidor.Databases(strNomeBD)
        ' percorre todas as tabelas do banco de dados
        For Each mTabela As Table In mBancoDeDados.Tables
            If mTabela.Name = strNomeTabela Then
                'Achou a tabela no banco de dados
                Return True
            End If
        Next
        'tabela não foi encontrada
        Return False
    End Function
    Public Shared Sub ExecutaScriptSQL_CriarTabelaAluno(ByVal strCaminhoArquivo As String)
        Dim asm As Assembly = Assembly.GetEntryAssembly()
        Dim diretorioAplicacao As String = Path.GetDirectoryName(asm.Location)
        Dim caminhoArquivo As String = Path.Combine(diretorioAplicacao, strCaminhoArquivo)
        Dim Arquivo As New FileInfo(caminhoArquivo)
        Dim strScript As String = Arquivo.OpenText().ReadToEnd()

        strScript = strScript.Replace("GO" & vbCr & vbLf, "")

        Using conn As New SqlConnection(MontaStringDeConexao())
            conn.Open()
            Dim cmd As New SqlCommand(strScript, conn)
            Try
                cmd.ExecuteNonQuery()
            Catch excp As Exception
                Throw
            End Try
        End Using
    End Sub
    Private Shared Function MontaStringDeConexao() As String
        Dim strSqlServerNome As String = GetNomeSQLServer()
        Dim strConnString As String = "Data Source=" & strSqlServerNome & ";" & "Initial Catalog=" + DALHelper.strNomeBancoDados + ";Integrated Security=True"
        Return strConnString
    End Function

End Class

Nesta classe temos o seguinte:

A declaração dos namespaces usados:

Imports System
Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
Imports System.ServiceProcess
Imports System.Reflection

A definição de constantes contendo o nome do banco de dados, da tabela e da localização dos scripts para criar o banco de dados e a tabela :

Public Const strNomeBancoDados As String = "EscolaDB"
Public Const strNomeTabela As String = "Alunos"
Public Const strSQLCriaTabelaAlunos As String = "..\..\Scripts\CriaTabelaAlunos.sql"
Public Const strSQLCriaBancoDadosEscolaDB As String = "..\..\Scripts\CriaEscolaBD.sql"
Public Shared strConnString As String = ""

Os métodos:

Todos os métodos são estáticos (Shared) e assim não precisaremos criar uma instância da classe DALHelper para usá-los.

Agora já podemos usar a classe e os scripts criados.

Selecione o formulário form1.vb e no evento Load do formulário digite o código abaixo:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        verificaBancoDeDados_Tabela()
    End Sub

A rotina verificaBancoDeDados_Tabela possui o seguinte código:

Private Sub verificaBancoDeDados_Tabela()

        'verifica se o SQL Server esta em execução
        If DALHelper.verificaSeSQLServerNaoEstaRodando() Then
            MessageBox.Show("O SQLServer não esta em execução. Pode estar parado ou pausado.", "Verifica SQLServer", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            MessageBox.Show("O SQL Server esta instalado e em execução.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        'se o banco de dados não existir roda o script para criar o banco de dados 
        'se a tabela não existir roda o script para crir a tabela
        If DALHelper.verificaSeBancoDadosExiste(DALHelper.strNomeBancoDados) = False Then
            DALHelper.CriaBancoDeDados(DALHelper.strNomeBancoDados)

            MessageBox.Show("O banco de dados : " + DALHelper.strNomeBancoDados + " foi criado no SQLServer com sucesso.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)

            If DALHelper.VerificaSeTabelaExiste(DALHelper.strNomeBancoDados, DALHelper.strNomeTabela) = False Then
                DALHelper.ExecutaScriptSQL_CriarTabelaAluno(DALHelper.strSQLCriaTabelaAlunos)
                MessageBox.Show("A tabela : " + DALHelper.strNomeTabela + " foi criada no SQLServer com sucesso.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Else
            MessageBox.Show("O  Banco de dados : " + DALHelper.strNomeBancoDados + " já existe no SQL Server.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        ' se o banco de dados existir e a tabela  não existir - roda o script para criar a tabela e sai
        If (DALHelper.verificaSeBancoDadosExiste(DALHelper.strNomeBancoDados) = True) AndAlso (DALHelper.VerificaSeTabelaExiste(DALHelper.strNomeBancoDados, DALHelper.strNomeTabela) = False) Then
            DALHelper.ExecutaScriptSQL_CriarTabelaAluno(DALHelper.strSQLCriaTabelaAlunos)
            MessageBox.Show("A tabela : " + DALHelper.strNomeTabela + " foi criada com sucesso no SQLServer", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            MessageBox.Show("A tabela : " + DALHelper.strNomeTabela + " já existe no Banco de dados.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

    End Sub

O código acima usa os métodos da classe DALHelper para executar as operações definidas.

Executando a aplicação e usando a ferramenta SQL Server Management Studio para verificação iremos ver o banco de dados EscolaDB e a tabela Alunos criados conforme esperávamos conforme a figura abaixo:

Pegue o projeto completo aqui: CriaSQLServerNet.zip

Joã 14:1 Não se turbe o vosso coração; credes em Deus, crede também em mim.

Joã 14:2 Na casa de meu Pai há muitas moradas; se não fosse assim, eu vo-lo teria dito; vou preparar-vos lugar.

Joã 14:3 E, se eu for e vos preparar lugar, virei outra vez, e vos tomarei para mim mesmo, para que onde eu estiver estejais vós também.

Joã 14:4 E para onde eu vou vós conheceis o caminho.

Joã 14:5 Disse-lhe Tomé: Senhor, não sabemos para onde vais; e como podemos saber o caminho?

Joã 14:6 Respondeu-lhe Jesus: Eu sou o caminho, e a verdade, e a vida; ninguém vem ao Pai, senão por mim.

Referências:

José Carlos Macoratti