AdventureWorks With WillFrame

In this Blog we want to illustrate some of the power of Willframe Framework in a complete example using the Adventure Works Database from Microsoft.

Learn in this page

• Automated Export of database schema into an xml output
• Automated code generation of entities for each table of the database
• Automated web service layer code generation of the new entities
• Example on how to use the generated code.
• Example on how to use the web service.

Downloads

The whole example can be uploaded from this link.

The Database can be downloaded from the Microsoft Pages. We recommand to download the release for SQL Server 2014 or newer.

Use Microsoft Visual Studio 2013 or newer to work with this project.

Components

This Example containts three Libraries

• AdventureWorksLib 

  contains the entity definition and the business logic. the entities are generated and stored in a sub directory named Entities.

• AdventureWorksService 

  contains the interface for fully qualified service for the generated classes.

• AdventureWorksBrowser 

  This project illustrates the usage of the generated code and contains a WinForms Dialog that browses all the contents of the tables using there dependences (foreign constraints).

• build.xml 

  This is a MS Build Script, that automates all the build and generating actions. Additional for each action there is a command line availiable, that can be executed directly.Before you use this script please make sure, that the database connection string in the build skript is configured correct and can access the adventure works database. Edit the file build.xml and modify the following clause:

  <DbConnection>server=.\SQL2014;database=AdventureWorks2014;Trusted_Connection=True</DbConnection>

  1	<DbConnection>server=.\SQL2014;database=AdventureWorks2014;Trusted_Connection=True</DbConnection>

  Following actions and commands can be executed using this build script:

  1. Schema Export (command line build-schemaexport.cmd)This commad executes the command “DbmsCmd.exe”, what can export a database in an xml file. Following command will be executed by calling this action:
     bin\DbmsCmd.exe \ export \ -schema "AdventureWorksLib\Schema\AdventureWorks-schema.xml" \ -cn "server=.\SQL2014;database=AdventureWorks2014;Trusted_Connection=True"

     1 2 3 4

     bin\DbmsCmd.exe \     export \     -schema "AdventureWorksLib\Schema\AdventureWorks-schema.xml" \     -cn "server=.\SQL2014;database=AdventureWorks2014;Trusted_Connection=True"

     The exported database schema definition is stored in the file “AdventureWorksLib\Schema\AdventureWorks-schema.xml”. Please open this file and study its contents. The File contains all components of the database definition (Tables, Columns, Views, Procedures, Triggers, Constraints, Foreign Keys)

  2. Entitiy Generation (command line build-entity.cmd)Now the upove xml file contains alot of information, on how an entity looks like. The command DbmsEntityTool.exe reads this xml file and generates out of it entities for each table in the database.by calling the command line build-entity.cmd the following command line will be executed
     bin\DbmsEntityTool.exe \ -repocmd "C:\Program Files\TortoiseSVN\bin\svn.exe" \ -schemafolder AdventureWorksLib\Schema \ -entityfolder AdventureWorksLib\Entities \ -templatefolder ..\..\DbmsEntityTool\Templates \ -namespace AdventureWorks \ -gen-foreignrelations true \ -gen-subentities true \ -gen-webservice true \ -webservicefolder AdventureWorksService\Entities \ -webservicename AdventureWorksService \ -listsuffix List -entitymanagername AdvEntityManager

     1 2 3 4 5 6 7 8 9 10 11 12 13

     bin\DbmsEntityTool.exe \ -repocmd "C:\Program Files\TortoiseSVN\bin\svn.exe" \ -schemafolder AdventureWorksLib\Schema \ -entityfolder AdventureWorksLib\Entities \ -templatefolder ..\..\DbmsEntityTool\Templates \ -namespace AdventureWorks \ -gen-foreignrelations true \ -gen-subentities true \ -gen-webservice true  \ -webservicefolder AdventureWorksService\Entities \ -webservicename AdventureWorksService \ -listsuffix List -entitymanagername AdvEntityManager

     The tool does not only creates entities, but also there web service derivates. The entities are stored in the folder AdventureWorksLib\Entities. The web service derivates of the entities are stored in the folder AdventureWorksService\Entities. Please open this folders and study the generated classes. all the generated classes are documented and have detailed information about there properties. As example, if you look at the person class, you will figure out, whitch table it reflects and what column each property reflects.

     /// <summary> /// Person / Person - Entity /// </summary> [DataContract] public partial class Person : WebEntity // Derivat aus 'WebEntity' { /// <summary> /// BusinessEntityId / Person.BusinessEntityID /// </summary> /// <returns>int?</returns> [DataMember] public virtual int? BusinessEntityId { get { return (int?)this["BusinessEntityID"]; } set { this.Add("BusinessEntityID", value, typeof(int?)); } } /// <summary> /// Link to foreign Entity Person / Person (this.BusinessEntityId) /// Foreign constraint key FK_Person_BusinessEntity_BusinessEntityID /// </summary> [IgnoreDataMember] public BusinessEntity BusinessEntity { get { // lazy loading if (this._BusinessEntity == null && this.BusinessEntityId != null) { this._BusinessEntity = ((AdvEntityManager)this.Manager).GetBusinessEntity(this.BusinessEntityId); if (! BusinessEntity.ResultInfo.IsValid) { throw new Exception("broken/wrong foreign relation from Person to BusinessEntity."); } } return this._BusinessEntity; } set { // Fremdschlüsselwerte übernehmen this.BusinessEntityId = value.BusinessEntityId; // Fremdtabelle zuweisen. this._BusinessEntity = value; } } private BusinessEntity _BusinessEntity = null; }

     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

     /// <summary>     /// Person / Person - Entity     /// </summary>     [DataContract]     public partial class Person         : WebEntity // Derivat aus 'WebEntity'     {           /// <summary>         /// BusinessEntityId / Person.BusinessEntityID         /// </summary>         /// <returns>int?</returns>         [DataMember]         public virtual int? BusinessEntityId         {             get             {                 return (int?)this["BusinessEntityID"];             }             set             {                 this.Add("BusinessEntityID", value, typeof(int?));             }         }           /// <summary>         /// Link to foreign Entity Person / Person (this.BusinessEntityId)         /// Foreign constraint key FK_Person_BusinessEntity_BusinessEntityID         /// </summary> [IgnoreDataMember]         public BusinessEntity BusinessEntity         {             get             {     // lazy loading                 if (this._BusinessEntity == null && this.BusinessEntityId != null)                 {                     this._BusinessEntity = ((AdvEntityManager)this.Manager).GetBusinessEntity(this.BusinessEntityId);                     if (! BusinessEntity.ResultInfo.IsValid)                     {                         throw new Exception("broken/wrong foreign relation from Person to BusinessEntity.");                     }                 }                 return this._BusinessEntity;             }             set             {                 // Fremdschlüsselwerte übernehmen                 this.BusinessEntityId = value.BusinessEntityId;   // Fremdtabelle zuweisen.                 this._BusinessEntity = value;             }         }         private BusinessEntity _BusinessEntity = null; }

      

  3. Compile Projects Folder (command line build.cmd)Now as we generated the entities and the web service, we can go and compile the whole stuff by executing the command build.cmd.
  4. Done.if no errors happen, we are done.
