MySQL variable setting through connection string or connection object?
<p>This is a contrived example however I have simplified it for ease of explanation.</p>
<blockquote>
<p>Please see my update at the bottom before investing too much of your
time!</p>
</blockquote>
<h2>Background</h2>
<p>I have some (a lot of) code that ordinarily queries my DB as follows:</p>
<p><code>SELECT name FROM sites where IsLive=1;</code></p>
<p>My challenge is to, under certain conditions, return the <em>full</em> list of sites, essentially</p>
<p><code>SELECT name from sites;</code></p>
<p>I do not wish to modify the actual C# code issuing the SQL (although I can do if I have to in order to achieve my goal which is purely for demonstration purposes).<br />
Therefore in order to leave as much untouched as possible my thoughts are to insert a <code>database-proxy-view</code> called <strong>site</strong> that returns the data dependent on a <code>control variable</code></p>
<h2>Method</h2>
<ol>
<li>Rename existing <code>site</code> table to <code>site_table</code></li>
<li>Create a new <strong>view</strong> named <code>site</code> that the C# code now unknowingly targets and which returns the (possibly filtered) details from <code>site_table</code> according to the <code>control variable</code> value (Note a limitation on variables in views meant I had to create a function in order to demonstrate this - see <a href="http://dev.mysql.com/doc/refman/5.7/en/create-view.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.7/en/create-view.html</a> wrt error 1351)</li>
</ol>
<h2>Changes made</h2>
<ol>
<li><code>ALTER TABLE site RENAME TO site_table;</code></li>
<li><code>CREATE FUNCTION controlVariableFn() RETURNS VARCHAR(16) RETURN @controlVariable;</code></li>
<li><code>CREATE OR REPLACE VIEW site AS SELECT * from site_table WHERE (IsLive = 1 OR controlVariableFn() = 'SHOWALL');</code></li>
</ol>
<p>The above statements are <strong>ugly</strong> but achieve the result I want, however my problem is to dynamically pass through <code>controlVariable</code> without changing my main SQL queries being sent.</p>
<h1>My Question</h1>
<p>Is there a way to (ideally as I am creating my connection object) define the <code>controlVariable</code> outside the actual SQL to be executed but which the <strong>View</strong> can still access similar to the above as though it had been supplied as a regular user variable parameter to the query?</p>
<p>so the code would look something like</p>
<pre><code>var connectionString = &quot;Server=localhost;User ID=un;Password=pw;Database=dbname;....&quot;;
DbConnection db = new MySql.Data.MySqlClient.MySqlConnection
                  (connectionString, &quot;controlVariable=SHOWALL&quot;);
var results = db.Query&lt;Site&gt;(&quot;SELECT * FROM site;&quot;);
</code></pre>
<p>(I understand that this would not be a smart permanent solution)</p>
<hr />
<blockquote>
<p><strong>Update</strong>
My preferred solution as outlined above will not work for me as once I get into my data access layer as the results set will
essentially be filtered again back to the original set.  There are some circumstances where it
could work; it would depend on the SQL issued (e.g. when collapsing a
results set down instead of trying to expand a results set as I was
trying to do here).
In that regard I am no longer looking for an answer here but will leave it for posterity as a preferred option and as per the guidelines - thanks anyway.</p>
</blockquote>