OK. It had to happen. Things have been cruising pretty well with .Net. My little experimental application is almost finished. Basically the idea was to do as many of the things I’d need to have to do in MixAction at a very elementary scale. Something more interesting and satisfying than fifty thousand sample projects trying out “stuff”.
I’ll post some screen shots of that next week. Any similarity to the MixAction screenshots seen on this blog or the MixAction website are purely intentional. 
Note though that this isn’t theatrical software. I’m not even sure if I’ll market it or not, but it’s certainly been very rewarding.
So – what was it that had to happen?
Data access. MixAction under Delphi uses a custom database format descended in the true spirit of the Delphi TDataset. This is one of the most powerful features of Delphi and the main reason I have stuck with Delphi for so long.
However, that’s not going to work so well under .Net unless I perform some really weird magic that I simply don’t want to get into right now. I have enough to do.
OK. That means ADO.Net. I figured – “How hard could it be?”
As it turns out it’s a piece of cake once you grasp it, which took me admittedly two days work, I’m ashamed to say, and experimentation.
One of the nice things under .Net is persistent application settings. Everything saved to a nice XML file. User preferences, application settings and so on. However the connection string for ADO.Net when saved using this method is read-only. Go figure.
I searched high and low for solutions for this and most of them were kludges, many were broken in VisualStudio 2008 and all of them were convoluted.
I did not want data access to be convoluted!
Here be dragons!
Repeatedly I saw VB6 programmers telling the .Net noobs to do it much inline with how things are done via good old VB6.
OK. Bloody useless to me. The last thing I wanted to do in 2008 was hand code field access to visual interface elements when I had perfectly good binding support under ADO.Net. Been there, done that – circa 1986-1995 under, gulp, DOS.
Nor did I want to grab the XML file and update during program initialization, experiments here were interesting but fallible.
One option I saw was to virtualize the connection string and trick the settings, but that to was anything but robust.
Finally, doing a manual edit of the connection string in the IDE meant every change to the configuration of the application saw the settings over written.
As it turns out the secret to using application settings for a connection string to a database, for my purposes (given this is a desktop implementation), short of forcing the installer to write the XML update at install time, is simply not to use it and hard code – using variables and not physical paths of course, the connection string at app startup.
Simple. Works fine it seems. In fact it’s working 100%.
So the next job is to locate a descent database file format for MixAction to use that works with ADO.Net. I’m not wrapped in SQLite, some of the .Net implementations I’ve played with seem to have memory leaks – quite an achievement as SQLite is pretty robust. Access is out for MixAction, it’s a tad too vague in terms of reliability and SQL Server, even the light edition, is rather large.
So I’ll keep looking for that, in the meantime I’ll add data access to this little application for the experience. After that I’m pleased to say I’ll be ready to fully implement MixAction again. Certainly looking forward to it, the beta testers are getting itchy!
Scott Kane
ROFLMAO Scott!!
Learning ADO.NET in two days? Well, the basics perhaps. It’s very extendable, and you should sink another day into the basics of LINQ if you’ve got the time to spare.
The power comes from data binding (no, not vb6 or delphi style data binding) through the Language Integrated Query and ASP.NET Entity Data Models. Google them and enjoy one of the most powerful selling points for .NET – the Microsoft Webcast! (search Microsoft events and sit back with a cuppa ‘joe).
Regarding the Connection String as read only in runtime, you need to use a workaround – or simply not use the Connection String enumerated type, just use a string and set this at runtime. It’s a shame there isn’t a mechanism (that I know of, anyway) in which Connection String can be changed at runtime.
All the best with your .NET adventures!
Hi Mike,
Thanks!
Yup. Two days gives me what I need for the projects I have planned. All I needed was a way to port the logic used in TDataset (in a sense) within .Net. So basic ADO is sufficient. When I have more time I’m keen on digging a lot deeper. There’s a number of things I want to do in respect of Azure et al that will need the more advanced knowledge.
Check out SQL Server Compact 3.5.
http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx
I’ve been using Nini for reading and writing to configuration files of any kind, ie ini and xml config files. http://sourceforge.net/projects/nini
Have you checked VistaDb for your database needs? I’ve heard a lot of good things about it. http://www.vistadb.net/
Hi Josh,
Thanks for that, I must admit I’d overlooked the compact edition, I’ll take a look at that. Thanks!
Hi Hector,
Thanks for that, I’ll have a look at Nini.
With Vistadb. Yes, I’ve heard good things about it too. It’s certainly on my list of final fives.
Thanks!
Maybe you could try “dBase”. nah. Before youre time.
I have used sqlite to good effect with C# in Visual Studio. It is small, fast and file based which makes it very portable and useful in single desktop applications. I used a helper file called SQLiteHelper.cs that might have some along with the stuff that lets you integrate it into Visual Studio. Will be interested in what you finally decide to use.
Hi Loren,
I’ve been doing quite a bit more research on the SQLite angle too, I’ll update next week on what I finally decide. I have found a better implementation than the ones I tried first that doesn’t have the memory leaks I mentioned. I’ll continue to experiment for now. Thanks for the reference to SQLiteHelper.cs, I’ll look at that further too.
Don’t consider this now that SQLlite and SQL/e are options, but I have previously used an Access .MDB file (renamed to .dat) as a data file and ADODB over JET to access it.
This works very well for proprietary data access. But if you have proprietary data files please consider XML as you can use the superfast XMLReader/XMLDataReader classes
(and BIND them via LINQ queries!)
Sorry about bringing LINQ up so often, I’m just getting into it for a new web app (my revised 30 day challenge) and the potential is amazing…
Hi Mike,
I’ve looked very closely at the XMLReader/XMLDataReader classes and I concur they are very nice. The problem for me though is that I need a single project file consisting of multiple tables, rather than several “tables” which is the way it would go with XML. The entire dataset has to be portable in MixAction. Breaking it up into seperate XML files means I end up packing it into an archive when a project is closed, then extracting it when it opens. That’s a bit of a problem in this context only in that it’s messy. If the app crashes I have XML strewen around etc, etc. I guess I’m looking for KISS here which is what I’m hoping for with SQLite in this instance.
While I’ve now got a comfortable handle on ADO.Net for my purposes, in the most basic context, there are still a lot of things I need to decide on in terms of method of reading data etc. Coming from Delphi where you hook into a TTable and operate directly on objects (no SQL) it’s quite a lot of extra coding I notice. I’m not complaining, it just takes getting used to, and of course I need to create my own classes in order to reduce that amount of coding relevant to what I’m doing.
EDIT REPLY: Scrub that nonsense about accessing fields as objects. I just worked out how to do it .Net style. So easy I’m rewarding myself with a kick in the posterior.
Ok so perhaps pure XML files isn’t the way to go for you, but you can simplify your database by using considerably less tables through careful use of the XML datatype. You could have a table to represent each act and bundle all the songs together into the XML and stream read from that. It saves having to “select” on a huge amount of information. Perhaps this isn’t the way forward if you’re embedding your actual media into your database, but I have personally queried Base64 encoded images out of an XML file stored in a SQL database (not SQL Lite though, it should still work) and the performance (compared to having to do the same through conventional table linking) was substantial.
Anyway, that’s a subject for another time! Good luck with your data access layer
By the way… I can rate your post but not your comments. Try logging out and see..
Hi Mike,
“Ok so perhaps pure XML files isn’t the way to go for you”
Or maybe…
Replied via private email.
“By the way… I can rate your post but not your comments. Try logging out and see.. ”
Hi Mike. They were working, the comment rating widget got updated to a new version today. Looks like they broke something…
I believe that pure ADO.Net is the way to go with database access. I am using this application that makes it a breeze. I do not have to write ADO.Net code and it generates only what I need with zero 3rd party dependencies. I can build my SQL with drag and drop and I can map to my objects without any restrictions. It is an easy to use IDE that generates .csproj fies that can be used in Studio 2008 and 2005. If you want to check it out, go to http://www.orasissoftware.com. Generate your code and throw the IDE away if you do not like it. They gave me a month trial. ( Enough to do my work
)
Personally I would use XML if at all possible for a task like this. .net drips XML. It begs you to use it and if it can be made to fit for this project it’d make things a lot easier to maintain too.
If this prokect were mine I’d be looking at XML.
It’s open.
It’s clean.
It’s easy to maintain and self upgrading.
It’s a standard on more than one platform.
It’s human readable.
Jacqi