• Execute AdventureWorksBrowser\bin\Debug\AdventureWorksBrowser.exe and enjoy the example.
• Adventure Web Service 

  To see the produced full qualified web service interface of the adventure works database goto to this link. You can create a new web service client and try to access the service.The following example code changes the middle name of the first person found over the web service.

  // access the adventure works web service // "http://wellcode.de/AdventureWorksService/AdventureWorksService.svc" using (var srv = new AdvSrv.AdventureWorksServiceClient()) { // read the first person var where = new AdvSrv.SqlWhereClause(); where.MaxResult = 1; var persons = srv.GetPersonList(where); if (persons.Count == 0) throw new Exception("No Persons found"); var thePerson = persons.Items[0]; // change middle name of person thePerson.MiddleName = "Changed From Webservice"; // store changes srv.SavePerson(thePerson); }

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  // access the adventure works web service             // "http://wellcode.de/AdventureWorksService/AdventureWorksService.svc"             using (var srv = new AdvSrv.AdventureWorksServiceClient())             {                 // read the first person                 var where = new AdvSrv.SqlWhereClause();                 where.MaxResult = 1;                 var persons = srv.GetPersonList(where);                   if (persons.Count == 0)                     throw new Exception("No Persons found");                   var thePerson = persons.Items[0];                   // change middle name of person                 thePerson.MiddleName = "Changed From Webservice";                   // store changes                 srv.SavePerson(thePerson);             }

  A web service client project is part of the adventure works example solution presented here. The project name is AdventureWorksServiceTest.

• Database Schema Modifications 

  Now add some columns to a table or add a new tables and execute the upove commands build-schemaexport.cmd, build-entity.cmd and build.cmd after each other and you will see that database changes are automatically reflected in the source code.

• Entity Check (command line build-entitycheck.cmd)This amazing command checks if the source code is up to date with the schema definitions. integrating this routine on the head of the compiling routine will prevent developers to continue work until the source is synchron with the database schema definition.
• Notice 

  You are not forced to use the Adventure Works Database. Bind any database you want, you will get the same results with different tables. Try it using your Database.To do so, you must do the following steps:

  • Change the connection string in build.xml to the database you want to reflect
  • Remove all files in AdventureWorksLib\Schema
  • Remove all files in AdventureWorksLib\Entities
  • Remove all files in AdventureWorksService\Entities
  • execute after each other the commands build-schemaexport.cmd, build-entity.cmd
  • Open the projects AdventureWorksLib and AdventureWorksService and remove all files in the project sub folders Entities and readd the new generated files from same directories.
  • Now execute the command line build.cmd. this builds the projects with other database.
• Comming Soon 

  Learn how to automatically upgrade database schema changes on target databases (as example customer databases).