• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Program Logic to summaries Data

I have a stored Procedure which returns me route Information to build a route table. The return data looks like the sample below

I have a app which queries a MS Sql Table which returns me Phone nbr Prefixes,Start and End Nbrs
The field is called prefix ,start,end and has data like

Prefix                Trunk          
===================
213232            Trunk 1
213235            Trunk 1                
213238            Trunk 1                
213239            Trunk 1              
213242            Trunk 2                
213243            Trunk 1          
213446            Trunk 1


to save space in my other apps i need to be able to get to be able to find all continues blocks and then summaries them like this

Start+ End       Start                        End           Trunk
213232_239     213232                    213239      Trunk 1
213242            213242                    213242      Trunk 2
213243_446     213446                    213446      Trunk 1


0
AlexPonnath
Asked:
AlexPonnath
  • 2
1 Solution
 
amyhxuCommented:
Is the second table an existing table in SQL? If so, you can generate a datatable based on the table in SQL; if not, you'll write code to generate the table structure in both dataset and SQL.
Suppose your stored procedure is used to generate a table named "tbl1", and your result table is called "tbl2". They are both in the same dataset "myDataSet".

Dim StartNewRow As Boolean = True
Dim Trunk As String
Dim newRow As DataRow

For Each dRow As DataRow In myDataSet.Tables("tbl1").Rows
    StartNewRow = Not (Trunk = dRow("Trunk"))
    If StartNewRow Then
        If Not newRow Is Nothing Then
            myDataSet.Tables("tbl2").Rows.Add(newRow)
        End If
        newRow = myDataSet.Tables("tbl2").NewRow
        Trunk = dRow("Trunk")
        newRow("StartEnd") = dRow("Prefix")
        newRow("Start") = dRow("Prefix")
        newRow("End") = dRow("Prefix")
        newRow("Trunk") = dRow("Trunk")
    Else
        newRow("StartEnd") = newRow("StartEnd").SubString(0, 6) & "_" & CStr(dRow("Prefix")).SubString(3, 3)
        newRow("End") = dRow("Prefix")
    End If
Next

' Add last new row to tbl2
If Not newRow Is Nothing Then
    myDataSet.Tables("tbl2").Rows.Add(newRow)
    newRow = Nothing
End If

0
 
AlexPonnathAuthor Commented:
yje first one is a Table in SQl Server, the second one is the output i like to save to a Text file
based on the Query and the logic..
0
 
AlexPonnathAuthor Commented:
Also i would like not to use a dataset if posible but rather use a data reader
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now