Jump to content
  • Revista PROGRAMAR: Já está disponível a edição #60 da revista programar. Faz já o download aqui!

Sign in to follow this  
fLaSh_PT

MS SQL Class-Simplificando Apps

Recommended Posts

fLaSh_PT

Ora viva;

Partilho uma classe para fácil acesso ao servidor de dados SQL Server.. desenvolvi para um projecto que fiz recentemente, e como ficou bem estruturada resolvi partilhar com vocês.

Contem bastantes funções muito úteis, e praticas de utilizar!

'========================================
'   Codificado por fLaSh - Carlos.DF
'           fLaSh - 2010-04
'         c4rl0s.pt@gmail.com
'========================================
Imports System.Data.SqlClient
Public Class MSSQL

    ' Condição de compilação, util para fazer debug
#Const IS_DEBUG = True

    ''' <summary>
    ''' Guarda a coneção á bd defenida ao iniciar a class
    ''' </summary>
    Private __ConnectionString As String = String.Empty
    Private __SqlConnection As SqlConnection
    Private __LastQuery As String

    ''' <summary>
    ''' Construtor da classe
    ''' </summary>
    Public Sub New(ByVal sConnectionString As String)
        __ConnectionString = sConnectionString
    End Sub

    ''' <summary>
    ''' Retorna o ultimo query utilizado..
    ''' </summary>
    Friend ReadOnly Property LastQuery() As String
        Get
            Return __LastQuery
        End Get
    End Property

    ''' <summary>
    ''' Retorna o objecto da ligação..
    ''' </summary>
    Friend ReadOnly Property SqlConnection() As SqlConnection
        Get
            Return __SqlConnection
        End Get
    End Property

    ''' <summary>
    ''' Retorna ou define a Connection String..
    ''' </summary>
    Friend Property ConnectionString() As String
        Get
            Return __ConnectionString
        End Get
        Set(ByVal value As String)
            __ConnectionString = value
        End Set
    End Property

    ''' <summary>
    ''' Abre a ligação ao servidor SQL Server..
    ''' </summary>
    Friend Function Open(Optional ByVal bMsgBoxOnError As Boolean = False) As Boolean
        Try
            ' Cria o objecto da conexão ao SQL
            Using oSqlCon As New SqlConnection(__ConnectionString)
                oSqlCon.Open()
                ' Verifica o estado da ligação
                Select Case oSqlCon.State
                    Case Data.ConnectionState.Open : Return True
                    Case Else : Return False
                End Select
                oSqlCon.Close()
            End Using
        Catch ex As SqlException
            If bMsgBoxOnError Then
                MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Warning)
            End If
        End Try
    End Function

    ''' <summary>
    ''' Fecha a ligação ao SQL
    ''' IMPORTANTE: depois de qualquer operação, deve se correr este Sub.
    ''' </summary>
    Friend Sub Close()
        Try
            If __SqlConnection IsNot Nothing Then
                If Not __SqlConnection.State = ConnectionState.Closed Then
                    __SqlConnection.Close()
                End If
            End If
#If IS_DEBUG Then
        Catch ex As Exception
            MessageBox.Show(ex.Message, "MSSQL.Close")
        End Try
#Else
        Catch : End Try
#End If
    End Sub

    ''' <summary>
    ''' Executa um query para a bd
    ''' </summary>
    Friend Function Execute(ByVal sQuery As String, _
                   Optional ByVal oParameters As List(Of SqlParameter) = Nothing, _
                   Optional ByRef oCommand As SqlCommand = Nothing) As Integer
#If IS_DEBUG Then
        Debug.WriteLine("Execute: " & sQuery)
#End If
        __LastQuery = "Execute [" & Now.ToString & "] " & sQuery
        Dim iRet As Integer = -1
        ' Cria o objecto da conexão ao SQL
        __SqlConnection = New SqlConnection(__ConnectionString)
        __SqlConnection.Open()
        oCommand = New SqlCommand(sQuery, __SqlConnection)
        ' Adiciona os parametros caso existam..
        If oParameters IsNot Nothing Then
            For Each oParam As SqlParameter In oParameters
                oCommand.Parameters.Add(oParam)
            Next
        End If
        iRet = oCommand.ExecuteNonQuery()
        Me.Close()
        Return iRet
    End Function

    ''' <summary>
    ''' Executa um query para o objecto SqlDataReader
    ''' </summary>
    Friend Function GetDataReader(ByVal sQuery As String, _
                         Optional ByRef oCommand As SqlCommand = Nothing) As SqlDataReader
#If IS_DEBUG Then
        Debug.WriteLine("GetDataReader: " & sQuery)
