+3
I need some smart objects to retrieve some lookup values from SQL. Each smart object will have two methods, 1 to return a list of ID's and values, and 1 to return a specific value matching a given ID.
It seems to me that the idea behind smart objects would be for me to have 4 different objects to handle the 4 different lookups I need to perform... but that would create multiple SQL connections, so is it good practise to put all these methods into one object?
Thanks
+8
You have several options.
The quick way would be as you noted to created a SmartObject for each lookup. Each lookup SmartObject would have its own table of data in the SmartBox and you would be done. I believe underneath the hood four sql connnections would be opened but they would managed and closed. I would have to have an understanding of your technical requirements to assess whether opening 4 connections one after another would be a problem.
You could try to create a single SmartObject using the SmartBox but you would need three fields on the SmartObject. The lookup key, value and type. Type being the type of lookup and that would have to be provided as a parameter to the query.
Now if hese look up tables, are on an existing database (do not care where sql, etc) you will need to create a custom SmartObject service that does not use data in the SmartBox. I blogged about how to do this really quickly here - http://k2distillery.blogspot.com/2007/11/create-custom-blackpearl-smartobject.html
Now if you do not want to write this service from scratch and you are using SQL Server on the BlackMarket site - check this out - http://k2underground.com/k2/ProjectHome.aspx?ProjectID=20
+3
Hiya
Thanks for getting back to me. I've already seen your blog about creating a custom SmartObject service and that was how I was going to hook up the object.
Yes the tables are in a custom database, and if it's possible to pass in a SQL table name as a parameter then maybe that is a neat way of doing it.
Thanks for your help
+8
Well my two cents is to not pass in a table name if you want to create a true services implementation. Passing a table name between different layers of your logical architecture assumes that the caller knows the underlying schema of the DB. The SmartObject layer is there to make that transparent and to hide it. But that is me just my opinion.
If you check out my blog, you can see that you can create a parameter to pass the table value through. Then you can build a sql query with that table name.
You should also check out that dynamic sql service I referenced in the blackmarket site. Then you will have to do no code! The you can create a composite SmartObject that can read off of multiple tables if the schema of those tables are the same. That is what I would do.
+3
Yeah that makes sense. If you can construct the query dynamically then a value is a better idea.
Downloaded that dynamic sql service project. Still going to write my own though, but it's nice to have a reference :)
+8
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.