snipt

Ctrl+h for KB shortcuts

C#

Generic Database Helper

public class OleDbHelper : DbHelper<OleDbConnection>
{
}

public class SqlDbHelper : DbHelper<SqlConnection>
{
}

public class DbHelper<TC> where TC : DbConnection
{
    public static DbDataReader ExecuteReader(string connectionString, string sql, params DbParameter[] pars)
    {
        return ExecuteCommand<TC, DbDataReader>(
            connectionString, sql,
            (Cx, Cd) =>
            {
                Cx.Open();
                return Cd.ExecuteReader(CommandBehavior.CloseConnection);
            },
            pars);
    }

    public static int Execute(string connectionString, string sql, params DbParameter[] pars)
    {
        return ExecuteCommand<TC, int>(
            connectionString, sql,
            (Cx, Cd) =>
            {
                Cx.Open();
                try
                {
                    return Cd.ExecuteNonQuery();
                }
                finally
                {
                    Cx.Close();
                }
            },
            pars);
    }

    public static T ExecuteScalar<T>(string connectionString, string sql, params DbParameter[] pars)
    {
        return ExecuteCommand<TC, T>(
            connectionString, sql,
            (Cx, Cd) =>
            {
                Cx.Open();
                try
                {
                    var Obj = Cd.ExecuteScalar();
                    if (Obj == DBNull.Value)
                        return default(T);
                    else
                        return (T)Obj;
                }
                finally
                {
                    Cx.Close();
                }
            },
            pars);
    }

