address = engine.Execute Address ( finder, 'get' ... CODE

1
Leveraging .NET Attributes to build a simple
Relational Object Mapping Framework

• Jason FabritzApplied Information Sciences, Inc.
• http//www.appliedis.com

2
Attributes

• Describing how to Serialize DataSerializableX
  mlElement
• Security CharacteristicsCodeAccessSecurity
• Affect the Just-in-Time (JIT) CompilerStructLayo
  utMarshalAs
• Code InteroperabilityComVisible

3
Usage Example
using System using System.Xml.Serialization Se
rializable XmlRoot( Namespace
"http//www.appliedis.com/address",
IsNullable false, ElementName "Address"
) public class Address XmlAttribute( "id"
) public int Id get set
XmlElement( "Street1" ) public String Street
get set XmlElement( "City" )
public String City get set
4
Rolling Your Own
AttributeUsage( AttributeTargets.Property,
AllowMultipletrue ) public class
SqlParameterAttribute Attribute public
SqlParameterAttribute( String name, params
String mode ) // public
bool ReturnsData get // set
//
5
Calling a Stored Procedure
public int addAddress( int personId, int type,
String street, String city, String state, String
zip ) using( SqlConnection connection new
SqlConnection( connectionString ) )
connection.Open( ) using( SqlCommand
command connection.CreateCommand( ) )
command.CommandType CommandType.StoredPro
cedure command.CommandText
"sp_add_address" command.Parameters.Add(
"_at_person_id", SqlDbType.Int ).Value personId
command.Parameters.Add( "_at_type_id",
SqlDbType.Int ).Value type
command.Parameters.Add( "_at_street",
SqlDbType.NVarChar, 255 ).Value street
command.Parameters.Add( "_at_city",
SqlDbType.NVarChar, 32 ).Value city
command.Parameters.Add( "_at_state",
SqlDbType.NVarChar, 120 ).Value state
command.Parameters.Add( "_at_zip",
SqlDbType.NVarChar, 12 ).Value zip
return command.ExecuteNonQuery( )
6
Calling a Stored Procedure
public int addAddress( Address address )
using( SqlConnection connection new
SqlConnection( connectionString ) )
connection.Open( ) using( SqlCommand
command connection.CreateCommand( ) )
command.CommandType CommandType.StoredPro
cedure command.CommandText
"sp_add_address" command.Parameters.Add(
"_at_person_id", SqlDbType.Int ).Value
address.PersonId command.Parameters.Add(
"_at_type_id", SqlDbType.Int ).Value
address.AddressType command.Parameters.A
dd( "_at_street", SqlDbType.NVarChar, 255 ).Value
address.Street command.Parameters.Add(
"_at_city", SqlDbType.NVarChar, 32 ).Value
address.City command.Parameters.Add(
"_at_state", SqlDbType.NVarChar, 120 ).Value
address.State command.Parameters.Add(
"_at_zip", SqlDbType.NVarChar, 12 ).Value
address.ZipCode return
command.ExecuteNonQuery( )
7
Calling a Stored Procedure
public int addAddress( Address address )
using( SqlEngine engine factory.CreateSqlEngine(
) ) return engine.Execute( address,
"add" )
8
Calling a Stored Procedure
using( SqlEngine engine factory.CreateSqlEngine(
) ) engine.Execute( address, "add" )
9
The Big Idea
public class Address public int PersonId
... public int AddressType ... public
String Street ... public String City
... public String State ... public
String ZipCode ...
CREATE PROCEDURE dbo.sp_add_address (
_at_person_id INT, _at_address_type_id INT,
_at_street NVARCHAR(255), _at_city NVARCHAR(32),
_at_state NVARCHAR(120), _at_zip NVARCHAR(12) )
AS BEGIN
10
The Big Idea
SqlProcedure("sp_add_address) public class
Address SqlParam("_at_person_id") public
int PersonId ... SqlParam("_at_address_type_id
") public int AddressType ...
SqlParam("_at_street") public String Street
... SqlParam("_at_city") public String
City ... SqlParam("_at_state") public
String State ... SqlParam("_at_zip")
public String ZipCode ...
CREATE PROCEDURE dbo.sp_add_address (
_at_person_id INT, _at_address_type_id INT,
_at_street NVARCHAR(255), _at_city NVARCHAR(32),
_at_state NVARCHAR(120), _at_zip NVARCHAR(12) )
AS BEGIN
11
The Big Idea
CREATE PROCEDURE dbo.sp_get_address( _at_id
int ) AS BEGIN SELECT person_id,
address_type_id, street,
city, state, zip FROM
address WHERE id _at_id END
public class Address SqlResult("person_id")
public int PersonId ...
SqlResult("address_type_id") public int
AddressType ... SqlResult("street")
public String Street ... SqlResult("city")
public String City ...
SqlResult("state") public String State
... SqlResult("zip") public String
ZipCode ...
12
Mapping Objects
SqlProcedure( "sp_add_address", "add"
) SqlProcedure( "sp_update_address", "update"
) SqlProcedure( "sp_delete_address", "delete"
) public class Address SqlParameter("_at_id","
update","delete") SqlResult( "id", "add",
"get" ) public int Id get set
SqlParameter("_at_person_id","add","update")
SqlResult("person_id","get") public int
PersonId get set SqlParameter("_at_street"
,"add","update") SqlResult("street","get")
public String Street get set ...
13
Using Mapped Objects
Address address FindAddress finder new
FindAddress( ) finder.AddressId 100 using(
SqlEngine engine factory.CreateSqlEngine( )
) address engine.ExecuteltAddressgt( finder,
"get" ) // modify address using(
SqlEngine engine factory.CreateSqlEngine( )
) engine.Execute( address, "update" )
14
CODE
15
Resources

• MSDNhttp//msdn2.microsoft.com/en-us/library/z0w1
  kczw(VS.80).aspx
• NUnithttp//www.nunit.org/
• Applied Information Sciences, Inc.http//www.appl
  iedis.com