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

1. Leveraging .NET Attributes to build a simple Relational Object Mapping Framework Jason FabritzApplied Information Sciences, Inc. http://www.appliedis.com Applied Information Sciences, Inc

2. Attributes • Describing how to Serialize Data[Serializable][XmlElement] • Security Characteristics[CodeAccessSecurity] • Affect the Just-in-Time (JIT) Compiler[StructLayout][MarshalAs] • Code Interoperability[ComVisible] Applied Information Sciences, Inc

3. Usage Example using System; using System.Xml.Serialization; [Serializable] [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; } … Applied Information Sciences, Inc

4. Rolling Your Own [AttributeUsage( AttributeTargets.Property, AllowMultiple=true )] public class SqlParameterAttribute : Attribute { public SqlParameterAttribute( String name, params String[] mode ) { //… } public bool ReturnsData { get { //… } set { //… } } } Applied Information Sciences, Inc

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.StoredProcedure; command.CommandText = "sp_add_address"; command.Parameters.Add( "@person_id", SqlDbType.Int ).Value = personId; command.Parameters.Add( "@type_id", SqlDbType.Int ).Value = type; command.Parameters.Add( "@street", SqlDbType.NVarChar, 255 ).Value = street; command.Parameters.Add( "@city", SqlDbType.NVarChar, 32 ).Value = city; command.Parameters.Add( "@state", SqlDbType.NVarChar, 120 ).Value = state; command.Parameters.Add( "@zip", SqlDbType.NVarChar, 12 ).Value = zip; return command.ExecuteNonQuery( ); } } } Applied Information Sciences, Inc

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.StoredProcedure; command.CommandText = "sp_add_address"; command.Parameters.Add( "@person_id", SqlDbType.Int ).Value = address.PersonId; command.Parameters.Add( "@type_id", SqlDbType.Int ).Value = address.AddressType; command.Parameters.Add( "@street", SqlDbType.NVarChar, 255 ).Value = address.Street; command.Parameters.Add( "@city", SqlDbType.NVarChar, 32 ).Value = address.City; command.Parameters.Add( "@state", SqlDbType.NVarChar, 120 ).Value = address.State; command.Parameters.Add( "@zip", SqlDbType.NVarChar, 12 ).Value = address.ZipCode; return command.ExecuteNonQuery( ); } } } Applied Information Sciences, Inc

7. Calling a Stored Procedure public int addAddress( Address address ) { using( SqlEngine engine = factory.CreateSqlEngine( ) ) { return engine.Execute( address, "add" ); } } Applied Information Sciences, Inc

8. Calling a Stored Procedure using( SqlEngine engine =factory.CreateSqlEngine( ) ) { engine.Execute( address, "add" ); } Applied Information Sciences, Inc

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( @person_id INT, @address_type_id INT, @street NVARCHAR(255), @city NVARCHAR(32), @state NVARCHAR(120), @zip NVARCHAR(12) )AS BEGIN … Applied Information Sciences, Inc

10. The Big Idea [SqlProcedure("sp_add_address“)] public class Address { [SqlParam("@person_id")] public int PersonId {...}; [SqlParam("@address_type_id")] public int AddressType {...}; [SqlParam("@street")] public String Street {...}; [SqlParam("@city")] public String City {...}; [SqlParam("@state")] public String State {...}; [SqlParam("@zip")] public String ZipCode {...}; } CREATE PROCEDURE dbo.sp_add_address( @person_id INT, @address_type_id INT, @street NVARCHAR(255), @city NVARCHAR(32), @state NVARCHAR(120), @zip NVARCHAR(12) )AS BEGIN … Applied Information Sciences, Inc

11. The Big Idea CREATE PROCEDURE dbo.sp_get_address( @id int ) AS BEGIN SELECT person_id, address_type_id, street, city, state, zip FROM address WHERE id = @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 {...}; } Applied Information Sciences, Inc

12. Mapping Objects [SqlProcedure( "sp_add_address", "add" )] [SqlProcedure( "sp_update_address", "update" )] [SqlProcedure( "sp_delete_address", "delete" )] public class Address { [SqlParameter("@id","update","delete")] [SqlResult( "id", "add", "get" )] public int Id { get; set } [SqlParameter("@person_id","add","update")] [SqlResult("person_id","get")] public int PersonId { get; set; } [SqlParameter("@street","add","update")] [SqlResult("street","get")] public String Street { get; set; } ... Applied Information Sciences, Inc

13. Using Mapped Objects Address address; FindAddress finder = new FindAddress( ) ; finder.AddressId = 100 ; using( SqlEngine engine = factory.CreateSqlEngine( ) ) { address = engine.Execute<Address>( finder, "get" ) ; } … // modify address using( SqlEngine engine = factory.CreateSqlEngine( ) ) { engine.Execute( address, "update" ) ; } Applied Information Sciences, Inc

14. CODE Applied Information Sciences, Inc

15. Resources • MSDNhttp://msdn2.microsoft.com/en-us/library/z0w1kczw(VS.80).aspx • NUnithttp://www.nunit.org/ • Applied Information Sciences, Inc.http://www.appliedis.com Applied Information Sciences, Inc