Hi,

I have following tables in SQL Server.

IMPORTANT NOTE: I do NOT have any relationship defined in SQL Server Database and I would like to define the relationship in my VB.NET code ONLY.

It has One (tblHeader) to Many (tblDetail) Relationship.

TABLE : tblHeader HeaderID (PK) varchar(10) HeaderName varchar(20) ... ... ...etc

TABLE : tblDetail HeaderID (PK - composite key) varchar(10) DetailNo (PK - composite key) int DetailName varchar(20) ... ... ...etc

I have few textboxes on WinForms to show data from tblHeader and one data grid on Winforms to show tblDetail line(s).

In short I would like to do below query and show data on WinForms.

SELECT * FROM tblHeader WHERE HeaderID = 2501 SELECT * FROM tblDetail WHERE HeaderID = 2501

Regards, Kaksss

Please show the code that you tried to run, and what exactly had failed in it.

        'local variables
        Dim filterBucket As IRelationPredicateBucket = New RelationPredicateBucket()
        Dim headers As New EntityCollection(New FactoryClasses.TblHeaderEntityFactory())
        Dim details As New EntityCollection(New FactoryClasses.TblDetailEntityFactory())

        'define relation(s)
        Dim primaryKeyField As IEntityField2 = New EntityField2("HeaderID", Nothing, Nothing)
        Dim foreignKeyField As IEntityField2 = New EntityField2("HeaderID", Nothing, Nothing)
        Dim typeOfRelation As RelationType = RelationType.OneToMany
        Dim relation As IEntityRelation = New EntityRelation(primaryKeyField, foreignKeyField, typeOfRelation)
        filterBucket.Relations.Add(relation)

        'define filter(s)
        Dim predicateToAdd As FieldCompareValuePredicate = New FieldCompareValuePredicate(FactoryClasses.EntityFieldFactory.Create(TblDetailFieldIndex.HeaderID), Nothing, ComparisonOperator.Equal, 2501)
        filterBucket.PredicateExpression.Add(predicateToAdd)

        'define prefetch path(s)
        Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.TblHeaderEntity, Integer))
        prefetchPath.Add(EntityType.TblHeaderEntity, 0, filterBucket.PredicateExpression, filterBucket.Relations)

        'fetch data
        Dim adapter As New DataAccessAdapter()
        Try
            adapter.FetchEntityCollection(Details, filterBucket)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            adapter.Dispose()
        End Try

        If Details.Count > 0 Then
            DataGridView1.DataSource = Details
        End If

Regards

Kaksss

Dim primaryKeyField As IEntityField2 = New EntityField2("HeaderID", Nothing, Nothing)

That won't work. Use _EntityName_Fields.HeaderID instead.

The relation you have defined does not relate field names to tables. Try it that way:

Dim typeOfRelation As RelationType = RelationType.OneToMany
        Dim relation As IEntityRelation = New EntityRelation(HeaderFields.HeaderID, DetailsFields.HeaderID, typeOfRelation)
        filterBucket.Relations.Add(relation)

Hi,

Can you please look at the prefetch code I posted as I am having below error

Error 1 Value of type 'DAL.EntityType' cannot be converted to 'SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPathElement2'. C:\WindowsApplication1\Form1.vb

help me to define the prefetch for this code...

Please give me a proper example.

Regards

Kaksss

First of all: if you want to do prefetch paths you have to have relations defined in the designer, this is because the data you FETCH has to be stored somewhere and that is done in the property embedded in the prefetchpath element which points to a property in the entity which will contain the data. E.g. customer will contain the order entities fetched and the orders will be stored in the construct supplied by the Orders property in customer.

Second of all, you do this:

prefetchPath.Add(EntityType.TblHeaderEntity, 0, filterBucket.PredicateExpression, filterBucket.Relations)

here you specify an EntityType value as first parameter. Why? Is there an overload of the Add() method which accepts an entity type value? No. Please check the overloads of the Add method to see which options you have and where to specify which parameter.

The first parameter has to be a PrefetchPathElement2 object. You can construct one yourself, the generated code does that too. Normally you get one created for you by using for example CustomerEntity.PrefetchPathOrders, which will construct the PrefetchPathElement2 object to fetch related order entities for a customer entity. If you want to construct that in code, that's ok, but you have to construct that object and pass it at that spot, as the first parameter.

Please give me a proper example.

We can't do your work, though we do want to help you. However you also have to do some things yourself, we can't do that for you. For example, you have to understand the different signatures of the Add method. You also have to understand that by NOT creating the relation in the designer, you are making things difficult for yourself. We can cook up a code example, but that won't teach you why it is done that way.

Hi Otis

Thanks for your reply, I now have added relationship in LLBLGen designer and i have now below code.

