?
Solved

Building DataSet dynamically with values from stored proc

Posted on 2009-04-21
6
Medium Priority
?
358 Views
Last Modified: 2012-08-13
I am inheriting a system where dashboard tables where built out on the fly in .asp.

The table were built using a series of stored proc calls that defined the columns and values for the resulting table.

I need to define an objectdatasource that consumes a function returning a dataset to populate an UltraWebGrid.

This is the concept of my solution, but I need some help:

Where the .asp was dynamically building the table, I will dynamically build the dataset to be returned to the UltraWebGrid.

First call to sp1 give the following data for the columns:
columnName, columnAlignment, Alignment, columnWidth, FormatType

The call to sp2 gives the following data for the values:
RowID, ColumnName, Value



Therefore my code looks like:
==================================================
Public Function GetDataSetForGrid() as Dataset
   Dim con as new SqlClient.SqlConnection(ConStr)
   Dim da As New SqlClient.SqlDataAdapter("pcGet_Columns " & sGridID, con)
   Dim ds As New DataSet()

       'Define columns
        For Each row as DataRow In ds.Tables(0).Rows
           Dim st As System.Type = GetDataType(row.Item("FormatType").ToString)
           dsReturn.Tables(0).Columns.Add(row.Item("ColumnName").ToString,st)
        Next

        da = New SqlClient.SqlDataAdapter("pcGet_Values " &  sGridID, con)
        ds = New DataSet()
        da.Fill(ds, "defaultview")

       'Define values
       For each row as DataRow In ds.Tables(0).Rows

      THIS IS WHERE I LOSE IT... HOW DO I CREATE THE ROWS WITH THE CORRESPONDING VALUES?

       Next


   Return dsReturn
End Function

=================================================================

Any thoughts???

0
Comment
Question by:JackWooten
  • 2
  • 2
  • 2
6 Comments
 
LVL 9

Expert Comment

by:Seven price
ID: 24198952
Datagrid.DataSource = dataSet.Tables("nameoftable")
Datagrid.DataBind()
0
 
LVL 11

Assisted Solution

by:srikanthreddyn143
srikanthreddyn143 earned 400 total points
ID: 24199320
 For each row as DataRow In ds.Tables(0).Rows
  'You can loop through each column in dataset dsReturn or you can manulaly do this way
    dsNewRow = dsReturn.Tables(0).NewRow()
                        dsNewRow.Item(Column1) = row(Column1)
            dsNewRow.Item(Column2) = row(Column2)
    Continue with other columns
     
     dsReturn.Tables(0).Rows.Add(dsNewRow)
 Next
0
 
LVL 9

Assisted Solution

by:Seven price
Seven price earned 1600 total points
ID: 24199465
I think he was talking about the final return after the ds is ran.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JackWooten
ID: 24204358
Snake: Thanks for the response, but no i am aware how to bind the dataset.  The issue is how to populate the dataset from the stored proc call giving me the values.  I will continue to work this and see if I can provide further definition...

srikanthreddyn143: I will try to implement what you suggest. Thanks

0
 

Accepted Solution

by:
JackWooten earned 0 total points
ID: 24215348
Right or Wrong this is the code I generated... Would love someone's opinion on a more "efficient" way to do this (other than writing the stored proc to handle this)      

Public Shared Function getDataSetForGrid(ByVal iTabID As Int32, ByVal iGridID As Int32) As DataSet

            dsReturn = New DataSet
            Dim tReturn As New DataTable
            dsReturn.Tables.Add(tReturn)

            Dim ds As DataSet = getDataSet("pcGet_Columns " & iGridID.ToString)

            'Define columns
            Dim iCt As Int32 = 0
            Dim sColumnArray(ds.Tables(0).Rows.Count - 1) As String
            For Each row As DataRow In ds.Tables(0).Rows
                  Dim st As System.Type = GetDataType(row.Item("FormatType").ToString)

                  'Build column array to be used later to match up values with columns
                  sColumnArray(iCt) = row.Item("ColumnName").ToString

                  dsReturn.Tables(0).Columns.Add(row.Item("ColumnName").ToString, st)
                  iCt += 1
            Next


            ds = getDataSet("pcGet_Values " & iTabID.ToString & ", " & iGridID.ToString)

            'Define values
            Dim i As Int32
            Dim x As Int32
            Dim row2 As DataRow
            Dim iRowID As Int32
            Dim prevID As Int32 = 1

            For x = 0 To ds.Tables(0).Rows.Count - 1
                  dsNewRow = dsReturn.Tables(0).NewRow()

                       'Column array from previous
                  For i = 0 To CShort(sColumnArray.Length - 1)
                        iRowID = CInt(ds.Tables(0).Rows(x)("RowID"))
                        row2 = ds.Tables(0).Rows(x)
                        If iRowID <> prevID Then Exit For
                        dsNewRow.Item(sColumnArray(i)) = row2("Value")
                        prevID = iRowID
                        If i <> sColumnArray.Length - 1 Then x += 1
                  Next
                  prevID += 1
                  dsReturn.Tables(0).Rows.Add(dsNewRow)
            Next

            Return dsReturn

      End Function
0
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24701507
I think you shared the points in wrong way. you told to add rows to dataset thats what my logic does right?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

807 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