Solved

need to import into a custom class using reader from a postsql database

Posted on 2014-12-05
19
58 Views
Last Modified: 2014-12-17
Hello;

I have the below working code, but I would rather pull everything into a class then be able to work on it that way vs the way I have it.

Any thoughts or guidance on a better way to code this would be great!

     'get all of the stop address details - move them into a in memory clone to stop it from going back to the database for each search later
        Dim stopAddressList = From s In db.stop_address_details Select s
        Dim searchAddressList = stopAddressList.ToArray
        searchAddressList.Clone
  
        DataGridView1.DataSource=stopAddressList.ToArray ' debug code - remove
        command.CommandText="select  distinct on (customer_reference) customer_reference, stop_name, stop_address, actual_service_date, Actual_arrival_time, stop_signature " & _
                                     "from cops_reporting.distribution_stop_information " & _
                                     "where customer_no = '91000' and stop_signature <> '' and datetime_updated > '"& lastDatetimeUpdated & "' and datetime_updated < '" & endDate &"'" 
        PgSqlConnection1.Open ' opens the connection to the postgresql server
     Label1.Text = "opening reader..."  
        Using reader As PgSqlDataReader =command.ExecuteReader()
            Label1.Text = "starting"
            Application.DoEvents
            While reader.Read
                totalRecordCount +=1
                Dim customerReferenceNo, stopName, recBy As String
                customerReferenceNo=reader.GetValue(0).ToString
                'go see if this is one of the sites we are monitoring
                Dim searchfor = From f In searchAddressList Where f.customer_reference_no = customerReferenceNo Select f
           
                label1.text = totalRecordCount.ToString              
                Label1.Update
                If searchfor.Count>0 ' found it, now see if we already have this POD in our database - the below code will be replaced with a more through routine
                    stopName =reader.GetValue(1).ToString
                    recBy=reader.GetValue(5).ToString
                    Dim dateOfStop As Date = reader.GetValue(3)
                    Dim timeOfStop As TimeSpan = reader.GetValue(4)
                    Dim datetimeOfStop As Date = dateOfStop.Add(timeOfStop)
                    foundRecordCount +=1
                    TextBox1.AppendText ("Stop at " & customerReferenceNo & " for " & stopName & " signed for by " & recBy &  " at " & datetimeOfStop.ToString & " - " & timeOfStop.ToString & vbCrLf)
                End If
                
            End While
            Label1.Text = "record count: " & foundRecordCount & " out of " & totalRecordCount
        End Using
        PgSqlConnection1.Close

Open in new window

0
Comment
Question by:Joe Ruder
  • 12
  • 7
19 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40484709
Hi Joe;

Here is some sample code showing how I would try it. I do not think I forgot anything from your code.
''get all of the stop address details - move them into a in memory clone to stop it from going back to the database for each search later
'' ------------------------------------------------------------------------
'' This is not needed as I call the query below and just get what is needed
''Dim stopAddressList = From s In db.stop_address_details Select s
''Dim searchAddressList = stopAddressList.ToArray
''searchAddressList.Clone
''DataGridView1.DataSource=stopAddressList.ToArray '' debug code - remove
'' -------------------------------------------------------------------------

command.CommandText="select  distinct on (customer_reference) customer_reference, stop_name, stop_address, actual_service_date, Actual_arrival_time, stop_signature " & _
                             "from cops_reporting.distribution_stop_information " & _
                             "where customer_no = '91000' and stop_signature <> '' and datetime_updated > '"& lastDatetimeUpdated & "' and datetime_updated < '" & endDate &"'" 
PgSqlConnection1.Open '' opens the connection to the postgresql server
Label1.Text = "opening reader..."

