Tag Archives: access

SQLite with .NET: excellent but some oddities

I have been porting a C# application which uses an MDB database (the old Access/JET format) to one that uses SQLite. The process has been relatively smooth, but I encountered a few oddities.

One is puzzling and is described by another user here. If you have a column that normally stores string values, but insert a string that happens to be numeric such as “12345”, then you get an invalid cast exception from the GetString method of the SQLite DataReader. The odd thing is that the GetFieldType method correctly returns String. You can overcome this by using GetValue and casting the result to a string, or calling GetString() on the result as in dr.GetValue().ToString().

Another strange one is date comparisons. In my case the application only stores dates, not times; but SQLite using the .NET provider stores the values as DateTime strings. The SQLite query engine returns false if you test whether “yyyy-mm-dd 00:00:00” is equal to “yyy-mm-dd”. The solution is to use the date function: date(datefield) = date(datevalue) works as you would expect. Alternatively you can test for a value between two dates, such as more than yesterday and less than tomorrow.

Performance is excellent, with SQLite . Unit tests of various parts of the application that make use of the database showed speed-ups of between 2 and 3 times faster then JET on average; one was 8 times faster. Note though that you must use transactions with SQLite (or disable synchronous operation) for bulk updates, otherwise database writes are very slow. The reason is that SQLite wraps every INSERT or UPDATE in a transaction by default. So you get the effect described here:

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

Without a transaction, a unit test that does a bulk insert, for example, took 3 minutes, versus 6 seconds for JET. Refactoring into several transactions reduced the SQLite time to 3 seconds, while JET went down to 5 seconds.

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 LocalDB: another option for local databases

Microsoft is launching SQL Server 2012 on  March 7th 2012. In Microsoft’s world “launches” do not always coincide with the availability of release code, which may come before or after, but they are usually not far apart.

The big news in SQL Server 2012 is in new BI (Business Intelligence) features and the ability to import and export from the open source Hadoop framework. Microsoft is also supporting Hadoop on Windows Server and Windows Azure. Robert Sheldon has an excellent article on TechTarget which describes the Hadoop integration.

At the other end of the scale though there is a new approach to local databases, which interests me as this is the kind of thing an application developer might use for local applications. SQL Express LocalDB uses the full SQL Server Express engine but does not require a SQL Server service to be running or even installed. In summary:

  • The LocalDB binaries can be installed with a separate installer or as part of the SQL Server Express.
  • LocalDB instances are isolated to the user.
  • The LocalDB system databases are buried deep in AppData in the user profile. The default location for user databases is the root of the user profile.
  • The old SQL Server User Instances are now deprecated

A driver for LocalDB has to know how to fire up the SQL Server binaries if they are not running, which means that old drivers will not work. Microsoft has patched System.Data.SqlClient in .NET 4 to work with LocalDB.

LocalDB Pros and cons

The advantage of LocalDB over the cut-down Compact Edition is that you get full access to SQL Server features including transactions, stored procedures, geographical data types and so on. It is meant to improve on the old user instances by simplifying matters for the user: no need to run a service, and management of SQL Server completely hidden.

The disadvantage is that your app still has the overhead of SQL Server running in a separate process. A SQL Server LocalDB install also takes around 140MB, which bumps up the download size if your app is distributed on the web.

If you need a local database, it seems to me that Microsoft still has nothing that quite matches SQLite, which runs in-process, is lightning fast, and which does not require any hidden system databases.

On the other hand, it might make sense to use SQL Server if you want to integrate with a server database, or if you are familiar with coding for SQL Server.

I would like to see Windows ship with a local database engine documented as something developers can rely on being there, as with Core Data on the Mac. It would also help if the SQL Server team got together with the Office team and worked out how to get Access and SQL Server Express to use the same database engine – yes, I know Access can use SQL Server data, but it still defaults to its own .ACCDB format and JET database engine.

Ten things you need to know about Microsoft’s Visual Studio LightSwitch

Microsoft has announced a new edition of Visual Studio called LightSwitch, now available in beta, and it is among the most interesting development tools I’ve seen. That does not mean it will succeed; if anything it is too radical and might fail for that reason, though it deserves better. Here’s some of the things you need to know.

1. LightSwitch builds Silverlight apps. In typical Microsoft style, it does not make the best of Silverlight’s cross-platform potential, at least in the beta. Publish a LightSwitch app, and by default you get a Windows click-once installation file for an out-of-browser Silverlight app. Still, there is also an option for a browser-hosted deployment, and in principle I should think the apps will run on the Mac (this is stated in one of the introductory videos) and maybe on Linux via Moonlight. Microsoft does include an “Export to Excel” button on out-of-browser deployments that only appears on Windows, thanks to the lack of COM support on other platforms.

