Automatically loading object properties from SQL data sources
-
I haven't even dipped my toe in the ADO water yet, but even so, I thought I would bounce this idea off you guys. I have an object which contains 50 or so properties, of varied types likes strings, integers and the odd enumeration. I am getting pretty sick of the fact that everytime I think of a new property that can go on my object I have to write property acessors and then modify the database layout and then modify all the functions which add, delete and modify records. I am working on a method that, so long as the columns in my table bare the same names as my properties I can enumerate the list of properties on an object and load the value for that property by fetching if from the .Items collection of a data record using the property name as the column name. This means as soon as I add a new property and a new column my code effectively adapts to ensure that column or property are always loaded, saved or updated, without me having to modify any CRUD (create, read, update, delete) statements. You are gonna tell me ADO does this and makes you coffee and predicts all the properties you want on your object using the ADO.CrystalBall.SeeFuture interfaces aren't yah!? What do you think of the idea. I also thought it might be useful to create a new attribute which would exclude properties that you do not want to have handled in this way. Thoughts and input, or other nice to haves are most welcome, as is all the scorn that this idea will create hahaha 8-) Nursey
-
I haven't even dipped my toe in the ADO water yet, but even so, I thought I would bounce this idea off you guys. I have an object which contains 50 or so properties, of varied types likes strings, integers and the odd enumeration. I am getting pretty sick of the fact that everytime I think of a new property that can go on my object I have to write property acessors and then modify the database layout and then modify all the functions which add, delete and modify records. I am working on a method that, so long as the columns in my table bare the same names as my properties I can enumerate the list of properties on an object and load the value for that property by fetching if from the .Items collection of a data record using the property name as the column name. This means as soon as I add a new property and a new column my code effectively adapts to ensure that column or property are always loaded, saved or updated, without me having to modify any CRUD (create, read, update, delete) statements. You are gonna tell me ADO does this and makes you coffee and predicts all the properties you want on your object using the ADO.CrystalBall.SeeFuture interfaces aren't yah!? What do you think of the idea. I also thought it might be useful to create a new attribute which would exclude properties that you do not want to have handled in this way. Thoughts and input, or other nice to haves are most welcome, as is all the scorn that this idea will create hahaha 8-) Nursey
It works like a dream.... I just completely integrated this approach into my VisualTheme project, which serialises heaps of color information into an Access database. Whenever I call my AutoBuildSQL function it returns a sql statement which satisfies the columnname=fieldvalue syntax of a SQL UPDATE statement. This means I no longer have to write a function to specifically create a sql statement with all the column names and associated values, I just call my AutoBuildSQL statement and it returns a SQL statement with all the property names as SQL column names followed by an "=" followed by the property value... Here's an example call.... Public Sub UpdateTheme() If Not mConnection Is Nothing Then Dim lCmd As System.Data.OleDb.OleDbCommand = mConnection.CreateCommand() Dim lsSQL As String = AutoBuildSQL() Try lCmd.CommandText = "UPDATE Themes SET " + lsSQL + " Where Name = '" & Name & "' " lCmd.ExecuteNonQuery() Catch Fail As Exception MsgBox(Fail.ToString & Chr(13) & Chr(13) & lCmd.CommandText, MsgBoxStyle.OKOnly, "UpdateTheme") End Try lCmd.Dispose() End If End Sub The only caveat is that my real code doesn't save any properties that exist in the "THEME" property category (you know that wonderful attribute you can assign to properties). So as I said in my previous post, I should really create a new attribute called "AutoSQL" which will include the associated property in my automated data handling method, otherwise the property will be disregarded. If this makes any kind of sense to you guys, and I hope it does, coz you are far better and more experienced than me at this stuff, then let me know if you think this is a good idea then I'll create it as a proper OO approach and generate a new article to post on here for all those interested parties to download and reuse. Thanks for reading. Nursey
-
It works like a dream.... I just completely integrated this approach into my VisualTheme project, which serialises heaps of color information into an Access database. Whenever I call my AutoBuildSQL function it returns a sql statement which satisfies the columnname=fieldvalue syntax of a SQL UPDATE statement. This means I no longer have to write a function to specifically create a sql statement with all the column names and associated values, I just call my AutoBuildSQL statement and it returns a SQL statement with all the property names as SQL column names followed by an "=" followed by the property value... Here's an example call.... Public Sub UpdateTheme() If Not mConnection Is Nothing Then Dim lCmd As System.Data.OleDb.OleDbCommand = mConnection.CreateCommand() Dim lsSQL As String = AutoBuildSQL() Try lCmd.CommandText = "UPDATE Themes SET " + lsSQL + " Where Name = '" & Name & "' " lCmd.ExecuteNonQuery() Catch Fail As Exception MsgBox(Fail.ToString & Chr(13) & Chr(13) & lCmd.CommandText, MsgBoxStyle.OKOnly, "UpdateTheme") End Try lCmd.Dispose() End If End Sub The only caveat is that my real code doesn't save any properties that exist in the "THEME" property category (you know that wonderful attribute you can assign to properties). So as I said in my previous post, I should really create a new attribute called "AutoSQL" which will include the associated property in my automated data handling method, otherwise the property will be disregarded. If this makes any kind of sense to you guys, and I hope it does, coz you are far better and more experienced than me at this stuff, then let me know if you think this is a good idea then I'll create it as a proper OO approach and generate a new article to post on here for all those interested parties to download and reuse. Thanks for reading. Nursey
Sorry to keep posting on and harping on, but I'm just getting so many good feelings about this approach. Even though its pretty simpistic, the amount of coding time it saves is pretty significant. I am going to extend the idea so that it can automatically create tables in a database that match the layouy of the object, i.e it will create the appropriate columns with the matching types and data lengths as the properties on the object. My Theme object now barely has a need to know anything about the database to which it is linked, which makes me feel pretty good as it can be changed by people to use SQL Server, Access, XML or text files with very little effort. Nursey