#End If
        __LastQuery = "GetDataReader [" & Now.ToString & "] " & sQuery
        Dim oDR As SqlDataReader
        ' Cria o objecto da conexão ao SQL
        __SqlConnection = New SqlConnection(__ConnectionString)
        __SqlConnection.Open()
        ' Cria o objecto da conexão ao SQL
        __SqlConnection = New SqlConnection(__ConnectionString)
        __SqlConnection.Open()
        oCommand = New SqlCommand(sQuery, __SqlConnection)
        'Executa o reader
        oDR = oCommand.ExecuteReader
        Return oDR
    End Function

    ''' <summary>
    ''' Executa um query para o objecto
    ''' </summary>
    ''' <param name="sQuery">Query string</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Friend Function GetDataSet(ByVal sQuery As String, _
                      Optional ByRef oCommand As SqlCommand = Nothing, _
                      Optional ByRef oDataAdapter As SqlDataAdapter = Nothing) As DataSet
#If IS_DEBUG Then
        Debug.WriteLine("GetDataSet: " & sQuery)
#End If
        __LastQuery = "GetDataSet [" & Now.ToString & "] " & sQuery
        Dim oDataSet As New DataSet
        ' Cria o objecto da conexão ao SQL
        __SqlConnection = New SqlConnection(__ConnectionString)
        __SqlConnection.Open()
        oCommand = New SqlCommand(sQuery, __SqlConnection)
        oDataAdapter = New SqlDataAdapter(oCommand)
        oDataAdapter.Fill(oDataSet)
        Return oDataSet
    End Function

    ''' <summary>
    ''' Faz uma contagem do rows..
    ''' </summary>
    Friend Function GetQueryCount(ByVal sQuery As String) As Integer
        Dim iCount As Integer = 0
        ' Executa o reader
        Using o As SqlDataReader = Me.GetDataReader(sQuery)
            If o IsNot Nothing Then
                Do While o.Read
                    iCount += 1
                Loop
            End If
        End Using
        Me.Close()
        Return iCount
    End Function

    ''' <summary>
    ''' Obtem apenas um valor..
    ''' </summary>
    Friend Function GetSingleItem(ByVal sQuery As String) As Object
        Using oDR As SqlDataReader = Me.GetDataReader _
               ( _
                   sQuery _
               )
            If oDR IsNot Nothing Then
                If oDR.Read Then
                    Return oDR.GetValue(0)
                Else
                    Return Nothing
                End If
            Else
                Return Nothing
            End If
        End Using
    End Function

    ''' <summary>
    ''' Preenche um DataTable com um query
    ''' </summary>
    ''' <param name="sQuery">query string</param>
    ''' <returns>DataTable</returns>
    ''' <remarks></remarks>
    Friend Function GetDataTable(ByVal sQuery As String, _
                        Optional ByRef oDataAdapter As SqlDataAdapter = Nothing) As DataTable
#If IS_DEBUG Then
        Debug.WriteLine("GetDataTable: " & sQuery)
