Tag Archives: database

Access Web App: at last a simple web database app builder from Microsoft

One thing hardly mentioned in the press materials for Office 2013, and therefore mostly ignored in the immediate publicity, is Microsoft Access 2013. It is included though, and its most interesting new feature is a thing called an Access Web app.

image

To make one of these, you click the big “Custom web app” button on the opening screen. The first thing you are asked is where to put it. It is looking for a SkyDrive or Office 365 team site – essentially, online SharePoint 2013 I imagine. If you are not signed in, this screen appears blank.

Advertisement

I selected Skydrive at my Office 365 preview site.

image

Hit Create and you can select an app from a template. I chose a Music Collection app. Access generated several tables and forms for me and opened the design environment.

image

The template app is a bit daft – Artists and Labels are based on a People template, so you get Labels with a Job Title field – but that does not bother me. What interests me is that Access generates a relational database that you can edit as you like. The template UI offers either a list/detail view called a List, or a Datasheet which shows rows in a grid format. There is also a Blank view which you can design from scratch.

I had a quick poke around. Access Web Apps do too good a job of hiding their innards for my taste, but what you get is a SharePoint app with data stored in SQL Server Azure. You can also use on-premise SharePoint and SQL Server 2012.

Programmability in Access Web Apps is limited, but you do get macros which let you combine multiple actions. There are two kinds of macros, UI macros and Data macros. UI macros support a range of actions including SetVariableif and else statements. The only loop functions I can see are in Data macros, which include a ForEachRecord action. You can call Data macros from other macros and a Data macro includes a SetReturnVar statement, so I guess with a bit of ingenuity you can do many kinds of automated operations. Macros are described here.

image

In my quick test, I put a button on a view and had it show a message. Apologies.

The application files are all stored on SharePoint, rather than locally, so I presume you could easily edit the app on any machine with Access 2013 installed.

Click Launch App and the web app opens in the browser. Everything worked, including my MessageBox.

image

I also tried it on the Google Nexus 7 Android device. Again it seems to work fine, though I did get some odd behaviour returning to the app. There are possibly some authentication issues.

image

An Access Web App is just another SharePoint app, as explained here, so you can publish it to selected groups via the built-in store.

There is no way that I can see to craft your own SQL, which to me is a disadvantage, but maybe we will discover how to bypass the UI and open a database in SQL Management Studio, or access it programmatically from other environments.

It seems to me that what Microsoft is offering here is what it tried, but failed, to offer in Visual Studio Lightswitch: database programming for the non-specialist. Access has always done this, though unfortunately it is easy to make rather a mess if you do not know what you are doing. An Access Web App gives the developer/user fewer ways to go wrong, and builds cross-browser web apps. It is not yet possible to judge whether Microsoft has got the feature set right, but fundamentally this looks useful for simple custom business database applications of the kind that many small organisations and departments find they need. It is a big advance on MDB files stuck on a file share, fits with the BYOD (Bring Your Own Device) concept by working on iPads and the like, and makes it easy to get started and experiment. Good work.

Microsoft SQL Azure versus SQL Server on Amazon AWS

Amazon RDS for Microsoft SQL Server offers cloud instances of SQL Server. Amazon’s offering even supports “License Mobility”, Microsoft jargon that lets volume licensing customers use an existing SQL Server license for an Amazon’s instance. But how does Amazon’s cloud SQL Server compare with Microsoft’s own offering, SQL Database running on Azure?

Peter Marriott has posted on the subject here (registration required). The key point: despite the obvious similarity (both are SQL Server), these two offerings are radically different. Amazon’s RDS SQL is more IaaS (infrastructure as a service) than PaaS (platform as a service). You choose an edition of SQL Server and rent one or more instances. The advantage is that you get full SQL Server, just like the on-premise editions but hosted by Amazon.

Microsoft’s Azure-hosted SQL on the other hand is more abstracted. You do not rent a SQL Server instance; you rent a database. Under the covers Microsoft provides multiple redundant copies of the data, and if traffic increases, it should scale automatically, though the database size is limited to 150GB. The downside is that not all features of SQL Server are available, as I discovered when migrating data.

