Code for several datasets

Question

Hi,

I have 4 ComboBoxen and 1 DatagridView into one Form.

I make a code, for binding with 5 tables, each is own dataSet and dataAdaptor

Is it possible to use one DataSet and one DataAdaptor for 5 Tables, so my code  become shorter.

I think the solution is a SQL SELECT query, but i dont no for sure.

If it is a SELECT query, ore something else, cane anybody help with make the code i need.

Perhaps its beter to use a Function?

Thans for helping

gr Fred

Holland

Imports System.Data.SqlClient
Public Class ArtikelBewerken
    Private Sub ArtikelBewerken_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        GridOphalen()
    End Sub
    Private Sub GridOphalen()
        Try
            Dim connectionString As String = My.Settings.receptConnectionString
            Dim SQLString As String = "SELECT * From Artikelbestand"
            Dim strSQL As String = "SELECT CrediteurenID, Naam  FROM Leverancierbestand"
            Dim stringSQL As String = "SELECT  ArtikelgroepID, Artikelgroep FROM artikelgroepbestand"
            Dim SQLstr As String = "SELECT EenheidID, Eenheid FROM Eenheidbestand"
            Dim SQLString1 As String = "SELECT VleesID, Vleesdefinitie FROM Definitievleesbestand"
            Dim mijnDataAdapter As New SqlDataAdapter(SQLString, connectionString)
            Dim mijnDataAdapter1 As New SqlDataAdapter(strSQL, connectionString)
            Dim mijnDataAdapter2 As New SqlDataAdapter(stringSQL, connectionString)
            Dim mijnDataAdapter3 As New SqlDataAdapter(SQLstr, connectionString)
            Dim mijnDataAdapter4 As New SqlDataAdapter(SQLString1, connectionString)
            Dim mijnDataSet As New DataSet
            Dim mijnDataSet1 As New DataSet
            Dim mijnDataSet2 As New DataSet
            Dim mijnDataSet3 As New DataSet
            Dim mijnDataSet4 As New DataSet
            mijnDataAdapter.Fill(mijnDataSet)
            mijnDataAdapter1.Fill(mijnDataSet1)
            mijnDataAdapter2.Fill(mijnDataSet2)
            mijnDataAdapter3.Fill(mijnDataSet3)
            mijnDataAdapter4.Fill(mijnDataSet4)
            DataGridView1.DataSource = mijnDataSet.Tables(0)
            cmbCrediteurenNR.DataSource = mijnDataSet1.Tables(0)
            cmbArtikelGroep.DataSource = mijnDataSet2.Tables(0)
            cmbEenheid.DataSource = mijnDataSet3.Tables(0)
            cmbVleesdefinitie.DataSource = mijnDataSet4.Tables(0)
            cmbCrediteurenNR.ValueMember = "CrediteurenID"
            cmbCrediteurenNR.DisplayMember = "Naam"
            cmbCrediteurenNR.SelectedIndex = -1
            cmbArtikelGroep.ValueMember = "ArtikelgroepID"
            cmbArtikelGroep.DisplayMember = "Artikelgroep"
            cmbArtikelGroep.SelectedIndex = -1
            cmbEenheid.ValueMember = "EenheidID"
            cmbEenheid.DisplayMember = "Eenheid"
            cmbEenheid.SelectedIndex = -1
            cmbVleesdefinitie.ValueMember = "VleesID"
            cmbVleesdefinitie.DisplayMember = "Vleesdefinitie"
            cmbVleesdefinitie.SelectedIndex = -1
        Catch ex As Exception
            MessageBox.Show("Fout opgetreden: " & ex.Message.ToString)
        Finally
        End Try
    End Sub

Answer 1

Hi,

you can create One Procedure with One Parameter.

Pass Sql Query as Parameter to that procedure. that parameter return data source.

you can direct assign data source to your combobox Data Source like below

DataGridView1.DataSource = GetDataSet("SELECT * From Artikelbestand")

Create Procedure GetDataSet that will return DataSet.

Hope this help you

Regards

Gautam

Answer 2

Thank you, for this solution.

I am a beginning programmer, so cane somebody help something more.

Maby olso a other solution to work with one Dataset, so i cane try several ways.

Gr Fred

Holland

Answer 3

Hello,

You can use another version of the overloaded fill method: DbDataAdapter.Fill (DataSet, String)

 http://msdn.microsoft.com/de-de/library/system.data.common.dbdataadapter.fill(v=vs.80).aspx

One Dataset and different tables: "Table0","Table1",......

Binding in this way:

cmbCrediteurenNR.DataSource = mijnDataSet1.Tables("Table0")

regards Ellen

---

Ich benutze/ I'm using VB2008 & VB2010

Answer 4

thanks,

I use now one Dataset and different tables: "Table0","Table1",  ect

Gr Fred

Answer 5

With very much work you can use one table adapter for more datatables. 

A dataset is to bind more tables together with in the dataset the extra collections for the relations between the tables.

However using one DataAdapter (or derived classes from that), it is like using one chair in a classroom for all students. 

Be aware it has not any advantage to use 1 adapter instead of 5, an adapter holds the code for filling, inserting, deleting and updating of a table.

As advice try to use in your case tables with relations, One generic dataset (the DATA tab) and use the TableAdapterManager

http://msdn.microsoft.com/en-us/library/bb384426.aspx

In the way you are going now you will see you end with goulash.

---

Success
Cor