Archive

Archive for the ‘SQLAzure’ Category

Create Windows Azure Mobile Services in a few minutes thanks to CodeFluent Entities

April 5, 2013 Leave a comment

You may have heard that a lot of new features were added to Windows Azure during the last few months.

Today we will focus on Windows Azure Mobile Services. First of all, what is it?

Long story short, Windows Azure Mobile Services allows you to expose your data through JSON web services. Microsoft provides SDKs for Windows Store and Windows Phone 8 apps, pre-configured projects for iOS and Android, and JavaScript libraries for HTML.

The SDKs offer a large number of methods that provide access to your data stored in Azure.

So let us create a new CodeFluent Entities project. As an example for this article, we will use the “advertising sample” model. I will also add a class library project which will contain our SQL scripts.

We will not need more for this article since we are just going to generate the SQL Azure database with CodeFluent Entities.

First, we will add the SQL Azure Producer to our project. Regarding the configuration of the producer, we will only set the property “Produce Scripts” of the SQL Azure section to “true”.

1

Also, and this is really important, we will set the “Produce Schema” property to “true”. This property can be found by clicking on the “Advanced Properties” button as shown in the following screenshot.

2

You may wonder why this is so important. It is simply because Windows Azure Mobile Services will use the name we are going to define for our service to map it with the schema name of our database tables. So choose it carefully!

In this example, we are going to name our schema “sampleadvertising”. To do so, we have to open the properties of our entities and fill in the schema property as shown below.

3

Once we have updated the “Schema name” property of each entity we want to expose, we can build our project to produce our database.

When the project has been produced, check the database in SQL Azure to make sure we have our custom schema name on our tables.

4

Let us go back on Windows Azure to create our mobile service. According to the schema name we used in this example, we will name it “sampleadvertising” and we will associate it with the database created earlier.

5

6

Now that our service has been created, click on the “Data” tab of our service to “add a table”. Here we will add tables which names are identical to our database tables; for instance, “Campaign”.

7

8

Note: for the purpose of this article we will set the authorization for all CRUD methods to “Everyone”. Do not do the same on production especially if you work with sensitive data.

Those tables are not new database tables, they are only created to associate the tables created thanks to CodeFluent Entities with our mobile service and therefore expose our database data.

Once the table is added, we can access our data from everywhere in a standardized format.

9

From now on, we will show how to use our data from a sample Windows Store application.

First of all, we need to install the Windows Azure Mobile Services SDK which can be found at this address: https://go.microsoft.com/fwLink/?LinkID=257545&clcid=0x40C

Once the SDK is installed, let us add a new project to our solution, a blank C# Windows Store app, named “SampleAdvertising.App”.

Then add a reference to the SDK we have previously installed.

10