Marriott adds that SQL Azure supports encrypted connections and has a more usable administration interface.

A further twist: you can also install SQL Server on an Azure Virtual Machine, which would get you something more like the Amazon approach though I suspect the cost will work out higher.

Full circle for Microsoft database APIs as OLEDB for SQL Server is deprecated

Microsoft’s Eric Nelson has posted about how the OLEDB driver for SQL Server is being deprecated and will not be supported beyond “Denali”, the forthcoming version.

OLEDB was created to be the successor to ODBC – expanding the supported data sources/models to include things other than relational databases. Notably OLEDB was tightly tied to a Windows only technology (COM) whilst ODBC was not (Although we did try and take COM cross platform via partners)

ODBC never did get replaced. What actually happened is that ODBC remained the dominant of the two technologies for many scenarios – and became increasingly used on none Windows platforms and has become the de-facto industry standard for native relational data access.

ODBC was as I recall Microsoft’s first attempt at creating a universal database API.

The death of OLEDB will be slow, according to Nelson. The OLEDB driver for Denali will be supported for seven years following Denali’s release. He also says that OLEDB itself, as opposed to the SQL Server OLEDB driver, is not necessarily being deprecated; though frankly if Microsoft ceases supporting it with its own database I cannot see much future for it.

Note that ADO.NET, which to some extent replaced OLEDB, is not being deprecated. However ADO.NET is only usable from .NET applications. When you consider that Microsoft may be to some extent tilting away from .NET and towards native code, the deprecation of OLEDB becomes even more significant.

ODBC is not particularly easy to use in its raw form. However, you can wrap ODBC with, yes, an OLEDB provider or an ADO.NET provider; or you can wrap the whole lot in an object-relational framework such as Entity Framework.

One more chapter in the long, strange and tortuous history of Microsoft’s data APIs.

Easy database apps for iPad and iPhone with FileMaker Pro and Go

FileMaker Pro is a database manager from FileMaker Inc, a wholly owned subsidiary of Apple. It is a capable produce that has been around for over 20 years and is the dominant Mac-based database manager, though there is also a Windows version. FileMaker has evolved relatively slowly, with more focus on usability than on features. In comparison to Microsoft Access, FileMaker wins on usability and scalability, but Access has a more traditional approach based on SQL and programming with Visual Basic for Applications. FileMaker has a drag-and-drop script editor and support for AppleScript on the Mac.

Although the script editor is frustrating for someone used to writing code, it does work. As well as manipulating the data, you can set and retrieve local and global variables, perform loops and display custom dialogs; it is not as limited as it may seem at first.

A FileMaker database can be huge, with 8 terabytes specified as the theoretical limit. External databases are accessible through ODBC on both Windows and Mac.

The number of users supported by FileMaker is limited. The desktop product supports up to 5 concurrent users, and FileMaker Server up to 250 users. FileMaker has its own built-in security system, though FileMaker server can also authenticate against an external directory. Security is fine-grained, and you can even specify permissions for an individual record.

I have not looked at FileMaker for a few years, but renewed my interest when the company came out with FileMaker Go, a runtime client for Apple iOS. Given that FileMaker runs scripts you might have thought this would be restricted, bearing in mind this provision in the App Store guidelines:

2.7 Apps that download code in any way or form will be rejected

This is normally taken to prohibit runtimes like Java or Adobe Flash/AIR. Well, either someone decided that FileMaker scripts are not code; or there are special rules for an Apple subsidiary, which is reasonable enough. Anyway, FileMaker Go is in the App Store and does run scripts.