#End If
        __LastQuery = "GetDataTable [" & Now.ToString & "] " & sQuery
        Dim oDataTable As New DataTable
        ' Cria o objecto da conexão ao SQL
        __SqlConnection = New SqlConnection(__ConnectionString)
        __SqlConnection.Open()
        oDataAdapter = New SqlDataAdapter(sQuery, __SqlConnection)
        oDataAdapter.Fill(oDataTable)
        Return oDataTable
    End Function

    ''' <summary>
    ''' Faz uma contagem do rows..
    ''' </summary>
    Friend Function CheckHasRows(ByVal sQuery As String) As Boolean
        Dim b As Boolean
        ' Executa o reader
        Using oDR As SqlDataReader = Me.GetDataReader(sQuery)
            If oDR IsNot Nothing Then
                b = oDR.Read()
            End If
        End Using
        Me.Close()
        Return b
    End Function

    ''' <summary>
    ''' Preenche uma combo com um query..
    ''' </summary>
    Friend Sub FillComboBox(ByVal c As ComboBox, _
                            ByVal sDisplayMember As String, _
                            ByVal sValueMember As String, _
                            ByVal sQuery As String, _
                   Optional ByVal iSelectedIndex As Integer = 0)
        c.DataSource = Nothing
        c.Items.Clear()
        c.DropDownStyle = ComboBoxStyle.DropDownList
        c.DataSource = Me.GetDataTable(sQuery)
        c.DisplayMember = sDisplayMember
        c.ValueMember = sValueMember
        c.SelectedIndex = iSelectedIndex
        Me.Close()
    End Sub

    ''' <summary>
    ''' Preenche uma ListView com um query..
    ''' </summary>
    Friend Sub FillListView(ByVal c As ListView, _
                            ByVal sQuery As String)
        ' Carrega a tablea..
        Using oDT As DataTable = Me.GetDataTable(sQuery)
            ' Limpa a listview
            c.Items.Clear()
            If oDT IsNot Nothing Then
                ' Prepara a listview
                c.BeginUpdate()
                ' Percorre todos os rows
                For Each r As DataRow In oDT.Rows
                    ' Cria o Item..
                    Dim Item As New ListViewItem
                    Item.Text = r.Item(0)
                    ' Precorre todos os subitems
                    For Each d As DataColumn In oDT.Columns
                        Item.SubItems.Add(r.Item(d))
                    Next
                    ' Adiciona o Item ao listview..
                    c.Items.Add(Item)
                Next
                c.EndUpdate()
            End If
        End Using
        Me.Close()
    End Sub

End Class

Alguns exemplos:

        ' Establece ligação ao servidor..
        Dim oSQL As New MSSQL _
                ( _
                    "Data Source=" & "CARLOS.DF\SQLEXPRESS" & ";" & _
                    "Network Library=DBMSSOCN;" & _
                    "Initial Catalog=" & "teste" & ";" & _
                    "User ID=" & "sa" & ";" & _
                    "Password=" & "123" & ";" & _
                    "MultipleActiveResultSets=True;" & _
                    "Connection Timeout=10" _
                )
        Try
            ' Abre a ligação
            oSQL.Open(True)
        Catch ex As Exception
            MessageBox.Show("Não foi possível estabelecer ligação com o servidor de dados!" & vbNewLine & vbNewLine & _
                "Descrição: " & ex.Message, APP_TITLE, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try


        ' Test carregar uma listview
        G_SQL.FillListView(lvwData, "SELECT * FROM Test")

        ' Test carregar uma CoboBOx
        G_SQL.FillComboBox(cmbFornecedor, "Nome", "Fornecedor", _
                "SELECT Fornecedor, Nome FROM fornecedores ORDER BY nome")

        ' Teste usando o DataReader
        Using oDR As SqlDataReader = _
        oSQL.GetDataReader _
            ( _
                "SELECT fornecedores.fornecedor,eartigos_encomedas.data_embarque,eartigos_encomedas.quantidade,eartigos_encomedas.id_artigos_encomenda,eencomendas.id_encomenda,econtratos.contrato,etipo_mercadoria.mercadoria " & _
                "FROM eartigos_encomedas  " & _
                "JOIN eencomendas ON eartigos_encomedas.id_encomenda=eencomendas.id_encomenda  " & _
                "JOIN etipo_mercadoria ON eencomendas.id_tipo_mercadoria=etipo_mercadoria.id_tipo_mercadoria  " & _
                "JOIN eencomendas_contratos ON eencomendas.id_encomenda=eencomendas_contratos.id_encomenda  " & _
                "JOIN econtratos ON eencomendas_contratos.id_contrato=econtratos.id_contrato  " & _
                "JOIN fornecedores ON eencomendas.id_fornecedor=fornecedores.fornecedor  " & _
                "WHERE eencomendas.id_tipo_mercadoria <> 1 and eartigos_encomedas.definido <> 1 " _
            )
            If oDR IsNot Nothing Then
                Do While oDR.Read
                    Dim Item As New ListViewItem(oDR("id_encomenda").ToString)
                    Item.SubItems.Add(oDR("data_criacao").ToString)
                    Item.SubItems.Add(oDR("nome").ToString)
                    lvwData.Items.Add(Item)
                Loop
                oDR.Close()
            End If
        End Using

        ' Adicionando um registo
        Dim oParams As New List(Of SqlParameter)
        oParams.Add((New SqlParameter("@id_fornecedor", SqlDbType.Variant).Value = cmbFornecedor.SelectedValue))
        oParams.Add((New SqlParameter("@contactofornecedor", SqlDbType.Variant).Value = "1"))
        oParams.Add((New SqlParameter("@id_responsavel", SqlDbType.Variant).Value = "1"))
        '
        Dim sQuery As String
        sQuery = "INSERT INTO [EEncomendas](" & _
                                    "[data_criacao]," & _
                                    "[id_fornecedor]," & _
                                    "[contactofornecedor]) " & _
                "VALUES(@id_fornecedor, @contactofornecedor, @id_responsavel)"
  
        If G_SQL.Execute(sQuery, oParams) = 1 Then
            MessageBox.Show("Registo adicionado com êxito.", APP_TITLE, MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
        Else
            MessageBox.Show("Não foi possivel adicionar o registo.", APP_TITLE, MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If

Compr.

EDIT: corregido pequenos bugs


Making the impossible possible and pwing the world on db at a time.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.