Populating Objects from Db
-
Hi What do you recomend to populate custom objects from the database? Let's say i have a "Product" object, and i have a Products table, each product has a "name", "Id","Stock","Type" etc. Some times i need to get a couple of products while other times i need a large list of them, So i build a Singleton "Products" class that has a "GetProduct(id)" method and a private Dictionary of products, each time a product is requested it first check if the product is in the dictionary, and if its not it calls a New Product(id) and add it to the dictionary. Class Products Public Shared Function GetProduct(id as integer) as product if Cache.containskey(id) then return Cache(id) return new product(id) end function End class Then i have a constructor in the "product" Class that looks like this: Sub New(id as integer) Dim Comm as new sqlcommand("Select * from products where id=@id",ConnString) comm.parameters.addwithvalue("@id",id) Dim Reader as sqldatareader=comm.executereader reader.read idvalue=reader("Id") Namevalue=reader("Name") end sub The problem i think is that when i need to get a large number of products i am making a lot of querys to the database, so i thougt of doing somenthing like this: Class Product Private Sub PopulateFromReader(r as datareader) idvalue=reader("Id") Namevalue=reader("Name") end sub Sub New(r as datareader) me.PopulateFromReader(r) end sub Sub New(id as integer) Dim Comm as new sqlcommand("Select * from products where id=@id",ConnString) comm.parameters.addwithvalue("@id",id) Dim Reader as sqldatareader=comm.executereader me.PopulateFromReader(r) end sub End class And then in the private constructor of the Singleton "Products" class i do somenthing like this: Class Products Private Cache as dictionary(of Integer,Product) Private Sub New Dim Comm as new sqlcommand("Select * from products",ConnString) Dim Reader as sqldatareader=comm.executereader do while reader.read Cache.add(reader("id"),new product(reader) loop end sub end class So, what do you think i should do? is it ok? is there a "Correct" way to do it? Thanks in advance PS: Sorry my english!
-
Hi What do you recomend to populate custom objects from the database? Let's say i have a "Product" object, and i have a Products table, each product has a "name", "Id","Stock","Type" etc. Some times i need to get a couple of products while other times i need a large list of them, So i build a Singleton "Products" class that has a "GetProduct(id)" method and a private Dictionary of products, each time a product is requested it first check if the product is in the dictionary, and if its not it calls a New Product(id) and add it to the dictionary. Class Products Public Shared Function GetProduct(id as integer) as product if Cache.containskey(id) then return Cache(id) return new product(id) end function End class Then i have a constructor in the "product" Class that looks like this: Sub New(id as integer) Dim Comm as new sqlcommand("Select * from products where id=@id",ConnString) comm.parameters.addwithvalue("@id",id) Dim Reader as sqldatareader=comm.executereader reader.read idvalue=reader("Id") Namevalue=reader("Name") end sub The problem i think is that when i need to get a large number of products i am making a lot of querys to the database, so i thougt of doing somenthing like this: Class Product Private Sub PopulateFromReader(r as datareader) idvalue=reader("Id") Namevalue=reader("Name") end sub Sub New(r as datareader) me.PopulateFromReader(r) end sub Sub New(id as integer) Dim Comm as new sqlcommand("Select * from products where id=@id",ConnString) comm.parameters.addwithvalue("@id",id) Dim Reader as sqldatareader=comm.executereader me.PopulateFromReader(r) end sub End class And then in the private constructor of the Singleton "Products" class i do somenthing like this: Class Products Private Cache as dictionary(of Integer,Product) Private Sub New Dim Comm as new sqlcommand("Select * from products",ConnString) Dim Reader as sqldatareader=comm.executereader do while reader.read Cache.add(reader("id"),new product(reader) loop end sub end class So, what do you think i should do? is it ok? is there a "Correct" way to do it? Thanks in advance PS: Sorry my english!
You could try the free edition of Diamond Binding by following the link in my signature. This will pretty much do what you are after, without you having to write any code. If you are really insistant on DIY you could tag all your fields with attributes and then write a templated base class that uses reflection to populate the fields. If you only have a few tables, it will probably be easier to not use a data layer at all to be honest. What you've written will work, but you'll need to remember to keep your cache up to date when you insert/update records.
Mark Churchill Director Dunn & Churchill
-
You could try the free edition of Diamond Binding by following the link in my signature. This will pretty much do what you are after, without you having to write any code. If you are really insistant on DIY you could tag all your fields with attributes and then write a templated base class that uses reflection to populate the fields. If you only have a few tables, it will probably be easier to not use a data layer at all to be honest. What you've written will work, but you'll need to remember to keep your cache up to date when you insert/update records.
Mark Churchill Director Dunn & Churchill
Mark Churchill wrote:
If you are really insistant on DIY you could tag all your fields with attributes and then write a templated base class that uses reflection to populate the fields. If you only have a few tables, it will probably be easier to not use a data layer at all to be honest.
You might be interested an article here on CP, the Tale of the Three Monkeys or something like that, basically outlining pros and cons of using hand-written DALs, Reflection generated DALs and thirdly (whole point of the article) generated custom code using
Reflection.EmitandDynamicMethods. Just search google withmonkey site:codeproject.comand it should be first link.