What this means is that you can create apps in FileMaker Pro and deploy them to iOS without going via the App Store. There are two models. FileMaker Go can open a file hosted by FileMaker desktop or server, in which case it behaves like a Mac or Windows client, or alternatively you can transfer a file to FileMaker Go to run locally. Transferring a file is easy using iOS launch service; essentially, if you can access the file via the internet or an email attachment, you can just tap it on the device and it will open in FileMaker Go. The advantage of running locally is offline use, whereas the advantage of the client-server model is that all users have the most up-to-date version of the data, and the database can be much larger. FileMaker is a real server application; this is not just file sharing. This also means that FileMaker must be running with the database open if you want to to use the client-server approach.

I tried FileMaker Go with a simple example and it works well. In essence it is delightful; you just open your database either locally or over the network, and it works. Here is a sample app on the iPhone 4:

image

That said, there are things that do not work, spell checking for example. It is also stripped of anything other than client features, so you cannot modify database structure, create new databases, or publish from the device to other clients. You also have to be careful with layout size. Most layouts designed for the desktop will need modification to work well.

There are a couple of issues. One is performance. It is just about bearable, but has that lethargic feel that you get with interpreted code on a relatively slow processor.

Another issue is synchronisation. If you want to work offline, how do you update your main database with any changes? The issue is little different with FileMaker Go than it is with a laptop, and it is discussed here. You have several choices:

1. Don’t synchronize, use client-server.

2. Treat your local database as read-only.

3. Use import and export. Existing records will simply be overwritten by imported ones.

4. Use a third-party tool. However the tool mentioned here, SyncDek, probably does not work with FileMaker Go since it needs to run a Java process on the client.

5. Roll your own. “FileMaker Pro has all the tools needed to create a robust synchronization system” says the guide; but it is non-trivial to implement this.

It is worth mentioning that FileMaker Pro also has an Instant Web Publishing feature that gives another route to mobile access and may perform better. There are pros and cons. The big one is offline, only available with FileMaker Go. Another is scripts. Some scripts work in Instant Web Publishing, but FileMaker Go is more compatible in this area.

I think this is significant for businesses where iOS devices are turning up. Many business apps do resolve down to forms over data, and this is is an easy way to deliver this kind of application to iOS users.

How is FileMaker pro as a programming tool? Just for fun, and because I have done it for other mobile development tools, I built a calculator in FileMaker. I do not recommend FileMaker for general-purpose programming; but it has the essentials, a form designer and scripting. Here is the result on an iPhone 4:

image

Oddly the biggest struggle I had was finding an easy way to display the input and result. In the end I added a field to the database just for this purpose. If a FileMaker expert could let me know a better way to update a text label on a layout via script, I would be interested to know.

The calculator is slow too, not for the calculation of course, but the operation of the user interface. Still, it does demonstrate that FileMaker Go is indeed able to download code and run it.

Update: I replaced the display field with a merge variable, which works better as it avoids focus issues and the keyboard popping up. Thanks to the commenter for the suggestion.

Hands On with Visual Studio LightSwitch – but what is it for?

Visual Studio LightSwitch, currently in public beta, is Microsoft’s most intriguing development tool for years. It is, I think, widely misunderstood, or not understood; but there is some brilliant work lurking underneath it. That does not mean it will succeed. The difficulty Microsoft is having in positioning it, together with inevitable version one limitations, may mean that it never receives the attention it deserves.

Let’s start with what Microsoft says LightSwitch is all about. Here is a slide from its Beta 2 presentation to the press:

image

Get the idea? This is development for the rest of us, "a simple tool to solve their problems” as another slide puts it.

OK, so it is an application builder, where the focus is on forms over data. That makes me think of Access and Excel, or going beyond Microsoft, FileMaker. This being 2011 though, the emphasis is not so much on single user or even networked Windows apps, but rather on rich internet clients backed by internet-hosted services. With this in mind, LightSwitch builds three-tier applications with database and server tiers hosted on Windows server and IIS, or optionally on Windows Azure, and a client built in Silverlight that runs either out of browser on Windows – in which case it gets features like export to Excel – or in-browser as a web application.

There is a significant issue with this approach. There is no mobile client. Although Windows Phone runs Silverlight, LightSwitch does not create Windows Phone applications; and the only mobile that runs Silverlight is Windows Phone.