'' Data from the postsql database download from the server
Dim postsqlDataList As New List(Of PostsqlData)()
Dim totalRecordCount As Integer = 0
'' Load the postsqlDataList from the database
Using reader As PgSqlDataReader =command.ExecuteReader()
    While reader.Read
       totalRecordCount += 1
       Dim dataRecord As New PostsqlData()
       dataRecord.CustomerReferenceNo = reader.GetValue(0).ToString
       dataRecord.StopName = reader.GetValue(1).ToString
       dataRecord.RecBy = reader.GetValue(5).ToString
       dataRecord.DateOfStop = reader.GetValue(3)
       dataRecord.TimeOfStop reader.GetValue(4)
       postsqlDataList.Add(dataRecord)
    End While
End Using 

PgSqlConnection1.Close

'' This EF query will return only the records that are needed ant not the complete
'' table from the database
Dim lookUpList = postsqlDataList.Select(Function(crn) crn.CustomerReferenceNo).ToList()
Dim searchfor = (From s In db.stop_address_details _
                 Where lookUpList.Contains(s.customer_reference_no) _
                 Select s).ToList()

For Each record In searchfor
  Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _
      record.CustomerReferenceNo, record.StopName, record.RecBy, _
      record.DatetimeOfStop.ToString(), record.timeOfStop.ToString(), vbCrLf)
  TextBox1.AppendText(outputLine)
Next
Label1.Text = "record count: " & searchfor.Count & " out of " & totalRecordCount


'' Custom class to hold data from postgresql database
Public Class PostsqlData                 
    Public Property CustomerReferenceNo As String
    Public Property StopName As String  
    Public Property RecBy As String
    Public Property DateOfStop As Date
    Public Property TimeOfStop As TimeSpan
    Public ReadOnly Property DatetimeOfStop As Date
        Get 
            Return DateOfStop.Add(TimeOfStop)
        End Get 
    End Property
End Class 

Open in new window

0
 

Author Comment

by:Joe Ruder
ID: 40490263
Thank you,

I was traveling over the weekend, I will dig into this tomorrow -- I just didn't want you to think I had abandoned it.

Joe
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40490327
Not a problem Joe.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:Joe Ruder
ID: 40501717
I'm sorry...I just got back to this!

Not quite, I don't think I explained things correctly.

I have two databases, one in Postgresql (call it remote) and one Sqlserver (call it local)

I need to query the remote database for new records, then check each against the local.  If they are not there I create them in the local.  Another process then does some more things with them.

The code you removed is the code that pulls the current list from the local database that it compares against, in memory.

What I am wanting to do is to somehow return all the records from the postgresql database (remote) to something that I can just query against or even use as a datasource for a datagridview.datasource= thing.

Your line:
Dim postsqlDataList As New List(Of PostsqlData)()

gives me a PostsqlData is not defined error also...
edit: I am sorry, I need to create the class like you said -- I will try that now.

Is that a better explanation Fernando?
0
 

Author Comment

by:Joe Ruder
ID: 40501725
I'm working through it now sir....sorry for the hasty post.
0
 

Author Comment

by:Joe Ruder
ID: 40501781
Interesting!

Thank you for getting me this close, the only thing is that "searchfor" is pulling form the "local" database and as such does not have all the information from the other side.  It is pulling from the wrong place.

Does that make sense?

In this code:

 
Dim searchfor = (From s In db.stop_address_details _
                 Where lookUpList.Contains(s.customer_reference_no) _
                 Select s).ToList()

DataGridView1.DataSource=postsqlDataList.ToArray
For Each record In searchfor
        Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _
      record.customer_reference_no, record.stop_name, record.stop_addres, _
      record.DatetimeOftop.ToString(), record.timeOfStop.ToString(), vbCrLf

TextBox1.AppendText(outputLine)

Open in new window


Search for is filled with data from the local database, which does not have the received by, delivery times etc.

You ARE loading up the class however, which is exactly what I wanted so regardless you have certainly help a ton with this problem.

Do you know what I am trying to do above?

I am assuming that I can just step through the records one at a time returned from the "remote" then do a compare against the local, correct?

Thank you,

Joe

here is all the code,
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40504835
Hi Joe;

Make the following change to the For Each loop to get the other records.

