CODEWITHSUNDEEP
c#sql-serverparameter-passing
Nim
Nim

Reputation: 376

Read .sql script with parameters

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.

Upvotes: 0

Views: 2088

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062630

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");

Upvotes: 1

Related Questions