Storing and executing queries efficiently?
Hello all, I've ran into situation where I'm really wondering about the best way to implement storing and executing queries. So our company has a central database where we track our production requests and different items, and normally I would just setup up my queries as sprocs on the database, but I've recently learned that when they do upgrades to the database, they destroy any ad hoc objects everytime. Now I'm trying to find another effecient alternative to storing my querys. I don't want to code them into my app as I don't want to recompile the app everytime I need to make a change. I figured the best option is to store them into some kind of XML template and then serialize the queries into a usuable object and then write a helper method to create a SqlCommand from that object. I'm definitely open to better suggestions if there are any: As far as the XML idea heres what I was thinking for a template:
Template 1:
Code Snippet
<?xml version="1.0" encoding="utf-8"?><!-- Template by Matthew Linscott -->
<toq:TechnicalOperationsQuery xmlns:toq="urn:TechnicalOperations TechnicalOperationsXmlQueryTemplate.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<toq:Name>[Name]</toq:Name>
<toq:CreateDate>2007-06-01</toq:CreateDate>
<toq:CreatedBy>[Created By]</toq:CreatedBy>
<toq:ModifiedDate>2007-06-01</toq:ModifiedDate>
<toq:ModifiedBy>[Modified By]</toq:ModifiedBy>
<toq:Sql>
[SQL Statements]
</toq:Sql>
<toq:Parameters>
<toq:Parameter>
<toq:Name>[Parameter Name]</toq:Name>
<toq:Type>[Parameter Type]</toq:Type>
<toq:DefaultValue>[Parameter Default Value]</toq:DefaultValue>
</toq:Parameter>
<toq:Parameter>
<toq:Name>[Parameter Name]</toq:Name>
<toq:Type>[Parameter Type]</toq:Type>
<toq:DefaultValue>[Parameter Default Value]</toq:DefaultValue>
</toq:Parameter>
<toq:Parameter>
<toq:Name>[Parameter Name]</toq:Name>
<toq:Type>[Parameter Type]</toq:Type>
<toq:DefaultValue>[Parameter Default Value]</toq:DefaultValue>
</toq:Parameter>
<toq:Parameter>
<toq:Name>[Parameter Name]</toq:Name>
<toq:Type>[Parameter Type]</toq:Type>
<toq:DefaultValue>[Parameter Default Value]</toq:DefaultValue>
</toq:Parameter>
</toq:Parameters>
</toq:TechnicalOperationsQuery>
or Template 2 Style:
Code Snippet
<?xml version="1.0" encoding="utf-8" ?><!-- Template by Matthew Linscott -->
<Query>
<Select>
<Columns>
<Column Alias="dai">DynamicAppItemID</Column>
<Column ColumnName="[ItemType]">
<Case>
<Column Alias="dai">DynamicAppID</Column>
<When>
<Expression>'B483C444-0C5E-4134-A6B0-7E2B7AEC1210'</Expression>
<Value>'ORA to ORA Item'</Value>
</When>
<When>
<Expression>'DF5A3CE0-F862-43ED-9A97-76E4212A0209'</Expression>
<Value>'Production Request Item'</Value>
</When>
<When>
<Expression>'6891020F-A14C-4333-8849-AFF1E8864518'</Expression>
<Value>'Data Item'</Value>
</When>
<When>
<Expression>'57492AF3-DAA3-4A34-8703-AD7F08132154'</Expression>
<Value>'Search Run Request Item'</Value>
</When>
<When>
<Expression>'08F0665C-55B0-4684-9C80-7274EA731352'</Expression>
<Value>'Import / Export Item'</Value>
</When>
<When>
<Expression>'3C4D8707-6CD7-471C-8242-5F2F1F05A9BB'</Expression>
<Value>'Special Instruction Item'</Value>
</When>
<When>
<Expression>'A0AB20CA-2423-4634-9104-F2797F66C0A5'</Expression>
<Value>'Data Release Item'</Value>
</When>
<When>
<Expression>'9776A866-0079-4275-A3C3-89E77D17A187'</Expression>
<Value>'Search Specification Item'</Value>
</When>
<When>
<Expression>'3FC2303E-8543-4BFB-B4FC-F1000C26947E'</Expression>
<Value>'IT Support Item'</Value>
</When>
<When>
<Expression>'2330C525-9161-4525-9388-283CFF9B1774'</Expression>
<Value>'ORA Support Item'</Value>
</When>
<When>
<Expression>'049B3420-8104-4B6F-BF8F-DC2AB242D6EB'</Expression>
<Value>'Expenses'</Value>
</When>
<Else/>
</Case>
</Column>
<Column Alias="dai">Title</Column>
<Column Alias="dai">CreatedOn</Column>
<Column Alias="cfvw" ColumnName="[Client]">CF_0</Column>
<Column Alias="cfvw" ColumnName="[CaseName]">CF_1</Column>
<Column Alias="cfvw" ColumnName="[CaseCode]">CF_3</Column>
<Column Alias="cfvw" ColumnName="[LeadAccountManager]">CF_16</Column>
<Column Alias="cfvw" ColumnName="[BackupAccountManager]">CF_17</Column>
<Column Alias="cfvw" ColumnName="[LeadProductionSpecialist]">CF_18</Column>
<Column Alias="cfvw" ColumnName="[BackupProductionSpecialist]">CF_7</Column>
</Columns>
<From>
<Table Alias="dai">DynamicAppItem</Table>
<Joins>
<Join Type="Inner">
<Table Alias="cfv">CustomFieldValues</Table>
<On>
<Condition Operator="=">
<FirstColumn Alias="dai">DynamicAppItemID</FirstColumn>
<SecondColumn Alias="cfv">ImplementerID</SecondColumn>
</Condition>
</On>
</Join>
<Join Type="Inner">
<Table Alias="w">Workspace</Table>
<On>
<Condition Operator="=">
<FirstColumn Alias="dai">ParentWorkspaceID</FirstColumn>
<SecondColumn Alias="w">WorkspaceID</SecondColumn>
</Condition>
</On>
</Join>
<Join Type="Inner">
<Table Alias="cfvw">CustomFieldValues</Table>
<On>
<Condition Operator="=">
<FirstColumn Alias="w">WorkspaceID</FirstColumn>
<SecondColumn Alias="cfvw">ImplementerID</SecondColumn>
</Condition>
</On>
</Join>
<Join Type="Inner">
<Table Alias="pw">Workspace</Table>
<On>
<Condition Operator="=">
<FirstColumn Alias="w">ParentWorkspaceID</FirstColumn>
<SecondColumn Alias="pw">WorkspaceID</SecondColumn>
</Condition>
</On>
</Join>
<Join Type="Inner">
<Table Alias="gpw">Workspace</Table>
<On>
<Condition Operator="=">
<FirstColumn Alias="pw">ParentWorkspaceID</FirstColumn>
<SecondColumn Alias="gpw">WorkspaceID</SecondColumn>
</Condition>
</On>
</Join>
</Joins>
</From>
<Where>
<Condition Operator="=">
<FirstColumn Alias="gpw">Title</FirstColumn>
<SecondColumn>'ClientProjects'</SecondColumn>
</Condition>
<Condition Operator="NOT IN">
<FirstColumn Alias="dai">DynamicApp</FirstColumn>
<SecondColumn>( '{3FC2303E-8543-4BFB-B4FC-F1000C26947E}','{2330C525-9161-4525-9388-283CFF9B1774}','{049B3420-8104-4B6F-BF8F-DC2AB242D6EB}','{BD9CE42D-DED3-442B-9B17-0EAD6ADC984F}')</SecondColumn>
</Condition>
</Where>
</Select>
</Query>
Thanks,
Matt
[9291 byte] By [MattLinscott] at [2008-1-4] 