Let us open the file “App.xaml.cs” to register our mobile service. In our case, we will add the following code:

    <br />public static MobileServiceClient MobileService = new MobileServiceClient(&quot;https://sampleadvertising.azure-mobile.net/&quot;);     <br />

In this particular example, we will not need to register the application key since we have set the permissions to “everyone”.

Now that our service is registered, I will show you how to consume our database data.

For instance, we will query our table “Customer” to get the customer “SoftDia”. To do so, we will create an async method that will get our “Customer” table. Then we will use the “ReadAsync” method with a query filter based on our “Customer_Name” column.

Finally, we will get a JsonObject from this IJsonValue and display its content in our Visual Studio output.

Here is the code used:

    <br />GetCustomerByName(&quot;SoftDia&quot;);</p>  <p>private async void GetCustomerByName(string customerName)</p>  <p>{</p>  <p>IMobileServiceTable customerTable = App.MobileService.GetTable(&quot;Customer&quot;);</p>  <p>IJsonValue myCustomer = await customerTable.ReadAsync(&quot;$filter=(Customer_Name eq '&quot; + customerName + &quot;')&quot;);</p>  <p>JsonObject myCustomerObject = myCustomer.GetArray()[0].GetObject();</p>  <p>foreach (var item in myCustomerObject)</p>  <p>{</p>  <p>Debug.WriteLine(item.Key.ToString() + &quot;: &quot; + item.Value.Stringify());</p>  <p>}</p>  <p>}    <br />

And this is what we get from our “Customer” table

11

Cheers,

Aymeric ROLAND

Using the SQL Server Template Producer to generate Clustered Indexes

November 27, 2012 Leave a comment

In this post we’ll see how using CodeFluent Entities’ SQL Server Template Producer, you can generate SQL scripts using  the built-in template engine whilst accessing the inferred meta-model, and then automatically deploy the generated script on your desired server.

By default SQL Azure and SQL Server add the clustered index on the primary key if a clustered index on the table does not already exist and if you do not specify a unique nonclustered index. In the case where the primary key is of GUID type it won’t be efficient. The reason for this is that GUIDs are generated in non-sequential order and SQL Server orders a clustered index sequentially. It will work – SQL Server will let you build a clustered index around a unique identifier column, however it will cause the SQL Server to do unnecessary work and cause performance slowdowns.

That being said, what we can do is using the SQL Template Producer to remove all inefficient clustered indexes and recreate them on the right columns. Let’s even go a bit further and create a little aspect that will add a property on each property to tell if a clustered index needs to be created or not on that particular property.

Add a new Part called IsClusteredIndexAspect and past it the following code (replacing the defaultNamespace’s value by yours):

<cf:project xmlns:cf=”http://www.softfluent.com/codefluent/2005/1” defaultNamespace=”yourNamespace”>

<cf:pattern name=”IsClusteredIndex Aspect” namespaceUri=”http://www.sample.com/aspects/isclusteredindexaspect/2012/11” preferredPrefix=”sa” step=”Tables”>

<cf:message class=”_doc”>
This aspect creates an extra IsClusteredIndex bool property on every property.
</cf:message>

<cf:descriptor name=”IsClusteredIndex” typeName=”boolean” targets=”Property” defaultValue=”false” displayName=”IsClusteredIndex” description=”Should the IsClusteredIndex Aspect apply to this property?” />

</cf:pattern>
</cf:project>

This will create our aspect and add a IsClusteredIndex property on each property in the “Aspects and Producers” property grid:

image

You can by now choose which property you want to use as a clustered index. Obviously this property should be set to true only on one property by entity since clustered index cannot be applied on several columns.

Now let’s write a script that will remove all clustered indexes and then create new ones based on the columns selected thanks to our aspect. In a file called “[Template]CreateIndexes.sql” add it the following code (Note that this code is only an illustration for this post, it does not take into account constraints, primary keys and so on):

[%@ namespace name="CodeFluent.Model"%]
[%@ namespace name="CodeFluent.Model.Persistence"%]
[%@ namespace name="CodeFluent.Producers.SqlServer"%]
/* [%=Producer.GetSignature()%] */

[%foreach (Entity e in Producer.Project.Entities)
{%]
    --remove any existing clustered index on e.Table.FullName
    --[...]
  
   [%foreach (Property p in e.Properties)
    {
        if (p.GetAttributeValue("sa:IsClusteredIndex", false))
        {%]      
        --create your index such as:
        CREATE CLUSTERED INDEX [CL_[%=p.Entity.Table.Name%]] ON [%=p.Entity.Table.FullName%] ([[%=p.Column.Name%]]);
        GO
        [%}
    }
}%]

 

Create a folder called “Template” under the file folder of your CodeFluent Entities project:

image

Right click on that folder and choose “Add existing item” then browse to your template file and select it.

Now in your CodeFluent Entities project, add an instance of the SQL Server Template Producer with your Template directory as the “Source Directory” and set the “Target Directory” to the project of you choice and then build over your model. The SQL Server Template Producer will generate a script file from your template, and run the script on the server removing and creating clustered indexes. Therefore using the template producer you can quickly create complex scripts by taking advantage of the meta model.

 

Cheers,

Thibault NESTOR

SQL Azure producer now has its “Diff engine”!

October 30, 2012 1 comment

CodeFluent Entities provides an out-of-the-box SQL Azure producer. Using this producer you can generate SQL Azure scripts and automatically run them on your SQL Azure database.

Although this producer enables you to work on a local SQL Server instance in order to benefit of the producer’s Diff Engine on the local instance, it was missing its own Diff Engine! Indeed, for each generation the producer was dropping tables before recreating them.

This new build 686 brings to the SQL Azure Producer its own Diff Engine. It means that as of today, either using SQL Azure or SQL Server producer, you can continuously generate without loosing data.

The SQL Azure producer’s property grid as been reorganized so the distinction between Local SQL server and Online SQL Azure properties is clearer. Besides, you’ll find a new property under the Online SQL Azure category called “Create Diffs” to enable or disable the SQL Azure Diff Engine. The Diff Engine is enable by default.

 

azure

 

Cheers,

Thibault Nestor

Goodbye SQL-DMO!

September 10, 2012 1 comment

Starting with build 671, SQL-DMO is not longer a requirement for the CodeFluent Entities SQL Server Producer. That’s good news!

We have been relying on SQL-DMO for almost 7 years, and it has served us well. It has been marked as obsolete for something like 5 years or so, but the producer was still relying on it. One of the reasons we kept it for so long was to maintain  compatibility with SQL Server 2000. Now, we don’t support that database either (june 2012).

We have replaced SQL-DMO by a similar technology without any dependency on the newer SQL-SMO (replacement for SQL-DMO) either. This database abstraction technology - our own SMO-like layer – works not only for SQL Server but also for all our supported importers and producers supported databases and modeling systems, namely: SQL Server, Oracle, PostgreSQL, MySQL, SqlLite, Xmi, Enterprise Architect, Access and SqlServerCe. It’s mainly located in the CodeFluent.Runtime.Database.dll assembly that ships with the product and in satellite ones for specific targeted systems (CodeFluent.Runtime.PostgreSQL.dll for PostgreSQL, CodeFluent.Runtime.MySQL.dll for MySQL, etc.).

Cheers,

The R&D team.

Quickly Generate SQL Azure Databases with CodeFluent Entities

August 14, 2012 1 comment

CodeFluent Entities ships out-of-the-box with an SQL Azure producer which allows you to generate databases to SQL Azure directly from Visual Studio, based on a CodeFluent Entities model. We’ll see in this post how we can actually do it.

First of all, we have to setup our Azure environment, so we’ll start by login on www.windowsazure.com with the LiveID associated with an Azure subscription. Once we’re logged on, we’ll go directly to the Database section of the portal and we’ll begin by creating a new database server as shown here:

Then you’ll have to choose a region to define where your server will be located. In my case it will be “North Europe”. Once you have chosen a region, you’ll have to provide credentials for the server as shown below:

The next step is very important here, as you’ll have to configure the firewall of your server to allow some IP ranges. The thing is, if you don’t allow your own IP address, you won’t be able to manage the database or generate it with the CodeFluent Entities SQL Azure producer, from the Visual Studio environment:

Once you’ve setup your firewall rule(s), click on finish to validate the creation of you server.

The server is now created, so click on it and you should see that a database has been created by default, the master database. But we’re not able to produce anything on this one, so we’ll have to create a new one. To do that, click on “Create” on the top on the database part of the ribbon, then give a name to your database and select its edition and size. In my case, I chose a 1GB Web edition which I named “MyDatabase”.

Azure Database configuration is now done so let’s open Visual Studio to see how to generate the persistence layer of our application directly on the database we’ve just created. For the purpose of this article I’ll use a sample project provided out-of-the-box with CodeFluent Entities, namely the “Advertising Sales Management Sample Model”. If you open this model, you’ll see it contains 7 entities, 4 enumerations, and different relations (e.g. one-to-one, one-to-many, many-to-many):

Once this project is created, I’ll add a new “SQL Server Database”-type project to my solution, which will be the target project for the CodeFluent Entities SQL Azure producer. I’ll name this project “SoftFluentArticle.Database” as shown here:

Once that’s done, let’s add a new producer to the CodeFluent Entities project, the ‘SQL Azure producer’. It can be found in the “Persistence Layer Producers” category as shown here:

As you can see, if you never used it, it’s pretty similar to the regular CodeFluent Entities SQL Server producer, but you will notice there are are a “Local SQL Server” and an “Online SQL Azure” sections. Moreover, in the “Targets” section, the property “Produce Online SQL Azure” is set to false and the property “Produce Local SQL Server” is set to true.

The reasons are multiple. The most important is simply… the cost for you :-) Because each time your application requests data from your SQL Azure, there is a cost applied to the transaction and you certainly don’t want to pay fees for these transactions while you’re developing/testing your application. That’s why you will use the Local SQL Server most of the time, and switch to the Online SQL Azure when you’re ready. It’s important to note the generated code will always target SQL Azure, even if the local SQL Server is in use, thus avoiding pure SQL Server statements unsupported on SQL Azure.

