ADOMD.NET AdomdDataReader + ASP.NET 2.0 gridview?

Anybody had any success with populating an ASP.NET 2.0 gridview using ADOMD.NET AdomdDataReader?

I am trying the following code:

Dim oSb As New StringBuilder

Dim sMDX As String

Dim sCnnString As String = "DataSource=localhost"

Dim oAdoMdCnn As AdomdConnection

Dim oAdoMdCmd As AdomdCommand

Dim oAdoMdRdr As AdomdDataReader

'...build oSb

sMDX = oSb.ToString

sCnnString = "DataSource=localhost"

oAdoMdCnn = New AdomdConnection(sCnnString)

oAdoMdCnn.Open()

oAdoMdCmd = New AdomdCommand(sMDX, oAdoMdCnn)

oAdoMdRdr = oAdoMdCmd.ExecuteReader()

Dim oTable As New DataTable

oTable.Load(oAdoMdRdr)

gvResults.DataSource = oTable

gvResults.DataBind()

I get an error message at oTable.Load(oAdoMdRdr) that says:

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

This gives a couple of options for datasets, but no mention of datareaders nevertheless AdomdDataReaders. I have tried "oTable.Constraints.Clear()" to eliminate any constraints, but no luck. I don't want any keys or constraints. I don't need any writeback so I think using a dataset may be more overhead than necessary.

Anyone?

Keehan

hello Keehan,

it's not competely clear to me what the problem is here (it might be specific to the query result, so if you can provide a query against Adventure Works sample that this error happens with, then perhaps it could shed some light here).

however, you could also try something like:

Dim oTable As New DataTable()

// where cmd is the command to execute
Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(oTable)
End Using

and see if that works to populate the table.

hope this helps,

|||

Mary,

I tried using AdomdDataAdapter as you recommended and I get an even weirder message:

InvalidOperationException was unhandled by user code

“The connection cannot be used while an XmlReader object is open.”

While debugging I found that if I look at the properties of adapter, the Message attribute of the SelectCommand property says:

Message"Unable to cast object of type 'Microsoft.AnalysisServices.AdomdClient.AdomdCommand' to type 'System.Data.Common.DbCommand'."String

And the StackTrace attribute says:

StackTrace"at System.Data.Common.DbDataAdapter.get_SelectCommand()"String

I know the MDX query itself works fine.I’m capturing it to a text box and then I can run it from Management Studio.I’m just having trouble getting the plumbing of this to work.Any other ideas?

If anyone has a sample using ADOMD.NET to populate a gridview I would be grateful.

Cheers,

Keehan

|||

hello Keehan,

actually, the error you observe indicates that a data reader (or xml reader) were not closed. It means that somewhere in the code there is a call to cmd.ExecuteReader(), but the returned reader is never closed or disposed. It is very impornant that the reader is closed otherwise the error would be thrown just as the one you observed. (so it is good idea to work with reader with Using or try-finally to make sure it's always closed/disposed)

the following code snippet works fine for me, populating the DataTable (resultsInTable) with data fine:

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()

con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

so, i'm not sure why it would not work for you. maybe something else happens when different data comes back (obviously when different query is executed), but i couldn't tell not knowing more specifics.

also, it looks like the GridView with AutoGenerateColumns=true, has some limitations as to how the columns of type System.Object are handled - i think they are not added to grid view (or maybe i was doing something wrong - as i'm not too familiar with the System.Web.UI.WebControls.GridView). so it might be that for the example above one would have to write some more code to actually create columns in the grid view for the un-bindable column types, or do some other things: like tweaking the data table converting the un-bindable data types to strings or something else...

all in all for the sample above the following code worked for me (note code is not too clean, and is just intended as an illustration; i'm also not too familiar with VB):

Imports Microsoft.AnalysisServices.AdomdClient
Imports System.Data

Partial Class _Default
Inherits System.Web.UI.Page

Dim gv As GridView

Protected Sub Page_Load1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()
con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

' because looks like GridView has limitations and only supports certain column types (and not not object typed one for autogenerate columns)
' let's do the following : for each not supported column type - add a string typed column

Dim numberColumns As Integer
numberColumns = resultsInTable.Columns.Count - 1
For i As Integer = 0 To numberColumns

Dim current As DataColumn
current = resultsInTable.Columns.Item(i)
Dim colName As String

Dim baseName = "column_"
'you might need to be smarter about picking the base of the column name,
'as column names based on it must not be present in the table already

If BaseDataList.IsBindableType(current.DataType) = False Then
colName = current.ColumnName
current.ColumnName = baseName + i.ToString() ' might need to be smarter and check if the name already exists in the table
Dim col As New DataColumn(colName, System.Type.GetType("System.String"), "Convert(" + current.ColumnName + ", 'System.String')")
resultsInTable.Columns.Add(col)
End If
Next i

gv.DataSource = resultsInTable
gv.DataBind()

End Sub

Protected Sub form1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Init
gv = New GridView()
gv.AutoGenerateColumns = True
form1.Controls.Add(gv)

End Class

hope this helps,

|||Hello Mary,

i was having the problem that when a i bound the return of an mdx command to a gridView, the columns simply doesn′t appear.

i searched at the web and i found your solution, i'm just interesting to know if there is another way to show the columns in the gridView, without handling them as you did, i mean if there is another component, another alternative, i know that this solutions works ( i tested it :P ) , but maybe there is another way, and since your post was made quite a long time, maybe you or other person, have another solution.

Thanx.|||

hello,

unfortunatelly, i don't have more info on this. I would suggest you to post a question on "Data Presentation Controls" on ASP.NET forum (http://forums.asp.net/24/ShowForum.aspx), and ask about the support for columns of System.Object type with AutoGenerateColumns=true. Perhaps there are other control(s), or maybe there are some settings that can make the GridView work in this scenario. Hopefully they would be able to answer.

hope this helps,

|||

You need to turn off constraints at the DataSet level. You have simply tried to clear existing constraints at the table level.

Here's a C# example

DataSet dataSet = new DataSet(); // Create a dataset

dataSet.EnforceConstraints = false; // turn off constraints

dataSet.Tables.Add("Results"); // Add an arbitary table

dataSet.Tables["Results"].Load(reportDataReader); // Load the ADOMD reader into the table

You will then be able to bind the 'Results' table to a gridview.

Hope this helps

Sacha Tomey

Blog - http://blogs.adatis.co.uk/blogs/sachatomey

Consultancy - http://www.adatis.co.uk