Read .sql script with parameters

Question

I have a .sql script that contain the following code

-- Drop stored procedure if it already exists
USE Temp

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'Custom'
     AND SPECIFIC_NAME = N'RestoreVersion' 
)
   DROP PROCEDURE Custom.RestoreVersion
GO

CREATE PROCEDURE Custom.RestoreVersion
    @ID INT
AS
    SELECT * FROM [App].[Version] WHERE ID = @ID
    DROP PROCEDURE Custom.RestoreVersion
GO

EXECUTE Custom.RestoreVersion @ID = [ID from c# program]
GO

I would like to take this file and set the @ID parameter in c# and then run the sql script.

I want this script to live in a folder on my application but I can't get it to work.

I tried this.

Console.Write(Server_Name + Environment.NewLine);
connectSqlServer(Server_Name);

SqlDataReader rdr = null;

Console.WriteLine("\nGet Version\n");
try
{
    SqlCommand cmd = new SqlCommand("Custom.RestoreVersion", conn);

    cmd.CommandType = System.Data.CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@ID", ID));

    rdr = cmd.ExecuteReader();

    while (rdr.Read())
    {
        Console.WriteLine(Values);
    }
}
finally
{
    if (conn != null)
    {
        conn.Close();
    }
    if (rdr != null)
    {
        rdr.Close();
    }
}

However I am unable to get this to read the file from the Resources folder and enter the parameters to the database.

There is nothing in the code that would read from a file, so it's not surprising that it doesn't. Why do you think that it would? — Guffa
– Guffa, Commented Dec 31, 2014 at 13:00
I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Dec 31, 2014 at 13:00
Also the script only creates the stored procedure, it doesn't run it and therefore does not need the parameter to have a value. I don't see how the two sides are meant to match up — Rhumborl
– Rhumborl, Commented Dec 31, 2014 at 13:05
Yes the proc deletes itself as we don't want to leave the procedure on the server at the end this is obviously a small snippit of the full proc. I have edited the question turns out the copy and past didn't work properly so missed it out. I run it at the end. @Guffa sorry my question was how would I make it read from file as whenever I try and read from file I can only read to string or would you read to string and then do a replace in string? thanks duely noted. — Nim
– Nim, Commented Dec 31, 2014 at 13:15

Marc Gravell · Accepted Answer · 2014-12-31 13:26:21Z

There is no purpose whatsoever in creating a proc that deletes itself just to parameterize something; you can do that directly:

using(SqlCommand cmd = new SqlCommand(
    "SELECT * FROM [App].[Version] WHERE ID = @ID", conn))
{
    cmd.Parameters.Add(new SqlParameter("@ID", ID));
    using(var rdr = cmd.ExecuteReader()
    {
        while (rdr.Read())
        {
            var id = rdr.GetInt32(0);
            var name = rdr.GetString(1);
            // etc
        }
    }
}

or with a tool like dapper:

foreach(var row in conn.Query("SELECT * FROM [App].[Version] WHERE ID = @ID",
       new { ID })
{
    int id = row.Id;
    string name = row.Name
    // etc
}

From comments, it sounds like the problem is larger queries; there are several ways of doing that; the simplest is just code it in C# anyway, for example:

var sql = @"
-- this is a long query
declare @foo table(id int not null)
insert @foo (id)
select RangeId
from SomeTable
where Category = @category

select ... /* blah blah

lots more blah */";

cmd.CommandText = sql;
cmd.Parameters.AddWithValue("category", whatever);
// ...

Or alternatively, if you really want the query to be separate:

cmd.CommandText = ReadQueryFromResources("myquery");

Collectives™ on Stack Overflow

Read .sql script with parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related