We now have to specify our SQL Azure connection string, just like a regular SQL Server connection string. To do that just click on the “Connection String” property line, a button will be shown at the extreme right of the line, click on it and you should get the same popup as the one shown below. Fill it with your Database Name, Server Name, Password, and User name. You can now click on test connection to check it works.

Back to our producer configuration, choose the database project we’ve created earlier as the “Default Target Directory” (you can use a sub folder if you want, or the root as it’s shown here):

Click on “Ok” to confirm all that.

Everything is now setup, so let’s build our project and see what happens! As for the SQL Server producer, the SQL Azure produced our scripts in the database project folder we’ve set as the target (see all the .SQL files automatically generated):

Now, let’s open the well-known SQL Server Management Studio tool and connect to our local SQL Server. You should see there the database which has been generated by the SQL Azure producer “diff engine”. Open now you web browser, go to your SQL Azure database address, in my case https://ttmshoc6av.database.windows.net/?langid=en#$database=MyDatabase, and fill-in the form with the server credentials you’ve setup earlier.

When you are logged on, click on the Design button on the bottom left of the screen. You should land on the following screen and, as you can see, there are no tables or stored procedures here, yet:

Why hasn’t my database been generated on SQL Azure? The answer is simple: the property “Produce Online SQL Azure” is still set to false. So let’s go back to Visual Studio. Double-click on the “SQL Azure Producer”, change that property to true, press OK, and build your solution again. Once your solution is built, go back to the SQL Azure portal, click on the Refresh button. All your tables and stored procedures should now appear like this:

