Jump To:

Using SqlSiphon

back to top...
Using SqlSiphon is very simple. Once you have referenced the SqlSiphon assembly or included the SqlSiphon source file in your project, you must only:
  1. create at least one Entity Class per stored procedure that maps to the result set schemas for your stored procedures, and
  2. create the Access Layer class implementing DataAccessLayer that you will use to execute stored procedures from within your application code

Entity Classes

back to top...
Once you have referenced the assembly in your project, you must create Entity Classes that will map to the row tuples returned by the database for your stored procedure calls.

Example Entity Class

back to top...
In this example, we will assume the existence of a stored procedure named sp_PageColumns_Get that returns rows from a table named PageColumns. For this table, assume that the Ordinal and Width columns are an INT type, the DateCreated is a DATETIME2, and all other columns are NVARCHAR(MAX).
PageName ColumnName RoleToShow RoleToHide Width Ordinal DateCreated CreatedBy
index left guest none 100 1 10/25/2009 dave
index middle guest none 100 2 10/25/2009 mike
index right admin user 100 3 10/25/2009 carl
news left user guest 100 1 10/25/2009 dave
news right user guest 100 2 10/25/2009 dave


Additionally, we will assume the existence of a UserRoles table that, for the sake of simplicity for this example, combines User Names and Security Roles.
UserName RoleName
mike admin
dave guest
carl user
val user
brenda user
lucy admin


The Entity Class may be named anything, but for simplicity's sake, we will name it as the singular-form of the pluralized table name. Each property must be marked as a public property--or else the framework will not be able to reflect over it--and must have both Getter and Setter methods defined--or else the framework can't set the value and you won't be able to read the value. The property getters and setters may be implemented with the default methods, or you may implement them manually as you wish. Additionally, we are not required to provide properties for every column in the returned row--as you can see, there is no matched property for the DateCreated column--only the properties that match the name of the columns will be copied (therefor, the framework will not attempt to do anything with ColumnWidthStr).
public class PageColumn
{
    public string PageName{get;set;}
    public string ColumnName{get;set;}
    public string RoleToShow{get;set;}
    public string RoleToHide{get;set;}
    public int Width{get;set;}

    private int _ordinal;
    public int Ordinal
    {
        get{ return this._ordinal;}
        set{ this._ordinal = value;}
    }

    public string ColumnWidthStr
    {
        get{ return string.Format("{0}%", this.Width}; }
    }
}

Data Access Layer

back to top...
Once you have your Entity Classes, you need to create an Access Layer class that inherits from one of the child classes of the DataAccessLayer class, creating a number of "stub methods" that forward on calls to the mapping system, returning void, single instances, or List<T>s of primitive data types or of your Entity Class types. These stub methods are are used to define the name and parameter names of the stored procedure you wish to execute.

The methods in the Access Layer class need two attributes:
  • [SqlSiphon.MappedMethodAttribute] the MappedMethodAttribute lets the system find the method
  • [System.Runtime.CompilerServices.MethodImplAttribute(System.Runtime.CompilerServices.MethodImplOptions.NoInlining | System.Runtime.CompilerServices.MethodImplOptions.NoOptimization | System.Runtime.CompilerServices.MethodImplOptions.PreserveSig | System.Runtime.CompilerServices.MethodImplOptions.Synchronized)] This unfortunately long snippet prevents the .NET execution environment from performing certain runtime optimization such as inlining the method. If inlining were to occur, it would make it impossible for the system to find the method information and the info it tells us about what stored procedure to call. At a bare minimum, you must use the NoInlining option. The others are optional, but a generally recommended to avoid issues in the future if the behavior of the .NET optimizer ever changes.

Example DAL

back to top...
In this example, we will assume the existence of the following stored procedure in the database
CREATE PROCEDURE sp_PageColumns_Get
	@UserName NVARCHAR(MAX),
	@PageName NVARCHAR(MAX)
AS BEGIN
	SET NOCOUNT ON;
	SELECT DISTINCT
		P.PageName,
		P.ColumnName,
		P.Width,
		P.Ordinal,
		P.DateCreated,
		P.CreatedBy
	FROM PageColumns P INNER JOIN UserRoles R
		ON P.RoleToShow = R.RoleName
		AND P.RoleToHide != R.RoleName
		AND R.UserName = @UserName
	WHERE P.PageName = @PageName
	ORDER BY P.Ordinal ASC
END

The mapped method must have the exact same name as the stored procedure. The parameters to the method must also be the exact same names as the parameters to the stored procedure. Order is not important, but all required parameters must be included. The parameters of the method must be passed to the base method in the same order they are received.
using SqlSiphon;
using System.Runtime.CompilerServices;
public class MyDataAccess : SqlClientDataAccessLayer
{
    public MyDataAccess() : base(ConfigSettings.ConnectionString) { }

    [MappedMethod]
    [MethodImpl(MethodImplOptions.NoInlining | MethodImplOptions.NoOptimization | MethodImplOptions.PreserveSig | MethodImplOptions.Synchronized)]
    public List<PageColumn> sp_PageColumns_Get(string UserName, string PageName)
    {
        return this.GetList<PageColumn>(UserName, PageName);
    }
}

By calling SqlClientDataAccessLayer::GetList, the SqlSiphon framework walks up the Call Stack to find the calling method marked with the SqlSiphon.MappedMethod attribute--in this case, our MyDataAccess::sp_PageColumns_Get method. Using the call frame information, the framework sees that the method is named sp_PageColumns_Get and assumes we are intending to call a stored procedure in our database by the same name. The framework also sees that our method takes parameters named "UserName" and "PageName", and assumes that we are intending to pass equivalently named parameters to the stored procedure. It is vital that you pass the parameters to the GetList method in the same order that they are defined in the method signature, or else the framework will not be able to map the right values to the expected parameters.

Last edited Oct 8, 2012 at 3:24 PM by capn_midnight, version 6

Comments

No comments yet.