How to easily get a single record from table

14 posts, 1 answers
  1. MWM
    MWM avatar
    64 posts
    Member since:
    Jul 2007

    Posted 08 Dec 2008 Link to this post

    Can someone provide me with simple code (C# preferrably) which will utilize the auto-generated classes per the OA wizard to
     
    1) retrieve a single record from a table (using pk such as "id"),
    2) edit that record through the use of an object, and
    3) persist the object back to the db table?

    I've tried without success, something like:

    string

     

    queryString = "select * from Org as x ";

     

    IObjectScope

     

    scope = Database.Get("DatabaseConnection1").GetObjectScope();
    IQuery oqlQuery = scope.GetOqlQuery(queryString);
    IQueryResult result = oqlQuery.Execute();

    Org
    oneOrg = new Org();

     

    oneOrg = (

    Org)result;

     

    result.Dispose();

    As you can see, I need some direction...

  2. Answer
    Burl
    Burl avatar
    41 posts
    Member since:
    Apr 2008

    Posted 08 Dec 2008 Link to this post

  3. DevCraft banner
  4. MWM
    MWM avatar
    64 posts
    Member since:
    Jul 2007

    Posted 08 Dec 2008 Link to this post

    Burl,

    This could not have been more helpful!  Thanks so much.
  5. Erik
    Erik avatar
    314 posts
    Member since:
    Feb 2008

    Posted 29 Mar 2011 Link to this post

    Look at this new article:

    http://blogs.telerik.com/blogs/posts/11-03-21/a_simpler_objectkey.aspx

    Espeassially at tyhe comment from Jan Blessenohl, thats works fine if you extend the partial class OA context with it.

    public T GetById<T>(int key)
        {
            return Context.GetObjectByKey<T>(new ObjectKey(typeof(T).FullName,key));
        
      
    public T GetById<T>(long key)
        {
            T t;
            Context.TryGetObjectByKey<T>(new ObjectKey(typeof(T).FullName,key), out t);
            return t;
        }

    VB:
    Public Function GetSingle(Of T)(aint_PKvalue As System.Int32) As T
        Return Me.GetObjectByKey(Of T)(New ObjectKey(GetType(T).FullName, aint_PKvalue))
    End Function
     
    Public Function GetSingle(Of T)(alng_PKvalue As Long) As T
        Return Me.GetObjectByKey(Of T)(New ObjectKey(GetType(T).FullName, CType(alng_PKvalue, System.Int32)))
    End Function
  6. Erik
    Erik avatar
    314 posts
    Member since:
    Feb 2008

    Posted 03 Apr 2011 Link to this post

    Back again...

    The code works fine, with some additional overloads.

    Now I have some tables where the PK is an int and some with a bigint. The function crashes with the bigints

    how do i determine the type of the PK for an entity?
    It should be something with object key I suppose, but can't get it to work.

    Thanks,

    Erik
  7. Patrice Boissonneault
    Patrice Boissonneault avatar
    18 posts
    Member since:
    Mar 2010

    Posted 03 Apr 2011 Link to this post

    And how about table where primary key is more than one field?
  8. Erik
    Erik avatar
    314 posts
    Member since:
    Feb 2008

    Posted 03 Apr 2011 Link to this post

    Yes, good point, so I'll rephrase: How can we analyze the PK of a entity?
  9. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 06 Apr 2011 Link to this post

    Hi all,

    @Mark
    We have an online documentation topic that discusses the various usage of the OpenAccess API. To work with OpenAccess you have two approaches at your disposal. The one you have tried some operations with is the classic OpenAccess scope API. We are currently concentrating our efforts in the direction of the visual designer approach which is the other alternative. The visual designer approach automates a lot of tasks that previously with the scope API were accomplished with several steps. You will find that the documentation is basically split into two sections - each elaborating on use-case scenarios with the respective approach.

    @Erik and Patrice
    The primary key of an entity can be fully analyzed with the help of an ObjectKey instance. The ObjectKey has an array of ObjectKeyMembers. Persistent types with composite identity have their array of ObjectKeyMembers with a size greater than one. Thus you can differentiate between single-field and multiple-field identity for a persistent type. You can further analyze each ObjectKeyMember and obtain the name of the property that participates in the identity (ObjectKeyMember.Key) and also the property value (ObjectKeyMember.Value). The type of the identity property can be obtained with the help of reflection in the following manner:
    OrderDetail orderDetail = context.OrderDetails.Where(od => od.Quantity > 10).First();
    ObjectKey objectKey = ObjectKey.Create(orderDetail);
    var objectKeyValues = objectKey.ObjectKeyValues;
    ObjectKeyMember firstCompositeKeyMember = objectKeyValues[0];
    Type firstCompositeKeyMemberType = firstCompositeKeyMember.Value.GetType();

    To sum up, the new ObjectKey API is designed to do just that -  non-trivial handling of the identity of persistent types. We hope the provided information is useful. Do not hesitate to contact us, If you have further doubts regarding the usage of the ObjectKey.

    Kind regards,
    Petko_I
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  10. Erik
    Erik avatar
    314 posts
    Member since:
    Feb 2008

    Posted 06 Apr 2011 Link to this post

    Thanks Petko_I for responding,

    I see wat you are doing, but I ran into other issues yesterday as wel, also concerning identity.

    I see your example, but how does that translate to the GetById function? In your example you know the entity object and pass it to the ObjectKey.Create function. But in the GetById (or vb GetSingle) function you do not have a queryable object, but just the type of the entity. When I use your code, it gives me the type of the parameter Key (regarding GetById function) and not the type of the identity. Also the name of the identity is unknown: "UnspecifiedKeyName" (firstCompositeKeyMember.Key in your example)

    I had a simulair problem yesterday, i just want to know the identity field/property name of an object. I end up with 3 loops on the Metadata.PersistentTypes (1 for looping entities and stop if I found the name, 1 for looping it's members and 1 for looping each member to see if it is the identity... because none of the collections support other ways... for ie: Metadata.PersistentTypes("Customers")

    So, to sum up:
    I just want to know, for T what are it's indentity credentials. What prop names and types there are.

    The test snip i now have is like this:
    Public Function GetSingle(Of T)(aint_PKvalue As Integer, Optional abln_ReturnNothingOnError As Boolean = True) As T
        If abln_ReturnNothingOnError Then On Error Resume Next
     
        Dim objectKey As ObjectKey = New ObjectKey(GetType(T).FullName, aint_PKvalue) ' <<< this is the problem I think
        Dim objectKeyValues = objectKey.ObjectKeyValues
        If objectKeyValues.Count = 1 Then
            Dim firstCompositeKeyMember As ObjectKeyMember = objectKeyValues(0)
            Dim firstCompositeKeyMemberType As Type = firstCompositeKeyMember.Value.[GetType]()
            Select Case firstCompositeKeyMemberType.FullName
                Case "System.Int32"
                    Return Me.GetObjectByKey(Of T)(New ObjectKey(GetType(T).FullName, CType(aint_PKvalue, System.Int32)))
                Case "System.Int64"
                    Return Me.GetObjectByKey(Of T)(New ObjectKey(GetType(T).FullName, CType(aint_PKvalue, System.Int64)))
                Case Else
                    Throw New Exception("Function '" & Me.Name & "'.'GetSingle(Of T)(..' encountered an entity identity with an unhandled type '" & firstCompositeKeyMemberType.FullName & "'. (1104061930)")
            End Select
        Else
            Throw New Exception("Function '" & Me.Name & "'.'GetSingle(Of T)(..' can only be used on single field identities. (1104061930)")
        End If
     
    End Function

    We'll get there :-)

    Thanks!



  11. Patrice Boissonneault
    Patrice Boissonneault avatar
    18 posts
    Member since:
    Mar 2010

    Posted 06 Apr 2011 Link to this post

    For multiple-field primary key, we came up with the following function which seems to work:

     

     

    public T GetById<T>(IEnumerable<KeyValuePair<string, object>> key)

     

     

    {

     

        T t;

     

     

     

        this.TryGetObjectByKey<T>(new ObjectKey(typeof(T).FullName, key), out t);

     

     

     

     

        return t;

     

     

    }

    And we call it this way:

    EntityType1 e = Context.GetById<EntityType1>(new Dictionary<string, object>() { { "idfield1", IDValue1 }, { "idfield2", IDValue2 } });

    That seems to work for us.

     

  12. Erik
    Erik avatar
    314 posts
    Member since:
    Feb 2008

    Posted 06 Apr 2011 Link to this post

    Thanks Patrice, that is somewhat simpler indeed, using the TryGetObjectByKey

    Still got the issue int32/int64 though.. (in my case, not a multiple field identity)
  13. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 11 Apr 2011 Link to this post

    Hello Erik,

    You can obtain full information regarding the identity members of a persistent type by using out Metadata API. As far as we understand you have used something similar to this method to obtain the identity members by having the Clr type of an entity:
    Public Function GetIdentityMembers(Of T)() As List(Of MetaPrimitiveMember)
        If Me.Metadata Is Nothing Then
            Return Nothing
        End If
     
        Dim type As Type = GetType(T)
        Dim persistentType As MetaPersistentType = Me.Metadata.PersistentTypes.FirstOrDefault(Function(x) String.Equals(x.Name, type.Name))
        Return MetadataWorker.GetIdentityMembers(persistentType)
    End Function
    This method is expected to be included as an extension in the partial class definition of the OpenAccessContext derived type. The advantage here is that you can use the static method GetIdentityMembers() in our MetadataWorker helper class. The class provides useful methods for working with our Metadata.
    We are, however, a bit confused as to why you would like to check the type of the value you provide in the method GetSingle(). The value provided in the arguments list is Integer (Int32) by definition. You have mentioned in one of your previous posts that you are having issues with a big integer. Can you elaborate more on that matter? The ObjectKeyMember.Key real value as you have correctly noticed is not available when you create a new ObjectKey instance. However, if you just need to evaluate the types of the identity fields you can do so with reflection without knowing the names of the properties behind the identity values. Yet, details concerning the identity members can be obtained with the Metadata API as shown in the example above.

    We are looking forward to resolving the issue.

    Greetings,
    Petko_I
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  14. Erik
    Erik avatar
    314 posts
    Member since:
    Feb 2008

    Posted 12 Apr 2011 Link to this post

    Hi Petko,

    Yes, this function does what you say. I can see the identity column(s) and can see its SQL and ado type. Also I can see the property name for the entity, so I can see the entity identity column property type. Thanks for that.

    But, you are right. Although I need it for a second issue as well, initially it started out for an other reason:
    In the prev function, retrieving a single record, and filling the indentity pk value for function GetObjectByKey resulted in problems. Because I have (always, in this case) single column identity, but the can be of SQL type int or bigint. When passing a vb system.int32 to this function, while the SQL type is a bigint, i got an error. (I used overloads) The other way around as well. That's why I wanted to know the entity or SQL type of the identity.

    I now also need it in some functions that do general handling and need to know what the identity column name is, but that's an other case.

    Thanks for the info Petko.
  15. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 17 Apr 2011 Link to this post

    Hi Erik,

    We are glad we could help. If you still have questions regarding the use of the OpenAccess Metadata API or the ObjectKey, feel free to contact us.

    Best wishes,
    Petko_I
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top
DevCraft banner