LocalDB and NHibernate

We’ve got a request to support SQL Server 2012 in our product at work. Well, the LocalDB (coming with Visual Studio 2012; a rough equivalent of SQL Server Express of the past) is practically SQL Server 2012 I thought, so it was reasonable to do initial testing on it. We use NHibernate 3 in our system, so I went to the .nhibernate.config file, and changed the connection.connection_string property (the Data Source part of it, to be precise) from

Data Source=localhost\SQLEXPRESS

to the logically sounding

Data Source=(localdb)\Projects

and… It did not work. A bit of googling pointed me to a solution: named pipe connection. To get its name, run

sqllocaldb info <InstanceName>

in the command line, which in my case translated into sqllocaldb info Projects. The output will contain the Instance pipe name line, and the value of it is the data source name you are after. So, in my case the correct Data Source read

Data Source=np:\\.\pipe\LOCALDB#5E0F8FF8\tsql\query

(as far as I understand, usually only the number after LOCALDB# will change).

Advertisements

Data Access Optimization in SQL Server

The Top 10 steps to optimize data access in SQL Server article series at CodeProject, despite some mistakes and arguable things, is not that bad starter’s guide for the SQL Server optimization question:

ORMs

Yesterday I was thinking about how complicated and painfull is accessing databases in .NET, and thus in WPF. It is absolute mess. Really. Much more messy that it was in good old ADO (anyone remembers those times?), and not even remotely close to data access nirvana of FoxPro and Visual FoxPro.

LINQ is very nice, but it is just a query language completely missing insert, update, and delete concepts. Then there is a weird thing called LINQ to SQL – it really sucks if you are changing database schemas (and you normally do that in the beginning of the progect frequently). Sheer amount of the code and “infrastructure” generated for you is mindboggling. Things get even worse if you want to use SQL Server CE – then you are stuck with something called SqlMetal. Brrrrrrr! Then, add plain old ADO.NET with LINQ to ADO, and EF to the “package” and you are completely and thoroughly lost. Bad… very bad… total mess… Does it have to be like that? And why Microsoft cannot decide on one thing and do it right?

That’s why I decided to spend some time and to see if there are any alternative ORMs available.

Everyone knows NHibernate (little brother of Hibernate), and DevExpress’ eXpress Persistent Objects (as one friend of mine said, “When I hear word DevExpress I am reaching for my gun!” :). These two ORM frameworks are suffering from the same problems as EF, LINQ to SQL and other things offered in this domain by Microsoft: too general, too complex, too steep learning curve, too many demands and expectations toward the code developed. They might be good for big and complex systems, but if you develop an application where database is just one small part of functionality they quickly become too big of a burden.

Clearly, there is a need for something more lightweight. And seems that I found something that looks rather promising: one thing is called Mindscape‘s LightSpeed, and the other one has intriguing name LLBLGen Pro. Both frameworks look really easy – I got pretty much of the LightSpeed’s idea in about 20 minutes, although have not checked LLBLGen Pro yet. Both are offering most essential features – as Mindscape says, “Solve the 95% case, i.e. more like Ruby on Rails than NHibernate”. Both provide Visual Studio integration. LLBLGen Pro supports more databases than LightSpeed, but both seem to be OK for our needs. Probably they are not as flexible and not as all-encompassing as their “older brothers”, but it is obvious that not everyone needs this flexibility anyway – I think “less is more” is very frequently true. Both frameworks fully support LINQ as well as INotify* interfaces, so they can work in WPF.

Personally I liked LightSpeed’s web-site and documentation more, but probably LLBLGen Pro has appropriate content as well. Cost wise they are not that bad, and LightSpeed exists in free version as well (with limit of 8 tables).

SQLite… SQL Server Express…

I have to choose a database engine for my project. After doing some preliminary research I left two options only – SQLite + System.Data.SQLite ADO.NET provider for it, and Microsoft’s SQL Server Express. SQL Server is much cooler and mature, and includes everything one could want from a database. SQLite, on the other hand, has much lower footprint – an important feature as the database will run in the embedded environment. It seems, that I will give SQLite a try, and lets see how it goes…