1

EDIT: Judging from the comments, I have been unclear in what I am trying to achieve. I'll try from another angle.

I have been developing sprocs for a number of years. I increasingly feel that choosing between SQL embedded in C# code and sprocs are both bad choices. I know that many people will disagree and that's fine. I didn't elaborate on this in the question to avoid having the discussion be about sprocs or not :-) .

As an experiment, I have tried embedding .sql files in my project in Visual Studio. Literally, in my project tree I have files with the .sql extension and the Build Action set to Embedded Resource.

That way I can edit the SQL query code from within VS and even run/execute it from within VS, without running the project. I love that. The actual code that I am working on right now, is akin to a "product list" with paging and multiple ordering and filtering options. The SQL query has parameters like @skip, @search, etc. This means that if I try to run/execute it from within VS (specifically by pressing CTRL + SHIFT + e or selecting "Execute" from the "SQL" menu in VS) these parameters are missing (of course), since they are meant to be provided as SqlParameter at runtime. I understand why this happens and by no way intend to imply that either VS or SSMS is bugged. I am merely looking for a way to "tell" VS that "When I execute this query from within VS, I intend for @skip to be 10". I (perhaps mistakenly) assumed that the reason that VS had IntelliSense support and Execute support for .sql files was to support a scenario akin to what I am trying).

I was just hoping someone else was doing the same and had a clever way/addon/trick to support it.

EDIT END

In an project in Visual Studio 2017, I have a number of .sql files which are embedded into the application when built.
While editing the files in VS, I can conveniently connect to a SQL server and execute the query.
However, if the query has parameters like this:

SELECT * FROM Employee WHERE ID=@ID;

The execution, from within Visual Studio, fails with 

Must declare the scalar variable "@ID".

This can be "fixed" by adding a line at the top of the script/file, so it looks like:

DECLARE @ID int = 123;
SELECT * FROM Employee WHERE ID=@ID;

However, now it doesn't work when called from within the code like this (using Dapper, it's not relevant to the question, but explains the syntax):

var emp = conn.Query<Employee>( sql, new { ID=123 } );

I was hoping that either I could specify the value of ID somewhere not in the file, or specify some part of the file which VS would execute, but would be ignored later when calling from code.

EDIT: To be clear, I can craft the SQL so that it works flawlessly from within Visual Studio or at runtime, but I cannot have both. I was hoping there was some neat hack, a VS addon or just some VS feature that I was just missing. I have considered adding a simple pre-processor so I can do something like this:

--if DEBUG
DECLARE @ID int = 123;
--endif
SELECT * FROM Employee WHERE ID=@ID;

And then I could add it to my function that loads the embedded resource.

Improve this question
15
  • Can't you store the SQL on the server as Stored Procedures? Commented Dec 6, 2017 at 11:29
  • I could but would rather avoid it. We have yet to find a satisfying solution to DB version control, so I would rather not store code in the DB. Besides, how would that help me edit and execute it from within VS? Commented Dec 6, 2017 at 11:33
  • Possible duplicate of Executing query with parameters Commented Dec 6, 2017 at 12:07
  • @AlexK. stored procedures are an unrelated discussion; sometimes valid, sometimes not - but simply: unrelated: blog.marcgravell.com/2017/12/… (see: Part The Second) Commented Dec 6, 2017 at 12:07
  • "However, now it doesn't work when called from within the code like this" - what happens? is sql the value that you expect it to be? what you show should work fine as long as sql is the string like the one in your question. Dapper doesn't care where the string comes from. It only cares what the string contains - so: what is the value of sql ? Commented Dec 6, 2017 at 12:09

3 Answers 3

Reset to default
1

Pardon if I understood the question wrongly. Could it be you're looking for something like this? The query approach actually doesn't matter, it more looks like the issue is that you declare it like @ID when you actually have to add the replacement to the script itself

con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM Employee WHERE ID=@ID;";
cmd.Parameters.Add("@ID", actualID); <--this is is why declaring doesn't work - You can declare it, but you also need to add it to the query
SqlDataReader rdr = cmd.ExecuteReader();
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Note: everything here is already what Dapper (the code shown in the question) would already have done. So while there's nothing wrong with the code in this answer - it also doesn't change anything from what is in the question
My point is, the SQL in your example is not executable from within VS or SSMS, it will complain that @ID is undeclared. In my actual code, CommandText is maybe 50 lines long and resides in a file with the .sql extension. I can handily execute this file against Sql Server from within VS via the SQL -> Execute menu, but only if there are no undeclared parameters.
1

SQLCMD supports environment variables.

https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-use-scripting-variables

VS and VS Code have an SQLCMD mode.

SQLCMD variables can be set at the visual studio project level.

https://documentation.red-gate.com/sca/developing-databases/working-with-the-visual-studio-extension/advanced-scenarios-for-visual-studio/adding-a-sqlcmd-variable-to-your-project-in-visual-studio

Improve this answer

Comments

0

Five years have passed since the original post remains without the answer, and now I stumbled upon it because I am trying to do exactly the same as Thomas. One way to do it, which is far from perfect, is to add the parameters with test values in the SQL file on top of the query. (e.g. DECLARE @p1=10) Later they need to be removed of course. What is needed in my view in VS is a user interface like when a stored procedure with parameters is run from SQL Server Management studio and it pops up with a form where parameters can be set before the SP runs.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.