LightSwitch apps should run on a Mac with Silverlight installed, though Microsoft never seems to mention this. It is presented as a tool for Windows. On the Mac, desktop applications will not be able to export to Excel since this is a Windows-only capability in Silverlight.

Silverlight MVP Michael Washington has figured out how to make a standard ASP.NET web application that accesses a LightSwitch back end. I think this should have been an option from the beginning.

I digress though. I decided to have a go with LightSwitch to see if I can work out how the supposed target market is likely to get on with it. The project I set myself was a an index of magazine articles; you may recognize some of the names. With LightSwitch you are insulated from the complexities of data connections and can just get on with defining data. Behind the scenes it is SQL Server. I created tables for Articles, Authors and Magazines, where magazines are composed of articles, and each article has an author.

The LightSwitch data designer is brilliant. It has common-sense data types and an easy relationship builder. I created my three tables and set the relationships.

image

Then I created a screen for entering articles. When you add a screen you have to say what kind of screen you want:

image

I chose an Editable Grid Screen for my three tables. LightSwitch is smart about including fields from related tables. So my Articles grid automatically included columns for Author and for Magazine. I did notice that the the author column only showed the firstname of the author – not good. I discovered how to fix it. Go into the Authors table definition, create a new calculated field called FullName, click Edit Method, and write some code:

partial void FullName_Compute(ref string result)
{
    // Set result to the desired field value
   result = this.Firstname + " " + this.Lastname;

}

Then you set FullName as the “Summary” field for the table.

Have we lost our non-developer developer? I don’t think so, this is easier than a formula in Excel once you work out the steps. I was interested to see the result variable in the generated code; echoes of Delphi and Object Pascal.

I did discover though that my app has a usability problem. In LightSwitch, the user interface is generated for you. Each screen becomes a Task in a menu on the left, and double-clicking opens it. The screen layout is also generated for you. My problem: when I tried entering a new article, I had to specify the Author from a drop-down list. If the author did not yet exist, I had to open an Authors editable grid, enter the new author, save it, then go back to the Articles grid to select the new author.

I set myself the task of creating a more user-friendly screen for new articles. It took me a while to figure out how, because the documentation does not seen to cover my requirement, but after some help from LightSwitch experts I arrived at a solution.

First, I created a New Data Screen based on the Article table. Then I clicked Add Data Item and selected a local property of type Author, which I called propAuthor.

image

Next, I added two groups to the screen designer. Screen designs in LightSwitch are not like any screen designs you have seen before. They are a hierarchical list of elements, with properties that affect their appearance. I added two new groups, Group Button and GroupAuthor, and set GroupAuthor to be invisible. Then I dragged fields from propAuthor into the Author group. Then I added two buttons, one called NewAuthor and one called SaveAuthor. Here is the dialog for adding a button:

image

and here is my screen design:

image

So the idea is that when I enter a new article, I can select the author from a drop down list; but if the author does not exist, I click New Author, enter the author details, and click Save. Nicer than having to navigate to a new screen.

In order to complete this I have to write some more code. Here is the code for NewAuthor:

partial void NewAuthor_Execute()
{
     // Write your code here.
     this.propAuthor = new Author();
     this.FindControl("GroupAuthor").IsVisible = true;
}

Note the use of FindControl. I am not sure if there is an easier way, but for some reason the group control does not show up as a property of the screen.

Here is the code for SaveAuthor:

partial void SaveAuthor_Execute()
{
    // Write your code here.
    this.ArticleProperty.Author = propAuthor;
    this.Save();
}

image

This works perfectly. When I click Save Author, the new author is added to the article, and both are saved. Admittedly the screen layout leaves something to be desired; when I have worked out what Weighted Row Height is all about I will try and improve it.

image

Before I finish, I must mention the LightSwitch Publish Wizard, which is clearly the result of a lot of work on Microsoft’s part. First, you choose between a desktop or web application. Next you choose an option for where the services are hosted, which can be local, or on an IIS server, or on Windows Azure.

