CODEWITHSUNDEEP
c#sqlite
Vozerpa
Vozerpa

Reputation: 31

How to insert variable in SQL query string in C#

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.

Upvotes: 3

Views: 6692

Answers (2)

Sebi
Sebi

Reputation: 3979

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.

Upvotes: 5

user622505
user622505

Reputation: 773

How about:

private String SQLSearchFormat = "SELECT * FROM MYDATA WHERE Name = {0}";

And later using that as:

string myVariable = "blablabla";
string.Format(SQLSearchFormat, myVariable);

If you're taking myVariable from input, beware of SQL injection.

As this looks very much like another case of XY problem, you most likely want to use parameterized queries, as other comments and @Sebi's answer suggests.

Upvotes: 0

Related Questions