Using C# to add stored procedures from solution folder to SQL Server database
<p>In my Visual Studio (C#) solution I have a folder named <code>stored procedures</code> with a large number of <code>.sql</code> files containing T-SQL.</p>

<p>Here's an example: </p>

<pre><code>-- =============================================
-- Author:      Foo Bar
-- Create date: 28-12-2016
-- Description: Create XrefUsers
-- =============================================
CREATE PROCEDURE [dbo].[spCreateXrefUsers] 
AS
BEGIN
</code></pre>

<p>I wish to add all these stored procedures to my database.</p>

<p>So I copied all the <code>.sql</code> files, renamed the copies to .tt (T4 file extension) and set <code>TextTemplatingFilePreprocessor</code> as customtool.</p>

<p>Now I wrote this code, to add the stored procedures:</p>

<pre><code>public bool AddStoredProcedures()
{
    try
    {
        //spCreateXrefUsers_sql.tt
        CreateStoredProcedure(new spCreateXrefUsers_sql().TransformText());
    }
    catch (Exception ex)
    {
        return false;
    }
    return true;
}

private static void CreateStoredProcedure(string spText)
{
    var connectionString = MigrateHelpers.GetConnectionStringQA();
    //var commandText = string.Format("EXEC sp_executesql {0}", spText);                

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        //SqlCommand command = new SqlCommand(commandText, connection);
        SqlCommand command = new SqlCommand(spText, connection);
        command.CommandType = CommandType.Text;
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}
</code></pre>

<p>But I get the following error:</p>

<blockquote>
  <p>Incorrect syntax near 'GO'.<br>
  'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.<br>
  Incorrect syntax near 'GO'.</p>
</blockquote>

<p>I have read: <a href="https://msdn.microsoft.com/en-us/library/ms188037.aspx" rel="nofollow noreferrer">GO (Transact-SQL)</a></p>

<p>So I tried: </p>

<pre><code>spText = spText.Replace("GO", "");
</code></pre>

<p>But that still leaves me with the</p>

<blockquote>
  <p>'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.</p>
</blockquote>

<p>error.</p>

<p>What should I change in my code, to be able to execute the T-SQL?</p>

<p>I am probably doing it all wrong. Please enlighten me!</p>

<p>Perhaps I should create a new <a href="https://msdn.microsoft.com/en-us/library/zxsa8hkf(v=vs.100).aspx" rel="nofollow noreferrer">SQL CLR database project</a></p>

<p>When I open the .SQL file in Visual Studio I am able to execute => create the Stored Procedure manually. Then I should be able to do it from code right?
<a href="https://i.stack.imgur.com/zM7qV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zM7qV.png" alt="enter image description here"></a></p>