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

JOAOPEDRO

[VB6] Acesso a dados MDB

6 mensagens neste tópico

Boas,

Pessoal, estou um pouco perdido para iniciar o acesso a dados com o VB 2005

e Winforms.

Tenho alguma experiência com o VB6, usava os Recordsets para tudo, com ADO.

Agora o que usar?

Existe tanta coisa, Dataset, DataTable, TableAdapter, DataAdapter, Command,

etc...

Quais os componentes a utilizar e em que situações?

Alguém pode dar uma ajuda "prática", alguns exemplos?

Já estou farto de ler sobre isto, mas toda a documentação é confusa, fala de

um componente, depois volta para outro.

Ainda não encontrei um exemplo que diga algo do tipo:

-Para fazer a conexão com a BD, fazer assim: ...

-Para carregar a tabela, fazer assim: ...

-Para actualizar dados, fazer assim: ...

-Para apagar...

-Para criar um novo...

-Para usar numa listagem,...

Se alguém tiver ou souber onde encontrar algo prático, com exemplos

objectivos, agradeço.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

curiosamente, tambem já passei por isso, recentemente.

parece uma confusão de inicio. depois de algum estudo, ganha sentido.

penso que o mais díficil é o binding.

alguma dúvida diz.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.OleDb

Imports System.Data.SqlClient.SqlException

Imports System.Data.OleDb.OleDbException

Imports System.Reflection

'estrutura publica para storprocedures

Public Structure SPDATA

  Dim SPVAR As String

  Dim SPDIRECTION As String

  Dim SPVALUE As String

  Dim SPARRAYBYTEVALUE As Byte()

  Dim SPTYPE As String

  Dim SPSIZE As Short

End Structure

'estrutura publica para escolha de tipo de connecção com o datareader

Public Enum Tipo_Connect

  ' Modo de Ligação

  ' 1- ExecuteNonQuery

  ' 2- ExecuteReader

  ' 3- ExecuteScalar

  ' 4- ExecuteStoreProcedure

  ' 5- ExecuteReader com parametros

  ' 6- ExecuteNonQuery com parametros

  Query = 1

  Reader = 2

  Scalar = 3

  StoreP = 4

  ReaderP = 5

  QueryP = 6

End Enum

Public Class SQLAccess

  'SQL DATAREADER

  Public Shared Function DataReaderSQL(ByVal ConnString As String, ByVal SQLQuery As String, ByVal Op As Tipo_Connect, Optional ByVal strSTOREPROCEDURE As String = Nothing, Optional ByVal arrSTOREPARAMS As Array = Nothing, Optional ByRef AffectedRows As Integer = 0)

      Dim con As SqlConnection

      Dim cmd As SqlCommand

      Dim dr As SqlDataReader

      Dim sqlParam As SqlParameter

      Dim count As Integer

      Dim shtSPCOUNT As Short

      Try

        con = New SqlConnection(ConnString)

        con.Open()

        cmd = New SqlCommand(SQLQuery, con)

        Select Case Op

            '*************************************************************************************************

            Case Tipo_Connect.Query

              count = cmd.ExecuteNonQuery()

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.QueryP

              With cmd

                  .Connection = con

                  .CommandType = CommandType.Text

              End With

              For shtSPCOUNT = 0 To arrSTOREPARAMS.GetUpperBound(0) - 1

                  If (arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE Is Nothing) Then

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPVALUE

                  Else

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE

                  End If                 

              Next

              count = cmd.ExecuteNonQuery()

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.Reader

              dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.ReaderP

              With cmd

                  .Connection = con

                  .CommandType = CommandType.Text

              End With

              For shtSPCOUNT = 0 To arrSTOREPARAMS.GetUpperBound(0) - 1

                  If (arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE Is Nothing) Then

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPVALUE

                  Else

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE

                  End If                 

              Next

              dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.Scalar

              cmd.ExecuteScalar()

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.StoreP

              With cmd

                  .Connection = con

                  .CommandType = CommandType.StoredProcedure

                  .CommandText = strSTOREPROCEDURE

              End With

              For shtSPCOUNT = 0 To arrSTOREPARAMS.GetUpperBound(0) - 1

                  If (arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE Is Nothing) Then

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPVALUE

                  Else

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE

                  End If                 

              Next

              dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

              '*************************************************************************************************

        End Select

      Catch ex As Exception

        MsgBox(ex.ToString)

      Finally

        'If Not dr Is Nothing Then

        '    dr.Close()

        'End If

        'If Not con Is Nothing Then

        '    con.Close()

        'End If

      End Try

      AffectedRows = count

      Return dr

  End Function

  'SQL DATAADAPTER

  Public Shared Function DataAdapterSQL(ByVal ConnString As String, ByVal SQLQuery As String)

      Dim con As SqlConnection

      Dim sda As SqlDataAdapter

      Try

        con = New SqlConnection(ConnString)

        con.Open()

        sda = New SqlDataAdapter(SQLQuery, con)

      Catch ex As Exception

        MsgBox(ex.ToString)

      End Try

      Return sda

  End Function

  'ROTINA QUE PREENCHE UM DATASET A PARTIR DE UM DATAREADER

  Public Shared Function DataReaderToDataset(ByVal ds As DataSet, ByVal table As String, ByVal dr As IDataReader)

      ' Cria um  xxxDataAdapter do mesmo tipo de um DataReader

      Dim tipoDataReader As Type = CObj(dr).GetType

      Dim nomeTipo As String = tipoDataReader.FullName.Replace("DataReader", "DataAdapter")

      Dim tipoDataAdapter As Type = tipoDataReader.Assembly.GetType(nomeTipo)

      Dim da As Object = Activator.CreateInstance(tipoDataAdapter)

      ' invoca o método protegido Fill que toma um objeto IDataReader

      Dim args() As Object = {ds, table, dr, 0, 999999}

      tipoDataAdapter.InvokeMember("Fill", BindingFlags.InvokeMethod Or BindingFlags.NonPublic Or BindingFlags.Instance, Nothing, da, args)

      ' fecha o DataReader

      dr.Close()

  End Function

