Last time I used an SQL database with C# it all went through the LINQ plugin. It’s useful, but it always takes a good while to setup and requires me to remember a good chunk of code (like where to put the NotifyChanging and NotifyChanged parameters, what attributes are needed, etc). One of the things I discovered courtesy of this page (accessed 13/06/2017) is that the .NET framework provides some APIs to link to a back-end database.
Using these I’ve created a simple database management system in order to query and update a database through a WPF GUI application. The database I’ve created is comprised of 3 tables: a customer table, an order table, and a product table. The application allows the user to query the data in the database, update the data, and insert new records.
The actual code involves first creating a data connection, then a command, and finally executing the command. It’s the ability to manually stick in the SQL string you want to execute that makes it the most useful to me, but since each row being returned is comprised of different rows it means having to come up with some alternate mechanism to account for the results of each row. This means that the finished program is lot longer than a LINQ alternative.
I think I like this approach over the LINQ option; it means writing the SQL queries in by hand, but I think this is a fair trade-off for having a simpler setup. I suppose if I’d known about this simple API beforehand then I’d probably used back-end databases in some of my older projects.
One possible change to be made to this program would be to store the SQL queries in some external file so I don’t have to recompile the thing every time I want alternatew functionality. The idea I’ve come up with is to use an external XML file and then use some kind of regular-expression replacement mechanism (this has become something of a go-to thing for string replacement lately (worrying maybe, but if it works)) in order to programmatically alter the subject of the search. This would be necessary as most of the queries inherent in the project require either data insertion or mild changes of the same query.