Solved

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

Posted on 2014-12-05
19
56 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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem Joe.
0
 

Author Comment

by:Joe Ruder
Comment Utility
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
Comment Utility
I'm working through it now sir....sorry for the hasty post.
0
 

Author Comment

by:Joe Ruder
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Joe Ruder
Comment Utility
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
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks again!
0
 
LVL 62

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now