Read Data out of a Temporary DataSet / DataTable

Question

In an older thread i was looking to read data into a temporary datatable,   Now I think I have gotten this working,  but i am not sure how to connect the temporary data set (if needed) to access the data from this.

Again here is my code

Imports System.Data
Imports System.Data.SqlClient
Public Class TestOperatorChecklistForm
    Private Property ShiftTasksTable As SqlDataReader
    Private Property ShiftTasksDataTable As SqlDataReader
    Dim daShiftTaskData As SqlDataAdapter
    Dim dsShiftTask As New DataSet
    Dim cmdShiftTaskData As New SqlCommand
    Dim qryShiftTaskData As String
    Dim LocalDataSet As New DataSet("Local_Dataset")
    Dim dtShiftTasksData As DataTable = LocalDataSet.Tables.Add("ShiftTasksData")
    Private Sub LoadDataFromDate(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim cmd As New SqlCommand
        Dim con As New SqlConnection
        Dim sqlShift As String
        Dim TaskDate As Date
        con = New SqlConnection(VariableModule.ConnString)
        '---- Handles the loading of data from "ShiftTasksData" ----
        dtShiftTasksData.Clear()
        sqlShift = "SELECT * FROM ShiftTasksData"
        Try
            con.Open()
            Dim adapter As New SqlDataAdapter(sqlShift, con)
            adapter.FillSchema(dtShiftTasksData, SchemaType.Source)
            dtShiftTasksData.Columns.Item(0).AutoIncrement = False
            dtShiftTasksData.Columns.Item(0).Unique = True
            cmd.Connection = con
            cmd.CommandText = VariableModule.ShiftTasksDataTable
            ShiftTasksDataTable = cmd.ExecuteReader
            While ShiftTasksDataTable.Read()
                TaskDate = ShiftTasksDataTable.GetDateTime(1)
                If TaskDate = VariableModule.DateField Then
                    'dtShiftDate    '[ID], [Date], [ShiftTaskIDNumber], [DataField]
                    Dim newRow As DataRow
                    newRow = dtShiftTasksData.NewRow()
                    newRow("ID") = ShiftTasksDataTable.GetInt32(0)
                    newRow("Date") = ShiftTasksDataTable.GetDateTime(1)
                    newRow("ShiftTaskIDNumber") = ShiftTasksDataTable.GetInt32(2)
                    newRow("DataField") = ShiftTasksDataTable.GetString(3)
                    dtShiftTasksData.Rows.Add(newRow)
                    'adapter.Update(dtShiftTasksData, "ShiftTasksData")
                End If
            End While
            ShiftTasksDataTable.Close()
	    'I know i want to put it here to test that the data that i am reading is there.
	Catch ex As Exception
            MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
        Finally
            con.Close()
        End Try
    End Sub
End Class

Any help would be great!

Answer 1

Fecha,

I'm always curious, why have you set expressly this question to discussion. 

It looks more to a question. If it is a question than set it back to question, all was it alone that the chance on answers become then higher. 

Non answered question are kept in sight. Discussion mostly slowly die.

Therefore make it again a question in top of that first message.

---

Success
Cor

Answer 2

In what way do you want to "connect the temporary data set".

In your earlier thread you said you simply wanted a readonly datatable so you don't actually need a dataset and in fact your code doesn't really make use of one.  Simply declare the temporary table as a New Datatable and get rid of the dataset declarations.

If you simply want to view the content of the dataset then add a DataGridView and set its DataSource to be the DataTable.

If you want something different then define what you want.

Answer 3

Here is the problem and a quick description of the program.

   Where i work we had a paper check list that we have to perform that is approximatly 75 tasks per shift * 3,  Approximatly 125 system checks per shift & fax system checks every hour *6 fax systems.  I have created a program that dynamicly loads with each shift (first, Second, Third) each of these shifts has there own tasks except system & Faxes.  So on the main load it loads the tasks and data that might be associated with that date/shift data.

Now each line in the data table for task takes 4 fields of data [ID], [Date], [ShiftTaskIDNumber], [DataField].   We have been running in test for 4 months and the load time on the page is starting to lag when looking at older data.
In an attempt to speed it up i am trying is to pull the data out of the sql DB when the date is selected and move it into a temporary DB so that i don't have to go to the sql server, search through ALL the data Again every time that you want to populate one of these fields.   In Theory it should cut load time dramaticly since i am only going to the SQL Server once when the Date Field is populated vs every line it loads it goes to the sql server  and looks to see if there is somthing in that field.

Now i can tell you that i am a newb to this,  but can gennerally figure out what i am trying to do,  wether it correct or not.   and i know that you need a connection statment to connect to the sql DB example of the one that i am using during this revision is out of a my variable module.
Public Property ConnString As String = "Data Source=PCNAME\SQLEXPRESS;Initial Catalog=OperationsChecklist;Integrated Security=True"

All i am looking for is how do you connect the temporary table that was just created.  Or is there a read statement that would be best suited for this?

Again any help is appriciated.

Answer 4

Well the "bare bones" of what you are trying to do is in the following:

Imports System.Data.SqlClient
Public Class Form1
   Dim DGV As New DataGridView
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      DGV.Dock = DockStyle.Fill
      Controls.Add(DGV)
      Dim DT As New DataTable
      Dim SelectString As String = "SELECT * FROM ShiftTasksData where Date=@Date"
      Dim DateToSelect As Date = #1/1/2012#
      Dim ConnectionString As String = "Data Source=PCNAME\SQLEXPRESS;Initial Catalog=OperationsChecklist;Integrated Security=True"
      Try
         Using Con As New SqlConnection(ConnectionString)
            Dim Command As New SqlCommand(SelectString, Con)
            Command.Parameters.AddWithValue("Date", DateToSelect)
            Using DA As New SqlDataAdapter()
               DA.SelectCommand = Command
               DA.Fill(DT)
            End Using
         End Using
      Catch ex As Exception
         MsgBox(ex.Message)
      End Try
      DGV.DataSource = DT
   End Sub
End Class

Answer 5

Dave, 
    Thanks for the answer,   although it is a much simpler way to gather the data that i am looking for,   but It still dosn't answer my question of How does one read the data from the temporary data set once you have the data loaded into it.

While reading the data from a SQL Dataset i am using 

While ShiftTasksTable.Read()
     'Do your meaningful code here

End While

I want to do somthing the same with the data that i have stored in the LocalDataSet dataset.   This is where i need the help at. 

Ed 

Answer 6

Facha,

Be aware that every dataset is temporally.

DataSets are disconnected collections for a database. They reflect the same scheme as the database.

Somehow you try to do a very simple task very difficult. What is your reason for that. 

If you want to create two the same datasets you can use this (and this is a real copy not a reference)

Imports System.Data
Imports System.Data.SqlClient
Public Class TestOperatorChecklistForm
    Dim LocalDataSet As New DataSet("Local_Dataset")
    Dim dtShiftTasksData As DataTable = LocalDataSet.Tables.Add("ShiftTasksData")
    Private Sub LoadDataFromDate(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim con As New SqlConnection(VariableModule.ConnString)
        dtShiftTasksData.Clear()
        Dim sqlShift = "SELECT * FROM ShiftTasksData"
        Using daShiftTaskData As New SqlDataAdapter(sqlShift, con)
            Try
                Dim adapter As New SqlDataAdapter(sqlShift, con)
                adapter.Fill(dtShiftTasksData)
            Catch ex As Exception
                MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
            End Try
        End Using
        LocalDataSet.Tables.Add(dtShiftTasksData.DefaultView.ToTable)
    End Sub
End Class

And try to avoid that kind of VB5 style coding. It is only misleading and has not any purpose in current programming.

---

Success
Cor

Answer 7

Cor,

the reason for the temp dataset is to lessen the read time from the sql server... if i have a datatable with 500 rows of data vs the full datatable that contains 25000 rows of data this should will take a shorter time when i go have to call this data later in the process. 

I used somthing similar to what you posted and it works in extracting the data that i am needing,   now my question how do i utilize this data it was all simple when i was reading it one row at a time, doing my task with the data and then moving to the next data read.  Now that i haveit stored in a data set i need to start pulling the data out of it and build my pages. 

Thanks in advance
Ed

Answer 8

Well referring back to my previous post you have a datatable "DT".  There are various ways to access the content of the table depending upon your exact requirement.  My post showed the simplest method of just displaying it in a DataGridView.

You could also access it via its rows and columns, e.g.

DT.Rows(RowNumber).Item("ColumnName").ToString

More commonly you would only wish to access certain rows in which case you could do a Select on the table which will return an array of datarows

Dim Rows As DataRow() = DT.Select("ColumnName = value")
For Each Row As DataRow In Rows
   MsgBox(Row.Item("columnname"))
Next