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.

The command is correct but when you loop over the results you need to add each user built during the loop to a `List` and return that list to whichever interface you are using to show them — Steve, Jan 14 '18 at 14:49

Ehsan Ullah Nazir · Accepted Answer · 2018-01-14T15:07:59.633

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

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

1 Answers1