Since you’re using CodeFluent Entities, as usual, continuous build is a feature. So, you can change the model and build anytime, this will update all the layers of your application, including your SQL Azure database.

Pretty cool isn’t it?

Aymeric ROLAND.

Designing Databases with CodeFluent Entities: Inheritance

April 20, 2012 Leave a comment

CodeFluent Entities tries to reduce the impedance mismatch between relational worlds and object-oriented worlds as much as possible and a good example of this I guess is how CodeFluent Entities supports inheritance across layers of your application.

Using CodeFluent Entities, an entity hierarchy defined in your model will be translated following the Table-Per-Type pattern in the persistence layer, when, in the generated business object model, this will be a regular class inheritance.

For instance, say we have the following hierarchy:

Here’s the result in the database:

Furthermore, data access methods will be modified accordingly to load the objects as expected in .NET. For instance, here’s the “Load” stored procedure that is automatically generated for the DiscountProduct entity:

CREATE PROCEDURE [dbo].[DiscountProduct_Load]
(
@Id [uniqueidentifier]
)
AS
SET NOCOUNT ON
SELECT DISTINCT [DiscountProduct].*, Product].* 
  FROM [DiscountProduct]
   INNER JOIN [Product] ON ([DiscountProduct].[Product_Id] = [Product].[Product_Id])
  WHERE ([DiscountProduct].[Product_Id] = @Id)

RETURN

Cheers,

Carl Anderson

Designing Databases with CodeFluent Entities: Names

April 19, 2012 Leave a comment

As explained in previous posts, CodeFluent Entities is way more that an ORM: you’re not just mapping classes to database objects, instead you’re creating a model which will be translated into stuff. Therefore if you add persistence producers such as SQL Server, Oracle Database or SQL Azure a database will be created or updated from it (see continuous generation from more info).

Creating a database implies naming a lot of stuff: tables, columns, constraints, stored procedures, etc., so I thought I’d give you a little details on how it works and how you can control this to create your own database.

First, CodeFluent Entities deduces names automatically from your model:

  • Table names are identical to entity names,
  • Columns are named “<EntityName>_<PropertyName>”,
  • Procedures are named “<EntityName>_<MethodName>”

Users can override this logic by:

  • defining a “Persistence Name” in the Property Grid on the object you want to change (entity, property, method, view, ..)
  • setting a “Persistence Property Name Format” at the project level to customize all property names of your project (e.g. replace the default “{0}_{1}” by “{1}” to remove the entity name prefix from all column names),
  • changing the naming convention

In practice a naming convention is a class implementing a naming convention rule which CodeFluent Entities will use to name persistence objects. Consequently changing the naming convention used will change the way all objects are named.

Several naming conventions are provided out-of-the-box and they all derive from one another, hence they benefit from the features of their parents. The hierarchy is as follows:

  • BaseNamingConvention (default)
    • FormatNamingConvention: modifies the name format of tables, columns, procedures, parameters, constraints.
      • LowerCaseNamingConvention: sets all database object names in lower case.
      • UpperCaseNamingConvention: sets all database object names in upper case.
      • DecamelizeNamingConvention: decamelizes all names (FirstName –> First_Name)
        • DecamelizeLowerCaseNamingConvention: all in lower case and decamelized (FirstName –> first_name)
        • DecamelizeUpperCaseNamingConvention: all in upper case and decamelized (FirstName –> FIRST_NAME)

Furthermore you can create your own custom naming convention by implementing the INamingConvention or by deriving from existing ones.

 

Cheers,

Carl Anderson

Follow

Get every new post delivered to your Inbox.

Join 52 other followers