Using a variable in a query

Question

I am trying to set up a query for my dataset in C# using a variable for the filter. For example I am trying to only display a specific account number and his balance, with a local variable being the account number used as a filter for that exact one. Am I going about this the wrong way?

I am in no stretch of the imagination a real programmer, I am in a bind and have skimmed along using a guide to programming in C# and the limited brain power I have (which is now running on empty) :)

I also would like to alter the database information using a button with an eventhandler to add specific amounts a cell that was queried. Am I doomed for my lack of knowledge on hard coding or can I actually pull this off?

Sincerely, noobish engineer trying to program... or Jev

Answer 1

You could just use the variable to generate your SQL-Query dynamically, but beware of SQL-Injection - be really sure, that your variable may not contain SQL-Statements.

You could use a function, that builds and returns your SQL-Query like this, with the variable for the filter as parameter:

internal string BuildSQLQueryForAccount(int account)
{
    StringBuilder sb = new StringBuilder();
    sb.Append("SELECT * ");
    sb.Append("FROM Accounts ");
    sb.AppendFormat("WHERE AccountNumber = {0}", account);
    return sb.ToString();
}

Answer 2

When you setup your dataset query you can do something like this;

SELECT Name FROM TableNames WHERE Name = @Variable

Have a look at this link for more info

It might be worth having a look into SQL injection attack too, click here

Answer 3

SqlCommand cmd = new sqlCommand("select * from table1 where column1 = @value", connection);
cmd.parameters.add(new SqlParameters("@value", "yourvalue"));
SqlDataReader dr = cmd.ExecuteReader();
while (dr.read())
{

   //code here!

}

I Hope this will be usefull!

Answer 4

Once upon a time, I've written a little article on why you should definitely use parameters in SQL statements. (I've written it in response to the fact that I saw way to many people using string concat enation to write their queries).

You can find it here: http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.html