How to insert variable in SQL query string in C#

Question

I would like to adapt this C# code for my project, but I don't know how to add the value of my variable to the query.

private String SQLSearch = "SELECT * FROM MYDATA WHERE Name = myVariable";

I don't know how to insert 'myVariable' in code. I tried this code:

String myVariable = "blablabla";

But it does not work. I get a compilation error.

Answer 1

If the goal is to add a varible value to a string you can use string interpolation. This will look sth. like this:

string myText = "Hello World";
string myHelloWord = $"I say {myText}";

But it is not recommended to do so in Sql-Queries!!! Here you should use parameterized Queries. So you add a specific element to your sql which is replaced by an SqlParameter. You have to declare this Parameter for sure. Let's take a look on an example:

this.command.CommandText = "Select * From myValues Where id in (@param1, @param2)";
this.command.CommandType = CommandType.Text;
this.command.Parameters.Add(new SQLiteParameter("@param1", myVariable1));
this.command.Parameters.Add(new SQLiteParameter("@param2", myVariable2));
var reader = this.command.ExecuteQuery();

This Parameter have some advantadges over the string interpolation. At first you don't have the risk to become an sql injection. Second approach is that your Database will cache the statement. If you use a string interpolation just for changing the where clause, you will always send a new Query to the Database. So the Database can't use it's caching at best. But if you use Parameter the Database knows your Query and just has to react on the changing where clause. Caching is working better then.