5

I am trying to query SQL Server database from C#

I have class 

Class_A 
{
  public fetch((string name, string last_name))
  {
    SqlConnection conn = null;
    double val = 0;
    string server = "123.444.22.sss";
    string dbase = "xyz";
    string userid = "cnsk";
    string password = "xxxxxx";
    string connection = "Data Source=" + server + ";Initial Catalog=" + dbase 
                        + ";User ID=" + userid + ";Password=" + password;

    conn = new SqlConnection(connection);

    try
    {
      conn.Open();
    }
    catch(Exception)
    {
      string e = "Database error contact administrator";
      MessageBox.Show(e, "Error!");
    }
    try
    {
      SqlDataReader myReader = null;
      SqlCommand myCommand = new SqlCommand("select * from table where NAME"
         + " = name and LAST_NAME = last_name", conn);
      myReader = myCommand.ExecuteReader();
      while (myReader.Read())
      {
        //do something

      }
    }
    catch (Exception e)
    {
      Console.WriteLine(e.ToString());
    }
    return (0);
  }
}

There is a problem in my query.

When I give normal query "select * from table" --- this gives me perfect results.

But when I try to give where condition it gives me error. Any suggestions, to fix this? Thanks.

Improve this question
5
  • 2
    This is prone to SQL injection. Don't forget to sanitize your data before inserting it in a query. Commented Apr 1, 2012 at 17:06
  • Based on the answers provided by the others I would have come up with this: SqlCommand myCommand = new SqlCommand(String.Format("select * from table where NAME = '{0}' and LAST_NAME = '{1}'", name, last_name), conn); Commented Apr 1, 2012 at 17:06
  • 2
    @Silvermind that is very open to sql injections en.wikipedia.org/wiki/SQL_injection Commented Apr 1, 2012 at 17:26
  • @ErikPhilips, I agree. +1 to indicate that your comment matters. I'm more a Linq2Entities guy, so I don't suffer from these problems ;). Commented Apr 1, 2012 at 18:14
  • @Silvermind I am too, until I have to update table set bitfield = 1 where somefield = somevalue... 43,563 rows affected Commented Apr 1, 2012 at 18:37

4 Answers 4

Reset to default
9

Use a parameterised query, and more usings, and stop with the generic exceptions.

something like this where somName and SomeLastName are the values that you wan t to query for.

String sql = "Select * From SomeTable Where [Name] = @Name and [Last_Name] = @LastName";
try
{
  using(SqlConnection conn = new SqlConnection(connection))
  {
    conn.Open();
    using( SqlCommand command = new SqlCommand(sql,conn))
    {
      command.Parameters.Add(new SqlParameter("Name", DbType.String,someName));
      command.Parameters.Add(new SqlParameter("LastName", DbType.String,someLastName));
      using(IDataReader myReader = command.ExecuteReader())
      {
        while (myReader.Read())
        {
           //do something
        }
      }
    }
  } 
  return 0; // Huh?
}
catch(SqlException sex)
{
   Console.Writeline(String.Format("Error - {0}\r\n{1}",sex.Message, sex.StackTace))
}

NB not checked might be a silly in it

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+1 for Using, it is frighteningly uncommon on so many SO questions. Take a look at SqlConnectionStringBuilder msdn.microsoft.com/en-us/library/dce36088.aspx as well (hidden gem people hardly use for some reason)
I know about that one, but rarely bother with it, has it's uses though. It's painful to me how often we shoot ourselves in the foot by being in too much of hurry to bother to aim as well.
Is the using block necessary for SqlCommand? The examples in the documentation for that class do not rely on using. Other Microsoft examples on how to query an SQL server do not show it either.
@Ama It implements IDisposable, so it could be necessary. Leaving it out gains you nothing.
7

⚠️ WARNING This answer contains a SQL injection security vulnerability. Do not use it. Consider using a parameterized query instead, as described in some of the other answers to this question (e.g. Tony Hopkinson's answer).

Try adding quotes around the values in the where clause like this:

select * from table where NAME = 'name' and LAST_NAME = 'last_name'

In your case where you are using variables you need to add the quotes and then concatenate the values of the variables into the string. Or you could use String.Format like this:

var sql = String.Format("select * from table where [NAME] = '{0}' and LAST_NAME = '{1}'", name, last_name);
SqlCommand myCommand = new SqlCommand(sql);
Improve this answer

2 Comments

name and last_name are the variable names
also if you are going to use a reserved work its best to use square brackets [name] for example
4

Try

select * from table where NAME = 'name' and LAST_NAME = 'last_name'

instead of

select * from table where NAME = name and LAST_NAME = last_name

Edit:

If name and last_name are your parameters then try this:

SqlCommand myCommand = new SqlCommand("select * from table where NAME = @name and LAST_NAME = @last_name", conn); 
myCommand.Parameters.AddWithValue( "@name", name );
myCommand.Parameters.AddWithValue( "@last_name", last_name );

Using parameterized commands means that you are invulnerable to a potential huge security hole - sql injection which is possible when command text is manually concatenated.

Improve this answer

Comments

1

The text needs to be quoted as others have said--but that's not really the right answer here. Even without malice you're going to run into trouble with the Irish here, look what happens when you try to look for Mr. O'Neill. Use parameters instead.

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.