locked
Best way to gets results from my data RRS feed

  • Question

  • Hi

    Firstly excuse the vague title, having trouble describing what I am trying to do with a simple title!

    I am pulling some data from a SQL database and I need to then further manipulate this data to get various results

    The data comes from a help desk system, so is basically a list of calls, who they are assigned to, date opened, date closed etc etc

    As I need to do get several different things from this dataset, I though it easier to run one query against SQL DB to pull out a datatable containing all the useful data, and then work with this - rather than multiple SQL queries for each item I want to show

    So as per example code below I am pulling out these fields and storing them in a dataset

    Firstly is this the best way to start this - or should I be pulling out data and storing it in a different format to then get my information?

    I know in the past people have suggested using LINQ as a good way of querying data - so not sure if relevant here?

    If I show you what I am pulling out first, then that may help, and I can then answer any questions that may then allow you to give me some thoughts please

    Dim mydataadapter = New SqlDataAdapter("SELECT Ticket, Completed_Date, Due_Date, Start_Date, Submit_Date, Assignee,  Category, Status, Priority, Created_by, Created_Date, Modified_Date, Activity_Count FROM Work_Order_List_Active", myconnection)
    Dim mydataset = New DataSet()
    mydataadapter.Fill(mydataset)

    So as an example the first result I want to get from this information is total number of calls per technician ("assignee" is the field name)

    Then divide up different "status"es of calls per "assignee" - e.g. so to show Fred has 8 open, 3 pending, 4 closed etc

    Then I would want to get calls between certain dates to show how many calls opened this week, how many closed etc

    Then things like how many calls per "Category"

    Once I know best way to store the data and how to query it to get one result I think the rest will follow and make sense

    Thanks in advance for your input


    Darren Rose

    Wednesday, February 27, 2019 10:17 PM

Answers

  • Here is a no frills example, busy day.

    Imports System.Data.SqlClient Imports BaseConnectionLibrary.ConnectionClasses Public Class DatabaseResults Inherits SqlServerConnection Public Sub New() DatabaseServer = ".\SQLEXPRESS" DefaultCatalog = "ForumExample" End Sub Public Function Demo1() As List(Of Call_List) Dim theList = New List(Of Call_List) Using cn As New SqlConnection(ConnectionString) Using cmd As New SqlCommand With {.Connection = cn} cmd.CommandText = "SELECT id,Assignee,[Status] FROM dbo.Call_List" cn.Open() Dim reader = cmd.ExecuteReader() If reader.HasRows Then While reader.Read() theList.Add(New Call_List() With { .id = reader.GetInt32(0), .Assignee = reader.GetString(1), .Status = reader.GetString(2) }) End While End If End Using End Using Return theList

    End Function End Class Public Class Call_List Public Property id As Integer Public Property Assignee As String Public Property Status As String End Class

    Usage

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) _
            Handles Button1.Click
    
            Dim ops = New DatabaseResults
            Dim results = ops.Demo1()
    
            Dim FredCount = results.
                    Where(Function(item) item.Assignee = "Fred").
                    Count()
    
            Console.WriteLine(FredCount)
    
            Dim FredCountSpecic = results.
                    Where(Function(item) item.Assignee = "Fred" AndAlso item.Status = "Open").
                    Count()
    
            Console.WriteLine(FredCountSpecic)
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by wingers Friday, March 1, 2019 5:51 PM
    Thursday, February 28, 2019 5:40 PM

