snipt

Ctrl+h for KB shortcuts

C#

Using a Custom Action to Create a Database at Installation

public class VbDeployInstaller
{

    System.Data.SqlClient.SqlConnection masterConnection = new System.Data.SqlClient.SqlConnection();
    public VbDeployInstaller()
        : base()
    {

        //This call is required by the Component Designer.
        InitializeComponent();

        //Add initialization code after the call to InitializeComponent

    }

    private string GetSql(string Name)
    {

        try
        {
            // Gets the current assembly.
            Assembly Asm = Assembly.GetExecutingAssembly();

            // Resources are named using a fully qualified name.
            Stream strm = Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name);

            // Reads the contents of the embedded file.
            StreamReader reader = new StreamReader(strm);
            return reader.ReadToEnd();

        }
        catch (Exception ex)
        {
            Interaction.MsgBox("In GetSQL: " + ex.Message);
            throw ex;
        }
    }

    private void ExecuteSql(string DatabaseName, string Sql)
    {
        System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand(Sql, masterConnection);

        // Initialize the connection, open it, and set it to the "master" database
        masterConnection.ConnectionString = My.Settings.masterConnectionString;
        Command.Connection.Open();
        Command.Connection.ChangeDatabase(DatabaseName);
        try
        {
            Command.ExecuteNonQuery();
        }
        finally
        {
            // Closing the connection should be done in a Finally block
            Command.Connection.Close();
        }
    }

    protected void AddDBTable(string strDBName)
    {
        try
        {
            // Creates the database.
            ExecuteSql("master", "CREATE DATABASE " + strDBName);

            // Creates the tables.
            ExecuteSql(strDBName, GetSql("sql.txt"));

        }
        catch (Exception ex)
        {
            // Reports any errors and abort.
            Interaction.MsgBox("In exception handler: " + ex.Message);
            throw ex;
        }
    }


    public override void Install(System.Collections.IDictionary stateSaver)
    {
        base.Install(stateSaver);
        AddDBTable(this.Context.Parameters.Item("dbname"));
    }

}
https://snipt.net/embed/f62af64f4a618097cafacf8ca9a43495/
/raw/f62af64f4a618097cafacf8ca9a43495/
f62af64f4a618097cafacf8ca9a43495
csharp
C#
84
2019-06-27T04:23:06
True
False
False
/api/public/snipt/37619/
using-a-custom-action-to-create-a-database-at-installation
<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">VbDeployInstaller</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><span id="L-4"><a name="L-4"></a> <span class="n">System</span><span class="p">.</span><span class="n">Data</span><span class="p">.</span><span class="n">SqlClient</span><span class="p">.</span><span class="n">SqlConnection</span> <span class="n">masterConnection</span> <span class="p">=</span> <span class="k">new</span> <span class="n">System</span><span class="p">.</span><span class="n">Data</span><span class="p">.</span><span class="n">SqlClient</span><span class="p">.</span><span class="n">SqlConnection</span><span class="p">();</span> </span><span id="L-5"><a name="L-5"></a> <span class="k">public</span> <span class="nf">VbDeployInstaller</span><span class="p">()</span> </span><span id="L-6"><a name="L-6"></a> <span class="p">:</span> <span class="k">base</span><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="c1">//This call is required by the Component Designer.</span> </span><span id="L-10"><a name="L-10"></a> <span class="n">InitializeComponent</span><span class="p">();</span> </span><span id="L-11"><a name="L-11"></a> </span><span id="L-12"><a name="L-12"></a> <span class="c1">//Add initialization code after the call to InitializeComponent</span> </span><span id="L-13"><a name="L-13"></a> </span><span id="L-14"><a name="L-14"></a> <span class="p">}</span> </span><span id="L-15"><a name="L-15"></a> </span><span id="L-16"><a name="L-16"></a> <span class="k">private</span> <span class="kt">string</span> <span class="nf">GetSql</span><span class="p">(</span><span class="kt">string</span> <span class="n">Name</span><span class="p">)</span> </span><span id="L-17"><a name="L-17"></a> <span class="p">{</span> </span><span id="L-18"><a name="L-18"></a> </span><span id="L-19"><a name="L-19"></a> <span class="k">try</span> </span><span id="L-20"><a name="L-20"></a> <span class="p">{</span> </span><span id="L-21"><a name="L-21"></a> <span class="c1">// Gets the current assembly.</span> </span><span id="L-22"><a name="L-22"></a> <span class="n">Assembly</span> <span class="n">Asm</span> <span class="p">=</span> <span class="n">Assembly</span><span class="p">.</span><span class="n">GetExecutingAssembly</span><span class="p">();</span> </span><span id="L-23"><a name="L-23"></a> </span><span id="L-24"><a name="L-24"></a> <span class="c1">// Resources are named using a fully qualified name.</span> </span><span id="L-25"><a name="L-25"></a> <span class="n">Stream</span> <span class="n">strm</span> <span class="p">=</span> <span class="n">Asm</span><span class="p">.</span><span class="n">GetManifestResourceStream</span><span class="p">(</span><span class="n">Asm</span><span class="p">.</span><span class="n">GetName</span><span class="p">().</span><span class="n">Name</span> <span class="p">+</span> <span class="s">&quot;.&quot;</span> <span class="p">+</span> <span class="n">Name</span><span class="p">);</span> </span><span id="L-26"><a name="L-26"></a> </span><span id="L-27"><a name="L-27"></a> <span class="c1">// Reads the contents of the embedded file.</span> </span><span id="L-28"><a name="L-28"></a> <span class="n">StreamReader</span> <span class="n">reader</span> <span class="p">=</span> <span class="k">new</span> <span class="n">StreamReader</span><span class="p">(</span><span class="n">strm</span><span class="p">);</span> </span><span id="L-29"><a name="L-29"></a> <span class="k">return</span> <span class="n">reader</span><span class="p">.</span><span class="n">ReadToEnd</span><span class="p">();</span> </span><span id="L-30"><a name="L-30"></a> </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">catch</span> <span class="p">(</span><span class="n">Exception</span> <span class="n">ex</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="n">Interaction</span><span class="p">.</span><span class="n">MsgBox</span><span class="p">(</span><span class="s">&quot;In GetSQL: &quot;</span> <span class="p">+</span> <span class="n">ex</span><span class="p">.</span><span class="n">Message</span><span class="p">);</span> </span><span id="L-35"><a name="L-35"></a> <span class="k">throw</span> <span class="n">ex</span><span class="p">;</span> </span><span id="L-36"><a name="L-36"></a> <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><span id="L-39"><a name="L-39"></a> <span class="k">private</span> <span class="k">void</span> <span class="nf">ExecuteSql</span><span class="p">(</span><span class="kt">string</span> <span class="n">DatabaseName</span><span class="p">,</span> <span class="kt">string</span> <span class="n">Sql</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 class="n">System</span><span class="p">.</span><span class="n">Data</span><span class="p">.</span><span class="n">SqlClient</span><span class="p">.</span><span class="n">SqlCommand</span> <span class="n">Command</span> <span class="p">=</span> <span class="k">new</span> <span class="n">System</span><span class="p">.</span><span class="n">Data</span><span class="p">.</span><span class="n">SqlClient</span><span class="p">.</span><span class="n">SqlCommand</span><span class="p">(</span><span class="n">Sql</span><span class="p">,</span> <span class="n">masterConnection</span><span class="p">);</span> </span><span id="L-42"><a name="L-42"></a> </span><span id="L-43"><a name="L-43"></a> <span class="c1">// Initialize the connection, open it, and set it to the &quot;master&quot; database</span> </span><span id="L-44"><a name="L-44"></a> <span class="n">masterConnection</span><span class="p">.</span><span class="n">ConnectionString</span> <span class="p">=</span> <span class="n">My</span><span class="p">.</span><span class="n">Settings</span><span class="p">.</span><span class="n">masterConnectionString</span><span class="p">;</span> </span><span id="L-45"><a name="L-45"></a> <span class="n">Command</span><span class="p">.</span><span class="n">Connection</span><span class="p">.</span><span class="n">Open</span><span class="p">();</span> </span><span id="L-46"><a name="L-46"></a> <span class="n">Command</span><span class="p">.</span><span class="n">Connection</span><span class="p">.</span><span class="n">ChangeDatabase</span><span class="p">(</span><span class="n">DatabaseName</span><span class="p">);</span> </span><span id="L-47"><a name="L-47"></a> <span class="k">try</span> </span><span id="L-48"><a name="L-48"></a> <span class="p">{</span> </span><span id="L-49"><a name="L-49"></a> <span class="n">Command</span><span class="p">.</span><span class="n">ExecuteNonQuery</span><span class="p">();</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="k">finally</span> </span><span id="L-52"><a name="L-52"></a> <span class="p">{</span> </span><span id="L-53"><a name="L-53"></a> <span class="c1">// Closing the connection should be done in a Finally block</span> </span><span id="L-54"><a name="L-54"></a> <span class="n">Command</span><span class="p">.</span><span class="n">Connection</span><span class="p">.</span><span class="n">Close</span><span class="p">();</span> </span><span id="L-55"><a name="L-55"></a> <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><span id="L-58"><a name="L-58"></a> <span class="k">protected</span> <span class="k">void</span> <span class="nf">AddDBTable</span><span class="p">(</span><span class="kt">string</span> <span class="n">strDBName</span><span class="p">)</span> </span><span id="L-59"><a name="L-59"></a> <span class="p">{</span> </span><span id="L-60"><a name="L-60"></a> <span class="k">try</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="c1">// Creates the database.</span> </span><span id="L-63"><a name="L-63"></a> <span class="n">ExecuteSql</span><span class="p">(</span><span class="s">&quot;master&quot;</span><span class="p">,</span> <span class="s">&quot;CREATE DATABASE &quot;</span> <span class="p">+</span> <span class="n">strDBName</span><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="c1">// Creates the tables.</span> </span><span id="L-66"><a name="L-66"></a> <span class="n">ExecuteSql</span><span class="p">(</span><span class="n">strDBName</span><span class="p">,</span> <span class="n">GetSql</span><span class="p">(</span><span class="s">&quot;sql.txt&quot;</span><span class="p">));</span> </span><span id="L-67"><a name="L-67"></a> </span><span id="L-68"><a name="L-68"></a> <span class="p">}</span> </span><span id="L-69"><a name="L-69"></a> <span class="k">catch</span> <span class="p">(</span><span class="n">Exception</span> <span class="n">ex</span><span class="p">)</span> </span><span id="L-70"><a name="L-70"></a> <span class="p">{</span> </span><span id="L-71"><a name="L-71"></a> <span class="c1">// Reports any errors and abort.</span> </span><span id="L-72"><a name="L-72"></a> <span class="n">Interaction</span><span class="p">.</span><span class="n">MsgBox</span><span class="p">(</span><span class="s">&quot;In exception handler: &quot;</span> <span class="p">+</span> <span class="n">ex</span><span class="p">.</span><span class="n">Message</span><span class="p">);</span> </span><span id="L-73"><a name="L-73"></a> <span class="k">throw</span> <span class="n">ex</span><span class="p">;</span> </span><span id="L-74"><a name="L-74"></a> <span class="p">}</span> </span><span id="L-75"><a name="L-75"></a> <span class="p">}</span> </span><span id="L-76"><a name="L-76"></a> </span><span id="L-77"><a name="L-77"></a> </span><span id="L-78"><a name="L-78"></a> <span class="k">public</span> <span class="k">override</span> <span class="k">void</span> <span class="nf">Install</span><span class="p">(</span><span class="n">System</span><span class="p">.</span><span class="n">Collections</span><span class="p">.</span><span class="n">IDictionary</span> <span class="n">stateSaver</span><span class="p">)</span> </span><span id="L-79"><a name="L-79"></a> <span class="p">{</span> </span><span id="L-80"><a name="L-80"></a> <span class="k">base</span><span class="p">.</span><span class="n">Install</span><span class="p">(</span><span class="n">stateSaver</span><span class="p">);</span> </span><span id="L-81"><a name="L-81"></a> <span class="n">AddDBTable</span><span class="p">(</span><span class="k">this</span><span class="p">.</span><span class="n">Context</span><span class="p">.</span><span class="n">Parameters</span><span class="p">.</span><span class="n">Item</span><span class="p">(</span><span class="s">&quot;dbname&quot;</span><span class="p">));</span> </span><span id="L-82"><a name="L-82"></a> <span class="p">}</span> </span><span id="L-83"><a name="L-83"></a> </span><span id="L-84"><a name="L-84"></a><span class="p">}</span> </span></pre></div> </td></tr></table>
installer