Dim Headers As New EntityCollection(New TblHeaderEntityFactory())
Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(TblHeaderEntity, Integer))
prefetchPath.Add(TblDetailEntity.PrefetchPathTblHeader)
Dim filter As IRelationPredicateBucket = New RelationPredicateBucket()
filter.PredicateExpression.Add(New FieldCompareValuePredicate(TblHeaderFields.Id, Nothing, ComparisonOperator.Equal, "2501"))
Dim adapter As New DataAccessAdapter()
Try
    adapter.FetchEntityCollection(Headers, filter, prefetchPath)
Catch ex As Exception
    MessageBox.Show(ex.ToString)
    adapter.Dispose()
End Try
Dim data as String
For Each Header As TblHeaderEntity In Headers
    data = data & Header.Id.ToString() & Environment.NewLine
    data = data & Header.ShipperCode & Environment.NewLine
    MessageBox.Show(data)
    DataGridView1.DataSource = Header.TblDetails
Next

and I am getting below exception.

System.ApplicationException: The prefetch path element at index 0 in the passed in prefetch path for root entity type 20 is meant for root entity type 13 which isn't a subtype of 20. This means that you've added a prefetch path node to a Path of an unrelated entity, like adding OrderDetailsEntity.PrefetchPathProduct to a prefetch path for CustomerEntity.

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchParameterisedPrefetchPath(IEntityCollection2 rootEntities, Int64 maxNumberOfItemsToReturn, IPrefetchPath2 prefetchPath)

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, IPrefetchPath2 prefetchPath)

   at Form1.Button3_Click(Object sender, EventArgs e) in C:\WindowsApplication1\Form1.vb:line 527

Hi Otis

Oh my silly mistake... Please ignore my last message, after changing prefetch path to below line it works.

prefetchPath.Add(TblHeaderEntity.PrefetchPathTblDetails)

But as mentioned in LLBLGen2 Help file to use Debugger Visualizers I have copy files etc.... everything is OK I have restarted VS2005 IDE...

What is now happening is application is working as it should but Debugger Visualizers is crashing. When I move mouse under 'Headers' object in VS2005 IDE and a tooltip is shown and I click on magnifyer glass and then Entity Collection Visualizer and it open a new window (with grid) which shows 1 row from Header table when I click + sign on left side of the TblHeader in grid it expand and shows me TblDetails link and when I click on TblDetail link it shows exception window as per below message...

See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
   at System.Windows.Forms.DataGridBoolColumn.GetColumnValueAtRow(CurrencyManager lm, Int32 row)
   at System.Windows.Forms.DataGridParentRows.GetColDataBoxWidth(Graphics g, Int32 colNum)
   at System.Windows.Forms.DataGridParentRows.PaintParentRows(Graphics g, Rectangle bounds, Boolean alignToRight)
   at System.Windows.Forms.DataGridParentRows.Paint(Graphics g, Rectangle visualbounds, Boolean alignRight)
   at System.Windows.Forms.DataGrid.OnPaint(PaintEventArgs pe)
   at System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs e, Int16 layer, Boolean disposeEventArgs)
   at System.Windows.Forms.Control.WmPaint(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
Microsoft.VisualStudio.CommonIDE
    Assembly Version: 8.0.0.0
    Win32 Version: 8.0.50727.42
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualStudio.CommonIDE/8.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.CommonIDE.dll
----------------------------------------
Microsoft.VisualStudio.DebuggerVisualizers
    Assembly Version: 8.0.0.0
    Win32 Version: 8.0.50727.42
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualStudio.DebuggerVisualizers/8.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.DebuggerVisualizers.dll
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
SD.LLBLGen.Pro.DebugVisualizers
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.0.060701
    CodeBase: file:///D:/My%20Documents/Visual%20Studio%202005/Visualizers/SD.LLBLGen.Pro.DebugVisualizers.dll
----------------------------------------
SD.LLBLGen.Pro.ORMSupportClasses.NET20
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.0.060712
    CodeBase: file:///D:/My%20Documents/Visual%20Studio%202005/Visualizers/SD.LLBLGen.Pro.ORMSupportClasses.NET20.DLL
----------------------------------------
NS.DAL
    Assembly Version: 1.0.2386.23612
    Win32 Version: 1.0.2386.23612
    CodeBase: file:///C:/WindowsApplication1/bin/Debug/NS.DAL.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Design
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Design/2.0.0.0__b03f5f7f11d50a3a/System.Design.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
Accessibility
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Accessibility/2.0.0.0__b03f5f7f11d50a3a/Accessibility.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Regards

Kaksss

What runtime Library version are you using?

Are there any records in the Details collection fetched with the header?

I have checked the data in SQL Server and there is a 1 row in Header table and 2 rows in details table.

Runtime Library from 13 July 2006

v2.0.0.0.07122006

(last release LLBLGen Pro 2)

Regards

Kaksss

Hmm, it crashes in the grid code, which is strange. Do you have nullable types in your entities in the collection you want to show and which gives this exception and are one of these fields null ? (should still work though... odd)

Hi Otis

After carefully checking I have noticed that when I fetch data in Parent-Child tables (prefetch), and after looking in the Parent table's data, the last 2 fields datatype in my parent table is (boolean, datetime) and both was NULL and when I was scroll ito right side in Visualizer Grid to see this two fields, I get exception, so then I filled Boolean and Datetime in above 2 fields it works perfectly.

So it looks like there is a bug in Visualizer where it is throwing the exception. Please advice.

Regards

Kaksss

The visualizer just binds the objects to a grid. The exception occurs in the datagrid, not in the llblgen pro code. I'll see if I can repro it. THe data gets serialized to the visualizer, which could mean there's some data not transfered, databinding to a grid without serialization works normally, WITH nulled fields.

Strange, I can't reproduce it. I have a nullable datetime column and I can view it perfectly and it does contain null. (I view a list of order entities in the debugger visualizer, one of them has ShippedDate set to NULL, which results in null, and thus null in the property...)

Hi Oris

I have re-tried and I can still getting exception. Below is the code, what should I do to fix this? if I change the value in last two column in database from NULL to some value then it's working but if I set to NULL it throw an exception.

Dim Headers As New HelperClasses.EntityCollection(New FactoryClasses.TblHeaderEntityFactory())
Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.TblHeaderEntity, Integer))

