Jump To:

Entity Classes

back to top...
A few methods in the DataAccessLayer class take a generic type parameter T. This type is called the Entity Class type, and is the data type to which data from your database gets mapped for further processing within your application. There are three types of Entity Classes to which data can be mapped: primitive .NET data types, plain-old .NET objects with public properties, and custom data objects that implement a specific constructor for processing SqlDataReader objects.

At all times, it is necessary to know what the type of data is that the database returns. SqlSiphon does not do any sort of data translation. If the database type does not directly cast to your provided data type, an InvalidCastException will occur. Thankfully, the exception will provide you information on how to correct the issue.

The following examples assume the following Users table:
Field T-SQL Type Is Nullable
UserID INT false
UserName NVARCHAR(255) false
DateCreated DATETIME2 false
DateDeleted DATETIME2 true


And the following stored procedure:
create procedure GetUsers as begin
    set nocount on;
    select 
        UserID,
        UserName,
        DateCreated,
        DateDeleted,
        cast(case when DateDeleted is null then 1 else 0 end as bit) as IsActive
    from Users;
end

Primitive Types

back to top...
In cases where a single column of data is to be returned from the database (either a single row or multiple rows), that data can be mapped to any of the .NET System types that map directly to the value types returned by the database: e.g. System.Int32 or System.String. These data types are the "primitive" data types of the .NET framework and have equivalents defined for each T-SQL data type.

To determine "primitiveness" of a data type, SqlSiphon reflects on the type provided, checking the IsPimitive property of that type. Additionally, System.Decimal, System.String, System.DateTime, and System.Guid are treated as primitive data types.

When using a primitive data type as the mapped type, you must also provide the column name or column index in the result from which to map data. The mapping can work with result sets that return more than one column, if one is only interested in one column.

Example:
To select only the UserID column from the result set, the the following mapped method (a method in a sub-class of the DataAccessLayer marked with the MappedMethodAttribute) is needed:
[MappedMethod(ProcedureName="GetUsers")]
[MethodImpl(MethodImplOptions.NoInlining | MethodImplOptions.NoOptimization | MethodImplOptions.PreserveSig | MethodImplOptions.Synchronized)]
public List<int> GetUserIDs()
{
    return this.GetList<int>("UserID"); // integer index 0 may also be used
}

A similar method for selecting only the UserName field would appear as:
[MappedMethod(ProcedureName="GetUsers")]
[MethodImpl(MethodImplOptions.NoInlining | MethodImplOptions.NoOptimization | MethodImplOptions.PreserveSig | MethodImplOptions.Synchronized)]
public List<string> GetUserNames()
{
    return this.GetList<string>("UserName"); // integer index 1 may also be used
}

This method may also be used with text queries not mapping to any database table:
[MappedMethod(CommandType=CommandType.Text, Query="SELECT @@IDENTITY")]
[MethodImpl(MethodImplOptions.NoInlining | MethodImplOptions.NoOptimization | MethodImplOptions.PreserveSig | MethodImplOptions.Synchronized)]
public int GetLastIdentity()
{
    return this.Get<int>(0);
}

Plain Old .NET Objects

back to top...
Result sets can be mapped to classes that you create that package up all of the columns into a conveniently named class with strongly-typed properties. This is handy for rapid development in environments that use code completion and runtime inspection of type information.

Example:
With the table and procedure assumed at the top of this article, the following method would return all users from the database:
public class User
{
    // any fields that do not have matching properties will not get copied
    // public int UserID {get;set;}

    // implementing as properties gives us the opportunity to modify values as they are set
    private string _userName;
    public string UserName
    {
         get{ return this._userName; }
         set{ this._userName = value.ToUpper(); }
    }

    public DateTime DateCreated {get;set;}
    // use T? or Nullable<T> for fields that are nullable of a .NET type that is not nullable
    public DateTime? DateDeleted {get;set;}
    public bool IsActive {get;set;}

    // any properties that do not match fields in the table will have nothing done to them
    public bool IsDeleted
    {
        get{ return this.DateDeleted.HasValue; }
    }

    // any number of methods may also be included, they have no effect on the mapping.
    public override int GetHashCode()
    {
        return this.UserName.GetHashCode();
    }

    public override string ToString()
    {
        return this.UserName;
    }
}

public MyDataAccessLayer : SqlClientDataAccessLayer
{
    public MyDataAccessLayer() : base("Data Source=myServerAddress;Initial Catalog=myDatabase;Integrated Security=True")
    {
    }
    [MappedMethod]
    [MethodImpl(MethodImplOptions.NoInlining | MethodImplOptions.NoOptimization | MethodImplOptions.PreserveSig | MethodImplOptions.Synchronized)]
    public List<User> GetUsers()
    {
        return this.GetList<User>();
    }
}

public class Program
{
    public static void Main(string[] args)
    {
         using(var dal = new MyDataAccessLayer())
         {
              List<User> users = dal.GetUsers();
              foreach(var user in users)
              {
                  if(user.IsDeleted)
                  {
                      Console.WriteLine("{0} deleted on {1}", user.UserName, user.DateDeleted.Value);
                  }
             }
         }
     }
}

The SqlSiphon stack trace analyzer will determine the stored procedure to execute and the automatic result set mapper will create a System.Collections.Generic.List<T> for the type User, filling it with a User object per row of the database, copying the field values from the result set to any public fields or properties that match the names of the columns in the result set.

Manually Mapped Objects

back to top...
Finally, if you require complete control over how a row from the database is mapped to your objects, one may directly manage the mapping process by implementing the Entity<T> abstract class.

Example:
With the table and procedure assumed at the top of this article, the following method would return all users from the database:
public class User : Entity<SqlDataReader>
{
    private string userName;
    private bool isDeleted;

    public User(SqlDataReader reader)
    {
        userName = (string)reader["UserName"];
        isDeleted = reader["DateDeleted"] != DBNull.Value;
    }

    public override string ToString()
    {
        return string.Format("{0}{1}", this.isDeleted? "<DELETED> " : "", this.userName);
    }
}

// see previous example for Data Access Layer as they are equivalent

public class Program
{
    public static void Main(string[] args)
    {
        using(var dal = new MyDataAccessLayer())
        {
            List<User> users = dal.GetUsers();
            foreach(var user in users)
            {
                Console.WriteLine(user);
            }
        }
    }
}

Last edited Oct 8, 2012 at 3:07 PM by capn_midnight, version 11

Comments

No comments yet.