Interoperate with an existing database using CodeFluent Entities
As illustrated in previous posts such as this one, using CodeFluent Entities you can create a model from an existing database. Once you have your new model you can either create an entirely new application (from database to UI) or keep on using your existing database and build on it. In this post, we’re going to illustrate the latter.
In this example we downloaded Microsoft’s AdventureWorksLT2008 database available here: http://msftdbprodsamples.codeplex.com/releases/view/37109, which provides a good sample database to start with.
We created a new CodeFluent Entities project named AdventureWorks, and using Visual Studio’s Server Explorer, we dragged and dropped all tables from the SalesLT namespace on our surface (see this post for more info):
This automatically triggers the Import wizard, which we configured as so:
As you can see, the connection string is automatically filled-in as well as your default namespace and target part. A nice option here in our case is to set the “Add Producer To Project” property to “True”. Since we’re going to generate on the same database later on, it’ll spare us the extra clicks to add and configure the SQL Server Producer.
Go next, next, next, and voilà, we have our model:
Note: this picture was created by clicking on the surface and selecting “Export Model as Image” 😉
In such scenarios, a common requirement is that the developer cannot touch the database schema as the database is often used by other applications. To ensure this, we’re going to open the part and set the “updateSchema” attribute on the SQL Server producer configuration to “false”:
<cf:producer name="SQL Server" typeName="CodeFluent.Producers.SqlServer.SqlServerProducer, CodeFluent.Producers.SqlServer">
<cf:configuration (…) updateSchema="false" />
In CodeFluent Entities, optimistic concurrency is enabled by default on all entities and this requires a “_rowVersion” column in all tables. Likewise entity tracking for time and user creations/modifications is enabled and requires extra columns in the database. Since we cannot change the database schema, disable those feature by selected the CodeFluent Entities project and by setting through the Property Grid the “Default Concurrency Mode” to “None” and unchecking the “Time” and “User” options for the “Default Entity Tracking Modes” (in the advanced view of the property grid).
The next step is to add our corresponding Visual Studio projects:
- a Class Library which will contain our generated classes and a simple folder for our persistence scripts,
- as well as a Console Application to test the generated code.
We then need to update the SQL Server producer config by making it point to the appropriate target location (double-click on it from the solution explorer) and add an instance of the Business Object Model producer to generate our C# classes.
In the end here’s our solution:
Building our model will generate all stored procedures so the C# classes, generated as well, can access and manipulate the data stored in our persistence layer. Now let’s use the generated code in our Console App:
CustomerCollection customers = CustomerCollection.LoadAll();
foreach (Customer customer in customers)
Console.WriteLine("Customer: " + customer.FirstName + " " + customer.LastName);
Note: Don’t forget to define the connection string in the application’s App.config file (more info here) so the C# classes know where to connect to at run time 😉
Run the code, and there we go: we now have a .NET application running on our existing database, and without having modified its schema (tables, columns, constraints, etc.!
Here we created a simple console application, but as the generated C# classes can be used across all .NET applications (Windows Forms, ASP.NET, WPF, WCF, Silverlight, etc.), we could in fact build any new .NET application from there.