I still find this interesting, particularly since LightSwitch is presented as a tool for business applications without a hint of bling – in fact, adding bling is challenging. You have to create a custom control in Silverlight and add it to a screen.

Microsoft should highlight the cross-platform capability of LightSwitch and make sure that Mac deployment is easy. What’s the betting it hardly gets a mention? Of course, there is also the iPhone/iPad problem to think about. Maybe ASP.NET and clever JavaScript would have been a better idea after all.

2. There is no visual form designer – at least, not in the traditional Microsoft style we have become used to. Here’s a screen in the designer:

image

Now, on one level this is ugly compared to a nice visual designer that looks roughly like what you will get at runtime. I can imagine some VB or Access developers will find this a difficult adjustment.

On the positive side though, it does relieve the developer of the most tedious part of building this type of forms application – designing the form. LightSwitch does it all for you, including validation, and you can write little snippets of code on top as needed.

I think this is a bold decision – it may harm LightSwitch adoption but it does make sense.

3. LightSwitch has runtime form customization. Actually it is not quite “runtime”, but only works when running in the debugger. When you run a screen, you get a “Customize Screen” button at top right:

image

which opens the current screen in Customization Mode, with the field list, property editor, and a preview of the screen.

image

It is still not a visual form designer, but mitigates its absence a little.

4. LightSwitch is model driven. When you create a LightSwitch application you are writing out XAML, not the XAML you know that defines a WPF layout, but XAML to define an application. The key file seems to be ApplicationDefinition.lsml, which starts like this:

image

Microsoft has invested hugely in modelling over the years with not that much to show for it. The great thing about modelling in LightSwitch is that you do not know you are doing it. It might just catch on.

Let’s say everyone loves LightSwitch, but nobody wants Silverlight apps. Could you add an option to generate HTML and JavaScript instead? I don’t see why not.

5. LightSwitch uses business data types, not just programmer data types. I mean types like EmailAddress, Image, Money and PhoneNumber:

image

I like this. Arguably Microsoft should have gone further. Do we really need Int16, Int32 and Int64? Why not “Whole number” and “Floating point number”? Or hide the techie choices in an “Advanced” list?

6. LightSwitch is another go at an intractable problem: how to get non-professional developers to write properly designed relational database applications. I think Microsoft has done a great job here. Partly there are the data types as mentioned above. Beyond that though, there is a relationship builder that is genuinely easy to use, but which still handles tricky things like many-to-many relationships and cascading deletes. I like the plain English explanations in the too, like “When a Patient is deleted, remove all related Appointment instances” when you select Cascade delete.

image

Now, does this mean that a capable professional in a non-IT field – such as a dentist, shopkeeper, small business owner, departmental worker – can now pick up LightSwitch and and write a well-designed application to handle their customers, or inventory, or appointments? That is an open question. Real-world databases soon get complex and it is easy to mess up. Still, I reckon LightSwitch is the best effort I’ve seen – more disciplined than FileMaker, for example, (though I admit I’ve not looked at FileMaker for a while), and well ahead of Access.

This does raise the question of who is really the target developer for LightSwitch? It is being presented as a low-end tool, but in reality it is a different approach to application building that could be used at almost any level. Some features of LightSwitch will only make sense to IT specialists – in fact, as soon as you step into the code editor, it is a daunting tool.

7. LightSwitch is a database application builder that does not use SQL. The query designer is entirely visual, and behind the scenes Linq (Language Integrated Query) is everywhere. Like the absence of a visual designer, this is a somewhat risky move; SQL is familiar to everyone. Linq has advantages, but it is not so easy to use that a beginner can express a complex query in moments. When using the Query designer I would personally like a “View and edit SQL” or even a “View and edit Linq” option.

8. LightSwitch will be released as the cheapest member of the paid-for Visual Studio range. In other words, it will not be free (like Express), but will be cheaper than Visual Studio Professional.

9. LightSwitch applications are cloud-ready. In the final release (but not the beta) you will be able to publish to Windows Azure. Even in the beta, LightSwitch apps always use WCF RIA Services, which means they are web-oriented applications. Data sources supported in the beta are SQL Server, SharePoint and generic WCF RIA Services. Apparently in the final release Access will be added.

10. Speculation – LightSwitch will one day target Windows Phone 7. I don’t know this for sure yet. But why else would Microsoft make this a Silverlight tool? This makes so much sense: an application builder using the web services model for authentication and data access, firmly aimed at business users. The first release of Windows Phone 7 targets consumers, but if Microsoft has any sense, it will have LightSwitch for Windows Phone Professional (or whatever) lined up for the release of the business-oriented Windows Phone.