Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Using Generic Lists and SQL Server Views in Model-Glue

DZone's Guide to

Using Generic Lists and SQL Server Views in Model-Glue

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

Model-Glue comes with some generic messages that you can use to manipulate data from your database. Generic messages are only available when you are using an ORM adapter with Model-Glue (such as Reactor or Transfer). In general, "messages" are how Model-Glue refers to requests to the server. As a very brief overview, messages are broadcast by the event handler and then handled by one ore more of functions defined in the Model-Glue configuration. You can read more about this at The Model-Glue Event Lifecycle in Layman's Terms on Doug Boude's blog.

I find that I most often use the "ModelGlue.genericList" message which will return a ColdFusion query from the table specified. To use this feature in your event-handler, you simply add something like so under the "broadcasts":

<message name="ModelGlue.genericList">
<!-- The name of your table -->
<argument name="object" value="Users" />

<!-- The name of query variable where the data will be stored -->
<argument name="queryName" value="usersList" />
</message>

The "object" and "queryName" are the two required arguments you have to specify. However, you can also add an order by clause:
<message name="ModelGlue.genericList">
<!-- The name of your table -->
<argument name="object" value="Users" />

<!-- The name of query variable where the data will be stored -->
<argument name="queryName" value="usersList" />

<!-- The column by which the data will be ordered -->
<argument name="orderBy" value="userName" />
</message>

Or even a criteria to filter the data:
<message name="ModelGlue.genericList">
<!-- The name of your table -->
<argument name="object" value="Users" />

<!-- The name of query variable where the data will be stored -->
<argument name="queryName" value="usersList" />

<!-- The column by which the data will be ordered -->
<argument name="orderBy" value="userName" />

<!-- The column by which the data will be filtered -->
<argument name="criteria" value="userName" />
</message>
The "criteria" argument is the equivalent of using the "where" clause in your SQL query. It has to contain a name of a column already part of your table. It can also contain a hard coded value for the column value such as:
<!-- The column by which the data will be filtered -->
<argument name="criteria" value="userName=boyan" />


If a hard coded value is not specified for the "criteria" argument, Model-Glue looks for the data in the event object. In the current example, it will look for variable with the name "userName" in the event scope and use it's value to set the value of the "criteria".
The last, somewhat advanced option in the "genericList" message is the "gatewayMethod" argument. In case your query is more complicated than a simple table lookup (such as many joins, various arguments and special processing), you can use the "gatewayMethod" argument to specify what cfc method Model-Glue should use to get the "genericList". When using this argument, the method has to reside in the model gateway created for the object specified under the "object" argument. In this example, the "gatewayMethod" has to exists in the "Users" gateway. Here is what a gateway generated by Reactor looks like:

<cfcomponent hint="I am the mssql custom Gateway object for the jobs object.  I am generated, but not overwritten if I exist.  You are safe to edit me."
extends="usersGateway" >

</cfcomponent>

And here is a simple "gatewayMethod" to get a list of users based on a SQL Server stored procedure. The method takes a single numeric parameter called "showDeletedUsers" based on which it returns all users or only currently active users. Please note that this method is over simplified to show how the functionality works:
<cfcomponent hint="I am the mssql custom Gateway object for the jobs object.  I am generated, but not overwritten if I exist.  You are safe to edit me."
extends="usersGateway" >

<cffunction name="getUsers" access="public" output="false" returntype="query">
<cfargument name="showDeletedUsers" type="numeric" required="yes" />

<cfset var usersQuery = queryNew("") />

<cfstoredproc datasource="#_getConfig().getDsn()#" procedure="dbo.spGetUsers">
<cfprocparam
type="in" null="no" cfsqltype="cf_sql_int"
dbvarname="showDeletedUsers" value="#arguments.showDeletedUsers#" />

<cfprocresult name="usersQuery" resultset="1">
</cfstoredproc>

<cfreturn usersQuery />
</cffunction>

</cfcomponent>

To invoke this method through the "genericList", you simply use:
<message name="ModelGlue.genericList">
<!-- The name of your table -->
<argument name="object" value="Users" />

<!-- The name of query variable where the data will be stored -->
<argument name="queryName" value="usersList" />

<!-- The column by which the data will be ordered -->
<argument name="orderBy" value="userName" />

<!-- The column by which the data will be filtered -->
<argument name="criteria" value="showDeletedUsers=0" />

<!-- The custom method to use when retrieving the data -->
<argument name="gatewayMethod" value="getUsers" />
</message>

The last thing to remember is that you can use the "genericList" with SQL Server views and not just tables. Views are like a custom table that you have defined on the server. They can contain custom fields and data from multiple tables. So if you have a created a view called "vGetUsers" (instead of using the custom "gatewayMethod"), you can use in the "genericList" in the same manner as you would a table:
<message name="ModelGlue.genericList">
<!-- The name of your view -->
<argument name="object" value="vGetUsers" />

<!-- The name of query variable where the data will be stored -->
<argument name="queryName" value="usersList" />

<!-- The column by which the data will be ordered -->
<argument name="orderBy" value="userName" />
</message>

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}