prefetchPath.Add(EntityClasses.TblHeaderEntity.PrefetchPathTblDetails)
prefetchPath.Add(EntityClasses.TblHeaderEntity.PrefetchPathTblMoreDetails)

Dim filter As IRelationPredicateBucket = New RelationPredicateBucket()
'filter.PredicateExpression.Add(New FieldCompareValuePredicate(HelperClasses.TblHeaderFields.Id, Nothing, ComparisonOperator.Equal, 2501))
filter.PredicateExpression.Add(HelperClasses.TblHeaderFields.Id = 2501)

Dim adapter As New DatabaseSpecific.DataAccessAdapter()
Try
    adapter.FetchEntityCollection(Headers, filter, prefetchPath)
Catch ex As Exception
    MessageBox.Show(ex.ToString)
    adapter.Dispose()
End Try

Thanks and Regards,

Kaksss

It's a bug in the DataGridBoolColumn class of .NET:

/// <summary>Gets the value at the specified row.</summary>
/// <returns>The value, typed as <see cref="T:System.Object"></see>.</returns>
/// <param name="lm">The <see cref="T:System.Windows.Forms.CurrencyManager"></see> for the column. </param>
/// <param name="row">The row number. </param>
protected internal override object GetColumnValueAtRow(CurrencyManager lm, int row)
{
      object obj1 = base.GetColumnValueAtRow(lm, row);
      object obj2 = Convert.DBNull;
      if (obj1.Equals(this.trueValue))
      {
            return true;
      }
      if (obj1.Equals(this.falseValue))
      {
            obj2 = false;
      }
      return obj2;
}

If the value of the bound field is null, obj1 is null. This of course crashes at the if statement.

Clearly buggy code which can't deal with nullable types. There's unfortunately not much I can do

Hi Otis,

Yeah that's right. It's a bug.

Should we report to Microsoft?

Thanks

Kaksss

You can try, I don't give you much hope though. The thing is: if they fix it, they won't release a public patch, so if you distribute an app relying on this to your customers, they don't have the patch and you can't distribute the private patch you've got from MS.

I've filed a bugreport here: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=165580

Thanks Otis for your wonderful support.

Very Very happy with your product and support.

Regards

Kaksss

I'll post back in this thread IF Ms ever decides to fix this.

Well, exactly after 2 months (! ) I got a reply: they can't reproduce it.

Now, I tried to repro it again, and I also can't repro it with common code and no debuggervisualizer. So it's either somewhere related to the debugger visualizers or to llblgen pro property descriptors, however I fail to see why.

Can you still reproduce it?

Otis wrote:

Well, exactly after 2 months (! ) I got a reply: they can't reproduce it.

Now, I tried to repro it again, and I also can't repro it with common code and no debuggervisualizer. So it's either somewhere related to the debugger visualizers or to llblgen pro property descriptors, however I fail to see why.

Can you still reproduce it?

Would this have something to do with the fact the ADO.Net uses DBNull.Value rather than null? That way the code in DataGridBoolColumn.GetColumnValueAtRow would actually be correct since obj1 would not be null.

Cheers Simon

That's what I thought but when I have a class with a Nullable<bool> property, even when I use an ITypedList implementing collection, it works. Which is remarkable, as the propertydescriptors are of type ReflectPropertyDescriptor which simply call the get method on the property, and thus when the property is null it will return null. However somehow it gets converted to DBNull.value. My propertydescriptors also simply return null, so how this can be different I have no idea.

It might be the currencymanager does this, as the exception reported happened in a debuggervisualizer, which might have a different environment.