All replies

  • Greetings,

    The first thought many have is to query against a database and store to a local container e.g. DataSet then use code to get what they are after but when humanly possible consider storing what would be commonly stored in a DataSet instead in a SQL-Server database with tables (or another possibility is using temp tables which is a tad more work involved in clean up) exclusively for the purpose of extracting information you are after.

    Transactional SQL (TSQL) has a wealth of functions (math, dates, strings etc) to work out task along with the ability to write scripts that can be executed from Visual Studio. The downside is there is a learning curve to this e.g. in some cases the SQL statements may be written in code while in other cases in stored procedures. 

    So high level, how many calls for "Category", that would most likely entail a date range where there is the BETWEEN operator to work on the range, there may be (dependent on the database schema) one or more joins involved. To make life easier use the query designer in SSMS to write the query fully or partly and be able to test it.

    Working from code, I would look at using Entity Framework Lambda style but be forewarned there can be times were dates are done a tad differently than in conventional DataSet flow coupled with LINQ or Lambda.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, February 27, 2019 10:39 PM
  • Hi Karen

    Thanks for reply

    I would like if possible to avoid having to use SQL to store the data I am then querying, the SQL server I am pulling it from is a very busy and critical server, so wanted to just get the data out (and it isn't a huge amount of data to be honest anyway) and then process it it locally in app away from the server.

    As I say the amount of data I am pulling out is not going to be huge, tonight it is no more than 100 rows of data, so very small, so hopefully negates need to use SQL to store it etc


    Darren Rose

    Wednesday, February 27, 2019 10:43 PM
  • I should had indicated the data would not be stored on a production database but instead a local database even SQL-Express would work.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, February 27, 2019 10:59 PM
  • I should had indicated the data would not be stored on a production database but instead a local database even SQL-Express would work.

    Okay fair point - and I understand what you are saying, but since the data I am pulling out is quite small I really don't see the point or want that overhead, several users may use the tool when done, and I don't want to have to rely on installing SQL-Express on each machine, or having them point to another server


    Darren Rose

    Wednesday, February 27, 2019 11:10 PM
  • Okay I have found the best way "for me" to store the data - as summarized below just showing two fields initially:-

    Public Class DatabaseResults
            Property Assignee As String
            Property Status As String
    End Class
    
    Private Sub btnTest1_Click(sender As Object, e As EventArgs) Handles btnTest1.Click
    
        Dim myconnectionstring As String = "Server=" & ServerName & ";Initial Catalog=" & Database & ";User ID=name;Password=password"
    
            Dim myconnection As New SqlConnection(myconnectionstring)
    
            mycommand = myconnection.CreateCommand
            mycommand.CommandText = "SELECT Assignee, Status FROM Call_List"
    
            myconnection.Open()
    
            mydatareader = mycommand.ExecuteReader()
    
            ' Add results to my list / collection - unless emtpy
            Dim DatabaseResultsCollection As List(Of DatabaseResults) = Nothing
            If mydatareader.HasRows Then
    
                DatabaseResultsCollection = New List(Of DatabaseResults)
                Do While mydatareader.Read()
    
                    DatabaseResultsCollection.Add(New DatabaseResults With {
                                       .Assignee = mydatareader("Assignee"),
                                       .Status = mydatareader("Status")})
    
                Loop
    
            End If
    
            mydatareader.Close()
            myconnection.Close()
    
    
    End Sub
    

    So now I have in my DatabaseResultsCollection (based on above sample of just two fields) a list containing "assignee" and the "status" of the call 

    The Status can be open, closed, in progress etc.  

    The Assignee contains one of four possible technician names e.g. Fred, John etc

    So how can I query my DatabaseResultsCollection to get count of how many time one name appears e.g. Fred - so it will tell me how many calls he has 

    And how can I query to show number of a particular status per assignee e.g. How many calls with "status" of Open does "assignee" John have

    In past questions I remember LINQ was usually suggested, would this be a potential solution to get information such as this?


    Darren Rose



    • Edited by wingers Thursday, February 28, 2019 4:24 PM
    Thursday, February 28, 2019 4:23 PM
  • Here is a no frills example, busy day.

    Imports System.Data.SqlClient Imports BaseConnectionLibrary.ConnectionClasses Public Class DatabaseResults Inherits SqlServerConnection Public Sub New() DatabaseServer = ".\SQLEXPRESS" DefaultCatalog = "ForumExample" End Sub Public Function Demo1() As List(Of Call_List) Dim theList = New List(Of Call_List) Using cn As New SqlConnection(ConnectionString) Using cmd As New SqlCommand With {.Connection = cn} cmd.CommandText = "SELECT id,Assignee,[Status] FROM dbo.Call_List" cn.Open() Dim reader = cmd.ExecuteReader() If reader.HasRows Then While reader.Read() theList.Add(New Call_List() With { .id = reader.GetInt32(0), .Assignee = reader.GetString(1), .Status = reader.GetString(2) }) End While End If End Using End Using Return theList

    End Function End Class Public Class Call_List Public Property id As Integer Public Property Assignee As String Public Property Status As String End Class

    Usage

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) _
            Handles Button1.Click
    
            Dim ops = New DatabaseResults
            Dim results = ops.Demo1()
    
            Dim FredCount = results.
                    Where(Function(item) item.Assignee = "Fred").
                    Count()
    
            Console.WriteLine(FredCount)
    
            Dim FredCountSpecic = results.
                    Where(Function(item) item.Assignee = "Fred" AndAlso item.Status = "Open").
                    Count()
    
            Console.WriteLine(FredCountSpecic)
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by wingers Friday, March 1, 2019 5:51 PM
    Thursday, February 28, 2019 5:40 PM
  • Thank you Karen, will try it out later

    That should cover basic queries, may need to do some more detailed so might come back later!!


    Darren Rose

    Thursday, February 28, 2019 6:09 PM
  • All working well and can now get the data I need

    Thanks


    Darren Rose

    Friday, March 1, 2019 5:51 PM