List<User> allUsers = new List<User>();

SqlConnection conn = new SqlConnection(连接字符串);

SqlCommand cmd = new SqlCommand("SELECT语句",conn);

conn.Open();

using(SqlDataReader rdr = cmd.ExecuteReader())
{
while(rdr.Read())
{
User u = new User();
user.Id = (int)rdr["Id"];
user.LoginId = (string)rdr["LoginId"];
user.LoginPwd = (string)rdr["LoginPwd"];
user.Name = (string)rdr["Name"];
user.Address = (string)rdr["Address"];
user.Phone = (string)rdr["Phone"];
user.Mail = (string)rdr["Mail"];
user.UserRoleId = (int)rdr["UserRoleId"];
user.UserStateId = (int)rdr["UserStateId"];

allUsers.Add(u);
}
}

conn.Close();

return allUsers;

======================================================

SqlConnection conn = new SqlConnection(连接字符串);

SqlDataAdapter adapter = new SqlDataAdapter("SELECT语句",conn);

DataSet ds = new DataSet();

adapter.Fill(ds,"表名");

return ds.Tables[0];