End Class

Public Class OLEDBAccess

  'OLEDB DATAREADER

  Public Shared Function DataReaderOLEDB(ByVal ConnString As String, ByVal SQLQuery As String, ByVal Op As Tipo_Connect, Optional ByVal strSTOREPROCEDURE As String = Nothing, Optional ByVal arrSTOREPARAMS As Array = Nothing, Optional ByRef AffectedRows As Integer = 0)

      Dim con As OleDb.OleDbConnection

      Dim cmd As OleDb.OleDbCommand

      Dim dr As OleDb.OleDbDataReader

      Dim sqlParam As OleDb.OleDbParameter

      Dim count As Integer

      Dim shtSPCOUNT As Short

      Try

        con = New OleDbConnection(ConnString)

        con.Open()

        cmd = New OleDbCommand(SQLQuery, con)

        Select Case Op

            '*************************************************************************************************

        Case Tipo_Connect.Query

              count = cmd.ExecuteNonQuery()

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.QueryP

              With cmd

                  .Connection = con

                  .CommandType = CommandType.Text

              End With

              For shtSPCOUNT = 0 To arrSTOREPARAMS.GetUpperBound(0) - 1

                  If (arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE Is Nothing) Then

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPVALUE

                  Else

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE

                  End If

              Next

              count = cmd.ExecuteNonQuery()

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.Reader

              dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.ReaderP

              With cmd

                  .Connection = con

                  .CommandType = CommandType.Text

              End With

              For shtSPCOUNT = 0 To arrSTOREPARAMS.GetUpperBound(0) - 1

                  If (arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE Is Nothing) Then

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPVALUE

                  Else

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE

                  End If                 

              Next

              dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.Scalar

              cmd.ExecuteScalar()

              '*************************************************************************************************

              '*************************************************************************************************

            Case Tipo_Connect.StoreP

              With cmd

                  .Connection = con

                  .CommandType = CommandType.StoredProcedure

                  .CommandText = strSTOREPROCEDURE

              End With

              For shtSPCOUNT = 0 To arrSTOREPARAMS.GetUpperBound(0) - 1

                  If (arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE Is Nothing) Then

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPVALUE

                  Else

                    cmd.Parameters.Add(arrSTOREPARAMS(shtSPCOUNT).SPVAR, arrSTOREPARAMS(shtSPCOUNT).SPTYPE).Value = arrSTOREPARAMS(shtSPCOUNT).SPARRAYBYTEVALUE

                  End If                 

              Next

              dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

              '*************************************************************************************************

        End Select

      Catch ex As Exception

        MsgBox(ex.ToString)

      Finally

        'If Not dr Is Nothing Then

        '    dr.Close()

        'End If

        'If Not con Is Nothing Then

        '    con.Close()

        'End If

      End Try

      AffectedRows = count

      Return dr

  End Function

  'OLEDB DATAADAPTER

  Public Shared Function DataAdapterOLEDB(ByVal ConnString As String, ByVal SQLQuery As String)

      Dim con As OleDb.OleDbConnection

      Dim sda As OleDb.OleDbDataAdapter

      Try

        con = New OleDbConnection(ConnString)

        con.Open()

        sda = New OleDbDataAdapter(SQLQuery, con)

      Catch ex As Exception

        MsgBox(ex.ToString)

      End Try

      Return sda

  End Function

  'ROTINA QUE PREENCHE UM DATASET A PARTIR DE UM DATAREADER

  Public Shared Function DataReaderToDataset(ByVal ds As DataSet, ByVal table As String, ByVal dr As IDataReader)

      ' Cria um  xxxDataAdapter do mesmo tipo de um DataReader

      Dim tipoDataReader As Type = CObj(dr).GetType

      Dim nomeTipo As String = tipoDataReader.FullName.Replace("DataReader", "DataAdapter")

      Dim tipoDataAdapter As Type = tipoDataReader.Assembly.GetType(nomeTipo)

      Dim da As Object = Activator.CreateInstance(tipoDataAdapter)

      ' invoca o método protegido Fill que toma um objeto IDataReader

      Dim args() As Object = {ds, table, dr, 0, 999999}

      tipoDataAdapter.InvokeMember("Fill", BindingFlags.InvokeMethod Or BindingFlags.NonPublic Or BindingFlags.Instance, Nothing, da, args)

      ' fecha o DataReader

      dr.Close()

  End Function

End Class

isto é a minha dll de ligações a bd's ( mdb e sql server ) ... ainda não está como quero mas já da para poderes perceber ....

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Já agora ... data binding não tem nada a ver com a ligação à bd em si .. mas sim como fazes a ligação do datareader ou dataadapter aos campos dos forms ....e é muuuuiiiitttoooo facil .. tem as suas limitações ....

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Crie uma conta ou ligue-se para comentar

Só membros podem comentar

Criar nova conta

Registe para ter uma conta na nossa comunidade. É fácil!


Registar nova conta

Entra

Já tem conta? Inicie sessão aqui.


Entrar Agora