image

Something I like very much: when you deploy, there is an option to create a new database, but to export the data you have already entered while creating the app. Thoughtful.

image

As you can see from the screens, LightSwitch handles security and access control as well as data management.

What do I think of LightSwitch after this brief exercise? Well, I am impressed by the way it abstracts difficult things. Considered as an easy to use tool for model-driven development, it is excellent.

At the same time, I found it frustrating and sometimes obscure. The local property concept is a critical one if you want to build an application that goes beyond what is generated automatically, but the documentation does not make this clear. I also have not yet found a guide or reference to writing code, which would tell me whether my use of FindControl was sensible or not.

The generated applications are functional rather than beautiful, and the screen layout designer is far from intuitive.

How is the target non-developer developer going to get on with this? I think they will retreat back to the safety of Access or FileMaker in no time. The product this reminds me of more is FoxPro, which was mainly used by professionals.

Making sense of LightSwitch

So what is LightSwitch all about? I think this is a bold effort to create a Visual Basic for Azure, an easy to use tool that would bring multi-tier, cloud-hosted development to a wide group of developers. It could even fit in with the yet-to-be-unveiled app store and Appx application model for Windows 8. But it is the Visual Basic or FoxPro type of developer which Microsoft should be targeting, not professionals in other domains who need to knock together a database app in their spare time.

There are lots of good things here, such as the visual database designer, the Publish Application wizard, and the whole model-driven approach. I suspect though that confused marketing, the Silverlight dependency, and the initial strangeness of the whole package, will combine to make it a hard sell for Microsoft. I would like to be wrong though, as a LightSwitch version 2 which generates HTML 5 instead of Silverlight could be really interesting.

Microsoft’s code-first Entity Framework 4.1 nearly done

Microsoft has announced the release candidate of Entity Framework 4.1, the data persistence library for .NET, with a go-live licence. The final release to the web is expected in around one month’s time.

The big new feature is code-first, where you do not need to define a database schema or even a database model. You simply write classes that define objects you want to store, and the framework handles the work of defining the database for you.

Note that according to this article on MSDN:

The Entity Framework is Microsoft’s recommended data access technology for most types of applications.

Of course Microsoft has a long history of data access APIs and keeping up with the latest recommendation over the years has been a challenge. That said, the low-level ADO.NET data API has been in place since the first release of the .NET framework and has evolved rather than been replaced. There has been some confusion over LINQ to SQL versus Entity Framework; but note that LINQ (Language Integrated Query) works with Entity Framework as well.

So what is code-first? A good starting point is VP Scott Guthrie’s post from July last year, where he walks through a complete example using his Nerd Dinner theme. He writes classes to define two entities, Dinner and RSVP. Then he writes the following code:

image

Having defined this NerdDinners class inheriting from DbContext, he can go ahead and write a complete database application.

At this point there is still no database. In the simplest case though, you can just add a database connection to the project with the same name as the DbContext class – in this case, “NerdDinners”. The Entity Framework will use this connection, define a database schema for you, and save and retrieve objects accordingly.

The magic under the hood is an example of convention over configuration. That is, the framework will generate code and schema according to assumptions it makes based on the names used in your classes. For example, it picks up the field named DinnerID and makes it a primary key; and seeing a collection of RSVP objects called RSVPs in the Dinner class, the framework creates a relationship between the two generated tables. You can override the default behaviour with code mapping rules. There is also provision for updating the schema if you need to add or modify the fields, though this is a point of uncertainty in Guthrie’s post.

It looks fantastic; though there are a few caveats. One is that Microsoft tends to assume use of its own database managers, SQL Server or for simple cases, SQL Server CE. That said, there are drivers for other databases; for example devart has code-first drivers for Oracle, MySQL, PostgreSQL and SQLite.

Another point is that there is a trade-off when working at such a high level of abstraction. There is less code for you to write, but a large amount of generated code, which can make debugging or optimizing an application harder. This is a familiar trade-off though; and you could say that hand-rolled SQL is no different in principle from hand-rolled assembly code; you can get fantastic results but the amount of effort and skill required is greater, as is the risk of errors if you get it slightly wrong.