For Each record In searchfor
  Dim pSqlRec As PostsqlData = postsqlDataList.Find( crn => crn.CustomerReferenceNo = record.customer_reference_no )
  '' From this point on records from remote database can be accessed through the variable pSqlRec
  '' and from the local database through record.
  Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _                                                                                     
      pSqlRec.CustomerReferenceNo, pSqlRec.StopName, pSqlRec.RecBy, _                                                                                                              
      pSqlRec.DatetimeOfStop.ToString(), pSqlRec.timeOfStop.ToString(), vbCrLf)                                                                                                   
  TextBox1.AppendText(outputLine)                                                                                                                                               
Next

Open in new window

0
 

Author Comment

by:Joe Ruder
ID: 40504998
Hello and thank you,

It is saying that crn is not declared.
If I dim it as a object it does not work either.

I have tried google....what does the '=>' do and what is it called?

Thank you,

Joe
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40505026
My mistake this is the correct version of the line of code in the For Each loop.

Dim pSqlRec As PostsqlData = postsqlDataList.Find( crn => crn.CustomerReferenceNo == record.customer_reference_no )

Open in new window


Please note the == meaning a test of being equal to each other and NOT an assignment of the right into the left variable. Because of this crn was not defined.
0
 

Author Comment

by:Joe Ruder
ID: 40505044
Hmm...

It is stating "Unexpected end of statement"  Missing ')' with the =>, the second crn and the == marked as errors.

What does the => mean/do?
0
 

Author Comment

by:Joe Ruder
ID: 40505063
If I :

dim crn as postsqlData it does let the crn.customerReferenceNo work, but the => and == are still popping errors.

Sorry to be such a PITA...

Joe
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40505176
The line of code looks fine. Please do the following.

Post the complete code I ask you to implement. Copy from your code and paste here.
Please post the complete error message for all the errors.
0
 

Author Comment

by:Joe Ruder
ID: 40505300
Thank you Fernando;

here is the Code with the errors::
'' This EF query will return only the records that are needed ant not the complete
'' table from the database
Dim lookUpList = postsqlDataList.Select(Function(crn) crn.CustomerReferenceNo).ToList()

Dim searchfor = (From s In db.stop_address_details _
                 Where lookUpList.Contains(s.customer_reference_no) _
                 Select s).ToList()

 For Each record In searchfor

   Dim pSqlRec As PostsqlData = postsqlDataList.Find (crn => crn.CustomerReferenceNo ==  record.customer_reference_no )
  '' From this point on records from remote database can be accessed through the variable pSqlRec
  '' and from the local database through record.
  Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _                                                                                     
      pSqlRec.CustomerReferenceNo, pSqlRec.StopName, pSqlRec.RecBy, _                                                                                                              
      pSqlRec.DatetimeOfStop.ToString(), pSqlRec.timeOfStop.ToString(), vbCrLf)                                                                                                   
  TextBox1.AppendText(outputLine)                                                                                                                                               
Next

Open in new window


Errors:

The one for => says: Unexpected end of expression - missing ')'

the first crn (crn.customerReferenceNo ==) says unknown entity crn

== says unexpected end of statement
and at the very end of the line it states:
missing line break - unexpected end of statement

Here is the entire code:
  Dim db As New firstChoiceEntities

        ''get all of the stop address details - move them into a in memory clone to stop it from going back to the database for each search later
'' ------------------------------------------------------------------------
' This is not needed as I call the query below and just get what is needed
'Dim stopAddressList = From s In db.stop_address_details Select s
'Dim searchAddressList = stopAddressList.ToArray
'searchAddressList.Clone
'DataGridView1.DataSource=stopAddressList.ToArray '' debug code - remove
'' -------------------------------------------------------------------------
Dim lastDatetimeUpdated As Date = "1/1/2013"
Dim endDate As Date = "12/6/2014"
        Dim checkit As string

                Dim command As PgSqlCommand = PgSqlConnection1.CreateCommand

