C# ASP.NET Web Api Controller - Get all rows from a table using SqlCommand

Question

I'm making a web api server that I need for a school project because we have to make several applications on different platforms to communicate through messages and the web api server has GET, POST and DELETE methods.

Right now I have a GET method that will return a row in a table using ID (for example http://localhost:1442/api/Users/1 will return User with ID of 1)

the code looks like this:

public User Get(int id)
    {
        SqlDataReader reader = null;
        SqlConnection myConnection = new SqlConnection();
        myConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\Downloads\SERVER\SERVER\App_Data\dbCoffeeBreak_.mdf;Integrated Security=True";

        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.CommandType = CommandType.Text;
        sqlCmd.CommandText = "Select * from tbUsers where ID=" + id + "";
        sqlCmd.Connection = myConnection;
        myConnection.Open();
        reader = sqlCmd.ExecuteReader();
        User u = null;
        while (reader.Read())
        {
            u = new User();
            u.ID = Convert.ToInt32(reader.GetValue(0));
            u.Login = reader.GetValue(1).ToString();
            u.Password = reader.GetValue(2).ToString();
            u.Avatar = reader.GetValue(3).ToString();
            u.Email = reader.GetValue(4).ToString();
            u.Online = Convert.ToBoolean(reader.GetValue(5));
        }
        myConnection.Close();
        return u;
    }

but I'm not sure how to make it so that by typing for example only http://localhost:1442/api/Users the server would return ALL columns in the table. I tried setting the sqlCmd.CommandText = to just Select * from tbUsers but that just returns the last User in the table not all of them.

Answer 1

That's because you are only returning the last user from Reader.Read

There are couple of issues and suggestions for you

1. Make Id as optional parameter , so that if you dont pass anyId, it will query for allusers`. With this you dont need to create separate method for getting all users.

2. Return List<User> instead of return single User

     public List<User> Get(int? id = null)
     {
       SqlDataReader reader = null;
       SqlConnection myConnection = new SqlConnection();
       myConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\Downloads\SERVER\SERVER\App_Data\dbCoffeeBreak_.mdf;Integrated Security=True";

       SqlCommand sqlCmd = new SqlCommand();
       sqlCmd.CommandType = CommandType.Text;

       if(id !=null)
           sqlCmd.CommandText = "Select * from tbUsers where ID=" + id + "";
       else
           sqlCmd.CommandText = "Select * from tbUsers ";

       sqlCmd.Connection = myConnection;
       myConnection.Open();
       reader = sqlCmd.ExecuteReader();
       List<User> users = List<User>();
       while (reader.Read())
       {
          u = new User();
          u.ID = Convert.ToInt32(reader.GetValue(0));
          u.Login = reader.GetValue(1).ToString();
          u.Password = reader.GetValue(2).ToString();
          u.Avatar = reader.GetValue(3).ToString();
          u.Email = reader.GetValue(4).ToString();
          u.Online = Convert.ToBoolean(reader.GetValue(5));
          users.Add(u);
      }
      myConnection.Close();
      return users; 
    }

Plus always use Parameterized queries to prevent SQL Injection Attacks

Will suggest you to update your query as

sqlCmd.CommandText = "Select * from tbUsers where ID=@Id";      
sqlCmd.Parameters.AddWithValue("@Id", id);