{"id":147,"date":"2015-05-07T18:00:23","date_gmt":"2015-05-07T18:00:23","guid":{"rendered":"http:\/\/www.wellcode.de\/home\/?page_id=147"},"modified":"2021-11-05T01:25:48","modified_gmt":"2021-11-05T01:25:48","slug":"adventureworks-with-willframe","status":"publish","type":"page","link":"https:\/\/wellcode.de\/home\/index.php\/en\/adventureworks-with-willframe\/","title":{"rendered":"AdventureWorks With WillFrame"},"content":{"rendered":"\n<p>In this Blog we want to illustrate some of&nbsp;the&nbsp;power of Willframe Framework in&nbsp;a complete example using <em>the Adventure Works Database from Microsoft<\/em>.<\/p>\n\n\n\n<p><strong>Learn in this page<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Automated Export of database schema into an xml output<\/li><li>Automated code generation of entities for each table of the database<\/li><li>Automated web service layer code generation of the new entities<\/li><li>Example on how to use the generated code.<\/li><li>Example on how to use the web service.<\/li><\/ul>\n\n\n\n<p><strong>Downloads<\/strong><\/p>\n\n\n\n<p>The whole example can be uploaded from <a href=\"\/downloads\/AdventureWorksWithWillFrame.zip\" target=\"_blank\" rel=\"noopener\">this link<\/a>.<\/p>\n\n\n\n<p>The Database can be downloaded from <a href=\"http:\/\/msftdbprodsamples.codeplex.com\/\" target=\"_blank\" rel=\"noopener\">the Microsoft Pages<\/a>. We recommand to download the release for SQL Server 2014 or newer.<\/p>\n\n\n\n<p>Use Microsoft Visual Studio 2013 or newer to work with&nbsp;this project.<\/p>\n\n\n\n<p><strong>Components<\/strong><\/p>\n\n\n\n<p>This Example&nbsp;containts three Libraries<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong><strong>AdventureWorksLib<\/strong><\/strong>&nbsp;\n<p>contains the entity definition and the business logic. the entities are generated and stored in a sub directory named <em>Entities<\/em>.<\/p>\n<\/li><li><strong><strong>AdventureWorksService<\/strong><\/strong>&nbsp;\n<p>contains the interface for fully qualified service&nbsp;for the generated classes.<\/p>\n<\/li><li><strong><strong>AdventureWorksBrowser<\/strong><\/strong>&nbsp;\n<p>This project illustrates the usage of the generated code and contains a WinForms&nbsp;Dialog that browses all the contents of the tables using there dependences (foreign constraints).<\/p>\n<\/li><li><strong><strong>build.xml<\/strong><\/strong>&nbsp;\n<p>This is a&nbsp;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 <em>build.xml<\/em> and modify the following clause:<\/p>\n<pre class=\"lang:default decode:true\">&lt;DbConnection&gt;server=.\\SQL2014;database=AdventureWorks2014;Trusted_Connection=True&lt;\/DbConnection&gt;<\/pre>\n<p>Following actions and commands&nbsp;can be executed using this build script:<\/p>\n<ol>\n<li><strong>Schema Export<\/strong> (command line <em>build-schemaexport.cmd<\/em>)This commad executes the command &#8220;DbmsCmd.exe&#8221;, what can export a database&nbsp;in an xml file. Following command will be executed by calling this action:\n<pre class=\"lang:default decode:true\">bin\\DbmsCmd.exe \\\n    export \\\n    -schema \"AdventureWorksLib\\Schema\\AdventureWorks-schema.xml\" \\\n    -cn \"server=.\\SQL2014;database=AdventureWorks2014;Trusted_Connection=True\"<\/pre>\n<p>The exported database schema definition is stored in the file&nbsp;<em>&#8220;AdventureWorksLib\\Schema\\AdventureWorks-schema.xml&#8221;.<\/em>&nbsp;Please&nbsp;open this file and study its&nbsp;contents. The File contains all components of the database definition (<em>Tables, Columns, Views, Procedures, Triggers, Constraints, Foreign Keys<\/em>)<\/p>\n<\/li>\n<li><strong>Entitiy Generation<\/strong> (command line <em>build-entity.cmd<\/em>)Now the upove xml file contains alot of information, on how an entity looks like. The command&nbsp;<em>DbmsEntityTool.exe<\/em>&nbsp;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\n<pre class=\"lang:default decode:true\">bin\\DbmsEntityTool.exe \\\n\t-repocmd \"C:\\Program Files\\TortoiseSVN\\bin\\svn.exe\" \\\n\t-schemafolder AdventureWorksLib\\Schema \\\n\t-entityfolder AdventureWorksLib\\Entities \\\n\t-templatefolder ..\\..\\DbmsEntityTool\\Templates \\\n\t-namespace AdventureWorks \\\n\t-gen-foreignrelations true \\\n\t-gen-subentities true \\\n\t-gen-webservice true  \\\n\t-webservicefolder AdventureWorksService\\Entities \\\n\t-webservicename AdventureWorksService \\\n\t-listsuffix List\n\t-entitymanagername AdvEntityManager<\/pre>\n<p>The tool does not only creates entities, but also there web service derivates. The entities are stored in the folder&nbsp;<em>AdventureWorksLib\\Entities<\/em>. The web service derivates of the entities are stored in the folder&nbsp;<em>AdventureWorksService\\Entities<\/em>. Please&nbsp;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.<\/p>\n<pre class=\"lang:default decode:true\">\/\/\/ &lt;summary&gt;\n    \/\/\/ Person \/ Person - Entity \n    \/\/\/ &lt;\/summary&gt;\n    [DataContract]\n    public partial class Person\n        : WebEntity \/\/ Derivat aus 'WebEntity' \n    {\n\n        \/\/\/ &lt;summary&gt;\n        \/\/\/ BusinessEntityId \/ Person.BusinessEntityID\n        \/\/\/ &lt;\/summary&gt;\n        \/\/\/ &lt;returns&gt;int?&lt;\/returns&gt;\n        [DataMember]\n        public virtual int? BusinessEntityId\n        {\n            get\n            {\n                return (int?)this[\"BusinessEntityID\"];\n            }\n            set\n            {\n                this.Add(\"BusinessEntityID\", value, typeof(int?));\n            }\n        }\n\n        \/\/\/ &lt;summary&gt;\n        \/\/\/ Link to foreign Entity Person \/ Person (this.BusinessEntityId)\n        \/\/\/ Foreign constraint key FK_Person_BusinessEntity_BusinessEntityID\n        \/\/\/ &lt;\/summary&gt;\n\t\t[IgnoreDataMember]\n        public BusinessEntity BusinessEntity\n        {\n            get\n            {\n\t\t\t    \/\/ lazy loading\n                if (this._BusinessEntity == null &amp;&amp; this.BusinessEntityId != null)\n                {\n                    this._BusinessEntity = ((AdvEntityManager)this.Manager).GetBusinessEntity(this.BusinessEntityId);\n                    if (! BusinessEntity.ResultInfo.IsValid)\n                    {\n                        throw new Exception(\"broken\/wrong foreign relation from Person to BusinessEntity.\");\n                    }\n                }\n                return this._BusinessEntity;\n            }\n            set\n            {\n                \/\/ Fremdschl\u00fcsselwerte \u00fcbernehmen\n                this.BusinessEntityId = value.BusinessEntityId;\n\n\t\t\t\t\/\/ Fremdtabelle zuweisen.\n                this._BusinessEntity = value;\n            }\n        }\n        private BusinessEntity _BusinessEntity = null;\n}<\/pre>\n<p>&nbsp;<\/p>\n<\/li>\n<li><strong>Compile Projects&nbsp;Folder<\/strong> (command line <em>build.cmd<\/em>)Now as we generated the entities and the web service, we can go and compile the whole stuff by executing the command <em>build.cmd<\/em>.<\/li>\n<li><strong>Done<\/strong>.if no errors happen, we are done.<\/li>\n<\/ol>\n<\/li><li>Execute&nbsp;<em>AdventureWorksBrowser\\bin\\Debug\\AdventureWorksBrowser.exe<\/em> and enjoy the example.<a href=\"\/home\/wp-content\/uploads\/AdventureWorksBrowser.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-159\" src=\"\/home\/wp-content\/uploads\/AdventureWorksBrowser.png\" alt=\"AdventureWorksBrowser\" width=\"817\" height=\"520\"><\/a><\/li><li><strong><strong>Adventure Web Service<\/strong><\/strong>&nbsp;\n<p>To see the produced full qualified web service interface of the adventure works database goto to <a href=\"\/AdventureWorksService\/AdventureWorksService.svc\" target=\"_blank\" rel=\"noopener\">this link<\/a>. 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.<\/p>\n<pre class=\"lang:default decode:true \">            \/\/ access the adventure works web service \n            \/\/ \"http:\/\/wellcode.de\/AdventureWorksService\/AdventureWorksService.svc\"\n            using (var srv = new AdvSrv.AdventureWorksServiceClient())\n            {\n                \/\/ read the first person\n                var where = new AdvSrv.SqlWhereClause();\n                where.MaxResult = 1;\n                var persons = srv.GetPersonList(where);\n\n                if (persons.Count == 0)\n                    throw new Exception(\"No Persons found\");\n\n                var thePerson = persons.Items[0];\n\n                \/\/ change middle name of person\n                thePerson.MiddleName = \"Changed From Webservice\";\n\n                \/\/ store changes\n                srv.SavePerson(thePerson);\n            }<\/pre>\n<p>A web service client project is part of <em>the adventure works example solution<\/em> presented here. The project name is <em>AdventureWorksServiceTest<\/em>.<\/p>\n<\/li><li><strong><strong>Database Schema Modifications<\/strong><\/strong>&nbsp;\n<p>Now add&nbsp;some columns to&nbsp;a table or add a new tables and execute the upove commands <em>build-schemaexport.cmd<\/em>, <em>build-entity.cmd<\/em> and <em>build.cmd<\/em> after each other and you will see that database changes are automatically reflected in the&nbsp;source code.<\/p>\n<\/li><li><strong>Entity Check<\/strong> (command line <em>build-entitycheck.cmd<\/em>)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.<\/li><li><strong><strong>Notice<\/strong><\/strong>&nbsp;\n<p>You are not forced to use <em>the Adventure Works Database<\/em>. Bind any database you want, you will get the same results with different tables. <strong>Try it using your Database<\/strong>.To do so, you must do the following steps:<\/p>\n<ul>\n<li>Change the connection string in build.xml to the database you want to reflect<\/li>\n<li>Remove all files in <em>AdventureWorksLib\\Schema<\/em><\/li>\n<li>Remove all files in <em>AdventureWorksLib\\Entities<\/em><\/li>\n<li>Remove all files in <em>AdventureWorksService\\Entities<\/em><\/li>\n<li>execute after each other the commands <em>build-schemaexport.cmd<\/em>, <em>build-entity.cmd<\/em><\/li>\n<li>Open the projects <em>AdventureWorksLib <\/em>and <em>AdventureWorksService <\/em> and remove all files in the project sub folders <em>Entities<\/em> and readd the new generated files from same directories.<\/li>\n<li>Now execute the command line <em>build.cmd<\/em>. this builds the projects with other database.<\/li>\n<\/ul>\n<\/li><li><strong><strong>Comming Soon<\/strong><\/strong>&nbsp;\n<p>Learn how to automatically&nbsp;upgrade database schema changes on target databases (as example customer databases).<\/p>\n<\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In this Blog we want to illustrate some of&nbsp;the&nbsp;power of Willframe Framework in&nbsp;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 &hellip; <a href=\"https:\/\/wellcode.de\/home\/index.php\/en\/adventureworks-with-willframe\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">AdventureWorks With WillFrame<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"class_list":["post-147","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/pages\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":31,"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/pages\/147\/revisions"}],"predecessor-version":[{"id":1685,"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/pages\/147\/revisions\/1685"}],"wp:attachment":[{"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/media?parent=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}