command.CommandText="select  distinct on (customer_reference) customer_reference, stop_name, stop_address, actual_service_date, Actual_arrival_time, stop_signature " & _
                             "from cops_reporting.distribution_stop_information " & _
                             "where customer_no = '91000' and stop_signature <> '' and actual_service_date IS NOT NULL and datetime_updated > '"& lastDatetimeUpdated & "' and datetime_updated < '" & endDate &"'" 

PgSqlConnection1.Open '' opens the connection to the postgresql server
Label1.Text = "opening reader..."

'' Data from the postsql database download from the server
Dim postsqlDataList As New List(Of PostsqlData)()
Dim totalRecordCount As Integer = 0
'' Load the postsqlDataList from the database
Using reader As PgSqlDataReader =command.ExecuteReader()
    While reader.Read
       totalRecordCount += 1
                Label1.Text = totalRecordCount.ToString
                Label1.Update
       Dim dataRecord As New PostsqlData()
       dataRecord.CustomerReferenceNo = reader.GetValue(0).ToString
       dataRecord.StopName = reader.GetValue(1).ToString
       dataRecord.RecBy = reader.GetValue(5).ToString
       dataRecord.DateOfStop = reader.GetValue(3)
                Try
dataRecord.TimeOfStop = reader.GetValue(4)
                Catch ex As Exception
                    'no time - use default of 9:00 am
                    dataRecord.TimeOfStop=TimeSpan.Parse("9")
                End Try
       
       postsqlDataList.Add(dataRecord)
    End While
End Using 

PgSqlConnection1.Close

'' This EF query will return only the records that are needed ant not the complete
'' table from the database
Dim lookUpList = postsqlDataList.Select(Function(crn) crn.CustomerReferenceNo).ToList()

Dim searchfor = (From s In db.stop_address_details _
                 Where lookUpList.Contains(s.customer_reference_no) _
                 Select s).ToList()

 For Each record In searchfor

   Dim pSqlRec As PostsqlData = postsqlDataList.Find (crn => crn.CustomerReferenceNo ==  record.customer_reference_no )
  '' From this point on records from remote database can be accessed through the variable pSqlRec
  '' and from the local database through record.
  Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _                                                                                     
      pSqlRec.CustomerReferenceNo, pSqlRec.StopName, pSqlRec.RecBy, _                                                                                                              
      pSqlRec.DatetimeOfStop.ToString(), pSqlRec.timeOfStop.ToString(), vbCrLf)                                                                                                   
  TextBox1.AppendText(outputLine)                                                                                                                                               
Next

Label1.Text = "record count: " & searchfor.Count & " out of " & totalRecordCount

End Sub

Open in new window



Is that enough detail or would a screen shot be helpful?

Joe
0
 

Author Comment

by:Joe Ruder
ID: 40505303
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40505324
Sorry Joe I screwed up again.

I work in C# most of the time and when I post VB question I normally check it out in Visual Studio to make sure it is syntactically correct but I have been working away from my computer with the compiler.

In C# this is the start of a Lambda expression, crn => ...., but in Visual Basic it starts like this, Function(crn), so that said the line should be this, mind you I am still at a computer that does not have a compiler. ;=)

Dim pSqlRec As PostsqlData = postsqlDataList.Find (Function(crn) crn.CustomerReferenceNo =  record.customer_reference_no )

Open in new window


By the way I had to change the == back to =, we are in VB .Net.
0
 

Author Comment

by:Joe Ruder
ID: 40505354
Cool....I am looking forward to the day I feel comfortable enough to work away from the complier!

I am working through it now...I'll post back shortly.

Joe
0
 

Author Comment

by:Joe Ruder
ID: 40505432
That's awesome.

That is SUCH a better way of doing it, that will help me going forward as well.

I really appreciate you taking the time to help me out on this sir!

Sincerely,

Joe Ruder
0
 

Author Closing Comment

by:Joe Ruder
ID: 40505433
Thanks again!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40505446
Not a problem Joe, glad to help. Next time I will leave the errors out.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question