    static T3 ExecuteCommand<T, T3>(string connectionString, string sql, Func<T, DbCommand, T3> action, params DbParameter[] pars) where T : DbConnection
    {
        T3 ReturnValue;

        var Cx = Activator.CreateInstance<T>();
        Cx.ConnectionString = connectionString;

        using (var Cd = Cx.CreateCommand())
        {
            Cd.CommandText = sql;

            if (pars != null && pars.Length > 0)
                Cd.Parameters.AddRange(pars);

            ReturnValue = action(Cx, Cd);
        }

        return ReturnValue;
    }
}
https://snipt.net/embed/577c384cbc1167ca92de9d52d8da3757/
/raw/577c384cbc1167ca92de9d52d8da3757/
577c384cbc1167ca92de9d52d8da3757
csharp
C#
84
2019-08-19T10:56:37
True
False
False
/api/public/snipt/23933/
my-own-usefull-multi-database-helper
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a> <a href="#L-29">29</a> <a href="#L-30">30</a> <a href="#L-31">31</a> <a href="#L-32">32</a> <a href="#L-33">33</a> <a href="#L-34">34</a> <a href="#L-35">35</a> <a href="#L-36">36</a> <a href="#L-37">37</a> <a href="#L-38">38</a> <a href="#L-39">39</a> <a href="#L-40">40</a> <a href="#L-41">41</a> <a href="#L-42">42</a> <a href="#L-43">43</a> <a href="#L-44">44</a> <a href="#L-45">45</a> <a href="#L-46">46</a> <a href="#L-47">47</a> <a href="#L-48">48</a> <a href="#L-49">49</a> <a href="#L-50">50</a> <a href="#L-51">51</a> <a href="#L-52">52</a> <a href="#L-53">53</a> <a href="#L-54">54</a> <a href="#L-55">55</a> <a href="#L-56">56</a> <a href="#L-57">57</a> <a href="#L-58">58</a> <a href="#L-59">59</a> <a href="#L-60">60</a> <a href="#L-61">61</a> <a href="#L-62">62</a> <a href="#L-63">63</a> <a href="#L-64">64</a> <a href="#L-65">65</a> <a href="#L-66">66</a> <a href="#L-67">67</a> <a href="#L-68">68</a> <a href="#L-69">69</a> <a href="#L-70">70</a> <a href="#L-71">71</a> <a href="#L-72">72</a> <a href="#L-73">73</a> <a href="#L-74">74</a> <a href="#L-75">75</a> <a href="#L-76">76</a> <a href="#L-77">77</a> <a href="#L-78">78</a> <a href="#L-79">79</a> <a href="#L-80">80</a> <a href="#L-81">81</a> <a href="#L-82">82</a> <a href="#L-83">83</a> <a href="#L-84">84</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">public</span> <span class="k">class</span> <span class="nc">OleDbHelper</span> <span class="p">:</span> <span class="n">DbHelper</span><span class="p">&lt;</span><span class="n">OleDbConnection</span><span class="p">&gt;</span> </span><span id="L-2"><a name="L-2"></a><span class="p">{</span> </span><span id="L-3"><a name="L-3"></a><span class="p">}</span> </span><span id="L-4"><a name="L-4"></a> </span><span id="L-5"><a name="L-5"></a><span class="k">public</span> <span class="k">class</span> <span class="nc">SqlDbHelper</span> <span class="p">:</span> <span class="n">DbHelper</span><span class="p">&lt;</span><span class="n">SqlConnection</span><span class="p">&gt;</span> </span><span id="L-6"><a name="L-6"></a><span class="p">{</span> </span><span id="L-7"><a name="L-7"></a><span class="p">}</span> </span><span id="L-8"><a name="L-8"></a> </span><span id="L-9"><a name="L-9"></a><span class="k">public</span> <span class="k">class</span> <span class="nc">DbHelper</span><span class="p">&lt;</span><span class="n">TC</span><span class="p">&gt;</span> <span class="k">where</span> <span class="n">TC</span> <span class="p">:</span> <span class="n">DbConnection</span> </span><span id="L-10"><a name="L-10"></a><span class="p">{</span> </span><span id="L-11"><a name="L-11"></a> <span class="k">public</span> <span class="k">static</span> <span class="n">DbDataReader</span> <span class="nf">ExecuteReader</span><span class="p">(</span><span class="kt">string</span> <span class="n">connectionString</span><span class="p">,</span> <span class="kt">string</span> <span class="n">sql</span><span class="p">,</span> <span class="k">params</span> <span class="n">DbParameter</span><span class="p">[]</span> <span class="n">pars</span><span class="p">)</span> </span><span id="L-12"><a name="L-12"></a> <span class="p">{</span> </span><span id="L-13"><a name="L-13"></a> <span class="k">return</span> <span class="n">ExecuteCommand</span><span class="p">&lt;</span><span class="n">TC</span><span class="p">,</span> <span class="n">DbDataReader</span><span class="p">&gt;(</span> </span><span id="L-14"><a name="L-14"></a> <span class="n">connectionString</span><span class="p">,</span> <span class="n">sql</span><span class="p">,</span> </span><span id="L-15"><a name="L-15"></a> <span class="p">(</span><span class="n">Cx</span><span class="p">,</span> <span class="n">Cd</span><span class="p">)</span> <span class="p">=&gt;</span> </span><span id="L-16"><a name="L-16"></a> <span class="p">{</span> </span><span id="L-17"><a name="L-17"></a> <span class="n">Cx</span><span class="p">.</span><span class="n">Open</span><span class="p">();</span> </span><span id="L-18"><a name="L-18"></a> <span class="k">return</span> <span class="n">Cd</span><span class="p">.</span><span class="n">ExecuteReader</span><span class="p">(</span><span class="n">CommandBehavior</span><span class="p">.</span><span class="n">CloseConnection</span><span class="p">);</span> </span><span id="L-19"><a name="L-19"></a> <span class="p">},</span> </span><span id="L-20"><a name="L-20"></a> <span class="n">pars</span><span class="p">);</span> </span><span id="L-21"><a name="L-21"></a> <span class="p">}</span> </span><span id="L-22"><a name="L-22"></a> </span><span id="L-23"><a name="L-23"></a> <span class="k">public</span> <span class="k">static</span> <span class="kt">int</span> <span class="nf">Execute</span><span class="p">(</span><span class="kt">string</span> <span class="n">connectionString</span><span class="p">,</span> <span class="kt">string</span> <span class="n">sql</span><span class="p">,</span> <span class="k">params</span> <span class="n">DbParameter</span><span class="p">[]</span> <span class="n">pars</span><span class="p">)</span> </span><span id="L-24"><a name="L-24"></a> <span class="p">{</span> </span><span id="L-25"><a name="L-25"></a> <span class="k">return</span> <span class="n">ExecuteCommand</span><span class="p">&lt;</span><span class="n">TC</span><span class="p">,</span> <span class="kt">int</span><span class="p">&gt;(</span> </span><span id="L-26"><a name="L-26"></a> <span class="n">connectionString</span><span class="p">,</span> <span class="n">sql</span><span class="p">,</span> </span><span id="L-27"><a name="L-27"></a> <span class="p">(</span><span class="n">Cx</span><span class="p">,</span> <span class="n">Cd</span><span class="p">)</span> <span class="p">=&gt;</span> </span><span id="L-28"><a name="L-28"></a> <span class="p">{</span> </span><span id="L-29"><a name="L-29"></a> <span class="n">Cx</span><span class="p">.</span><span class="n">Open</span><span class="p">();</span> </span><span id="L-30"><a name="L-30"></a> <span class="k">try</span> </span><span id="L-31"><a name="L-31"></a> <span class="p">{</span> </span><span id="L-32"><a name="L-32"></a> <span class="k">return</span> <span class="n">Cd</span><span class="p">.</span><span class="n">ExecuteNonQuery</span><span class="p">();</span> </span><span id="L-33"><a name="L-33"></a> <span class="p">}</span> </span><span id="L-34"><a name="L-34"></a> <span class="k">finally</span> </span><span id="L-35"><a name="L-35"></a> <span class="p">{</span> </span><span id="L-36"><a name="L-36"></a> <span class="n">Cx</span><span class="p">.</span><span class="n">Close</span><span class="p">();</span> </span><span id="L-37"><a name="L-37"></a> <span class="p">}</span> </span><span id="L-38"><a name="L-38"></a> <span class="p">},</span> </span><span id="L-39"><a name="L-39"></a> <span class="n">pars</span><span class="p">);</span> </span><span id="L-40"><a name="L-40"></a> <span class="p">}</span> </span><span id="L-41"><a name="L-41"></a> </span><span id="L-42"><a name="L-42"></a> <span class="k">public</span> <span class="k">static</span> <span class="n">T</span> <span class="n">ExecuteScalar</span><span class="p">&lt;</span><span class="n">T</span><span class="p">&gt;(</span><span class="kt">string</span> <span class="n">connectionString</span><span class="p">,</span> <span class="kt">string</span> <span class="n">sql</span><span class="p">,</span> <span class="k">params</span> <span class="n">DbParameter</span><span class="p">[]</span> <span class="n">pars</span><span class="p">)</span> </span><span id="L-43"><a name="L-43"></a> <span class="p">{</span> </span><span id="L-44"><a name="L-44"></a> <span class="k">return</span> <span class="n">ExecuteCommand</span><span class="p">&lt;</span><span class="n">TC</span><span class="p">,</span> <span class="n">T</span><span class="p">&gt;(</span> </span><span id="L-45"><a name="L-45"></a> <span class="n">connectionString</span><span class="p">,</span> <span class="n">sql</span><span class="p">,</span> </span><span id="L-46"><a name="L-46"></a> <span class="p">(</span><span class="n">Cx</span><span class="p">,</span> <span class="n">Cd</span><span class="p">)</span> <span class="p">=&gt;</span> </span><span id="L-47"><a name="L-47"></a> <span class="p">{</span> </span><span id="L-48"><a name="L-48"></a> <span class="n">Cx</span><span class="p">.</span><span class="n">Open</span><span class="p">();</span> </span><span id="L-49"><a name="L-49"></a> <span class="k">try</span> </span><span id="L-50"><a name="L-50"></a> <span class="p">{</span> </span><span id="L-51"><a name="L-51"></a> <span class="kt">var</span> <span class="n">Obj</span> <span class="p">=</span> <span class="n">Cd</span><span class="p">.</span><span class="n">ExecuteScalar</span><span class="p">();</span> </span><span id="L-52"><a name="L-52"></a> <span class="k">if</span> <span class="p">(</span><span class="n">Obj</span> <span class="p">==</span> <span class="n">DBNull</span><span class="p">.</span><span class="n">Value</span><span class="p">)</span> </span><span id="L-53"><a name="L-53"></a> <span class="k">return</span> <span class="nf">default</span><span class="p">(</span><span class="n">T</span><span class="p">);</span> </span><span id="L-54"><a name="L-54"></a> <span class="k">else</span> </span><span id="L-55"><a name="L-55"></a> <span class="nf">return</span> <span class="p">(</span><span class="n">T</span><span class="p">)</span><span class="n">Obj</span><span class="p">;</span> </span><span id="L-56"><a name="L-56"></a> <span class="p">}</span> </span><span id="L-57"><a name="L-57"></a> <span class="k">finally</span> </span><span id="L-58"><a name="L-58"></a> <span class="p">{</span> </span><span id="L-59"><a name="L-59"></a> <span class="n">Cx</span><span class="p">.</span><span class="n">Close</span><span class="p">();</span> </span><span id="L-60"><a name="L-60"></a> <span class="p">}</span> </span><span id="L-61"><a name="L-61"></a> <span class="p">},</span> </span><span id="L-62"><a name="L-62"></a> <span class="n">pars</span><span class="p">);</span> </span><span id="L-63"><a name="L-63"></a> <span class="p">}</span> </span><span id="L-64"><a name="L-64"></a> </span><span id="L-65"><a name="L-65"></a> <span class="k">static</span> <span class="n">T3</span> <span class="n">ExecuteCommand</span><span class="p">&lt;</span><span class="n">T</span><span class="p">,</span> <span class="n">T3</span><span class="p">&gt;(</span><span class="kt">string</span> <span class="n">connectionString</span><span class="p">,</span> <span class="kt">string</span> <span class="n">sql</span><span class="p">,</span> <span class="n">Func</span><span class="p">&lt;</span><span class="n">T</span><span class="p">,</span> <span class="n">DbCommand</span><span class="p">,</span> <span class="n">T3</span><span class="p">&gt;</span> <span class="n">action</span><span class="p">,</span> <span class="k">params</span> <span class="n">DbParameter</span><span class="p">[]</span> <span class="n">pars</span><span class="p">)</span> <span class="k">where</span> <span class="n">T</span> <span class="p">:</span> <span class="n">DbConnection</span> </span><span id="L-66"><a name="L-66"></a> <span class="p">{</span> </span><span id="L-67"><a name="L-67"></a> <span class="n">T3</span> <span class="n">ReturnValue</span><span class="p">;</span> </span><span id="L-68"><a name="L-68"></a> </span><span id="L-69"><a name="L-69"></a> <span class="kt">var</span> <span class="n">Cx</span> <span class="p">=</span> <span class="n">Activator</span><span class="p">.</span><span class="n">CreateInstance</span><span class="p">&lt;</span><span class="n">T</span><span class="p">&gt;();</span> </span><span id="L-70"><a name="L-70"></a> <span class="n">Cx</span><span class="p">.</span><span class="n">ConnectionString</span> <span class="p">=</span> <span class="n">connectionString</span><span class="p">;</span> </span><span id="L-71"><a name="L-71"></a> </span><span id="L-72"><a name="L-72"></a> <span class="k">using</span> <span class="p">(</span><span class="kt">var</span> <span class="n">Cd</span> <span class="p">=</span> <span class="n">Cx</span><span class="p">.</span><span class="n">CreateCommand</span><span class="p">())</span> </span><span id="L-73"><a name="L-73"></a> <span class="p">{</span> </span><span id="L-74"><a name="L-74"></a> <span class="n">Cd</span><span class="p">.</span><span class="n">CommandText</span> <span class="p">=</span> <span class="n">sql</span><span class="p">;</span> </span><span id="L-75"><a name="L-75"></a> </span><span id="L-76"><a name="L-76"></a> <span class="k">if</span> <span class="p">(</span><span class="n">pars</span> <span class="p">!=</span> <span class="k">null</span> <span class="p">&amp;&amp;</span> <span class="n">pars</span><span class="p">.</span><span class="n">Length</span> <span class="p">&gt;</span> <span class="m">0</span><span class="p">)</span> </span><span id="L-77"><a name="L-77"></a> <span class="n">Cd</span><span class="p">.</span><span class="n">Parameters</span><span class="p">.</span><span class="n">AddRange</span><span class="p">(</span><span class="n">pars</span><span class="p">);</span> </span><span id="L-78"><a name="L-78"></a> </span><span id="L-79"><a name="L-79"></a> <span class="n">ReturnValue</span> <span class="p">=</span> <span class="n">action</span><span class="p">(</span><span class="n">Cx</span><span class="p">,</span> <span class="n">Cd</span><span class="p">);</span> </span><span id="L-80"><a name="L-80"></a> <span class="p">}</span> </span><span id="L-81"><a name="L-81"></a> </span><span id="L-82"><a name="L-82"></a> <span class="k">return</span> <span class="n">ReturnValue</span><span class="p">;</span> </span><span id="L-83"><a name="L-83"></a> <span class="p">}</span> </span><span id="L-84"><a name="L-84"></a><span class="p">}</span> </span></pre></div> </td></tr></table>
c#, database