问题描述:

I have a Form with a TabControl with 8 tabs, each tab have a Datagridview that needs to be populated each one from a different table of my database. I'd like to know if i need to make all the 8 connections and queries or i can apply the DRY principle and how i can do it.

Actually I'm trying populate the Datagridviews but I don't know why I'm just getting populated 3 of them and the rest just stay empty.

I would really appreciate your help.

Thanks.

Here is my code:

Imports MySql.Data.MySqlClient

Imports System.Data

Public Class frmMantenimiento

Dim conn As MySqlConnection

Dim myCommand As New MySqlCommand

Dim myAdapter As New MySqlDataAdapter

Dim dataEmp As New DataTable

Dim dataNomi As New DataTable

Dim dataPres As New DataTable

Dim dataGast As New DataTable

Dim dataAmon As New DataTable

Dim dataHora As New DataTable

Dim SQL As String

Public Sub FillEmps()

conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

myCommand.Connection = conn

SQL = "SELECT IDEmpleado as ID, Nombre, Cargo FROM Empleados"

Try

conn.Open()

Try

myCommand.Connection = conn

myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand

myAdapter.Fill(dataEmp)

dgvConsEmp.DataSource = dataEmp

dgvConsEmp.Columns(0).Width = 30

dgvConsEmp.Columns(1).Width = 200

dgvConsEmp.Columns(2).Width = 200

Catch myerror As MySqlException

MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)

End Try

Catch myerror As MySqlException

MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)

Finally

If conn.State <> ConnectionState.Closed Then conn.Close()

End Try

End Sub

Public Sub FillNom()

conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

myCommand.Connection = conn

SQL = String.Empty

SQL = "SELECT IDNomina as ID, Fecha, MontoBruto as 'Monto Bruto', MontoNeto as 'Monto Neto', CantidadEmpleados as Empleados FROM Nomina"

Try

conn.Open()

Try

myCommand.Connection = conn

myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand

myAdapter.Fill(dataNomi)

dgvConsNomi.DataSource = dataNomi

dgvConsNomi.Columns(0).Width = 30

Catch myerror As MySqlException

MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)

End Try

Catch myerror As MySqlException

MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)

Finally

If conn.State <> ConnectionState.Closed Then conn.Close()

End Try

End Sub

Private Sub FillPres()

conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

myCommand.Connection = conn

SQL = String.Empty

SQL = "SELECT IDPrestamo as ID, IDEmpleado as 'ID Empleado', MontoInicial as Monto, Fecha, MontoDescuento as Descuento, FormaDescuento as Recurrencia, Status FROM Prestamos"

Try

conn.Open()

Try

myCommand.Connection = conn

myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand

myAdapter.Fill(dataPres)

dgvConsPres.DataSource = dataPres

dgvConsPres.Columns(0).Width = 30

Catch myerror As MySqlException

MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)

End Try

Catch myerror As MySqlException

MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)

Finally

If conn.State <> ConnectionState.Closed Then conn.Close()

End Try

End Sub

Private Sub FillGast()

conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

myCommand.Connection = conn

SQL = String.Empty

SQL = "SELECT IDGasto as ID, IDEmpleado as 'ID Empleado', Monto, Fecha, Concepto, IDUsuario as Creador FROM GastosEmpleados"

Try

conn.Open()

Try

myCommand.Connection = conn

myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand

myAdapter.Fill(dataGast)

Me.dgvConsGast.DataSource = dataGast

Me.dgvConsGast.Columns(0).Width = 30

Catch myerror As MySqlException

MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)

End Try

Catch myerror As MySqlException

MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)

Finally

If conn.State <> ConnectionState.Closed Then conn.Close()

End Try

End Sub

Private Sub FillAmon()

conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

myCommand.Connection = conn

SQL = "SELECT IDAmonestacion as ID, TipoAmonestacion as Tipo, Descripcion, IDUsuario as Creador FROM Amonestaciones"

Try

conn.Open()

Try

myCommand.Connection = conn

myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand

myAdapter.Fill(dataAmon)

dgvConsAmon.DataSource = dataAmon

dgvConsAmon.Columns(0).Width = 30

Catch myerror As MySqlException

MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)

End Try

Catch myerror As MySqlException

MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)

Finally

If conn.State <> ConnectionState.Closed Then conn.Close()

End Try

End Sub

Private Sub FillHora()

conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

myCommand.Connection = conn

SQL = "SELECT IDControlHorario as ID, IDEmpleado as 'ID Empleado', Fecha, Hora FROM ControlHorario"

Try

conn.Open()

Try

myCommand.Connection = conn

myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand

myAdapter.Fill(dataHora)

dgvConsHora.DataSource = dataHora

dgvConsHora.Columns(0).Width = 30

Catch myerror As MySqlException

MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)

End Try

Catch myerror As MySqlException

MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)

Finally

If conn.State <> ConnectionState.Closed Then conn.Close()

End Try

dgvConsHora.Refresh()

End Sub

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

FillEmps()

FillNom()

FillPres()

FillHora()

FillGast()

FillAmon()

End Sub

网友答案:

I think you could be going abit overboard with the DRY principle. When you consider connection pooling, one connection object is all you will need. If the each table resides on a different DB, all you need it 8 different connection strings and the pooling will take care of the each of the connections. And unless you have a framework in place that could dynamically create you SQL queries for you, you will definately need an SQL select string per Datagrid. Hope you aren't using the VS wizards to do this.

Public Sub FillEmps() 

    myCommand.Connection = conn 
    SQL = "SELECT IDEmpleado as ID, Nombre, Cargo FROM Empleados" 

    Try 
       myCommand.Connection = conn 
       myCommand.CommandText = SQL 

       myAdapter.SelectCommand = myCommand 
       myAdapter.Fill(dataEmp) 

       dgvConsEmp.DataSource = dataEmp 

       dgvConsEmp.Columns(0).Width = 30 
       dgvConsEmp.Columns(1).Width = 200 
       dgvConsEmp.Columns(2).Width = 200 

    Catch myerror As MySqlException 
        MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message) 
    End Try 
End Sub 


Private Sub frmMantenimiento_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
    conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

    Try
        conn.Open()

        FillEmps() 
        FillNom() 
        FillPres() 
        FillHora() 
        FillGast() 
        FillAmon() 
    Catch
        If conn.State <> ConnectionState.Closed Then conn.Close() 
    Finally
        If conn.State <> ConnectionState.Closed Then conn.Close() 
    End Try

End Sub 
相关阅读:
Top