CODEWITHSUNDEEP
c#-4.0stored-proceduressql-server-2008-r2metadatadapper
ElHaix
ElHaix

Reputation: 13006

Using Dapper.Net ORM, how do I cast stored procedure output to a concrete type?

Using Entity Framework I can create concrete classes from most of the sprocs in the database of a project I'm working on. However, some of the sprocs use dynamic SQL and as such no metadata is returned for the sproc.

So for a that sproc, I manually created a concrete class and now want to map the sproc output to this class and return a list of this type.

Using the following method I can get a collection of objects:

                var results = connection.Query<object>("get_buddies", 
                    new {   RecsPerPage = 100,
                            RecCount = 0,
                            PageNumber = 0,
                            OrderBy = "LastestLogin",
                            ProfileID = profileID,
                            ASC = 1}, 
                        commandType: CommandType.StoredProcedure);

My concrete class contains

[DataContractAttribute(IsReference=true)]
[Serializable()]
public partial class LoggedInMember : ComplexObject
{

   /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
    [DataMemberAttribute()]
    public global::System.Int16 RowID
    {
        get
        {
            return _RowID;
        }
        set
        {
            OnRowIDChanging(value);
            ReportPropertyChanging("RowID");
            _RowID = StructuralObject.SetValidValue(value);
            ReportPropertyChanged("RowID");
            OnRowIDChanged();
        }
    }
    private global::System.Int16 _RowID;
    partial void OnRowIDChanging(global::System.Int16 value);
    partial void OnRowIDChanged();

    [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
    [DataMemberAttribute()]
    public global::System.String NickName
    {
        get
        {
            return _NickName;
        }
        set
        {
            OnNickNameChanging(value);
            ReportPropertyChanging("NickName");
            _NickName = StructuralObject.SetValidValue(value, false);
            ReportPropertyChanged("NickName");
            OnNickNameChanged();
        }
    }
    private global::System.String _NickName;
    partial void OnNickNameChanging(global::System.String value);
    partial void OnNickNameChanged();
    .
    .
    .

Without having to iterate through the results and add the output parameters to the LoggedInMember object, how do I map these on the fly so I can return a list of them through a WCF service?

If I try var results = connection.Query<LoggedInMember>("sq_mobile_get_buddies_v35", ... I get the following error:

System.Data.DataException: Error parsing column 0 (RowID=1 - Int64) ---> System.InvalidCastException: Specified cast is not valid. at Deserialize...

Upvotes: 2

Views: 2572

Answers (2)

ElHaix
ElHaix

Reputation: 13006

The solution to this was to create a complex object derived from the sproc with EF:

    public ProfileDetailsByID_Result GetAllProfileDetailsByID(int profileID)
    {
        using (IDbConnection connection = OpenConnection("PrimaryDBConnectionString"))
        {
            try
            {
                var profile = connection.Query<ProfileDetailsByID_Result>("sproc_profile_get_by_id",
                    new { profileid = profileID },
                    commandType: CommandType.StoredProcedure).FirstOrDefault();

                return profile;
            }
            catch (Exception ex)
            {
                ErrorLogging.Instance.Fatal(ex);        // use singleton for logging
                return null;
            }
        }
    }

In this case, ProfileDetailsByID_Result is the object that I manually created using Entity Framework through the Complex Type creation process (right-click on the model diagram, select Add/Complex Type..., or use the Complex Types tree on the RHS).

A WORD OF CAUTION

Because this object's properties are derived from the sproc, EF has no way of knowing if a property is nullable. For any nullable property types, you must manually configure these by selecting the property and setting its it's Nullable property to true.

Upvotes: 0

craigb
craigb

Reputation: 16907

At a guess your SQL column is a bigint (i.e. Int64 a.k.a. long) but your .Net type has a n Int16 property.

You could play around with the conversion and ignore the stored procedure by doing something like:

var results = connection.Query<LoggedInMember>("select cast(9 as smallint) [RowID] ...");

Where you are just selecting the properties and types you want to return your object. (smallint is the SQL equivalent of Int16)

Upvotes: 1

Related Questions