The Mono team has said that it does not intend to implement Entity Framework currently; there is a summary of work needed here. If you want to write .NET code that ports easily to Mono it is best avoided.

Are you using Entity Framework in new .NET projects? I would be interested to hear from .NET developers what approach you take to data persistence.

Why Oracle is immoveable in the Enterprise

At Oracle OpenWorld yesterday I spoke to an attendee from a global enterprise. His company is a big IBM customer and would like to standardise on DB2. To some extent it does, but there is still around 30% Oracle and significant usage of Microsoft SQL Server. Why three database platforms when they would prefer to settle on one? Applications, which in many cases are only certified for a specific database manager.

I was at MySQL Sunday earlier in the day, and asked whether he had any interest in Oracle’s open source database product. As you would expect, he said it was enough trouble maintaining three different systems; the last thing he wanted was a fourth.

Visual Studio LightSwitch – model-driven architecture for the mainstream?

I had a chat with Jay Schmelzer and  Doug Seven from the Visual Studio LightSwitch team. I asked about the release date – no news yet.

What else? Well, Schmelzer and Seven had read my earlier blog post so we discussed some of the things I speculated about. Windows Phone 7? Won’t be in the first release, they said, but maybe later.

What about generating other application types from the same model? Doug Seven comments:

The way we’ve architected LightSwitch does not preclude us from making changes .. it’s not currently on the plan to have different output formats, but if demand were high it’s feasible in the future.

I find this interesting, particularly given that the future of the business client is not clear right now. The popularity of Apple’s iPad and iPhone is a real and increasing deployment problem, for example. No Flash, no Silverlight, no Java, only HTML or native apps. The idea of simply selecting a different output format is compelling, especially when you put it together with the fast JIT-compiled JavaScript in modern web browsers. Of course support for multiple targets has long been the goal of model-driven architecture (remember PIM,PSM and PDM?) ; but in practice the concept of a cross-platform runtime has proved more workable.

There’s no sign of this in the product yet though, so it is idle speculation. There is another possible approach though, which is to build a LightSwitch application, and then build an alternative client, say in ASP.NET, that uses the same WCF RIA Services. Since Visual Studio is extensible, it will be fun to see if add-ins appear that exploit these possibilities.

I also asked about Mac support. It was as I expected – the team is firmly Windows-centric, despite Silverlight’s cross-platform capability. Schmelzer was under the impression that Silverlight on a Mac only works within the browser, though he added “I could be wrong”.

In fact, Silverlight out of browser already works on a Mac; the piece that doesn’t work is COM interop, which is not essential to LightSwitch other than for export to Excel. It should not be difficult to run a LightSwitch app out-of-browser on a Mac, just right-click a browser-hosted app and choose Install onto this computer, but Microsoft is marketing it as a tool for Windows desktop apps, or Web apps for any other client where Silverlight runs.

Finally I asked whether the making of LightSwitch had influenced the features of Silverlight or WCF RIA Services themselves. Apparently it did:

There are quite a few aspects of both Silveright 4 and RIA services that are in those products because we were building on them. We uncovered things that we needed to make it easier to build a business application with those technologies. We put quite a few changes into the Silverlight data grid.

said Schmelzer, who also mentioned performance optimizations for WCF RIA Services, especially with larger data sets, some of which will come in a future service pack. I think this is encouraging for those intending to use Silverlight for business applications.

There are many facets to LightSwitch. As a new low-end edition of Visual Studio it is not that interesting. As an effort to establish Silverlight as a business application platform, it may be significant. As an attempt to bring model-driven architecture to the mainstream, it is fascinating.

The caveat (and it is a big one) is that Microsoft’s track-record on modelling in Visual Studio is to embrace in one release and extinguish in the next. The company’s track-record on cross-platform is even worse. On balance it is unlikely that LightSwitch will fulfil its potential; but you never know.