CODEWITHSUNDEEP
c#sql
Csharp_learner
Csharp_learner

Reputation: 341

SQL query from C#

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.

Upvotes: 5

Views: 66303

Answers (4)

Robbie
Robbie

Reputation: 19510

⚠️ 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);

Upvotes: 7

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

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

Upvotes: 9

Loren Pechtel
Loren Pechtel

Reputation: 9093

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.

Upvotes: 1

Wiktor Zychla
Wiktor Zychla

Reputation: 48314

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.

Upvotes: 4

Related Questions